asked on

Connection to SQL Server 2005

Hi experts,

I have a C# windows form application and I connect to SQL server 2005 using two methods:
1)
SqlCommand cmd = new SqlCommand("select ...", sqlConnectionObject);
2)
SqlDataAdapter mDA = new SqlDataAdapter(("select ...", "Data Source...");
mDA.Fill(someDataTable);

A process is created in SQL server after the first method is called. I have used a single object to limit
the connection. Now, when I called the second method, a new process is created. As a result, there
are two processes for each application in the SQL server!

For SqlDataAdapter, I understand that C# will open a connection if it is not connected,
and closed automatically after use.
C# will remain connect if there is already an opened connection after use.

Question:
How can I limit my application to have only one connection in the SQL server?

regards

Gautham Janardhan

always use one connection object

Gautham Janardhan

u have to write a data acess layer which the whole application will use thru out

Gautham Janardhan

public frmMain()
            {
                  InitializeComponent();
                  AppStartUp.ConnectFromConfigFile();


            }

public class AppStartUp
      {
            public static void ConnectFromConfigFile()
            {
                  SQLDBProvider.DataAccess.Connect(" DATA SOURCE="
                        + SC.ConfigurationSettings.AppSettings["SERVER"] + ";"
                        + " PERSIST SECURITY INFO=TRUE;"
                        + " UID=" + SC.ConfigurationSettings.AppSettings["USERNAME"] + ";"
                        + " PWD=" + SC.ConfigurationSettings.AppSettings["PASSWORD"] + ";"
                        + " DATABASE=" + SC.ConfigurationSettings.AppSettings["DATABASE"]);



            }
      }

novknow

ASKER

Am I doing the correct thing? :

// my class
public sealed class SingleCon
{
public readonly static string mString = "Data Source.........";

private static readonly SqlConnection mSQLConn = new SqlConnection(mString);

public static void Disconnect()
{
if (mSQLConn.State != ConnectionState.Closed)
mSQLConn.Close();
}

public static SqlConnection SQLConnection
{
get
{
try
{
if (mSQLConn.State != ConnectionState.Open)
mSQLConn.Open();
}
catch (SqlException ex)
{
// ...
}

return mSQLConn;
}
}
}

// Connection code #1 :
SqlCommand cmd = new SqlCommand("select...", SingleCon.SQLConnection);

// Connection code #2 :
SqlDataAdapter mDA = new SqlDataAdapter("select...", SingleCon.mString);

Hi gauthampj, appreciate if you could provide example on your second point.

regards

novknow

ASKER

Hi gauthampj,

What is SQLDBProvider?
How do I connect to SQL server with SqlDataAdapter and SQLDBProvider?

regards

ASKER CERTIFIED SOLUTION

Gautham Janardhan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

novknow

ASKER

Hi gauthampj,

Thanks for providing the example.
While exploring your example, could you point to me what's wrong with my code (why is it creating an additional process in the SQL server)?

regards

Gautham Janardhan

1)
SqlCommand cmd = new SqlCommand("select ...", sqlConnectionObject);
2)
SqlDataAdapter mDA = new SqlDataAdapter(("select ...", "Data Source...");--- here new connetion will be made b'coz u r not asking the adaptor to use the connection u have already created....

using my code u could call the adpators overload

SqlDataAdapter mDA = new SqlDataAdapter("Comman',Connection)

or the command s overload

SQLCommand mDA = new SQLCommand ("Comman',Connection)

connection would be CDataBase.Handle.Connection in my case