.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
MarieAdela
Imran Ghani
Post New Web Links

Disposing a SqlConnection (ADO.NET)

Posted By:      Posted Date: October 15, 2010    Points: 0   Category :.NET Framework
 

Hello guys,

I had a question relating to the disposal of a SQL connection. I have some code that checks whether a certain database is present on a SQL Server instance. This code is being called in an installer, so what I actually want it to do is ensure that there isn't already a database on the server with the same name than the one that will be created in the installer. If such a database exists, the user will be notified so that he can remove the database manually, and then click OK or something to let the installer proceed. I extracted the relevant piece of code. This snippet can be used in a console application to reproduce the problem:

static void Main(string[] args)
{
	try
	{
 		string connectionString = "Data Source=.;Initial Catalog=MyDatabase;User Id=MyUser;Password=MyPassword;";

		using (SqlConnection connection = new SqlConnection(connectionString))
		{
			connection.Open();
			connection.Close();
			//connection.Dispose();
		}

		Console.WriteLine(
                                     
                                            
                                        

View Complete Post


More Related Resource Links

Alternatives for SqlConnection()

  
Hello ...I am using databases in my project.I always use SqlConnection() To open a connection then use the connection to interact database.But I have heard somewhere that opening SQLconnection() take a time.  and if on different pages of projects I repeatedly Connect database then It will take long access time always....So I want to know that if there is any other methods which don't use the SQLConnection to interact database and increase the response time of database...

'SqlConnection' Error

  
Within the button code (Default.aspx.vb), there is an undefined 'SqlConnection.' What is the cause of this error? Go to the following URL link, download the 'High Res' Visual Studio snapshots at: http://cforedu.com/snap.pdf The button code written in VB code reads as follows:Partial Class _Default Inherits System.Web.UI.Page Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click Public Function strLong(ByVal pstrText As String) As String '''/ Extract just the 0-9 chars from a string Dim strTemp As String = Nothing Dim lngI As Long = 0 strTemp = "" For lngI = 1 To Strings.Len(pstrText) If Strings.InStr(1, "1234567890", Strings.Mid(pstrText, lngI, 1)) > 0 Then strTemp = strTemp & Strings.Mid(pstrText, lngI, 1) End If Next Return Strings.Left(strTemp, 20) End Function ' strLong Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("sqlapplicationConnectionString").ToString()) Try Dim cmd As SqlCommand = conn.CreateCommand() cmd.CommandText = "INSERT INTO Application (" & " First, Last, City, State)"

'SqlConnection' Error

  
Within the button code (Default.aspx.vb), there is an undefined 'SqlConnection.' What is the cause of this error? Go to the following URL link, download the 'High Res' Visual Studio snapshots at: http://cforedu.com/snap.pdf The button code is written in VB code and reads as follows: Partial Class _Default Inherits System.Web.UI.Page Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click Public Function strLong(ByVal pstrText As String) As String '''/ Extract just the 0-9 chars from a string Dim strTemp As String = Nothing Dim lngI As Long = 0 strTemp = "" For lngI = 1 To Strings.Len(pstrText) If Strings.InStr(1, "1234567890", Strings.Mid(pstrText, lngI, 1)) > 0 Then strTemp = strTemp & Strings.Mid(pstrText, lngI, 1) End If Next Return Strings.Left(strTemp, 20) End Function ' strLong Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("sqlapplicationConnectionString").ToString()) Try Dim cmd As SqlCommand = conn.CreateCommand() cmd.CommandText = "INSERT INTO Application (" & " First, Last, City, State)" & ") VALUES (" & " @First, @Last, @City, @State" cmd.P

The type initializer for 'System.Data.SqlClient.SqlConnection' threw an exception.

  
 Hi all,When i run my project on my own computer, it works fine. But when i deploy it to server, it gives me this error. I can run my other dts and sp, but i get this error on this.. The type initializer for 'System.Data.SqlClient.SqlConnection' threw an exception. I googled it but could not find a solution. How can i solve this problem? 

disposing SPSite and SPWeb objects?

  
hi          can i code using(SPSite oSite=SPContext.Current.Site) {    using(SPWeb oWeb=oSite.OpenWeb()) {} } or should i code like this SPSite oSite=SPContext.Current.Site; using(SPWeb oWeb=oSite.Openweb()){} which is better? i am trying to code this inside ItemAdding Event handler. please suggest

How to just define connection string, sqlconnection and sqlcommand once and use it over and over..

  
I'm curious if there is a way to in the web.config. or somewhere else, to set up "the basics" for executiting various sql queries and stored procedures throughout a website, with out having to keep identitifying the connectionstring, sql connection, command, etc.   I'm thinking back to my old ColdFusion days, where you would declare all this in the application.cfm, and then it was quickly and easily reused without needing to retype the info.   Any suggestions/samples on doing this in VS 2010?   

Declaring the SqlConnection and SqlCommand objects in a webform without CodeBehind

  
Declaring the SqlConnection and SqlCommand objects in a webform without CodeBehindI am trying to query data from a MS SQL server with a C# web form without "CodeBehind". I want to write the information to a table in the web form's body.I am having trouble declaring the SqlConnection and SqlCommand objects that connect to the server.How do you use "using System;", "using System.Data;", "using System.Data.SqlClient" inside the script element of an .ASPX file?I know how to do it with JavaScript and ADO. (code below)---------------------------------------------------------------------------------------------------<html><head><table><%@ language="javascript" %><%var sqlstr = " ";var conn_str = " ";var AdOpenForwardOnly = 0;var adLockReadOnly = 1;var rs = new ActiveXObject("ADODB.Recordset");var conn = new ActiveXObject("ADODB.Connection");conn_str = "PROVIDER=SQLOLEDB;Server=my_server;" +"Database=db_myDatabase;User ID=myUserID;" +"Password=myPassword;"sqlstr = "SELECT * FROM [myTable]";conn.open(conn_str, "", "");rs.open(sqlstr, conn, AdOpenForwardOnly, adLockReadOnly);while(!rs.eof){Response.write("<tr><td>" + rs(0));Response.write("</td><td>"+ rs(1));Response.write("</td><td>"+ rs

How does SqlConnection manages IsolationLevel?

  

This MSDN article states that:

An isolation level has connection-wide scope, and once set for a connection with the SET TRANSACTION ISOLATION LEVEL statement, it remains in effect until the connection is closed or another isolation level is set. When a connection is closed and returned to the pool, the isolation level from the last SET TRANSACTION ISOLATION LEVEL statement is retained. Subsequent connections reusing a pooled connection use the isolation level that was in effect at the time the connection is pooled.

The SqlConnection class has no member that may hold the isolation level. So how does a connection know what isolation level to run in???

The reason I'm asking this is because of the following scenario:

  1. I opened a transaction using TransactionScope in Serializable mode, say "T1".
  2. Opened a connection for T1.
  3. T1 is finished/disposed, connection goes back to connection pool.
  4. Called another query on same connection (after getting it from connection pool) and this query runs in serializable mode!!!

Problem:

  1. How does the pooled

SEHException in Finalyzer while disposing SoftHandles

  

 

Hi All,

     I am hitting with SEHException while disposing Softhandles which are internal to .NET Framework. Can you please suggest me a way to how to solve this issue ? When i check the stack, almost all the softhandles are pointing to the same object, that surprises me more. Please do check the Windbg output :

/*****************************************************************************************************/

!threads output

/*****************************************************************************************************/

  0    1 2fd4 0015b790      a020 Enabled  00000000:00000000 00163f78     0 MTA
   2    2 3174 0016e5f0      b220 Enabled  00000000:00000000 00163f78     0 MTA (Finalizer) System.Runtime.InteropServices.SEHException (0c3e9468)
   3    5 4be8 00195d30      b220 Enabled  00000000:00000000 00163f78     0 MTA
   4    7 40ac 001b8798      b220 Enabled  00000000:00000000 00163f78     0 MTA
   6    9 4b50 001c6d08   200b220 Enabled  0000

Trusted_Connection=false is not working in Sqlconnection

  

Hi,

I am not sure if this is the right place to post this problem.

Briefly I have a custom Sqlconnection string in my infopath 2010 form which is:

SqlConnection

 

con = new SqlConnection("Server=SQL2;Database=Payroll;User ID=xxxx;Password=xxxxxxx;Persist Security Info=false;Trusted_Connection=false");

Trusted_Connection=false indicates that it uses user ID and Password from the string to connect to the MS SQL database.

System.Data.SqlClient.SqlConnection'

  

Hello

Please look at the error given below

'System.Data.SqlClient.SqlConnection' does not contain a definition for 'Fill' and no extension method 'Fill' accepting a first argument of type 'System.Data.SqlClient.SqlConnection' could be found (are you missing a using directive or an assembly reference?)


Code which directing me error

using System;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using System.ComponentModel;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.IO;

public partial class Display_Image_IO : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        GridView1.DataSource = fetchImages();
        GridView1.DataBind();
    }

    public DataTable fetchImages()
    {
        SqlConnection da = new SqlConnection(GetConnectionSt

Where should I keep SQLConnection object ?

  

Hello,

What's the best place to keep database connection object like SQLConnection or ISession if I can choose only Application, Session or Items objects. And why ?

Thanks in advance.


Disposing things

  

Hi Everybody,

I have noobishly written an ASP.Net without any Dispose methods for unmanged objects. The object I am using are Sqlconnection, Sqlcommand and SqlDataReader. My application on my shared host shows very high resource usage, and I'm the only one using it. Which seems to me like a badly designed app. Because it's done in C# ASP.Net 3.5 the garbage collection should handle managed objects without my intervention correct? But it doesn't handle unmanaged objects I think.

So I had a look at FxCop and ran it on my app. But it is coming up with no error for "Implements IDisposable correctly" in my whole app. This seems odd since I have not used the Dispose method once. So here is a section of my code for example. Is this code leaking memory? most of my code is the same with different params tables etc.

public List<Classy> Get_Classy(int classyId)
    {
        List<Classy> classyList = new List<Classy>();
        using (SqlConnection connection = new SqlConnection(connStr))
        {
            connection.Open();
            SqlCommand command = new SqlCommand();
            command.CommandText = "SELECT * FROM ClassyStore WHERE classyId = @classyId";
            command.CommandType = CommandType.Text;
            command.Connection = connection;
          

SqlConnection Error

  

Hi,
I have recently changing my coding in my program to use SqlConnection. Previously I was connecting to SQL Server database using ODBC connection.

            public void Connect()
            {
                  try
                  {
                        connection = new SqlConnection("Data Source=10.100.11.41,4848;Initial Catalog=XXXXDB;User Id=XXXUser;Password=xxxxx;");
                        connection.Open();
                        command.Connection = connection;
                  }
                  catch (Exception ex

Disposing when hower AND CTRL is activated AND left mouse button is pressed ????

  

I want to let the user get rid of some designelements (PictureBoxes) on a form very fast.

This code can do it IF the user holds down the CTRL-key AND moves the mouse cursor over the designelements.

    private void signal_MouseHover(object sender, EventArgs e)
    {
      Cursor.Current = Cursors.Hand;
      if (commonThings.ctrlActivated)
      {
        ((PictureBox)sender).Dispose();
      }
    }

I have two problems witch I can't solve:

1) I want to secure wrong deleting of designelements by ALSO test that the LEFT-mouse key is pressed togather with CTRL (there is no "buttons" with the e !)

2) the Hover-event is a little delayed, so the deleting is in fact a little slow

How can this be done ?

 


Best regards KSor, Denmark

disposing a Modelvisual (reset viewport and load new model)

  

Hello,

I am constructing a 3D model viewer, and all is working quite well at a first glance. But when i want to load a new model the memory will not reset so after two or three "new loads" the program process breaks the 2Gb limit and exits. I have used "perfmon" to monitor the private bytes used by my program and I can see that almost no memory is returned between loads (with one exception, I'll return to that in a sec)

in my model class i have variables such as:

  • GeometryModel3D
  • MeshGeometry3D
  • ModelVisual3D
  • Bitmap
  • DiffuseMaterial
  • List<string>
  • Int32Collection
  • Point3DCollection
  • PointCollection

And when trying to create a new model I first remove the current model with the following:

 

 ModelVisual3D m;

 for
 (int
 i = myViewport.Children.Count - 1; i >= 0; i--)
 { 
 m = (ModelVisual3D)myViewport.Children[i]; 

 if
 (m.Content is
 DirectionalLight == false
) 
  myViewport.Children.Remove(m); 
 } 

 

as seen at http://ww

'SqlConnection' is not defined

  

'SqlConnection' is not defined in the behind code as follows:

 

 

Source Error:

Line 22:         ' we don't need to explicitly dispose the object in the code, 
Line 23:         ' the using statement takes care of it.
Line 24:         Using conn As New SqlConnection(ConfigurationManager.ConnectionStrings("subscriberConnectionString").ToString())
Line 25:             ' Create a DataSet object.
Line 26:             Dim dsMenu1 As New DataSet()


Source File: I:\Websites\CMP\Host\Fox Backup\Default.aspx.vb    Line: 24

Snippet reads as follows:

 

Imports System.Web.DynamicData


Partial Class _Default
    Inherits System.Web.UI.Page

        
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
        Dim visibleTables As System.Collections.IList = MetaModel.Default.VisibleTables
        If (visibleTables.Count = 0) Then
            Throw New InvalidOperationException("There are no accessible tables. Make sure that at least one data model is registered in Global.asax a" & _
        
Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend