1using System; 2using System.Collections.Generic; 3using System.Text; 4using System.Data; 5using System.Configuration; 6using System.Data.Common; 7using System.Data.SqlClient; 8using System.Data.OleDb; 9using System.Data.Odbc; 10using System.Data.OracleClient; 11using System.IO; 12 13namespace BinaryIntellect.DataAccess 14{ 15 public class DatabaseHelper:IDisposable 16 { 17 private string strConnectionString; 18 private DbConnection objConnection; 19 private DbCommand objCommand; 20 private DbProviderFactory objFactory = null; 21 private bool boolHandleErrors; 22 private string strLastError; 23 private bool boolLogError; 24 private string strLogFile; 25 26 public DatabaseHelper(string connectionstring,Providers provider) 27 { 28 strConnectionString = connectionstring; 29 switch (provider) 30 { 31 case Providers.SqlServer: 32 objFactory = SqlClientFactory.Instance; 33 break; 34 case Providers.OleDb: 35 objFactory = OleDbFactory.Instance; 36 break; 37 case Providers.Oracle: 38 objFactory = OracleClientFactory.Instance; 39 break; 40 case Providers.ODBC: 41 objFactory = OdbcFactory.Instance; 42 break; 43 case Providers.ConfigDefined: 44 string providername=ConfigurationManager.ConnectionStrings["connectionstring"].ProviderName; 45 switch (providername) 46 { 47 case "System.Data.SqlClient": 48 objFactory = SqlClientFactory.Instance; 49 break; 50 case "System.Data.OleDb": 51 objFactory = OleDbFactory.Instance; 52 break; 53 case "System.Data.OracleClient": 54 objFactory = OracleClientFactory.Instance; 55 break; 56 case "System.Data.Odbc": 57 objFactory = OdbcFactory.Instance; 58 break; 59 } 60 break; 61 62 } 63 objConnection = objFactory.CreateConnection(); 64 objCommand = objFactory.CreateCommand(); 65 66 objConnection.ConnectionString = strConnectionString; 67 objCommand.Connection = objConnection; 68 } 69 70 public DatabaseHelper(Providers provider):this(ConfigurationManager.ConnectionStrings["connectionstring"].ConnectionString,provider) 71 { 72 } 73 74 public DatabaseHelper(string connectionstring): this(connectionstring, Providers.SqlServer) 75 { 76 } 77 78 public DatabaseHelper():this(ConfigurationManager.ConnectionStrings["connectionstring"].ConnectionString,Providers.ConfigDefined) 79 { 80 } 81 82 public bool HandleErrors 83 { 84 get 85 { 86 return boolHandleErrors; 87 } 88 set 89 { 90 boolHandleErrors = value; 91 } 92 } 93 94 public string LastError 95 { 96 get 97 { 98 return strLastError; 99 } 100 } 101 102 public bool LogErrors 103 { 104 get 105 { 106 return boolLogError; 107 } 108 set 109 { 110 boolLogError=value; 111 } 112 } 113 114 public string LogFile 115 { 116 get 117 { 118 return strLogFile; 119 } 120 set 121 { 122 strLogFile = value; 123 } 124 } 125 126 public int AddParameter(string name,object value) 127 { 128 DbParameter p = objFactory.CreateParameter(); 129 p.ParameterName = name; 130 p.Value=value; 131 return objCommand.Parameters.Add(p); 132 } 133 134 public int AddParameter(DbParameter parameter) 135 { 136 return objCommand.Parameters.Add(parameter); 137 } 138 139 public DbCommand Command 140 { 141 get 142 { 143 return objCommand; 144 } 145 } 146 147 public void BeginTransaction() 148 { 149 if (objConnection.State == System.Data.ConnectionState.Closed) 150 { 151 objConnection.Open(); 152 } 153 objCommand.Transaction = objConnection.BeginTransaction(); 154 } 155 156 public void CommitTransaction() 157 { 158 objCommand.Transaction.Commit(); 159 objConnection.Close(); 160 } 161 162 public void RollbackTransaction() 163 { 164 objCommand.Transaction.Rollback(); 165 objConnection.Close(); 166 } 167 168 public int ExecuteNonQuery(string query) 169 { 170 return ExecuteNonQuery(query, CommandType.Text, ConnectionState.CloseOnExit); 171 } 172 173 public int ExecuteNonQuery(string query,CommandType commandtype) 174 { 175 return ExecuteNonQuery(query, commandtype, ConnectionState.CloseOnExit); 176 } 177 178 public int ExecuteNonQuery(string query,ConnectionState connectionstate) 179 { 180 return ExecuteNonQuery(query,CommandType.Text,connectionstate); 181 } 182 183 public int ExecuteNonQuery(string query,CommandType commandtype, ConnectionState connectionstate) 184 { 185 objCommand.CommandText = query; 186 objCommand.CommandType = commandtype; 187 int i=-1; 188 try 189 { 190 if (objConnection.State == System.Data.ConnectionState.Closed) 191 { 192 objConnection.Open(); 193 } 194 i = objCommand.ExecuteNonQuery(); 195 } 196 catch (Exception ex) 197 { 198 HandleExceptions(ex); 199 } 200 finally 201 { 202 objCommand.Parameters.Clear(); 203 if (connectionstate == ConnectionState.CloseOnExit) 204 { 205 objConnection.Close(); 206 } 207 } 208 209 return i; 210 } 211 212 public object ExecuteScalar(string query) 213 { 214 return ExecuteScalar(query, CommandType.Text, ConnectionState.CloseOnExit); 215 } 216 217 public object ExecuteScalar(string query,CommandType commandtype) 218 { 219 return ExecuteScalar(query, commandtype, ConnectionState.CloseOnExit); 220 } 221 222 public object ExecuteScalar(string query, ConnectionState connectionstate) 223 { 224 return ExecuteScalar(query, CommandType.Text, connectionstate); 225 } 226 227 public object ExecuteScalar(string query,CommandType commandtype, ConnectionState connectionstate) 228 { 229 objCommand.CommandText = query; 230 objCommand.CommandType = commandtype; 231 object o = null; 232 try 233 { 234 if (objConnection.State == System.Data.ConnectionState.Closed) 235 { 236 objConnection.Open(); 237 } 238 o = objCommand.ExecuteScalar(); 239 } 240 catch (Exception ex) 241 { 242 HandleExceptions(ex); 243 } 244 finally 245 { 246 objCommand.Parameters.Clear(); 247 if (connectionstate == ConnectionState.CloseOnExit) 248 { 249 objConnection.Close(); 250 } 251 } 252 253 return o; 254 } 255 256 public DbDataReader ExecuteReader(string query) 257 { 258 return ExecuteReader(query, CommandType.Text, ConnectionState.CloseOnExit); 259 } 260 261 public DbDataReader ExecuteReader(string query,CommandType commandtype) 262 { 263 return ExecuteReader(query, commandtype, ConnectionState.CloseOnExit); 264 } 265 266 public DbDataReader ExecuteReader(string query, ConnectionState connectionstate) 267 { 268 return ExecuteReader(query, CommandType.Text, connectionstate); 269 } 270 271 public DbDataReader ExecuteReader(string query,CommandType commandtype, ConnectionState connectionstate) 272 { 273 objCommand.CommandText = query; 274 objCommand.CommandType = commandtype; 275 DbDataReader reader=null; 276 try 277 { 278 if (objConnection.State == System.Data.ConnectionState.Closed) 279 { 280 objConnection.Open(); 281 } 282 if (connectionstate == ConnectionState.CloseOnExit) 283 { 284 reader = objCommand.ExecuteReader(CommandBehavior.CloseConnection); 285 } 286 else 287 { 288 reader = objCommand.ExecuteReader(); 289 } 290 291 } 292 catch (Exception ex) 293 { 294 HandleExceptions(ex); 295 } 296 finally 297 { 298 objCommand.Parameters.Clear(); 299 } 300 301 return reader; 302 } 303 304 public DataSet ExecuteDataSet(string query) 305 { 306 return ExecuteDataSet(query, CommandType.Text, ConnectionState.CloseOnExit); 307 } 308 309 public DataSet ExecuteDataSet(string query,CommandType commandtype) 310 { 311 return ExecuteDataSet(query, commandtype, ConnectionState.CloseOnExit); 312 } 313 314 public DataSet ExecuteDataSet(string query,ConnectionState connectionstate) 315 { 316 return ExecuteDataSet(query, CommandType.Text, connectionstate); 317 } 318 319 public DataSet ExecuteDataSet(string query,CommandType commandtype, ConnectionState connectionstate) 320 { 321 DbDataAdapter adapter = objFactory.CreateDataAdapter(); 322 objCommand.CommandText = query; 323 objCommand.CommandType = commandtype; 324 adapter.SelectCommand = objCommand; 325 DataSet ds = new DataSet(); 326 try 327 { 328 adapter.Fill(ds); 329 } 330 catch (Exception ex) 331 { 332 HandleExceptions(ex); 333 } 334 finally 335 { 336 objCommand.Parameters.Clear(); 337 if (connectionstate == ConnectionState.CloseOnExit) 338 { 339 if (objConnection.State == System.Data.ConnectionState.Open) 340 { 341 objConnection.Close(); 342 } 343 } 344 } 345 return ds; 346 } 347 348 private void HandleExceptions(Exception ex) 349 { 350 if (LogErrors) 351 { 352 WriteToLog(ex.Message); 353 } 354 if (HandleErrors) 355 { 356 strLastError = ex.Message; 357 } 358 else 359 { 360 throw ex; 361 } 362 } 363 364 private void WriteToLog(string msg) 365 { 366 StreamWriter writer= File.AppendText(LogFile); 367 writer.WriteLine(DateTime.Now.ToString() + " - " + msg); 368 writer.Close(); 369 } 370 371 public void Dispose() 372 { 373 objConnection.Close(); 374 objConnection.Dispose(); 375 objCommand.Dispose(); 376 } 377 378 } 379 380 public enum Providers 381 { 382 SqlServer,OleDb,Oracle,ODBC,ConfigDefined 383 } 384 385 public enum ConnectionState 386 { 387 KeepOpen,CloseOnExit 388 } 389}