I am currently studying as web developer, and are learning ASP.NET WEBFORMS. I have a question regarding DataAdapter and Updating/Deleting a table.
I wanna know which is the right way to do this. Lets say I have this method.
The Update Method
public void UpdateDataTable()
{
SqlConnection conn = new SqlConnection(strcon);
SqlDataAdapter da = null;
DataSet ds = null;
DataTable dt = null;
string sqlsel = "SELECT ActId, Title FROM Act WHERE ArtistId = " + Session["UserId"];
try
{
da = new SqlDataAdapter();
da.SelectCommand = new SqlCommand(sqlsel, conn);
ds = new DataSet();
da.Fill(ds, "MyTable");
dt = ds.Tables["MyTable"];
Gridview1.DataSource = dt;
Gridview1.DataBind();
}
catch (Exception ex)
{
LabelMessage.Text = ex.Message;
}
finally
{
conn.Close();
}
}
I call this method at when the pages load in a (!Page.IsPostBack). So my question is, since a DataSet saves it all in memory (DataTable too). And I want to Update one row with the DataAdapter object again, which method is the best to use? In a click event.
Method 1
protected void ButtonUpdate_Click1(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection(strcon);
SqlDataAdapter da = null;
string sqlupd = "UPDATE [Act] SET [Title] = @Title, [Description] = @Description, [Duration] = @Duration WHERE [ActId] = @ActId";
try
{
conn.Open();
da = new SqlDataAdapter();
da.UpdateCommand = new SqlCommand(sqlupd, conn);
da.UpdateCommand.Parameters.AddWithValue("@Title", TextBoxTitle.Text);
da.UpdateCommand.Parameters.AddWithValue("@Description", TextBoxText.Text);
da.UpdateCommand.Parameters.AddWithValue("@Duration", TextBoxDuration.Text);
da.UpdateCommand.Parameters.AddWithValue("@ActId", LabelID.Text);
da.UpdateCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
LabelMessage.Text = "" + ex.Message;
}
finally
{
conn.Close();
}
// Call the Update Method
UpdateDataTable();
}
Or is it better to populate all again, and put it in the DataSet -> DataTable? Like this.
Method 2
protected void ButtonUpdate_Click1(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection(strcon);
SqlDataAdapter da = null;
DataSet ds = null;
DataTable dt = null;
SqlCommand cmd = null;
string sqlsel = "SELECT * FROM Act WHERE ArtistId = " + Session["UserId"];
string sqlupd = "UPDATE [Act] SET [Title] = @Title, [Description] = @Description, [Duration] = @Duration WHERE [ActId] = @ActId";
try
{
da = new SqlDataAdapter();
da = new SqlDataAdapter();
da.SelectCommand = new SqlCommand(sqlsel, conn);
ds = new DataSet();
da.Fill(ds, "MyTable");
dt = ds.Tables["MyTable"];
dt.Rows[Gridview1.SelectedIndex]["Title"] = TextBoxTitle.Text;
dt.Rows[Gridview1.SelectedIndex]["Description"] = TextBoxText.Text;
dt.Rows[Gridview1.SelectedIndex]["Duration"] = TextBoxDuration.Text;
// UPDATE
cmd = new SqlCommand(sqlupd, conn);
cmd.Parameters.Add("@Title", SqlDbType.NVarChar, 50, "Title");
cmd.Parameters.Add("@Description", SqlDbType.Text, 250, "Description");
cmd.Parameters.Add("@Duration", SqlDbType.Int, 4, "Duration");
SqlParameter parm = cmd.Parameters.Add("@ActId", SqlDbType.Int, 4, "ActId");
parm.SourceVersion = DataRowVersion.Original;
da.UpdateCommand = cmd;
da.Update(ds, "MyAct");
}
catch (Exception ex)
{
LabelMessage.Text = "" + ex.Message;
}
finally
{
conn.Close();
}
UpdateDataTable();
;
}
So which method is the best to use? And why? :)
