public async void Executes_Linq_Style_Statements(string connectionString, DatabaseProviderType dbType)
{
var id = Guid.NewGuid();
var request = new QueryRequest
{
Id = id,
ServerType = dbType,
ConnectionString = connectionString,
Text = @"
from c in city
where c.Name.StartsWith(""Ca"")
select c
"
};
var json = JsonConvert.SerializeObject(request);
var res = await _client
.PostAsync(EndpointAddress, new StringContent(json));
var jsonRes = await res.Content.ReadAsStringAsync();
var output = JsonConvert.DeserializeObject <QueryResponse>(jsonRes);
Assert.Equal(StatusCode.Ok, output.Code);
var msgs = GetMessages();
var rows = msgs.Where(msg => msg.Type == ItemType.ListValues).SelectMany(x => x.Values);
Assert.NotEmpty(rows);
Assert.All(rows, (val) => ((string)((JObject)val)["Name"]).StartsWith("Ca"));
}
public static void ConfigureWebHost(IWebHostBuilder builder, DatabaseProviderType providerName = DatabaseProviderType.SqlServer)
{
if (providerName == DatabaseProviderType.SQLite)
{
var dbPath = RetrievePath($"UnitTest{Path.DirectorySeparatorChar}DB{Path.DirectorySeparatorChar}UnitTest.db");
var dbFile = Path.Combine(AppContext.BaseDirectory, "UnitTest.db");
File.Copy(dbPath, dbFile, true);
builder.ConfigureAppConfiguration(app => app.AddInMemoryCollection(new KeyValuePair<string, string>[] {
new KeyValuePair<string, string>("DB:0:Enabled", "false"),
new KeyValuePair<string, string>("DB:1:Enabled", "true")
}));
}
if (providerName == DatabaseProviderType.MySql)
{
builder.ConfigureAppConfiguration(app => app.AddInMemoryCollection(new KeyValuePair<string, string>[] {
new KeyValuePair<string, string>("DB:0:Enabled", "false"),
new KeyValuePair<string, string>("DB:1:Enabled", "false"),
new KeyValuePair<string, string>("DB:2:Enabled", "true")
}));
}
if (providerName == DatabaseProviderType.Npgsql)
{
builder.ConfigureAppConfiguration(app => app.AddInMemoryCollection(new KeyValuePair<string, string>[] {
new KeyValuePair<string, string>("DB:0:Enabled", "false"),
new KeyValuePair<string, string>("DB:1:Enabled", "false"),
new KeyValuePair<string, string>("DB:2:Enabled", "false"),
new KeyValuePair<string, string>("DB:3:Enabled", "true")
}));
}
}
/// <summary>
///
/// </summary>
/// <param name="builder"></param>
/// <param name="providerName"></param>
public static void ConfigureDatabase(this IWebHostBuilder builder, DatabaseProviderType providerName = DatabaseProviderType.SQLite)
{
if (providerName == DatabaseProviderType.SqlServer)
{
builder.ConfigureAppConfiguration(app => app.AddInMemoryCollection(new KeyValuePair <string, string>[] {
new KeyValuePair <string, string>("DB:0:Enabled", "false"),
new KeyValuePair <string, string>("DB:1:Enabled", "true")
}));
}
if (providerName == DatabaseProviderType.MySql)
{
builder.ConfigureAppConfiguration(app => app.AddInMemoryCollection(new KeyValuePair <string, string>[] {
new KeyValuePair <string, string>("DB:0:Enabled", "false"),
new KeyValuePair <string, string>("DB:1:Enabled", "false"),
new KeyValuePair <string, string>("DB:2:Enabled", "true")
}));
}
if (providerName == DatabaseProviderType.Npgsql)
{
builder.ConfigureAppConfiguration(app => app.AddInMemoryCollection(new KeyValuePair <string, string>[] {
new KeyValuePair <string, string>("DB:0:Enabled", "false"),
new KeyValuePair <string, string>("DB:1:Enabled", "false"),
new KeyValuePair <string, string>("DB:2:Enabled", "false"),
new KeyValuePair <string, string>("DB:3:Enabled", "true")
}));
}
}
/// <summary>
/// Update the Database Provider to use
/// </summary>
/// <param name="databaseProviderType">Database Provider Type</param>
protected void UpdateDatabaseProvider(DatabaseProviderType databaseProviderType)
{
if (databaseProviderType == DatabaseProvider.DatabaseProviderType)
{
return;
}
switch (databaseProviderType)
{
case DatabaseProviderType.SqlServer:
DatabaseProvider = new SqlServerDatabaseProvider();
break;
case DatabaseProviderType.Postgres:
DatabaseProvider = new PostgresDatabaseProvider();
break;
case DatabaseProviderType.Firebird:
DatabaseProvider = new FirebirdDatabaseProvider();
break;
default:
throw new StatementBuilderException($"Unsupported Database Provider provided [{databaseProviderType}]");
}
DatabaseProviderChanged();
}
public DbConnection GetConnection(DatabaseProviderType providerType)
{
var conn = GetFactoryFromProvider(providerType).CreateConnection();
conn.ConnectionString = ConnectionString;
return(conn);
}
public Database(string connectionString, DatabaseProviderType providerType, string defaultAppName, ITransactionResolver transactionResolver)
{
ArgumentValidator.ThrowIfNullOrEmpty(connectionString, "connectionString");
ArgumentValidator.ThrowIfNull(transactionResolver, "transactionResolver");
if (providerType == DatabaseProviderType.None && DefaultProvider == DatabaseProviderType.None)
{
providerType = DatabaseProviderType.SqlServer;
}
if (providerType == DatabaseProviderType.None && DefaultProvider != DatabaseProviderType.None)
{
providerType = DefaultProvider;
}
ProviderType = providerType;
var connString = new DbConnectionStringBuilder {
ConnectionString = connectionString
};
_userName = GetUserIdFromConnStringObject(connString);
_serverName = GetServerFromConnStringObject(connString);
_databaseName = GetDatabaseFromConnStringObject(connString);
_connectionString = connectionString;
CommandTimeoutInSeconds = 30;
_transactionResolver = transactionResolver;
_sqlStatement = SqlStatement.CreateInstance(ProviderType);
}
public async void Handles_Multiple_Expressions(string connectionString, DatabaseProviderType dbType)
{
var id = Guid.NewGuid();
var request = new QueryRequest
{
Id = id,
ServerType = dbType,
ConnectionString = connectionString,
Text = @"
city.Take(10)
countrylanguage.Take(10)"
};
var json = JsonConvert.SerializeObject(request);
var res = await _client
.PostAsync(EndpointAddress, new StringContent(json))
;
var jsonRes = await res.Content.ReadAsStringAsync();
var output = JsonConvert.DeserializeObject <QueryResponse>(jsonRes);
var msgs = GetMessages();
Assert.Equal(StatusCode.Ok, output.Code);
Assert.Equal(2, msgs.Count(x => x.Type == ItemType.List));
Assert.Equal(20, msgs.Where(x => x.Type == ItemType.ListValues).SelectMany(x => x.Values).Count());
}
public void Build_GivenTableAndColumns_ShouldReturnExpectedStatement(DatabaseProviderType providerType, string tableName,
string[][] tableColumns, object[][] whereConditions, string expectedStatement)
{
//---------------Set up test pack-------------------
var builder = SelectStatementBuilder.Create.WithDatabaseProvider(providerType).WithTable(tableName);
foreach (var currentColumn in tableColumns)
{
if (string.IsNullOrWhiteSpace(currentColumn[0]))
{
builder.WithColumn(currentColumn[1], currentColumn[2]);
}
else
{
var selectColumn = new ColumnModel(currentColumn[0], currentColumn[1], currentColumn[2]);
builder.WithColumn(selectColumn);
}
}
foreach (var currentCondition in whereConditions)
{
builder.WithWhereCondition(currentCondition[0].ToString(), currentCondition[1].ToString(), (EqualityOperators)currentCondition[2], currentCondition[3]);
}
//---------------Assert Precondition----------------
//---------------Execute Test ----------------------
var selectStatement = builder.Build();
//---------------Test Result -----------------------
Assert.AreEqual(expectedStatement, selectStatement);
}
public static IDbExceptionHandler Create(DatabaseProviderType provider)
{
var key = "DbExceptionHandler.{0}".Fi(provider);
var assemblyInfo = ConfigurationManager.AppSettings[key];
if(string.IsNullOrWhiteSpace(assemblyInfo)) return new AnsiDbExceptionHandler();
return CreateFromAssembly(assemblyInfo);
}
static SecurityTestHelper()
{
#if DebugTest
CurrentDatabaseProviderType = DatabaseProviderType.LOCALDB_2012;
#else
CurrentDatabaseProviderType = DatabaseProviderType.IN_MEMORY;
#endif
}
public static SqlStatement CreateInstance(DatabaseProviderType providerType)
{
if (providerType == DatabaseProviderType.MySql)
{
return(new MySqlStatement());
}
return(new SqlServerStatement());
}
private DbProviderFactory GetFactory(DatabaseProviderType provider)
{
if (_providerFactory == null)
{
_providerFactory = DbProviderFactories.GetFactory(GetDatabaseProviderName(provider));
}
return(_providerFactory);
}
public IDbConnection GetConnection(DatabaseProviderType providerType)
{
var factory = GetFactoryFromProvider(providerType);
var conn = factory.CreateConnection();
if (string.IsNullOrWhiteSpace(ConnectionString))
ConnectionString = DbConfiguration.DefaultConnectionString;
conn.ConnectionString = ConnectionString;
return conn;
}
public LocalContextTransactionResolver(string connectionString, DatabaseProviderType providerType)
{
ProviderType = providerType;
DbProviderFactory factory;
factory = providerType == DatabaseProviderType.None ? Database.GetDefaultFactory() : Database.GetFactoryFromProvider(providerType);
Connection = factory.CreateConnection();
Connection.ConnectionString = connectionString;
}
public void Build_GivenTableButNoColumns_ShouldReturnExpectedStatement(DatabaseProviderType providerType, string tableName, string expectedStatement)
{
//---------------Set up test pack-------------------
//---------------Assert Precondition----------------
//---------------Execute Test ----------------------
var selectStatement = SelectStatementBuilder.Create.WithDatabaseProvider(providerType).WithTable(tableName).Build();
//---------------Test Result -----------------------
Assert.AreEqual(expectedStatement, selectStatement);
}
public static string ResolveCrossDatabaseReferences(string sqlStatement, DatabaseProviderType providerType, string databaseNamePrefix)
{
const string pattern = @"\bEWM_.*?\.";
if (providerType == DatabaseProviderType.SqlServer)
{
var regEx = new Regex(pattern, RegexOptions.IgnoreCase);
var results = regEx.Matches(sqlStatement).Cast <Match>().Where(i => i.Success).Select(i => i.Value);
sqlStatement = results.Aggregate(sqlStatement, (current, item) => current.Replace(item, "{0}.".Fi(item)));
}
return(sqlStatement);
}
public void Build_GivenDatabaseProvider_ShouldReturnExpectedStatement(DatabaseProviderType databaseProviderType, string expectedStatement)
{
//---------------Set up test pack-------------------
//---------------Assert Precondition----------------
//---------------Execute Test ----------------------
var sqlStatement = ConditionBuilder.Create
.WithDatabaseProvider(databaseProviderType)
.WithCondition("TestTable1", "TestColumn1", EqualityOperators.Equals, "TestTable2", "TestColumn2")
.Build();
//---------------Test Result -----------------------
StringAssert.AreEqualIgnoringCase(expectedStatement, sqlStatement);
}
public static ISqlDialectProvider GetProvider(DatabaseProviderType providerType)
{
switch (providerType)
{
case DatabaseProviderType.SqlServer:
return new SqlServerDialectProvider();
case DatabaseProviderType.MySql:
return new MySqlDialectProvider();
case DatabaseProviderType.Postgresql:
return new AnsiSqlDialectProvider();
default:
return new AnsiSqlDialectProvider();
}
}
public static ISqlLanguageProvider GetProvider(DatabaseProviderType providerType)
{
switch (providerType)
{
case DatabaseProviderType.None:
return new AnsiSqlLanguageProvider(new SqlExpressionProvider(),new SqlServerDialectProvider(), new FastReflectionObjectAccessor());
case DatabaseProviderType.SqlServer:
return new AnsiSqlLanguageProvider(new SqlExpressionProvider(), new SqlServerDialectProvider(), new FastReflectionObjectAccessor());
case DatabaseProviderType.MySql:
return new MySqlLanguageProvider(new SqlExpressionProvider(), new FastReflectionObjectAccessor());
default:
return new AnsiSqlLanguageProvider(new SqlExpressionProvider(), new AnsiSqlDialectProvider(), new FastReflectionObjectAccessor());
}
}
private static string GetDatabaseProviderName(DatabaseProviderType providerType)
{
var result = "System.Data.SqlClient";
switch (providerType)
{
case DatabaseProviderType.MySql:
result = "MySql.Data.MySqlClient";
break;
case DatabaseProviderType.Postgresql:
result = "Npgsql";
break;
}
return result;
}
public void WithDatabaseProvider_ShouldChangeDatabaseProvider(DatabaseProviderType defaultProviderType, DatabaseProviderType databaseProviderType)
{
//---------------Set up test pack-------------------
var builder = (FakeUpdateStatementBuilder)FakeUpdateStatementBuilder.Create;
builder.Should().NotBeNull();
builder.WithDatabaseProvider(defaultProviderType);
builder.HasDatabaseProviderChanged = false;
//---------------Assert Precondition----------------
builder.Should().NotBeNull();
//---------------Execute Test ----------------------
builder?.WithDatabaseProvider(databaseProviderType);
//---------------Test Result -----------------------
Assert.IsTrue(builder?.HasDatabaseProviderChanged);
}
/// <summary>
/// 构建方言
/// </summary>
/// <param name="providerType">数据库类型</param>
/// <returns></returns>
public static DbDialect Build(DatabaseProviderType providerType)
{
return(DbDialects.GetOrAdd(providerType, type =>
{
switch (type)
{
case DatabaseProviderType.MySql:
return new MySqlDialect();
case DatabaseProviderType.SqlServer:
return new SqlServer2008Dialect();
}
return null;
}));
}
private static string GetDatabaseProviderName(DatabaseProviderType providerType)
{
var result = "System.Data.SqlClient";
switch (providerType)
{
case DatabaseProviderType.MySql:
result = "MySql.Data.MySqlClient";
break;
case DatabaseProviderType.Postgresql:
result = "Npgsql";
break;
}
return(result);
}
public void WithDatabaseProvider_ShouldChangeDatabaseProvider(DatabaseProviderType defaultProviderType, DatabaseProviderType databaseProviderType)
{
//---------------Set up test pack-------------------
var builder = (FakeSelectStatementBuilder)FakeSelectStatementBuilder.Create;
builder.Should().NotBeNull();
builder.WithDatabaseProvider(defaultProviderType);
builder.HasDatabaseProviderChanged = false;
//---------------Assert Precondition----------------
//---------------Execute Test ----------------------
// ReSharper disable once PossibleNullReferenceException
builder.WithDatabaseProvider(databaseProviderType);
//---------------Test Result -----------------------
Assert.IsTrue(builder.HasDatabaseProviderChanged);
}
/// <inheritdoc />
public IConditionBuilder WithDatabaseProvider(DatabaseProviderType databaseProviderType)
{
switch (databaseProviderType)
{
case DatabaseProviderType.SqlServer:
_databaseProvider = new SqlServerDatabaseProvider();
break;
case DatabaseProviderType.Postgres:
_databaseProvider = new PostgresDatabaseProvider();
break;
case DatabaseProviderType.Firebird:
_databaseProvider = new FirebirdDatabaseProvider();
break;
}
return(this);
}
public async Task <SchemaResult> GetSchemaSource(string connectionString, DatabaseProviderType type, string assemblyNamespace, bool withUsings = true)
{
if (type == DatabaseProviderType.SqlServer)
{
return(await _sqlServer.GetSchemaSource(connectionString, assemblyNamespace, withUsings));
}
else if (type == DatabaseProviderType.NpgSql)
{
return(await _npgsql.GetSchemaSource(connectionString, assemblyNamespace, withUsings));
}
else if (type == DatabaseProviderType.Sqlite)
{
return(await _sqlite.GetSchemaSource(connectionString, assemblyNamespace, withUsings));
}
else
{
throw new NotImplementedException();
}
}
public static DbCommand GetCommand(DatabaseProviderType providerType)
{
switch (providerType)
{
case DatabaseProviderType.Sql:
return(new SqlCommand());
case DatabaseProviderType.OleDb:
return(new OleDbCommand());
case DatabaseProviderType.Odbc:
return(new OdbcCommand());
case DatabaseProviderType.Oracle:
return(new OracleCommand());
default:
return(null);
}
}
public static DbDataAdapter GetDataAdapter(DatabaseProviderType providerType)
{
switch (providerType)
{
case DatabaseProviderType.Sql:
return(new SqlDataAdapter());
case DatabaseProviderType.OleDb:
return(new OleDbDataAdapter());
case DatabaseProviderType.Odbc:
return(new OdbcDataAdapter());
case DatabaseProviderType.Oracle:
return(new OracleDataAdapter());
default:
return(null);
}
}
public async void Template_Contains_User_Text(string connectionString, DatabaseProviderType dbType)
{
var id = Guid.NewGuid();
var request = new QueryRequest
{
Id = id,
ServerType = dbType,
ConnectionString = connectionString,
Text = "hej mor"
};
var json = JsonConvert.SerializeObject(request);
var res = await _client.PostAsync(EndpointAddress, new StringContent(json));
var jsonRes = await res.Content.ReadAsStringAsync();
var output = JsonConvert.DeserializeObject <TemplateResponse>(jsonRes);
Assert.Contains(request.Text, output.Template);
}
public static string GetDataProvider(DatabaseProviderType provider)
{
switch (provider)
{
case DatabaseProviderType.Sql:
_objFactory = "System.Data.SqlClient";
break;
case DatabaseProviderType.OleDb:
_objFactory = "System.Data.OracleClient";
break;
case DatabaseProviderType.Oracle:
_objFactory = "System.Data.OleDBClient";
break;
case DatabaseProviderType.Odbc:
_objFactory = "System.Data.OdbcClient";
break;
}
return(_objFactory);
}
public IDbConnection GetConnection(DatabaseProviderType providerType)
{
return new DbConnection();
}
public IDbDataAdapter GetAdapter(DatabaseProviderType providerType)
{
var factory = GetFactoryFromProvider(providerType);
return factory.CreateDataAdapter();
}
private DbProviderFactory GetFactoryFromProvider(DatabaseProviderType provider)
{
return DbProviderFactories.GetFactory(GetDatabaseProviderName(provider));
}
public Database(string connectionString, DatabaseProviderType providerType) : this(connectionString, providerType, null, new NullTransactionResolver())
{
}
public IDbDataAdapter GetAdapter(DatabaseProviderType providerType)
{
throw new NotImplementedException();
}
public static DbProviderFactory GetFactoryFromProvider(DatabaseProviderType provider)
{
return(DbProviderFactories.GetFactory(GetDatabaseProviderName(provider)));
}
public static void SetDefaultDatabaseProviderType(DatabaseProviderType type)
{
_dbProvider = type;
}
public DbTransactionProvider(IDbConnectionProvider connectionProvider, DatabaseProviderType providerType)
{
_connectionProvider = connectionProvider;
_providerType = providerType;
}
/// <inheritdoc />
public new IInsertStatementBuilder WithDatabaseProvider(DatabaseProviderType databaseProvider)
{
UpdateDatabaseProvider(databaseProvider);
return(this);
}
/// <inheritdoc />
public IStatementBuilder WithDatabaseProvider(DatabaseProviderType databaseProviderType)
{
UpdateDatabaseProvider(databaseProviderType);
return(this);
}
public IDbConnection GetConnection(DatabaseProviderType providerType)
{
if (_connection != null) return _connection;
_connectionProvider.ConnectionString = ConnectionString;
return _connectionProvider.GetConnection(providerType);
}
public IDbDataAdapter GetAdapter(DatabaseProviderType providerType)
{
return _connectionProvider.GetAdapter(providerType);
}
public IDbConnection GetConnection(DatabaseProviderType providerType)
{
return _connectionProvider.GetConnection(providerType);
}
