/// <summary>
/// Initializes a new instance of the <see cref="SWCommandAttribute"/> class with the specified
/// <see cref="CommandType"/>, <see cref="CommandText"/>
/// and <see cref="ReturnIfNull"/> values
/// </summary>
/// <param name="commandType">Is a value of <see cref="SWCommandAttribute.CommandType"/> property</param>
/// <param name="commandText">Is a value of <see cref="SWCommandAttribute.CommandText"/> property</param>
/// <param name="returnIfNull">Is a value of <see cref="SWCommandAttribute.ReturnIfNull"/> property</param>
/// <param name="missingSchemaAction">Is a value of <see cref="SWCommandAttribute.MissingSchemaAction"/> property</param>
public SWCommandAttribute(SWCommandType commandType, string commandText, object returnIfNull, MissingSchemaAction missingSchemaAction)
{
m_commandType = commandType;
m_commandText = commandText;
m_returnIfNull = returnIfNull;
if (m_returnIfNull is string)
{
if ((string)m_returnIfNull == NullReturnValueToken)
{
m_returnIfNull = null;
}
}
m_missingSchemaAction = missingSchemaAction;
}
/// <summary>
/// Initializes a new instance of the <see cref="SWCommandAttribute"/> class with the specified
/// <see cref="CommandType"/> and <see cref="CommandText"/> values
/// </summary>
/// <param name="commandType"></param>
/// <param name="commandText">Is a value of <see cref="SWCommandAttribute.CommandText"/> property</param>
public SWCommandAttribute(SWCommandType commandType, string commandText)
: this(commandType, commandText, null, MissingSchemaAction.Add)
{
}
/// <summary>
/// Generates command parameters.
/// For some command types the command text can be changed during parameter generating.
/// </summary>
/// <param name="command">Command object.</param>
/// <param name="method"><see cref="MethodInfo"/> type object</param>
/// <param name="values">Array of values for the command parameters.</param>
/// <param name="swCommandType"><see cref="SWCommandType"/> enumeration value</param>
/// <param name="indexes">Array of parameter indices.</param>
private static void GenerateCommandParameters(DbCommand command, MethodInfo method, object[] values, int[] indexes, SWCommandType swCommandType)
{
#region InsertUpdate parts declaration
string sUpdatePart1 = "";
string sUpdatePart2 = "";
string sUpdate = "";
string sInsertPart1 = "";
string sInsertPart2 = "";
string sInsert = "";
string sAutoincrementColumnName = "";
#endregion
//DatabaseHelper db = new DatabaseHelper();
ParameterInfo[] methodParameters = method.GetParameters();
int sqlParamIndex = 0;
for(int paramIndex = 0; paramIndex < methodParameters.Length; ++paramIndex)
{
indexes[paramIndex] = -1;
ParameterInfo paramInfo = methodParameters[paramIndex];
// create command parameter
DbParameter sqlParameter = command.CreateParameter();
// set default values
string paramName = paramInfo.Name;
SWParameterType paramCustType = SWParameterType.Default;
object v = values[paramIndex];
// get parameter attribute and set command parameter settings
SWParameterAttribute paramAttribute = (SWParameterAttribute) Attribute.GetCustomAttribute(paramInfo, typeof(SWParameterAttribute));
if(paramAttribute != null)
{
paramCustType = paramAttribute.ParameterType;
if (paramAttribute.IsNameDefined)
paramName = sqlParameter.ParameterName;
if (paramAttribute.IsTypeDefined)
sqlParameter.DbType = (DbType)paramAttribute.SqlDbType;
if (paramAttribute.IsSizeDefined)
sqlParameter.Size = paramAttribute.Size;
//if (paramAttribute.IsScaleDefined)
// sqlParameter.Scale = paramAttribute.Scale;
//if (paramAttribute.IsPrecisionDefined)
// sqlParameter..Precision = paramAttribute.Precision;
if(CompareTreatAsNullValues(paramAttribute.TreatAsNull, v, paramInfo.ParameterType))
{
v = DBNull.Value;
}
}
// parameter direction
if(paramCustType == SWParameterType.SPReturnValue)
{
sqlParameter.Direction = ParameterDirection.ReturnValue;
sqlParameter.DbType = DbType.Int32;
}
else if (paramInfo.ParameterType.IsByRef)
{
sqlParameter.Direction = paramInfo.IsOut ? ParameterDirection.Output : ParameterDirection.InputOutput;
}
else
{
sqlParameter.Direction = ParameterDirection.Input;
}
// generate parts of InsertUpdate expresion
#region generate parts of InsertUpdate expresion
if(paramCustType == SWParameterType.Identity)
{
if(sAutoincrementColumnName.Length > 0)
{
throw new SqlWrapperException("Only one identity parameter is possible");
}
sAutoincrementColumnName = paramName;
Type reftype = GetRefType(paramInfo.ParameterType);
if(reftype == null)
{
throw new SqlWrapperException("Identity parameter must be ByRef parameter");
}
// check default value
if(paramAttribute.TreatAsNull.ToString() == SWParameterAttribute.NullReturnValueToken)
{
if(Convert.ToInt64(v) <= 0)
{
v = DBNull.Value;
}
}
}
if(swCommandType == SWCommandType.InsertUpdate)
{
string fieldName = "[" + paramName + "]";
string cmdparamName = "@" + paramName;
if(paramCustType != SWParameterType.Identity)
{
sInsertPart1 = AddWithDelim(sInsertPart1, ", ", fieldName);
sInsertPart2 = AddWithDelim(sInsertPart2, ", ", cmdparamName);
}
if((paramCustType == SWParameterType.Key) || (paramCustType == SWParameterType.Identity))
{
sUpdatePart2 = AddWithDelim(sUpdatePart2, " and ", fieldName + "=" + cmdparamName);
}
if(paramCustType != SWParameterType.Identity)
{
sUpdatePart1 = AddWithDelim(sUpdatePart1, ", ", fieldName + "=" + cmdparamName);
}
}
#endregion
// set parameter name
sqlParameter.ParameterName = "@" + paramName;
// set parameter value
if(v == null)
{
v = DBNull.Value;
}
sqlParameter.Value = values[paramIndex];// this is to set a proper data type
sqlParameter.Value = v;
// add parameter to the command object
command.Parameters.Add(sqlParameter);
indexes[paramIndex] = sqlParamIndex;
sqlParamIndex++;
}
// in case of InsertUpdate command type compile new command text
#region generate InsertUpdate expresion
if(swCommandType == SWCommandType.InsertUpdate)
{
string TableName = command.CommandText;
string CommandText = "";
if(sUpdatePart2 == "")
{
throw new SqlWrapperException("No Identity or Autoincrement field is defined.");
}
sInsert = String.Format(" insert into [{0}]({1}) values({2}) ", TableName, sInsertPart1, sInsertPart2);
sUpdate = String.Format(" update [{0}] set {1} where {2} ", TableName, sUpdatePart1, sUpdatePart2);
if(sAutoincrementColumnName == "")
{
CommandText += String.Format("{0} if (@@rowcount = 0) {1}", sUpdate, sInsert);
}
else
{
CommandText += String.Format("if(@{0} is NULL) begin {1} select @{0} = SCOPE_IDENTITY() end ", sAutoincrementColumnName, sInsert);
CommandText += String.Format("else begin {0} end", sUpdate);
}
command.CommandText = CommandText;
}
#endregion
}
/// <summary>
/// Initializes a new instance of the <see cref="SWCommandAttribute"/> class with the specified
/// <see cref="CommandType"/>, <see cref="CommandText"/>
/// and <see cref="ReturnIfNull"/> values
/// </summary>
/// <param name="commandType">Is a value of <see cref="SWCommandAttribute.CommandType"/> property</param>
/// <param name="commandText">Is a value of <see cref="SWCommandAttribute.CommandText"/> property</param>
/// <param name="returnIfNull">Is a value of <see cref="SWCommandAttribute.ReturnIfNull"/> property</param>
/// <param name="missingSchemaAction">Is a value of <see cref="SWCommandAttribute.MissingSchemaAction"/> property</param>
public SWCommandAttribute(SWCommandType commandType, string commandText, object returnIfNull, MissingSchemaAction missingSchemaAction)
{
m_commandType = commandType;
m_commandText = commandText;
m_returnIfNull = returnIfNull;
if(m_returnIfNull is string)
if((string)m_returnIfNull == NullReturnValueToken)
m_returnIfNull = null;
m_missingSchemaAction = missingSchemaAction;
}
/// <summary>
/// Initializes a new instance of the <see cref="SWCommandAttribute"/> class with the specified
/// <see cref="CommandType"/> and <see cref="CommandText"/> values
/// </summary>
/// <param name="commandType"></param>
/// <param name="commandText">Is a value of <see cref="SWCommandAttribute.CommandText"/> property</param>
public SWCommandAttribute(SWCommandType commandType, string commandText)
: this(commandType, commandText, null, MissingSchemaAction.Add)
{
}
/// <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>
/// Generates command parameters.
/// For some command types the command text can be changed during parameter generating.
/// </summary>
/// <param name="command">Command object.</param>
/// <param name="method"><see cref="MethodInfo"/> type object</param>
/// <param name="values">Array of values for the command parameters.</param>
/// <param name="swCommandType"><see cref="SWCommandType"/> enumeration value</param>
/// <param name="indexes">Array of parameter indices.</param>
private static void GenerateCommandParameters(DbCommand command, MethodInfo method, object[] values, int[] indexes, SWCommandType swCommandType)
{
#region InsertUpdate parts declaration
string sUpdatePart1 = "";
string sUpdatePart2 = "";
string sUpdate = "";
string sInsertPart1 = "";
string sInsertPart2 = "";
string sInsert = "";
string sAutoincrementColumnName = "";
#endregion
//DatabaseHelper db = new DatabaseHelper();
ParameterInfo[] methodParameters = method.GetParameters();
int sqlParamIndex = 0;
for (int paramIndex = 0; paramIndex < methodParameters.Length; ++paramIndex)
{
indexes[paramIndex] = -1;
ParameterInfo paramInfo = methodParameters[paramIndex];
// create command parameter
DbParameter sqlParameter = command.CreateParameter();
// set default values
string paramName = paramInfo.Name;
SWParameterType paramCustType = SWParameterType.Default;
object v = values[paramIndex];
// get parameter attribute and set command parameter settings
SWParameterAttribute paramAttribute = (SWParameterAttribute)Attribute.GetCustomAttribute(paramInfo, typeof(SWParameterAttribute));
if (paramAttribute != null)
{
paramCustType = paramAttribute.ParameterType;
if (paramAttribute.IsNameDefined)
{
paramName = sqlParameter.ParameterName;
}
if (paramAttribute.IsTypeDefined)
{
sqlParameter.DbType = (DbType)paramAttribute.SqlDbType;
}
if (paramAttribute.IsSizeDefined)
{
sqlParameter.Size = paramAttribute.Size;
}
//if (paramAttribute.IsScaleDefined)
// sqlParameter.Scale = paramAttribute.Scale;
//if (paramAttribute.IsPrecisionDefined)
// sqlParameter..Precision = paramAttribute.Precision;
if (CompareTreatAsNullValues(paramAttribute.TreatAsNull, v, paramInfo.ParameterType))
{
v = DBNull.Value;
}
}
// parameter direction
if (paramCustType == SWParameterType.SPReturnValue)
{
sqlParameter.Direction = ParameterDirection.ReturnValue;
sqlParameter.DbType = DbType.Int32;
}
else if (paramInfo.ParameterType.IsByRef)
{
sqlParameter.Direction = paramInfo.IsOut ? ParameterDirection.Output : ParameterDirection.InputOutput;
}
else
{
sqlParameter.Direction = ParameterDirection.Input;
}
// generate parts of InsertUpdate expresion
#region generate parts of InsertUpdate expresion
if (paramCustType == SWParameterType.Identity)
{
if (sAutoincrementColumnName.Length > 0)
{
throw new SqlWrapperException("Only one identity parameter is possible");
}
sAutoincrementColumnName = paramName;
Type reftype = GetRefType(paramInfo.ParameterType);
if (reftype == null)
{
throw new SqlWrapperException("Identity parameter must be ByRef parameter");
}
// check default value
if (paramAttribute.TreatAsNull.ToString() == SWParameterAttribute.NullReturnValueToken)
{
if (Convert.ToInt64(v) <= 0)
{
v = DBNull.Value;
}
}
}
if (swCommandType == SWCommandType.InsertUpdate)
{
string fieldName = "[" + paramName + "]";
string cmdparamName = "@" + paramName;
if (paramCustType != SWParameterType.Identity)
{
sInsertPart1 = AddWithDelim(sInsertPart1, ", ", fieldName);
sInsertPart2 = AddWithDelim(sInsertPart2, ", ", cmdparamName);
}
if ((paramCustType == SWParameterType.Key) || (paramCustType == SWParameterType.Identity))
{
sUpdatePart2 = AddWithDelim(sUpdatePart2, " and ", fieldName + "=" + cmdparamName);
}
if (paramCustType != SWParameterType.Identity)
{
sUpdatePart1 = AddWithDelim(sUpdatePart1, ", ", fieldName + "=" + cmdparamName);
}
}
#endregion
// set parameter name
sqlParameter.ParameterName = "@" + paramName;
// set parameter value
if (v == null)
{
v = DBNull.Value;
}
sqlParameter.Value = values[paramIndex]; // this is to set a proper data type
sqlParameter.Value = v;
// add parameter to the command object
command.Parameters.Add(sqlParameter);
indexes[paramIndex] = sqlParamIndex;
sqlParamIndex++;
}
// in case of InsertUpdate command type compile new command text
#region generate InsertUpdate expresion
if (swCommandType == SWCommandType.InsertUpdate)
{
string TableName = command.CommandText;
string CommandText = "";
if (sUpdatePart2 == "")
{
throw new SqlWrapperException("No Identity or Autoincrement field is defined.");
}
sInsert = String.Format(" insert into [{0}]({1}) values({2}) ", TableName, sInsertPart1, sInsertPart2);
sUpdate = String.Format(" update [{0}] set {1} where {2} ", TableName, sUpdatePart1, sUpdatePart2);
if (sAutoincrementColumnName == "")
{
CommandText += String.Format("{0} if (@@rowcount = 0) {1}", sUpdate, sInsert);
}
else
{
CommandText += String.Format("if(@{0} is NULL) begin {1} select @{0} = SCOPE_IDENTITY() end ", sAutoincrementColumnName, sInsert);
CommandText += String.Format("else begin {0} end", sUpdate);
}
command.CommandText = CommandText;
}
#endregion
}
