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