/// <summary>
/// Executes Sql command and returns execution result.
/// Command text, type and parameters are taken from method using reflection.
/// Command parameter values are taken from method parameter values.
/// </summary>
/// <param name="connection">Connection property.</param>
/// <param name="transaction">Transaction property.</param>
/// <param name="method"><see cref="MethodInfo"/> type object from which the command object is built.</param>
/// <param name="values">Array of values for the command parameters.</param>
/// <param name="autoCloseConnection">Determines if the connection must be closed after the command execution.</param>
/// <returns></returns>
public static object ExecuteMethodAndGetResult(DbConnection connection, DbTransaction transaction, MethodInfo method, object[] values, bool autoCloseConnection)
{
if (method == null)
{
// this is done because this method can be called explicitly from code.
method = (MethodInfo) (new StackTrace().GetFrame(1).GetMethod());
}
// create command object
DatabaseHelper db = new DatabaseHelper();
DbCommand command = db.Command;
command.Connection = connection;
command.Transaction = transaction;
// define default command properties (command text, command type and missing schema action)
string commandText = method.Name;
SWCommandType swCommandType = SWCommandType.StoredProcedure;
MissingSchemaAction missingSchemaAction = MissingSchemaAction.Add;
// try to get command properties from calling method attribute
SWCommandAttribute commandAttribute = (SWCommandAttribute)Attribute.GetCustomAttribute(method, typeof(SWCommandAttribute));
if(commandAttribute != null)
{
if(commandAttribute.CommandText.Length > 0)
{
commandText = commandAttribute.CommandText;
}
swCommandType = commandAttribute.CommandType;
missingSchemaAction = commandAttribute.MissingSchemaAction;
}
// set command text
command.CommandText = commandText;
// set command type
switch(swCommandType)
{
case SWCommandType.InsertUpdate: command.CommandType = CommandType.Text; break;
case SWCommandType.StoredProcedure: command.CommandType = CommandType.StoredProcedure; break;
case SWCommandType.Text: command.CommandType = CommandType.Text; break;
default: command.CommandType = CommandType.Text; break;
}
// define command parameters.
// In this step command text can be changed.
int[] indexes = new int[values.Length];
GenerateCommandParameters(command, method, values, indexes, swCommandType);
// execute command
object result = null;
result = ExecuteCommand(command, method.ReturnType, autoCloseConnection, missingSchemaAction);
// return command parameter values
for(int i = 0; i < values.Length; ++i)
{
int sqlParamIndex = indexes[i];
if(sqlParamIndex >= 0)
{
values[i] = command.Parameters[i].Value;
}
}
// adjust null result
if(result == null || result == System.DBNull.Value)
{
if(commandAttribute != null)
{
result = commandAttribute.ReturnIfNull;
if(method.ReturnType == typeof(DateTime))
{
result = new DateTime((int)commandAttribute.ReturnIfNull);
}
}
}
return result;
}
/// <summary>
/// Executes Sql command and returns execution result.
/// Command text, type and parameters are taken from method using reflection.
/// Command parameter values are taken from method parameter values.
/// </summary>
/// <param name="connection">Connection property.</param>
/// <param name="transaction">Transaction property.</param>
/// <param name="method"><see cref="MethodInfo"/> type object from which the command object is built.</param>
/// <param name="values">Array of values for the command parameters.</param>
/// <param name="autoCloseConnection">Determines if the connection must be closed after the command execution.</param>
/// <returns></returns>
public static object ExecuteMethodAndGetResult(DbConnection connection, DbTransaction transaction, MethodInfo method, object[] values, bool autoCloseConnection)
{
if (method == null)
{
// this is done because this method can be called explicitly from code.
method = (MethodInfo)(new StackTrace().GetFrame(1).GetMethod());
}
// create command object
DatabaseHelper db = new DatabaseHelper();
DbCommand command = db.Command;
command.Connection = connection;
command.Transaction = transaction;
// define default command properties (command text, command type and missing schema action)
string commandText = method.Name;
SWCommandType swCommandType = SWCommandType.StoredProcedure;
MissingSchemaAction missingSchemaAction = MissingSchemaAction.Add;
// try to get command properties from calling method attribute
SWCommandAttribute commandAttribute = (SWCommandAttribute)Attribute.GetCustomAttribute(method, typeof(SWCommandAttribute));
if (commandAttribute != null)
{
if (commandAttribute.CommandText.Length > 0)
{
commandText = commandAttribute.CommandText;
}
swCommandType = commandAttribute.CommandType;
missingSchemaAction = commandAttribute.MissingSchemaAction;
}
// set command text
command.CommandText = commandText;
// set command type
switch (swCommandType)
{
case SWCommandType.InsertUpdate: command.CommandType = CommandType.Text; break;
case SWCommandType.StoredProcedure: command.CommandType = CommandType.StoredProcedure; break;
case SWCommandType.Text: command.CommandType = CommandType.Text; break;
default: command.CommandType = CommandType.Text; break;
}
// define command parameters.
// In this step command text can be changed.
int[] indexes = new int[values.Length];
GenerateCommandParameters(command, method, values, indexes, swCommandType);
// execute command
object result = null;
result = ExecuteCommand(command, method.ReturnType, autoCloseConnection, missingSchemaAction);
// return command parameter values
for (int i = 0; i < values.Length; ++i)
{
int sqlParamIndex = indexes[i];
if (sqlParamIndex >= 0)
{
values[i] = command.Parameters[i].Value;
}
}
// adjust null result
if (result == null || result == System.DBNull.Value)
{
if (commandAttribute != null)
{
result = commandAttribute.ReturnIfNull;
if (method.ReturnType == typeof(DateTime))
{
result = new DateTime((int)commandAttribute.ReturnIfNull);
}
}
}
return(result);
}
/// <summary>
/// Executes a command object according to the return type.
/// </summary>
/// <param name="cmd">The command object.</param>
/// <param name="retType">Return type</param>
/// <param name="autoCloseConnection">Determines if the connection must be closed after the command execution.</param>
/// <param name="missingSchemaAction">Determines <see cref="MissingSchemaAction"/> type value in case of filling a datasets.</param>
/// <returns></returns>
private static object ExecuteCommand(DbCommand cmd, Type retType, bool autoCloseConnection, MissingSchemaAction missingSchemaAction)
{
object result = null;
lock(cmd.Connection)
{
if ((cmd.Connection.State != System.Data.ConnectionState.Open) && (cmd.Connection.State == System.Data.ConnectionState.Closed))
cmd.Connection.Open();
if(retType.FullName == "System.Void")
{
cmd.ExecuteNonQuery();
result = null;
}
else if(retType == typeof(DataSet))
{
result = new DatabaseHelper().ExecuteDataSet(cmd.CommandText, cmd.CommandType);
}
else if(retType == typeof(DataTable))
{
result = new DatabaseHelper().ExecuteDataSet(cmd.CommandText, cmd.CommandType).Tables[0];
}
else if (retType == typeof(DbDataReader))
{
DbDataReader dr = null;
if(autoCloseConnection)
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
else
dr = cmd.ExecuteReader();
result = dr;
}
else if (retType == typeof(DbDataAdapter))
{
throw new System.NotImplementedException();
/*
DbDataAdapter da = new DbDataAdapter(cmd);
da.MissingSchemaAction = missingSchemaAction;
result = da;
* */
}
else if(retType == typeof(DbCommand))
{
result = cmd;
}
else
{
result = cmd.ExecuteScalar();
}
if (autoCloseConnection && !(retType == typeof(DbDataReader)))
cmd.Connection.Close();
}
return result;
}
/// <summary>
/// Executes a command object according to the return type.
/// </summary>
/// <param name="cmd">The command object.</param>
/// <param name="retType">Return type</param>
/// <param name="autoCloseConnection">Determines if the connection must be closed after the command execution.</param>
/// <param name="missingSchemaAction">Determines <see cref="MissingSchemaAction"/> type value in case of filling a datasets.</param>
/// <returns></returns>
private static object ExecuteCommand(DbCommand cmd, Type retType, bool autoCloseConnection, MissingSchemaAction missingSchemaAction)
{
object result = null;
lock (cmd.Connection)
{
if ((cmd.Connection.State != System.Data.ConnectionState.Open) && (cmd.Connection.State == System.Data.ConnectionState.Closed))
{
cmd.Connection.Open();
}
if (retType.FullName == "System.Void")
{
cmd.ExecuteNonQuery();
result = null;
}
else if (retType == typeof(DataSet))
{
result = new DatabaseHelper().ExecuteDataSet(cmd.CommandText, cmd.CommandType);
}
else if (retType == typeof(DataTable))
{
result = new DatabaseHelper().ExecuteDataSet(cmd.CommandText, cmd.CommandType).Tables[0];
}
else if (retType == typeof(DbDataReader))
{
DbDataReader dr = null;
if (autoCloseConnection)
{
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
else
{
dr = cmd.ExecuteReader();
}
result = dr;
}
else if (retType == typeof(DbDataAdapter))
{
throw new System.NotImplementedException();
/*
* DbDataAdapter da = new DbDataAdapter(cmd);
* da.MissingSchemaAction = missingSchemaAction;
* result = da;
* */
}
else if (retType == typeof(DbCommand))
{
result = cmd;
}
else
{
result = cmd.ExecuteScalar();
}
if (autoCloseConnection && !(retType == typeof(DbDataReader)))
{
cmd.Connection.Close();
}
}
return(result);
}
