Wrapper for SQLite and SQL

I am trying to expand an application that was originally designed with an sqlite database, to also interface with an sql server.  My initial thought was to make a wrapper class to handle these transactions.  With a Sqlselect function for returning data and a SQLexecute function to do non-query transactions.

My initial stab is something like this:

public static class Sqlwrapper
    {

        public static int Sqltype = 0;

        /// <summary>
        /// Fills a datatable with the result of the select command.
        /// </summary>
        /// <param name="cmdstring"> Connection string </param>
        /// <param name="dt">Datatable to be filled</param>
        /// <returns>Returns true if successful, false if there is any issues.</returns>
        public static bool Sqlselect(string cmdstring, out DataTable dt)
        {
            dt = new DataTable();

            if (Sqltype == 0)
            {
                SQLiteConnection c = new SQLiteConnection("Data Source=Resources\\DB.sqlite;Version=3");
                try
                {
                    c.Open();
                    SQLiteDataAdapter a = new SQLiteDataAdapter(cmdstring, c);
                    a.Fill(dt);
                    c.Close();
                    return true;
                }
                catch (Exception exc)
                {
                    Debug.WriteLine(exc.Message);
                    c.Close();
                    return false;
                }
            }
            else
            {
                SqlConnection c = new SqlConnection("Server=Server;Database=DB;Trusted_Connection=True;");
                try
                {
                    c.Open();
                    SqlDataAdapter a = new SqlDataAdapter(cmdstring, c);
                    a.Fill(dt);
                    c.Close();
                    return true;
                }
                catch (Exception exc)
                {
                    Debug.WriteLine(exc.Message);
                    c.Close();
                    return false;
                }
            }
        }

        /// <summary>
        /// Executes a sql command
        /// </summary>
        /// <param name="cmdstring">sql command string</param>
        /// <returns>True for success.</returns>
        public static bool Sqlexecute(string cmdstring)
        {
            if (Sqltype == 0)
            {
                SQLiteConnection c = new SQLiteConnection("Data Source=Resources\\DB.sqlite;Version=3");
                try
                {
                    c.Open();
                    SQLiteCommand cmd = new SQLiteCommand(cmdstring, c);
                    cmd.ExecuteNonQuery();
                    c.Close();
                    return true;
                }
                catch (Exception exc)
                {
                    Debug.WriteLine(exc.Message);
                    c.Close();
                    return false;
                }
            }
            else
            {
                SqlConnection c = new SqlConnection("Server=Server;Database=DB;Trusted_Connection=True;");
                try
                {
                    c.Open();
                    SqlCommand cmd = new SqlCommand(cmdstring, c);
                    cmd.ExecuteNonQuery();
                    c.Close();
                    return true;
                }
                catch (Exception exc)
                {
                    Debug.WriteLine(exc.Message);
                    c.Close();
                    return false;
                }
            }
        }

    }

Open in new window


this seems to work, but there are some issues I am running into, for example.  Pulling from the sqlserver I'll need to add in a username, for the local database we are not sending that data.

Is my method of passing a command string and/or getting out a datatable a good practice?  Is there an easier method I am overlooking?

thanks!
mjelecAsked:
Who is Participating?
 
SammyCommented:
There is no such thing as IWrapper, I was recommending you to create one for your class.
Always do your best to avoid static classes; Static classes should only be used as utility classes and this is not the case for a warpper class

    public interface IWrapper
    {
        /// <summary>
        /// Fills a datatable with the result of the select command.
        /// </summary>
        /// <param name="cmdstring"> Connection string </param>
        /// <param name="dt">Datatable to be filled</param>
        /// <returns>Returns true if successful, false if there is any issues.</returns>
        bool Sqlselect(string cmdstring, out DataTable dt);

        /// <summary>
        /// Executes a sql command
        /// </summary>
        /// <param name="cmdstring">sql command string</param>
        /// <returns>True for success.</returns>
        bool Sqlexecute(string cmdstring);
    }

    public  class Sqlwrapper : IWrapper
    {

        public  int Sqltype = 0;

        /// <summary>
        /// Fills a datatable with the result of the select command.
        /// </summary>
        /// <param name="cmdstring"> Connection string </param>
        /// <param name="dt">Datatable to be filled</param>
        /// <returns>Returns true if successful, false if there is any issues.</returns>
        public  bool Sqlselect(string cmdstring, out DataTable dt)
        {
            dt = new DataTable();

            if (Sqltype == 0)
            {
                SQLiteConnection c = new SQLiteConnection("Data Source=Resources\\DB.sqlite;Version=3");
                try
                {
                    c.Open();
                    SQLiteDataAdapter a = new SQLiteDataAdapter(cmdstring, c);
                    a.Fill(dt);
                    c.Close();
                    return true;
                }
                catch (Exception exc)
                {
                    Debug.WriteLine(exc.Message);
                    c.Close();
                    return false;
                }
            }
            else
            {
                SqlConnection c = new SqlConnection("Server=Server;Database=DB;Trusted_Connection=True;");
                try
                {
                    c.Open();
                    SqlDataAdapter a = new SqlDataAdapter(cmdstring, c);
                    a.Fill(dt);
                    c.Close();
                    return true;
                }
                catch (Exception exc)
                {
                    Debug.WriteLine(exc.Message);
                    c.Close();
                    return false;
                }
            }
        }

        /// <summary>
        /// Executes a sql command
        /// </summary>
        /// <param name="cmdstring">sql command string</param>
        /// <returns>True for success.</returns>
        public  bool Sqlexecute(string cmdstring)
        {
            if (Sqltype == 0)
            {
                SQLiteConnection c = new SQLiteConnection("Data Source=Resources\\DB.sqlite;Version=3");
                try
                {
                    c.Open();
                    SQLiteCommand cmd = new SQLiteCommand(cmdstring, c);
                    cmd.ExecuteNonQuery();
                    c.Close();
                    return true;
                }
                catch (Exception exc)
                {
                    Debug.WriteLine(exc.Message);
                    c.Close();
                    return false;
                }
            }
            else
            {
                SqlConnection c = new SqlConnection("Server=Server;Database=DB;Trusted_Connection=True;");
                try
                {
                    c.Open();
                    SqlCommand cmd = new SqlCommand(cmdstring, c);
                    cmd.ExecuteNonQuery();
                    c.Close();
                    return true;
                }
                catch (Exception exc)
                {
                    Debug.WriteLine(exc.Message);
                    c.Close();
                    return false;
                }
            }
        }

    }

Open in new window


This is just OOP's simple principles to apply when you write classes.
An interface is a contract, when a class implements said interface; it will have to comply with all of the interface rules. In this case, your interface needs a class to implement  bool Sqlexecute(string cmdstring); and     bool Sqlselect(string cmdstring, out DataTable dt);

the best place to understand design patterns is http://www.dofactory.com/Patterns/Patterns.aspx

Good luck
0
 
SammyCommented:
I would use an interface like (IWrapper) and use builder or factory pattern to do this.
Simple OOP principles are missed here.
0
 
mjelecAuthor Commented:
@sammy1971 could you possible expound on this a little bit?  Perhaps a point towards a tutorial or something?  I tried googling IWrapper but I don't think I am getting the right kind of results.
0
 
mjelecAuthor Commented:
Thanks!  Just so I can wrap my head around this in it's application.

using factory method http://www.dofactory.com/Patterns/PatternFactory.aspx#_self1, would my Iwrapper Interface become the product? The two classes (one for sql and one for sqlite) become the concrete products?

thanks!
0
 
SammyCommented:
Yes, if you use the factory pattern they will both be different products.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.