在EF中,是否可以使用存储过程返回原始的DataTable/DataSet,而不是返回映射/翻译后的实体对象?
EF建立在ADO.NET之上,因此每当您需要时,可以直接访问DbContext的DbConnection并直接使用它。例如:
using (var db = new MyDbContext())
{
db.Database.Connection.Open();
var con = (SqlConnection)db.Database.Connection;
var cmd = new SqlCommand("exec MyProc", con);
DataTable dt = new DataTable();
using (var rdr = cmd.ExecuteReader())
{
dt.Load(rdr);
}
//. . .
是的,这是可能的。
下面我描述了如何实现。存储过程是数据库的一部分。因此,最好将存储过程添加到访问数据库并创建数据库模型的类中:您的dbContext。
public class MyDbContext : DbContext
{
// TODO: add DbSet properties
#region stored procedures
// TODO (1): add a function that calls the stored procedure
// TODO (2): add a function to check if the stored procedure exists
// TODO (3): add a function that creates the stored procedure
// TODO (4): make sure the stored procedure is created when the database is created
#endregion stored procedure
}
TODO (1): 调用存储过程的过程:
private const string MyStoredProcedureName = ...;
private const string myParamName1 = ...;
private const string myParamName2 = ...;
public void CallMyStoredProcedure(MyParameters parameters)
{
object[] functionParameters = new object[]
{
new SqlParameter(myParamName1, parameters.GetParam1Value(),
new SqlParameter(myParamName2, parameters.GetParam2Value(),
... // if needed add more parameters
};
const string sqlCommand = @"Exec " + MyStoredProcedureName
+ " @" + myParamName1
+ ", @" + myParamName2
... // if needed add more parameters
;
this.Database.ExecutSqlCommand(sqlComman, functionParameters);
}
TODO (2) 检查存储过程是否存在
// returns true if MyStoredProcedure exists
public bool MyStoredProcedureExists()
{
return this.ProcedureExists(MyStoredProcedureName);
}
// returns true if stored procedure with procedureName exists
public bool StoredProcedureExists(string procedureName)
{
object[] functionParameters = new object[]
{
new SqlParameter(@"procedurename", procedureName),
};
string query = @"select [name] from sys.procedures where name= @procedurename";
return this.Database.SqlQuery<string>(query, functionParameters)
.AsEnumerable() // bring to local memory
.Where(item => item == procedureName) // take only the procedures with desired name
.Any(); // true if there is such a procedure
}
TODO (3) 创建存储过程:
public void CreateProcedureUpdateUsageCosts(bool forceRecreate)
{
bool storedProcedureExists = this.MyStoredProcedureExists();
// only create (or update) if not exists or if forceRecreate:
if (!storedProcedureExists || forceRecreate)
{ // create or alter:
Debug.WriteLine("Create stored procedures");
// use a StringBuilder to create the SQL command that will create the
// stored procedure
var x = new StringBuilder();
// ALTER or CREATE?
if (!storedProcedureExists)
{
x.Append(@"CREATE");
}
else
{
x.Append(@"ALTER");
}
// procedure name:
x.Append(@" procedure ");
x.AppendLine(MyStoredProcedureName);
// parameters: (only as an example)
x.AppendLine(@"@ReportPeriod int,");
x.AppendLine(@"@CustomerContractId bigint,");
x.AppendLine(@"@CallType nvarChar(80),");
// etc.
// code
x.AppendLine(@"as");
x.AppendLine(@"begin");
// only as example some of my procedure
x.AppendLine(@"Merge [usagecosts]");
x.AppendLine(@"Using (Select @ReportPeriod as reportperiod,");
x.AppendLine(@" @CustomerContractId as customercontractId,");
...
x.AppendLine(@"end");
// execute the created SQL command
this.Database.ExecuteSqlCommand(x.ToString());
}
}
TODO (4) 确保在创建数据库时创建存储过程
在MyDbContext中:
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
// TODO: if needed add fluent Api to build model
// create stored procedure
this.CreateProcedureUpdateUsageCosts(true);
}
使用方法:
using (var dbContext = new MyDbContext(...))
{
MyParameters parms = FillMyParameters();
dbContext.CallMyStoredProcedure(parms);
}
const string SqlCreateText = "Create Procedure " + myStoredProcedureName + "... etc, ";
- Harald Coppoolse