Can I use same SQL connection string multiple times in my application?

Question

I am new to SQL. I am building application using C# which uses local SQL Server to read/write data. I only have one database and the connection string is always the same when I connect to SQL Server.

I have 9 windows forms in my project application and each form uses the same connection string and in some forms I use the same connection multiple times. Can I use the same connection string multiple times in the same form ? Thank you

Here is the connection string:

SqlConnection cn = new SqlConnection(@"Data Source=localhost; AttachDbFilename=E:\myDB\DB1.mdf; trusted_connection=yes

Answer 1

yes you can use same by storing it inside web.config file or app.config file in case of windows form application and then reuse it

System.Configuration.ConfigurationManager.
ConnectionStrings["connectionStringName"].ConnectionString;

where connectionStringName is name of connection string stored in web.config file

Answer 2

You can use one connection for all data operations but a better way would be to remove all data operations from the forms and place these operations in a class that handles the data operations. Also I would advice along with the above to use a connection for each method were each methods connection shares the connection string. Here is an example for a code sample I am writing for MSDN. Note each methods connection is not shared, it's local to the method and a using statement is used which will close the connection when finished. For simply apps having one connection reused is fine but once working with a more complex app with many users consider conserving resources and keep a connection open only long enough for the intended operation.

Conceptual example.

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace DataOperations_cs
{
    public class BackendOperations
    {
        public string ConnectionString { get; set; }
        public DataTable DataTable { get; set; }
        public List<string> ContactTitles { get; set; }
        public Exception Exception { get; set; }

        public bool HasException
        {
            get
            {
                return this.Exception != null;
            }
        }

        public bool RetrieveAllRecords()
        {
            this.DataTable = new DataTable();
            try
            {
                using (SqlConnection cn = new SqlConnection { ConnectionString = this.ConnectionString })
                {
                    using (SqlCommand cmd = new SqlCommand { Connection = cn, CommandType = CommandType.StoredProcedure, CommandText = "dbo.[SelectAllCustomers]" })
                    {
                        try
                        {
                            cn.Open();
                        }
                        catch (SqlException sqlex)
                        {

                            if (sqlex.Message.Contains("Could not open a connection"))
                            {
                                this.Exception = sqlex;
                                return false;
                            }
                        }

                        this.DataTable.Load(cmd.ExecuteReader());
                    }
                }

                if (ContactTitles == null)
                {
                    RetrieveContactTitles();
                }

                this.Exception = null;
                return true;
            }
            catch (Exception ex)
            {
                this.Exception = ex;
                return false;
            }
        }

        public bool RetrieveAllRecordsbyContactTitle(string contactType)
        {
            this.DataTable = new DataTable();
            try
            {
                using (SqlConnection cn = new SqlConnection { ConnectionString = this.ConnectionString })
                {
                    using (SqlCommand cmd = new SqlCommand { Connection = cn, CommandType = CommandType.StoredProcedure, CommandText = "dbo.ContactByType" })
                    {
                        cmd.Parameters.Add(new SqlParameter { ParameterName = "@ContactTitleType", SqlDbType = SqlDbType.NVarChar });
                        cmd.Parameters["@ContactTitleType"].Value = contactType;
                        cn.Open();
                        this.DataTable.Load(cmd.ExecuteReader());
                    }
                }

                this.Exception = null;
                return true;
            }
            catch (Exception ex)
            {
                this.Exception = ex;
                return false;
            }
        }

        public bool RetrieveContactTitles()
        {
            if (ContactTitles != null)
            {
                return true;
            }

            try
            {
                using (SqlConnection cn = new SqlConnection { ConnectionString = this.ConnectionString })
                {
                    using (SqlCommand cmd = new SqlCommand { Connection = cn, CommandType = CommandType.StoredProcedure, CommandText = "dbo.[SelectContactTitles]" })
                    {
                        cn.Open();
                        SqlDataReader reader = cmd.ExecuteReader();
                        if (reader.HasRows)
                        {
                            this.ContactTitles = new List<string>();
                            while (reader.Read())
                            {
                                this.ContactTitles.Add(reader.GetString(0));
                            }
                        }
                    }
                }

                this.Exception = null;
                return true;
            }
            catch (Exception ex)
            {
                this.Exception = ex;
                return false;
            }
        }

        public int AddCustomer(string CompanyName, string ContactName, string ContactTitle)
        {
            try
            {
                using (SqlConnection cn = new SqlConnection { ConnectionString = this.ConnectionString })
                {
                    using (SqlCommand cmd = new SqlCommand { Connection = cn, CommandType = CommandType.StoredProcedure, CommandText = "dbo.InsertCustomer" })
                    {
                        cmd.Parameters.Add(new SqlParameter { ParameterName = "@CompanyName", SqlDbType = SqlDbType.NVarChar });
                        cmd.Parameters.Add(new SqlParameter { ParameterName = "@ContactName", SqlDbType = SqlDbType.NVarChar });
                        cmd.Parameters.Add(new SqlParameter { ParameterName = "@ContactTitle", SqlDbType = SqlDbType.NVarChar });
                        cmd.Parameters.Add(new SqlParameter { ParameterName = "@Identity", SqlDbType = SqlDbType.Int, Direction = ParameterDirection.Output });

                        cmd.Parameters["@CompanyName"].Value = CompanyName;
                        cmd.Parameters["@ContactName"].Value = ContactName;
                        cmd.Parameters["@ContactTitle"].Value = ContactTitle;
                        cn.Open();
                        var affected = cmd.ExecuteScalar();

                        this.Exception = null;
                        return Convert.ToInt32(cmd.Parameters["@Identity"].Value);
                    }
                }
            }
            catch (Exception ex)
            {
                this.Exception = ex;
                return -1;
            }
        }

        public bool RemoveCustomer(int Indentifier)
        {
            using (SqlConnection cn = new SqlConnection { ConnectionString = this.ConnectionString })
            {
                using (SqlCommand cmd = new SqlCommand { Connection = cn, CommandType = CommandType.StoredProcedure, CommandText = "dbo.[DeleteCustomer]" })
                {
                    cmd.Parameters.Add(new SqlParameter { ParameterName = "@Identity", SqlDbType = SqlDbType.Int });
                    cmd.Parameters.Add(new SqlParameter { ParameterName = "@flag", SqlDbType = SqlDbType.Bit, Direction = ParameterDirection.Output });

                    cmd.Parameters["@Identity"].Value = Indentifier;
                    cmd.Parameters["@flag"].Value = 0;

                    try
                    {
                        cn.Open();
                        var affected = cmd.ExecuteNonQuery();
                        this.Exception = null;
                        if (Convert.ToBoolean(cmd.Parameters["@flag"].Value))
                        {
                            return true;
                        }
                        else
                        {
                            return false;
                        }
                    }
                    catch (Exception ex)
                    {
                        this.Exception = ex;
                        return false;
                    }
                }
            }
        }

        public bool UpdateCustomer(int PrimaryKey, string CompanyName, string ContactName, string ContactTitle)
        {
            try
            {
                using (SqlConnection cn = new SqlConnection { ConnectionString = this.ConnectionString })
                {
                    using (SqlCommand cmd = new SqlCommand { Connection = cn, CommandType = CommandType.StoredProcedure, CommandText = "dbo.[UpateCustomer]" })
                    {
                        cmd.Parameters.Add(new SqlParameter { ParameterName = "@CompanyName", SqlDbType = SqlDbType.NVarChar });
                        cmd.Parameters.Add(new SqlParameter { ParameterName = "@ContactName", SqlDbType = SqlDbType.NVarChar });
                        cmd.Parameters.Add(new SqlParameter { ParameterName = "@ContactTitle", SqlDbType = SqlDbType.NVarChar });
                        cmd.Parameters.Add(new SqlParameter { ParameterName = "@Identity", SqlDbType = SqlDbType.Int });
                        cmd.Parameters.Add(new SqlParameter { ParameterName = "@flag", SqlDbType = SqlDbType.Bit, Direction = ParameterDirection.Output });

                        cmd.Parameters["@CompanyName"].Value = CompanyName;
                        cmd.Parameters["@ContactName"].Value = ContactName;
                        cmd.Parameters["@ContactTitle"].Value = ContactTitle;
                        cmd.Parameters["@Identity"].Value = PrimaryKey;
                        cmd.Parameters["@flag"].Value = 0;

                        cn.Open();
                        var affected = cmd.ExecuteNonQuery();
                        this.Exception = null;

                        if (Convert.ToBoolean(cmd.Parameters["@flag"].Value))
                        {
                            return true;
                        }
                        else
                        {
                            return false;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                this.Exception = ex;
                return false;
            }
        }
    }
}

Answer 3

Yes you definitely can and the best approach is to define the connection string in web.config or app.config and then read them to your application like

System.Configuration.ConfigurationManager.ConnsectionStrings["CS"].ConnestionString

  <connectionStrings>
    <add name="CS" connectionString="Data Source=localhost; AttachDbFilename=E:\myDB\DB1.mdf; trusted_connection=yes" providerName="Sysem.Data.SqlClient"/>
  </connectionStrings>

Answer 4

There is a quite intelligent mechanism behind: Connection Pooling. A connection remains available for a while. If you need a connection again and you pass in exactly the same connection string (case sensitive) the same connection will be re-used.

That means:

• Yes, you may use one "global" connection in your application
• It won't make a difference in most cases :-)

Answer 5

Yes you can. Although, you might want to look at ways of not having to repeat the code all of the time, that way if the connection string changes you only have to change it once, not several times. One way could be to have the connection string in a config files. You could have a static instance of a class with the connection string in it or a simple connection factory.

public static class ConnectionFactory{
    private static string connectionString = "connection string"; //You could get this from config file as other answers suggest.

    public static SqlConnection GetConnection(){
         return new SqlConnection(connectionString);
    }
}

Not tested so may have some syntax errors.

Answer 6

This is the best strategy:

In your application create a static class with getConnection method

public class StaticContext
{
    public static SqlConnection getConnessione()
    {
        string conn = string.Empty;
        conn = System.Configuration.ConfigurationManager.ConnectionStrings["connectionStringName"].ConnectionString;
        SqlConnection aConnection = new SqlConnection(conn);
        return aConnection;
    }
}

In each form when you have needed a connection, use this way:

try
{
    try
    {
        conn = StaticContext.getConnessione();

        SqlCommand aCommand = new SqlCommand("SELECT.....", conn);

        conn.Open();
        aReader = aCommand.ExecuteReader();



        while (aReader.Read())
        {
            //TODO
        }



    }
    catch (Exception e)
    {
        Console.Write(e.Message);
    }
}


finally
{
    conn.Close();
}

Answer 7

What you want to do is to add your connection string to the App.Config or Web.config (depends on your project type) file in your project's solution. It might look like this:

<?xml version="1.0"?>
<configuration>
  <connectionStrings>
    <add name="MyConnection" 
    connectionString="Data Source=localhost; AttachDbFilename=E:\myDB\DB1.mdf; trusted_connection=yes"/>
  </connectionStrings>
</configuration> 

Next you should have the following reference included:

using System.Configuration;

Now you can get your string as follows:

string connectionString = ConfigurationManager.ConnectionStrings["MyConnection"].ConnectionString;

It is possible that ConfigurationManager will not be found even with using System.Configuration; in your code. To fix that:

1. Right-click References in the Solution Explorer
2. Click Add Reference
3. Find and add System.Configuration.dll