public void DoWork()
{
using (SqlConnection conn = new SqlConnection(GlobalUtils.TopSecret.SqlServerCS))
using (SqlCommand command = new SqlCommand())
{
try
{
conn.Open();
conn.StatisticsEnabled = true;
command.Connection = conn;
}
catch (Exception e)
{
Console.Error.WriteLine(e.Message);
return;
}
try
{
using (SqlTransaction sqlTran = conn.BeginTransaction())
{
command.Transaction = sqlTran;
SqlDataReader reader;
List<string> commands = GetCommands(com);
foreach (string c in commands)
{
command.CommandText = c;
using (reader = command.ExecuteReader())
{
ShowResultSet(reader);
while (reader.NextResult())
ShowResultSet(reader);
}
}
var stats = conn.RetrieveStatistics();
using (TextWriter tw = new StreamWriter(path + ".stats"))
{
tw.WriteLine("Execution time: {0} sec, rows selected: {1}, rows affected: {2}",
Math.Round((double)(long)stats["ExecutionTime"] / 1000, 2),
stats["SelectRows"],
stats["IduRows"]);
}
}
}
catch (Exception e)
{
Console.Error.WriteLine(e.Message);
if (command != null)
command.Cancel();
}
}
}
public static async Task <T> QueryFirstOrDefaultAsync <T>(this SqlConnection connection, string sql, object parameters = null)
{
T record;
connection.StatisticsEnabled = true;
try
{
record = await SqlMapper.QueryFirstOrDefaultAsync <T>(connection, sql, parameters);
}
catch (Exception originalException)
{
throw AddAdditionalInfoToException(originalException, $"Error: {nameof(QueryFirstOrDefaultAsync)}: " + typeof(T).Name, sql, parameters);
}
var stats = connection.RetrieveStatistics();
LogInfo("GetRecords: " + typeof(T).Name, stats, sql, parameters);
return(record);
}
public async Task<IEnumerable<TestData>> AllAsync()
{
List<TestData> results = new List<TestData>();
using (SqlConnection connection = new SqlConnection(AppConfiguration.ConnectionString))
{
bool useStaticCommand = false;
await connection.OpenAsync();
if(_useStats) connection.StatisticsEnabled = true;
SqlCommand command;
if (useStaticCommand)
{
command = staticCommand;
command.Connection = connection;
}
else
{
command = new SqlCommand(getAllCommandText, connection);
}
using (SqlDataReader reader = await command.ExecuteReaderAsync())
{
while (reader.Read())
{
var id = reader.GetString(0) ?? "";
var content = reader.GetString(1) ?? "";
results.Add(new TestData { Id = id, Content = content });
}
}
if (_useStats)
{
var stats = connection.RetrieveStatistics();
storeStatistics(stats);
}
}
return results;
}
public static SalesOrderDetailList Search(string pcompany, out double mappingTiem, out double sqlCallTie, out Hashtable pRetrieveStatistics)
{
SqlConnection conn = new SqlConnection(GetCnnstring_App(pcompany));
conn.StatisticsEnabled = true;
SqlCommand cmd = new SqlCommand("SalesOrderDetail_s", conn);
cmd.CommandType = CommandType.StoredProcedure;
//cmd.Connection = conn;
//cmd.CommandText = "SalesOrderDetail_s";
SalesOrderDetailList list;
SalesOrderDetail sales;
Stopwatch watch = new Stopwatch();
try
{
conn.Open();
watch.Start();
IDataReader reader = cmd.ExecuteReader();
watch.Stop();
sqlCallTie = watch.Elapsed.TotalMilliseconds;
watch.Reset();
watch.Start();
#region DATA Mapping
list = new SalesOrderDetailList();
while (reader.Read())
{
sales = new SalesOrderDetail();
sales.CarrierTrackingNumber = reader["CarrierTrackingNumber"].ToString();
sales.LineTotal = Convert.ToInt32(reader["LineTotal"]);
sales.ModifiedDate = Convert.ToDateTime(reader["ModifiedDate"]);
sales.ProductID = Convert.ToInt32(reader.GetOrdinal("ProductID"));
sales.OrderQty = Convert.ToInt32(reader["OrderQty"]);
sales.rowguid = (Guid)reader["rowguid"];
sales.SalesOrderDetailID = Convert.ToInt32(reader["SalesOrderDetailID"]);
sales.SalesOrderID = Convert.ToInt32(reader["SalesOrderID"]);
sales.SpecialOfferID = Convert.ToInt32(reader["SpecialOfferID"]);
sales.UnitPrice = Convert.ToDouble(reader["UnitPrice"]);
sales.UnitPriceDiscount = Convert.ToDouble(reader["UnitPriceDiscount"]);
list.Add(sales);
}
reader.Close();
reader.Dispose();
#endregion
watch.Stop();
mappingTiem = watch.Elapsed.TotalMilliseconds;
//Recojo las estadísticas
pRetrieveStatistics = (Hashtable)conn.RetrieveStatistics();
return list;
}
catch (Exception ex)
{
throw Fwk.Exceptions.ExceptionHelper.ProcessException(ex);
}
}
public async Task<int> CallScalarStoredProc()
{
int result = 0;
using (SqlConnection connection = new SqlConnection(AppConfiguration.ConnectionString))
{
string commandText = "EXEC [dbo].[TestProcedureThatDoNothing]";
SqlCommand command = new SqlCommand(commandText, connection);
if (_useStats) connection.StatisticsEnabled = true;
await connection.OpenAsync();
var procResult = await command.ExecuteScalarAsync();
if (procResult != null) result = (int) procResult;
if (_useStats)
{
var stats = connection.RetrieveStatistics();
storeStatistics(stats);
}
return result;
}
}
public IEnumerable<TestData> All()
{
List<TestData> results = new List<TestData>();
using (SqlConnection connection = new SqlConnection(AppConfiguration.ConnectionString))
{
try
{
SqlCommand command = new SqlCommand(getAllCommandText, connection);
if (_useStats) connection.StatisticsEnabled = true;
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
var id = reader.GetString(0) ?? "";
var content = reader.GetString(1) ?? "";
results.Add(new TestData { Id = id, Content = content });
}
}
if (_useStats)
{
var stats = connection.RetrieveStatistics();
storeStatistics(stats);
}
}
catch (SqlException ex)
{
Console.WriteLine(
"Error {0}: {1}",
ex.Number, ex.Message);
}
}
return results;
}
