/// <summary>Execute Scalar Query </summary>
/// <param name="dv">DataValue Object</param>
/// <param name="query">The Select Query</param>
/// <returns>If The Execute Scalar Returns the Null, the function will return
/// the empty string otherwise the value</returns>
/// <exception name="ArgumentNullException"></exception>
/// <exception name="InvalidOperationException"></exception>
public ResultArgs ExecuteScalar(DataValue dv, string query, EnumCommand.SQLType sqlType = EnumCommand.SQLType.Static)
{
ResultArgs resultArgs = new ResultArgs();
string ScalarValue = string.Empty;
SqlCommand Command = null;
SqlConnection Con = OpenConnection();
try
{
if (string.IsNullOrEmpty(query))
{
// throw new ArgumentNullException("Query is empty!", "ExecuteScalar(DataValue dv, string query)");
using (ErrorLog objlog = new ErrorLog())
{
objlog.WriteError("Error Handler", "ExecuteScalar(DataValue,string)", query, "Query is empty!");
}
}
if (dv == null)
{
Command = new SqlCommand(query, Con);
}
else
{
Command = SetSQLCommand(dv, query, Con, sqlType);
}
Object obj = Command.ExecuteScalar();
if (obj != null)
{
ScalarValue = obj.ToString();
resultArgs.Success = true;
}
}
catch (InvalidOperationException ex)
{
using (ErrorLog objHandler = new ErrorLog())
{
objHandler.WriteError("MysqlHandler", "ExecuteScalar(DataValue dv, string query)", ex.Message);
objHandler.WriteError(ex);
}
}
catch (Exception ex)
{
using (ErrorLog objHandler = new ErrorLog())
{
objHandler.WriteError("MysqlHandler", "ExecuteScalar(DataValue dv, string query)", ex.Message);
objHandler.WriteError(ex);
}
}
finally
{
if (Command != null)
{
Command.Dispose();
}
Command = null;
CloseConnection();
}
resultArgs.StringResult = ScalarValue;
return(resultArgs);
}
/// <summary>Check a parameter is available in the query </summary>
/// <param name="query">The Select,Insert,Update or Delete Query</param>
/// <param name="fieldName">The Filed Name</param>
/// <returns>if the query contains the field,it return the filed name other wise returns the empty</returns>
private string GetParameter(string query, string fieldName, EnumCommand.SQLType sqlType)
{
string param = fieldName;
if (sqlType == EnumCommand.SQLType.Static)
{
param = fieldName.ToUpper();
param = query.Contains(param) ? param : string.Empty;
}
return(param);
}
private SqlCommand SetSQLCommand(DataValue dv, string query, SqlConnection connection, EnumCommand.SQLType sqlType)
{
string Param = "";
string FieldName = "";
SqlCommand Command = new SqlCommand(query, connection);
Command.CommandType = (sqlType == EnumCommand.SQLType.StoredProcedure) ? CommandType.StoredProcedure : CommandType.Text;
foreach (DataValueBase dvBase in dv)
{
try
{
FieldName = dvBase.FieldName;
Param = GetParameter(query, FieldName, sqlType);
if (Param != string.Empty)
{
SqlParameter Parameter = new SqlParameter(Param, GetSQLFieldType(dvBase.FieldDataType));
if (dvBase.FieldDataType == EnumCommand.DataType.ByteArray)
{
if (string.IsNullOrEmpty(dvBase.FieldValue))
{
Parameter.Value = SqlBinary.Null;
}
else
{
Parameter.Value = Encoding.ASCII.GetBytes(dvBase.FieldValue);
}
}
else
{
if (string.IsNullOrEmpty(dvBase.FieldValue))
{
Parameter.Value = DBNull.Value;
}
else
{
Parameter.Value = dvBase.FieldValue;
}
}
Command.Parameters.Add(Parameter);
}
}
catch (Exception ex)
{
throw ex;
}
}
return(Command);
}
/// <summary>Add the parameter name, value and data type to the MySql Command Object </summary>
/// <param name="dv">DataValue Object</param>
/// <param name="query">The Insert,Update,Delete or Select Query </param>
/// <returns>MySql Command</returns>
private SqlCommand SetMySqlCommand(DataValue param, string query, SqlConnection connection, EnumCommand.SQLType sqlType)
{
string Param = "";
string FieldName = "";
SqlCommand Command = new SqlCommand(query, connection);
Command.CommandType = (sqlType == EnumCommand.SQLType.StoredProcedure) ? CommandType.StoredProcedure : CommandType.Text;
if (param != null)
{
foreach (SqlParameter p in param)
{
if (p != null)
{
// Check for derived output value with no value assigned
if ((p.Direction == ParameterDirection.InputOutput ||
p.Direction == ParameterDirection.Input) &&
(p.Value == null))
{
p.Value = DBNull.Value;
}
Command.Parameters.Add(p);
}
}
}
return(Command);
}
/// <summary>Execute the Select Query </summary>
/// <param name="dv">DataValue Object</param>
/// <param name="query">The Select Query</param>
/// <returns>DataTable with the records</returns>
/// <exception name="ArgumentNullException"></exception>
/// <exception name="InvalidOperationException"></exception>
public ResultArgs FetchData(string query, EnumCommand.DataSource dataSourceType, EnumCommand.SQLType sqlType = EnumCommand.SQLType.Static, DataValue dv = null)
{
ResultArgs result = new ResultArgs();
result.Success = false;
SqlDataAdapter Adapter;
SqlCommand Command = null;
DataSet ds = new DataSet();
SqlConnection Con = OpenConnection();
try
{
if (string.IsNullOrEmpty(query))
{
//throw new ArgumentNullException("Query is empty", "FetchData(DataValue dv, string query)");
using (ErrorLog objlog = new ErrorLog())
{
result.Success = false;
objlog.WriteError("Error Handler", "FetchData(DataValue,string)", query, "Query is empty!");
}
}
else
{
if (dv == null)
{
Command = new SqlCommand(query, Con);
}
else
{
Command = SetSQLCommand(dv, query, Con, sqlType);
}
result.dataSource = null;
try
{
if (Con.State == ConnectionState.Open && Command != null)
{
switch (dataSourceType)
{
case EnumCommand.DataSource.DataSet:
{
Adapter = new SqlDataAdapter(Command);
if (result.dataSource == null)
{
result.dataSource = new DataSet();
}
result.RowsAffected = Adapter.Fill(result.dataSource as DataSet);
result.Success = true;
break;
}
case EnumCommand.DataSource.DataView:
{
Adapter = new SqlDataAdapter(Command);
if (result.dataSource == null)
{
result.dataSource = new DataTable();
}
result.RowsAffected = Adapter.Fill(result.dataSource as DataTable);
result.dataSource = ((DataTable)result.dataSource).DefaultView;
result.Success = true;
break;
}
case EnumCommand.DataSource.DataReader:
{
result.dataSource = Command.ExecuteReader();
result.RowsAffected = ((SqlDataReader)result.dataSource).RecordsAffected;
result.Success = true;
break;
}
case EnumCommand.DataSource.Scalar:
{
result.dataSource = Command.ExecuteScalar();
if (result.dataSource != null)
{
result.RowsAffected = 1;
result.Success = true;
}
else
{
result.RowsAffected = 0;
result.Success = true;
}
break;
}
default:
{
Adapter = new SqlDataAdapter(Command);
if (result.dataSource == null)
{
result.dataSource = new DataTable();
}
result.RowsAffected = Adapter.Fill(result.dataSource as DataTable);
result.Success = true;
break;
}
}
}
}
catch (Exception e)
{
//Update Exception
result.Exception = e;
result.Success = false;
using (ErrorLog objlog = new ErrorLog())
{
objlog.WriteError("Error Handler", "FetchData(DataValue,string)", query, e.Message + " Query is empty!");
}
//new ErrorLog().WriteError("MySQLDataHandler", "Fetch", e.Message, Command.CommandText, CommonMethods.GetExceptionLineNo(e));
}
finally
{
// CloseConnection(TransactionType.None);
CloseConnection();
}
//result.ShowMessage("Updated");
if (result.Success)
{
result.DataSource.Data = result.dataSource;
}
//Adapter = new SqlDataAdapter(Command);
//Adapter.Fill(ds);
}
}
catch (Exception ex)
{
result.Success = false;
using (ErrorLog objHandler = new ErrorLog())
{
objHandler.WriteError("MysqlHandler", "FetchData", ex.Message);
}
}
finally
{
if (Command != null)
{
Command.Dispose();
}
Command = null;
CloseConnection();
}
return(result);
}
public ResultArgs FecthDataAsList <T>(string query, EnumCommand.SQLType sqlType = EnumCommand.SQLType.Static, DataValue param = null) where T : new()
{
ResultArgs result = new ResultArgs();
result.Success = false;
SqlCommand Command = null;
DataSet ds = new DataSet();
SqlConnection Con = OpenConnection();
List <T> res = new List <T>();
try
{
if (string.IsNullOrEmpty(query))
{
//throw new ArgumentNullException("Query is empty", "FetchData(DataValue dv, string query)");
using (ErrorLog objlog = new ErrorLog())
{
result.Success = false;
objlog.WriteError("Error Handler", "FetchData(DataValue,string)", query, "Query is empty!");
}
}
else
{
if (param == null)
{
Command = new SqlCommand(query, Con);
}
else
{
Command = SetSQLCommand(param, query, Con, sqlType);
}
result.dataSource = null;
try
{
if (Con.State == ConnectionState.Open && Command != null)
{
result.dataSource = Command.ExecuteReader();
SqlDataReader SqlDataReader = ((SqlDataReader)result.dataSource);
result.RowsAffected = ((SqlDataReader)result.dataSource).RecordsAffected;
result.Success = true;
Command.Parameters.Clear();
if (SqlDataReader.HasRows)
{
while (SqlDataReader.Read())
{
T t = new T();
object objValue;
for (int inc = 0; inc < SqlDataReader.FieldCount; inc++)
{
objValue = null;
Type type = t.GetType();
PropertyInfo prop = type.GetProperty(SqlDataReader.GetName(inc));
objValue = SqlDataReader.GetValue(inc).ToString();
if (objValue == DBNull.Value)
{
objValue = null;
}
prop.SetValue(t, objValue, null);
}
res.Add(t);
}
result.DataSource.Data = res;
}
}
}
catch (Exception e)
{
//Update Exception
result.Exception = e;
result.Success = false;
using (ErrorLog objlog = new ErrorLog())
{
objlog.WriteError("Error Handler", "FetchData(DataValue,string)", query, e.Message + " Query is empty!");
}
//new ErrorLog().WriteError("MySQLDataHandler", "Fetch", e.Message, Command.CommandText, CommonMethods.GetExceptionLineNo(e));
}
finally
{
// CloseConnection(TransactionType.None);
Command.Parameters.Clear();
CloseConnection();
}
}
}
catch (Exception ex)
{
result.Success = false;
using (ErrorLog objHandler = new ErrorLog())
{
objHandler.WriteError("MysqlHandler", "FetchData", ex.Message);
}
}
finally
{
if (Command != null)
{
Command.Dispose();
}
Command = null;
CloseConnection();
}
return(result);
}
/// <summary>Execute the Insert,Update,Delete Query </summary>
/// <param name="dv">DataValue Object</param>
/// <param name="query">The Insert,Update or Delete Query</param>
/// <param name="getlastinsert_ID">LAST_INSERT_ID if set to true
/// bind last insert id in the result args</param>
/// <returns>Number of Rows Affected by the Query
/// greater than 1 --> success
/// 0 --> failure
/// -1 --> table referred(delete mode)
/// -2 --> record referred(delete mode)
/// </returns>
/// <exception name="ArgumentNullException"></exception>
/// <exception name="InvalidOperationException"></exception>
public ResultArgs ExecuteCommand(DataValue dv, string query, bool getlastinsert_ID, EnumCommand.SQLType sqlType = EnumCommand.SQLType.Static, string spOutput = null)
{
ResultArgs result = new ResultArgs();
result.Success = false;
SqlCommand Command = null;
SqlConnection Con = OpenConnection();
try
{
if (string.IsNullOrEmpty(query))
{
//throw new ArgumentNullException("Query is empty", "ExecuteCommand(DataValue dv, string query");
using (ErrorLog objlog = new ErrorLog())
{
objlog.WriteError("Error Handler", "ExecuteCommand(DataValue,string)", query, "Query is empty!");
}
}
else
{
if (dv == null)
{
Command = new SqlCommand(query, Con);
}
else
{
Command = SetSQLCommand(dv, query, Con, sqlType);
}
if (spOutput != null)
{
SqlParameter outParameter = new SqlParameter(spOutput, GetSQLFieldType(EnumCommand.DataType.Int64));
outParameter.Direction = ParameterDirection.Output;
Command.Parameters.Add(outParameter);
}
result.RowsAffected = Command.ExecuteNonQuery();
if (spOutput != null)
{
result.ReturnValue = Command.Parameters[spOutput].Value;
}
//get the last insert id if flag is set to true
if (getlastinsert_ID)
{
getRowUniqueId = getlastinsert_ID; //Set true to return the last insert id - not handled for stored procedure
SetRowUniqueIdentifierValue(result, Command);
}
result.Success = true;
}
}
catch (InvalidOperationException ex)
{
//throw new InvalidOperationException("Problem in Execute Command " + iex.Message, iex);
result.RowsAffected = ex.Message.ToLower().Contains("foreign keys") ? -1 : ex.Message.ToLower().Contains("integrity constraint") ? -2 : 0;
using (ErrorLog objHandler = new ErrorLog())
{
objHandler.WriteError("Error Handler", "ExecuteCommand(DataValue,string)", query + "Err MSg: " + ex.Message, "Query is empty!");
// objHandler.WriteLogtoDB(ex, "ExecuteCommand(DataValue dv, string query)", "MysqlHandler");
}
}
catch (Exception ex)
{
result.RowsAffected = ex.Message.ToLower().Contains("foreign key") ? -1 : ex.Message.ToLower().Contains("integrity constraint") ? -2 : 0;
using (ErrorLog objHandler = new ErrorLog())
{
objHandler.WriteError("Error Handler", "ExecuteCommand(DataValue,string)", query + "Err MSg: " + ex.Message, "Query is empty!");
objHandler.WriteError("MysqlHandler", "ExecuteCommand(DataValue dv, string query)", ex.Message);
}
}
finally
{
if (Command != null)
{
Command.Dispose();
}
Command = null;
CloseConnection();
}
return(result);
}
/// <summary>
/// This mehtod is to save image byte
/// </summary>
/// <param name="dv">data values</param>
/// <param name="query">query</param>
/// <param name="imageByte">imageByte</param>
/// <param name="fieldName">parametername</param>
/// <returns>1 --> success
/// 0 or le 0 --> failure</returns>
public int ExecuteImageByte(OracleParameter[] dv, string query, byte[] imageByte, string fieldName, EnumCommand.SQLType sqlType = EnumCommand.SQLType.Static)
{
int RowsAffected = 0;
OracleCommand Command = null;
OracleConnection Con = OpenConnection();
try
{
Command = SetOracleCommand(dv, query, Con, sqlType);
//if (imageByte.Length>0)
Command.Parameters.Add("?" + fieldName, OracleType.Blob, imageByte.Length, fieldName).Value = (object)imageByte;
RowsAffected = Command.ExecuteNonQuery();
}
catch (Exception ex)
{
using (ErrorLog objHandler = new ErrorLog())
{
objHandler.WriteError("MysqlHandler", "ExecuteImageByte(DataValue dv, string query, byte[] imageByte)", ex.Message);
objHandler.WriteError(ex);
}
}
finally
{
if (Command != null)
{
Command.Dispose();
}
Command = null;
CloseConnection();
}
return(RowsAffected);
}
/// <summary>
/// This method is to execute sql with image data.(nominee identity details are saveed through this method)
/// </summary>
/// <param name="dv">data value</param>
/// <param name="query">query</param>
/// <param name="digitalByte">digital signature</param>
/// <returns>1 --> success
/// 0 or le 0 --> failure</returns>
public int ExecuteByte(OracleParameter[] dv, string query, byte[] digitalByte, int type, EnumCommand.SQLType sqlType = EnumCommand.SQLType.Static)
{
int RowsAffected = 0;
OracleCommand Command = null;
OracleConnection Con = OpenConnection();
try
{
Command = SetOracleCommand(dv, query, Con, sqlType);
if (type > 0)
{
//if (digitalByte.Length>0)
Command.Parameters.Add("?SIGNATURE", OracleType.Blob, digitalByte.Length, "SIGNATURE").Value = (object)digitalByte;
}
else
{
//if (digitalByte.Length > 0)
Command.Parameters.Add("?PHOTO", OracleType.Blob, digitalByte.Length, "PHOTO").Value = (object)digitalByte;
}
RowsAffected = Command.ExecuteNonQuery();
}
catch (Exception ex)
{
using (ErrorLog objHandler = new ErrorLog())
{
objHandler.WriteError("MysqlHandler", "ExecuteByte(DataValue dv, string query, byte[] digitalByte)", ex.Message);
objHandler.WriteError(ex);
}
}
finally
{
if (Command != null)
{
Command.Dispose();
}
Command = null;
CloseConnection();
}
//ServicedComponent.DisposeObject(this);
return(RowsAffected);
}
