"Spam Catcher" <sp**********@r ogers.com> wrote in message
news:Xn******** *************** ***********@127 .0.0.1...
"Chubbly Geezer" <ch************ @newsgroup.nosp am> wrote in
news:#Y******** ******@TK2MSFTN GP04.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.GetSqlString Command(sqlComm and)
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(product sTable)
' 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).D elete()
' Establish our Insert, Delete, and Update commands
Dim insertCommand As DbCommand = db.GetSqlString Command("INSERT INTO
Products(Produc tName, CategoryID, UnitPrice) VALUES(@Product Name,
@CategoryID, @UnitPrice)")
'Dim insertCommand As DbCommand = db.GetStoredPro cCommand("AddPr oduct")
db.AddInParamet er(insertComman d, "ProductNam e", DbType.String,
"ProductNam e", DataRowVersion. Current)
db.AddInParamet er(insertComman d, "CategoryID ", DbType.Int32, "CategoryID ",
DataRowVersion. Current)
db.AddInParamet er(insertComman d, "UnitPrice" , DbType.Currency , "UnitPrice" ,
DataRowVersion. Current)
Dim deleteCommand As DbCommand = db.GetSqlString Command("Delete From
Products where ProductId = @ProductId")
'Dim deleteCommand As DbCommand = db.GetStoredPro cCommand("Delet eProduct")
db.AddInParamet er(deleteComman d, "ProductID" , DbType.Int32, "ProductID" ,
DataRowVersion. Current)
Dim updateCommand As DbCommand = db.GetSqlString Command("UPDATE Products SET
ProductName = @ProductName WHERE ProductID = @ProductID AND LastUpdate =
@LastUpdate")
'Dim updateCommand As DbCommand = db.GetStoredPro cCommand("Updat eProduct")
db.AddInParamet er(updateComman d, "ProductID" , DbType.Int32, "ProductID" ,
DataRowVersion. Current)
db.AddInParamet er(updateComman d, "ProductNam e", DbType.String,
"ProductNam e", DataRowVersion. Current)
db.AddInParamet er(updateComman d, "LastUpdate ", DbType.DateTime ,
"LastUpdate ", DataRowVersion. Current)
' Submit the DataSet, capturing the number of rows that were affected
Dim rowsAffected As Integer = db.UpdateDataSe t(productsDataS et, "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( )
=============== =========
