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