0

I have multiple set of data to insert at once, say 10 rows.

My table has three columns: PlanId,MomEntryId and CreatedBy

Can I insert all 10 rows in a single SQL statement?

This is my Store procedure. How can I pass 10 rows values in SP. I need to validate that PlanId and MomEntryId too that it's value is greater than zero or not.

CREATE PROCEDURE SP_Document_Plan_Or_MomEntry_Insert
@PlanId int = null,
@MomEntryId int = null,
@CreatedBy varchar(20)  
AS
BEGIN
    if(@PlanId > 0)
    begin
        Insert into t_Document(PlanId,CreatedBy,CreatedDate)
        values
        (@PlanId,@CreatedBy,GETDATE())
    end
    else if (@MomEntryId>0)
    begin
        Insert into t_Document([MomEntryId],CreatedBy,CreatedDate)
        values
        (@MomEntryId,@CreatedBy,GETDATE())
    end
END
GO

Below is my C# code for passing parameters.

  cblCheckList.Items.Cast<ListItem>().ToList().ForEach(d =>
                    {
                        if(d.Selected)
                        {
                            momEntry.AddDocumentDetailForMomEntry(Convert.ToInt32(d.Value), Session["userId"].ToString());
                        }
                    });

and this is for Datalayer code.I use Enterprise library file for creating DbHelper class.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;

/// <summary>
/// Summary description for MOMGateway
/// </summary>
public class MOMGateway
{
    private MOMGateway()
    {
        //
        // TODO: Add constructor logic here
        //
    }

   ///
   /// This method perform insert operation in Document table
   ///
    public static int AddDocumentEntryforMomEntry(int momEntryId,string createdBy)
    {
        int i = Convert.ToInt32(DBHelper.GetDatabase().ExecuteScalar("SP_Document_Plan_Or_MomEntry_Insert",
                      null,momEntryId,createdBy));
        return i;
    }



}
3
  • Have you considered SqlBulkCopy? Commented May 7, 2013 at 6:29
  • @Sruti: Yes but same time need to perform validation too. Commented May 7, 2013 at 6:30
  • Can you not perform the validation on the C# side, ease of unit testing comes for free :) Commented May 7, 2013 at 11:54

1 Answer 1

1

This is my Store procedure. How can I pass 10 rows values in SP

Use User Defined Table Type for this purpose. You can pass the DataTable or your collection of object from C# code as parameter to your defined table type.

See: Using Table-Valued Parameters in SQL Server 2008 and C#

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

7 Comments

How I will validate the PlanId and MomEntryId in my SP
@SaroopTrivedi, see this article,
I need to validate that If PlanId > 0 then only PlanId value insert perform other wise MomEntryId. Both entry not perform on same page
@SaroopTrivedi if you follow the link , there is store proc in it. you may validate things there.
@Habib , Can we have this adapter other than WCF .
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.