Sql Connection

[DaveP]

Can you have Persistent Connection (always open)
in Dot.net
I have multiple calls i have to make back to the server
would like to keep the connection open...

or is this a mute point since pooling is handling cached connections

Tks
DaveP

 

[Nicholas Paldino [.NET/C# MVP]]

DaveP,

You can have a persistent connection, but to be honest, you are better
off opening the connection when you need it, and then closing it when you
are done. If you are going to do a number of database operations in a
well-defined context, then I would say it's fine to keep the connection open
for that context and close it when that context is left.

However, I think that doing this (keeping a connection open) is
considered a premature optimization.

 

[Arnshea]

Can you have Persistent Connection (always open)
in Dot.net
I have multiple calls i have to make back to the server
would like to keep the connection open...

or is this a mute point since pooling is handling cached connections

Tks
DaveP

By default connections are kept open as long as possible (Connection
Lifetime connection string property defaults to 0). With connection
pooling enabled (the default), Close() just releases the connection
back to the pool.

 

[sloan]

Open Late
Use quickly
Close Early

is the best advice 99.9% of the time.


If you need to do (2 to N operations) during the "Use Quickly" part, then
that's ok.

But if you're keeping it open for the sake of keeping it open, then that's a
bad practice.

 

[DaveP]

im in a loop...making repeted calls to the data base retrieviing ids for the
offline record set
normally i close the connections when im finished..but
here in a loop ..i think id like to keep it open til finished
looping

i can't seem to keep it open...im using DataReader
any help thanks
oh server is Sql2005

Tia
DaveP

 

[Willy Denoyette [MVP]]

By default connections are kept open as long as possible (Connection
Lifetime connection string property defaults to 0). With connection
pooling enabled (the default), Close() just releases the connection
back to the pool.

Actually, the "Connection Lifetime = 0" defaults to 6 minutes. That means
that the pool manager closes the physical connection after 6 minutes
inactivity.
That also means that you will incur a "physical connection" overhead if you
aren't "re-opening" the connection within this interval.

Willy.

 

[Chris Shepherd]

im in a loop...making repeted calls to the data base retrieviing ids for the
offline record set
normally i close the connections when im finished..but
here in a loop ..i think id like to keep it open til finished
looping

This sounds bad to me but maybe I'm just misunderstanding it. Mind
providing an example?

i can't seem to keep it open...im using DataReader
any help thanks
oh server is Sql2005

What about what it is telling you it is not persisting the connection
object?

Posting a simple example would do wonders.

Chris.

 

[DaveP]

// 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

 

[Kevin Spencer]

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

// 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

 

[DaveP]

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

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

// 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