Using stored procedure output parameters

 Using stored procedure output parameters

Suppose database Northwind has the following stored procedures for table [Order Details]:

      (1) –––––––––––––––––––––––––––––––––––––––––––––––

            ALTER PROCEDURE dbo.SelectOrderDetails

            AS

            SELECT OrderID, ProductID, Quantity, TStamp FROM [Order Details] WHERE OrderID < 10254

           

           

      (2) –––––––––––––––––––––––––––––––––––––––––––––––

            ALTER PROCEDURE dbo.UpdateOrderDetails

            (

                @OrderID_New int,

                @ProductID_New int,

                @Quantity_New smallint,

                @OrderID_Orig int,

                @ProductID_Orig int,

                @TStamp timestamp OUTPUT

             )

            AS

            UPDATE [Order Details]

                SET OrderID = @OrderID_New, ProductID = @ProductID_New, Quantity = @Quantity_New

                WHERE OrderID = @OrderID_Orig AND ProductID = @ProductID_Orig AND TStamp = @TStamp;

               

            IF @@ROWCOUNT = 1

                SELECT @TStamp = TStamp FROM [Order Details] WHERE OrderID = @OrderID_New and ProductID = @ProductID_New

           

           

      (3) –––––––––––––––––––––––––––––––––––––––––––––––

            ALTER PROCEDURE dbo.InsertOrderDetails

            (

                @OrderID int,

                @ProductID int,

                @Quantity smallint,

                @TStamp timestamp OUTPUT

            )

            AS

               INSERT INTO [Order Details] (OrderID, ProductID, Quantity) VALUES(@OrderID, @ProductID, @Quantity);

           

               SELECT @TStamp = TStamp FROM [Order Details] WHERE OrderID = @OrderID and ProductID = @ProductID

           

           

      (4) –––––––––––––––––––––––––––––––––––––––––––––––

            ALTER PROCEDURE dbo.DeleteOrderDetails

            (

                @OrderID int,

                @ProductID int

  )

            AS

               DELETE FROM [Order Details] WHERE OrderID = @OrderID AND ProductID = @ProductID

The code is listed as follow:

Dim param As OleDbParameter

' SelectCommand

Dim selectCmd As OleDbCommand = mcn.CreateCommand()

selectCmd.CommandText = "SelectOrderDetails"

selectCmd.CommandType = CommandType.StoredProcedure

mda.SelectCommand = selectCmd

' UpdateCommand

Dim updateCmd As OleDbCommand = mcn.CreateCommand()

updateCmd.UpdatedRowSource = UpdateRowSource.OutputParameters

updateCmd.CommandText = "UpdateOrderDetails"

updateCmd.CommandType = CommandType.StoredProcedure

updateCmd.Parameters.Add("OrderID_New", OleDbType.Integer, 0, "OrderID")

updateCmd.Parameters.Add("ProductID_New", OleDbType.VarChar, 0, "ProductID")

updateCmd.Parameters.Add("Quantity_New", OleDbType.VarChar, 0, "Quantity")

param = updateCmd.Parameters.Add("OrderID_Orig", OleDbType.Integer, 0, "OrderID")

param.SourceVersion = DataRowVersion.Original

param = updateCmd.Parameters.Add("ProductID_Orig", OleDbType.VarChar, 0, "ProductID")

param.SourceVersion = DataRowVersion.Original

param = updateCmd.Parameters.Add("TimeStamp_Orig", OleDbType.Binary, 8, "TStamp")

param.SourceVersion = DataRowVersion.Original

param.Direction = ParameterDirection.InputOutput

mda.UpdateCommand = updateCmd

' DeleteCommand

Dim deleteCmd As OleDbCommand = mcn.CreateCommand()

deleteCmd.CommandText = "DeleteOrderDetails"

deleteCmd.CommandType = CommandType.StoredProcedure

deleteCmd.Parameters.Add("OrderID", OleDbType.Integer, 0, "OrderID")

deleteCmd.Parameters.Add("ProductID", OleDbType.VarChar, 0, "ProductID")

mda.DeleteCommand = deleteCmd

' InsertCommand

Dim insertCmd As OleDbCommand = mcn.CreateCommand()

insertCmd.UpdatedRowSource = UpdateRowSource.OutputParameters

insertCmd.CommandText = "InsertOrderDetails"

insertCmd.CommandType = CommandType.StoredProcedure

insertCmd.Parameters.Add("OrderID", OleDbType.Integer, 0, "OrderID")

insertCmd.Parameters.Add("ProductID", OleDbType.VarChar, 0, "ProductID")

insertCmd.Parameters.Add("Quantity", OleDbType.VarChar, 0, "Quantity")

param = insertCmd.Parameters.Add("TStamp", OleDbType.Binary, 8, "TStamp")

param.Direction = ParameterDirection.Output

mda.InsertCommand = insertCmd

mda.Fill(mds)

mdg.DataSource = mds

mdg.DataMember = "Table"

Note that in the UPDATE command the time stamp parameter is used for both input and output, and in the INSERT command the time stamp parameter is used for output only.

Using batch queries in stored procedures

 Using batch queries in stored procedures

Database Northwind has the following stored procedures for table [Order Details]:

      (1)–––––––––––––––––––––––––––––––––––––––––––––––

            ALTER PROCEDURE dbo.SelectOrderDetails

            AS

                SELECT OrderID, ProductID, Quantity, TStamp FROM [Order Details] WHERE OrderID < 10254

           

           

      (2) –––––––––––––––––––––––––––––––––––––––––––––––

            ALTER PROCEDURE dbo.UpdateOrderDetails

                (

                  @OrderID_New int,

                  @ProductID_New int,

                  @Quantity_New smallint,

                  @OrderID_Orig int,

                  @ProductID_Orig int,

                  @TStamp timestamp

                )

            AS

                UPDATE [Order Details]

                  SET OrderID = @OrderID_New, ProductID = @ProductID_New, Quantity = @Quantity_New

                  WHERE OrderID = @OrderID_Orig AND ProductID = @ProductID_Orig AND TStamp = @TStamp;

                 

                IF @@ROWCOUNT = 1

                SELECT TStamp FROM [Order Details] WHERE OrderID = @OrderID_New and ProductID = @ProductID_New

           

           

      (3) –––––––––––––––––––––––––––––––––––––––––––––––

            ALTER PROCEDURE dbo.InsertOrderDetails

                (

                  @OrderID int,

                  @ProductID int,

                  @Quantity smallint

                )

            AS

                INSERT INTO [Order Details] (OrderID, ProductID, Quantity) VALUES(@OrderID, @ProductID, @Quantity);

               

                SELECT TStamp FROM [Order Details] WHERE OrderID = @OrderID and ProductID = @ProductID

           

           

      (4) –––––––––––––––––––––––––––––––––––––––––––––––

            ALTER PROCEDURE dbo.DeleteOrderDetails

               (

                  @OrderID int,

                  @ProductID int

                )

            AS

            DELETE FROM [Order Details] WHERE OrderID = @OrderID AND ProductID = @ProductID

The code is listed as follow:

Dim param As OleDbParameter

' SelectCommand

Dim selectCmd As OleDbCommand = mcn.CreateCommand()

selectCmd.CommandText = "SelectOrderDetails"

selectCmd.CommandType = CommandType.StoredProcedure

mda.SelectCommand = selectCmd

' UpdateCommand

Dim updateCmd As OleDbCommand = mcn.CreateCommand()

updateCmd.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord

updateCmd.CommandText = "UpdateOrderDetails"

updateCmd.CommandType = CommandType.StoredProcedure

updateCmd.Parameters.Add("OrderID_New", OleDbType.Integer, 0, "OrderID")

updateCmd.Parameters.Add("ProductID_New", OleDbType.VarChar, 0, "ProductID")

updateCmd.Parameters.Add("Quantity_New", OleDbType.VarChar, 0, "Quantity")

param = updateCmd.Parameters.Add("OrderID_Orig", OleDbType.Integer, 0, "OrderID")

param.SourceVersion = DataRowVersion.Original

param = updateCmd.Parameters.Add("ProductID_Orig", OleDbType.VarChar, 0, "ProductID")

param.SourceVersion = DataRowVersion.Original

param = updateCmd.Parameters.Add("TimeStamp_Orig", OleDbType.Binary, 8, "TStamp")

param.SourceVersion = DataRowVersion.Original

mda.UpdateCommand = updateCmd

' DeleteCommand

Dim deleteCmd As OleDbCommand = mcn.CreateCommand()

deleteCmd.CommandText = "DeleteOrderDetails"

deleteCmd.CommandType = CommandType.StoredProcedure

deleteCmd.Parameters.Add("OrderID", OleDbType.Integer, 0, "OrderID")

deleteCmd.Parameters.Add("ProductID", OleDbType.VarChar, 0, "ProductID")

mda.DeleteCommand = deleteCmd

' InsertCommand

Dim insertCmd As OleDbCommand = mcn.CreateCommand()

insertCmd.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord

insertCmd.CommandText = "InsertOrderDetails"

insertCmd.CommandType = CommandType.StoredProcedure

insertCmd.Parameters.Add("OrderID", OleDbType.Integer, 0, "OrderID")

insertCmd.Parameters.Add("ProductID", OleDbType.VarChar, 0, "ProductID")

insertCmd.Parameters.Add("Quantity", OleDbType.VarChar, 0, "Quantity")

mda.InsertCommand = insertCmd

mda.Fill(mds)

mdg.DataSource = mds

mdg.DataMember = "Table"

Note that in the UPDATE command the time stamp parameter is used for input only, and in the INSERT command there is no time stamp parameter.

Retrieve dataset schema from database at run time

  Retrieve dataset schema from database at run time

This approach is the slowest and not recommended for production-scale products, because retrieving schema from database takes a lot of time.

You can either retrieve the schema from database separately or together with data. To retrieve schema separately, callDataAdapter.FillSchema. It can be done either before or after Fill. When you call FillSchema, you have a choice to use the original table and column names in the database as the table and column names of the dataset, or to use different names at your choice by providing a mapping of the table and column names. The first parameter ofFillSchema is the dataset, and the second is an enumeration indicating whether you want to use original namesin the database (SchemaType.Source), or the TableMappings and ColumnMappings that you have added into the dataset (SchemaType.Map). See section Mapping Table Names in DataAdapter.Fill and Mapping column Names in DataAdapter.Fill for details.

To fill schema together with data, set DataAdapter's MissingSchemaAction property to enumerationMissingSchemaAction.AddWithKey before calling DataAdapter.Fill.

In the following example, the code to retrieve schema from database into dataset has been commented out, so the result will contain no schema.

Dim cn As OleDbConnection = New OleDbConnection("File Name=DataLink.udl")

Dim cmd As OleDbCommand = cn.CreateCommand()

cmd.CommandText = "Select * from Customers where Country = 'Germany'"

Dim da As OleDbDataAdapter = New OleDbDataAdapter(cmd)

'da.MissingSchemaAction = MissingSchemaAction.AddWithKey

Dim ds As DataSet = New DataSet()

'da.FillSchema(ds, SchemaType.Source)

da.Fill(ds)

'da.FillSchema(ds, SchemaType.Source)

Dim col As DataColumn

Dim constraints As String

For Each col In ds.Tables(0).Columns

constraints = "Col Name: " & col.ColumnName & ". AutoIncrement: " & col.AutoIncrement &

" MaxLength: " & col.MaxLength & " AllowDBNull: " & col.AllowDBNull

MessageBox.Show(constraints)

Next

MessageBox.Show("Table Customers's primary key: " & ds.Tables(0).PrimaryKey(0).ColumnName)