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] "); } }
//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