public void ExecuteNonQuery(string query)
{
SqlConnection connection = null;
try
{
using (connection = new SqlConnection(GetConnectionString(ServerName, DatabaseName)))
{
using (SqlCommand command = new SqlCommand(query, connection))
{
if (Timeout != null)
{
command.CommandTimeout = (int)Timeout;
}
connection.InfoMessage += OnInfoMessageGenerated;
connection.Open();
command.StatementCompleted += OnStatementCompleted;
int rowCount = command.ExecuteNonQuery();
}
}
}
catch (SqlException se)
{
ProcessSqlError(se);
throw;
}
catch (Exception e)
{
MessageLogging.WriteLine(string.Format("Exception {0}", e.Message));
throw;
}
}
public void GetReaderValue(SqlDataReader reader)
{
if (string.IsNullOrWhiteSpace(ColumnName) && (Ordinal == null || Ordinal < 0))
{
throw new Exception("You must specify either a columnName or an ordinal number.");
}
if (!string.IsNullOrWhiteSpace(ColumnName) && Ordinal != null && Ordinal >= 0)
{
throw new Exception("Please specify either a column name or an ordinal, not both");
}
if (Ordinal == null)
{
Ordinal = reader.GetOrdinal(ColumnName);
}
else
{
ColumnName = reader.GetName((int)Ordinal);
}
if (reader.IsDBNull((int)Ordinal))
{
IsNull = true;
IntValue = -1;
StringValue = "";
DoubleValue = -1;
DateTimeValue = DateTime.Today;
return;
}
DataTypeName = reader.GetFieldType((int)Ordinal).ToString();
switch (DataTypeName)
{
case "System.Int32":
IntValue = reader.GetInt32((int)Ordinal);
break;
case "System.String":
StringValue = reader.GetString((int)Ordinal);
break;
case "System.Double":
DoubleValue = reader.GetDouble((int)Ordinal);
break;
case "System.DateTime":
DateTimeValue = reader.GetDateTime((int)Ordinal);
break;
default:
ObjectValue = reader.GetProviderSpecificValue((int)Ordinal);
MessageLogging.WriteLine(string.Format("The data type was unrecognized. The Sql datatype was {0}. The provider thinks it's {1}.",
DataTypeName, reader.GetProviderSpecificFieldType((int)Ordinal).ToString()));
break;
}
}
private void ProcessSqlError(SqlException se)
{
foreach (SqlError item in se.Errors)
{
if (item.Class != 0)
{
MessageLogging.WriteLine(string.Format("SqlError: Msg: {0}, Class: {1}, State: {2}, Line: {3}",
item.Message, item.Class, item.State, item.LineNumber));
}
else
{
MessageLogging.WriteLine(string.Format("SqlError: Msg: {0}", item.Message));
}
}
}
public void ExecuteReader(string query, CommandType commandType = CommandType.Text, List <SqlParameter> parameters = null)
{
SqlConnection connection = null;
try
{
using (connection = new SqlConnection(GetConnectionString(ServerName, DatabaseName)))
{
using (SqlCommand command = new SqlCommand(query, connection))
{
if (Timeout != null)
{
command.CommandTimeout = (int)Timeout;
}
connection.InfoMessage += OnInfoMessageGenerated;
connection.Open();
command.StatementCompleted += OnStatementCompleted;
command.CommandType = commandType;
if (parameters != null)
{
command.Parameters.AddRange(parameters.ToArray());
}
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
SqlRow row = new SqlRow();
row.GetReaderRow(reader);
Rows.Add(row);
}
}
}
}
}
catch (SqlException se)
{
ProcessSqlError(se);
throw;
}
catch (Exception e)
{
MessageLogging.WriteLine(string.Format("Exception {0}", e.Message));
}
}
public void ExecuteCatchPrint(string query)
{
SqlConnection connection = null;
try
{
using (connection = new SqlConnection(GetConnectionString(ServerName, DatabaseName)))
{
using (SqlCommand command = new SqlCommand(query, connection))
{
if (Timeout != null)
{
command.CommandTimeout = (int)Timeout;
}
connection.InfoMessage += OnInfoMessageGenerated;
connection.Open();
command.StatementCompleted += OnStatementCompleted;
catchPrint = true;
command.ExecuteNonQuery();
SqlRow row = new SqlRow();
row.GetPrintValue(caughtPrint);
Rows.Add(row);
}
}
}
catch (SqlException se)
{
ProcessSqlError(se);
throw;
}
catch (Exception e)
{
MessageLogging.WriteLine(string.Format("Exception {0}", e.Message));
throw;
}
finally
{
catchPrint = false;
caughtPrint = "";
}
}
public void GetScalarValue(object value)
{
ColumnName = "Scalar Value";
Ordinal = 0;
if (value == null)
{
IsNull = true;
DataTypeName = "Unknown";
IntValue = -1;
StringValue = "";
DoubleValue = -1;
DateTimeValue = DateTime.Today;
return;
}
DataTypeName = value.GetType().ToString();
switch (DataTypeName)
{
case "System.Int32":
IntValue = Convert.ToInt32(value);
break;
case "System.String":
StringValue = Convert.ToString(value);
break;
case "System.Double":
DoubleValue = Convert.ToDouble(value);
break;
case "System.DateTime":
DateTimeValue = Convert.ToDateTime(value);
break;
default:
ObjectValue = value;
MessageLogging.WriteLine(string.Format("The data type was unrecognized: {0}", DataTypeName));
break;
}
}
public void ExecuteScalar(string query)
{
SqlConnection connection = null;
try
{
using (connection = new SqlConnection(GetConnectionString(ServerName, DatabaseName)))
{
using (SqlCommand command = new SqlCommand(query, connection))
{
if (Timeout != null)
{
command.CommandTimeout = (int)Timeout;
}
connection.InfoMessage += OnInfoMessageGenerated;
connection.Open();
command.StatementCompleted += OnStatementCompleted;
Object value = command.ExecuteScalar();
SqlRow row = new SqlRow();
row.GetScalarValue(value);
Rows.Add(row);
MessageLogging.WriteLine(string.Format("DataTypeName: {0}, Value: {1}",
row.ColumnOrdinal[0].DataTypeName, row.ColumnOrdinal[0].ToString()));
}
}
}
catch (SqlException se)
{
ProcessSqlError(se);
throw;
}
catch (Exception e)
{
MessageLogging.WriteLine(string.Format("Exception {0}", e.Message));
throw;
}
}