Solved

Wrapper for SQLite and SQL

Posted on 2014-02-05
5
435 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

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 Detach & Attach 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.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

738 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