Re: Sql Connection
- From: "DaveP" <dvs_bis@xxxxxxxxxxxxx>
- Date: Wed, 03 Oct 2007 20:42:03 GMT
again code is sample....
1 datatable not ready for inserts it is missing key columns
i require from the database.
2. i am asking if there is a way to link a Array or a this table to
retrieve all values required before i do a batch update..
3 ..i dont like the code either....again a example of a loop
making calls to the database ....
if i can bind a array or a offline table to the database
i can make a single call....finialize this offline table
and write complete rows back to the database
.....
what i did yesterday is just get the parsed data to a temp table on the
server and run a proc to get the rest of the values needed....
again..
looking for other options and experience from others
the final decision is mine how i accomplish a task
DaveP
"Kevin Spencer" <unclechutney@xxxxxxxxxxxx> wrote in message
news:eY4k19aBIHA.1188@xxxxxxxxxxxxxxxxxxxxxxx
This code is a mess. It sounds like you're doing double the work and
adding enormous complexity to something as simple as updating a number of
customer records. You're executing a large number of SELECT statements one
at a time to fetch data that you plan to update in any case. Doing a batch
update with a DataAdapter actually performs multiple INSERTs or UPDATEs in
a loop.
Now, depending on whether you're doing an INSERT or an UPDATE, each record
could be handled completely in your loop using a simple INSERT or UPDATE
statement. For example:
INSERT INTO Database1.dbo.someTable (CustID, ColumnA, ColumnB)
SELECT t1.SomeID1. 'Column A Value', 'Column B Value'
FROM Database1.dbo.someOtherTable t1
- or -
UPDATE Database1.dbo.someTable
SET ColumnA = 'ColumnA Value', ColumnB = 'Column B Value'
WHERE CustID = @CustID
A Stored Procedure would be more efficient than building a string. And
finally, your usage of GetType() to find out if a DataReader column value
is null is superfluous. When you reference the column value without a
type, it is of type object, and if it is null, it is null. Example:
if (Reader1.IsDbNull(0)) ...
--
HTH,
Kevin Spencer
Microsoft MVP
DSI PrintManager, Miradyne Component Libraries:
http://www.miradyne.net
"DaveP" <dvs_bis@xxxxxxxxxxxxx> wrote in message
news:JRvMi.30621$eY.12142@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
// Set up command for reuse in loop just change the customer
id
SqlConn1.Cmd = new SqlCommand();
SqlConn1.Cmd.Parameters.Clear();
SqlConn1.Cmd.CommandText = "Select Top 1
t1.SomeID1,t1.SomeID2,t2,UserId,fp From Database1.dbo.t1 c WITH (NOLOCK)
" +
"left Join database2.DBO.somtable2
t2 WITH (NOLOCK) on fp.FileId=c.FileId "+
"where CustID=@CustId";
SqlConn1.Cmd.Connection=SqlConn1.Conn;
SqlConn1.Cmd.CommandType = CommandType.Text; //
.StoredProcedure;
SqlConn1.Cmd.Parameters.Add("ClaimID", SqlDbType.Int).Value =
0; //Direction = ParameterDirection.Input;
SqlConn1.Open();
int ID1=0;
int ID2=0;
int UserID = 0;
//oserver is local offline table of parsed data from text file
1000 0r 10000 rows
//oTable is "Select top 0 from Database..sometable" the empty
table in dataset is the table i will be updating from parsed data
while (oServer.RowPos<20) //(oServer.Eof==false)
{
otable.AppendRow();
//add columns to the row
otable,.ColumnPut("FileID", FileId); //File of parent
table Identity key Incomming Parameter
CustID=Convert.ToInt32(oServer.ColumnGet("CustID")); //
get cust id from local table of parsed data
oTable.ColumnPut("ClaimId",ClaimID);
//Area In Question Below is this the proper way or should
i persist the open connection
// get the tthe other information from the server using
SqlDataReader
SqlConn1.Cmd.Open(); // in queston lots of opens for a
Single Run
SqlConn1.Cmd.Parameters["CustID"].Value=CustID;
Reader =
SqlConn1.Cmd.ExecuteReader(CommandBehavior.SingleRow);
ClaimFileID = 0;
MemberID = 0;
UserID = 0;
FormTypeID = 0;
if (Reader.HasRows == true)
{
while (Reader.Read())
{
if (Reader[0].GetType()!=typeof(System.DBNull))
ID1 = Convert.ToInt32(Reader[0].ToString());
if (Reader[1].GetType()!=typeof(System.DBNull))
ID2 = Convert.ToInt32(Reader[1].ToString());
if (Reader[2].GetType()!=typeof(System.DBNull))
UserID = Convert.ToInt32(Reader[2].ToString());
}
}
this is looping through a local table that holds parsed data
i place the parsed data into another table with dataset and
sqldataadapter
the avove sqldatareader gets some more information from the database that
is required
then i do a batch update with sqladapter
the question is the open connection where the Datareader is used insid
the loop could be 10000 loops
would it be better to open before the loop and close after the loop
or is the opening of the connection ok inside the loop
Thanks
DaveP
.
- References:
- Sql Connection
- From: DaveP
- Re: Sql Connection
- From: DaveP
- Re: Sql Connection
- From: Chris Shepherd
- Re: Sql Connection
- From: DaveP
- Re: Sql Connection
- From: Kevin Spencer
- Sql Connection
- Prev by Date: Re: .NET framework source code available soon...
- Next by Date: Re: Help: Continue String Code to next line
- Previous by thread: Re: Sql Connection
- Next by thread: Writing Byte Array to Disk
- Index(es):
Relevant Pages
|
