Want your feedback on my first object in C# (with sample solution now!)

Last night I worked on this class, since I am new in C# I wish to know your feedbacks so I can improve it, it's an object that I intend to encapsulate the process of using parameters when executing action queries against an Access database...

Code:

---

using System;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;

namespace Dee_U
{
        public class PreparedStatement
        {
                private OleDbConnection connApp;
                private string strConnect;
                private OleDbCommand cmd;
       
                //Constructor
                public PreparedStatement(string db_provider, string db_path)
                {
                        try
                        {
                                strConnect = "Provider=" + db_provider + ";Data Source=" + db_path + ";";
                                connApp = new OleDbConnection(strConnect);
                                connApp.Open();
                                //cmd.Connection = connApp;
//                                if (connApp.State.ToString()=close){
//                                        MessageBox.Show("Cannot open database, pls. contact developer or system administrator.","Error");
//                                        //application.exit;
//                                }                               
                        }
                        catch(Exception ex)
                        {                               
                                MessageBox.Show(ex.Message.ToString(),"Error",MessageBoxButtons.OK,MessageBoxIcon.Error);       
                        }
                }

                public void SetParam(string pName,string pValue)
                {
                        //cmd.Parameters.Add(pName,pValue);       
                        OleDbParameter pParam = new OleDbParameter();                       
                        pParam.OleDbType = OleDbType.VarChar;
                        pParam.ParameterName = pName;
                        pParam.Value = pValue;
                        cmd.Parameters.Add(pParam);                                       
                }

                public void SetParam(string pName,int pValue)
                {
                        //cmd.Parameters.Add(pName,pValue);       
                        OleDbParameter pParam = new OleDbParameter();                       
                        pParam.OleDbType = OleDbType.Integer;
                        pParam.ParameterName = pName;
                        pParam.Value = pValue;
                        cmd.Parameters.Add(pParam);                       
                }

                public void SetParam(string pName,bool pValue)
                {
                        //cmd.Parameters.Add(pName,pValue);
                        OleDbParameter pParam = new OleDbParameter();                       
                        pParam.OleDbType = OleDbType.Boolean;
                        pParam.ParameterName = pName;
                        pParam.Value = pValue;
                        cmd.Parameters.Add(pParam);                       
                }

                public void SetParam(string pName,long pValue)
                {
                        //cmd.Parameters.Add(pName,pValue);       
                        OleDbParameter pParam = new OleDbParameter();                       
                        pParam.OleDbType = OleDbType.BigInt;
                        pParam.ParameterName = pName;
                        pParam.Value = pValue;
                        cmd.Parameters.Add(pParam);                       
                }

                public void SetParam(string pName,double pValue)
                {
                        //cmd.Parameters.Add(pName,pValue);
                        OleDbParameter pParam = new OleDbParameter();                       
                        pParam.OleDbType = OleDbType.Double;
                        pParam.ParameterName = pName;
                        pParam.Value = pValue;
                        cmd.Parameters.Add(pParam);                       
                }

                public void SetParam(string pName,short pValue)
                {
                        //cmd.Parameters.Add(pName,pValue);
                        OleDbParameter pParam = new OleDbParameter();                       
                        pParam.OleDbType = OleDbType.SmallInt;
                        pParam.ParameterName = pName;
                        pParam.Value = pValue;
                        cmd.Parameters.Add(pParam);                       
                }

                public void SetParam(string pName,decimal pValue)
                {
                        //cmd.Parameters.Add(pName,pValue);
                        OleDbParameter pParam = new OleDbParameter();                       
                        pParam.OleDbType = OleDbType.Decimal;
                        pParam.ParameterName = pName;
                        pParam.Value = pValue;
                        cmd.Parameters.Add(pParam);                       
                }
               
                public void SetParam(string pName,DateTime pValue)
                {       
                        OleDbParameter pParam = new OleDbParameter();                       
                        pParam.OleDbType = OleDbType.DBDate;
                        pParam.ParameterName = pName;
                        pParam.Value = pValue;
                        cmd.Parameters.Add(pParam);                       
                }

                public void SetStatement(string pStmt)
                {
                        if (pStmt!=string.Empty)
                        {       
                                cmd = new OleDbCommand(pStmt);
                                cmd.Connection = connApp;
                                cmd.CommandType = CommandType.Text;
                                cmd.Prepare();                               
                        }                       
                }

                public void ExecuteSQL()
                {
                        try                               
                        {                               
                                cmd.ExecuteNonQuery();
                                connApp.Close();
                        }
                        catch(Exception ex)
                        {
                                MessageBox.Show(ex.Message.ToString(),"Error",MessageBoxButtons.OK,MessageBoxIcon.Error);       
                        }
                }

        }
}

---

Code:

---

