private static DataConnection CreateDataConnection(string connectionString, SqlServerVersion sqlServerVersion)
{
var sqlServerDataProvider = (SqlServerDataProvider)SqlServerTools.GetDataProvider(sqlServerVersion, SqlServerProvider.MicrosoftDataSqlClient);
DatabaseContext.CreateMappings(sqlServerDataProvider.MappingSchema);
return(new DataConnection(sqlServerDataProvider, new SqlConnection(connectionString)));
}
public void Test(string context)
{
SqlServerTools.ResolveSqlTypes("");
using (var conn = new DataConnection(context))
{
var sp = conn.DataProvider.GetSchemaProvider();
var dbSchema = sp.GetSchema(conn);
dbSchema.Tables.ToDictionary(
t => t.IsDefaultSchema ? t.TableName : t.SchemaName + "." + t.TableName,
t => t.Columns.ToDictionary(c => c.ColumnName));
var table = dbSchema.Tables.SingleOrDefault(t => t.TableName.ToLower() == "parent");
Assert.That(table, Is.Not.Null);
Assert.That(table.Columns.Count, Is.EqualTo(2));
// Assert.That(dbSchema.Tables.Single(t => t.TableName.ToLower() == "doctor").ForeignKeys.Count, Is.EqualTo(1));
switch (context)
{
case ProviderName.SqlServer2000:
case ProviderName.SqlServer2005:
case ProviderName.SqlServer2008:
case ProviderName.SqlServer2012:
var indexTable = dbSchema.Tables.Single(t => t.TableName == "IndexTable");
Assert.That(indexTable.ForeignKeys.Count, Is.EqualTo(1));
Assert.That(indexTable.ForeignKeys[0].ThisColumns.Count, Is.EqualTo(2));
break;
}
}
}
private void DropSchemaTable()
{
using (var con = SqlServerTools.OpenConnection())
{
con.ExecuteNonQuery(@"DROP TABLE [dbo].[DatabaseSchema]");
}
}
public static void Run(string platform, bool asyncProcessing)
{
Console.WriteLine($"Testing {platform}...");
var serverName = ".";
DataConnection.AddConfiguration(
"Test",
$"Server={serverName};Database=PerformanceTest;Trusted_Connection=True;Application Name=LinqToDB Test;"
+ (asyncProcessing ? "Asynchronous Processing=True;" : ""),
SqlServerTools.GetDataProvider(SqlServerVersion.v2012));
DataConnection.DefaultConfiguration = "Test";
var basePath = Path.GetDirectoryName(typeof(LinqTestRunner).Assembly.Location);
while (!Directory.Exists(Path.Combine(basePath, "Result")))
{
basePath = Path.GetDirectoryName(basePath);
}
var resultPath = Path.Combine(basePath, "Result");
CreateResultDatabase(false, resultPath);
CreateTestDatabase(false, serverName);
RunTests(platform);
}
private void SetVersion(int version)
{
using (var con = SqlServerTools.OpenConnection())
{
con.ExecuteNonQuery("UPDATE DatabaseSchema SET Version = @version", new { version = version });
}
}
public void Test1(string context)
{
var connectionString = ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;
using (var conn = new DataConnection(SqlServerTools.GetDataProvider(), connectionString))
{
conn.InitCommand(CommandType.Text, "SELECT 1", null, null);
var rd = conn.Command.ExecuteReader();
if (rd.Read())
{
var dp = conn.DataProvider;
var p = Expression.Parameter(typeof(IDataReader));
var dr = Expression.Convert(p, dp.DataReaderType);
var ex = (Expression <Func <IDataReader, int, int> >)dp.GetReaderExpression(conn.MappingSchema, rd, 0, dr, typeof(int));
var func = ex.Compile();
do
{
var value = func(rd, 0);
Assert.AreEqual(1, value);
} while (rd.Read());
}
else
{
Assert.Fail();
}
}
}
/// <summary>
/// Creates an <see cref="IDataProvider" /> that uses Microsoft.Data.SqlClient internally.
/// </summary>
/// <param name="sqlServerVersion">The SQL Server version of the target database (optional). Defaults to <see cref="SqlServerVersion.v2017" />.</param>
/// <param name="createMappings">
/// The delegate that manipulates the mapping schema of the data provider (optional). Alternatively, you could use the Linq2Db attributes to configure
/// your model classes, but we strongly recommend that you use the Linq2Db <see cref="FluentMappingBuilder" /> to specify how model classes are mapped.
/// </param>
public static IDataProvider CreateSqlServerDataProvider(SqlServerVersion sqlServerVersion = SqlServerVersion.v2017, Action <MappingSchema>?createMappings = null)
{
var dataProvider = SqlServerTools.GetDataProvider(sqlServerVersion, SqlServerProvider.MicrosoftDataSqlClient);
createMappings?.Invoke(dataProvider.MappingSchema);
return(dataProvider);
}
private DataConnection CreateDataConnection(SchemaInitializationCommand command)
{
var connectionString = _connectionStringSettings.GetConnectionString(command.ConnectionStringIdentity);
var connection = SqlServerTools.CreateDataConnection(connectionString);
connection.AddMappingSchema(command.MappingSchema);
return(connection);
}
private DataConnection CreateDataConnection(string connectionString, MappingSchema schema, TimeSpan timeout)
{
var connection = SqlServerTools.CreateDataConnection(connectionString);
connection.AddMappingSchema(schema);
connection.CommandTimeout = (int)timeout.TotalMilliseconds;
return(connection);
}
private static IDataProvider DetermineProvider(SqlConnection connection)
{
var sqlVersion = connection.ServerVersion.StartsWith("11.", System.StringComparison.Ordinal) ?
SqlServerVersion.v2008 :
SqlServerVersion.v2012;
return(SqlServerTools.GetDataProvider(sqlVersion));
}
public static IEnumerable <Person> PersonSelectByKeyLowercaseColumns(DataConnection dataConnection, int? @id)
{
var databaseName = TestUtils.GetDatabaseName(dataConnection);
var escapedTableName = SqlServerTools.QuoteIdentifier(databaseName);
return(dataConnection.QueryProc <Person>(escapedTableName + "..[Person_SelectByKeyLowercase]",
new DataParameter("@id", @id)));
}
// This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
public void Configure(IApplicationBuilder app, IHostingEnvironment env, ILoggerFactory loggerFactory)
{
loggerFactory.AddConsole(Configuration.GetSection("Logging"));
loggerFactory.AddDebug();
if (env.IsDevelopment())
{
app.UseDeveloperExceptionPage();
}
else
{
app.UseExceptionHandler("/Home/Error");
}
var connectionString = new SqlConnectionStringBuilder(Configuration["Data:DefaultConnection:ConnectionString"])
{
InitialCatalog = "master"
}.ConnectionString;
using (var db = new DataConnection(SqlServerTools.GetDataProvider(), connectionString))
{
try
{
var sql = "create database [" +
new SqlConnectionStringBuilder(Configuration["Data:DefaultConnection:ConnectionString"])
.InitialCatalog + "]";
db.Execute(sql);
}
catch
{
//
}
}
// Try to create tables
using (var db = new ApplicationDataConnection())
{
TryCreateTable <ApplicationUser>(db);
TryCreateTable <LinqToDB.Identity.IdentityRole>(db);
TryCreateTable <LinqToDB.Identity.IdentityUserClaim <string> >(db);
TryCreateTable <LinqToDB.Identity.IdentityRoleClaim <string> >(db);
TryCreateTable <LinqToDB.Identity.IdentityUserLogin <string> >(db);
TryCreateTable <LinqToDB.Identity.IdentityUserRole <string> >(db);
TryCreateTable <LinqToDB.Identity.IdentityUserToken <string> >(db);
}
app.UseStaticFiles();
app.UseAuthentication();
// To configure external authentication please see http://go.microsoft.com/fwlink/?LinkID=532715
app.UseMvc(routes =>
{
routes.MapRoute(
"default",
"{controller=Home}/{action=Index}/{id?}");
});
}
public DbContext(ILogger logger, DbContextOptions options)
: base(
connectionString: options.ConnectionString,
dataProvider: SqlServerTools.GetDataProvider(
SqlServerVersion.v2017,
SqlServerProvider.MicrosoftDataSqlClient))
{
_logger = logger;
}
public DataConnection CreateConnection(StorageDescriptorFeature storageDescriptorFeature)
{
var connectionString = _connectionStringSettings.GetConnectionString(storageDescriptorFeature.ConnectionStringName);
var connection = SqlServerTools.CreateDataConnection(connectionString);
connection.AddMappingSchema(storageDescriptorFeature.MappingSchema);
connection.CommandTimeout = (int)TimeSpan.FromMinutes(30).TotalMilliseconds;
return(connection);
}
private DataConnection CreateDataConnection(StorageDescriptor storageDescriptor)
{
var connectionString = _connectionStringSettings.GetConnectionString(storageDescriptor.ConnectionStringIdentity);
var connection = SqlServerTools.CreateDataConnection(connectionString);
connection.AddMappingSchema(storageDescriptor.MappingSchema);
connection.CommandTimeout = (int)storageDescriptor.CommandTimeout.TotalMilliseconds;
return(connection);
}
public static IServiceCollection AddDatabaseContext(this IServiceCollection services, string connectionString, SqlServerVersion sqlServerVersion = SqlServerVersion.v2017)
{
var sqlServerDataProvider = (SqlServerDataProvider)SqlServerTools.GetDataProvider(sqlServerVersion, SqlServerProvider.MicrosoftDataSqlClient);
CreateMappings(sqlServerDataProvider.MappingSchema);
return(services.AddSingleton(sqlServerDataProvider)
.AddTransient(c => new DataConnection(c.GetRequiredService <SqlServerDataProvider>(), new SqlConnection(connectionString), true))
.AddSingleton <Func <DataConnection> >(c => c.GetRequiredService <DataConnection>));
}
private DataConnection CreateDataConnection(IConnectionStringIdentity connectionStringIdentity, MappingSchema mappingSchema)
{
var connectionString = _connectionStringSettings.GetConnectionString(connectionStringIdentity);
var dataConnection = SqlServerTools.CreateDataConnection(connectionString);
dataConnection.AddMappingSchema(mappingSchema);
dataConnection.CommandTimeout = 0;
return(dataConnection);
}
public DataConnection CreateErmConnection()
{
var dataConnection = new DataConnection(
SqlServerTools.GetDataProvider(SqlServerVersion.v2012),
_connectionStringSettings.GetConnectionString(ErmConnectionStringIdentity.Instance, _tenantProvider.Current));
dataConnection.AddMappingSchema(Schema.Erm);
return(dataConnection);
}
static void CreateTestDatabase(bool enforceCreate, string serverName)
{
Console.WriteLine("Creating database...");
using (var db = SqlServerTools.CreateDataConnection($"Server={serverName};Database=master;Trusted_Connection=True"))
{
if (!enforceCreate)
{
if (db.Execute <object>("SELECT db_id('PerformanceTest')") != null)
{
if (db.Execute <object>("SELECT OBJECT_ID('PerformanceTest.dbo.Setting', N'U')") != null)
{
if (db.GetTable <Setting>()
.DatabaseName("PerformanceTest")
.Any(s => s.Name == "DB Version" && s.Value == DatabaseVersion))
{
return;
}
}
}
}
db.Execute("DROP DATABASE IF EXISTS PerformanceTest");
db.Execute("CREATE DATABASE PerformanceTest");
}
using (var db = new L2DB.L2DBContext())
{
CreateTable(db, new[] { new Setting {
Name = "DB Version", Value = DatabaseVersion
} });
CreateTable(db, new[] { new Narrow {
ID = 1, Field1 = 2
} });
CreateTable(db, Enumerable.Range(1, 1000000).Select(i => new NarrowLong {
ID = i, Field1 = -i
}));
CreateTable(db, Enumerable.Range(1, 1000000).Select(i => new WideLong
{
ID = i,
Field1 = -i,
ByteValue = i % 2 == 0 ? null : (byte?)(i % Byte.MaxValue / 2),
ShortValue = i % 2 == 0 ? null : (short?)(i % Int16.MaxValue / 2),
IntValue = i % 2 == 1 ? null : (int?)(i % Int32.MaxValue - 1),
LongValue = i % 2 == 0 ? null : (long?)(i * 2),
StringValue = i % 2 == 0 ? null : new string(Enumerable.Range(0, 95).Select(n => (char)(n % 30 + (int)' ')).ToArray()),
DateTimeValue = i % 2 == 1 ? null : (DateTime?)new DateTime(i),
TimeValue = i % 2 == 1 ? null : (TimeSpan?)new TimeSpan(i),
DecimalValue = i % 2 == 0 ? null : (decimal?)i,
DoubleValue = i % 2 == 1 ? null : (double?)i,
FloatValue = i % 2 == 0 ? null : (float?)i,
}));
}
Console.WriteLine("Database created.");
}
public void Compiled_Update_Nullable_Full()
{
for (var i = 0; i < _iterations; i++)
{
using (var db = new DataConnection(SqlServerTools.GetDataProvider(SqlServerVersion.v2008, SqlServerProvider.MicrosoftDataSqlClient), _cn))
{
_compiledNullable(db, i);
}
}
}
public void Test1(string context)
{
var connectionString = ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;
using (var conn = new DataConnection(SqlServerTools.GetDataProvider(), connectionString))
{
Assert.That(conn.Connection.State, Is.EqualTo(ConnectionState.Open));
Assert.That(conn.ConfigurationString, Is.Null);
}
}
public DataConnection CreateVrConnection()
{
var dataConnection = new DataConnection(
SqlServerTools.GetDataProvider(SqlServerVersion.v2012),
_connectionStringSettings.GetConnectionString(ValidationRulesConnectionStringIdentity.Instance, _tenantProvider.Current));
// Schema.Facts needed for Facts.EntityName table
dataConnection.AddMappingSchema(Schema.Facts);
dataConnection.AddMappingSchema(Schema.Messages);
return(dataConnection);
}
public static void Run(string[] args)
{
var serverName = ".";
DataConnection.AddConfiguration(
"Test",
$"Server={serverName};Database=PerformanceTest;Trusted_Connection=True",
SqlServerTools.GetDataProvider(SqlServerVersion.v2012));
DataConnection.DefaultConfiguration = "Test";
CreateDatabase(serverName);
RunTests();
}
public void Update_Nullable_Full()
{
for (var i = 0; i < _iterations; i++)
{
using (var db = new DataConnection(SqlServerTools.GetDataProvider(SqlServerVersion.v2008, SqlServerProvider.MicrosoftDataSqlClient), _cn))
{
db.GetTable <MyPOCON>()
.Where(p => p.Code == "A" + i && p.Currency == "SUR")
.Set(p => p.Weight, i * 10)
.Set(p => p.Currency, "SUR")
.Set(p => p.Value, i * i + 2)
.Update();
}
}
}
private void EnsureSchemaTable()
{
using (var con = SqlServerTools.OpenConnection())
{
con.ExecuteNonQuery(@"
IF OBJECT_ID(N'dbo.[DatabaseSchema]', N'U') IS NULL
BEGIN
CREATE TABLE [dbo].[DatabaseSchema] (
[Version] int not null default 1
);
INSERT INTO DatabaseSchema VALUES(1);
END
");
}
}
public void Test(string context)
{
SqlServerTools.ResolveSqlTypes("");
using (var conn = new DataConnection(context))
{
var sp = conn.DataProvider.GetSchemaProvider();
var dbSchema = sp.GetSchema(conn);
var tableNames = new HashSet <string>();
foreach (var schemaTable in dbSchema.Tables)
{
var tableName = schemaTable.CatalogName + "."
+ (schemaTable.IsDefaultSchema
? schemaTable.TableName
: schemaTable.SchemaName + "." + schemaTable.TableName);
if (tableNames.Contains(tableName))
{
Assert.Fail("Not unique table " + tableName);
}
tableNames.Add(tableName);
var columnNames = new HashSet <string>();
foreach (var schemaColumm in schemaTable.Columns)
{
if (columnNames.Contains(schemaColumm.ColumnName))
{
Assert.Fail("Not unique column {0} for table {1}.{2}", schemaColumm.ColumnName, schemaTable.SchemaName, schemaTable.TableName);
}
columnNames.Add(schemaColumm.ColumnName);
}
}
var table = dbSchema.Tables.SingleOrDefault(t => t.TableName.ToLower() == "parent");
Assert.That(table, Is.Not.Null);
Assert.That(table.Columns.Count(c => c.ColumnName != "_ID"), Is.EqualTo(2));
AssertType <Model.LinqDataTypes>(conn.MappingSchema, dbSchema);
AssertType <Model.Parent>(conn.MappingSchema, dbSchema);
// Assert.That(dbSchema.Tables.Single(t => t.TableName.ToLower() == "doctor").ForeignKeys.Count, Is.EqualTo(1));
}
}
static IoC()
{
var builder = new Autofac.ContainerBuilder();
builder.RegisterModule(new MediatrModule());
builder.RegisterType <WebAPI>().AsSelf().SingleInstance();
builder.RegisterType <QueryWorker>().AsSelf().SingleInstance();
builder.RegisterType <ServiceImpl>().AsSelf().SingleInstance();
builder.Register(x =>
{
var sqlString =
"Data Source=.;Initial Catalog=BusinessModel;Integrated Security=true;User Id=POSSQL;Password=123456";
return(SqlServerTools.CreateDataConnection(sqlString));
}
).InstancePerLifetimeScope();
Default = builder.Build();
}
static void Main(string[] args)
{
Console.WriteLine("Hello World!");
string ConnectionString =
"Server=(localdb)\\MSSQLLocalDB;Database=Northwind;Trusted_Connection=True;MultipleActiveResultSets=true";
using (var db = SqlServerTools.CreateDataConnection(ConnectionString))
{
if (!db.IsDatabaseExists())
{
db.CreateDatabase();
}
var sp = db.DataProvider.GetSchemaProvider();
var dbSchema = sp.GetSchema(db);
if (!dbSchema.Tables.Any(t => t.TableName == typeof(Category).Name))
{
//no required table-create it
db.CreateTable <Category>();
//db.CreateTable();
}
}
using (var db = SqlServerTools.CreateDataConnection(ConnectionString))
{
var q =
from c in db.GetTable <Category>()
select c;
foreach (var category in q)
{
Console.WriteLine("ID : {0}, Name : {1}",
category.CategoryID,
category.CategoryName);
}
}
}
public void Test1()
{
Type modelType = GetModelType.Get();
string ConnectionString =
"Server=(localdb)\\MSSQLLocalDB;Database=Northwind;Trusted_Connection=True;MultipleActiveResultSets=true";
using (DataConnection db = SqlServerTools.CreateDataConnection(ConnectionString))
{
Type t = db.GetType();
MethodInfo mi = t.GetMethod("GetTable", new Type[] { });
MethodInfo miConstructed = mi.MakeGenericMethod(modelType);
object[] args = { };
object res = miConstructed.Invoke(db, args);
var res2 = db.GetTable <Category>().Select(s => s.CategoryName).ToArray();
var res3 = db.GetTable <Category>().Select(s => s.CategoryName).ToArray();
//var q =
// from c in db.GetTable<Category>()
// select c;
//foreach (var category in q)
//{
// Console.WriteLine("ID : {0}, Name : {1}",
// category.CategoryID,
// category.CategoryName);
//}
}
// create an instance of the class
//object destObject = Activator.CreateInstance(dynamicType);
}
public void Setup()
{
_cn = new MockDbConnection(new QueryResult()
{
Return = 1
}, ConnectionState.Open);
_db = new DataConnection(SqlServerTools.GetDataProvider(SqlServerVersion.v2008, SqlServerProvider.MicrosoftDataSqlClient), _cn);
_compiled = CompiledQuery.Compile <DataConnection, int, int>((ctx, i) =>
ctx.GetTable <MyPOCO>()
.Where(p => p.Code == "A" + i && p.Currency == "SUR")
.Set(p => p.Weight, i * 10)
.Set(p => p.Currency, "SUR")
.Set(p => p.Value, i * i + 2)
.Update());
_compiledNullable = CompiledQuery.Compile <DataConnection, int, int>((ctx, i) =>
ctx.GetTable <MyPOCON>()
.Where(p => p.Code == "A" + i && p.Currency == "SUR")
.Set(p => p.Weight, i * 10)
.Set(p => p.Currency, "SUR")
.Set(p => p.Value, i * i + 2)
.Update());
}
