none
Correctly defined class RRS feed

  • Question

  • Hello,

    please i need help with my defniton class. I have class which connection to Sql. What is better? And no only this class for all class. Thanks for help

    1. Create object to get to the methods and properties.

    public class Test {
            public SqlConnection con { get; set; }
    
            public Test() {
                    con = new SqlConnection(connetionString);
            }
    }

    2. Composition

    public class Test {
            public SqlConnection con { get; set; }
    
            public Test() {
                    con = new SqlConnection(connetionString);
            }
    
            public void Open() {
                    con.Open();
            }
    }

    3. Inheritance

    public class Test : SqlConnection {
            public Test() : this(connetionString) {
            }
    }

    Thank you

    Thursday, August 16, 2018 11:42 AM

All replies

  • What will class do? If it is like repository or working with data you can store connection string parameter only. In method like GetById you create SqlConnection instance, open it and execute command.

    This solution is better because SqlConnection should be disposed. Your examples don't have implementation of disposable pattern.

    Problem of your examples is encapsulation princip. It means you should not to expose SqlConnection as public but it should be private and you shoudl encapsulate method/properties of it. But why do you need it?

    Thursday, August 16, 2018 1:16 PM
  • Another idea would be to have a base connection and exception class. In the example below I use a Interface so that my Oracle or SQL-Server classes both have a connection string property.

    A full example is here on GitHub if using C# 7 but the classes below as is work in C# 6.

    public interface IConnection
    {
        string ConnectionString { get; }
    }

    For exceptions

    public class BaseExceptionsHandler
    {
    
        protected bool mHasException;
        /// <summary>
        /// Indicate the last operation thrown an 
        /// exception or not
        /// </summary>
        /// <returns></returns>
        public bool HasException
        {
            get
            {
                return mHasException;
            }
        }
        protected Exception mLastException;
        /// <summary>
        /// Provides access to the last exception thrown
        /// </summary>
        /// <returns></returns>
        public Exception LastException
        {
            get
            {
                return mLastException;
            }
        }
    
        /// <summary>
        /// Indicates if there was a sql related exception
        /// </summary>
        public bool HasSqlException
        {
            get
            {
                if (mLastException != null)
                {
                    return mLastException is SqlException;
                }
                else
                {
                    return false;
                }
            }
        }
        /// <summary>
        /// If you don't need the entire exception as in 
        /// LastException this provides just the text of the exception
        /// </summary>
        /// <returns></returns>
        public string LastExceptionMessage
        {
            get
            {
                return mLastException.Message;
            }
        }
        /// <summary>
        /// Indicate for return of a function if there was an 
        /// exception thrown or not.
        /// </summary>
        /// <returns></returns>
        public bool IsSuccessFul
        {
            get
            {
                return !mHasException;
            }
        }
    }

    Connection class

    public class BaseSqlServerConnection : BaseExceptionsHandler, IConnection
    {
        /// <summary>
        /// This points to your database server
        /// </summary>
        protected string DatabaseServer = ".\\SQLEXPRESS";
        /// <summary>
        /// Default database
        /// </summary>
        protected string DefaultCatalog = "";
        public string ConnectionString
        {
            get
            {
                return $"Data Source={DatabaseServer};" + 
                        $"Initial Catalog={DefaultCatalog};" + 
                        "Integrated Security=True";
            }
        }
    }

    Data class to use the above

    public class Operations : BaseSqlServerConnection
    {
        public Operations() => DefaultCatalog = "ForumExample";
    
        private CancellationTokenSource cts;
        private async void TestSqlServerCancelSprocExecution()
        {
            cts = new CancellationTokenSource();
            try
            {
                await Task.Run(() =>
                {
                    using (SqlConnection cn = new SqlConnection("connStr"))
                    {
                        cn.Open();
    
                        var cmd = cn.CreateCommand();
                        cts.Token.Register(() => cmd.Cancel());
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.CommandText = "dbo.[CancelSprocTest]";
                        cmd.ExecuteNonQuery();
                    }
                });
            }
            catch (SqlException)
            {
                // sproc was cancelled
            }
        }
    
        public DataTable GetPeople()
        {
            DataTable dt = new DataTable();
            DefaultCatalog = "NorthWindAzure1";
            try
            {
                using (SqlConnection cn = new SqlConnection() { ConnectionString = ConnectionString })
                {
                    using (SqlCommand cmd = new SqlCommand() { Connection = cn })
                    {
                        cmd.CommandText = "SELECT CustomerIdentifier, CompanyName, CountryIdentfier FROM dbo.Customers";
                        cn.Open();
                        dt.Load(cmd.ExecuteReader());
                    }
                }
            }
            catch (Exception e)
            {
                mHasException = true;
                mLastException = e;
            }
    
            return dt;
        }
    }

    Calling one of the methods

    var ops = new Operations();
    var dt = ops.GetPeople();
    if (ops.IsSuccessFul)
    {
        // use data table
    }
    else
    {
        MessageBox.Show(ops.LastExceptionMessage);
    }

    The idea is to have a repeatable pattern from project to project. The interface and base classes would reside in a class project.

    The following could be an empty string and set in the data class but in always using the same server keep as is.

    protected string DatabaseServer = ".\\SQLEXPRESS";

    Oracle example with encrypted connection string.

    public class BaseOracleConnections : BaseExceptionsHandler , IConnection
    {
        /// <summary>
        /// Represents an encrypted connection string to OCS dev server
        /// </summary>
        public  string ConnectionString {
            get
            {
                return DecryptConnectionString("Qscuv5aVJ4qOdpaw/3XgF6bvo1q3Sm3w4kFPc9zU5cT2fshizo+" + 
                    "jOYocSedw2cWBIi9o7BTr8z4Vy1Txx99wldTC/ehkkTz43b2zAjFUr8Fy3PzZ33jjD2cR7p/G4meNeD" + 
                    "HLuro4VKsNY5XIOZHqOkSD7J4YITNyXlHWI5WPcEiAdgYl3NsN3LX6pyjlqXHOVP7AUNlevFm6ZFMvaka" + 
                    "QDAs1MVyDK6NTXUGVGii+fkY=");
            }
        }
    }


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites


    Thursday, August 16, 2018 1:47 PM
    Moderator
  • Yes it is what I wrote - each method create new instance of SqlConnection … 
    Thanks for your code :)
    • Edited by Petr B Thursday, August 16, 2018 2:34 PM
    Thursday, August 16, 2018 2:34 PM