public static IDictionary <string, long> CountRecords(this IDAC dac, IEnumerable <string> tableNames = null)
{
var result = new Dictionary <string, long>();
if (tableNames == null)
{
tableNames = dac.GetSchemaCached().Tables.Select(table => table.Name);
}
if (!tableNames.Any())
{
return(result);
}
var sqlBuilder = dac.CreateSQLBuilder();
foreach (var table in tableNames.WithDescriptions())
{
if (table.Index > 0)
{
sqlBuilder.NewLine().Emit("UNION ALL").NewLine();
}
sqlBuilder.Emit("SELECT '{0}', COUNT(1) FROM {1}", table.Item, SQLBuilderCommand.TableName(table.Item));
}
dac
.ExecuteQuery(((Object)sqlBuilder).ToString())
.Rows
.Cast <DataRow>()
.ForEach(row => result.Add(row.Get <string>(0), row.Get <long>(1)));
return(result);
}
public static void CreateTable(this IDAC dac, TableSpecification tableSpecification)
{
var builder = dac.CreateSQLBuilder();
builder.CreateTable(tableSpecification);
dac.ExecuteBatch(builder);
}
public static DataTable ExecuteQuery(this IDAC dac, string query, params object[] args)
{
var builder = dac.CreateSQLBuilder();
builder.Emit(query, args);
return(ExecuteQuery(dac, builder.ToString()));
}
protected virtual void AssertSameTableRowCount(IDAC source, IDAC dest, string tableName)
{
var sourceCount = source.Count(tableName);
var destCount = dest.Count(tableName);
Assert.AreEqual(sourceCount, destCount);
}
public static DACScope BeginDirtyReadScope(this IDAC dac, bool openConnection = true)
{
var scope = new DACScope(dac, ScopeContextPolicy.None, openConnection, autoAction: DACScope.Auto.Rollback);
scope.BeginTransaction(IsolationLevel.ReadUncommitted);
return(scope);
}
public static async Task BulkInsertAsync(this IDAC dac, DataTable table, BulkInsertOptions bulkInsertOptions, TimeSpan timeout, CancellationToken cancellationToken)
{
// Begin a scope to make sure it's disposed if cancellationToken is turned on
using (var scope = dac.BeginScope()) {
await Task.Run(() => dac.BulkInsert(table, bulkInsertOptions, timeout), cancellationToken);
}
}
public static DataTable Select(this IDAC dac, DataRow row)
{
return(dac.Select(
row.Table.TableName,
columns: (row.Table.Columns.Cast <DataColumn>().Select(c => c.ColumnName)).ToArray(),
columnMatches: (from key in row.Table.PrimaryKey select new ColumnValue(key.ColumnName, row[key.ColumnName])).ToArray()
));
}
public AutoIdentityDAC(
IDAC decoratedDAC,
AutoIdentityPolicy autoIdentityPolicy = AutoIdentityPolicy.UseDBMSAutoIncrement,
PrimaryKeyConvention primaryKeyConvention = PrimaryKeyConvention.Default)
: base(decoratedDAC)
{
DefaultAutoIdentityPolicy = autoIdentityPolicy;
DefaultPrimaryKeyConvention = primaryKeyConvention;
}
protected virtual void PrintTableData(IDAC dac, string header, string tableName)
{
Console.WriteLine(header);
foreach (var row in dac.Select(tableName).Rows.Cast <DataRow>())
{
Console.WriteLine("\t{0}".FormatWith(row.ItemArray.ToDelimittedString(",\t", "NULL")));
}
Console.WriteLine();
}
public static long GetMaxID(this IDAC dac, string tableName, string idColName)
{
return(dac.ExecuteScalar <long>(
dac.QuickString(
"SELECT MAX(T.ID) FROM (SELECT {0} ID FROM {1} WHERE ID IS NOT NULL UNION SELECT 0 ID ) T",
SQLBuilderCommand.ColumnName(idColName),
SQLBuilderCommand.TableName(tableName)
)
));
}
public static void InvalidateCachedSchema(this IDAC dac)
{
using (_schemaCache.EnterWriteScope()) {
var connectionString = dac.ConnectionString;
if (_schemaCache.ContainsKey(connectionString))
{
_schemaCache.Remove(connectionString);
}
}
}
public ExecuteScriptForm(IDAC dac, ISQLBuilder script)
{
InitializeComponent();
_scriptTextBox.Text = script.ToString();
_scriptTextBox.FocusAtEnd();
_databaseConnectionStringLabel.Text = dac.ConnectionString;
DAC = dac;
Script = script;
DialogResult = DialogResult.Cancel;
}
public static IDbConnection CreateOpenConnection(this IDAC dac)
{
var connection = dac.CreateConnection();
if (connection.State.IsIn(ConnectionState.Closed, ConnectionState.Broken))
{
connection.Open();
}
return(connection);
}
// Improve this
public static DataTable Select(this IDAC dac, string tableName, IEnumerable <string> columns = null, bool distinct = false, int?limit = null, int?offset = null, IEnumerable <ColumnValue> columnMatches = null, string whereClause = null, string orderByClause = null)
{
var sqlBuilder = dac.CreateSQLBuilder();
sqlBuilder.Select(tableName, columns: columns != null ? columns.Cast <object>() : null, distinct: distinct, limit: limit, offset: offset, columnMatches: columnMatches, whereClause: whereClause, orderByClause: orderByClause, endStatement: true);
var table = dac.ExecuteQuery(sqlBuilder.ToString());
table.TableName = tableName;
return(table);
}
public static long Count(this IDAC dac, string tableName, IEnumerable <ColumnValue> columnMatches = null, string whereClause = null)
{
var table = dac.Select(tableName, columns: new[] { "COUNT(1)" }, columnMatches: columnMatches, whereClause: whereClause);
if (table.Rows.Count == 0)
{
return(0);
}
return(table.Rows[0].Get <long>(0));
}
public static DBSchema GetSchemaCached(this IDAC dac)
{
using (_schemaCache.EnterWriteScope()) {
var connectionString = dac.ConnectionString;
if (!_schemaCache.ContainsKey(connectionString))
{
_schemaCache[connectionString] = dac.GetSchema();
}
return(_schemaCache[connectionString]);
}
}
private void AssertRowsInternal(IDAC dac, string tableName, IEnumerable <IEnumerable <object> > expectedRows, bool anyOrder)
{
var tableRows = dac.Select(tableName).Rows.Cast <DataRow>().Select(r => r.ItemArray as IEnumerable <object>);
if (anyOrder)
{
expectedRows = expectedRows.OrderByAll();
tableRows = tableRows.OrderByAll();
}
NUnitTool.AssertSame2DArrays(expectedRows, tableRows, tableName);
}
public static DACScope BeginScope(this IDAC dac, bool openConnection = true, ScopeContextPolicy policy = ScopeContextPolicy.None)
{
if (dac.UseScopeOsmosis)
{
return(new DACScope(dac, policy, openConnection));
}
if (policy == ScopeContextPolicy.MustBeNested)
{
throw new ArgumentException("Policy cannot be MustBeNested for DAC that uses direct connections (i.e. UseScopeOsmosis == false).", "policy");
}
return(new DACScope(dac, ScopeContextPolicy.None, openConnection, string.Format("{0}:", dac.InstanceID.ToStrictAlphaString())));
}
public static long Insert(this IDAC dac, DataRow row, bool ommitAutoIncrementPK = true)
{
var cols = row.Table.Columns.Cast <DataColumn>();
var insertValues = cols.Select(c => new ColumnValue(c.ColumnName, row[c.ColumnName]));
if (ommitAutoIncrementPK && row.Table.PrimaryKey.Length == 1 && row.Table.PrimaryKey[0].AutoIncrement)
{
insertValues = insertValues.Except(new ColumnValue(row.Table.PrimaryKey[0].ColumnName, row[row.Table.PrimaryKey[0].ColumnName]));
}
return(dac.Insert(
row.Table.TableName,
insertValues
));
}
internal DACScope(IDAC dac, ScopeContextPolicy policy, bool openConnection, string contextPrefix = DefaultContextPrefix, Auto?autoAction = null)
: base(string.Format(ContextNameTemplate, contextPrefix, dac.ConnectionString), policy)
{
if (dac == null)
{
throw new ArgumentNullException("dac");
}
DAC = dac;
if (IsRootScope)
{
_connection = new RestrictedConnection(DAC.CreateConnection());
if (openConnection)
{
_connection.Open();
}
_scopeOwnsConnection = true;
_transaction = null;
_scopeOwnsTransaction = false;
_transactionOwner = null;
}
else
{
if (RootScope._connection == null)
{
throw new SoftwareException("Internal Error: RootScope DAC had null connection");
}
_connection = RootScope._connection;
_transaction = RootScope._transaction;
_transactionOwner = RootScope._transactionOwner;
_scopeOwnsTransaction = false;
_scopeOwnsConnection = false;
if (openConnection && _connection.State.IsIn(ConnectionState.Closed, ConnectionState.Broken))
{
_connection.Open();
}
}
_withinSystemTransactionScope = System.Transactions.Transaction.Current != null;
if (_scopeOwnsConnection && _withinSystemTransactionScope)
{
DAC.EnlistInSystemTransaction(_connection.DangerousInternalConnection, System.Transactions.Transaction.Current);
}
_voteRollback = false;
_scopeHasOpenTransaction = false;
_autoAction = autoAction;
}
protected virtual void DropDatabase(IDAC dac)
{
switch (dac.DBMSType)
{
case DBMSType.Sqlite:
Tools.Sqlite.Drop(Tools.Sqlite.GetFilePathFromConnectionString(dac.ConnectionString));
break;
#if !__MOBILE__
case DBMSType.SQLServer:
MSSQL.DropDatabase(MSSQLServer, MSSQL.GetDatabaseNameFromConnectionString(dac.ConnectionString), MSSQLUser, MSSQLPassword);
break;
#endif
default:
throw new NotImplementedException("DBMSTypes {0}".FormatWith(dac.DBMSType));
}
}
protected virtual IDAC DuplicateDAC(IDAC sourceDAC)
{
switch (sourceDAC.DBMSType)
{
case DBMSType.Sqlite:
return(new SqliteDAC(sourceDAC.ConnectionString, sourceDAC.Log));
break;
case DBMSType.SQLServer:
return(new MSSQLDAC(sourceDAC.ConnectionString, sourceDAC.Log));
break;
default:
throw new NotSupportedException(sourceDAC.DBMSType.ToString());
}
}
public static long Update(this IDAC dac, DataRow row)
{
var allColumns =
row
.Table
.Columns
.Cast <DataColumn>()
.Select(c => new ColumnValue(c.ColumnName, row[c.ColumnName]));
var pkCols =
row
.Table
.PrimaryKey
.Select(c => new ColumnValue(c.ColumnName, row[c.ColumnName]));
return(dac.Update(
row.Table.TableName,
allColumns.Except(pkCols),
pkCols
));
}
private async Task AssertConnectionPropagationAsync(IDbConnection expectedConnectionObj, IDAC dac2)
{
using (var scope2 = dac2.BeginScope(true)) {
Assert.AreSame(expectedConnectionObj, scope2.Connection);
}
}
public UnitTestDAC(Action endAction, IDAC innerDAC) : base(innerDAC)
{
EndAction = endAction;
}
public AmbientDAC(IDAC decoratedDAC, Func <IDbConnection> getConnectionFunc, Func <IsolationLevel, IDbTransaction> getTransactionFunc)
: base(decoratedDAC)
{
_getConnectionFunc = getConnectionFunc;
_getTransactionFunc = getTransactionFunc;
}
public static string QuickString(this IDAC dac, string sql, params object[] args)
{
var newBuilder = dac.CreateSQLBuilder();
return(((Object)newBuilder.Emit(sql, args)).ToString());
}
public static DataTable ExecuteQuery(this IDAC dac, string query)
{
using (var scope = dac.BeginScope())
using (var reader = dac.ExecuteReader(query))
return(reader.ToDataTable());
}
public static T ExecuteScalar <T>(this IDAC dac, string query)
{
return(Tools.Object.ChangeType <T>(dac.ExecuteScalar(query)));
}
public static long Save(this IDAC dac, DataRow dataRow, bool ommitAutoIncrementPK = true)
{
using (var scope = dac.BeginScope(openConnection: true)) {
return(dataRow.RowState == DataRowState.Added ? dac.Insert(dataRow, ommitAutoIncrementPK) : dac.Update(dataRow));
}
}
