Writing a Table with Many Fields

  • Thread starter Thread starter Jonathan Wood
  • Start date Start date
J

Jonathan Wood

When writing a table with many (one to two dozen) fields, I'm trying to
decide it it makes more sense to write a stored procedure that takes the
fields as arguments, or if there is some way to populate a DataSet and then
save the DataSet. I'm really not sure what is involved in the second option
or how much sense it makes.

Any suggestions?

Thanks.
 
Not sure on your question, are you trying to compare SQL Statment Vs Store
Proc?
- JIGNESH
 
¤ When writing a table with many (one to two dozen) fields, I'm trying to
¤ decide it it makes more sense to write a stored procedure that takes the
¤ fields as arguments, or if there is some way to populate a DataSet and then
¤ save the DataSet. I'm really not sure what is involved in the second option
¤ or how much sense it makes.
¤
¤ Any suggestions?

I guess it depends upon how many rows you're planning on inserting or updating at a time. I've
always found the DataSet/DataTable methods to be somewhat inefficient although it is somewhat easier
to write the code and implement for batch inserts or updates.

If you're updating or inserting a single row just use a Command object w/Parameters to hold your
argument values. You can use either a SQL statement or stored procedure with this scenario.


Paul
~~~~
Microsoft MVP (Visual Basic)
 
It probably makes more sense to use a bulk copy technique to populate a data
table.

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________
 
Paul,
I guess it depends upon how many rows you're planning on inserting or
updating at a time. I've
always found the DataSet/DataTable methods to be somewhat inefficient
although it is somewhat easier
to write the code and implement for batch inserts or updates.

If you're updating or inserting a single row just use a Command object
w/Parameters to hold your
argument values. You can use either a SQL statement or stored procedure
with this scenario.
Click to expand...

For now, it's mostly a single row. This is the approach I've been taking but
the SQL statements start getting a little unmanageable. Just wondered if
there's a better way. Perhaps not.

Thanks.
 
No matter how you approach the app-end of the code, SQL server will still
need to have a procedure with individual columns specified for
inserts/updating, etc.

MS offer the DAAB through their patterns & practices sub-site. The DAAB is
a library that provides some abstraction techniques for making your code
easier to read and faster to write.

One of these techniques is the UpdateDataset method, which allows you to
specify sprocs for Insert, Update, Delete (or only one of these), and pass a
dataset that these sprocs will operate against. I believe that this
approach also is smart enough to only update the records that actually have
changes, so if the current row hasn;t changed since being loaded from the
DB, it doesn;t get sent over the wire to the DB for updating.

You will still need to write your sproc in full and specify the parameters
for each sproc in your app code (no getting away from that yet!), e.g;
dbCmd = db.GetStoredProcCommand("usp_My_UPDATE_sproc_name")

db.AddInParameter(dbCmd, "RepID", DbType.Int32, "RepID",
DataRowVersion.Current)

db.AddInParameter(dbCmd, "ClientID", DbType.Int32, myClient.ClientID)

db.AddInParameter(dbCmd, "RptOrder", DbType.Int32, "RptOrder",
DataRowVersion.Current)

db.AddInParameter(dbCmd, "ForOHA", DbType.Boolean, "ForOHA",
DataRowVersion.Current)

db.AddInParameter(dbCmd, "KPIBoundary", DbType.Int32, "KPIBoundary",
DataRowVersion.Current)

Dim rowsAffected As Integer = db.UpdateDataSet(Me.dsCustomer, "Reports",
Nothing, dbCmd, Nothing, UpdateBehavior.Continue)


Alec
 
I'm not sure if I understand the advantage. By writing a simple data layer
method, ExecProcInt, I can call a stored procedure using code like this:

DataLayer.ExecProcInt("mc_Clients_UpdateActivityLevel",
"@ClientID", clientId,
"@ActivityLevel", activityLevel);

Since I still need to write the store procedure anyway, the code you posted
would appear to take significantly more type.

Is there some advantage to using the library you described?
 
Back
Top