SQL和SQLite的包装器(Wrapper for SQL and SQLite)

SQL和SQLite的包装器(Wrapper for SQL and SQLite)

我正在尝试扩展最初使用sqlite数据库设计的应用程序，以便与sql server进行交互。 我最初的想法是创建一个包装类来处理这些事务。 使用Sqlselect函数返回数据，使用SQLexecute函数执行非查询事务。

我最初的刺伤是这样的：

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; } } } }

这似乎有效，但是我遇到了一些问题，例如。 从sqlserver中提取我需要添加一个用户名，对于我们不发送该数据的本地数据库。

我传递命令字符串和/或获取数据表的方法是一种好的做法吗？ 有一种我更容易忽视的方法吗？

谢谢！

编辑：这是第二个实现：

public class DbManager { public IDbProvider DbProvider; /// <summary> /// /// </summary> /// <param name="sqltype">An integer to set which database type to use, defaults to 0 for sqlite, set to 1 for sql server.</param> public DbManager(int sqltype = 0) { if (sqltype == 0) { DbProvider = new SqliteWrapper(); } else { DbProvider = new SqlWrapper(); } } } public interface IDbProvider { /// <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 SqliteWrapper : IDbProvider { /// <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(); 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; } } /// <summary> /// Executes a sql command /// </summary> /// <param name="cmdstring">sql command string</param> /// <returns>True for success.</returns> public bool Sqlexecute(string cmdstring) { 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; } } } public class SqlWrapper : IDbProvider { /// <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(); 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; } } public bool Sqlexecute(string cmdstring) { 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; } } }

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; } } } }

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!

Edit: Here is the second implementation:

public class DbManager { public IDbProvider DbProvider; /// <summary> /// /// </summary> /// <param name="sqltype">An integer to set which database type to use, defaults to 0 for sqlite, set to 1 for sql server.</param> public DbManager(int sqltype = 0) { if (sqltype == 0) { DbProvider = new SqliteWrapper(); } else { DbProvider = new SqlWrapper(); } } } public interface IDbProvider { /// <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 SqliteWrapper : IDbProvider { /// <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(); 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; } } /// <summary> /// Executes a sql command /// </summary> /// <param name="cmdstring">sql command string</param> /// <returns>True for success.</returns> public bool Sqlexecute(string cmdstring) { 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; } } } public class SqlWrapper : IDbProvider { /// <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(); 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; } } public bool Sqlexecute(string cmdstring) { 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; } } }

最满意答案

通常，ADO.NET框架中的每个类都有一个由所有提供程序使用的公共父级。 因此，您应该能够通过使用Factory模式（ http://www.dofactory.com/Patterns/PatternFactory.aspx ）或抽象工厂（ http：// ）来解决应用程序使用两种数据库这一事实。 www.dofactory.com/Patterns/PatternAbstract.aspx ）。

这可以确保您不需要两个包装器的实现。 例如 ：

public abstract class SqlFactory { public abstract DbConnection CreateConnection(); public abstract DataAdapter CreateAdapter(string command, DbConnection connection); } public class SqlLiteFactory : SqlFactory { public override DbConnection CreateConnection() { return new SQLiteConnection("Data Source=Resources\\DB.sqlite;Version=3"); } public override DataAdapter CreateAdapter(string command, DbConnection connection) { return new SQLiteDataAdapter(command, connection as SQLiteConnection); } } public class MSSqlFactory : SqlFactory { public override DbConnection CreateConnection() { return new SqlConnection("CONNECTION STRING HERE"); } public override DataAdapter CreateAdapter(string command, DbConnection connection) { return new SqlDataAdapter(command, connection as SqlConnection); } } //Composite and Singleton class... public class SqlHandler : SqlFactory { private static SqlHandler _instance; private SqlLiteFactory _sqlLiteFactory; private MSSqlFactory _msSqlFactory; //Singleton pattern. public static SqlHandler Instance { get { if (_instance == null) { _instance = new SqlHandler(); } return _instance; } } private SqlHandler() { _sqlLiteFactory = new SqlLiteFactory(); _msSqlFactory = new MSSqlFactory(); } public override DbConnection CreateConnection() { //Some code determining if better to use SqlLite or MS SQL. if (useSqlLite) { return _sqlLiteFactory.CreateConnection(); } else { return _msSqlFactory.CreateConnection(); } } public override DataAdapter CreateAdapter(string command, DbConnection connection) { //Some code determining if better to use SqlLite or MS SQL. if (useSqlLite) { return _sqlLiteFactory.CreateAdapter(command, connection); } else { return _msSqlFactory.CreateAdapter(command, connection); } } }

因此，当您需要调用包装器中相关的任何数据库时，您可以执行以下操作：

DbConnection c = SqlHandler.Instance.CreateConnection();

这种方式还允许您轻松实现新数据库。

PS：对于你的连接字符串，我建议你使用App.config文件来设置它。 如果将来发生任何变化，您将无需重新编译应用程序即可更改数据库。

Usually, every class in the ADO.NET framework has a common parent used by all providers. So you should be able to get around the fact that the application uses two kinds of databases just by using a Factory pattern (http://www.dofactory.com/Patterns/PatternFactory.aspx) or an Abstract Factory (http://www.dofactory.com/Patterns/PatternAbstract.aspx).

This would insure that you don't need two implementation of the wrappers. For example :

public abstract class SqlFactory { public abstract DbConnection CreateConnection(); public abstract DataAdapter CreateAdapter(string command, DbConnection connection); } public class SqlLiteFactory : SqlFactory { public override DbConnection CreateConnection() { return new SQLiteConnection("Data Source=Resources\\DB.sqlite;Version=3"); } public override DataAdapter CreateAdapter(string command, DbConnection connection) { return new SQLiteDataAdapter(command, connection as SQLiteConnection); } } public class MSSqlFactory : SqlFactory { public override DbConnection CreateConnection() { return new SqlConnection("CONNECTION STRING HERE"); } public override DataAdapter CreateAdapter(string command, DbConnection connection) { return new SqlDataAdapter(command, connection as SqlConnection); } } //Composite and Singleton class... public class SqlHandler : SqlFactory { private static SqlHandler _instance; private SqlLiteFactory _sqlLiteFactory; private MSSqlFactory _msSqlFactory; //Singleton pattern. public static SqlHandler Instance { get { if (_instance == null) { _instance = new SqlHandler(); } return _instance; } } private SqlHandler() { _sqlLiteFactory = new SqlLiteFactory(); _msSqlFactory = new MSSqlFactory(); } public override DbConnection CreateConnection() { //Some code determining if better to use SqlLite or MS SQL. if (useSqlLite) { return _sqlLiteFactory.CreateConnection(); } else { return _msSqlFactory.CreateConnection(); } } public override DataAdapter CreateAdapter(string command, DbConnection connection) { //Some code determining if better to use SqlLite or MS SQL. if (useSqlLite) { return _sqlLiteFactory.CreateAdapter(command, connection); } else { return _msSqlFactory.CreateAdapter(command, connection); } } }

So when you would need to invoke anything database related in your wrapper, you can just do :

DbConnection c = SqlHandler.Instance.CreateConnection();

This way also allows you to implement easily new databases.

PS : For your connection strings, I would recommend you use the App.config file to set it. If anything changes in the future, you will be able to change the database without having to recompile your application.