SQL 数据适配器插入命令

发布于 2024-10-28 06:49:46 字数 1105 浏览 6 评论 0原文

以下代码似乎有问题我想使用以下代码片段在数据库中插入一个值。

DataTable dtUsers = new DataTable("tblUsers");
BindingSource bsUsers = new BindingSource();
SqlDataAdapter daUsers = new SqlDataAdapter("usp_GetUsers", Properties.Resources.ConnectionString);


     daUsers.InsertCommand = new SqlCommand("usp_InsertNewUser");
                daUsers.InsertCommand.Connection = new SqlConnection(Properties.Resources.ConnectionString);
                daUsers.InsertCommand.CommandType = CommandType.StoredProcedure;
                daUsers.InsertCommand.Parameters.Clear();
                daUsers.InsertCommand.Parameters.Add("@username", SqlDbType.VarChar, 50).Value = txtUser.Text;
                daUsers.InsertCommand.Parameters.Add("@password", SqlDbType.VarChar, 50).Value = txtPass.Text;
                daUsers.InsertCommand.Parameters.Add("@userType", SqlDbType.Int).Value = cbxUserType.SelectedValue;
                daUsers.Update(dtUsers);

在您询问之前,存储过程运行良好。而且,如果我将上面的 InsertCommand 更改为 SelectCommand 并调用 Fill 方法,那么一切都会正常!这件事令人沮丧,因为如果您使用选择/填充组合,数据适配器的插入/更新方法无法正常工作。我也监控到没有插入数据行。也不会产生异常。 铝

There seems to be a problem with the following code I would like to insert a value in the database using the following code snippet.

DataTable dtUsers = new DataTable("tblUsers");
BindingSource bsUsers = new BindingSource();
SqlDataAdapter daUsers = new SqlDataAdapter("usp_GetUsers", Properties.Resources.ConnectionString);


     daUsers.InsertCommand = new SqlCommand("usp_InsertNewUser");
                daUsers.InsertCommand.Connection = new SqlConnection(Properties.Resources.ConnectionString);
                daUsers.InsertCommand.CommandType = CommandType.StoredProcedure;
                daUsers.InsertCommand.Parameters.Clear();
                daUsers.InsertCommand.Parameters.Add("@username", SqlDbType.VarChar, 50).Value = txtUser.Text;
                daUsers.InsertCommand.Parameters.Add("@password", SqlDbType.VarChar, 50).Value = txtPass.Text;
                daUsers.InsertCommand.Parameters.Add("@userType", SqlDbType.Int).Value = cbxUserType.SelectedValue;
                daUsers.Update(dtUsers);

And before you ask, the stored procedure is working fine. And also if I change the InsertCommand above to SelectCommand and call the Fill method, then everything works fine! This thing is frustrating as the Insert/update methods of the data adapter is not working as it should if you are using Select/Fill combo. No data rows are being inserted as I monitored that as well. Also no exceptions are generated.
Al

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

离鸿 2024-11-04 06:49:46

使用该确切的代码片段,不会插入任何内容 - SqlDataAdapter.Update() 的工作方式是对于所提供的数据表 (dtUsers) 中具有已添加 RowState 的每个 DataRow,它将调用已在数据适配器上定义的 InsertCommand 。同样,对于每个 RowState 为 Modified 的 DataRow,它将调用已定义的 UpdateCommand(如果有)。

SqlDataAdapter.Update() 的目的是提供一个 DataTable(可能包含新的、更新的和删除的行),它将为其调用适当的已定义存储过程。

因此,在您的代码中,dtUsers 中没有任何 RowState 为“已添加”的内容,因此没有任何工作可以发送到数据库......因此不会插入任何内容。

With that exact code snippet, nothing will be inserted - the way SqlDataAdapter.Update() works is for every DataRow in the supplied datatable (dtUsers) that has a RowState of Added, it will call the InsertCommand that has been defined on the data adapter. Likewise, for every DataRow with a RowState of Modified, it will call the UpdateCommand that has been defined (if there is one).

The purpose of SqlDataAdapter.Update(), is to be supplied with a DataTable (potentially containing new, updated and deleted rows) which it will call the appropriate defined sproc for.

So in your code, you have nothing in dtUsers with a RowState of Added and so there is no work to send to the DB....hence nothing gets inserted.

走过海棠暮 2024-11-04 06:49:46

在设置参数后,您可以使用 adapter.InsertCommand.ExecuteNonQuery() 来提交数据库中的插入操作。

例如:

DataTable dtUsers = new DataTable("tblUsers");
BindingSource bsUsers = new BindingSource();
SqlDataAdapter daUsers = new SqlDataAdapter("usp_GetUsers",Properties.Resources.ConnectionString);
daUsers.InsertCommand = new SqlCommand("usp_InsertNewUser");
daUsers.InsertCommand.Connection = new SqlConnection(Properties.Resources.ConnectionString);
daUsers.InsertCommand.CommandType = CommandType.StoredProcedure;
daUsers.InsertCommand.Parameters.Clear();
daUsers.InsertCommand.Parameters.Add("@username", SqlDbType.VarChar, 50).Value = txtUser.Text;
daUsers.InsertCommand.Parameters.Add("@password", SqlDbType.VarChar, 50).Value = txtPass.Text;
daUsers.InsertCommand.Parameters.Add("@userType", SqlDbType.Int).Value = cbxUserType.SelectedValue;
daUsers.InsertCommand.ExecuteNonQuery();
daUsers.Update(dtUsers);

You may use adapter.InsertCommand.ExecuteNonQuery() after setting the params to commit inserting in database.

For example:

DataTable dtUsers = new DataTable("tblUsers");
BindingSource bsUsers = new BindingSource();
SqlDataAdapter daUsers = new SqlDataAdapter("usp_GetUsers",Properties.Resources.ConnectionString);
daUsers.InsertCommand = new SqlCommand("usp_InsertNewUser");
daUsers.InsertCommand.Connection = new SqlConnection(Properties.Resources.ConnectionString);
daUsers.InsertCommand.CommandType = CommandType.StoredProcedure;
daUsers.InsertCommand.Parameters.Clear();
daUsers.InsertCommand.Parameters.Add("@username", SqlDbType.VarChar, 50).Value = txtUser.Text;
daUsers.InsertCommand.Parameters.Add("@password", SqlDbType.VarChar, 50).Value = txtPass.Text;
daUsers.InsertCommand.Parameters.Add("@userType", SqlDbType.Int).Value = cbxUserType.SelectedValue;
daUsers.InsertCommand.ExecuteNonQuery();
daUsers.Update(dtUsers);
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文