Is It Necessary To Open And Close Connections When Using SqlDataAdapter
Jan 28, 2013
Is this necessary to open and close connection when i am using SqlDataAdapter and DataSet to get data from  from table from  databse in sql server? Which one will be better to use in the following code. Code1 or Code2.
Code: 1 With open and colse connection
public DataSet GetFAcadSlidingImage()
{
SqlConnection con = GetConnection();
cmd = new SqlCommand("Pro_GetFAcadSlidingImage", con);
cmd.CommandType = CommandType.StoredProcedure;
[Code].....
Code:2 Without open and colse connection
public DataSet GetFAcadSlidingImage()
{
SqlConnection con = GetConnection();
cmd = new SqlCommand("Pro_GetFAcadSlidingImage", con);
cmd.CommandType = CommandType.StoredProcedure;
I am trying to determine if I have a database connection leak. So I need to see the number of open connections. I have some simple test code that creates a leak:
protected void Page_Load(object sender, EventArgs e) { for(int i = 0; i < 100; i++) { SqlConnection sql = new SqlConnection(@"Data Source=.SQLExpress;UID=sa;PWD=fjg^%kls;Initial Catalog=ABC"); sql.Open(); } }
Note there is no .Close and this does infact crash after being run 3 times in quick succession.
In order to measure the leak I am running the Performance monitor and measuring SQLServer: General Statistics/User Connections:
However, these seem to be zero when I run my code:
What should I change to actually see the connections?
ANSWER
I have approved an answer below. Even though it doesn't use the performance tools, its good enough for my use. Bottom line is I wanted to see how many connections remain open after opening a web page and this did the trick.
We have an internal ASP.Net 3.5 application that opens Pervasive SQL databases, reads the data, and then closes the connection. Unfortunately, when the user exits the application, and even when they close their browser, the w3wp process seems to be holding the PSQL database connections open, which is causing licensing issues for us. I would like the w3wp process to release the database connections once the last user has exited from the ASP.Net application. How can I get w3wp to release the connections?
Someone told me that the Powershell command, "gps w3wp | kill" will stop the w3wp process. Is there any way I can automate this, so that the process will be killed automatically when the last user exits our ASP.Net application?
When should i open and close the database connection.and which are best data classes (for e.g dataset, datatable etc) should i use when connection is open or close.
does anyone have any working code that uses javascript to open and close a cpe when the CPE is in a masterpage? It seems like the javascript never is able to find the CPE when it is on a master page. I have an DropDownList on the page and when certain items are selected, i want to collapse or expand when others are selected. It seems to work fine on a regular page, but not when it is a page nested in a master page. It is adding a suffix to the CPE and javascript is unable to find it.
In PageA, there is a button to open a crystal report. I want to add a HTML button into crystal report to close report(browser) and back to PageA. How to use javascript to do this job?
below i am using dataset and con.oen and close. but i read that dataset doesnt require con.open/close since it does that autoatically.now my project is complete and implemented with opens and closes for datasets.here is code for all datasets are liek this:-
[Code]....
1)so is what i have done a bad coding practise?2)will it make the application pool to hang?3)should i remove all open/close from the files?
Dim ds As DataSet = New DataSet con.Open() Dim dad As SqlDataAdapter = New SqlDataAdapter(cmd)
I am tring to open the panel with clicking linkbutton and to close the same panel using that link button .How can i write close and open in one event .
In PageA, there is a button to open a crystal report. I want to add a HTML button into crystal report to close report(browser) and back to PageA. How to use javascript to do this job?
In Visual Studio Team Edition there is a load test tool where you can select unit tests, integration tests etc... to form scenarios which are then used in a load test session with different configurations for example varying usage levels.
Looking at for example the NUnit framework, it would be possible to develop a form of such a tool, along with a base load testing code base, but I was interested if anyone knows of any open source project which has has already done a similar thing which could then be of use for this situation and possibly extended?
I have a login page that is loaded in popup window (colorbox) and after user logs in it should be closed and parent window should be loaded with new page.
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click Do While reader.Read() If Trim(reader("ZAP").ToString()) = Trim(textBoxZap.Text) Then Session("A") = "1" Response.Redirect("~/default.aspx") Else lblErr.Text = "incorect" End If Loop
So once the buton is clicked and user name and password is corect popup window should be closed and user redirected to default aspx.
I have a series of collapsible panel extenders on my page and each time the page opens or postsback the panels open for a time and then close. Is there a way to prevent this?
I have website which runs multiple threads. When user close the browser but threads are still running. How to kill/stop all thread in asp.net on browser close.
I have a web page with paypal. In the page user add items to list then buy it. when user add item to list i block that item in stock until user buy or delete that item.Now my problem is when user add item in the list and then close the broswer from IE close button , the item get blocked. I want to rollback the added items when user close the browser from IE button.
I have a theoretical question.i have a function in DAL that does a select sql query. and these are my questions:
I was told that it's not appropriate to pass datasets between layers. so how should i pass the results? which value should i return to the BLLBAL (i thought of a List<>).? and to the presentation layer.
currently my implemention is like that:
SqlCommand cmd = new SqlCommand("getRequestById", con); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("@requestId", rId)); SqlDataAdapter datAdp = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); datAdp.Fill(ds,"getRequestById"); List<String> rs = new List<string>(); for (int i=0; i<11; i++) { rs.Add((Convert.ToString(ds.Tables[0].Rows[0].ItemArray.GetValue(i)))); } return (rs);
is it better to create xsd file in VS2010 with only fill (all other implemtation i'll do on my own) property.
I added New Item, DataSet, over that i added tableadapter with stored procedure. i declared that it will do only the fill property(without returning the data). i want to implement all the other properties by demand in my way.
i wrote in the dal test.getRequestById1DataTable dt1 = new test.getRequestById1DataTable() how can i get the data and pass it between the layers?
I have the following sub that I need help with an error on. When run error message is saying that the parameter called by the stored proc was not supplied. Am I missing something? I can't seem to find it.....
Would anyone know how to pass a variable to SqlDataAdapter. Here is my code.
TextBox1.Text = 1 Dim textboxval As String textboxval = TextBox1.Text connection.Open()Using connection As New SqlConnection("Data Source=CHRIS-PCSQLEXPRESS;Initial Catalog=TorGHL;Integrated Security=True") Dim dapubcb As New SqlDataAdapter("SELECT [BarName], [MenuSubId], [Address], [PhoneNo], [BarURL], [Email] [BarDescription], [Id], [bg_long], [bg_lat] FROM [BarDetails] WHERE [MenuSubId] = texboxval ", connection)
I am trying to use a SqlDataAdapter to fill a DataTable, which I use as a data source to a DataGrid. The query is fine, at least when I run it manually in SSMSE. When I do the fill operation, the table gets the right columns, but no rows. The most frustrating part of this is that the code is identical (with a different query) on a different page. I cannot post the query, but my initialization code looks like this:
SqlCommand areaDAC = new SqlCommand (areaQuery, connection); areaDAC.Parameters.Add (new SqlParameter ("@param", System.Data.SqlDbType.NVarChar, 50)); m_areaDataAdapter = new SqlDataAdapter (areaDAC);
At this point, table has the right number of columns and no rows. I know the parameters are being added correctly, I know that data exists for the given query.
I´m having a code making some txt-files out of a query for each af a dropdownlist.
I need to name the the output file to a name including the distinct data of a column in my query.
[Code]....
The output file name should include the data from the query of agent_input36. It could be several data. I´m trying to make an order file. The orderfile name (.txt) should include the possible names of agent_input36. It could look like. YYY_"agent_input36"_Order_"Date"_CCC.txt. Can I retrieve the data of this column from a sqlDataAdapter into a stringvalue?
