I have developed a small database application and was really overwhelmed by the amount of SQL code I had to generate. At some point, I decided to change databases from Access to Sqlite and had to change a huge amount of code because of this change.
I came up with three new database classes that can save you a substantial amount of work.
SqlDatabaseAdaptor
This is an extremely small and useful class:
SqlDatabaseAdaptor db_adaptor = new SqlDatabaseAdaptor(DatabaseType.SqlServer, connectionString);
After you have created a db_adaptor, you can use it to access a database in the way you would use ADO Net interfaces. Say you want to insert a new record into some Test table and you want to use a transaction:
using(DbTransaction trans = db_adaptor.Connection.BeginTransaction()) { using(DbCommand cmd = db_adaptor.Connection.CreateCommand()) { cmd.CommandText = "insert into Test ( Name,Age) Values ('Joe',29)"; cmd.ExecuteNonQuery(); } trans.Commit(); }
Say you want to open a DataSet:
using(DbCommand cmd = db_adaptor.Connection.CreateCommand()) { cmd.CommandText = "select * from Test"; DbDataAdapter adapter = db_adaptor.DBFactory.CreateDataAdapter(); adapter.SelectCommand = cmd; DataSet ds = new DataSet(); adapter.Fill(ds); }
If you are familiar with the Ado NET, there are no differences at all, but the advantage of using SqlDatabaseAdaptor is amazing; you can switch to another database in no time at all:
SqlDatabaseAdaptor db_adaptor = new SqlDatabaseAdaptor(SqlDatabaseAdaptorNS.DatabaseType.Access, connectionString);
Another nice class that can be used instead of standard DbCommandBuilder is a new easyCommandBuilder class:
easyCommandBuilder command_builder = db_adaptor.CreateSqlCommandBuilder();
This command builder doesn’t have the huge overhead that a standard DbCommandBuilder has. The easyCommandBuilder class caches all tables information and uses simplified queries for better performance. Check this out:
//assuming that you filled these parameter array prior to the call DbParameter[] parameters; easyCommandBuilder command_builder = db_adaptor.CreateSqlCommandBuilder(); command_builder.Init("Test"); //Name of the table DbCommand ins_cmd = command_builder.GetInsertCommand(parameters); DbCommand upd_cmd = command_builder.GetUpdateCommand(parameters); DbCommand del_cmd = command_builder.GetDeleteCommand(parameters); DbCommand set_cmd = command_builder.GetSelectCommand(parameters);
And the last class is SqlProcessor that automates all mentioned above:
SqlProcessor p = new SqlProcessor(DatabaseType.SQLite, connectionString); p.SetObject(p); Test t = new Test(); t.iNumber = 10; t.dNumber = 25; t.sNumber = "hello"; //inserts the record into the Test database ( table and class name //should be the same p.Insert() t.sNumber = "bye"; p.Update(); p.iNumber = 5; // this is a key field p.Select(); //selects a record where iNumber = 5 p.Delete() // deletes a record where iNumber = 5
Note: All queries are parameterized and high performing.
This is it. I would be happy to have your opinion about this and ideas how to improve it.
/////////////////////// Some test class //////////////////////// public class Test { private int iVal; private double dVal; private string sVal; private bool m_Modified; public Test() { iVal = 10; dVal = 15.45f; sVal = "hello_again"; } public int iNumber { get { return iVal; } set { iVal = value; } } public double dNumber { get { return dVal; } set { dVal = value; } } public string sNumber { get { return sVal; } set { sVal = value; } } #region ISqlHandle Members #endregion }