public static int GetScalarValue(string queryString, CommandType commandType, IDataParameter[] dataParameter)
{
using (SqlConnection sqlConnection = new SqlConnection(GlobalMsADO.ConnectionString()))
{
SqlCommand sqlCommand = new SqlCommand(queryString, sqlConnection);
sqlCommand.CommandType = commandType;
if (dataParameter != null)
{
sqlCommand.Parameters.AddRange(dataParameter);
}
sqlConnection.Open();
int returnValue;
object returnObject = sqlCommand.ExecuteScalar();
if (returnObject != DBNull.Value && returnObject != null && int.TryParse(returnObject.ToString(), out returnValue))
{
return((int)returnValue);
}
else
{
return(-1);
}
}
}
public static void UpdateNullableColumns(int dataMessageID, int splitDataMessageID)
{
using (SqlConnection sqlConnection = new SqlConnection(GlobalMsADO.ConnectionString()))
{
try
{
sqlConnection.Open(); //According to SONG THAN USER: This following code is used to update these NULLABLE column(s) of table DataMessageMaster (These NULLABLE column(s) are created by SONG THAN USER)
DataSet dataSetDataMessageMaster = new DataSet();
SqlDataAdapter sqlDataAdapterDataMessage;
SqlDataAdapter sqlDataAdapterSplitDataMessage;
SqlCommandBuilder sqlCommandBuilder;
sqlDataAdapterDataMessage = new SqlDataAdapter("SELECT * FROM DataMessageMaster WHERE DataMessageID = " + dataMessageID, sqlConnection);
sqlDataAdapterDataMessage.Fill(dataSetDataMessageMaster, "DataMessageMaster"); //Fill the base datatable
//Initialize the SqlDataAdapter object by specifying a Select command that retrieves data from the DataMessageMaster table.
sqlDataAdapterSplitDataMessage = new SqlDataAdapter("SELECT * FROM DataMessageMaster WHERE DataMessageID = " + splitDataMessageID, sqlConnection);
//Initialize the SqlCommandBuilder object to automatically generate and initialize the UpdateCommand, InsertCommand, and DeleteCommand properties of the SqlDataAdapter.
sqlCommandBuilder = new SqlCommandBuilder(sqlDataAdapterSplitDataMessage);
//Populate the DataSet by running the Fill method of the SqlDataAdapter.
sqlDataAdapterSplitDataMessage.Fill(dataSetDataMessageMaster, "SplitDataMessageMaster");
bool hasUpdateNullableColumn = false;
foreach (DataColumn dataColumn in dataSetDataMessageMaster.Tables["SplitDataMessageMaster"].Columns)
{
if (dataSetDataMessageMaster.Tables["SplitDataMessageMaster"].Rows[0].IsNull(dataColumn.ColumnName))
{
dataSetDataMessageMaster.Tables["SplitDataMessageMaster"].Rows[0][dataColumn.ColumnName] = dataSetDataMessageMaster.Tables["DataMessageMaster"].Rows[0][dataColumn.ColumnName];
hasUpdateNullableColumn = true;
}
}
if (hasUpdateNullableColumn)
{
sqlDataAdapterSplitDataMessage.Update(dataSetDataMessageMaster, "SplitDataMessageMaster"); //Post the data modification to the database.
}
}
catch (Exception exception)
{
throw exception;
}
}
}
public static int ExecuteNonQuery(string commandText, CommandType commandType)
{
Console.WriteLine(commandText);
using (SqlConnection sqlConnection = new SqlConnection(GlobalMsADO.ConnectionString()))
{
try
{
sqlConnection.Open();
SqlCommand sqlCommand = new SqlCommand(commandText, sqlConnection);
sqlCommand.CommandType = commandType;
return(sqlCommand.ExecuteNonQuery());
}
catch (Exception exception)
{
throw exception;
}
}
}
/// <summary>
/// GetDataTable
/// </summary>
/// <param name="queryString"></param>
/// <returns></returns>
public static DataTable GetDataTable(string queryString, CommandType commandType, IDataParameter[] dataParameter)
{
using (SqlConnection sqlConnection = new SqlConnection(GlobalMsADO.ConnectionString()))
{
SqlCommand sqlCommand = new SqlCommand(queryString, sqlConnection);
sqlCommand.CommandType = commandType;
if (dataParameter != null)
{
sqlCommand.Parameters.AddRange(dataParameter);
}
using (SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand))
{
DataTable dataTable = new DataTable();
sqlDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
sqlDataAdapter.Fill(dataTable);
return(dataTable);
}
}
}
public static int ExecuteTransaction(string commandText, CommandType commandType)
{
using (SqlConnection sqlConnection = new SqlConnection(GlobalMsADO.ConnectionString()))
{
int rowsAffected = -1;
SqlTransaction sqlTransaction = null;
try
{
sqlConnection.Open();
sqlTransaction = sqlConnection.BeginTransaction("MyTransaction");
//Begin add command here
using (SqlCommand sqlCommand = new SqlCommand(commandText, sqlConnection, sqlTransaction))
{
sqlCommand.CommandType = commandType;
rowsAffected = sqlCommand.ExecuteNonQuery();
}
//End add command here
sqlTransaction.Commit();
return(rowsAffected);
}
catch (Exception exception)
{
try
{
sqlTransaction.Rollback();
throw exception;
}
catch (Exception rollbackException)
{
throw rollbackException;
}
}
}
}
public static int GetReturnValue(string queryString, CommandType commandType, IDataParameter[] dataParameter)
{
using (SqlConnection sqlConnection = new SqlConnection(GlobalMsADO.ConnectionString()))
{
SqlCommand sqlCommand = new SqlCommand(queryString, sqlConnection);
sqlCommand.CommandType = commandType;
if (dataParameter != null)
{
sqlCommand.Parameters.AddRange(dataParameter);
}
sqlConnection.Open();
SqlParameter returnParameter = sqlCommand.Parameters.Add("@ReturnValue", SqlDbType.Int);
returnParameter.Direction = ParameterDirection.ReturnValue;
sqlCommand.ExecuteNonQuery();
return((int)returnParameter.Value);
}
}
public static int ExecuteNonQuery(string commandText, CommandType commandType)
{
return(ADODatabase.ExecuteNonQuery(commandText, commandType, GlobalMsADO.ConnectionString()));
}
