00001
00002 using System;
00003 using System.Collections.Generic;
00004 using System.Reflection;
00005 using System.Text;
00006 using System.Data.Common;
00007 using Foodolini.Database.Sql;
00008
00012 namespace Foodolini.Database
00013 {
00014
00022 public class Repository : IDisposable
00023 {
00027 private DbConnection connection = null;
00028
00032 private SqlStrategy builder;
00033
00037 private List<string> tables = new List<string>();
00038
00045 private DbTransaction transaction = null;
00046
00056 public Repository(DbConnection connection, SqlStrategy builder){
00057 this.connection = connection;
00058 this.builder = builder;
00059 this.connection.Open();
00060 this.transaction = this.connection.BeginTransaction();
00061 using(DbCommand cmd = this.connection.CreateCommand()){
00062 cmd.CommandText = this.builder.TableQuery();
00063 using(DbDataReader rdr = cmd.ExecuteReader()){
00064 if(rdr.HasRows){
00065 while(rdr.Read())
00066 this.tables.Add(rdr.GetString(0));
00067 }
00068 }
00069 }
00070 }
00071
00080 public void Commit(){
00081 if(!this.disableCommit){
00082 this.transaction.Commit();
00083 this.transaction = this.connection.BeginTransaction();
00084 }
00085 }
00086
00090 public void Rollback(){
00091 this.transaction.Rollback();
00092 this.transaction = this.connection.BeginTransaction();
00093 }
00094
00104 public static Repository ConnectSqlite(string database){
00105 #if MONO
00106 var conn = new Mono.Data.Sqlite.SqliteConnection("data source=" + database + ";Pooling=true;FailIfMissing=false");
00107 #else
00108 var conn = new System.Data.SQLite.SQLiteConnection("data source=" + database + ";Pooling=true;FailIfMissing=false");
00109 #endif
00110 return new Repository(conn, new Sqlite());
00111 }
00112
00116 private bool disableCommit = false;
00117
00130 public static Repository ConnectSqlite(string database, bool disableCommit){
00131 Repository repo = ConnectSqlite(database);
00132 repo.disableCommit = disableCommit;
00133 return repo;
00134 }
00135
00136
00145 private void EnsureTable<T>() where T : class, new(){
00146
00147 if(this.connection == null)
00148 throw new ObjectDisposedException("connection", "The database connection have been closed/disposed.");
00149 if(!this.tables.Contains(this.TableName<T>()))
00150 this.CreateTable<T>();
00151 }
00152
00159 private string TableName<T>() where T : class, new(){
00160 return typeof(T).Name + "s";
00161 }
00162
00169 public void CreateTable<T>() where T : class, new(){
00170
00171 if(this.connection == null)
00172 throw new ObjectDisposedException("connection", "The database connection have been closed/disposed.");
00173
00174 using(DbCommand cmd = this.connection.CreateCommand()){
00175
00176 Dictionary<string,Type> cols = new Dictionary<string, Type>();
00177 foreach(PropertyInfo property in typeof(T).GetProperties()){
00178 if(string.Compare(property.Name, typeof(T).Name + "Id", true) == 0)
00179 continue;
00180 cols.Add(property.Name, property.PropertyType);
00181 }
00182
00183 cmd.CommandText = this.builder.CreateTableQuery(this.TableName<T>(), cols, typeof(T).Name + "Id");
00184
00185
00186 cmd.ExecuteNonQuery();
00187
00188
00189 this.tables.Add(this.TableName<T>());
00190 }
00191 }
00192
00202 public void Add<T>(T item) where T : class, new(){
00203
00204 this.EnsureTable<T>();
00205
00206 using(DbCommand cmd = this.connection.CreateCommand()){
00207
00208 List<string> parameters = new List<string>();
00209 foreach(PropertyInfo property in typeof(T).GetProperties()){
00210 if(string.Compare(property.Name, typeof(T).Name + "Id") != 0){
00211 parameters.Add(property.Name);
00212 var par = cmd.CreateParameter();
00213 par.ParameterName = "@" + property.Name;
00214 par.Value = property.GetValue(item, null);
00215 cmd.Parameters.Add(par);
00216 }
00217 }
00218
00219 cmd.CommandText = this.builder.InsertQuery(this.TableName<T>(), parameters);
00220
00221
00222 long id = (long)cmd.ExecuteScalar();
00223
00224
00225 foreach(PropertyInfo property in typeof(T).GetProperties()){
00226 if(string.Compare(property.Name, typeof(T).Name + "Id") == 0)
00227 property.SetValue(item, id, null);
00228 }
00229 }
00230 }
00231
00244 public bool Update<T>(T item) where T : class, new(){
00245
00246 this.EnsureTable<T>();
00247
00248 using(DbCommand cmd = this.connection.CreateCommand()){
00249
00250 List<string> parameters = new List<string>();
00251 long id = 0;
00252 foreach(PropertyInfo property in typeof(T).GetProperties()){
00253 if(string.Compare(property.Name, typeof(T).Name + "Id") == 0 && property.PropertyType == typeof(long)){
00254 id = (long)property.GetValue(item, null);
00255 }else{
00256 parameters.Add(property.Name);
00257 var par = cmd.CreateParameter();
00258 par.ParameterName = "@" + property.Name;
00259 par.Value = property.GetValue(item, null);
00260 cmd.Parameters.Add(par);
00261 }
00262 }
00263
00264 if(id == 0)
00265 throw new Exception("Type must have an identifier");
00266
00267 var para = cmd.CreateParameter();
00268 para.ParameterName = "@" + typeof(T).Name + "Id";
00269 para.Value = id;
00270 cmd.Parameters.Add(para);
00271
00272
00273 cmd.CommandText = this.builder.UpdateQuery(this.TableName<T>(), parameters, typeof(T).Name + "Id");
00274
00275
00276 int retval = cmd.ExecuteNonQuery();
00277
00278
00279 if(retval > 1)
00280 throw new Exception("More than one row was updated!");
00281
00282
00283 return retval == 1;
00284 }
00285 }
00286
00299 public bool Delete<T>(T item) where T : class, new(){
00300
00301 this.EnsureTable<T>();
00302
00303 using(DbCommand cmd = this.connection.CreateCommand()){
00304
00305 long id = 0;
00306 foreach(PropertyInfo property in typeof(T).GetProperties()){
00307 if(string.Compare(property.Name, typeof(T).Name + "Id") == 0 && property.PropertyType == typeof(long)){
00308 id = (long)property.GetValue(item, null);
00309
00310 if(id == 0)
00311 return false;
00312
00313 property.SetValue(item, 0, null);
00314 }
00315 }
00316
00317 if(id == 0)
00318 throw new Exception("Type must have an identifier");
00319
00320 cmd.CommandText = this.builder.DeleteQuery(this.TableName<T>(), typeof(T).Name + "Id");
00321
00322 var para = cmd.CreateParameter();
00323 para.ParameterName = "@" + typeof(T).Name + "Id";
00324 para.Value = id;
00325 cmd.Parameters.Add(para);
00326
00327
00328 int retval = cmd.ExecuteNonQuery();
00329
00330
00331 if(retval > 1)
00332 throw new Exception("More than one row was deleted in the database");
00333
00334
00335 return retval == 1;
00336 }
00337 }
00338
00351 public int DeleteWhere<T>(string condition, params object[] parameters) where T : class, new(){
00352
00353 this.EnsureTable<T>();
00354
00355 using(DbCommand cmd = this.connection.CreateCommand()){
00356 cmd.CommandText = this.builder.DeleteWhereQuery(this.TableName<T>(), condition);
00357 int pCount = 0;
00358 foreach(object param in parameters){
00359 var p = cmd.CreateParameter();
00360 p.ParameterName = "@" + pCount++;
00361 p.Value = param;
00362 cmd.Parameters.Add(p);
00363 }
00364 return cmd.ExecuteNonQuery();
00365 }
00366 }
00367
00380 public IEnumerable<T> Where<T>(string condition, params object[] parameters) where T : class, new(){
00381 return this.Query<T>(this.builder.WhereQuery(this.TableName<T>(), condition), parameters);
00382 }
00383
00399 public T SingleWhere<T>(string condition, params object[] parameters) where T : class, new(){
00400 return this.SingleQuery<T>(this.builder.PagedWhereQuery(this.TableName<T>(), condition, 0, 1), parameters);
00401 }
00402
00409 public IEnumerable<T> All<T>() where T : class, new(){
00410 return this.Query<T>(this.builder.AllQuery(this.TableName<T>()));
00411 }
00412
00425 public IEnumerable<T> Paged<T>(int page, int pageSize) where T : class, new(){
00426 return this.Query<T>(this.builder.PagedQuery(this.TableName<T>(), page, pageSize));
00427 }
00428
00435
00448 public IEnumerable<T> Query<T>(string sql, params object[] parameters) where T : class, new(){
00449
00450 this.EnsureTable<T>();
00451
00452 using(DbCommand cmd = this.connection.CreateCommand()){
00453 cmd.CommandText = sql;
00454 int pCount = 0;
00455 foreach(object param in parameters){
00456 var p = cmd.CreateParameter();
00457 p.ParameterName = "@" + pCount++;
00458 p.Value = param;
00459 cmd.Parameters.Add(p);
00460 }
00461 using(DbDataReader rdr = cmd.ExecuteReader()){
00462
00463 if(!rdr.HasRows)
00464 yield break;
00465
00466 while(rdr.Read()){
00467
00468 T row = new T();
00469
00470 for(int i = 0; i < rdr.FieldCount; i++){
00471
00472 PropertyInfo[] properties = typeof(T).GetProperties();
00473 foreach(PropertyInfo property in properties){
00474
00475
00476 if(string.Compare(property.Name, rdr.GetName(i), true) == 0){
00477
00478 object val = null;
00479 if(!rdr.IsDBNull(i)){
00480
00481 val = rdr.GetValue(i);
00482
00483 Type targetType = property.PropertyType;
00484
00485 if(targetType != rdr.GetFieldType(i)){
00486
00487 if(targetType.IsGenericType &&
00488 targetType.GetGenericTypeDefinition() == typeof(Nullable<int>).GetGenericTypeDefinition()){
00489
00490
00491 targetType = Nullable.GetUnderlyingType(targetType);
00492 }
00493
00494 val = Convert.ChangeType(val, targetType);
00495 }
00496 }
00497
00498 property.SetValue(row, val, null);
00499 }
00500 }
00501 }
00502
00503
00504 yield return row;
00505 }
00506 }
00507 }
00508
00509
00510 }
00511
00524 public T SingleQuery<T>(string sql, params object[] parameters) where T : class, new(){
00525 var query = this.Query<T>(sql, parameters);
00526 foreach(T t in query)
00527 return t;
00528 return null;
00529 }
00530
00531 #region IDisposable Implementation
00532
00533
00534
00546 protected virtual void Dispose(bool disposing){
00547 if(disposing){
00548
00549 if(this.transaction != null){
00550 if(!this.disableCommit)
00551 this.transaction.Commit();
00552 this.transaction.Dispose();
00553 this.transaction = null;
00554 }
00555 if(this.connection != null)
00556 this.connection.Dispose();
00557 this.connection = null;
00558 }
00559 }
00560
00564 public void Dispose(){
00565
00566 this.Dispose(true);
00567 GC.SuppressFinalize(this);
00568 }
00569
00573 ~Repository(){
00574
00575
00576 this.Dispose(false);
00577 }
00578 #endregion
00579
00586 public void Close(){
00587 this.Dispose();
00588 }
00589 }
00590 }