CRUD Operations In ADO.NET

(ADO.NET)


Tools Used: Visual Studio 2013 Express, Sql Server Express 2008/2012.

I have written code for crud(Insert/Update/Delete) operations using ado.net.

ADO.NET Architecture

                          ASP.NET WEB APP
                                |
                         ADO.NET DRIVERS
                                |
                         SQL SERVER DB



The main classes in ado.net are

1. Connection Class
2.Command Class


The main methods in Connection class are

Open() -> To open the sql server connection
Close() -> To cloase the sql server connection

The main methods in Command class are

ExecuteNonQuery() -> It is used to execute insert, update and delete Sql statement. It return the number of rows affected as the result. If you execute  the insert/update/delete statement, the rows affected value will be one.

ExecuteScalar() -> It is used to execute sql count statement. It returns the first row first column value as the result.

ExecuteReader() -> It is used execute sql select statement. It returns selected datasource as the sqldatareader.

The command class has commandtype enumeration, The values of commandtype enumeration are listed below

CommandType.Text -> This is the default value of commandtype enumeration. This value is set while executing the sql query statement.

CommandType.Procedure -> CommandType enumeration set to this value while executing the stored procedure through ado.net.


I have written code/sample for the above mentioned methods and to execute the stored procedure.

I attached the sample project which includes sql server db in the app_data folder. Once you download the project you can run without any changes. If you couldn't run, you check the connection string in web.config file.

  protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
                BindGridview();
        }

        //Example for ExecuteNonQuery() method
        protected void btn_insert_Click(object sender, EventArgs e)
        {
            try
            {
                using(SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["FruitsConn"].ToString()) )
                {
                    string sqlQuery = "INSERT INTO FruitsList(Name,Quantity) VALUES(@Name,@Quantity)";
                    conn.Open();
                    SqlCommand cmd = new SqlCommand(sqlQuery, conn);
                    cmd.CommandType = System.Data.CommandType.Text;
                    cmd.Parameters.AddWithValue("@Name", txt_fruitname.Text);
                    cmd.Parameters.AddWithValue("@Quantity", txt_fruitqnty.Text);
                    int rowsAffected = cmd.ExecuteNonQuery();

                    if (rowsAffected == 1)
                    {
                        lbl_result.Text =  txt_fruitname.Text + " added successfully.";
                        lbl_result.ForeColor = System.Drawing.Color.Green;
                      
                    }
                 
                    //rebind the gridview
                    BindGridview();
                }
            }
            catch(Exception exc)
            {
                lbl_result.Text = exc.Message;
                lbl_result.ForeColor = System.Drawing.Color.Red;
            }
        }

        //Example for executing the stored procedure
        protected void btn_insertsp_Click(object sender, EventArgs e)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["FruitsConn"].ToString()))
                {
                  
                    conn.Open();
                    //stored procedure name
                    SqlCommand cmd = new SqlCommand("InsertFruit", conn);

                    //commandtype stored procedure
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;

                    //add storedprocedure parameters
                    cmd.Parameters.Add(new SqlParameter("@Name", System.Data.SqlDbType.VarChar, 150));
                    cmd.Parameters.Add(new SqlParameter("@Quantity", System.Data.SqlDbType.Int));

                    //set value for the parameters
                    cmd.Parameters["@Name"].Value= txt_fruitname.Text;
                    cmd.Parameters["@Quantity"].Value = txt_fruitqnty.Text;


                    int rowsAffected = cmd.ExecuteNonQuery();

                    if (rowsAffected == 1)
                    {
                        lbl_result.Text = txt_fruitname + " added successfully through stored procedure.";
                        lbl_result.ForeColor = System.Drawing.Color.Green;
                    }
                 

                    //rebind the gridview
                    BindGridview();
                }
            }
            catch (Exception exc)
            {
                lbl_result.Text = exc.Message;
                lbl_result.ForeColor = System.Drawing.Color.Red;
            }
        }

        //Example for ExecuteScalar() method
        protected void btn_count_Click(object sender, EventArgs e)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["FruitsConn"].ToString()))
                {
                    string sqlQuery = "SELECT Count(*) FROM FruitsList";
                    conn.Open();
                    SqlCommand cmd = new SqlCommand(sqlQuery, conn);
                    cmd.CommandType = System.Data.CommandType.Text;
                    int totalRecords =(Int32) cmd.ExecuteScalar();
                    lbl_result.Text = "Total Records: "+ totalRecords;
                    lbl_result.ForeColor = System.Drawing.Color.Green;
                }
            }
            catch (Exception exc)
            {
                lbl_result.Text = exc.Message;
                lbl_result.ForeColor = System.Drawing.Color.Red;
            }
        }

     
        protected void btn_list_Click(object sender, EventArgs e)
        {
            try
            {
                BindGridview();
            }
            catch (Exception)
            { }
        }



        //Example for ExecuteReader() method
        private void BindGridview()
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["FruitsConn"].ToString()))
                {
                  
                    string sqlQuery = "SELECT * FROM FruitsList";
                    conn.Open();
                    SqlCommand cmd = new SqlCommand(sqlQuery, conn);
                    cmd.CommandType = System.Data.CommandType.Text;
                    SqlDataReader rdr = cmd.ExecuteReader();
                    grdvw_fruits.DataSource = rdr;
                    grdvw_fruits.DataBind();

                }
            }
            catch (Exception exc)
            {
                lbl_result.Text = exc.Message;
                lbl_result.ForeColor = System.Drawing.Color.Red;
            }
        }


        //Delete fruit through stored procedure
        protected void btn_delete_Click(object sender, EventArgs e)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["FruitsConn"].ToString()))
                {

                    conn.Open();
                    //stored procedure name
                    SqlCommand cmd = new SqlCommand("DeleteFruit", conn);

                    //commandtype stored procedure
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;

                    //add storedprocedure parameters
                    cmd.Parameters.Add(new SqlParameter("@Name", System.Data.SqlDbType.VarChar, 150));
               

                    //set value for the parameters
                    cmd.Parameters["@Name"].Value = txt_fruitname.Text;
                


                    int rowsAffected = cmd.ExecuteNonQuery();

                    if (rowsAffected == 1)
                    {
                        lbl_result.Text = txt_fruitname.Text + " deleted successfully through stored procedure.";
                        lbl_result.ForeColor = System.Drawing.Color.Green;
                    }


                    //rebind the gridview
                    BindGridview();
                }
            }
            catch (Exception exc)
            {
                lbl_result.Text = exc.Message;
                lbl_result.ForeColor = System.Drawing.Color.Red;
            }
        }

        //checkbox selection change event, to prepopulate the selected fruit in the textboxes
        protected void chck_select_CheckedChanged(object sender, EventArgs e)
        {
            try
            {
                CheckBox chck = sender as CheckBox;
              
                if(chck.Checked)
                {
                    //uncheck the checkboxes
                for(int i=0;i<grdvw_fruits.Rows.Count;i++)
                {
                    var chckbx = (CheckBox)grdvw_fruits.Rows[i].FindControl("chck_select");
                    if(chckbx.Checked)
                    {
                        if(chckbx.ClientID != chck.ClientID)
                        {
                            chckbx.Checked = false;
                        }
                    }
                }

                    //get the current row
                GridViewRow row = (GridViewRow) chck.Parent.Parent;
                txt_fruitname.Text = row.Cells[1].Text;
                txt_fruitqnty.Text = row.Cells[2].Text;

                //id
               hdn_id.Value = grdvw_fruits.DataKeys[row.RowIndex].Value.ToString();
                }
                else
                {
                    txt_fruitname.Text = "";
                    txt_fruitqnty.Text = "";
                }
            }
            catch(Exception exc)
            {
                lbl_result.Text = exc.Message;
                lbl_result.ForeColor = System.Drawing.Color.Red;
            }
        }

        protected void btn_updatesp_Click(object sender, EventArgs e)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["FruitsConn"].ToString()))
                {

                    conn.Open();
                    //stored procedure name
                    SqlCommand cmd = new SqlCommand("UpdateFruit", conn);

                    //commandtype stored procedure
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;

                    //add storedprocedure parameters
                    cmd.Parameters.Add(new SqlParameter("@Name", System.Data.SqlDbType.VarChar, 150));
                    cmd.Parameters.Add(new SqlParameter("@Quantity", System.Data.SqlDbType.Int));
                    cmd.Parameters.Add(new SqlParameter("@Id", System.Data.SqlDbType.Int));

                    //set value for the parameters
                    cmd.Parameters["@Name"].Value = txt_fruitname.Text;
                    cmd.Parameters["@Quantity"].Value = txt_fruitqnty.Text;
                    cmd.Parameters["@Id"].Value = hdn_id.Value;


                    int rowsAffected = cmd.ExecuteNonQuery();

                    if (rowsAffected == 1)
                    {
                        lbl_result.Text = txt_fruitname.Text + " updated successfully through stored procedure.";
                        lbl_result.ForeColor = System.Drawing.Color.Green;
                    }


                    //rebind the gridview
                    BindGridview();
                }
            }
            catch (Exception exc)
            {
                lbl_result.Text = exc.Message;
                lbl_result.ForeColor = System.Drawing.Color.Red;
            }
        }


Ratings


Average Rating: 0.00 by 0 users
Giri Prasad
5/17/2014
5/17/2014
Download PDF
Download Project

Comments