/// <summary>
/// Execute by store with input and outupt params
/// </summary>
/// <param name="ExecuteStoreProcedureTransaction">Store name</param>
/// <param name="commandTimeOut">Input params</param>
/// <param name="inputParameters">Input params</param>
/// <param name="outputParameters">Output params</param>
public static void ExecuteStoreProcedureTransaction(SqlConnection connection, SqlTransaction tran, string storeProcedureName, int commandTimeOut, SqlDBParameter[] inputParameters, params SqlDBParameter[] outputParameters)
{
try
{
SqlCommand command = new SqlCommand(storeProcedureName, connection, tran);
command.CommandType = CommandType.StoredProcedure;
command.CommandTimeout = commandTimeOut;
if (inputParameters != null && inputParameters.Length > 0)
{
foreach (SqlDBParameter param in inputParameters)
{
if (param != null)
{
SqlDataParameter.AddParameter(command, param.ParameterName, param.SqlDbType, param.Value);
}
}
}
// Output Parameters
if (outputParameters != null && outputParameters.Length > 0)
{
foreach (SqlDBParameter param in outputParameters)
{
if (param != null)
{
SqlDataParameter.AddParameter(command, ParameterDirection.Output, param.ParameterName, param.SqlDbType, param.Size, param.Value);
}
}
}
if (command.Connection.State != ConnectionState.Open)
{
command.Connection.Open();
}
command.ExecuteNonQuery();
if (outputParameters != null && outputParameters.Length > 0)
{
foreach (SqlDBParameter outParam in outputParameters)
{
if (outParam != null)
{
SqlParameter param = command.Parameters[outParam.ParameterName];
outParam.Value = param.Value;
outParam.SqlDbType = param.SqlDbType;
outParam.Size = param.Size;
}
}
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Search by comment text with condition search, and return dataset
/// </summary>
/// <param name="commandText">Comand text to search</param>
/// <param name="inputParameters">Condition search</param>
/// <returns>Data set</returns>
public static DataSet SearchCommandText(string commandText, int commandTimeout, params SqlDBParameter[] inputParameters)
{
SqlCommand cmm = new SqlCommand(commandText);
cmm.Connection = new SqlConnection(connectionString);
cmm.CommandType = CommandType.Text;
cmm.CommandTimeout = commandTimeout;
SqlDataAdapter adp = new SqlDataAdapter();
adp.SelectCommand = cmm;
if (inputParameters != null && inputParameters.Length > 0)
{
foreach (SqlDBParameter param in inputParameters)
{
if (param != null)
{
SqlDataParameter.AddParameter(cmm, param.ParameterName, param.SqlDbType, param.Value);
}
}
}
DataSet result = new DataSet();
try
{
if (cmm.Connection.State != ConnectionState.Open)
{
cmm.Connection.Open();
}
adp.Fill(result);
}
catch (Exception ex)
{
result = null;
}
finally
{
if (cmm.Connection.State != ConnectionState.Closed)
{
cmm.Connection.Close();
}
cmm.Connection.Dispose();
cmm.Dispose();
}
return(result);
}
/// Executes the query, and returns the first column of the first row in the
/// result set returned by the query. Additional columns or rows are ignored.
/// </summary>
/// <param name="commantText">Command Text</param>
/// <param name="inputParameters">Input params</param>
/// <returns>object</returns>
public static object ExecuteScalarCommandText(string commantText, params SqlDBParameter[] inputParameters)
{
SqlCommand cmm = new SqlCommand(commantText);
cmm.Connection = new SqlConnection(connectionString);
cmm.CommandType = CommandType.Text;
if (inputParameters != null && inputParameters.Length > 0)
{
foreach (SqlDBParameter param in inputParameters)
{
if (param != null)
{
SqlDataParameter.AddParameter(cmm, param.ParameterName, param.SqlDbType, param.Value);
}
}
}
object result = null;
try
{
if (cmm.Connection.State != ConnectionState.Open)
{
cmm.Connection.Open();
}
result = cmm.ExecuteScalar();
}
catch (Exception ex)
{
result = null;
}
finally
{
if (cmm.Connection.State != ConnectionState.Closed)
{
cmm.Connection.Close();
}
cmm.Connection.Dispose();
cmm.Dispose();
}
return(result);
}
public static bool UpdateDataTable(DataRow[] rows, string commandText, params SqlDBParameter[] parameters)
{
SqlCommand command = new SqlCommand();
command.Connection = new SqlConnection(connectionString);
command.CommandType = CommandType.Text;
command.CommandText = commandText;
if (parameters != null && parameters.Length > 0)
{
foreach (SqlDBParameter param in parameters)
{
if (param != null)
{
SqlDataParameter.AddParameter(command, param.ParameterName, param.SqlDbType, param.Value);
}
}
}
SqlDataAdapter adapter = new SqlDataAdapter(command);
try
{
if (command.Connection.State != ConnectionState.Open)
{
command.Connection.Open();
}
SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
adapter.Update(rows);
return(true);
}
catch (Exception ex)
{
return(false);
}
finally
{
if (command.Connection.State != ConnectionState.Closed)
{
command.Connection.Close();
}
command.Connection.Dispose();
command.Dispose();
}
}
/// <summary>
/// Execute comand text with input params, and return bool
/// </summary>
/// <param name="commantText">Comand text</param>
/// <param name="inputParameters">Inpurt param</param>
/// <returns>True if success, False if error</returns>
public static bool ExecuteCommandText(string commantText, params SqlDBParameter[] inputParameters)
{
SqlCommand cmm = new SqlCommand(commantText);
cmm.Connection = new SqlConnection(connectionString);
cmm.CommandType = CommandType.Text;
if (inputParameters != null && inputParameters.Length > 0)
{
foreach (SqlDBParameter param in inputParameters)
{
if (param != null)
{
SqlDataParameter.AddParameter(cmm, param.ParameterName, param.SqlDbType, param.Value);
}
}
}
bool result = false;
try
{
if (cmm.Connection.State != ConnectionState.Open)
{
cmm.Connection.Open();
}
cmm.ExecuteNonQuery();
result = true;
}
finally
{
if (cmm.Connection.State != ConnectionState.Closed)
{
cmm.Connection.Close();
}
cmm.Connection.Dispose();
cmm.Dispose();
}
return(result);
}
/// <summary>
/// Execute by store with input and outupt params
/// </summary>
/// <param name="storeProcedureName">Store name</param>
/// <param name="inputParameters">Input params</param>
/// <param name="outputParameters">Output params</param>
public static void ExecuteStoreProcedure(string storeProcedureName, int commandTimeOut, SqlDBParameter[] inputParameters, params SqlDBParameter[] outputParameters)
{
SqlCommand cmm = new SqlCommand(storeProcedureName);
cmm.Connection = new SqlConnection(connectionString);
cmm.CommandType = CommandType.StoredProcedure;
cmm.CommandTimeout = commandTimeOut;
if (inputParameters != null && inputParameters.Length > 0)
{
foreach (SqlDBParameter param in inputParameters)
{
if (param != null)
{
SqlDataParameter.AddParameter(cmm, param.ParameterName, param.SqlDbType, param.Value);
}
}
}
// Output Parameters
if (outputParameters != null && outputParameters.Length > 0)
{
foreach (SqlDBParameter param in outputParameters)
{
if (param != null)
{
SqlDataParameter.AddParameter(cmm, ParameterDirection.Output, param.ParameterName, param.SqlDbType, param.Size, param.Value);
}
}
}
try
{
if (cmm.Connection.State != ConnectionState.Open)
{
cmm.Connection.Open();
}
cmm.ExecuteNonQuery();
if (outputParameters != null && outputParameters.Length > 0)
{
foreach (SqlDBParameter outParam in outputParameters)
{
if (outParam != null)
{
SqlParameter param = cmm.Parameters[outParam.ParameterName];
outParam.Value = param.Value;
outParam.SqlDbType = param.SqlDbType;
outParam.Size = param.Size;
}
}
}
}
catch (Exception ex)
{
string a = ex.Message;
}
finally
{
if (cmm.Connection.State != ConnectionState.Closed)
{
cmm.Connection.Close();
}
cmm.Connection.Dispose();
cmm.Dispose();
}
}
/// <summary>
/// Search by command text with condiction search and return List data base on type of T
/// </summary>
/// <typeparam name="T">The type of element in the list</typeparam>
/// <param name="commandText">Comand text to search</param>
/// <param name="inputParameters">Condition search</param>
/// <returns>List data with type is T</returns>
public static List <T> SearchStoreProcedure <T>(string storeProcedureName, params SqlDBParameter[] inputParameters)
{
List <T> result = new List <T>();
Type myType = typeof(T);
if (myType != null)
{
SqlCommand cmm = new SqlCommand(storeProcedureName);
cmm.Connection = new SqlConnection(connectionString);
cmm.CommandType = CommandType.StoredProcedure;
SqlDataAdapter adp = new SqlDataAdapter();
adp.SelectCommand = cmm;
if (inputParameters != null && inputParameters.Length > 0)
{
foreach (SqlDBParameter param in inputParameters)
{
if (param != null)
{
SqlDataParameter.AddParameter(cmm, param.ParameterName, param.SqlDbType, param.Value);
}
}
}
try
{
if (cmm.Connection.State != ConnectionState.Open)
{
cmm.Connection.Open();
}
IDataReader dre = cmm.ExecuteReader();
FieldInfo[] myFieldInfo;
// Get the type and fields of FieldInfoClass.
myFieldInfo = myType.GetFields(BindingFlags.NonPublic | BindingFlags.Instance | BindingFlags.Public);
if (myFieldInfo.Length <= dre.FieldCount)
{
while (dre.Read())
{
object objNew = System.Activator.CreateInstance(typeof(T), true);
SetValueForObject(dre, myFieldInfo, objNew);
result.Add((T)objNew);
}
}
else
{
while (dre.Read())
{
object objNew = System.Activator.CreateInstance(typeof(T), true);
SetValueForObject(dre, myType, objNew);
result.Add((T)objNew);
}
}
}
catch (Exception ex)
{
result = null;
}
finally
{
if (cmm.Connection.State != ConnectionState.Closed)
{
cmm.Connection.Close();
}
cmm.Connection.Dispose();
cmm.Dispose();
}
}
return(result);
}
/// <summary>
/// Châu thêm vào để lấy schema của du liệu tra ra
/// </summary>
/// <param name="SchemaTable"></param>
/// <param name="storeProcedureName"></param>
/// <param name="inputParameters"></param>
/// <returns></returns>
public static DataTable SearchStoreProcedureDataTable(DataTable SchemaTable, string storeProcedureName, params SqlDBParameter[] inputParameters)
{
//DataSet ds = SearchStoreProcedure(storeProcedureName, ConstantClass.timeout, inputParameters);
//if (ds != null && ds.Tables.Count > 0)
//{
// return ds.Tables[0];
//}
//return null;
SqlCommand cmm = new SqlCommand(storeProcedureName);
cmm.Connection = new SqlConnection(connectionString);
cmm.CommandType = CommandType.StoredProcedure;
cmm.CommandTimeout = ConstantClass.timeout;
SqlDataAdapter adp = new SqlDataAdapter();
adp.SelectCommand = cmm;
if (inputParameters != null && inputParameters.Length > 0)
{
foreach (SqlDBParameter param in inputParameters)
{
if (param != null)
{
SqlDataParameter.AddParameter(cmm, param.ParameterName, param.SqlDbType, param.Value);
}
}
}
DataSet dataS = new DataSet();
DataTable Result = new DataTable();
System.Data.Common.DataTableMapping mapping = new System.Data.Common.DataTableMapping();
try
{
if (cmm.Connection.State != ConnectionState.Open)
{
cmm.Connection.Open();
}
adp.Fill(dataS);
//adp.Fill(dataS);
Result = dataS.Tables[0];
adp.FillSchema(dataS, SchemaType.Mapped, "#HRDMaster");
//mapping = adp.TableMappings.Add("Table", "table0");
//foreach (DataColumn col in Result.Columns)
//{
// mapping.ColumnMappings.Add(col.ColumnName.ToString(), col.ColumnName.ToString());
//}
//adp.FillSchema(dataS, SchemaType.Mapped);
SchemaTable = dataS.Tables[1];
}
catch (Exception ex)
{
string str = ex.Message.ToString();
//if (Result==)
//Result = null;
}
finally
{
if (cmm.Connection.State != ConnectionState.Closed)
{
cmm.Connection.Close();
}
cmm.Connection.Dispose();
cmm.Dispose();
}
return(Result);
}
/// <summary>
/// Search by store with condition search, and return dataset
/// </summary>
/// <param name="storeProcedureName">Store name</param>
/// <param name="inputParameters">Condition search</param>
/// <param name="outputParameters">List output paramaters</param>
/// <returns>Data set</returns>
public static DataSet SearchStoreProcedure(string storeProcedureName, int commandTimeout, SqlDBParameter[] inputParameters, params SqlDBParameter[] outputParameters)
{
SqlCommand cmm = new SqlCommand(storeProcedureName);
cmm.Connection = new SqlConnection(connectionString);
cmm.CommandType = CommandType.StoredProcedure;
cmm.CommandTimeout = commandTimeout;
SqlDataAdapter adp = new SqlDataAdapter();
adp.SelectCommand = cmm;
// Input Parameters
if (inputParameters != null && inputParameters.Length > 0)
{
foreach (SqlDBParameter param in inputParameters)
{
if (param != null)
{
SqlDataParameter.AddParameter(cmm, param.ParameterName, param.SqlDbType, param.Value);
}
}
}
// Output Parameters
if (outputParameters != null && outputParameters.Length > 0)
{
foreach (SqlDBParameter param in outputParameters)
{
if (param != null)
{
SqlDataParameter.AddParameter(cmm, ParameterDirection.Output, param.ParameterName, param.SqlDbType, param.Size, param.Value);
}
}
}
DataSet result = new DataSet();
try
{
if (cmm.Connection.State != ConnectionState.Open)
{
cmm.Connection.Open();
}
adp.Fill(result);
// return out parameters.
if (outputParameters != null)
{
foreach (SqlDBParameter outParam in outputParameters)
{
SqlParameter param = cmm.Parameters[outParam.ParameterName];
outParam.Value = param.Value;
outParam.SqlDbType = param.SqlDbType;
outParam.Size = param.Size;
}
}
}
catch (Exception ex)
{
result = null;
}
finally
{
if (cmm.Connection.State != ConnectionState.Closed)
{
cmm.Connection.Close();
}
cmm.Connection.Dispose();
cmm.Dispose();
}
return(result);
}
