Avoid code repetition in conditions using C# in ASP.Net

lingers
 
on Mar 26, 2021 04:12 AM
687 Views

How to avoid repetition of select and insert statement on button click in code behind.

I have this form that i have to check whether textbox is empty before i submit to the database. Now i have to write insert statement 3 times for 3 different Textboxes. Is there a way i can loop through instead of repeating the select and insert statement.

My Code below 

public partial class Order : System.Web.UI.Page
{
    SqlCommand cmd1 = new SqlCommand();
    SqlCommand cmd22 = new SqlCommand();
    SqlCommand cmd2 = new SqlCommand();
    SqlCommand cmd2222 = new SqlCommand();
    SqlCommand cmd222 = new SqlCommand();
    SqlCommand cmd33 = new SqlCommand();
    SqlCommand cmd3333 = new SqlCommand();
    SqlCommand cmd333 = new SqlCommand();
    SqlCommand cmd44 = new SqlCommand();
    SqlCommand cmd4444 = new SqlCommand();
    SqlCommand cmd444 = new SqlCommand();
    SqlConnection dbConn = new SqlConnection();
    SqlConnection dbConn1 = new SqlConnection();
    SqlConnection dbConn2 = new SqlConnection();
    SqlConnection dbConn22 = new SqlConnection();
    SqlConnection dbConn222 = new SqlConnection();
    SqlConnection dbConn2222 = new SqlConnection();
    SqlConnection dbConn33 = new SqlConnection();
    SqlConnection dbConn333 = new SqlConnection();
    SqlConnection dbConn3333 = new SqlConnection();
    SqlConnection dbConn44 = new SqlConnection();
    SqlConnection dbConn444 = new SqlConnection();
    SqlConnection dbConn4444 = new SqlConnection();
    SqlDataReader dr1, dr22, dr222, dr2222, dr33, dr333, dr3333, dr44, dr444, dr4444;
    string selectSQL ; 
    
    protected void Button2_Click(object sender, EventArgs e)
    {
            string selectSQL1;
            selectSQL1 = " Select FinYear,Year,Vat  FROM Settings where id= '1'  ";
            dbConn1.ConnectionString = "Data Source=NER\\SQLEXPRESS01; Initial Catalog=kaging;Integrated Security=True;";
            cmd2.Connection = dbConn1;
            cmd2.CommandText = selectSQL1;
            cmd2.CommandType = CommandType.Text;

            try
            {
                dbConn1.Open();
                dr1 = cmd2.ExecuteReader();
                if (dr1.Read())
                {
                    Session["1"]= dr1["FinYear"].ToString();
                    Session["2"] = dr1["Year"].ToString();
                    Session["3"] = dr1["Vat"].ToString();


                    string selectSQL22;
                    selectSQL22 = " SELECT id FROM job  where pid = '" + txtSearch.Text + "'   ";
                    dbConn22.ConnectionString = "Data Source=NER\\SQLEXPRESS01; Initial Catalog=kaging;Integrated Security=True;";
                    cmd22.Connection = dbConn22;
                    cmd22.CommandText = selectSQL22;
                    cmd22.CommandType = CommandType.Text;

                    try
                    {
                        dbConn22.Open();
                        dr22 = cmd22.ExecuteReader();
                        if (dr22.Read())
                        {
                            Session["4"] = dr22["id"].ToString();
                        }
                        else
                        {
                            //  Label007.Text = "Error @ Emboss";
                        }
                    }
                    catch (Exception err)
                    {
                        Response.Write(err.ToString());
                    }
                    finally
                    {
                        dbConn22.Close();
                    }
                }
                else
                {
                  //  Label007.Text = "Error @ Emboss";
                }

            }
            catch (Exception err)
            {
                Response.Write(err.ToString());

            }
            finally
            {
                dbConn1.Close();
            }
                    string insertSQL;
                    insertSQL = "INSERT INTO Orders (";
                    insertSQL += "financialyear,calendaryear,vat,month,productcode,productcodeM,purchaseorderno,supplier,dateordered,dateexpected,producttypeM,producttype,requisitioncode,posino,quantity,requirement)";
                    insertSQL += "VALUES('";
                    insertSQL += Session["1"] + "','";
                    insertSQL += Session["2"] + "','";
                    insertSQL += Session["3"] + "','";
                    insertSQL += "March" + "','";
                    insertSQL += Session["4"] + "','";
                    insertSQL += txtSearch.Text + "','";
                    insertSQL += TextBox4.Text + "','";
                    insertSQL += TextBox5.Text + "','";
                    insertSQL += TextBox6.Text + "','";
                    insertSQL += TextBox7.Text + "','";

                    insertSQL += DropDownList1.SelectedItem.ToString() + "','";
                    insertSQL += DropDownList1.SelectedValue.ToString() + "','";
                    insertSQL += TextBox9.Text + "','";
                    insertSQL += txtSearch1.Text + "','";
                    insertSQL += TextBox2.Text + "','";
                    insertSQL += TextBox3.Text + "')";

                    String connectionString = "Data Source=NER\\SQLEXPRESS01; Initial Catalog= kaging;Integrated Security=True";
                    SqlConnection con = new SqlConnection(connectionString);
                    SqlCommand cmd1 = new SqlCommand(insertSQL, con);
                    int added = 0;

                    try
                    {
                        con.Open();
                        added = cmd1.ExecuteNonQuery();

                        // Label007.Text = added.ToString();

                        if (added == 1)
                        {
                        }
                        else
                        {

                        }
                    }
                    catch (Exception err)
                    {
                        Response.Write(err.ToString());
                    }
                    finally
                    {
                        con.Close();
                    }                                                  
    }
}

 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Mar 27, 2021 01:51 AM

Hi lingers,

Refer below modified code.

protected void Page_Load(object sender, EventArgs e)
{
    Panel1.Visible = true;

    Panel3.Visible = false;
    Panel4.Visible = false;
    Panel5.Visible = false;

}
[WebMethod]
public static List<string> GetAutoCompleteData(string pid)
{
    List<string> result = new List<string>();
    using (SqlConnection con = new SqlConnection("Data Source=NER\\SQLEXPRESS01;Integrated Security=true;Initial Catalog=kaging;"))
    {
        using (SqlCommand cmd = new SqlCommand("select id,pid from job where pid LIKE '%'+@SearchText+'%'", con))
        {
            con.Open();
            cmd.Parameters.AddWithValue("@SearchText", pid);
            SqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                result.Add(string.Format("{0}/{1}", dr["pid"], dr["id"]));
            }
            return result;
        }
    }
}

[WebMethod]
public static List<string> GetAutoCompleteData1(string dieno)
{
    List<string> result = new List<string>();
    using (SqlConnection con = new SqlConnection("Data Source=NER\\SQLEXPRESS01;Integrated Security=true;Initial Catalog=kaging;"))
    {
        using (SqlCommand cmd = new SqlCommand("select id,dieno from job_die where dieno LIKE '%'+@SearchText1+'%'", con))
        {
            con.Open();
            cmd.Parameters.AddWithValue("@SearchText1", dieno);
            SqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                result.Add(string.Format("{0}/{1}", dr["dieno"], dr["id"]));
            }
            return result;
        }
    }
}

protected void TextBox9_TextChanged(object sender, EventArgs e)
{

}

protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
{
    if (DropDownList1.SelectedValue == "1")
    {
        Panel1.Visible = true;
        Panel3.Visible = false;
        Panel4.Visible = false;
        Panel5.Visible = false;
    }
    else if (DropDownList1.SelectedValue == "3")
    {
        Panel1.Visible = false;
        Panel3.Visible = true;
        Panel4.Visible = false;
        Panel5.Visible = false;
    }
    else if (DropDownList1.SelectedValue == "4")
    {
        Panel1.Visible = false;
        Panel3.Visible = false;
        Panel4.Visible = true;
        Panel5.Visible = false;

    }
    else if (DropDownList1.SelectedValue == "5")
    {
        Panel1.Visible = false;
        Panel3.Visible = false;
        Panel4.Visible = false;
        Panel5.Visible = true;
    }
}

protected void Button2_Click(object sender, EventArgs e)
{
    SetSession("Select FinYear,Year,Vat FROM Settings where id= '1'", txtSearch.Text);

    string insertSQL;
    insertSQL = "INSERT INTO Orders (";
    insertSQL += "financialyear,calendaryear,vat,month,productcode,productcodeM,purchaseorderno,supplier,dateordered,dateexpected,producttypeM,producttype,requisitioncode,posino,quantity,requirement)";
    insertSQL += "VALUES('";
    insertSQL += Session["1"] + "','";
    insertSQL += Session["2"] + "','";
    insertSQL += Session["3"] + "','";
    insertSQL += "March" + "','";
    insertSQL += Session["4"] + "','";
    insertSQL += txtSearch.Text + "','";
    insertSQL += TextBox4.Text + "','";
    insertSQL += TextBox5.Text + "','";
    insertSQL += TextBox6.Text + "','";
    insertSQL += TextBox7.Text + "','";
    insertSQL += DropDownList1.SelectedItem.ToString() + "','";
    insertSQL += DropDownList1.SelectedValue.ToString() + "','";
    insertSQL += TextBox9.Text + "','";
    insertSQL += txtSearch1.Text + "','";
    insertSQL += TextBox2.Text + "','";
    insertSQL += TextBox3.Text + "')";
    Insert(insertSQL);
}

protected void Button3_Click(object sender, EventArgs e)
{

}

