Placing a Configuration connection string in the Class so it can be viewed and use by all classes

zachvaldez
zachvaldez used Ask the Experts™
on
Adding configuration manager in namespace so it can be visible to all the classes is not possible as below

Public string cs = ConfigurationManager.ConnectionStrings["ProductCalls"].ConnectionString; >>>>>> This does not work.

It seems that it works only if code is place inside each Public classes.


Namespace ABC



{
    Public Class XYX
{
 ,,,,,,,,}

Public Class XXX
 {
    }

Public Class ZZZ
{

   }
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Chinmay PatelChief Technology Ninja
Distinguished Expert 2018

Commented:
Hi Zachvaldez,

Ideally you write a class, say DataAccess and then in that class

internal static SqlConnection GetConnection()
{
return new SqlConnection(ConfigurationManager.ConnectionStrings["ProductCalls"].ConnectionString); 
}

Open in new window

Please tweak the code as I wrote it without IDE, it can be improved a lot. Error handling can be added and connection usage can be optimized even further by making this connection limited to DataAccess class only and have various methods like
ExecuteSQLReader, ExecuteDataSet, ExecuteInsert, etc.. (This is how Microsoft Enterprise Library handles DataAccess) so that your connection is only utilized via this DataAccess Class only.

So, XYX, XXX, ZZZ whenever you need to perform a data operation, you will do something like this...

Class XYX{
internal DataSet GetXYXDataSet()
{
DataAccess.ExecuteDataSet(XYXQuery);
}
}

Open in new window


Regards,
Chinmay.
Most Valuable Expert 2018
Distinguished Expert 2018

Commented:
Another approach (maybe more inline with OOP) is to write a data access class that contains your connection string (as a property or method), along with any other methods and properties that you need to share.

You can then either inherit from it (maybe implemented as an abstract class), or dependency inject it - very easy to do if you're using a DI Framework, such as AutoFac.
Co-Founder and Chief Architect
Top Expert 2016
Commented:
@zachvaldez

As Chinamy pointed out, but a little explanation.

The difference is a static class, which doesn't have to be instanced to call it, and you can call it from your instances of other classes.

Quick Example:
 public class Sample1
    {
        public Sample1(Guid id, string value)
        {
            _id = id;
            Value = value;
        }

        private Guid _id;
        
        public Guid ID { get { return _id; } }
        public string Value { get; set; }
        
        private string SaveQuery
        {
            get
            {
                return "UPDATE Table SET Value = @value WHERE Id = @id";
            }
        }

        public void Save()
        {
            using (var _cs = StaticSample.GetSqlConnection())
            {
                SqlCommand sqlCommand = new SqlCommand(SaveQuery, _cs) { CommandType = CommandType.Text };
                sqlCommand.Parameters.Add("value", SqlDbType.VarChar).Value = Value;
                sqlCommand.Parameters.Add("id", SqlDbType.UniqueIdentifier).Value = ID;

                _cs.Open();
                sqlCommand.ExecuteNonQuery();
            }
        }
    }

    internal static class StaticSample
    {
        internal static SqlConnection GetSqlConnection()
        {
            return new SqlConnection(ConfigurationManager.ConnectionStrings["ProductCalls"].ConnectionString);
        }
    }

Open in new window

Most Valuable Expert 2018
Distinguished Expert 2018

Commented:
@Dustin - not a great example - you're hardcoding the connection into the class, which makes testing impossible. You'll also create a new connection for every query you run. Not great on performance.

The cons of a static class (not an embedded one like above) is it's effectively a global variable, accesible to all sorts of other classess. This may be a quick-fix to a problem, but it'll be more robust if you just inject an instance to only those classes that need it. And for testing - just register a different class with the IoC.
Dustin SaundersCo-Founder and Chief Architect
Top Expert 2016

Commented:
Not sure what your qualms are with the quick example, demonstrating how to call the static class from an instanced class.

There is plenty of utility in using the static class as such, and you should wrap your queries in using blocks or close the connections.  Are you forgetting about Connection Pooling?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial