C# DataTable into SQL Server stored procedure

问题:

I have a DataTable in C#, and I'm trying to pass it to SQL Server.

What I have is:

DataTable asd = new DataTable();

for (int i = 0; i < LocationDataList.Count; i++)
{
    asd.Columns.Add(LocationDataList[i], typeof(string));
}

command.Parameters.Add("@PLocationDataList", SqlDbType.NVarChar, -1).Value = asd;

command.ExecuteNonQuery();

Here is the procedure T-SQL;

ALTER PROCEDURE [Sade].[SPLocationInsert]
     @... INT
     ,@... VARCHAR(64)
     ,@... VARCHAR(MAX)
     ,@... INT
     ,@... INT
     ,@... INT
     ,@... INT
     ,@... INT
     ,@PLocationDataList NVARCHAR(MAX)
AS

I have no idea how to pass the DataTable and which data type I should use.

Thanks in advance

回答1:

Create a user defined table type in SQL Server:

CREATE TYPE LocationTableType AS TABLE 
( LocationName VARCHAR(50));
GO

And pass this table valued parameter to the stored procedure :

CREATE PROCEDURE [Sade].[SPLocationInsert]
 @... INT
,@... VARCHAR(64)
,@... VARCHAR(MAX)
,@... INT
,@... INT
,@... INT
,@... INT
,@... INT
,@PLocationDataList LocationTableType READONLY
AS 
    SET NOCOUNT ON
    -- insert your code here
GO

References:

User defined table types : https://technet.microsoft.com/en-us/library/bb522526(v=sql.105).aspx

Table valued parameters: https://technet.microsoft.com/en-us/library/bb510489(v=sql.105).aspx

回答2:

You are trying to pass a datatable which is a structured type and so it has to be of type SqlDbType.Structured

command.Parameters.Add("@PLocationDataList", SqlDbType.Structured, -1).Value = asd;

Also, you will have to create a table type variable in SQL Server for the same

CREATE TYPE dbo.LocationDataList AS TABLE
    ( col1 nvarchar(50) )

Check Table-Valued Parameters

回答3:

If you want to set the type of a column check this page: https://msdn.microsoft.com/en-us/library/hfx3s9wd(v=vs.110).aspx

You might want to take a look at SQLBulkCopy (https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy(v=vs.110).aspx). It accepts per default datatables.

---

There is a extension available when you work with objects that it can be easily converted, for that see https://www.nuget.org/packages/FastMember/.

回答4:

if you need multiple string values together, you can, for example, use a comma separated string, and then in your stored procedure, you can split this comma separated string to a table

for example, you pass aaa,bbb,ccc to you stored procedure, and then split it to a table

回答5:

1.First convert datatable into XMl like below

DataSet ds = new DataSet();
ds.Tables.Add(dt1); // dataTable 
string dsXml= ds.GetXml();

2.Then pass this XML string as a one parameter to Stored procedure.

3.In SQL , MYSQL XML datatype is ther to define . By using that you can execute stored procedure.

For reference see this link