C＃插入到SQL表中列出的参数 [英] C# Insert into SQL Table with List as parameter

问题描述

通过专家真诚的帮助在这个美好的论坛上，我已经能够解析由SharePoint列表回到我的XML来获得所需的列表项为C＃列表。

With sincere help from experts in this wonderful forum, I have been able to parsed my xml returned by a SharePoint list to get the desired list items into C# Lists.

XDocument xdoc = XDocument.Parse(activeItemData.InnerXml);
XNamespace z = "#RowsetSchema";

List<int> listID = (from row in xdoc.Descendants(z + "row") 
select (int)row.Attribute("ows_ID")).ToList();

List<string> listTitle = (from row in xdoc.Descendants(z + "row") 
select (string)row.Attribute("ows_LinkTitle")).ToList();

我创建了一个SQL表，我想插入使用列表listID和listTitle作为参数在我的表值

I have created a SQL table and I want to insert values in my table using Lists listID and listTitle as parameters

System.Data.SqlClient.SqlConnection sqlConnection1 =
            new System.Data.SqlClient.SqlConnection(MyconnectionString);

            System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
            cmd.CommandType = System.Data.CommandType.Text;
            cmd.CommandText = "INSERT TechOpsProjectTracker (ID, [Project Name]) VALUES (@ID, @ProjectName)";

//What I want to do:
            //1. @ID should get the values from the List listID: List<int> listID = (from row in xdoc.Descendants(z + "row") select (int)row.Attribute("ows_ID")).ToList();
            //2. @ProjectName should get the values from the List listTitle: List<string> listTitle = (from row in xdoc.Descendants(z + "row") select (string)row.Attribute("ows_LinkTitle")).ToList();
            //3. so each new record inserted in the table has ID and its corresponding Project Name. Something like this
            /* ID    Project Name
               360   GEI Survey data to Sharepoint
               378   Create back end and environment to support User demographic reporting


             */

可能有一些其他的可能是更容易的方式来完成我的工作。请告诉我。 TIA。

There might be some other possibly easier ways to accomplish my job. Please let me know. TIA.

推荐答案

设置一个用户定义类型相似。

Setup a user defined type similar to.

CREATE TYPE [dbo].[tableOf_Ints] AS TABLE(
    [ID] [int] NULL
)
GO

然后你可以使用它是这样的。

Then you can can use it like this.

public static SqlCommand CreateCommand(List<int> ints)
{
    var dt = new DataTable();
    dt.Columns.Add("ID",typeof(Int32));
    for (int i = 0; i < ints.Count; i++)
    {
        dt.Rows.Add(ints[i]);
    }

    SqlCommand cmd = new SqlCommand("SomeStoredProc");
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandTimeout = 120;
    var param1 = cmd.Parameters.AddWithValue("@SomeParam", dt);
    param1.SqlDbType = SqlDbType.Structured;
    param1.TypeName = "dbo.tableOf_Ints";

    return cmd;
}

假设你有一个这样的存储过程。

Assuming you have a stored proc like this.

CREATE PROCEDURE [dbo].[SomeStoredProc]  
    @SomeParam TableOf_Ints READONLY
AS
BEGIN
END

这篇关于C＃插入到SQL表中列出的参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！