public static async Task EnsureViewCreatedAsync(this Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade database)
{
var conn = database.GetDbConnection();
// ¼ì²é±í
var hasTable = await conn.ExecuteScalarAsync <bool>($@"SELECT CASE WHEN EXISTS(SELECT * FROM [sys].[tables] WHERE object_id = OBJECT_ID('StudentsStore')) THEN 1 ELSE 0 END");
if (!hasTable)
{
await conn.ExecuteAsync($@"
CREATE TABLE [dbo].[StudentStore]
(
[Id] INT NOT NULL PRIMARY KEY,
[Name] NVarchar(50) NOT NULL,
[Age] INT Null
)
INSERT INTO [dbo].[StudentStore]([Id],[Name],[Age]) VAlues(1,N'A',12)
INSERT INTO [dbo].[StudentStore]([Id],[Name],[Age]) VAlues(2,N'B',13)
");
}
var hasView = await conn.ExecuteScalarAsync <bool>($@"SELECT CASE WHEN EXISTS( SELECT * FROM [sys].[views] WHERE object_id = OBJECT_ID('Students') ) THEN 1 ELSE 0 END");
if (!hasView)
{
await conn.ExecuteAsync($@"
CREATE VIEW [dbo].[Students]
AS SELECT [Id],[Name],[Age] FROM [dbo].[StudentStore]
");
}
}
private static void Migrate(IHost webHost)
{
using (IServiceScope scope = webHost.Services.CreateScope())
{
var db = scope.ServiceProvider.GetRequiredService <ApplicationContext>();
Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade database = db.Database;
database.Migrate();
}
}
private Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade _db()
{
Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade db = null;
try
{
db = _context.Database;
}
catch // context disposed
{
db = _provider.GetService <AppDbContext>()?.Database;
}
return(db);
}
//public static System.Data.DataTable ExecuteStoredProcedure(this Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade databaseFacade
// )
//{
// return ExecuteStoredProcedure(databaseFacade, "foo", ("abc", "def"), ("abc", 5));
//}
public static System.Data.DataTable ExecuteStoredProcedure(
this Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade databaseFacade,
string procedureName,
params System.ValueTuple <string, object>[] parameters
)
{
System.Data.DataTable dt = new System.Data.DataTable();
// using (System.Data.Common.DbConnection con = databaseFacade.GetDbConnection())
System.Data.Common.DbConnection con = databaseFacade.GetDbConnection();
{
using (System.Data.Common.DbCommand cmd = con.CreateCommand())
{
cmd.CommandText = procedureName;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
if (parameters != null)
{
for (int i = 0; i < parameters.Length; ++i)
{
System.Data.Common.DbParameter p = cmd.CreateParameter();
p.ParameterName = parameters[i].Item1;
p.Value = parameters[i].Item2;
p.DbType = GetDbType(p.Value);
if (p.Value == null)
{
p.Value = System.DBNull.Value;
} // End if (p.Value == null)
cmd.Parameters.Add(p);
} // Next i
} // End if (parameters != null)
// cmd.ExecuteNonQuery();
// cmd.ExecuteReader();
using (System.Data.Common.DbDataAdapter da = System.Data.Common.ProviderExtensions.CreateDataAdapter(con, cmd))
{
da.Fill(dt);
} // End Using da
} // End Using cmd
} // End Using con
return(dt);
} // End Function ExecuteStoredProcedure
public void Initialize()
{
Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade db = _db.Database;
if (db.GetPendingMigrations().Any())
{
_logger.LogInformation("Has pending migrations..");
db.Migrate();
_logger.LogInformation("Success");
}
else
{
_logger.LogInformation("No pending migrations.");
}
try
{
InitializeProducts();
}
catch (Exception ex)
{
_logger.LogInformation("Initialize products data exception.\n" + ex.ToString());
}
}