public static TransferableDataTable ExecuteSelectCommand(SqlCommand command)
{
// The DataTable to be returned
using (DataTable table = new DataTable())
{
// Execute the command making sure the connection gets closed in the end
try
{
// Open the data connection
command.Connection.Open();
// Execute the command and save the results in a DataTable
SqlDataReader reader = command.ExecuteReader();
table.Locale = CultureInfo.CurrentCulture;
table.Load(reader);
// Close the reader
reader.Close();
}
catch (Exception ex)
{
DataErrorLogger.LogError(ex);
throw;
}
finally
{
// Close the connection
command.Connection.Close();
}
return(new TransferableDataTable(table));
}
}
public static string ExecuteScalar(SqlCommand command)
{
// The value to be returned
object result = null;
string value = string.Empty;
// Execute the command making sure the connection gets closed in the end
try
{
// Open the connection of the command
command.Connection.Open();
// Execute the command and get the number of affected rows
result = command.ExecuteScalar();
if (result != null)
{
value = result.ToString();
}
}
catch (Exception ex)
{
// Log eventual errors and rethrow them
DataErrorLogger.LogError(ex);
throw;
}
finally
{
// Close the connection
command.Connection.Close();
}
// return the result
return(value);
}
public static TransferableDataSet ExecuteMultipleSelectCommands(SqlCommand command)
{
// The DataTable to be returned
using (DataSet ds = new DataSet())
{
ds.Locale = CultureInfo.CurrentCulture;
// Execute the command making sure the connection gets closed in the end
try
{
// Open the data connection
command.Connection.Open();
using (SqlDataAdapter adpData = new SqlDataAdapter((SqlCommand)command))
{
adpData.Fill(ds);
}
}
catch (Exception ex)
{
DataErrorLogger.LogError(ex);
throw;
}
finally
{
// Close the connection
command.Connection.Close();
}
return(new TransferableDataSet(ds));
}
}
public static int ExecuteNonQuery(SqlCommand command)
{
// The number of affected rows
int affectedRows = -1;
// Execute the command making sure the connection gets closed in the end
try
{
// Open the connection of the command
command.Connection.Open();
// Execute the command and get the number of affected rows
affectedRows = command.ExecuteNonQuery();
}
catch (Exception ex)
{
// Log eventual errors and rethrow them
DataErrorLogger.LogError(ex);
throw;
}
finally
{
// Close the connection
command.Connection.Close();
}
// return the number of affected rows
return(affectedRows);
}