//Sample Usage
using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;

namespace Dee_U
{
        /// <summary>
        /// Summary description for Form1.
        /// </summary>
        public class Form1 : System.Windows.Forms.Form
        {
                private System.Windows.Forms.Button button1;
                private const string db_provider = "Microsoft.Jet.OLEDB.4.0";
                private const string db_path = "C:\\dee_u.mdb";

                private const string DB_INSERT = "INSERT INTO MyTable(a,b,c,d,e) VALUES (?,?,?,?,?);";
                //private const string DB_INSERT = "INSERT INTO MyTable(a,b,c,e) VALUES (?,?,?,?);";
                private const string DB_UPDATE = "UPDATE MyTable SET a = ?,b = ?,c = ?,d = ?,e = ? WHERE a = ?;";               
                private const string DB_DELETE = "DELETE * FROM eData;";
                /// <summary>
                /// The main entry point for the application.
                /// </summary>
                [STAThread]
                static void Main()
                {
                        Application.Run(new Form1());
                }

                private void button1_Click(object sender, System.EventArgs e)
                {
                        PreparedStatement x;
                        x = new PreparedStatement(db_provider,db_path);
                        x.SetStatement(DB_INSERT);
                        x.SetParam("a","reo'o");
                        x.SetParam("b","rodriguez");
                        x.SetParam("c",100.000101);
                        x.SetParam("d",DateTime.Now.Date);
                        x.SetParam("e",true);
                        x.ExecuteSQL();
                        MessageBox.Show("DONE!");
                }

                private void button2_Click(object sender, System.EventArgs e)
                {
                        PreparedStatement x;
                        x = new PreparedStatement(db_provider,db_path);
                        x.SetStatement(DB_UPDATE);
                        x.SetParam("a","xx");
                        x.SetParam("b","xx");
                        x.SetParam("c",104343.000101);
                        x.SetParam("d",DateTime.Now);
                        x.SetParam("e",false);
                        x.SetParam("a","x");
                        x.ExecuteSQL();
                        MessageBox.Show("DONE!");
                }
        }
}

---

"MessageBox.Show(ex.Message.ToString()" - Message is a string, so you dont need Message.ToString().

instead of writing a string with escape chars you can place a '@' in front of the string.

Code:

---

string str1 = "C:\\MyFolder\\file.txt";
string str2 = @"C:\MyFolder\file.txt";
//str1 is the same as str2
string str3 = (str1 == str2) ? str1 : str2;

---

if you have only one statement inside an if/while/for/foreach statement, you don't need to use {} around the code.

Code:

---

int i = 0;

while (i < 10)
    i++;

for (int j = 0; j < 20; j++)
    if (j == i)
        break;

---

Quote:

---

Originally Posted by tr333

instead of writing a string with escape chars you can place a '@' in front of the string.

---

Any issue when using the escape character? Thanks for the feedbacks! :afrog:

Quote:

---

Originally Posted by dee-u

Any issue when using the escape character?

---

not that i know of...
the '@' character makes it easier to not have to remember what characters require the '\', and it is slightly easier to read without seeing lots of '\' in the string.

The issue with using the "@" character is that every character in the string is interpreted as a literal. In the example given it is prefereable to use "@" but if you want to include escape sequences, like "\n", "\t", etc. then you cannot use "@".

This is a relatively small thing, and not specifically realted to C#, but I never use more than two string concatenation operators as it makes things more difficult to read. I would change

Code:

---

strConnect = "Provider=" + db_provider + ";Data Source=" + db_path + ";";

---

to

Code:

---

strConnect = string.Format("Provider={0};Data Source={1}", db_provider, db_path);

---

Thanks jm, those are worthy info for a C# newbie like me... :thumb:

I have actually progressed a bit with that class, I have made it that it would work with Access and other databases, I used a base class and inherited from it, I'll post it later...

Here they are, any comments are welcome...

Base Class:

Code:

---

//Copyright: dee-u

using System;
using System.Configuration;
using System.Windows.Forms;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data.OleDb;

namespace DataAccess.Base
{
        //public class PreparedStatement
        public abstract class PreparedStatement
        {
                private IDbCommand cmd;

                public void SetCommand(IDbCommand pCommand)
                {
                        cmd = pCommand;
                }

                protected void SetParam(string pName,string pValue)
                {                       
                        IDataParameter pParam = cmd.CreateParameter(); // = new IDbParameter();                       
                        pParam.DbType = DbType.String;
                        pParam.ParameterName = pName;
                        pParam.Direction= ParameterDirection.Input;
                        pParam.Value = pValue;
                        cmd.Parameters.Add(pParam);
                }

