C# 调用SQL的存储过程的接口及实现
原创
©著作权归作者所有:来自51CTO博客作者muzizongheng的原创作品,请联系作者获取转载授权,否则将追究法律责任
1. 接口为ExecuteStoredProcedure(string storedProcedureName, params ObjectParameter[] parameters)
2. 参数为存储过程名字, 及输入值。
3. 思路:创建连接(连接中指定了是Sql/MySql/ODBC等等); 创建通用DbCommand;更改Text以及Type;添加通用Parameter(DBParameter是抽象类,因此需要判断connection类型);判断连接状态(需Open); 调用Execute方法; 关闭连接。
4.
实现如下:
// In V1 of the EF, the context connection is always an EntityConnection EntityConnection entityConnection = (EntityConnection)protocolDB.Connection; // The EntityConnection exposes the underlying store connection DbConnection storeConnection = entityConnection.StoreConnection; DbCommand command = storeConnection.CreateCommand(); command.CommandText = storedProcedureName; command.CommandType = CommandType.StoredProcedure; if (storeConnection is SqlConnection) { foreach (ObjectParameter p in parameters) { command.Parameters.Add(new SqlParameter { ParameterName = p.Name, Value = p.Value }); } } else if (storeConnection is MySqlConnection) { foreach (ObjectParameter p in parameters) { command.Parameters.Add(new MySqlParameter { ParameterName = p.Name, Value = p.Value }); } } else { return enProtocolDBReturnCodes.OPERATION_FAILED; } bool openingConnection = command.Connection.State == ConnectionState.Closed; if (openingConnection) { command.Connection.Open(); } command.ExecuteNonQuery(); if (openingConnection && command.Connection.State == ConnectionState.Open) { command.Connection.Close(); }