Insert multiple rows with parameters Sql Server

Question

I am trying to figure out if there is a way to perform a multiple values insert in Sql Server while using parameters, to be precise, having a command like this:

com = new SqlCommand("insert into myTable values (@recID,@tagID)", con);
com.Parameters.Add("@recID", SqlDbType.Int).Value = recID;
com.Parameters.Add("@tagID", SqlDbType.Int).Value = tagID;
com.ExecuteNonQuery();

Is there a way to perform a multiple values single insert with parameters taking into account that parameters may be different for each value? (Example: tagID may be always different)

I have been searching in Internet but no luck so far, thanks in advance, greetings.

You can use a table valued parameters : stackoverflow.com/questions/5595353/… — Rom Eh
– Rom Eh, Commented Jul 22, 2016 at 10:05

Community · Accepted Answer · 2017-05-23 12:08:07Z

8

You can use a table valued parameters : How to pass table value parameters to stored procedure from .net code

First, create the type, in SQL Server :

CREATE TYPE [dbo].[myTvpType] AS TABLE 
(
    [RecordID] int,
    [TagID] int
)

And the C# code to insert your data :

internal void InsertData(SqlConnection connection, Dictionary<int, int> valuesToInsert)
{
    using (DataTable myTvpTable = CreateDataTable(valuesToInsert))
    using (SqlCommand cmd = connection.CreateCommand())
    {
        cmd.CommandText = "INSERT INTO myTable SELECT RecordID, TagID FROM @myValues";
        cmd.CommandType = CommandType.Text;

        SqlParameter parameter = cmd.Parameters.AddWithValue("@myValues", myTvpTable);
        parameter.SqlDbType = SqlDbType.Structured;

        cmd.ExecuteNonQuery();
    }
}

private DataTable CreateDataTable(Dictionary<int, int> valuesToInsert)
{
    // Initialize the DataTable
    DataTable myTvpTable = new DataTable();
    myTvpTable.Columns.Add("RecordID", typeof(int));
    myTvpTable.Columns.Add("TagID", typeof(int));

    // Populate DataTable with data
    foreach(key in valuesToInsert.Key)
    {
        DataRow row = myTvpTable.NewRow();
        row["RecordID"] = valuesToInsert[key];
        row["TagID"] = key;
    }
}

edited May 23, 2017 at 12:08

CommunityBot

11 silver badge

answered Jul 22, 2016 at 10:21

Rom Eh

2,0711 gold badge17 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

JCO9 Over a year ago

Great! Thank you for the tip, this solves the problem.

Michael B. Over a year ago

Don't forget to add the new row to the table at the end of your loop. myTvpTable.Rows.Add(row);

Pranav Mishra · Accepted Answer · 2017-05-24 09:42:49Z

You can do this by sending your data as an xml string and convert in into table in a stored procedure in sql. For example: suppose I am sending multiple rows to add/update in an sql table then here are the steps:

Convert your class or list of class into an xml string using following method:

public static string SerializeObjectToXmlString(object value)

          {
          var emptyNamepsaces = new XmlSerializerNamespaces(new[] { 
                                    XmlQualifiedName.Empty });

    var serializer = new XmlSerializer(value.GetType());
    var settings = new XmlWriterSettings();
    settings.Indent = true;
    settings.OmitXmlDeclaration = true;

    using (var stream = new StringWriter())
    using (var writer = XmlWriter.Create(stream, settings))
    {
        serializer.Serialize(writer, value, emptyNamepsaces);
        return stream.ToString();
    }
}

Now while sending data to the database convert your class object into xml string (Here I am using entity framework in my code, you can do this without using it as well):

bool AddUpdateData(List<MyClass> data)
{
    bool returnResult = false;
    string datatXml = Helper.SerializeObjectToXmlString(data);
    var sqlparam = new List<SqlParameter>()
                 {
   new SqlParameter() { ParameterName = "dataXml", Value = datatXml}

                 };
    var result = this.myEntity.Repository<SQL_StoredProc_ComplexType>().ExecuteStoredProc("SQL_StoredProc", sqlparam);
    if (result != null && result.Count() > 0)
    {
        returnResult = result[0].Status == 1 ? true : false;
    }
    return returnResult;
}

Now your SQL Code:

3.1 Declare a table variable:

DECLARE @tableVariableName TABLE
(
    ID INT, Name VARCHAR(20)
)

3.2 Insert Your xml string into Table variable

INSERT INTO @tableVariableName
SELECT 
    Finaldata.R.value ('(ID/text())[1]', 'INT') AS ID, 
    Finaldata.R.value ('(Name/text())[1]', 'VARCHAR(20)') AS Name
FROM @MyInputXmlString.nodes ('//ArrayMyClass/MyClass') AS Finaldata (R)

3.3 Finally insert this table value into your sql table

INSERT INTO MyTable (ID, Name)                  
SELECT ID, Name          
FROM @tableVariableName

This will save your effort of hitting database again and again using a for loop.

Hope it will help you

Whilst this may theoretically answer the question, it would be preferable to include the essential parts of the answer here, and provide the link for reference.

Collectives™ on Stack Overflow

Insert multiple rows with parameters Sql Server

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related