                protected void SetParam(string pName,int pValue)
                {                       
                        IDataParameter pParam = cmd.CreateParameter(); // = new IDbParameter();                       
                        pParam.DbType = DbType.Int32;
                        pParam.ParameterName = pName;
                        pParam.Direction= ParameterDirection.Input;
                        pParam.Value = pValue;
                        cmd.Parameters.Add(pParam);                       
                }

                protected void SetParam(string pName,bool pValue)
                {               
                        IDataParameter pParam = cmd.CreateParameter(); // = new IDbParameter();                       
                        pParam.DbType= DbType.Boolean;
                        pParam.ParameterName = pName;
                        pParam.Direction= ParameterDirection.Input;
                        pParam.Value = pValue;
                        cmd.Parameters.Add(pParam);                       
                }

                protected void SetParam(string pName,long pValue)
                {                       
                        IDataParameter pParam = cmd.CreateParameter(); // = new IDbParameter();                       
                        pParam.DbType = DbType.Single;
                        pParam.ParameterName = pName;
                        pParam.Direction= ParameterDirection.Input;
                        pParam.Value = pValue;
                        cmd.Parameters.Add(pParam);                       
                }

                protected void SetParam(string pName,double pValue)
                {                       
                        IDataParameter pParam = cmd.CreateParameter(); // = new IDbParameter();                       
                        pParam.DbType = DbType.Double;
                        pParam.ParameterName = pName;
                        pParam.Direction= ParameterDirection.Input;
                        pParam.Value = pValue;                       
                        cmd.Parameters.Add(pParam);                       
                }

                protected void SetParam(string pName,short pValue)
                {
                        IDataParameter pParam = cmd.CreateParameter(); // = new IDbParameter();                       
                        pParam.DbType = DbType.Int16;
                        pParam.ParameterName = pName;
                        pParam.Direction= ParameterDirection.Input;
                        pParam.Value = pValue;
                        cmd.Parameters.Add(pParam);                       
                }

                protected void SetParam(string pName,decimal pValue)
                {
                        IDataParameter pParam = cmd.CreateParameter(); // = new IDbParameter();                       
                        pParam.DbType = DbType.Decimal;
                        pParam.ParameterName = pName;
                        pParam.Direction= ParameterDirection.Input;
                        pParam.Value = pValue;
                        cmd.Parameters.Add(pParam);                       
                }
       
                protected void SetParam(string pName,DateTime pValue)
                {       
                        IDataParameter pParam = cmd.CreateParameter(); // = new IDbParameter();                       
                        pParam.DbType = DbType.Date;
                        pParam.ParameterName = pName;
                        pParam.Direction= ParameterDirection.Input;
                        pParam.Value = pValue;
                        cmd.Parameters.Add(pParam);                       
                }

                protected void SetStatement(string pStmt)
                {                       
                        if ((pStmt == null)) {
                                throw new ArgumentNullException("pStmt");
                        }
                        cmd.CommandType = CommandType.Text;
                        cmd.CommandText = pStmt;                       
                        cmd.Prepare();               
                }

                protected double ExecuteSQL()
                {
                        try                               
                        {                               
                                return cmd.ExecuteNonQuery();
                        }
                        catch(Exception ex)
                        {
                                MessageBox.Show(ex.Message.ToString(),"Error",MessageBoxButtons.OK,MessageBoxIcon.Error);       
                                return 0;
                        }
                }                       
        }
}

---

Inherited Class for Access (Haven't done for SQL Server):

Code:

---

//Copyright: dee-u

using System;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;

namespace DataAccess.AccessDB
{       
        public class PreparedStatement : DataAccess.Base.PreparedStatement
        {
                //Constructors
                public PreparedStatement()
                {       
                        OleDbCommand cmd = new OleDbCommand();                       
                        try
                        {
                                // Set the connection to our Access connection object
                                cmd.Connection = DataAccess.AccessConnection.PublicConnection.connApp;
                                // Pass an OleDbCommand to our base class
                                base.SetCommand(cmd);                               
                                // Open the connection
                                DataAccess.AccessConnection.PublicConnection.OpenConnection();
                        }
                        catch(Exception ex)
                        {                               
                                MessageBox.Show(ex.Message.ToString(),"Error",MessageBoxButtons.OK,MessageBoxIcon.Error);       
                        }
                }
       
                public void SetParameter(string pName,string pValue)
                {
                        SetParam(pName,pValue);
                }
               
                public void SetParameter(string pName,int pValue)
                {                       
                        SetParam(pName,pValue);
                }

                public void SetParameter(string pName,bool pValue)
                {               
                        SetParam(pName,pValue);
                }

                public void SetParameter(string pName,long pValue)
                {                       
                        SetParam(pName,pValue);
                }

                public void SetParameter(string pName,double pValue)
                {                       
                        SetParam(pName,pValue);
                }

                public void SetParameter(string pName,short pValue)
                {
                        SetParam(pName,pValue);
                }

                public void SetParameter(string pName,decimal pValue)
                {
                        SetParam(pName,pValue);
                }

                public void SetParameter(string pName,DateTime pValue)
                {
                        SetParam(pName,pValue);
                }

                public void SetActionQuery(string pStmt)
                {
                        if ((pStmt == null)) {
                                throw new ArgumentNullException("pStmt");
                        }                       
                        SetStatement(pStmt);                                       
                }

                public double ExecuteActionQuery()
                {
                        try                               
                        {                       
                                return ExecuteSQL();                               
                        }
                        catch(Exception ex)
                        {
                                MessageBox.Show(ex.Message.ToString(),"Error",MessageBoxButtons.OK,MessageBoxIcon.Error);       
                                return 0;
                        }
                }
        }
}

---

I'm not a big fan of Hungarian notation. What does the "p" prefix mean? At first I thought it was "parameter" but then I see it on your local variables as well. The C/C++ convention was to prefix pointer variables with "p". Just being picky. Obviously what conventions you use is up to you.

Yes, I intended it to denote Parameter, didn't noticed those other variables, still hasn't made up my mind about the notation, I realized in .Net it seem better to use btnSave that cmdSave already...

How about the whole thing? Is what I am doing right? Any issues you can find?

Quote:

---

Originally Posted by dee-u

How about the whole thing? Is what I am doing right? Any issues you can find?

---

Oh yeah, the point in the first place.

Well, you've got a lot of redundant code in your SetParam methods. There's only one line that's different in each so you should pull out the common stuff and put it in its own method that each of the others calls.

Also, there seems little point having the SetParameter methods as they don't do anything. You're creating a parameter of the appropriate type anyway because it's being generated by the command, so why not just make the SetParam methods in the base class public and do away with the SetParameter methods altogether?

don't know why you have ex.Message.ToString(), when ex.Message is already a string...

One additional note. I'd not use MessageBox.Show in that class. I'd actually not catch the exceptions in those classes, or else catch them and throw a new exception with the original as the InnerException. That way you aren't limited to WinForms usage and the calling app can handle the exceptions how it sees fit. As you're doing it, if the execution returns zero the caller doesn't know whether the statement was successful but no rows were affected or an exception was thrown. Remember, your APPLICATION should catch all exceptions somewhere, but it is often not appropriate to catch them at the point at which they were thrown.

Would you care to cite a sample algo/code for such? Thanks for your pointers in your previous post, gonna modify it later... :-)

Quote:

---

Originally Posted by dee-u

Would you care to cite a sample algo/code for such? Thanks for your pointers in your previous, gonna modify it later... :-)

---

I'm not quite sure what you mean. If you're talking about what I posted regarding exceptions then you either don't catch the exception at all or else catch it, create a new exception, assign the caught exception the new exception's InnerException property and then throw the new exception:

Code:

---

try
{
}
catch (Exception inner)
{
    throw new ApplicationException("Oops!", inner);
}

---

Quote:

---

Originally Posted by dee-u

Would you care to cite a sample algo/code for such?

---

Yup, I was talking about the InnerException, thanks for the sample code, gonna try it later...

Quote:

---

Originally Posted by dee-u

Thanks for your pointers in your previous, gonna modify it later... :-)

---

I was referring to your post #11, thanks for those...

I've got a bit of problem, I want it that I could access a shared property that could return to me either an OleDbConnection or an SqlConnection, I tried using IDbConnection as the property type but encountered an error in conversion, could anyone provide a workaround for this? What I am currently doing right now is to use two shared classes for SqlConnection and OleDbConnection...

What do you mean by a "shared property"? Are you using "shared" in the VB.NET sense, which is "static" in C#? Also, what class are you using this in? I would think that this should be an abstract method of the base class that you override in the derived PreparedStatement classes.

Code:

---

    public abstract class Parent
    {
        public abstract IDbConnection GetConnection();
    }

    public class Child1 : Parent
    {
        public override IDbConnection GetConnection()
        {
            return new OleDbConnection();
        }
    }

---

Yup, I meant abstract, have you tried your sample code if it works? I think I have also something along such line and encountered an error, can't test for now since I don't have IDE here... And I am thinking of probably using like the ff but encountered an error also , I'm just freehand typing it, don't know if it's 100% correct...

Code:

---

public class Child
{       
        private OleDbConnection conn1 = new OleDbConnection;
        private SqlConnection conn2 = new SqlConnection();
        private IDbConnection conn3;

        public void SetConnection(int pType)
        {
                switch
                {
                        case is 1:
                                conn3 = conn1;                                       
                                break;
                        case is 2"
                                conn3 = conn2;
                                break;

                        default;
                                conn3 = conn1
                                break;
                }
        }

