/// <summary> /// Execute query stored in DatabaseConnectionParameters.Query /// </summary> /// <param name="dbParameters">Connection Parameters</param> /// <returns>Returns IDataReader if command succeeded. Returns NULL if command fails. Command execution success will also be stored in DatabaseConnectionParameters.LastCommandSucceeded</returns> public static IDataReader ExecuteReader(DatabaseConnectionParameters dbParameters) { try { var mySqlCommand = new MySqlCommand(dbParameters.QueryString, dbParameters.MySQLConnection); if (dbParameters.QueryParameters != null) { foreach (MySqlParameter item in dbParameters.QueryParameters) { mySqlCommand.Parameters.Add(item); } } mySqlCommand.CommandTimeout = dbParameters.CommandTimeout; mySqlCommand.CommandType = dbParameters.CommandType; dbParameters.LastCommandSucceeded = true; return(mySqlCommand.ExecuteReader()); } catch (Exception excp) { Logs.AddException(excp); dbParameters.LastCommandSucceeded = false; return(null); } }
/// <summary> /// Execute query stored in DatabaseConnectionParameters.Query /// </summary> /// <param name="dbParameters">Connection Parameters</param> /// <returns>Returns the number of rows affected if command succeeds. Returns -1 if command fails. Command execution success will also be stored in DatabaseConnectionParameters.LastCommandSucceeded</returns> public static int ExecuteNonQuery(DatabaseConnectionParameters dbParameters) { try { var oleDbCommand = new OleDbCommand(dbParameters.QueryString, dbParameters.OLEDBConnection); if (dbParameters.QueryParameters != null) { foreach (OleDbParameter item in dbParameters.QueryParameters) { oleDbCommand.Parameters.Add(item); } } oleDbCommand.CommandTimeout = dbParameters.CommandTimeout; oleDbCommand.CommandType = dbParameters.CommandType; dbParameters.LastCommandSucceeded = true; return(oleDbCommand.ExecuteNonQuery()); } catch (Exception excp) { Logs.AddException(excp); dbParameters.LastCommandSucceeded = false; return(-1); } }
/// <summary> /// Execute query stored in DatabaseConnectionParameters.Query /// </summary> /// <param name="dbParameters">Connection Parameters</param> /// <returns>Returns a single value as object if command succeeds. May return NULL whether command fails or not. Command execution success will also be stored in DatabaseConnectionParameters.LastCommandSucceeded</returns> public static object ExecuteScalar(DatabaseConnectionParameters dbParameters) { try { var sqlCommand = new SqlCommand(dbParameters.QueryString, dbParameters.SQLConnection); if (dbParameters.QueryParameters != null) { foreach (SqlParameter item in dbParameters.QueryParameters) { sqlCommand.Parameters.Add(item); } } sqlCommand.CommandTimeout = dbParameters.CommandTimeout; sqlCommand.CommandType = dbParameters.CommandType; dbParameters.LastCommandSucceeded = true; return(sqlCommand.ExecuteScalar()); } catch (Exception excp) { Logs.AddException(excp); dbParameters.LastCommandSucceeded = false; return(null); } }
/// <summary> /// Check if connection is open /// </summary> /// <param name="dbParameters">Connection Parameters</param> /// <returns>Returns TRUE if connection is open</returns> public static bool IsOpen(DatabaseConnectionParameters dbParameters) { try { return(dbParameters.OLEDBConnection != null && dbParameters.OLEDBConnection.State == ConnectionState.Open); } catch (Exception excp) { Logs.AddException(excp); return(false); } }
/// <summary> /// Disconnect from OleDB /// </summary> /// <param name="dbParameters">Connection Parameters</param> /// <returns></returns> public static bool DisconnectFromDatabase(DatabaseConnectionParameters dbParameters) { try { dbParameters.OLEDBConnection.Close(); dbParameters.LastCommandSucceeded = (dbParameters.OLEDBConnection.State == ConnectionState.Closed); return(dbParameters.LastCommandSucceeded); } catch (Exception excp) { Logs.AddException(excp); dbParameters.LastCommandSucceeded = false; return(dbParameters.LastCommandSucceeded); } }
/// <summary> /// Connect to MySQL Server using given parameters /// </summary> /// <param name="dbParameters">Connection Parameters</param> /// <returns>Returns TRUE is connection succeeds. Returns FALSE if connection fails.</returns> public static bool ConnectToDatabase(DatabaseConnectionParameters dbParameters) { try { dbParameters.MySQLConnection = new MySqlConnection(dbParameters.ConnectionString); if (dbParameters.MySQLConnection.State == System.Data.ConnectionState.Closed) { dbParameters.MySQLConnection.Open(); } dbParameters.LastCommandSucceeded = (dbParameters.MySQLConnection.State == System.Data.ConnectionState.Open); return(dbParameters.LastCommandSucceeded); } catch (Exception excp) { Logs.AddException(excp); dbParameters.LastCommandSucceeded = false; return(false); } }
/// <summary> /// Build INSERT, UPDATE, DELETE queries /// </summary> /// <param name="dbParameters">Connection Parameters</param> /// <returns>Returns the number of rows affected if command succeeds. Returns -1 if command fails. Command execution success will also be stored in DatabaseConnectionParameters.LastCommandSucceeded</returns> public static int BuildCommand(DatabaseConnectionParameters dbParameters) { try { var oleDbCommand = new OleDbCommand(); oleDbCommand.CommandText = dbParameters.QueryString; oleDbCommand.Connection = dbParameters.OLEDBConnection; oleDbCommand.CommandType = dbParameters.CommandType; oleDbCommand.CommandTimeout = dbParameters.CommandTimeout; if (dbParameters.QueryParameters != null) { foreach (OleDbParameter item in dbParameters.QueryParameters) { oleDbCommand.Parameters.Add(item); } } var oleDbAdapter = new OleDbDataAdapter(); switch (dbParameters.CommandBuildType) { case CommandBuildType.Insert: oleDbAdapter.InsertCommand = oleDbCommand; break; case CommandBuildType.Update: oleDbAdapter.UpdateCommand = oleDbCommand; break; case CommandBuildType.Delete: oleDbAdapter.DeleteCommand = oleDbCommand; break; default: throw new Exception("Build Type missing"); } dbParameters.LastCommandSucceeded = true; switch (dbParameters.DataContainerType) { case DataContainerType.DataTable: return(oleDbAdapter.Update(dbParameters.DataTableContainer)); case DataContainerType.DataSet: return(oleDbAdapter.Update(dbParameters.DataSetContainer)); case DataContainerType.DataSetWithTable: return(oleDbAdapter.Update(dbParameters.DataSetContainer, dbParameters.DataTableContainerName)); case DataContainerType.DataRowsCollection: return(oleDbAdapter.Update(dbParameters.DataRowsCollectionContainer)); default: throw new Exception("Data Container Type missing"); } } catch (Exception excp) { Logs.AddException(excp); dbParameters.LastCommandSucceeded = false; return(-1); } }