Click here to Skip to main content
12,349,148 members (55,834 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: SQL-server-2005 .NET3.5 C# VB , +
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
RaisKazi34.3K
v2
Comments
digimanus 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>

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 2-Aug-11 6:48am
   
my code is getting deferred by 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..
digimanus 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
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)

  Print Answers RSS
Top Experts
Last 24hrsThis month


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

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100