        public IDbConnection GetConnection()
        {
                return conn3;               
        }

}

---

I copied my code form the IDE after compiling it. An instance of any class that implements an inteface IS an instance of that interface. That's what polymorphism is all about. Anywhere you need an IDbConnection object you can use an instance of any class that implements that interface. Of course, when you access that property/method the object you get back is an IDbConnection reference, so you'd have to cast it if you wanted to use any members other than those of the IDbConnection interface.

Correct me if I'm wrong but isn't accesing the GetConnection property would create a new OleDbConnection each time?

BTW, here is the update version of my objects:

Base class:

Code:

---

//Copyright: dee-u

using System;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data.OleDb;
//
namespace DataAccess.Base
{
        //public class PreparedStatement
        public abstract class PreparedStatement
        {
                private IDbCommand cmd;               

                // To set either as OleDbCommand or SqlCommand
                protected void SetCommand(IDbCommand pCommand)
                {        cmd = pCommand;               
                }

                //To add the parameters to the command object
                public void SetParam(string pName,string pValue)
                {        this.AddParam(DbType.String, pName, pValue);       
                }

                public void SetParam(string pName,int pValue)
                {        this.AddParam(DbType.Int32, pName, pValue);               
                }

                public void SetParam(string pName,bool pValue)
                {        this.AddParam(DbType.Boolean, pName, pValue);       
                }

                public void SetParam(string pName,long pValue)
                {        this.AddParam(DbType.Single, pName, pValue);       
                }

                public void SetParam(string pName,double pValue)
                {        this.AddParam(DbType.Double, pName, pValue);       
                }

                public void SetParam(string pName,short pValue)
                {        this.AddParam(DbType.Int16, pName, pValue);               
                }

                public void SetParam(string pName,decimal pValue)
                {        this.AddParam(DbType.Decimal, pName, pValue);
                }
       
                public void SetParam(string pName,DateTime pValue)
                {        this.AddParam(DbType.Date, pName, pValue);       
                }

                private void AddParam(DbType pDbType,string pName, object pValue)
                {
                        try
                        {        // IDataParameter is the interface for Parameter object
                                IDataParameter param = cmd.CreateParameter();
                                param.DbType = pDbType;
                                param.ParameterName = pName;
                                param.Direction= ParameterDirection.Input;
                                //Check for null parameter:
                                if (pValue != null)
                                {        param.Value = pValue;
                                }
                                else
                                {        param.Value = System.DBNull.Value;
                                }
                                cmd.Parameters.Add(param);                       
                        }
                        catch(Exception inner)
                        {        throw new ApplicationException(inner.Message, inner);                               
                        }
                }

                public void SetActionQuery(string pStmt)
                {                       
                        if ((pStmt == null))
                        {        throw new ArgumentNullException("pStmt");
                        }
                        try
                        {        cmd.CommandType = CommandType.Text;
                                cmd.CommandText = pStmt;                       
                                cmd.Prepare();               
                        }
                        catch(Exception inner)
                        {        throw new ApplicationException(inner.Message, inner);                               
                        }
                }

                public double ExecuteActionQuery()
                {
                        try                               
                        {        return cmd.ExecuteNonQuery();
                        }
                        catch(Exception inner)
                        {        throw new ApplicationException(inner.Message, inner);                               
                        }
                }                       
        }


        public abstract class CommonUtilities
        {
                private IDbCommand command;                       

                // To set either as OleDbCommand or SqlCommand
                protected void SetCommand(IDbCommand pCommand)
                {        command = pCommand;                               
                }

                protected bool IsExisting(string pSQL)
                {
                        bool result = false;                       
                        IDataReader rtnReader;                               
                        try
                        {       
                                command.CommandText = pSQL;
                                rtnReader = command.ExecuteReader();
                                result = rtnReader.Read();
                                rtnReader.Close();
                                return result;
                        }
                        catch(Exception inner)
                        {        throw new ApplicationException(inner.Message, inner);                               
                        }
                }

                protected int RCount(string pSQL)
                {
                        int result;                       
                        try
                        {                                       
                                command.CommandText = pSQL;                               
                                result = (int)command.ExecuteScalar();                               
                                return result;
                        }
                        catch(Exception inner)
                        {        throw new ApplicationException(inner.Message, inner);                               
                        }                       
                }
        }
}

---

Inherited Classes:

Code:

---

//Copyright: dee-u

using System;
using System.Data;
using System.Data.OleDb;

