Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-server-2005 .NET3.5 C# , +
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 2-Aug-11 0:26am
Edited 2-Aug-11 0:30am
RaisKazi32.4K
v2
Comments
digimanus at 2-Aug-11 6:37am
   
why don't you make static methods in your separate class?
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

as an example:
I have a solution that exists of several projects. I have 1 project called DataAccess.
 
To create the SqlCommand I use:
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.
 
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
  Permalink  
Comments
digimanus at 2-Aug-11 6:48am
   
my code is getting deferred by tag
Kishore Jangid at 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..
digimanus at 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 at 22-May-12 3:20am
   
sdasda
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

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
  Permalink  
v2

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



Advertise | Privacy | Mobile
Web01 | 2.8.140709.1 | Last Updated 2 Aug 2011
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid