Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
  • Passing a DataTable Parameter to Stored Procedure from C#

    • 0
    • 2
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 716
    Comment on it

    Some time we need to pass a tabular form data to our database. one option is to using loop in Datatable and on every iteration we have to make a Database call. But this approach is not too good and also it's a time taking and decrease our application performance.

    We can achieve this through stored procedure by passing a Table type parameter.

    Firstly we need to create a User-Defined Table Type in our SQL database. As :

    CREATE TYPE [dbo].[BookingFormTableType] AS TABLE(
        [Id] [int] NOT NULL,
        [IsEnabled] [bit] NULL,
        [IsMandatory] [bit] NULL,
        [DisplayOrder] [int] NOT NULL

    And now pass this Table Type in Stored procedure

    CREATE PROC [dbo].[uspCreateBookingForm]                
    @AccountId INT,                                  
    @BFMapping BookingFormTableType READONLY               
     SET NOCOUNT ON;                           
             INSERT INTO dbo.BookingFormAndFieldLibraryMapping                
             SELECT @AccountId, IsEnabled, IsMandatory, DisplayOrder,                      
             GETUTCDATE() FROM @BFMapping        

    Now we will call this Stored procedure from our C# code and pass a Datatable in this SP for BookingFormTableType parameter.
    Here we have a DataTable Name : bookingFormMapping, which have same number of column as in our Table type BookingFormTableType.

    using (SqlConnection conn = new SqlConnection(dbConnectionString))
                        using (SqlCommand sqlCommand = new SqlCommand("uspCreateBookingForm", conn))
                            sqlCommand.CommandType = CommandType.StoredProcedure;
                            SqlParameter parameter = sqlCommand.Parameters.AddWithValue("@BFMapping", bookingFormMapping);
                            parameter.SqlDbType = SqlDbType.Structured;
                            parameter.TypeName = "BookingFormAndFieldLibraryMappingTableType";
                            parameter = sqlCommand.Parameters.AddWithValue("@accountId", accountId);
                            parameter.SqlDbType = SqlDbType.Int;

 0 Comment(s)

Sign In

Sign up using

Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: