/// <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); }