Insert Update Delete Using Stored Procedure

SqlConnection CN = new SqlConnection(ConfigurationManager.ConnectionStrings["EmployeeConnectionString"].ConnectionString);
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
BindData();
ddlBindlist();
ViewState["sortOrder"] = "";
bindGridView("", "");
}
}

private void BindData()
{
try
{
SqlDataAdapter da = new SqlDataAdapter("SelectEmployeeAll", CN);
da.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure;
DataSet ds = new DataSet();
da.Fill(ds);
GridView1.DataSource = ds;
//GridView1.Columns[2].Visible = false;
GridView1.DataBind();
}
catch (Exception ex)
{
throw ex;
}
}

protected void btnAdd_Click(object sender, EventArgs e)
{
if (btnAdd.Text == "Add")
{
try
{
//string str = "InsertEmployee";
SqlDataAdapter da = new SqlDataAdapter();
da.InsertCommand = new SqlCommand("InsertEmployee", CN);
da.InsertCommand.CommandType = CommandType.StoredProcedure;
da.InsertCommand.Parameters.AddWithValue("@Name", txtEmpName.Text);
da.InsertCommand.Parameters.AddWithValue("@Address", txtAddress.Text);
da.InsertCommand.Parameters.AddWithValue("@DateOfBirth", txtDoB.Text);
da.InsertCommand.Parameters.AddWithValue("@JoingDate", txtJoingDate.Text);
da.InsertCommand.Parameters.AddWithValue("@CompanyId", ddlCompanyid.SelectedValue);
CN.Open();
da.InsertCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
lblMessage.Text= "Inserted Successfully";
}
}
else if (btnAdd.Text == "Update")
{
try
{
int id = Convert.ToInt32(GridView1.SelectedRow.Cells[2].Text);
//string strUpdate = "UpdateEmployee";
SqlDataAdapter da = new SqlDataAdapter();
da.UpdateCommand = new SqlCommand("UpdateEmployee", CN);
da.UpdateCommand.CommandType = CommandType.StoredProcedure;
da.UpdateCommand.Parameters.AddWithValue("@Name", txtEmpName.Text);
da.UpdateCommand.Parameters.AddWithValue("@Address", txtAddress.Text);
da.UpdateCommand.Parameters.AddWithValue("@DateOfBirth", txtDoB.Text);
da.UpdateCommand.Parameters.AddWithValue("@JoingDate", txtJoingDate.Text);
da.UpdateCommand.Parameters.AddWithValue("@CompanyId", ddlCompanyid.SelectedValue);
da.UpdateCommand.Parameters.AddWithValue("@Id", id.ToString());
CN.Open();
da.UpdateCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
lblMessage.Text = "Updated Successfully";
}

}

clear();
btnAdd.Text = "Add";
BindData();
}

protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
{
txtEmpName.Text = GridView1.SelectedRow.Cells[3].Text;
txtAddress.Text = GridView1.SelectedRow.Cells[4].Text;
txtDoB.Text = GridView1.SelectedRow.Cells[5].Text;
txtJoingDate.Text = GridView1.SelectedRow.Cells[6].Text;
ddlCompanyid.SelectedItem.Text = GridView1.SelectedRow.Cells[7].Text;
btnAdd.Text = "Update";
}

private void clear()
{
txtEmpName.Text = "";
txtAddress.Text = "";
txtDoB.Text = "";
txtJoingDate.Text = "";
}

protected void btnCancle_Click1(object sender, EventArgs e)
{
clear();
}

public void ddlBindlist()
{
try
{
string str = "SelectCompanyInfoAll";
SqlDataAdapter da = new SqlDataAdapter(str, CN);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
DataSet ds = new DataSet();
da.Fill(ds);
ddlCompanyid.DataSource = ds;
ddlCompanyid.DataTextField = "CompanyName";
ddlCompanyid.DataValueField = "Id";
ddlCompanyid.DataBind();
}
catch (Exception ex)
{
throw ex;
lblMessage.Text = ex.Message;
}
}
protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
//int Id = Convert.ToInt32(GridView1.SelectedRow.Cells[2].Text);
int Id = Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Value);
DeleteRecordByID(Id);
}

protected void DeleteRecordByID(long Id)
{
SqlDataAdapter da = new SqlDataAdapter();
da.DeleteCommand = new SqlCommand("DeleteEmployee", CN);
da.DeleteCommand.CommandType = CommandType.StoredProcedure;
da.DeleteCommand.Parameters.AddWithValue("@Id",Id);
CN.Open();
da.DeleteCommand.ExecuteNonQuery();
//DataSet ds = new DataSet();
//da.Fill(ds);
BindData();
}
protected void GridView1_RowDeleted(object sender, GridViewDeletedEventArgs e)
{
if (e.Exception == null)
{
lblMessage.Text = "Record deleted";
}

else
{
lblMessage.Text = e.Exception.Message;
e.ExceptionHandled = true;
}
}
protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
GridView1.PageIndex = e.NewPageIndex;
BindData();
}
public void bindGridView(string sortExp, string sortDir)
{
if (CN.State == ConnectionState.Closed)
{
CN.Open();
}

SqlCommand mySqlCommand = new SqlCommand("SelectEmployeeAll", CN);
SqlDataAdapter mySqlAdapter = new SqlDataAdapter(mySqlCommand);
DataSet myDataSet = new DataSet();
mySqlAdapter.Fill(myDataSet);

DataView myDataView = new DataView();
myDataView = myDataSet.Tables[0].DefaultView;

if (sortExp != string.Empty)
{
myDataView.Sort = string.Format("{0} {1}", sortExp, sortDir);
}

GridView1.DataSource = myDataView;
GridView1.DataBind();

if (CN.State == ConnectionState.Open)
{
CN.Close();
}
}

public string sortOrder
{
get
{
if (ViewState["sortOrder"].ToString() == "desc")
{
ViewState["sortOrder"] = "asc";
}
else
{
ViewState["sortOrder"] = "desc";
}

return ViewState["sortOrder"].ToString();
}
set
{
ViewState["sortOrder"] = value;
}
}
protected void GridView1_Sorting(object sender, GridViewSortEventArgs e)
{
bindGridView(e.SortExpression, sortOrder);
}