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:
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!
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;
}
}
}
}
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!
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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!
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.
Simple OOP principles are missed here.