SQL XML .NET Data Provider

SQL XML .NET Data Provider

SqlXmlCommand belongs to Microsoft.Data.SqlXml namespace, which is not part of .NET framework. The extra functionality SqlXmlCommand provides are: you can send a XPATH query instead of a SELECT SQL query to database to retrieve data, and submit changes to database using a diffgram XML document.

When you provide a XPATH to the command, it converts it to a “FOR XML” SQL query. When you submit a diffgram, the command generates a batch of SQL queries wrapped in a transaction. In both cases the command needs to know the schema of the table to be able to generate those SQL queries, so you need to provide the command with a XSD file:

' Retrieve data using XPATH

Dim cmd As New SqlXmlCommand(“Orders[CustomerID=’GROSR’]”, strConn)

cmd.SchemaPath = “C:\MySchema.xsd”

cmd.CommandType = SqlXmlCommandType.XPath

Dim rdr As XmlReader = cmd.ExecuteXmlReader()

mds.ReadXml(rdr)

' Do some changes

…

' Write the changed dataset into a DiffGram

mds.WriteXml(“C:\MyDiffGram.xml”, XmlWriteMode.DiffGram)

' Submit the DiffGram

cmd = New SqlXmlCommand(strConn)

cmd.SchemaPath = “C:\MySchema.xsd”

cmd.CommandType = SqlXmlCommandType.DiffGram

cmd.CommandStream = New FileStream(“C:\MyDiffGram.xml”, FileMode.Open, FileAccess.Read)

cmd.ExecuteNonQuery

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.