SqlDataSource的code事件顺序的背后

问题描述

我有一个SqlDataSource的，一个GridView和在同一页上一个DropDownList。 DropDownList的选择是通过一组 SelectCommands ， UpdateCommands 的关联，DeleteCommands这样我可以在GridView的优势AutoGenerateEditButton属性=true和AutoGenerateUpdateButton =真的机制。

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

如何或在什么时候，我需要添加参数？它是全自动的吗？基本上，我只是想更新和删除表中的列的能力。我想要做的这一切在实际cs文件，如.aspx文件中的反对，因为我想，使其更加动感最终;但现在我只是想获得基本下跌。我怀疑我可能调用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.

推荐答案

修改：这确实出现，如果你在GridView中使用的AutoGenerateColumns =true和填充通过SqlDataSource的，它会自动绑定该控件的值按名称在SQL查询中相应的参数没有任何额外的code。但是，我们必须使用 GetInsertCommand（真）等使该命令使用的列名（见下面，我展示了如何使用code <$ C 。$ C> SqlCommandBuilder 有几个陷阱，但我在测试中所发现的：

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:

• 您需要设置的DataKeyNames 你的GridView的


• 您需要设置 OldValues​​ParameterFormatString =Original_ {0}您sqlDS。


• 您将需要 scb.ConflictOption = System.Data.ConflictOption.OverwriteChanges; 在 SqlCommandBuilder 如果您想不比较旧的值只更新。


• 看样子，如果你在一个SqlDataSource编程填充选择/更新/ DeleteCommand会，你要做的是在每个回发。

• 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.

然而，如果你需要定制，在的SqlDataSource 控件提供的事件插入，更新，删除，你可以用它来填充参数所采取的数据库之前，SQL动作：

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
}

同样的事情可以在插入和删除事件通过 e.Command.Parameters [...] 访问。

请注意，您也可以生成相应的删除/插入/更新命令会自动使用 SqlCommandBuilder 类，这样你就不必建立一个包含所有一个巨大的switch语句你的表。这里有一个例子：

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;

当然，这需要所有的表都可以用来生成相关的更新和delete语句的主键。如果没有，你会得到有关动态SQL生成异常。即使你不喜欢，因为对数据库引擎中查找的模式运行时的成本这种方法，你总是可以美元，T4模板p $ P-生成它们的所有而不是手动输入这一切。

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.

这篇关于SqlDataSource的code事件顺序的背后的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！