namespace DataAccess.AccessDB
{       
        public class PreparedStatement : DataAccess.Base.PreparedStatement
        {               
                //Constructors
                public PreparedStatement()
                {       
                        OleDbCommand cmd = new OleDbCommand();                                               
                        try
                        {
                                // Set the connection to our Access connection object
                                cmd.Connection = DataAccess.AccessConnection.PublicConnection.connApp;
                                // Pass an OleDbCommand to our base class
                                base.SetCommand(cmd);                               
                                // Open the connection
                                DataAccess.AccessConnection.PublicConnection.OpenConnection();
                        }
                        catch(Exception inner)
                        {        throw new ApplicationException(inner.Message, inner);                               
                        }
                }
        }
}

---

Code:

---

//Copyright: dee-u

using System;
using System.IO;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;

namespace DataAccess.CommonUtilities
{
        /// <summary>
        /// Summary description for CommonUtilities.
        /// </summary>
        public class Access : DataAccess.Base.CommonUtilities
        {
                public Access()
                { // TODO: Add constructor logic here               
                       
                }

                private void PrepareCommand()
                {
                        OleDbCommand cmd = new OleDbCommand();
                        cmd.Connection = DataAccess.AccessConnection.PublicConnection.connApp;                       
                        SetCommand(cmd);
                }
               
                public bool RecordExists(string pSQL)
                {
                        bool result;                       
                        DataAccess.AccessConnection.PublicConnection.OpenConnection();
                        PrepareCommand();
                        result = IsExisting(pSQL);
                        DataAccess.AccessConnection.PublicConnection.CloseConnection();
                        return result;
                }

                public int RecordCount(string pSQL)
                {
                        int result;
                        DataAccess.AccessConnection.PublicConnection.OpenConnection();
                        PrepareCommand();
                        result = RCount(pSQL);
                        DataAccess.AccessConnection.PublicConnection.CloseConnection();
                        return result;
                }

                public static void BackUpAccessDB(string strSource, string strDestination)
                {
                        FileInfo fil = new FileInfo(strSource);
                        FileStream strmIn = fil.OpenRead();
                        if (File.Exists(strDestination) == true)
                        {
                                try
                                {
                                        File.Delete(strDestination);
                                }
                                catch (Exception ex)
                                {
                                        MessageBox.Show(ex.Message.ToString(),"Error",MessageBoxButtons.OK,MessageBoxIcon.Error);       
                                        return;
                                }
                        }
                        FileStream strmOut = File.Create(strDestination);
                        while (!(strmOut.Length == strmIn.Length))
                        {
                                strmOut.WriteByte((byte)strmIn.ReadByte());
                        }
                        strmOut.Close();
                        strmIn.Close();
                        fil = null;   
                }

                public static void CompactRepairAccessDB(string strSource,string strDestination)
                {
                        //Note: Change first argument to the path of your MS Access application
                        System.Diagnostics.Process.Start(
                                @"C:\Program Files\Microsoft Office\Office10\msaccess.exe", @strSource + " /compact " + @strDestination);
                }


        }
}

---

That code I provided was just an example to show the inheritance situation and that you could return an OleDbConnection from a function with return type IDbConnection. You'd definitely want to change the implementation.

Thanks, will play with it later on.

If you will notice in my base class in my AddParam method I used an object as a parameter, do you think it is just okay? Variants were frowned upon in VB6.0 and it seems object is the equivalent of variant so I want to know the consequences of using it... :)

Any further whacks on my objects/classes? I think I am improving them right? :D

You should always use the most specific common ancestor you can. If your method needs to accept objects whose only common ancestor is Object then you have no real choice but to use that type. If the range of types is relatively small you could overload the method name but that is not practical in many situations.

How about if I use something like the ff...

Code:

---

public void SetParam(string pName, object pValue)
{
        switch
        {
               
        }       
}

---

Is it better than what I am currently doing?

I didn't say there was anything wrong with what you were doing already. :) Like I said, using Object is your only choice sometimes. It's a private method and you know that the object type will match the parameter type so it's not a big deal.

So my AddParam method is just fine? Sorry for my stubborness, it's just that if I can avoid the object variable then I would not really use it...

This class is evolving and I am starting to love C#... :D I am planning to make it as my generic Data Access Layer that I could use whether it is Access or SQL Server, any things that you think I should include? I still have a long list but I'm doing it slowly for now...

Here's what I have now for the generic connection object...

Code:

---

using System;
using System.Data;
using DataAccess;
using System.Data.OleDb;
using System.Data.SqlClient;

namespace DataAccess
{
        public class PublicConnection
        {       
                private static bool IsOleDbConnection = true;
                private static IDbConnection _connApp;
               
