Click here to Skip to main content
15,895,809 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
In my project i need my data to be fetched from SQL Server and i had placed my Connection string in web.config, so that it can be modified and encrypted... But now i want to open the sql connection using
SqlConnection conn = new SqlConnection(connectionstring);
conn.open;

when i open this conn.open it should be called from all my webforms or winforms so that there is no need to write conn.open again and again. i can used this conn anywhere i want like SqlCommand cmd = new SqlCommand("select * from xyz",conn);

Upto now i used a seperate class in my project but for that too i have to declare the class in every page and i dont want to used as a static or shared variable... Is making conn as shared or static in good?

Guide me, is there any other way of doing this....
Posted
Updated 2-Aug-11 0:30am
v2
Comments
Herman<T>.Instance 2-Aug-11 6:37am    
why don't you make static methods in your separate class?

as an example:
I have a solution that exists of several projects. I have 1 project called DataAccess.

To create the SqlCommand I use:
C#
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using System.Collections;

namespace DataAccess
{
    public class SQLCommands
    {
        public static String DefaultConnectionName()
        {
            return System.Web.Configuration.WebConfigurationManager.ConnectionStrings[0].Name;
        }
        public static SqlCommand OpenCommand(String StoredProcedure, List<sqlparameter> sqlParameters, String ConnectionName)
        {
            ConnectionStringSettings settings = ConfigurationManager.ConnectionStrings[ConnectionName ?? DefaultConnectionName()];
            if (settings == null) throw new Exception("No connectionstring found");
            SqlCommand cmd = new SqlCommand(StoredProcedure, new SqlConnection(settings.ConnectionString));
            cmd.CommandType = CommandType.StoredProcedure;
            if (null != sqlParameters)
            {
                CheckParameters(sqlParameters);
                cmd.Parameters.AddRange(sqlParameters.ToArray());
            }
            cmd.CommandTimeout = 60; // 1 minute
            cmd.UpdatedRowSource = UpdateRowSource.None;
            cmd.Connection.Open();
            return cmd;
        }
        private static void CheckParameters(List<sqlparameter> sqlParameters)
        {
            foreach (SqlParameter parm in sqlParameters)
            {
                if (null == parm.Value)
                    parm.Value = DBNull.Value;
            }
        }
        public static void CloseCommand(SqlCommand sqlCommand)
        {
            if (null != sqlCommand && sqlCommand.Connection.State == ConnectionState.Open)
                sqlCommand.Connection.Close();
        }
    }
}
</sqlparameter></sqlparameter>


to do a ExecuteNonQuery I have another class that uses the above class.

C#
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
namespace DataAccess
{
    public class ToDataBase
    {
        /// <summary>
        /// Shoot the DATA
        /// </summary>
        /// <param name="StoredProcedure"></param>
        /// <param name="parms">List<sqlparameter></sqlparameter></param>
        /// <param name="ConnectionName"></param>
        public static Int32 InsertData(String StoredProcedure, List<sqlparameter> parms, String ConnectionName)
        {
            SqlCommand myCommand = null;
            Int32 affectedRows = 0;
            try
            {
                myCommand = SQLCommands.OpenCommand(StoredProcedure, parms, ConnectionName);
                affectedRows = myCommand.ExecuteNonQuery();
            }
            catch (Exception err)
            {
                // do something with the error
                string error = err.ToString();
            }
            finally
            {
                SQLCommands.CloseCommand(myCommand);
            }
            return affectedRows;
        }
    }
}
</sqlparameter>


I hope this will give you a lead to your solution
 
Share this answer
 
Comments
Herman<T>.Instance 2-Aug-11 6:48am    
my code is getting deferred by <sqlparameter> tag
Kishore Jangid 2-Aug-11 7:07am    
This is quiet tedious, this will call the SQLCommand class everytime you do transaction with sql server... means if u want to have hundred different sql statements then this call will be called hundred times.....

if want the connection to be opened once for the entire project..
Herman<T>.Instance 2-Aug-11 7:57am    
it is pretty simple. a sqlsommand use a sqlconnection. when the sqlcommand changes the sqlconnection provides you an error that the sqlcommand changed. So you need to open and close your connection each time
abhay shankar 22-May-12 3:20am    
sdasda
Have a read of these

General Discussion on connection practises[^]

MSDN ADO.NET Best Practises[^]

Solution 1 gives a good solution to your problem also, but the links should be a good background reading for you
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900