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);
}

Insert Update Delete Linq

Employee Name:
Address:
Date of Birth:	(eg. MM/dd/yyyy)
Joing Date:	(eg. MM/dd/yyyy)
CompanyName:

//-----Code

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

private void BindData()
{
try
{
GridView1.DataSource = from emp in db.Employees
join cinfo in db.CompanyInformations on emp.CompanyId equals cinfo.Id
orderby emp.Id
select new { emp.Id, emp.Name, emp.Address, emp.DateOfBirth, emp.JoingDate, cinfo.CompanyName };
GridView1.DataBind();
}
catch (Exception ex)
{
throw ex;
}
}

protected void btnAdd_Click(object sender, EventArgs e)
{
if (btnAdd.Text == "Add")
{
Employees emp = new Employees();
emp.Name = txtEmpName.Text;
emp.Address = txtAddress.Text;
emp.DateOfBirth = txtDoB.Text;
emp.JoingDate = txtJoingDate.Text;
emp.CompanyId = Convert.ToInt32(ddlCompanyid.SelectedValue);

db.Employees.InsertOnSubmit(emp);
try
{
db.SubmitChanges();
}
catch (Exception ex)
{
throw ex;
}
finally
{
lblMessage.Text = "Inserted Successfully";
}
}
else if (btnAdd.Text == "Update")
{
Employees emp = db.Employees.Single(ep => ep.Id == Convert.ToInt32(GridView1.SelectedRow.Cells[2].Text));
emp.Name = txtEmpName.Text;
emp.Address = txtAddress.Text;
emp.DateOfBirth = txtDoB.Text;
emp.JoingDate = txtJoingDate.Text;
emp.CompanyId = Convert.ToInt32(ddlCompanyid.SelectedValue);

try
{
db.SubmitChanges();
}
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
{
//DataClassesDataContext db = new DataClassesDataContext();
ddlCompanyid.DataSource = from cinfo in db.CompanyInformations
select cinfo;
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);
lblMessage.Text = "Record deleted";
}

protected void DeleteRecordByID(long Id)
{
//DataClassesDataContext db = new DataClassesDataContext();
Employees toDelete = db.Employees.Single(p => p.Id == Id);
db.Employees.DeleteOnSubmit(toDelete);
db.SubmitChanges();
BindData();
lblMessage.Text = "Record deleted";
}
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();
}

var company = db.SpCompanyInformation();
//SqlCommand mySqlCommand = new SqlCommand("select Employee.Id, Employee.Name, Employee.Address, Employee.DateOfBirth, Employee.JoingDate, CompanyInformation.CompanyName from Employee left join CompanyInformation on Employee.CompanyId = CompanyInformation.Id", CN);
SqlDataAdapter mySqlAdapter = new SqlDataAdapter(mySqlCommand);
DataSet myDataSet = company;
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();
}
}
protected void GridView1_Sorting(object sender, GridViewSortEventArgs e)
{
bindGridView(e.SortExpression, sortOrder);
}
public string sortOrder
{
get
{
if (ViewState["sortOrder"].ToString() == "desc")
{
ViewState["sortOrder"] = "asc";
}
else
{
ViewState["sortOrder"] = "desc";
}

return ViewState["sortOrder"].ToString();
}
set
{
ViewState["sortOrder"] = value;
}
}

Linq Insert Update Delete

protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
if (EventID != 0)
{
lblCrateEvent.Text = "Modify an Event";
var events = DBGolfVolunteer.SpLoadEventByID(EventID);
foreach (var item in events)
{
txtEvenName.Text = item.fld_event_name;
txtStartDate.Text = Convert.ToDateTime(item.fld_startdate).ToShortDateString().ToString();
txtEndDate.Text = Convert.ToDateTime(item.fld_enddate).ToShortDateString().ToString();
txtTotalRound.Text = item.fld_roundcount.ToString();
txtMainName.Text = item.fld_contact_primary_name;
txtMainEmail.Text = item.fld_contact_primary_email;
txtMainPhone.Text = item.fld_contact_primary_telephone;
txtSecondName.Text = item.fld_second_contact_name;
txtSecondEmail.Text = item.fld_second_contact_email;
txtSecondPhone.Text = item.fld_second_contact_telephone;
txtThirdName.Text = item.fld_third_contact_name;
txtThirdEmail.Text = item.fld_third_contact_email;
txtThirdPhone.Text = item.fld_third_contact_telephone;
txtSummary.Text = item.fld_summary;
txtStartDate1.Text = Convert.ToDateTime(item.fld_display_startdate).ToShortDateString().ToString();
txtEndDate1.Text = Convert.ToDateTime(item.fld_display_enddate).ToShortDateString().ToString();
txtDescription.Text = item.fld_message;
}
btnSave.Text = "Accept Changes";

}
else
{
lblCrateEvent.Text = "Create an Event";
}
}
}
//Event Insert
protected void btnSave_Click(object sender, EventArgs e)
{

if (EventID == 0)
{
//lblCrateEvent.Text = "Create an Event";
tbl_event objtbl_event = new tbl_event();
objtbl_event.fld_event_name = txtEvenName.Text;
objtbl_event.fld_startdate = Convert.ToDateTime(txtStartDate.Text);
objtbl_event.fld_enddate = Convert.ToDateTime(txtEndDate.Text);
objtbl_event.fld_roundcount = int.Parse(txtTotalRound.Text);
objtbl_event.fld_contact_primary_name = txtMainName.Text;
objtbl_event.fld_contact_primary_email = txtMainEmail.Text;
objtbl_event.fld_contact_primary_telephone = txtMainPhone.Text;
objtbl_event.fld_second_contact_name = txtSecondName.Text;
objtbl_event.fld_second_contact_email = txtSecondEmail.Text;
objtbl_event.fld_second_contact_telephone = txtSecondPhone.Text;
objtbl_event.fld_third_contact_name = txtThirdName.Text;
objtbl_event.fld_third_contact_email = txtThirdEmail.Text;
objtbl_event.fld_third_contact_telephone = txtThirdPhone.Text;
objtbl_event.fld_status = 1;
objtbl_event.fld_homepage = 0;
objtbl_event.fld_displaynames = 0;
objtbl_event.fld_summary = txtSummary.Text;
objtbl_event.fld_message = txtDescription.Text;
objtbl_event.fld_display_startdate = Convert.ToDateTime(txtStartDate1.Text);
objtbl_event.fld_display_enddate = Convert.ToDateTime(txtEndDate1.Text);

DBGolfVolunteer.tbl_events.InsertOnSubmit(objtbl_event);
try
{
DBGolfVolunteer.SubmitChanges();
}
catch (Exception ex)
{
throw ex;
}
finally
{
divMessage.Visible = true;
divMessage.InnerHtml = "Event Inserted Successfully";
}

}

//Evetn update
else
{
tbl_event events = DBGolfVolunteer.tbl_events.Single(ev => ev.fld_id == Convert.ToInt32(EventID));
events.fld_event_name = txtEvenName.Text;
events.fld_startdate = Convert.ToDateTime(txtStartDate.Text);
events.fld_enddate = Convert.ToDateTime(txtEndDate.Text);
events.fld_roundcount = Convert.ToInt32(txtTotalRound.Text);
events.fld_contact_primary_name = txtMainName.Text;
events.fld_contact_primary_email = txtMainEmail.Text;
events.fld_contact_primary_telephone = txtMainPhone.Text;
events.fld_second_contact_name = txtSecondName.Text;
events.fld_second_contact_email = txtSecondEmail.Text;
events.fld_second_contact_telephone = txtSecondPhone.Text;
events.fld_third_contact_name = txtThirdName.Text;
events.fld_third_contact_email = txtThirdEmail.Text;
events.fld_third_contact_telephone = txtThirdPhone.Text;
events.fld_summary = txtSummary.Text;
events.fld_display_startdate = Convert.ToDateTime(txtStartDate1.Text);
events.fld_display_enddate = Convert.ToDateTime(txtEndDate1.Text);
events.fld_message = txtDescription.Text;
try
{
DBGolfVolunteer.SubmitChanges();
}
catch (Exception ex)
{
throw ex;
}
finally
{
divMessage.Visible = true;
divMessage.InnerHtml = "Event Updated Successfully";
}
}

clear();

}