public async Task BatchExcuteAsync(BulkCopyModel bulkCopyModel, params SqlCommand[] commands) //IContextInfo contextInfo, { if (commands?.Length < 1) { return; } using (var connection = new sql.SqlConnection(_connectionString)) { //connection.StateChange += new System.Data.StateChangeEventHandler((sender, e) => //{ // if (e.CurrentState == System.Data.ConnectionState.Open) // setContextInfo(connection, contextInfo); //}); connection.TryOpen(); using (var tran = connection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted)) { try { foreach (var command in commands) { command.Connection = connection; command.Transaction = tran; await command.ExecuteNonQueryAsync(); } using (var bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, tran)) { bulkCopy.BatchSize = bulkCopyModel.BatchSize; // ~ 100 bulkCopy.DestinationTableName = bulkCopyModel.TableName; if (bulkCopyModel.ColumnMappings != null) { bulkCopyModel.ColumnMappings.ForEach(c => bulkCopy.ColumnMappings.Add(c)); } var dataTable = bulkCopyModel.DataTable; bulkCopy.BulkCopyTimeout = bulkCopyModel.Timeout; await bulkCopy.WriteToServerAsync(dataTable); } tran.Commit(); } catch (Exception e) { tran.Rollback(); throw e; } finally { //clearContextInfo(connection, contextInfo); connection.TryClose(); } } } }
public async Task BatchExcuteAsync(params sql.SqlCommand[] commands) //IContextInfo contextInfo, { if (commands?.Length < 1) { return; } using (var connection = new sql.SqlConnection(_connectionString)) { //connection.StateChange += new System.Data.StateChangeEventHandler((sender, e) => //{ // if (e.CurrentState == System.Data.ConnectionState.Open) // setContextInfo(connection, contextInfo); //}); await connection.TryOpenAsync(); using (var tran = connection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted)) { try { foreach (var command in commands) { command.Connection = connection; command.Transaction = tran; await command.ExecuteNonQueryAsync(); } tran.Commit(); } catch (Exception e) { tran.Rollback(); throw e; } finally { //clearContextInfo(connection, contextInfo); connection.TryClose(); } } } }
protected System.Data.DataSet Execute(sql.SqlCommand command) //, IContextInfo contextInfo { var dataSet = new System.Data.DataSet(); using (var connection = new sql.SqlConnection(_connectionString)) { //connection.StateChange += new System.Data.StateChangeEventHandler((sender, e) => //{ // if (e.CurrentState == System.Data.ConnectionState.Open) // setContextInfo(connection, contextInfo); //}); command.Connection = connection; connection.TryOpen(); using (var reader = command.ExecuteReader()) { do { if (reader.HasRows && reader.FieldCount > 0) { using (var table = dataSet.Tables.Add()) { table.Load(reader); dataSet.Tables.Add(table); } } else { reader.Close(); } }while (!reader.IsClosed); } //clearContextInfo(connection, contextInfo); connection.TryClose(); } return(dataSet); }
private DataTable loadData(string query) { var table = new DataTable(); using (var connection = new q.SqlConnection(_connectionString)) { connection.TryOpen(); using (var command = connection.CreateCommand()) { command.CommandText = query; using (var reader = command.ExecuteReader()) { table.Load(reader); reader.Close(); } } connection.TryClose(); } return(table); }