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
    • 706
    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
    )
    GO  
    

    And now pass this Table Type in Stored procedure

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

    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;
    
                            conn.Open();
                            sqlCommand.ExecuteNonQuery();
                            conn.Close();
                        }
                    }
    

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
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: