Scribd Logo
Upload

Welcome to Scribd!

  • 26 views

    VP - Lab 14

    Uploaded by

    Bisma Tasawar
    This document discusses bulk insertion of records into a SQL Server database using stored procedures and user-defined table types. It provides instructions on: 1. Creating a user-defined table type to hold the data table of records to insert. 2. Creating a stored procedure that accepts the user-defined table type as a parameter and inserts the records into the target database table. 3. Testing the stored procedure by executing it and passing a sample data table. 4. Calling the stored procedure from .NET code by passing a data table parameter to the stored procedure.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd

    VP - Lab 14

    Uploaded by

    Bisma Tasawar
    26 views3 pages
    This document discusses bulk insertion of records into a SQL Server database using stored procedures and user-defined table types. It provides instructions on: 1. Creating a user-defined table type to hold the data table of records to insert. 2. Creating a stored procedure that accepts the user-defined table type as a parameter and inserts the records into the target database table. 3. Testing the stored procedure by executing it and passing a sample data table. 4. Calling the stored procedure from .NET code by passing a data table parameter to the stored procedure.

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    This document discusses bulk insertion of records into a SQL Server database using stored procedures and user-defined table types. It provides instructions on: 1. Creating a user-defined table type to hold the data table of records to insert. 2. Creating a stored procedure that accepts the user-defined table type as a parameter and inserts the records into the target database table. 3. Testing the stored procedure by executing it and passing a sample data table. 4. Calling the stored procedure from .NET code by passing a data table parameter to the stored procedure.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    26 views3 pages

    VP - Lab 14

    Uploaded by

    Bisma Tasawar
    This document discusses bulk insertion of records into a SQL Server database using stored procedures and user-defined table types. It provides instructions on: 1. Creating a user-defined table type to hold the data table of records to insert. 2. Creating a stored procedure that accepts the user-defined table type as a parameter and inserts the records into the target database table. 3. Testing the stored procedure by executing it and passing a sample data table. 4. Calling the stored procedure from .NET code by passing a data table parameter to the stored procedure.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    of 3

    National University of Modern Languages

    Department of Computer Sciences and Engineering

    Visual Programming

    Lab 14

    Objectives

    1. Adding bulk of records in database


    2. Using Stored Procedures
    3. Using User Defined Table Types

    Adding bulk of records in database

    Although ‘Insert’ query can be used multiple times to add bulk of records in database, however,
    .Net environment and Microsoft SQL Server provide a better and effective approach for this.

    It includes creating a stored procedure that is executed for inserting records; and a User Defined
    Table Type that can be used as a parameter to stored procedure. This User Defined Table Type
    can hold a DataTable which makes it easy to insert a while DataTable at once in database.

    Task 1: Creating User Defined Table Type

    For any table in your database, create a User Defined Type Table including columns for which
    you want to add values. (They may or may not be all columns).

    In your database, go to Programmability > Types > User-Defined Table Type > User-Defined
    Table Type

    Use the following sample script to add a User-Defined Table Type. Make changes as needed:

    CREATE TYPE UD_Inventory as Table


    (
    ItemName nvarchar(50),
    ItemDescription nvarchar(max),
    Price int,
    Quantity int,
    DistributorID int
    )
    Task 2: Creating a Stored Procedure

    Now, we have to create a stored procedure that uses the previously created User Defined Table
    Type, in order to insert records.

    Use the following script to create your stored procedure. Make changes where required.

    CREATE PROCEDURE USP_InsertInventory(@InventoryTable [UD_Inventory] ReadOnly)


    AS
    BEGIN

    INSERT INTO dbo.Inventory


    (
    ItemName,
    ItemDescription,
    Price,
    Quantity,
    DistributorID
    )
    SELECT * FROM @InventoryTable
    END

    Notice that previously created User Defined Table Type (UD_Inventory) is being used here to
    insert records in Inventory table. All records from UD_Inventory will be added to Inventory
    table.

    Task 3: Checking if stored procedure is working fine

    Before moving to Visual Studio for programming our application, we may test if the stored
    procedure is running fine.

    As our stored procedure takes a table as parameter, we will create one and pass it to the stored
    procedure to check.

    Use the following script to check your stored procedure. Make changes where required.

    Declare @Tab as [UD_Inventory];

    insert into @Tab


    Select 'abc','aaa',54,58,6 UNION ALL
    Select 'abc','aaasdasda',54,58,6

    Exec USP_InsertInventory @Tab;


    Task 4: Executing stored procedure from code

    We will be using the following function DBAccess function to execute stored procedures.

    Notice that we are simply using ExecuteNonQuery() function that was already used for insertion.
    Moreover, command type has been modified to StoredProcedure in this case.

    We just have to pass a paramter to our command, which is a datatable that will be replaced for
    @InventoryTable defined in the stored procedure.

    Specify your desired data table in a command and pass it to executeStoredProcedure()


    function, and specify the name of Stored Procedure to be executed.

    Run the project and observe rows added in database.

    Pro Tips:

     The data table can be created with any approach; getting data from a grid view or even
    textboxes or other controls.

    You might also like