C＃使用sqlhelper类将数据表保存到sqlserver中 [英] C# save datatable into sqlserver using sqlhelper class

问题描述

我尝试将dataTable保存到SQL服务器中。我可以保存记录，但我尝试只保存三条记录但是SQL表上的记录重复（3次）。我不知道为什么它会重复。有人请尽快帮我解决这个问题



我尝试了什么：

I try save dataTable into SQL server. I can able to save records, but i try to save only three records But the records get duplicated(3 times) on SQL Table. I don't know why its get duplicated. Someone please help me to solve this issue ASAP

What I have tried:

Here is My code :

C#:
            DataTable InfoTB = new DataTable("infotb");
            DataColumn DC_Name = new DataColumn("name", typeof(string));
            DataColumn DC_Age = new DataColumn("age", typeof(string));
            DataColumn DC_Details = new DataColumn("details", typeof(string));    
            InfoTB.Columns.Add(DC_Name);
            InfoTB.Columns.Add(DC_Age);
            InfoTB.Columns.Add(DC_Details);
           
            InfoTB.Rows.Add("Mano", "27", "Programmer");
            InfoTB.Rows.Add("Jhon", "50", "Analyst");
            InfoTB.Rows.Add("Diwani", "26", "Tester");

            DataSet ds = new DataSet();
            ds.Tables.Add(InfoTB);          

            SqlConnection sqlConn = new SqlConnection(localconstr);
            SqlCommand ins_cmd = new SqlCommand("Insert_Info", sqlConn);
            ins_cmd.CommandType = CommandType.StoredProcedure;
            ins_cmd.Parameters.Add("@tblinfo", ds.Tables["infotb"]);

            SqlHelper.UpdateDataset(ins_cmd, null, null, ds, "infotb");


Stored Procedure :
=================
CREATE PROCEDURE [dbo].[Insert_Info]

@tblinfo infoType READONLY

as
Begin     
      insert into infotb (name,age,details)
      SELECT name,age,details FROM @tblinfo

End


UserDefinedTableType
=====================
CREATE TYPE [dbo].[infoType] AS TABLE(
	[name] [varchar](50) NULL,
	[age] [varchar](10) NULL,
	[details] [varchar](250) NULL
)
GO


Table Script:
============

CREATE TABLE [dbo].[infotb](
	[ID] [int] IDENTITY(1000,1) NOT NULL,
	[Name] [varchar](50) NULL,
	[Age] [varchar](50) NULL,
	[Details] [varchar](150) NULL,
	[RID] [uniqueidentifier] ROWGUIDCOL  NOT NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[infotb] ADD  CONSTRAINT [DF_infotb_RID]  DEFAULT (newid()) FOR [RID]
GO

推荐答案

记录是重复的，因为您的Update方法不处理Where子句，或者因为它将错误的列与传入的数据进行比较。使用调试器并逐步执行代码。您可能会立即看到问题所在。

The records are duplicated because your Update method either isn't processing a Where clause, or because it's comparing the wrong columns with the incoming data. Use your debugger and step through the code. You'll probably immediately see where your problem is.

原因是您在更新命令中调用存储过程传递所有行以添加并使用数据适配器。



换句话说，如果您的数据表包含3行，则数据适配器会调用该过程3次，每行一次。但是，您将数据表中的所有数据传递给过程，因此每个执行都会添加所有行。



这意味着适配器调用就像这样

- 第一个datarow调用1：添加3行

- 第二个datarow调用2：添加3行

- 第三个数据路由调用3：添加3行

结果是添加了9行。

The reason is that you call a stored procedure in your update command passing all rows to add and you use data adapter.

In other words, if your data table contains 3 rows the data adapter calls the procedure 3 times, one time for each row. However, you pass all the data from the data table to the procedure so each one of the executions adds all the rows.

This means that the adapter calls go like this
- Call 1 for first datarow: 3 rows added
- Call 2 for second datarow: 3 rows added
- Call 3 for third datarow: 3 rows added
And the result is 9 rows added.

现在我只是评论这一行SqlHelper.UpdateDataset（ins_cmd，null，null，ds，infotb）;并添加以下编码，（sqlConn.Open（）; ins_cmd.ExecuteNonQuery（）; sqlConn.Close（）;）现在它正常工作。但是我仍然无法找到使用SqlHelper.UpdateDataset方法时记录重复的原因。


C＃：

DataSet ds = new DataSet（） ;

ds.Tables.Add（InfoTB）;



SqlConnection sqlConn = new SqlConnection（localconstr）;

SqlCommand ins_cmd = new SqlCommand（Insert_Info，sqlConn）;

ins_cmd.CommandType = CommandType.StoredProcedure;

ins_cmd.Parameters.Add（@ tblinfo，ds.Tables [infotb]）;



//SqlHelper.UpdateDataset(ins_cmd，null，null，ds，infotb）;



sqlConn.Open（）;

ins_cmd.ExecuteNonQuery（）;

sqlConn.Close（）;

Now i am just comment this line SqlHelper.UpdateDataset(ins_cmd, null, null, ds, "infotb"); and Add following codings,(sqlConn.Open(); ins_cmd.ExecuteNonQuery(); sqlConn.Close();) Now its working properly. But stil i cant find out why records get duplicated while using SqlHelper.UpdateDataset method.

C#:
DataSet ds = new DataSet();
ds.Tables.Add(InfoTB);

SqlConnection sqlConn = new SqlConnection(localconstr);
SqlCommand ins_cmd = new SqlCommand("Insert_Info", sqlConn);
ins_cmd.CommandType = CommandType.StoredProcedure;
ins_cmd.Parameters.Add("@tblinfo", ds.Tables["infotb"]);

//SqlHelper.UpdateDataset(ins_cmd, null, null, ds, "infotb");

sqlConn.Open();
ins_cmd.ExecuteNonQuery();
sqlConn.Close();

这篇关于C＃使用sqlhelper类将数据表保存到sqlserver中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！