SqlDataSource Code Behind Event Order

Question

I have an SqlDataSource, a Gridview and a DropDownList on the same page. The DropDownList selection is associated with a set of SelectCommands, UpdateCommands, and DeleteCommands so that I can take advantage of the GridView AutoGenerateEditButton="true" and AutoGenerateUpdateButton="true" mechanism.

Page_Load
{
  switch(ddl.SelectedItem.Text)
  {
     case "A":
       sqlDS.SelectCommand = "Select * From A";
       sqlDS.UpdateCommand = "Update A Set Name = @Name WHERE ID = @ID";
       sqlDS.DeleteCommand = "Delete A WHERE ID = @ID";
       break;
     ...
  }

  sqlDS.DataBind();
  grd.DataSourceID = sqlDS.ID;
  grd.DataBind();
}

How or at what point do I need to add Parameters? Is it automatic? I basically just want the ability to update and delete columns from a table. I want to do all of this in the actual .cs file, as opposed to within the .aspx file as I'd like to make it more dynamic eventually; but for now I just want to get the basics down. I suspect that I may have the DataBind() logic in the inappropriate event because I don't fully understand the order of events associated with the data binding.

The queries are not complicated and involve no joins or views; they are simple SELECTs over single tables.

Answer 1

Edit: It does appear that if you use AutoGenerateColumns="true" on the GridView and populate via SqlDataSource, it will automatically bind the values of the controls by name to the appropriate parameters in the SQL query without any extra code. However, we have to use GetInsertCommand(true), etc. so that the commands use the column names (see code below where I show how to use SqlCommandBuilder. There are a few gotchas, however as I've discovered in testing:

• You need to set the DataKeyNames of your GridView
• You'll need to set OldValuesParameterFormatString="Original_{0}" on your sqlDS.
• You'll need scb.ConflictOption = System.Data.ConflictOption.OverwriteChanges; on your SqlCommandBuilder if you want to just update without comparing old values.
• It appears that if you are populating Select/Update/DeleteCommand on a SqlDataSource programmatically, you have to do it on every postback.

However, in case you need to customize, the SqlDataSource control provides the events Inserting, Updating, Deleting that you can use to populate the parameters before the SQL actions are taken on the database:

sqlDS.Updating += new SqlDataSourceCommandEventHandler(sqlDS_Updating);

protected void sqlDS_Updating(object sender, SqlDataSourceCommandEventArgs e)
{
    e.Command.Parameters["@Name"].Value = // retrieve value from user entry
}

The same kind of thing can be done in the Inserting and Deleting events via the e.Command.Parameters[...] access.

---

Note that you can also generate the appropriate Delete/Insert/Update command automatically using the SqlCommandBuilder class so that you don't have to build a giant switch statement containing all of your tables. Here's an example:

string tableName = ddl.SelectedValue;
string connectionString = ConfigurationManager
    .ConnectionStrings["MyConnectionString"].ConnectionString;
string select = "SELECT * FROM [" + tableName + "]";
SqlDataAdapter sda = new SqlDataAdapter(select, connection);
SqlCommandBuilder scb = new SqlCommandBuilder(sda);

sqlDS.SelectCommand = select;
sqlDS.InsertCommand = scb.GetInsertCommand(true).CommandText;
sqlDS.UpdateCommand = scb.GetUpdateCommand(true).CommandText;
sqlDS.DeleteCommand = scb.GetDeleteCommand(true).CommandText;

This will of course require that all of your tables have primary keys that can be used to generate the relevant update and delete statements. If not, you will get an exception about dynamic SQL generation. Even if you don't like this method because of the run-time cost of looking up the schema on the database engine, you could always pre-generate them all with a T4 template instead of typing them all in by hand.