This is something I found that is really cool. I Always like to find the coding to do things and I found one for the GridView.
i use the web.config for my connection and call it just below. Im a newbie and dont have too much experience but this teaches me alot.
static string theConnectionString = ConfigurationManager.ConnectionStrings["MyConnectionStringSql"].ToString();
protected void Page_Load(object sender, EventArgs e)
{
BindGridView();
}
private void BindGridView()
{
SqlConnection m_objConnection = new SqlConnection(theConnectionString);
m_objConnection.Open();
SqlCommand sqlCommand = new SqlCommand("select * from Customers", m_objConnection);
SqlDataReader reader = sqlCommand.ExecuteReader();
GridView1.DataSource = reader;
GridView1.DataBind();
}
private void UpdateOrAddNewRecord(string ID, string Company, string Name, string Title, string Address, string Country, bool isUpdate)
{
SqlConnection connection = new SqlConnection(theConnectionString);
string sqlStatement = string.Empty;
if (!isUpdate)
{
sqlStatement = "INSERT INTO Customers"+
"(CustomerID,CompanyName,ContactName,ContactTitle,Address,Country)" +
"VALUES (@CustomerID,@CompanyName,@ContactName,@ContactTitle,@Address,@Country)";
}
else
{
sqlStatement = "UPDATE Customers" +
"SET CompanyName = @CompanyName,ContactName = @ContactName," +
"ContactTitle = @ContactTitle,Address = @Address,Country = @Country" +
"WHERE CustomerID = @CustomerID,";
}
try
{
connection.Open();
SqlCommand cmd = new SqlCommand(sqlStatement, connection);
cmd.Parameters.AddWithValue("@CustomerID", ID);
cmd.Parameters.AddWithValue("@CompanyName", Company);
cmd.Parameters.AddWithValue("@ContactName", Name);
cmd.Parameters.AddWithValue("@ContactTitle", Title);
cmd.Parameters.AddWithValue("@Address", Address);
cmd.Parameters.AddWithValue("@Country", Country);
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
}
catch (System.Data.SqlClient.SqlException ex)
{
string msg = "Insert/Update Error:";
msg += ex.Message;
throw new Exception(msg);
}
finally
{
connection.Close();
}
}
protected void Button1_Click(object sender, EventArgs e)
{
UpdateOrAddNewRecord(TextBox1.Text, TextBox2.Text, TextBox3.Text, TextBox4.Text, TextBox5.Text, TextBox6.Text, false);
//Re Bind GridView to reflect changes made
BindGridView();
}
protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
{
GridView1.EditIndex = e.NewEditIndex; // turn to edit mode
BindGridView(); // Rebind GridView to show the data in edit mode
}
protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
GridView1.EditIndex = -1;
BindGridView();
}
protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
string id = GridView1.Rows[e.RowIndex].Cells[0].Text; //ID
string company = ((TextBox)GridView1.Rows[e.RowIndex].Cells[1].Controls[0]).Text; //Company
string name = ((TextBox)GridView1.Rows[e.RowIndex].Cells[2].Controls[0]).Text; //Name
string title = ((TextBox)GridView1.Rows[e.RowIndex].Cells[3].Controls[0]).Text; //Title
string address = ((TextBox)GridView1.Rows[e.RowIndex].Cells[4].Controls[0]).Text; //Address
string country = ((TextBox)GridView1.Rows[e.RowIndex].Cells[5].Controls[0]).Text; //Country
UpdateOrAddNewRecord(id, company, name, title, address, country, true); // call update method
GridView1.EditIndex = -1;
BindGridView(); // Rebind GridView to reflect changes made
}
private void DeleteRecord(string ID)
{
SqlConnection connection = new SqlConnection(theConnectionString);
string sqlStatement = "DELETE FROM Customers WHERE CustomerID = @CustomerID";
try
{
connection.Open();
SqlCommand cmd = new SqlCommand(sqlStatement, connection);
cmd.Parameters.AddWithValue("@CustomerID", ID);
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
}
catch (System.Data.SqlClient.SqlException ex)
{
string msg = "Deletion Error:";
msg += ex.Message;
throw new Exception(msg);
}
finally
{
connection.Close();
}
}
protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
string id = GridView1.Rows[e.RowIndex].Cells[0].Text;// get the id of the selected row
DeleteRecord(id);//call delete method
BindGridView();//rebind grid to reflect changes made
}