protected void Button1_Click(object sender, EventArgs e)
{
    SetSession("Select FinYear,Year,Vat FROM Settings where id= '1'", txtSearch.Text);
    if (!string.IsNullOrEmpty(TextBox24.Text))
    {
        string query;
        query = "INSERT INTO Orders (";
        query += "financialyear,calendaryear,vat,month,productcode,productcodeM,purchaseorderno,supplier,dateordered,dateexpected,producttypeM,producttype,requisitioncode,posino,quantity,requirement)";
        query += "VALUES('";
        query += Session["1"] + "','";
        query += Session["2"] + "','";
        query += Session["3"] + "','";
        query += "March" + "','";
        query += Session["4"] + "','";
        query += txtSearch.Text + "','";
        query += TextBox4.Text + "','";
        query += TextBox5.Text + "','";
        query += TextBox6.Text + "','";
        query += TextBox7.Text + "','";
        query += DropDownList1.SelectedItem.ToString() + "','";
        query += DropDownList1.SelectedValue.ToString() + "','";
        query += TextBox9.Text + "','";
        query += TextBox24.Text + "','";
        query += TextBox25.Text + "','";
        query += TextBox26.Text + "')";
        Insert(query);
    }

    if (!string.IsNullOrEmpty(TextBox27.Text))
    {
        string query;
        query = "INSERT INTO Orders (";
        query += "financialyear,calendaryear,vat,month,productcode,productcodeM,purchaseorderno,supplier,dateordered,dateexpected,producttypeM,producttype,requisitioncode,posino,quantity,requirement)";
        query += "VALUES('";
        query += Session["1"] + "','";
        query += Session["2"] + "','";
        query += Session["3"] + "','";
        query += "March" + "','";
        query += Session["4"] + "','";
        query += txtSearch.Text + "','";
        query += TextBox4.Text + "','";
        query += TextBox5.Text + "','";
        query += TextBox6.Text + "','";
        query += TextBox7.Text + "','";
        query += DropDownList1.SelectedItem.ToString() + "','";
        query += DropDownList1.SelectedValue.ToString() + "','";
        query += TextBox9.Text + "','";
        query += TextBox27.Text + "','";
        query += TextBox28.Text + "','";
        query += TextBox29.Text + "')";
        Insert(query);
    }

    if (string.IsNullOrEmpty(TextBox46.Text))
    {
        Response.Redirect("3.aspx");
    }
    else
    {
        string query;
        query = "INSERT INTO Orders (";
        query += "financialyear,calendaryear,vat,month,productcode,productcodeM,purchaseorderno,supplier,dateordered,dateexpected,producttypeM,producttype,requisitioncode,posino,quantity,requirement)";
        query += "VALUES('";
        query += Session["1"] + "','";
        query += Session["2"] + "','";
        query += Session["3"] + "','";
        query += "March" + "','";
        query += Session["4"] + "','";
        query += txtSearch.Text + "','";
        query += TextBox4.Text + "','";
        query += TextBox5.Text + "','";
        query += TextBox6.Text + "','";
        query += TextBox7.Text + "','";
        query += DropDownList1.SelectedItem.ToString() + "','";
        query += DropDownList1.SelectedValue.ToString() + "','";
        query += TextBox9.Text + "','";
        query += TextBox46.Text + "','";
        query += TextBox47.Text + "','";
        query += TextBox48.Text + "')";
        if (Insert(query) > 0)
        {
            Response.Redirect("3.aspx");
        }
    }
}

protected void Button4_Click(object sender, EventArgs e)
{

}

protected void Button5_Click(object sender, EventArgs e)
{

}

private int Insert(string query)
{
    string connectionString = "Data Source=NER\\SQLEXPRESS01; Initial Catalog= kaging;Integrated Security=True";
    SqlConnection con = new SqlConnection(connectionString);
    SqlCommand cmd = new SqlCommand(query, con);
    int added = 0;
    try
    {
        con.Open();
        added = cmd.ExecuteNonQuery();
    }
    catch (Exception err)
    {
        Response.Write(err.ToString());
    }
    finally
    {
        con.Close();
    }

    return added;
}

// Set Session1, Session2 and Session3.
private void SetSession(string query, string searchtext)
{
    SqlConnection con = new SqlConnection();
    con.ConnectionString = "Data Source=NER\\SQLEXPRESS01; Initial Catalog=kaging;Integrated Security=True;";
    SqlCommand cmd = new SqlCommand();
    cmd.Connection = con;
    cmd.CommandText = query;
    cmd.CommandType = CommandType.Text;
    try
    {
        con.Open();
        SqlDataReader dr = cmd.ExecuteReader();
        if (dr.Read())
        {
            Session["1"] = dr[0].ToString();
            Session["2"] = dr[1].ToString();
            Session["3"] = dr[2].ToString();
            SetSession4(searchtext);
        }
    }
    catch (Exception err)
    {
        Response.Write(err.ToString());
    }
    finally
    {
        con.Close();
    }
}

// Set Session4.
private void SetSession4(string pId)
{
    string query = "SELECT id FROM job where pid = @PId";
    SqlConnection con = new SqlConnection();
    con.ConnectionString = "Data Source=NER\\SQLEXPRESS01; Initial Catalog=kaging;Integrated Security=True;";
    SqlCommand cmd = new SqlCommand();
    cmd.Connection = con;
    cmd.CommandText = query;
    cmd.CommandType = CommandType.Text;
    cmd.Parameters.AddWithValue("@PId", pId.Trim());
    try
    {
        con.Open();
        object id = cmd.ExecuteScalar();
        if (id != null)
        {
            Session["4"] = id.ToString();
        }
    }
    catch (Exception err)
    {
        Response.Write(err.ToString());
    }
    finally
    {
        con.Close();
    }
}