locked
How to write one "central" Sqlconnection? RRS feed

  • Question

  • Hello guys, I'm wondering how can I write one "central" SqlConnection, first of all I would like to say that I'm beginner in c#/sql. I have DB on server and every single time I have SqlCommand for insert/update/del I have to write this SqlConnection:

      SqlConnection mojespojeni = new SqlConnection("Data Source=MyServerName;Initial Catalog=connection_tests;User ID=MyID;Password=MyPass");

    I think it can be done by using classes and namespaces but I have no idea how, can anyone help me solve this thing out?

    To make it more clear: I'm trying to find way that doesnt require write in each form SqlConnection.

    Thanks,

    Marek


    Monday, July 1, 2013 8:54 AM

Answers

  • Create a data access layer as a new project in your solution in Visual Studio and then do all communication with the database from a class in this project. In the client application, add a reference to this project and also specify the connection string in the application configuration file (App.config):

    <connectionStrings>
            <add name="conn"
                connectionString="Data Source=MyServerName;Initial Catalog=connection_tests;UserID=MyID;Password=MyPass"
                providerName="System.Data.SqlClient" />
        </connectionStrings>

    The connection string can then be retrived from the class in the data access layer when you create the SqlConnection object using:

    SqlConnection mojespojeni = new SqlConnection(ConfigurationManager.ConnectionStrings["conn"].ConnectionString;);


     

     

    • Marked as answer by Marek Šíp Tuesday, July 9, 2013 8:25 AM
    Monday, July 1, 2013 9:40 AM

All replies

  • Create a data access layer as a new project in your solution in Visual Studio and then do all communication with the database from a class in this project. In the client application, add a reference to this project and also specify the connection string in the application configuration file (App.config):

    <connectionStrings>
            <add name="conn"
                connectionString="Data Source=MyServerName;Initial Catalog=connection_tests;UserID=MyID;Password=MyPass"
                providerName="System.Data.SqlClient" />
        </connectionStrings>

    The connection string can then be retrived from the class in the data access layer when you create the SqlConnection object using:

    SqlConnection mojespojeni = new SqlConnection(ConfigurationManager.ConnectionStrings["conn"].ConnectionString;);


     

     

    • Marked as answer by Marek Šíp Tuesday, July 9, 2013 8:25 AM
    Monday, July 1, 2013 9:40 AM
  • Hi,

    I think you can create a class  ,then put connection and command   encapsulation in a class

    using System.Diagnostics;

    using

    System.Data.SqlClient;

    namespace Connection
    {
        public class DBConnection
        {
           
             public static SqlConnection getConnection()
            {
                string ConnectionString = "Integrated Security=SSPI;"+"Initial Catalog=student;"+"Data Source=localhost;";
                SqlConnection conn = new SqlConnection(ConnectionString); // 创建一个连接的对象
                conn.Open();
                try
                {
                    if (conn.State == ConnectionState.Open)
                        Console.WriteLine("Connection Suceess");
                }
                catch (SqlException e)
                {
                   Console.WriteLine(e.Message);
                }
                return conn;
            }

            public void ExecuteSQLStmt(string sql,SqlConnection con)
            {
                // 关闭数据库连接
                if (con.State == ConnectionState.Open)
                    con.Close();

                // 重新打开数据库连接
                string ConnectionString = "Integrated Security=SSPI;Initial Catalog='student';Data Source=localhost;";
                //连接字符串
                con = new SqlConnection(ConnectionString); // 创建一个连接的对象
                con.Open();

                //生成SqlCommand的对象
                SqlCommand cmd = new SqlCommand(sql, con);
                try
                {
                    cmd.ExecuteNonQuery();
                    //执行一个SqlCommand返回操作影响的行数,这个多半是判断操作是否成功的,
                    //例如插入删除和更新操作,如果影响行数为0的话,则证明操作是不成功的。大于0证明操作成功
                }
                catch (SqlException e)
                {
                       Debug.WriteLine(e.Message);
                }
            }
           public static void closeConnection()
            {
                conn.Close();
                try
                {
                    if (conn.State != ConnectionState.Open)
                        Debug.WriteLine("database close success");
                }
                catch (SqlException e)
                {
                    Debug.WriteLine(e.Message);
                }
            }
        }
    }

    Monday, July 1, 2013 9:50 AM
  • Hi 

    Refer Article

    you can use Microsoft Application Block which will take care of create sql connection and all .

    and you can create one sqlconnection and use that connection for executes all command.

    Monday, July 1, 2013 9:55 AM
  • I think you should not write a central SQLConnection.

    DBConnections should be opened, used and then Disposed of as quickly as possible. A design that splits opening, using and closing of unmanaged resources usually invites more troubles then it is worth.

    If you are worried about performance, the SQLConnection class itself uses Connection Pooling. So reopening the same connection does not nessesary involve any overhead.

    This is the way I use SQLConnections. How Microsoft advises to use SQLConnections:

    1. Have the connection string at some central place, like appliaction settings, user settings, string varriable

    2.

        using (SqlConnection connection = new SqlConnection(
                   connectionString))
        {
            SqlCommand command = new SqlCommand(queryString, connection);
            command.Connection.Open();
            command.ExecuteNonQuery();
        }


    Let's talk about MVVM: http://social.msdn.microsoft.com/Forums/en-US/wpf/thread/b1a8bf14-4acd-4d77-9df8-bdb95b02dbe2

    Monday, July 1, 2013 2:58 PM
  • What you might want to do is create one, central place to create your connection and command object instances, you can use one of the helper mentioned in other post or create your own. What you possibly don't want is to use one central SqlConnection instance for your data access operations like Christopher has noted.

    Your own might be simple solution, if you are not building any enterprise level application, that way you can understand and learn data access. As in following sample with just few methods you can create one place to create those object instances.

    public partial class CustomerForm : System.Windows.Forms.Form
    {
        private void GetCustomers()
        {
            string sql = "SELECT * FROM Customer;";
    
            using (var connection = SqlServer.GetConnection())
            using (var command = SqlServer.GetCommand(sql, connection: connection))
            { 
            }
        }
    }
    
    public static class SqlServer
    {
        public static SqlCommand GetCommand(string sql, CommandType commandType = CommandType.Text, SqlConnection connection = null, IEnumerable<SqlParameter> parameters = null)
        {
            if (connection == null)
            {
                connection = GetConnection();
            }
    
            var command = new SqlCommand(sql, connection);
            command.CommandType = commandType;
    
            if (parameters != null)
            {
                command.Parameters.AddRange(parameters.ToArray());
            }
    
            return command;
        }
    
        public static SqlConnection GetConnection()
        {
            string connectionString = ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;
    
            return new SqlConnection(connectionString);
        }
    }

    Tuesday, July 2, 2013 12:07 PM