public static RuntimeBuilder AddSqlServerRuntime(this RuntimeBuilder builder,
string connectionString,
ConnectionScope scope = ConnectionScope.ByRequest,
Action <SqlServerOptions> configureDatabase = null)
{
void ConfigureDatabase(SqlServerOptions o)
{
configureDatabase?.Invoke(o);
}
builder.Services.Configure <SqlServerOptions>(ConfigureDatabase);
var serviceProvider = builder.Services.BuildServiceProvider();
var options = serviceProvider.GetService <IOptions <SqlServerOptions> >()?.Value ?? new SqlServerOptions();
configureDatabase?.Invoke(options);
var dialect = new SqlServerDialect();
SqlBuilder.Dialect = dialect;
builder.AddSqlRuntimeStores <SqlServerConnectionFactory>(connectionString, scope, OnCommand(), OnConnection);
builder.Services.AddMetrics();
builder.Services.TryAddSingleton <ISqlDialect>(dialect);
builder.Services.TryAddSingleton(dialect);
builder.Services.TryAddSingleton <IDataBatchOperation <SqlServerPreBatchStatus>, SqlServerBatchDataOperation>();
var runtimeOptions = serviceProvider.GetRequiredService <IOptions <RuntimeOptions> >().Value;
MigrateToLatest(connectionString, runtimeOptions);
return(builder);
}
/// <summary>
/// Gets the Dapper Extensions Implementation
/// </summary>
public static IDapperImplementor Instance(DataBaseType dbType = DefaultDBType)
{
IDapperImplementor instance;
if (!_instanceList.TryGetValue(dbType, out instance))
{
ISqlDialect sqlDialect;
switch (dbType)
{
case DataBaseType.SqlServer: sqlDialect = new SqlServerDialect(); break;
case DataBaseType.Oracle: sqlDialect = new OracleDialect(); break;
case DataBaseType.MySql: sqlDialect = new MySqlDialect(); break;
default: sqlDialect = new SqlServerDialect(); break;
}
IDapperExtensionConfiguration iDapperExtensionsConfiguration = new DapperExtensionConfiguration(
typeof(AutoClassMapper <>),
new List <Assembly>(),
sqlDialect
);
instance = InstanceFactory(iDapperExtensionsConfiguration, dbType);
_instanceList[dbType] = instance;
}
if (null == _DapperImplementor || _DapperImplementor.DbType != dbType)
{
_DapperImplementor = instance;
}
return(instance);
}
public void METHODNAME()
{
SqlServerDialect dialect = new SqlServerDialect();
string sql = dialect.GetPagingSql("SELECT [client].[Individual].[FirstName], [client].[Individual].[LastName] AS [Last] FROM [client].[Individual] ORDER BY [client].[Individual].[LastName], [client].[Individual].[FirstName]", 1, 10, new Dictionary <string, object>());
sql = dialect.GetPagingSql("SELECT [client].[Individual].[FirstName], [client].[Individual].[LastName] AS [Last] FROM [client].[Individual]", 1, 10, new Dictionary <string, object>());
}
private void Init(DbType dbType)
{
ISqlDialect dialect = null;
switch (dbType)
{
case DbType.SQLServer:
dialect = new SqlServerDialect();
break;
case DbType.Oracle:
dialect = new OracleDialect();
break;
case DbType.DB2:
dialect = new DB2DialectCustom();
break;
case DbType.MySQL:
dialect = new MySqlDialect();
break;
default:
throw new NotSupportedException("dbType");
}
Config = new IdentityServerDapperExtensionsConfiguration(this, dialect);
SqlGenerator = new SqlGeneratorImpl(Config);
Db = new Database(Connection, SqlGenerator);
}
/// <summary>
/// 根据配置创建数据库连接
/// </summary>
/// <returns></returns>
public ISqlDialect CreateDialect()
{
ISqlDialect sqlDialect = null;
switch (_dbType)
{
case DBType.SqlServer:
sqlDialect = new SqlServerDialect();
break;
case DBType.MySql:
sqlDialect = new MySqlDialect();
break;
case DBType.Oracle:
sqlDialect = new OracleDialect();
break;
case DBType.SqlCe:
sqlDialect = new SqlCeDialect();
break;
case DBType.SQLite:
sqlDialect = new SqliteDialect();
break;
}
return(sqlDialect);
}
public void ShouldBuildProperDDLForANewSqlServerDatabase()
{
// Setup
string expectedDDL = "CREATE TABLE [Teachers] (" + _nl +
"[Id] uniqueidentifier PRIMARY KEY NONCLUSTERED NOT NULL," + _nl +
"[TeacherName] nvarchar(100) NOT NULL);" + _nl +
"CREATE TABLE [Courses] (" + _nl +
"[Id] uniqueidentifier PRIMARY KEY NONCLUSTERED NOT NULL," + _nl +
"[CourseName] nvarchar(100) NOT NULL," + _nl +
"[CourseDescription] nvarchar(max) ," + _nl +
"[CourseTeacher] uniqueidentifier CONSTRAINT FK_Courses_CourseTeacher FOREIGN KEY (CourseTeacher) REFERENCES Teachers (Id) ON DELETE NO ACTION ON UPDATE NO ACTION NOT NULL," + _nl +
"[IsAvailable] bit NOT NULL DEFAULT(0));";
var dialect = new SqlServerDialect();
var database = new Database("MyDatabase", dialect);
var teacher = database.AddTable("Teachers");
teacher.AddColumn("Id", typeof(Guid)).PrimaryKey().NonClustered().NotNullable();
teacher.AddColumn("TeacherName", typeof(String), 100).NotNullable();
var course = database.AddTable("Courses");
course.AddColumn("Id", typeof(Guid)).PrimaryKey().NonClustered().NotNullable();
course.AddColumn("CourseName", typeof(String), 100).NotNullable();
course.AddColumn("CourseDescription", typeof(String));
course.AddColumn("CourseTeacher", typeof(Guid)).ForeignKey("Teachers", "Id").NotNullable();
course.AddColumn("IsAvailable", typeof(bool)).NotNullable(false);
// Execute
var actualDDL = database.ToString();
// Assert
Assert.AreEqual(expectedDDL, actualDDL);
}
public void Insert_into_SQL_Server_with_return_keys()
{
var d = new SqlServerDialect();
var sql = d.InsertInto("Foo", "dbo", new[] { "CreatedAt" }, true);
Assert.Equal("INSERT INTO [dbo].[Foo] ([CreatedAt]) OUTPUT Inserted.Id VALUES (@CreatedAt)", sql);
}
public void Delete_SQL_Server()
{
var d = new SqlServerDialect();
var sql = d.DeleteFrom("Foo", "dbo", new[] { "Id", "Key" });
Assert.Equal("DELETE FROM [dbo].[Foo] WHERE [Id] = @Id AND [Key] = @Key", sql);
}
public void ScriptParse()
{
var input =
@"-- nullable True -> False
ALTER TABLE [dbo].[Widget] ALTER COLUMN [Name] nvarchar(50) NOT NULL
GO
-- Added: Name, Removed: Id
GO
ALTER TABLE [dbo].[Widget] DROP CONSTRAINT [PK_Widget]
GO
ALTER TABLE [dbo].[Widget] ADD CONSTRAINT [PK_Widget] PRIMARY KEY ([Name] ASC)
";
var output = new SqlServerDialect().ParseScript(input);
Assert.IsTrue(output.SequenceEqual(new string[]
{
"\r\nALTER TABLE [dbo].[Widget] ALTER COLUMN [Name] nvarchar(50) NOT NULL",
"ALTER TABLE [dbo].[Widget] DROP CONSTRAINT [PK_Widget]",
"ALTER TABLE [dbo].[Widget] ADD CONSTRAINT [PK_Widget] PRIMARY KEY ([Name] ASC)"
}));
}
public void IfExistsGeneratesExpectedSql()
{
var dialect = new SqlServerDialect();
var sql = dialect.WriteDropTableIfExists("<tablename>");
Assert.Equal("if exists (select 1 from INFORMATION_SCHEMA.TABLES where TABLE_NAME = '<tablename>') drop table [<tablename>]", sql);
}
public void ShouldUpdateAnExistingTableAndAddANewColumn()
{
// Setup
const string expectedDDL = @"
ALTER TABLE [Test] ADD
[Name] nvarchar(100) NULL;
ALTER TABLE [Test] ADD
[Foo] int DEFAULT(1);
CREATE INDEX [IX_Test_Name_Foo] ON [Test] (Name,Foo);";
var dialect = new SqlServerDialect();
var database = new Database("MyDatabase", dialect);
var testTable = database.UpdateTable("Test");
testTable.AddColumn("Name", typeof(string), 100).Nullable();
testTable.AddColumn("Foo", typeof(int)).Default(1);
database.AddIndex("Test", "Name", "Foo");
// Execute
var actualDDL = database.ToString();
// Assert
Assert.AreEqual(expectedDDL, actualDDL);
}
public void Update_SQL_Server()
{
var d = new SqlServerDialect();
var sql = d.Update("Foo", "dbo", new[] { "CreatedAt" }, new[] { "Id" });
Assert.Equal(@"UPDATE [dbo].[Foo] SET [CreatedAt] = @CreatedAt_set WHERE [Id] = @Id", sql);
}
/// <summary>
/// 工厂模式 根据配置创建数据库
/// </summary>
/// <returns></returns>
public static ISqlDialect CreateSqlDialect()
{
ISqlDialect sqlDialect = null;
switch (PubConstant.DbType.ToLower())
{
case "mssql":
sqlDialect = new SqlServerDialect();
break;
case "mysql":
sqlDialect = new MySqlDialect();
break;
case "oracle":
sqlDialect = new OracleDialect();
break;
case "sqlce":
sqlDialect = new SqlCeDialect();
break;
case "sqlite":
sqlDialect = new SqliteDialect();
break;
}
return(sqlDialect);
}
public void WhenColumnNameDiffersFromProperty_InsertQueryShouldUseColumnName()
{
// Arrange
Person2 person = new Person2 {
Name = "Bob", Age = 40, BirthDate = DateTime.Now
};
Dialect dialect = new SqlServerDialect();
ColumnMapCollection mappings = MapRepository.Instance.GetColumns(typeof(Person2));
var command = new System.Data.SqlClient.SqlCommand();
var db = MockRepository.GenerateStub <IDataMapper>();
db.Expect(d => d.Command).Return(command);
var mappingHelper = new MappingHelper(db);
mappingHelper.CreateParameters <Person2>(person, mappings, true);
string targetTable = "PersonTable";
InsertQuery query = new InsertQuery(dialect, mappings, command, targetTable);
// Act
string queryText = query.Generate();
// Assert
Assert.IsTrue(queryText.Contains("[PersonName]"), "Query should contain column name");
Assert.IsTrue(queryText.Contains("[PersonAge]"), "Query should contain column name");
Assert.IsTrue(queryText.Contains("[BirthDate]"), "Query should contain property name");
Assert.IsTrue(queryText.Contains("[IsHappy]"), "Query should contain property name");
}
private static ICountWriter MakeTarget()
{
var dialect = new SqlServerDialect();
var configuration = MakeConfig();
var selectWriter = new CountWriter(dialect, configuration);
return(selectWriter);
}
/// <summary>
/// Use SqlServer
/// </summary>
public void UseSqlServer(string connectionString)
{
GetDbConnection = () =>
{
return(new SqlConnection(connectionString));
};
SqlDialect = new SqlServerDialect();
}
public async Task <IEnumerable <string> > GetStatementsAsync(IDbConnection connection)
{
var dialect = new SqlServerDialect();
var createObjects = await ScriptCreateTablesAsync(connection, dialect);
return(createObjects
.SelectMany(scriptAction => scriptAction.Commands)
.Select(statement => dialect.FormatStatement(statement)));
}
public void GetSqlShouldThrowsExceptionIfQueryIsNotSelectStatement()
{
var dialect = new SqlServerDialect();
Assert.Throws <ArgumentException>("sql", () =>
{
dialect.GetSetSql("update TestEntity set name = 'Alex'", 1, 1, new Dictionary <string, object>());
});
}
public void GetSetSqlShouldThrowsExceptionIfParamsIsNullEmpty()
{
var dialect = new SqlServerDialect();
Assert.Throws <ArgumentNullException>("parameters", () =>
{
dialect.GetSetSql("select * from TestEntity", 1, 1, null);
});
}
public void GetSetSqlShouldThrowsExceptionIfSqlIsNullOrEmpty(string sql)
{
var dialect = new SqlServerDialect();
Assert.Throws <ArgumentNullException>("sql", () =>
{
dialect.GetSetSql(sql, 1, 1, null);
});
}
public static void QuoteIdentifier_GivenNameWithDotSeparators_ReturnsQuotedIdentifier()
{
const string input = "test.table.name";
const string expected = "[test.table.name]";
var dialect = new SqlServerDialect();
var result = dialect.QuoteIdentifier(input);
Assert.That(result, Is.EqualTo(expected));
}
public static void QuoteIdentifier_GivenNameWithLeftAndRightSquareBrackets_ReturnsQuotedIdentifier()
{
const string input = "[test]table";
const string expected = "[[test]]table]";
var dialect = new SqlServerDialect();
var result = dialect.QuoteIdentifier(input);
Assert.That(result, Is.EqualTo(expected));
}
public static void QuoteIdentifier_GivenRegularLocalName_ReturnsQuotedIdentifier()
{
const string input = "test_table";
const string expected = "[test_table]";
var dialect = new SqlServerDialect();
var result = dialect.QuoteIdentifier(input);
Assert.That(result, Is.EqualTo(expected));
}
private IDialect CreateDialectForSqlServer(string connectionString)
{
IDialect dialect = null;
if (connectionString.Contains("Initial Catalog"))
{
dialect = new SqlServerDialect();
}
return(dialect);
}
public IDialect Create(string connectionString)
{
var dialect = SearchForDialect(connectionString);
if (dialect == null)
{
dialect = new SqlServerDialect();
}
return(dialect);
}
public static async Task CreateTablesAsync(IEnumerable <Type> modelTypes, IDbConnection connection, SqlDialect dialect = null, string defaultSchema = "dbo", string defaultIdentityColumn = "Id")
{
var dataModel = AssemblyModelBuilder.GetDataModelFromTypes(modelTypes, defaultSchema, defaultIdentityColumn);
if (dialect == null)
{
dialect = new SqlServerDialect();
}
var script = await dataModel.ScriptCreateTablesAsync(connection, dialect);
await dialect.ExecuteAsync(connection, script);
}
public void WriteLimit_WithNullCount_WritesOffsetOnly()
{
var expected = "OFFSET 42 ROWS";
var dialect = new SqlServerDialect();
var builder = new StringBuilder();
using (TestSqlWriter writer = new TestSqlWriter(builder, dialect))
{
dialect.WriteLimit(writer, 42, null);
}
var actual = builder.ToString();
Assert.Equal(expected, actual);
}
public void WriteLimit_WithNullCount_WritesOffsetOnly()
{
var expected = "OFFSET 42 ROWS";
var dialect = new SqlServerDialect();
var builder = new StringBuilder();
using (TestSqlWriter writer = new TestSqlWriter(builder, dialect))
{
dialect.WriteLimit(writer, 42, null);
}
var actual = builder.ToString();
Assert.Equal(expected, actual);
}
public void OrderByNestedForeignKeyDesc()
{
var query = new SelectQuery <Post>(new NonExecutingSelectQueryExecutor()).Fetch(p => p.Blog).OrderByDescending(p => p.Blog.CreateDate);
var config = new CustomConfig();
var dialect = new SqlServerDialect();
var writer = new OrderClauseWriter(config, dialect);
var fetchTreeWriter = new FetchTreeWriter(dialect, config);
bool containsPrimaryKeyClause = false;
var result = writer.GetOrderClause(
((SelectQuery <Post>)query).OrderClauses.Dequeue(),
fetchTreeWriter.GetFetchTree((SelectQuery <Post>)query),
out containsPrimaryKeyClause);
Assert.Equal("t_1.[CreateDate] desc", result);
}
public void OrderByNestedNestedPropAsc()
{
var query = new SelectQuery <Comment>(new NonExecutingSelectQueryExecutor()).Fetch(c => c.Post.Blog).OrderBy(c => c.Post.Blog.Title);
var config = new CustomConfig();
var dialect = new SqlServerDialect();
var writer = new OrderClauseWriter(config, dialect);
var fetchTreeWriter = new FetchTreeWriter(dialect, config);
bool containsPrimaryKeyClause = false;
var result = writer.GetOrderClause(
((SelectQuery <Comment>)query).OrderClauses.Dequeue(),
fetchTreeWriter.GetFetchTree((SelectQuery <Comment>)query),
out containsPrimaryKeyClause);
Assert.Equal("t_2.[Title] asc", result);
}
public void WriteLimit_WithNullOffset_WritesZeroOffset()
{
var expected = "OFFSET 0 ROWS FETCH FIRST 10 ROWS ONLY";
var dialect = new SqlServerDialect();
var builder = new StringBuilder();
using (TestSqlWriter writer = new TestSqlWriter(builder, dialect))
{
dialect.WriteLimit(writer, null, 10);
}
var actual = builder.ToString();
Assert.Equal(expected, actual);
}
public void WriteLimit_WritesOffsetFetch()
{
var expected = "OFFSET 42 ROWS FETCH FIRST 10 ROWS ONLY";
var dialect = new SqlServerDialect();
var builder = new StringBuilder();
using (TestSqlWriter writer = new TestSqlWriter(builder, dialect))
{
dialect.WriteLimit(writer, 42, 10);
}
var actual = builder.ToString();
Assert.Equal(expected, actual);
}
