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?

Show quote Hide quote 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()

========================

Hi Chubbly,

The .NET Data Access Application Block methods deal with cases that a
DataSet is modified with multiple rows. The Update methods can update the
changes to database at one time. And it is not always suitable for all
cases. If you're just updating changes for one row, you can wrap the update
statements in your own code to achieve this.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

SQL query on Datatables [VB 2005]
Datagridview cell background color?
arraylist to use in another form
Disable objectdatasource-control
Class Instantiation
Creating Multiple Listviews in Code
Validating Data in a DataGrid
private font collection - delete
scope - integer
Open Excel file with ASP.Net