Link to home
Start Free TrialLog in
Avatar of mjelec
mjelec

asked on

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!
Avatar of Sammy
Sammy
Flag of Canada image

I would use an interface like (IWrapper) and use builder or factory pattern to do this.
Simple OOP principles are missed here.
Avatar of mjelec
mjelec

ASKER

@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.
ASKER CERTIFIED SOLUTION
Avatar of Sammy
Sammy
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mjelec

ASKER

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!
Yes, if you use the factory pattern they will both be different products.