private static async Task Main()
{
var commandFactory = new DbCommandFactory($"\"Npgsql-Test-{Guid.NewGuid():N}\"");
var commandExecutor = new NpgsqlCommandExecutor();
var cts = new CancellationTokenSource();
// Use the connection type that is loaded by the runtime through the typical loading algorithm
using (var connection = OpenConnection(typeof(NpgsqlConnection)))
{
await RelationalDatabaseTestHarness.RunAllAsync <NpgsqlCommand>(connection, commandFactory, commandExecutor, cts.Token);
}
// Test the result when the ADO.NET provider assembly is loaded through Assembly.LoadFile
// On .NET Core this results in a new assembly being loaded whose types are not considered the same
// as the types loaded through the default loading mechanism, potentially causing type casting issues in CallSite instrumentation
var loadFileType = AssemblyHelpers.LoadFileAndRetrieveType(typeof(NpgsqlConnection));
using (var connection = OpenConnection(loadFileType))
{
// Do not use the strongly typed SqlCommandExecutor because the type casts will fail
await RelationalDatabaseTestHarness.RunBaseClassesAsync(connection, commandFactory, cts.Token);
}
// allow time to flush
await Task.Delay(2000, cts.Token);
}
public override DbCommandWrapper InitializeCommand(Database db, DataTransferObject criteria)
{
UserIPAddress userIPAddressDto = criteria as UserIPAddress;
//Create a database command object within which T-SQL commands can
//be executed.
DbCommandWrapper cw = DbCommandFactory.GetStoredProcCommandWrapper(db, "spInsertUserIPAddress");
//Parameters are added here, update each line to reflect the parameter from
//the subscription object
cw.AddInParameter("UserId", DbType.Int32, userIPAddressDto.UserId);
cw.AddInParameter("IPOctet1Begin", DbType.Int32, userIPAddressDto.BeginAddress.Octet1);
cw.AddInParameter("IPOctet2Begin", DbType.Int32, userIPAddressDto.BeginAddress.Octet2);
cw.AddInParameter("IPOctet3Begin", DbType.Int32, userIPAddressDto.BeginAddress.Octet3);
cw.AddInParameter("IPOctet3End", DbType.Int32, userIPAddressDto.EndAddress.Octet3);
cw.AddInParameter("IPOctet4Begin", DbType.Int32, userIPAddressDto.BeginAddress.Octet4);
cw.AddInParameter("IPOctet4End", DbType.Int32, userIPAddressDto.EndAddress.Octet4);
//Return the commandwrapper object to DALCHelper where the stored proc
//will be executed
return(cw);
}
private static async Task Main()
{
var commandFactory = new DbCommandFactory($"[System-Data-SqlClient-NetFx2.0-Test-{Guid.NewGuid():N}]");
var cts = new CancellationTokenSource();
var sqlCommandExecutor = new SqlCommandExecutor20Adapter(new SqlCommandExecutor20());
var dbCommandClassExecutor = new DbCommandClassExecutor20Adapter(new DbCommandClassExecutor20());
var dbCommandInterfaceExecutor = new DbCommandInterfaceExecutor20Adapter(new DbCommandInterfaceExecutor20());
var dbCommandInterfaceGenericExecutor = new DbCommandInterfaceGenericExecutor20Adapter <SqlCommand>(new DbCommandInterfaceGenericExecutor20 <SqlCommand>());
using (var connection = OpenConnection())
{
await RelationalDatabaseTestHarness.RunAllAsync(
connection,
commandFactory,
cts.Token,
sqlCommandExecutor,
dbCommandClassExecutor,
dbCommandInterfaceExecutor,
dbCommandInterfaceGenericExecutor);
}
// allow time to flush
await Task.Delay(2000, cts.Token);
}
public override DbCommandWrapper InitializeCommand(Database db, DataTransferObject criteria)
{
//Cast the DataTransferObject to the Subscription object
Subscription subscriptionDto = criteria as Subscription;
//Create a database command object within which T-SQL commands can
//be executed.
DbCommandWrapper cw = DbCommandFactory.GetStoredProcCommandWrapper(db, "spAdminUpdateSubscription");
//Parameters are added here, update each line to reflect the parameter from
//the subscription object
cw.AddInParameter("SubscriptionId", DbType.Int32, subscriptionDto.SubscriptionId);
cw.AddInParameter("UserId", DbType.Int32, subscriptionDto.UserId);
cw.AddInParameter("VolumeIssueId", DbType.Int32, subscriptionDto.VolumeIssueId);
cw.AddInParameter("ArticleId", DbType.Int32, subscriptionDto.ArticleId);
cw.AddInParameter("EffectiveDate", DbType.DateTime, subscriptionDto.EffectiveDate);
cw.AddInParameter("ExpirationDate", DbType.DateTime, subscriptionDto.ExpirationDate);
cw.AddInParameter("Active", DbType.Boolean, subscriptionDto.IsActive);
cw.AddInParameter("UpdateUserId", DbType.Int32, subscriptionDto.UpdateUserId);
//Return the commandwrapper object to DALCHelper where the stored proc
//will be executed
return(cw);
}
public async Task <DbData> ExtractAsync(string viewName, ViewParameters parameters)
{
using (DbConnection connection = DbConnectionFactory.Create(_dbEngine, _connectionString))
{
try
{
_logger.LogDebug("Extract db data async via \"View\" started");
DbData result = null;
await connection.OpenAsync().ConfigureAwait(false);
string cmdText = SqlStatmentsGenerator.CreateSelectStatement(SqlStatmentsGenerator.SelectAllColumns, viewName, parameters);
using (IDbCommand command = DbCommandFactory.Create(_dbEngine, connection, cmdText))
{
command.CommandType = CommandType.Text;
result = await ReadDataImplAsync((DbCommand)command);
}
connection.Close();
_logger.LogDebug("Extract db data async via \"View\" completed");
return(result);
}
catch (Exception e)
{
_logger.LogError($"An error occured during async data extraction via \"View\", exception: {e}");
return(null);
}
}
}
public static string GetMaxLsn(DbConnection connection)
{
using (var cmd = DbCommandFactory.Create("SELECT CONVERT(varchar(22), sys.fn_cdc_get_max_lsn(), 1);", connection))
{
return((string)cmd.ExecuteScalar());
}
}
internal static void Commit(DbConnection con)
{
using (var command = new DbCommandFactory(con, "commit").Create())
{
command.ExecuteNonQuery();
}
}
internal static void SetAutoCommit(DbConnection con, bool autoCommit)
{
using (var command = new DbCommandFactory(con, "set autocommit=" + (autoCommit == true ? "1" : "0")).Create())
{
command.ExecuteNonQuery();
}
}
private static async Task Main()
{
var commandFactory = new DbCommandFactory($"[Microsoft-Data-SqlClient-Test-{Guid.NewGuid():N}]");
var commandExecutor = new MicrosoftSqlCommandExecutor();
var cts = new CancellationTokenSource();
using (var connection = OpenConnection(typeof(SqlConnection)))
{
await RelationalDatabaseTestHarness.RunAllAsync <SqlCommand>(connection, commandFactory, commandExecutor, cts.Token);
}
// Version 4.0.0 causes a hard crash
#if !SQLCLIENT_4
// Test the result when the ADO.NET provider assembly is loaded through Assembly.LoadFile
// On .NET Core this results in a new assembly being loaded whose types are not considered the same
// as the types loaded through the default loading mechanism, potentially causing type casting issues in CallSite instrumentation
var loadFileType = AssemblyHelpers.LoadFileAndRetrieveType(typeof(SqlConnection));
using (var connection = OpenConnection(loadFileType))
{
// Do not use the strongly typed SqlCommandExecutor because the type casts will fail
await RelationalDatabaseTestHarness.RunBaseClassesAsync(connection, commandFactory, cts.Token);
}
#endif
// allow time to flush
await Task.Delay(2000, cts.Token);
}
public Tuple <IDataReader, IDbConnection> ExecuteDbReader(string connectionString, string cmdText)
{
IDbConnection connection = DbConnectionFactory.Create(_dbEngine, connectionString);
IDbCommand command = DbCommandFactory.Create(_dbEngine, connection, cmdText);
connection.Open();
return(new Tuple <IDataReader, IDbConnection>(ExecuteDbReader(command as DbCommand), connection));
}
public static string GetLastExecutedLsn(DbConnection connection, string providerUrl)
{
using (var cmd = DbCommandFactory.Create($"SELECT TOP(1) LastExecutedLsn FROM [dbo].[CdcLastExecutedLsn] WHERE ProviderUrl = {@ProviderUrl};", connection))
{
cmd.Parameters.AddWithValue(@ProviderUrl, providerUrl);
return((string)cmd.ExecuteScalar());
}
}
public async Task <Tuple <DbDataReader, DbConnection> > ExecuteDbReaderAsync(string connectionString, string cmdText)
{
DbConnection connection = DbConnectionFactory.Create(_dbEngine, connectionString);
await connection.OpenAsync();
IDbCommand command = DbCommandFactory.Create(_dbEngine, connection, cmdText);
return(new Tuple <DbDataReader, DbConnection>(await ExecuteDbReaderAsync(command as DbCommand), connection));
}
public override DbCommandWrapper InitializeCommand(Database db, DataTransferObject criteria)
{
Issue issueDto = criteria as Issue;
DbCommandWrapper cw = DbCommandFactory.GetStoredProcCommandWrapper(db, "spGetActiveArticles");
cw.AddInParameter("VolumeIssueId", DbType.Int32, issueDto.VolumeIssueId);
return(cw);
}
private static void ExecuteIdsQuery(DbConnection connection, string query, IEnumerable <int> ids)
{
using (var cmd = DbCommandFactory.Create(query, connection))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add(SqlQuerySyntaxHelper.GetIdsDatatableParam("@ids", ids, DatabaseTypeHelper.ResolveDatabaseType(connection)));
cmd.ExecuteNonQuery();
}
}
public override DbCommandWrapper InitializeCommand(Database db, DataTransferObject criteria)
{
User userDto = criteria as User;
DbCommandWrapper cw = DbCommandFactory.GetStoredProcCommandWrapper(db, "spGetUserRolesByUserId");
cw.AddInParameter("UserId", DbType.Int32, userDto.UserId);
return(cw);
}
public override DbCommandWrapper InitializeCommand(Database db, DataTransferObject criteria)
{
Volume volDeleteDto = criteria as Volume;
DbCommandWrapper cw = DbCommandFactory.GetStoredProcCommandWrapper(db, "spDeleteVolume");
cw.AddInParameter("VolumeId", DbType.Int32, volDeleteDto.VolumeId);
return(cw);
}
public override DbCommandWrapper InitializeCommand(Database db, DataTransferObject criteria)
{
User userDto = criteria as User;
DbCommandWrapper cw = DbCommandFactory.GetStoredProcCommandWrapper(db, "spGetUserByEmail");
cw.AddInParameter("Email", DbType.String, userDto.EmailAddress);
return(cw);
}
public static void DeleteSentNotifications(DbConnection connection)
{
var dbType = DatabaseTypeHelper.ResolveDatabaseType(connection);
var query = $"DELETE FROM SYSTEM_NOTIFICATION_QUEUE WHERE SENT = {SqlQuerySyntaxHelper.ToBoolSql(dbType, true)}";
using (var cmd = DbCommandFactory.Create(query, connection))
{
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
}
}
/// <summary>
/// Full sql command execution in one call without manual connection open and close, constructing
/// proper instance of DbCommand
/// </summary>
/// <param name="connectionString"> Database connection string </param>
/// <param name="cmdText"> Script that should be executed </param>
/// <returns>
/// True if execution was successful, otherwise - false
/// </returns>
public bool ExecuteNonQuery(string connectionString, string cmdText)
{
using (DbConnection connection = DbConnectionFactory.Create(_dbEngine, connectionString))
{
IDbCommand command = DbCommandFactory.Create(_dbEngine, connection, cmdText);
connection.Open();
bool result = ExecuteNonQuery(command as DbCommand);
connection.Close();
return(result);
}
}
public static void InsertNotifications(DbConnection connection, string notificationsXml)
{
var dbType = DatabaseTypeHelper.ResolveDatabaseType(connection);
var query = dbType == DatabaseType.Postgres ? PgInsertNotificationsQuery : SqlInsertNotificationsQuery;
using (var cmd = DbCommandFactory.Create(query, connection))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add(SqlQuerySyntaxHelper.GetXmlParameter("@notifications", notificationsXml, dbType));
cmd.ExecuteNonQuery();
}
}
private static void ExecuteIdsQuery(DbConnection connection, string query, IEnumerable <int> ids, string lastExceptionMessage = null)
{
using (var cmd = DbCommandFactory.Create(query, connection))
{
var dbType = DatabaseTypeHelper.ResolveDatabaseType(connection);
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add(SqlQuerySyntaxHelper.GetIdsDatatableParam("@ids", ids, dbType));
cmd.Parameters.AddWithValue("@lastExceptionMessage", lastExceptionMessage);
cmd.ExecuteNonQuery();
}
}
public override DbCommandWrapper InitializeCommand(Microsoft.Practices.EnterpriseLibrary.Data.Database db, MN.Enterprise.Base.DataTransferObject criteria)
{
Issue issueDto = criteria as Issue;
//Create a database command object within which T-SQL commands can
//be executed.
DbCommandWrapper cw = DbCommandFactory.GetStoredProcCommandWrapper(db, "spGetVolumeAndIssueName");
cw.AddInParameter("VolumeIssueId", DbType.Int32, issueDto.VolumeIssueId);
return(cw);
}
private bool ExecuteStatement(string connectionString, string statement)
{
bool result = true;
using (IDbConnection connection = DbConnectionFactory.Create(_dbEngine, connectionString))
{
connection.Open();
IDbCommand command = DbCommandFactory.Create(_dbEngine, connection, statement);
result = ExecuteNonQuery(command);
connection.Close();
return(result);
}
}
internal static List <string> getStringData(DbConnection con, string sql)
{
var data = new List <string>();
using (var reader = new DbCommandFactory(con, sql).Create().ExecuteReader())
{
while (reader.Read())
{
data.Add(reader.GetString(0).ToUpper());
}
}
return(data);
}
public async Task <bool> ExecuteNonQueryAsync(string connectionString, string cmdText)
{
using (DbConnection connection = DbConnectionFactory.Create(_dbEngine, connectionString))
{
IDbCommand command = DbCommandFactory.Create(_dbEngine, connection, cmdText);
await connection.OpenAsync().ConfigureAwait(false);
bool result = await ExecuteNonQueryAsync(command as DbCommand);
connection.Close();
return(result);
}
}
internal static int getIntSingleResult(DbConnection con, string sql)
{
int result = 0;
using (var reader = new DbCommandFactory(con, sql).Create().ExecuteReader())
{
while (reader.Read())
{
result = reader.GetInt32(0);
}
}
return(result);
}
public override DbCommandWrapper InitializeCommand(Database db, DataTransferObject criteria)
{
UserIPAddress userIPAddressDto = criteria as UserIPAddress;
//Create a database command object within which T-SQL commands can
//be executed.
DbCommandWrapper cw = DbCommandFactory.GetStoredProcCommandWrapper(db, "spAdminGetUserIPAddresses");
//TODO: Add the appropriate parameter to the commandwrapper object here
cw.AddInParameter("UserId", DbType.Int32, userIPAddressDto.UserId);
return(cw);
}
internal static List <string> getTableFields(DbConnection con, string table)
{
var data = new List <string>();
using (var reader = new DbCommandFactory(con, new SqlBuilderFactory(con).Create(con.Database).describeTableSql(table)).Create().ExecuteReader())
{
while (reader.Read())
{
data.Add(reader.GetString(0).ToUpper() + " " + reader.GetString(1) + ", Nullable:" + reader.GetString(2));
}
}
return(data);
}
internal static List <Table> getTablesInfo(sqlservers server, DbConnection con)
{
var list = new List <Table>();
using (var r = new DbCommandFactory(con, new SqlBuilderFactory(server).Create(con.Database).getAllFieldsFromAllTablesInDb()).Create().ExecuteReader())
{
while (r.Read())
{
list.Add(new Table(r.GetString(0), r.GetString(1), r.GetString(2), r.GetString(3),
r.GetValue(4) != DBNull.Value ? r.GetInt64(4) : default(long)));
}
}
return(list);
}
public override DbCommandWrapper InitializeCommand(Database db, DataTransferObject criteria)
{
User userDto = criteria as User;
DbCommandWrapper cw = DbCommandFactory.GetStoredProcCommandWrapper(db, "spUpdateUserPasswordHash");
cw.AddInParameter("Email", DbType.String, userDto.EmailAddress);
cw.AddInParameter("PasswordHash", DbType.String, userDto.PasswordHash);
cw.AddInParameter("AccountStatus", DbType.Int32, userDto.AccountStatus);
return(cw);
}
public void Should_be_able_to_create_a_command()
{
var dataSource = DefaultDataSource();
var factory = new DbCommandFactory();
var connection = new Mock<IDbConnection>();
var query = new Mock<IQuery>();
var command = new Mock<IDbCommand>();
command.SetupSet(m=>m.CommandTimeout = 15).Verifiable("CommandTimeout not set to 15");
connection.Setup(m => m.CreateCommand()).Returns(command.Object);
query.Setup(m => m.Prepare(dataSource, command.Object));
var result = factory.CreateCommandUsing(dataSource, connection.Object, query.Object);
connection.VerifyAll();
query.VerifyAll();
Assert.AreSame(result, command.Object);
}
