multiple inserts or updates

[Guest]

Hello,

I have two different, but related questions.
1) In .net or ADO is there a way to make an insert by just passing new
parameters to string. I am doing multiple inserts where the only difference
is values being inserted. In unmanged code I could use sprintf to have my
insert command with parameters I need to substitute. For each insert command
there are 9 parameters. Currently the only way I could figure to do it is to
use StringBuilder and Append each piece seperately, this seems wasteful,
since need to create a new
Stringbuilder object for each insert and build it from scratch.

2)Is there a way to buffer updates with where clauses so that you only need
to issue only on execute command instead of issuing them one at a time. I am
using ADO .net. In other programing environments I could buffer the commands
such that each element in an array represents a specific update. The updates
are the same except for the where clause parameters and actual data being
updated. I did this in oracle proc using cursors, but don't know how to
handle it in ADO .net.

Thanks
Brian

 

[Marina Levit [MVP]]

1. Use parameterized queries. You can find the details in the documentation
and online

2. Sorry, I don't really understand what you mean here. It sort of sounds
like parameterized queries also, except they are updates instead of inserts.

 

[Val Mazur \(MVP\)]

Hi Brian,

1. Use parameterized query as described in next KB. Do not use string
concatenation, because it could lead to the SQL injection

http://support.microsoft.com/kb/308049/en-us
2. To cache data you could query database with the query that will return
table schema, but no data, lke SELECT .... FROM .. WHERE 1=0. It will create
DataTable on a client side with the structure of your fields in a database.
Then add recordset to this DataTable and after all the records there call
Update Method of the DataAdapter to move everything in one shot. It is not a
real batch, but at least it is one spot for the updates

 

[Guest]

I see from the responses that I should be using parameters to do this. But
all examples seem to be geared around using data adapters and data sets which
as I understand it for use with non connected data. I am connected to
database and want to do this in a real time application, so I don't think I
want to use data sets and data adapters. I also at this time do not want to
use stored procedures for my
inserts. So I am looking for a small example in creating an insert with
parameters that are then immediatly executed in the database. From
documentation it is not clear to me if this can be done without using data
adapters and data sets.

Thanks
Brian

 

[Guest]

I got the insert to database withe parameters to work using the oracle
command class. Below is sample of code, where I did not define any data sets
or database adapeters

cmd->CommandText =
"insert into time_keeper (acctcomp,prodcode,empcode,origstart,
startscan, endscan,editor,editdate,comments,active) values ("
":acctcomp, rodcode, :empcode, to_date origstart,'MM/DD/YYYY
HH:MI:SS AM'),"
"to_date startscan,'MM/DD/YYYY HH:MI:SS AM'), to_date
endscan,'MM/DD/YYYY HH:MI:SS AM'), :editor, to_date editdate,'MM/DD/YYYY
HH:MI:SS AM'),:comments,'Y')";

cmd->Parameters->Add("acctcomp",keeperReader->GetString(1));
cmd->Parameters->Add("prodcode",keeperReader->GetString(2));
cmd->Parameters->Add("empcode",keeperReader->GetString(3));
cmd->Parameters->Add("origstart",keeperReader->GetDateTime(4).ToString());
cmd->Parameters->Add("startscan",keeperReader->GetDateTime(5).ToString());
cmd->Parameters->Add("endscan",tempReader->GetDateTime(3).ToString());
cmd->Parameters->Add("editor",tempReader->GetString(4));
cmd->Parameters->Add("editdate",tempReader->GetDateTime(5).ToString());
cmd->Parameters->Add("comments",(keeperReader->IsDBNull(9))?
Convert:BNull : keeperReader->GetString(9));
cmd->ExecuteNonQuery(); // execute insert command
cmd->Parameters->Clear();

 

[Alec MacLean]

Brian,

Are you using Oracle as the DB server?

My example is based on using SQL server, but the principles will remain the
same.

I recommend you check out the use of the MS Enterprise Library DAAB. This
is free from http://msdn.microsoft.com/practices. There's a version for 1.1
framework (VS 2003) and a new version just released for 2.0 framework (VS
2005).

For explanation of how to implement 1.1 version, see MSDN mag article by
John Papa:
http://msdn.microsoft.com/msdnmag/issues/05/08/DataPoints/default.aspx.
This is covered in three articles. The above link points to part two, which
is most relevant to example below.

I'd recommend creating a function to be called in a loop to do the inserts
or updates. I've illustrated the function below using three of the
parameters you have indicated you use.
Note that I've added some error trapping and a return (output) parameter
just to indicate what is possible.


Private Function InsertValues(ByVal AcctComp As String, _
ByVal ProdCode As String, ByVal OrigStart As DateTime) As Integer
'Create DAAB objects:
Dim db As Database = DatabaseFactory.CreateDatabase
Dim cmd As DBCommandWrapper =
db.GetStoredProcCommandWrapper("usp_TargetTable_INSERT")
'Add input params
cmd.AddInParameter("@acctcomp", DbType.String, AcctComp.Trim)
cmd.AddInParameter("@prodcode", DbType.String, ProdCode.Trim)
cmd.AddInParameter("@origstart", DbType.DateTime, OrigStart)
'...add more input parameters as required for your app
'...etc
'Add any output params you might use, e.g. return integer for
'result status of the insert.
cmd.AddOutParameter("@Result", DbType.Int32, 4)
Try
'Send the values into db...
db.ExecuteNonQuery(cmd)
'Retrieve and return the output result value of sproc.
InsertValues = cmd.GetParameterValue("@Result")
Catch ex As Exception
'Your error handling code here
End Try
End Function


This example doesn't require declaring datasets or adapters as it just
inserts. You'd consider using datasets for retrieving and holding data
locally for your app.

Hope that helps.

Al