                public static void OpenConnection()
                {               
                        try
                        {                               
                                if (_connApp == null)
                                {       
                                        if (IsOleDbConnection == true)
                                        {        _connApp = new OleDbConnection();
                                        }
                                        else
                                        {        _connApp = new SqlConnection();
                                        }
                                }                               
                                if (_connApp.ConnectionString == string.Empty)
                                {        _connApp.ConnectionString = ConnectionString.PublicConnectionString.propConnectionString;
                                }
                                _connApp.Open();                                               
                        }
                        catch(Exception inner)
                        {        throw new ApplicationException(Error.Message, inner);                               
                        }
                }

                public static void CloseConnection()
                {
                        try
                        {        _connApp.Close();                               
                        }
                        catch(Exception inner)
                        {        throw new ApplicationException(Error.Message, inner);                               
                        }
                }

                public static void SetAsAccessConnection()
                {        IsOleDbConnection = true;
                }

                public static void SetAsSqlConnection()
                {        IsOleDbConnection = false;
                }

                public static IDbConnection GetConnection
                {               
                        get
                        {
                                if (_connApp == null)
                                {
                                        if (IsOleDbConnection == true)
                                        {        _connApp = new OleDbConnection();
                                        }
                                        else
                                        {        _connApp = new SqlConnection();
                                        }
                                }                                       
                                return _connApp;
                        }
                }
        }
}

---

And I'm calling it like this...

Code:

---

cmd.Connection = (OleDbConnection)PublicConnection.GetConnection;

---

Is there an overhead or performance degradation on what I am doing?

JM: I tried your example but I still need to cast it for it to get around the implicit conversion error? And why does you code have colors and mind does not when in fact we only use the same tags? :)

My code is coloured because I use the VBF Firefox Extension. I had a link to it in my sig for some time but I removed it recently. If you're a Firefox user you should definitely install it. A forum search should turn up the original thread in which it was posted by its creator. NoteMe championed it too, and I originally discovered it in one of his threads, although I don't remember which.

How about my inquiry about an overhead or performance degradation on hat I am doing? :)

Casting a reference is pretty inexpensive.

Thanks again. The way I did it I did not use any base class for my PublicConnection class, any trade-offs? Only the PublicConnection class is gonna inherit from such base class so I thought it wasn't necessary anymore...

Complicated it even further... :D

Base class:

Code:

---

using System;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Windows.Forms;
using DataAccess;

public abstract class BaseClass : IDisposable
{
    private DbCommand _cmd;
    private DbConnection _conn;
    private DbProviderFactory _factory;

    protected BaseClass()
    {}

    protected BaseClass(string databaseType)
    {
        MessageBox.Show(databaseType);
        ConnectionStringSettings connectionSetting = ConfigurationManager.ConnectionStrings[databaseType];
        MessageBox.Show(connectionSetting.ConnectionString);
        _factory = DbProviderFactories.GetFactory(connectionSetting.ProviderName);
        _conn = _factory.CreateConnection();
        _conn.ConnectionString = connectionSetting.ConnectionString;       
        SetCommand();
    }

    public void SetCommand()
    {
        _cmd = _conn.CreateCommand();
        _cmd.Connection = _conn;
    }

    public void AddParam(string parameterName, object parameterValue)
    {
        DbParameter param = _cmd.CreateParameter();
        param.ParameterName = parameterName;
        param.Direction = ParameterDirection.Input;
        if (parameterValue != null)
        {
            param.Value = parameterValue;
        }
        else
        {
            param.Value = System.DBNull.Value;
        }       
        _cmd.Parameters.Add(param);
    }

    protected DbCommand Command
    {
        get
        {
            return _cmd;
        }
    }

    protected DbProviderFactory Factory
    {
        get
        {
            return _factory;
        }

    }

    protected DbConnection Connection
    {
        get
        {
            return _conn;
        }
    }

    public void Dispose()
    {
        // dispose of the transaction if it exists
        //if (_transaction != null)
        //    _transaction.Dispose();

        // dispose of the connection
        if (_conn != null)
        {
            if (_conn.State == ConnectionState.Open)
                _conn.Close();
            _conn.Dispose();
        }
    }

}

---

Derived class:

Code:

---

//Copyright : dee-u

using System;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data.OleDb;
using DataAccess;
using System.Windows.Forms;
//
namespace DataAccess
{
        public abstract class Methods : BaseClass
        {
        protected Methods() : base()
        {}

        protected Methods(string databaseType) : base(databaseType)
        {}

        public void PrepareQuery(string pStmt)
                {
            if ((pStmt == null))
            {
                throw new ArgumentNullException("pStmt");
            }
            if (this.Command.CommandText != pStmt)
            {
                SetCommand();
            }
                        this.Command.CommandType = CommandType.Text;
                        this.Command.CommandText = pStmt;                       
                }

                public double ExecuteActionQuery()
                {
            this.Connection.Open();
            double result = (double)this.Command.ExecuteNonQuery();
            this.Connection.Close();
                        return result;
                }                       
       
