Solved

Wrapper for SQLite and SQL

Posted on 2014-02-05
5
422 Views
Last Modified: 2014-02-06
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!
0
Comment
Question by:mjelec
  • 3
  • 2
5 Comments
 
LVL 27

Expert Comment

by:Sammy
ID: 39836440
I would use an interface like (IWrapper) and use builder or factory pattern to do this.
Simple OOP principles are missed here.
0
 

Author Comment

by:mjelec
ID: 39836701
@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
 
LVL 27

Accepted Solution

by:
Sammy earned 500 total points
ID: 39836809
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
 

Author Comment

by:mjelec
ID: 39836910
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
 
LVL 27

Expert Comment

by:Sammy
ID: 39836965
Yes, if you use the factory pattern they will both be different products.
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question