C# save datatable into sqlserver using sqlhelper class

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

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:

C#

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

Posted 9-Oct-16 19:22pm

SnvMohan

Updated 10-Oct-16 3:16am

Add a Solution

Comments

Wendelius 10-Oct-16 1:25am

Using debugger verify that you don't simply call the C# method twice

SnvMohan 10-Oct-16 1:29am

I checked that, Methods are executed only one time

Wendelius 10-Oct-16 2:33am

How does method SqlHelper.UpdateDataset look like?

SnvMohan 10-Oct-16 2:35am

public static void UpdateDataset(SqlCommand insertCommand, SqlCommand deleteCommand, SqlCommand updateCommand, DataSet dataSet, string tableName)
{
//if (insertCommand == null) throw new ArgumentNullException("insertCommand");
//if (deleteCommand == null) throw new ArgumentNullException("deleteCommand");
//if (updateCommand == null) throw new ArgumentNullException("updateCommand");
if (tableName == null || tableName.Length == 0) throw new ArgumentNullException("tableName");

// Create a SqlDataAdapter, and dispose of it after we are done
using (SqlDataAdapter dataAdapter = new SqlDataAdapter())
{
// Set the data adapter commands
if (updateCommand != null) dataAdapter.UpdateCommand = updateCommand;
if (insertCommand != null) dataAdapter.InsertCommand = insertCommand;
if (deleteCommand != null) dataAdapter.DeleteCommand = deleteCommand;

// Update the dataset changes in the data source
dataAdapter.Update(dataSet, tableName);

// Commit all the changes made to the DataSet
dataSet.AcceptChanges();
}
}

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

#realJSOP · Answer 1 · 2016-10-10T00:11:00

Solution 1

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.

Posted 10-Oct-16 0:11am

#realJSOP

v2

Comments

SnvMohan 10-Oct-16 9:03am

In Debugging, When executing dataAdapter.Update(dataSet, tableName); line in UpdateDataset method in SQLHELPER class, dataset parameter contains only 3 rows, columns and values are properly assigning. After execute this line totally 9 rows added in SQL table.(Before execution, SQL table is empty). But through SqlCommand.ExecuteNonQuery(); its working properly

Wendelius · Answer 2 · 2016-10-10T03:16:00

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.

SnvMohan · Answer 3 · 2016-10-10T02:57:00

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# save datatable into sqlserver using sqlhelper class

3 solutions

Solution 1

Solution 3

Solution 2

Add your solution here

Preview 0