Solved

Wrapper for SQLite and SQL

Posted on 2014-02-05
5
413 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Error in JQuery 5 39
TSQL DateADD update Question 4 32
Sql Query 4 21
SQL Server (2008) Declare Variable (Date) and Set value 6 19
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

863 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now