Hi all,
I am writing the C# code function that take 2 parameters and access
the data in the MS SQL database and return a SqlDataReader object. In
my funtion, a SQL connection is established and open the connnection
before reading the data. Everything seem to be fine, but once I add the
close() function to close the sql connection, error come out. So I
leave out the close() function in my function. My code is showing below
-------------------------------------------------------------------
using System;
using System.Data;
using System.Data.Sql Client;
namespace ConsoleApplicat ion3
{
class Class1
{
[STAThread]
static void Main(string[] args)
{
string found = "c553a15f-15bd-4081-bd5d-89cfa2cc0505";
int photoId = 3;
SqlDataReader answer = ReadQuantity(fo und, photoId);
while(answer.Re ad())
{
int photoQuantity = Convert.ToInt16 (answer["Quantity"]);
Console.WriteLi ne("\t{0}", photoQuantity);
}
}
static SqlDataReader ReadQuantity(st ring cartId, int photoId)
{
SqlConnection thisConnection = new SqlConnection
(@"Data Source=(local); Integrated Security=SSPI;" +
"Initial Catalog=PhotoDB ");
thisConnection. Open();
SqlCommand ReadQuantity = thisConnection. CreateCommand() ;
ReadQuantity.Co mmandText = "SELECT Quantity FROM ShoppingCart WHERE
CartID = '"+cartId+"' AND PhotoID = '"+photoId+" '";
SqlDataReader thisReader = ReadQuantity.Ex ecuteReader();
// thisConnection. Close();
return thisReader;
}
}
}
------------------------------------------------------------------------
Could everyone tell me what happen to the sql connection if I don't
include the close function in my code. Would it close automatically
after timeout and would it be security threat???
Thanks for your time
Wing 3 1748
I think that DataReader is connection-oriented, meaning that you must
maintain the connection for the data to be returned. Basically a DataReader
returns on record at a time. This is different from a DataSet/DataAdapter
which loads all the records in memory.
"Wing" <li******@gmail .com> wrote in message
news:11******** **************@ z14g2000cwz.goo glegroups.com.. . Hi all,
I am writing the C# code function that take 2 parameters and access the data in the MS SQL database and return a SqlDataReader object. In my funtion, a SQL connection is established and open the connnection before reading the data. Everything seem to be fine, but once I add the close() function to close the sql connection, error come out. So I leave out the close() function in my function. My code is showing below
------------------------------------------------------------------- using System; using System.Data; using System.Data.Sql Client;
namespace ConsoleApplicat ion3 { class Class1 { [STAThread] static void Main(string[] args) { string found = "c553a15f-15bd-4081-bd5d-89cfa2cc0505"; int photoId = 3;
SqlDataReader answer = ReadQuantity(fo und, photoId); while(answer.Re ad()) { int photoQuantity = Convert.ToInt16 (answer["Quantity"]); Console.WriteLi ne("\t{0}", photoQuantity); } }
static SqlDataReader ReadQuantity(st ring cartId, int photoId) { SqlConnection thisConnection = new SqlConnection (@"Data Source=(local); Integrated Security=SSPI;" + "Initial Catalog=PhotoDB "); thisConnection. Open(); SqlCommand ReadQuantity = thisConnection. CreateCommand() ; ReadQuantity.Co mmandText = "SELECT Quantity FROM ShoppingCart WHERE CartID = '"+cartId+"' AND PhotoID = '"+photoId+" '"; SqlDataReader thisReader = ReadQuantity.Ex ecuteReader(); // thisConnection. Close(); return thisReader; }
} }
------------------------------------------------------------------------
Could everyone tell me what happen to the sql connection if I don't include the close function in my code. Would it close automatically after timeout and would it be security threat???
Thanks for your time
Wing
"Wing" wrote... I am writing the C# code function that take 2 parameters and access the data in the MS SQL database and return a SqlDataReader object. In my funtion, a SQL connection is established and open the connnection before reading the data. Everything seem to be fine, but once I add the close() function to close the sql connection, error come out. So I leave out the close() function in my function. My code is showing below
You didn't close the Reader before you closed the Connection...
There are several different solutions to your problem, and it comes down to
what design you really want for your system. There are basically two
different approaches for how to deal with connections, readers, etc, which
can be combined into several different solutions.
1. Open, deal with the data, and close it as fast as you can.
2. Keep it open during the whole session
Both have advantages and drawbacks depending on what tasks the application
should do, but I'll try to sketch two basic examples on how you could deal
with it.
----------------------------------------------------
1.
static ArrayList ReadQuantity(st ring cartId, int photoId)
{
ArrayList quantities = new ArrayList();
SqlConnection thisConnection = new SqlConnection
(@"Data Source=(local); Integrated Security=SSPI;" +
"Initial Catalog=PhotoDB ");
thisConnection. Open();
SqlCommand ReadQuantity = thisConnection. CreateCommand() ;
ReadQuantity.Co mmandText = "SELECT Quantity FROM ShoppingCart WHERE
CartID = '"+cartId+"' AND PhotoID = '"+photoId+" '";
SqlDataReader thisReader = ReadQuantity.Ex ecuteReader();
while(thisReade r.Read())
{
quantities.Add( answer["Quantity"]);
}
thisReader.Clos e();
thisConnection. Close();
return quantities;
}
----------------------------------------------------
1 + 2.
In this scenario you open the connection beforehand, and can keep it open
the whole session. Note that you then pass the connection to the method.
(Not really Recommended!)
static ArrayList ReadQuantity(st ring cartId, int photoId, SqlConnection
con)
{
ArrayList quantities = new ArrayList();
SqlCommand ReadQuantity = thisConnection. CreateCommand() ;
ReadQuantity.Co mmandText = "SELECT Quantity FROM ShoppingCart WHERE
CartID = '"+cartId+"' AND PhotoID = '"+photoId+" '";
SqlDataReader thisReader = ReadQuantity.Ex ecuteReader();
while(thisReade r.Read())
{
quantities.Add( answer["Quantity"]);
}
thisReader.Clos e();
return quantities;
}
----------------------------------------------------
Note that both examples deals with the actual data before returning.
Hmm, but I wonder if you really need a DataReader at all? Does the statement
really return more than one row, or could you suffice with using only the
ExecuteScalar method?
Could everyone tell me what happen to the sql connection if I don't include the close function in my code. Would it close automatically after timeout and would it be security threat???
That is depending on many things, but it will logically not close, even if
it times out from the server, so it's good practice in all cases to close
the connection as soon as possible when you're done with it.
I wouldn't see it as a bigger "security threat" to keep it open than to just
open and close it, but rather a possible misuse of resources. If the number
of simultaneous connections are limited, an unnecessary open connection can
block out other users, or even yourself, if you keep opening connections,
but never close them.
// Bjorn A
Thanks very much for your suggestion, help and time. very appreciated!
What I want the function ReadQuantity to do is to return 2 values in
the particular row (of caurse I need to change the SELECT statemant) by
return the SqlDataReader object. Now your exmaple remind me that I can
assign the value in SqlDataReader object into array and return the
array instead, and I can close the connection which no longer need.
Thanks again.
Wing This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Mudge |
last post by:
Hi,
My hosting provider only allows me to use 50 connections to my MySQL
database that my Web site will use.
I don't know what this 50 connections means exactly. Does this mean that
only 50 visitors to my Web site can access my database through my Web site
at one time?
Or does this mean that in my code I can only use 50 connections? and like
|
by: eScrewDotCom |
last post by:
eScrew
Welcome to eScrew!
eScrew is eScrew and this is eScrew story. eScrew will tell you eScrew
story if you promise eScrew to consider eScrew story as joke. eScrew
story is very funny. eScrew story is so funny that eScrew will have to
take break from time to time because eScrew needs some rest from
laughing. Oh boy, here it comes... eScrew funny laugh laughing
screaming crying must stop can not take any more this is killing eScrew...
|
by: eScrewDotCom |
last post by:
eScrew
Welcome to eScrew!
eScrew is eScrew and this is eScrew story. eScrew will tell you eScrew
story if you promise eScrew to consider eScrew story as joke. eScrew
story is very funny. eScrew story is so funny that eScrew will have to
take break from time to time because eScrew needs some rest from
laughing. Oh boy, here it comes... eScrew funny laugh laughing
screaming crying must stop can not take any more this is killing eScrew...
|
by: Utada P.W. SIU |
last post by:
Dear All,
My asp run smoothly before. But, now, I found the ASP page is not smoothly
recently.
Sometime if the ASP is requested to connect to Access, the page will prompt
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
General error Unable to open
registry key 'Temporary (volatile) Jet DSN for process 0xdd8 Thread 0xae0
DBC 0x3a85074 Jet'.
|
by: eScrewDotCom |
last post by:
www.eScrew.com
eScrew
Welcome to eScrew!
eScrew is eScrew and this is eScrew story. eScrew will tell you eScrew
story if you promise eScrew to consider eScrew story as joke. eScrew
story is very funny. eScrew story is so funny that eScrew will have to
take break from time to time because eScrew needs some rest from
laughing. Oh boy, here it comes... eScrew funny laugh laughing
| |
by: 0to60 |
last post by:
I have a question about socket programming in general.
Exactly what happens behind the scenes when I one socket connects to a
different socket in listen mode? Using the dotnet framework, I create a
socket, bind it to a port, put it in listen mode, and then n sockets can
connect to it. The code:
Socket newSocket = listeningSocket.Accept();
returns a socket. I can communicate on newSocket, and listeningSocket goes
|
by: Ellen Manning |
last post by:
Using A2K and adp. Is it necessary to open a connection (to SQL
Server 2000) everytime you call a stored procedure or open a
recordset? Or will opening a connection one time do, like at
Form_Open?
I have a number of combo boxes that call the following public sub when
the NotInList event is fired:
Public Sub InsertItem(strCommandText As String, strParameterName As
String, _
|
by: Tom |
last post by:
1. I drag the sqlDataAdapter and sqlConnection from the
tool bar to the web form.
It creates automatically:
protected System.Data.SqlClient.SqlDataAdapter
sqlDataAdapter1;
protected System.Data.SqlClient.SqlCommand
sqlSelectCommand1;
|
by: Alvin Bruney [MVP] |
last post by:
with the using construct, exit from scope calls dispose on said object. But
what happens in a connection pooling scenario? Is the run-time smart enough
to realize that Object Pooling is being used so it will not call dispose and
instead flag the object similar to what close() does when pooling is in
effect. Or does it really go ahead and shut down the object? If it does,
then isn't it circumventing the object pooling? Does anybody know?
...
|
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, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
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...
|
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,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
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...
| |