Iwan Petrow
Hi,
I have an .net windows form application and sql server 2005.
In one of the functions I have one connection for a sqldatareader and
after that I want to use other connections to sql server (do some
things like insert and update statements).
At this moment I create other SqlConnection objects in this way :
SqlConnection newSqlConn=new
SqlConnection(b asicSqlConnecti on.ConnectionSt ring). And this procedure
two or three times.
Is there other possibilities to do this? (If it is possible to avoid so
many SqlConnections) ?
Thanks.
Apr 4 '06 #1
Subscribe Reply
5 
15073 
Michael Voss
Iwan Petrow wrote:
[...snip...]
In one of the functions I have one connection for a sqldatareader and
after that I want to use other connections to sql server (do some
things like insert and update statements).[...snip...]Why would you want to use another connection ? Using the same connection for
doing both would be fine (as long as you don't need different credentials to
perform selects, inserts and updates, but I'd consider this useless).Is there other possibilities to do this? (If it is possible to avoid so
many SqlConnections) ?
[...snip...]
Continue using your connection until you need to close it ;-)
Apr 4 '06 #2
Rob Banfield
Iwan,
Quite simply, you won't need to create multiple SQLConnections. The
first time you create your first, VS will open a pool of them for you,
and the next time you need a SQLConnection object, VS will look at the
pool to see if any are currently available. If not, it will dynamically
add more to the pool.
The best way to do all of this cleanly is to use the "Using ()..."
syntax as in this code fragment:
using (SqlConnection sqlConn = new
SqlConnection(A ppGlobals.ConnS tring))
{
sqlConn.Open();
cSelect = " select blah from Yaaah";
SqlDataAdapter sdaLVData = new SqlDataAdapter( cSelect, sqlConn);
DataSet dsLVData = new DataSet();
sdaLVData.Fill( dsLVData, "SerNums");
// Do something!...
foreach (DataRow oRow in dsLVData.Tables["SerNums"].Rows)
{
blahh...
}
sqlConn.Close() ;
sqlConn.Dispose ();
}
Just do a google on SQL and connection and pool, and you'll get lots
more on this.
Happy coding
Robert
Apr 4 '06 #3
Rob Banfield
PS - forgot to add that you do this anywhere and everywhere you need a
SQLConnection. That's all there is to it!
Apr 4 '06 #4
Iwan Petrow
I use datareader (one open connection). When I try to use the same open
connection it throws an exception that the connection is opened and is
used by datareader.
Apr 4 '06 #5
Rob Banfield
Iwan,
Look at the example I gave you - you need this kind of construction
each time you wish to create and use a datareader. Don't worry about
the number of connections - this is all handled automatically by VS
itself. Each time you use a construction like the following:
using (SqlConnection sqlConn = new
SqlConnection(A ppGlobals.ConnS tring))
{
sqlConn.Open();
//Create datareader #1...
//
// use it...
//
// then discard it.
sqlConn.Close() ;
sqlConn.Dispose ();
}
using (SqlConnection sqlConn = new
SqlConnection(A ppGlobals.ConnS tring))
{
sqlConn.Open();
//Create datareader #2...
//
// use it...
//
// then discard it.
sqlConn.Close() ;
sqlConn.Dispose ();
}
, etc.,
you are simply asking VS to give you one of the available connections
in the pool. Nothing is being wasted here, and pooling connections is
the most efficient way of handling things. That's why VS does this for
you...
HTH,
Robert
Apr 4 '06 #6
This thread has been closed and replies have been disabled. Please start a new discussion.
Similar topics
| 8 1598 Static vs. non-static connection by: Natan |last post by: Hi, I`m creating a asp.net intranet system, and would like to put all the logic of the system in a single dll. That is, when i need to create an user for example, i would use "User.Check()" and "User.Add()" instead of putting "insert" sql statements in the page. Considering he following logic: User u = new User(); ASP.NET |
| 26 16516 Underlying connection was closed by: Rajeev Tipnis |last post by: http://support.microsoft.com/default.aspx?scid=kb;en- us;819450 Questions: 1) Is this patch (fix) applicable to the .NET 1.1 Framework as well? That is, if we have Framework 1.1 (On Win2K3/IIS6.0), do we still need this patch? 2) Does it specifically solve only this error: The underlying connection was closed. An unexpected error .NET Framework |
| 6 5644 Making a persistent HTTP connection by: David Rasmussen |last post by: I use urllib2 to do some simple HTTP communication with a web server. In one "session", I do maybe 10-15 requests. It seems that urllib2 opens op a connection every time I do a request. Can I somehow make it use _one_ persistent connection where I can do multiple GET->"receive data" passes before the connection is closed? /David Python |
| 14 3505 Connection String in .config file - Security Concerns by: WebMatrix |last post by: Hello, I have developed a web application that connects to 2 different database servers. The connection strings with db username + password are stored in web.config file. After a code review, one developer suggested that it's a security flaw; therefore connection strings should be kept somewhere else or encrypted. My argument is that web.config file is protected by IIS and Windows security which is the case. And another argument is that... C# / C Sharp |
| 35 4840 Making a database connection global by: Terry Jolly |last post by: Web Solution Goal: Have a global database connection Why: (There will be 30+ tables, represented by 30+ classes) I only want to reference the database connection once. I put the connection string in the web.config. I created a class with a static database connection and the class opens and closes the database. ASP.NET |
| 10 1728 application level database connection by: rich |last post by: I have a PHP5 application that accepts external messages, and inserts them into a database. It's rather high traffic.. the server could be receiving thousands at a time, and recently mysql has been locking up because it's exceeding the max_connections limit. I raised it for now, but that's only a temporary fix. My question is is there a way to make a connection to mysql that multiple instances of an object will use together? I'm under... PHP |
| 1257 Multiple Web Part consumer connection points by: =?Utf-8?B?TWFyYyBXb29sZnNvbg==?= |last post by: Hello, I can't seem to find any information on how best to implement multiple connections on a consumer web part and was wondering if anyone could point me in the right direction. I have three web parts: two containing grids of data (the providers) and one containing item detail (the consumer). All of the web parts implement a known interface to enable the connections, and the AllowsMultipleConnections attribute is set to 'true' on... ASP.NET |
| 1 5937 connection limit questions for web service on IIS 5.1 / xp by: jacobyte |last post by: HI have a few questions regarding IIS5.1 and the the 10 connection limit, If anyone can help clarify this I would greatly appreciate any help. - this is the situation. We host a site on a win2k3 server and are currently teaming up with another company who have a network of windows xp machines across the country, each one of these boxes has IIS 5.1, no web site is installed on the xp boxes instead its just a .net web service which sends data... IIS / Internet Information Server |
| 9 377 Connection pooling issue with pass through queries by: Bill E. |last post by: I'm creating an MS Access application that connects to a SQL Server 2005 database using pass-through queries and ADO to call stored procedures. I ran a SQL trace and started clicking through various forms to look at how things were being handled on the database. I noticed an alarming number of connections were opened as if there was absolutely no connection pooling. After several clicks I had 20 new connections. It appeared that a new... Microsoft Access / VBA |
| 9 6279 PDO: Switch database user without reopening connection by: Gordon |last post by: I want to add a feature to a project I'm working on where i have multiple users set up on my Postgres database with varying levels of access. At the bare minimum there will be a login user who only has read access to the users table so that users can log in. Once a user has been logged in successfully I want to escalate that user's access level to one appropriate to their role, which will include switching the postgres user they are... PHP |
| 8966
What is ONU? by: marktang |last post by: ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, well explore What is ONU, What Is Router, ONU & Routers main usage, and What is the difference between ONU and Router. Lets take a closer look ! Part I. Meaning of... General |
| 9331
Maximizing Business Potential: The Nexus of Website Design and Digital Marketing by: jinu1996 |last post by: In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is... Online Marketing |
| 1 9257 The easy way to turn off automatic updates for Windows 10/11 by: Hystou |last post by: Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,... Windows Server |
| 1 6749
Access Europe - Using VBA to create a class based on a table - Wed 1 May by: isladogs |last post by: The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will... Microsoft Access / VBA |
| 4567 Trying to create a lan-to-lan vpn between two differents networks by: TSSRALBI |last post by: Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls... Networking - Hardware / Configuration |
| 4823 Windows Forms - .Net 8.0 by: adsilva |last post by: A Windows Forms form does not have the event Unload, like VB6. What one acts like? Visual Basic .NET |
| 1 3278 transfer the data from one system to another through ip address by: 6302768590 |last post by: Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system C# / C Sharp |
| 2 2744
How to add payments to a PHP MySQL app. by: muto222 |last post by: How can i add a mobile payment intergratation into php mysql website. PHP |
| 3 2192
Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy by: bsmnconsultancy |last post by: In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive... General |