        public bool RecordExists()
                {
            this.Connection.Open();
            IDataReader reader = this.Command.ExecuteReader(CommandBehavior.SingleResult);
                        bool result = reader.Read();
                        reader.Close();
            this.Connection.Close();
                        return result;
                }

                public int RecordCount()
                {
            this.Connection.Open();
            int result = (int)this.Command.ExecuteScalar();
            this.Connection.Close();
                        return result;
                }

        public object GetFieldValue()
                {
            this.Connection.Open();
            object result = this.Command.ExecuteScalar();
            this.Connection.Close();
            if (result != null)
            { 
                return result;
            }
            else
            {
                            return string.Empty;
            }
        }

        //overload for ComboBox
        public void PopulateList(ref ComboBox control)
        {
            this.Connection.Open();
            IDataReader reader = this.Command.ExecuteReader(CommandBehavior.SequentialAccess);
            control.Items.Clear();
            while (reader.Read())
            {
                control.Items.Add(reader.GetValue(0));
            }
            reader.Close();
            this.Connection.Close();
        }

        //overload for Listbox   
        public void PopulateList(ref ListBox control)
        {
            this.Connection.Open();
            DbDataReader reader = this.Command.ExecuteReader(CommandBehavior.SequentialAccess);
            control.Items.Clear();
            while (reader.Read())
            {
                control.Items.Add(reader.GetValue(0));
            }
            reader.Close();
            this.Connection.Close();
        }

        public DataSet FillDataSet()
        {
            //DbDataAdapter adapter = Factory.CreateAdapter(this.Command);
            DbDataAdapter adapter = this.Factory.CreateDataAdapter();
            adapter.SelectCommand = this.Command;
            DataSet ds = new DataSet();           
            adapter.Fill(ds);
            return ds;
        }
        }
}

---

Access:

Code:

---

//Copyright : dee-u

using System;
using DataAccess;
using System.Data;
using System.Data.OleDb;

namespace DataAccess
{       
        public class OleDb : DataAccess.Methods
        {               
        public OleDb() : base("MS Access")
                {                               
                }
        }
}

---

Sample usage:

Code:

---

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.Common;
using System.Configuration;
using System.Diagnostics;
using DataAccess.CommonUtilities;
using DataAccess;
using BusinessLogic.CommonFunctions;

namespace ApplicationLayer
{
    public partial class Form1 : Form
    {

        private const string DB_INSERT = "INSERT INTO MyTable(a,b,c,d,e,f) VALUES (?,?,?,?,?,?);";       

        public Form1()
        {
            InitializeComponent();           
        }

        private void button1_Click(object sender, EventArgs e)
        {
            DataAccess.Methods x = new DataAccess.OleDb();                       
            x.PrepareQuery(DB_INSERT);
            x.AddParam("a", "reo'o");
            x.AddParam("b", null);
            x.AddParam("c", 100.000101);
            x.AddParam("d", DateTime.Now.Date);
            x.AddParam("e", true);
            x.AddParam("f", this.pictureBox1);
            if (x.ExecuteActionQuery() != 0)
            {
                MessageBox.Show("DONE!");
            }
            else
            {
                MessageBox.Show("NOT DONE!");
            }         
        }

        private void button2_Click(object sender, EventArgs e)
        {
            DataAccess.CommonUtilities.Access x = new DataAccess.CommonUtilities.Access();           
            x.PrepareQuery("SELECT * FROM MyTable WHERE x = ?");
            x.AddParam("x", 199);
            try
            {
                CommonFunctions.Inform(x.RecordExists().ToString());
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.InnerException.ToString());
            }
            x.PrepareQuery("SELECT Count(a) FROM MyTable WHERE b = ?");
            x.AddParam("b", "rodriguez");
            MessageBox.Show(x.RecordCount().ToString());
        }

        private void button3_Click(object sender, EventArgs e)
        {
            DataAccess.CommonUtilities.Access x = new DataAccess.CommonUtilities.Access();
            //x.BackUpAccessDB(@"C:\dee_u.mdb",@"C:\d.mdb");
            //MessageBox.Show("done");
            x.PrepareQuery("SELECT x FROM MyTable WHERE d = ?");
            x.AddParam("d", DateTime.Now.Date);
            x.PopulateList(ref this.comboBox1);
            x.PopulateList(ref this.listBox1);
        }

        private void button4_Click(object sender, EventArgs e)
        {
            DataAccess.CommonUtilities.Access xx = new DataAccess.CommonUtilities.Access();
            xx.PrepareQuery("SELECT * FROM MyTable");
            DataSet y = xx.FillDataSet();
            MessageBox.Show(y.Tables.Count.ToString());
        }

    }
}

---

It's done, check on my sig, thanks for the people who have help me... :wave: