"Spam Catcher" <sp**********@rogers.com> wrote in message
news:Xn**********************************@127.0.0. 1...
"Chubbly Geezer" <ch************@newsgroup.nospam> wrote in
news:#Y**************@TK2MSFTNGP04.phx.gbl:
Does anyone have any experience of this functionality. I have started
to swap out my connection code for the std functionality within these
dll's. The data connection is fine, I can also run commands and return
datasets. However, the example they use to update a dataset is awful.
It seems to reply on hard coded sql store procedures (3 for each
table) in order to work. This is so much more unfriendly than the
standard process of updating a datarow or table. Any suggestions
please.
The data blocks work like the regular sql classes, except everything is
wrapped into one call... What hard coded SQL are you talking about?
The demo app uses the following code to update a dataset. It references 3
stored procedures for the insert, delete and update functionality. These
sp's have the table name hard coded in (i.e. Products).
I have realised that I can bypass these and drop the equivalent code direct
into my code (as I have done below). What I am now wanting to do, is wrap
up most of this functionality within a module and pass in the required
parameters. If I have to type all this every time, just for an update to a
dataset I'd be better off creating a datarow and updating that. It's easier
and has less code.
========================
Public Function UpdateProducts() As Integer
' Create the Database object, using the default database service. The
' default database service is determined through configuration.
Dim db As Database = DatabaseFactory.CreateDatabase()
Dim productsDataSet As DataSet = New DataSet
Dim sqlCommand As String = "Select ProductID, ProductName, CategoryID,
UnitPrice, LastUpdate " & _
"From Products"
Dim dbCommand As DbCommand = db.GetSqlStringCommand(sqlCommand)
Dim productsTable As String = "Products"
' Retrieve the initial data
db.LoadDataSet(dbCommand, productsDataSet, productsTable)
' Get the table that will be modified
Dim table As DataTable = productsDataSet.Tables(productsTable)
' Add a new product to existing DataSet
Dim addedRow As DataRow = table.Rows.Add(New Object() {DBNull.Value, "Sid's
product", 2, 25})
' Modify an existing product
table.Rows(0)("ProductName") = "Harry's Modified product"
' Delete an existing product
table.Rows(1).Delete()
' Establish our Insert, Delete, and Update commands
Dim insertCommand As DbCommand = db.GetSqlStringCommand("INSERT INTO
Products(ProductName, CategoryID, UnitPrice) VALUES(@ProductName,
@CategoryID, @UnitPrice)")
'Dim insertCommand As DbCommand = db.GetStoredProcCommand("AddProduct")
db.AddInParameter(insertCommand, "ProductName", DbType.String,
"ProductName", DataRowVersion.Current)
db.AddInParameter(insertCommand, "CategoryID", DbType.Int32, "CategoryID",
DataRowVersion.Current)
db.AddInParameter(insertCommand, "UnitPrice", DbType.Currency, "UnitPrice",
DataRowVersion.Current)
Dim deleteCommand As DbCommand = db.GetSqlStringCommand("Delete From
Products where ProductId = @ProductId")
'Dim deleteCommand As DbCommand = db.GetStoredProcCommand("DeleteProduct")
db.AddInParameter(deleteCommand, "ProductID", DbType.Int32, "ProductID",
DataRowVersion.Current)
Dim updateCommand As DbCommand = db.GetSqlStringCommand("UPDATE Products SET
ProductName = @ProductName WHERE ProductID = @ProductID AND LastUpdate =
@LastUpdate")
'Dim updateCommand As DbCommand = db.GetStoredProcCommand("UpdateProduct")
db.AddInParameter(updateCommand, "ProductID", DbType.Int32, "ProductID",
DataRowVersion.Current)
db.AddInParameter(updateCommand, "ProductName", DbType.String,
"ProductName", DataRowVersion.Current)
db.AddInParameter(updateCommand, "LastUpdate", DbType.DateTime,
"LastUpdate", DataRowVersion.Current)
' Submit the DataSet, capturing the number of rows that were affected
Dim rowsAffected As Integer = db.UpdateDataSet(productsDataSet, "Products",
insertCommand, updateCommand, deleteCommand, UpdateBehavior.Standard)
Return rowsAffected
End Function
========================
ALTER PROCEDURE [dbo].[AddProduct]
(
@ProductName nvarchar(50),
@CategoryID int,
@UnitPrice money
)
AS
INSERT INTO
Products (ProductName, CategoryID, UnitPrice)
VALUES
(@ProductName, @CategoryID, @UnitPrice)
SELECT
ProductID, ProductName, CategoryID, UnitPrice
FROM
Products
WHERE
ProductID = SCOPE_IDENTITY()
========================
