利用存储过程来提高数据库的更新问题

1. 创建存储过程，

DELIMITER //

use protocoldb//

drop procedure if exists sp_protocol_Update//

create procedure sp_protocol_Update
(
in p_ProtocolNodeUID                               varchar(64),
in p_VersionUID                                    varchar(64),
in p_ParentProtocolNodeUID                      varchar(64),
in p_IsDefaultSelected                          TINYINT(1),
in p_SequenceOrder                              INT(11),
in p_NodeType                                   CHAR(50),
in p_NodeKey                                    varchar(64),
in p_NodeLabel                                  varchar(200),
in p_ExamedBodyPart                             varchar(200),
in p_ScanTypeSupported                          CHAR(50),
in p_PatientPosition                            CHAR(50),
in p_PrivateProtocolFilePath                    varchar(1024),
in p_Description                               varchar(1024),
in p_LeftRightFlag                              CHAR(50),
in p_AgeGroup                                   CHAR(50),
in p_FileVersionNumber                          varchar(64),
in p_ExampleFigurePath                          varchar(1024),
in p_PatientTypeID                              varchar(64),
in p_ProcedureID                                varchar(64),
in p_PSASID                                     varchar(64),
in p_ProtocolID                                 varchar(64),
in p_IsFavourite                                TINYINT(1),
in p_IsTimed                                    TINYINT(1),
in p_IsFactoryPredefine                         TINYINT(1),
in p_IsNeedInjection                            TINYINT(1),
in p_IsLicensed                                 TINYINT(1),
in p_IsCardicScan                               TINYINT(1),
in p_IsPediatric                                TINYINT(1),
in p_CreateDate                                 DATETIME,
in p_CreateBy                                   varchar(64),
in p_UpdateDate                                 DATETIME,
in p_UpdateBy                               varchar(64)
)

begin

update protocolnode set

VersionUID = p_VersionUID,
ParentProtocolNodeUID = p_ParentProtocolNodeUID,
IsDefaultSelected = p_IsDefaultSelected,
SequenceOrder = p_SequenceOrder,
NodeType = p_NodeType,
NodeKey = p_NodeKey,
NodeLabel = p_NodeLabel,
ExamedBodyPart = p_ExamedBodyPart,
ScanTypeSupported = p_ScanTypeSupported,
PatientPosition = p_PatientPosition,
PrivateProtocolFilePath = p_PrivateProtocolFilePath,
Description = p_Description,
LeftRightFlag = p_LeftRightFlag,
AgeGroup = p_AgeGroup,
FileVersionNumber = p_FileVersionNumber,
ExampleFigurePath = p_ExampleFigurePath,
PatientTypeID = p_PatientTypeID,
ProcedureID = p_ProcedureID,
PSASID = p_PSASID,
ProtocolID = p_ProtocolID,
IsFavourite = p_IsFavourite,
IsTimed = p_IsTimed,
IsFactoryPredefine = p_IsFactoryPredefine,
IsNeedInjection = p_IsNeedInjection,
IsLicensed = p_IsLicensed,
IsCardicScan = p_IsCardicScan,
IsPediatric = p_IsPediatric,
CreateDate = p_CreateDate,
CreateBy = p_CreateBy,
UpdateDate = p_UpdateDate,
UpdateBy = p_UpdateBy

where ProtocolNodeUID = p_ProtocolNodeUID;

end

//

DELIMITER ;

2.用EF映射存储过程

在Model View里选择Function Import，添加存储过程的映射方法。如XXX.Designer.cs里的

      #region Function Imports
    
        /// <summary>
        /// No Metadata Documentation available.
        /// </summary>
        /// <param name="p_ProtocolNodeUID">No Metadata Documentation available.</param>
        /// <param name="p_VersionUID">No Metadata Documentation available.</param>
        /// <param name="p_ParentProtocolNodeUID">No Metadata Documentation available.</param>
        /// <param name="p_IsDefaultSelected">No Metadata Documentation available.</param>
        /// <param name="p_SequenceOrder">No Metadata Documentation available.</param>
        /// <param name="p_NodeType">No Metadata Documentation available.</param>
        /// <param name="p_NodeKey">No Metadata Documentation available.</param>
        /// <param name="p_NodeLabel">No Metadata Documentation available.</param>
        /// <param name="p_ExamedBodyPart">No Metadata Documentation available.</param>
        /// <param name="p_ScanTypeSupported">No Metadata Documentation available.</param>
        /// <param name="p_PatientPosition">No Metadata Documentation available.</param>
        /// <param name="p_PrivateProtocolFilePath">No Metadata Documentation available.</param>
        /// <param name="p_Description">No Metadata Documentation available.</param>
        /// <param name="p_LeftRightFlag">No Metadata Documentation available.</param>
        /// <param name="p_AgeGroup">No Metadata Documentation available.</param>
        /// <param name="p_FileVersionNumber">No Metadata Documentation available.</param>
        /// <param name="p_ExampleFigurePath">No Metadata Documentation available.</param>
        /// <param name="p_PatientTypeID">No Metadata Documentation available.</param>
        /// <param name="p_ProcedureID">No Metadata Documentation available.</param>
        /// <param name="p_PSASID">No Metadata Documentation available.</param>
        /// <param name="p_ProtocolID">No Metadata Documentation available.</param>
        /// <param name="p_IsFavourite">No Metadata Documentation available.</param>
        /// <param name="p_IsTimed">No Metadata Documentation available.</param>
        /// <param name="p_IsFactoryPredefine">No Metadata Documentation available.</param>
        /// <param name="p_IsNeedInjection">No Metadata Documentation available.</param>
        /// <param name="p_IsLicensed">No Metadata Documentation available.</param>
        /// <param name="p_IsCardicScan">No Metadata Documentation available.</param>
        /// <param name="p_IsPediatric">No Metadata Documentation available.</param>
        /// <param name="p_CreateDate">No Metadata Documentation available.</param>
        /// <param name="p_CreateBy">No Metadata Documentation available.</param>
        /// <param name="p_UpdateDate">No Metadata Documentation available.</param>
        /// <param name="p_UpdateBy">No Metadata Documentation available.</param>
        public int sp_protocol_Update(global::System.String p_ProtocolNodeUID, global::System.String p_VersionUID, global::System.String p_ParentProtocolNodeUID, Nullable<global::System.SByte> p_IsDefaultSelected, Nullable<global::System.Int32> p_SequenceOrder, global::System.String p_NodeType, global::System.String p_NodeKey, global::System.String p_NodeLabel, global::System.String p_ExamedBodyPart, global::System.String p_ScanTypeSupported, global::System.String p_PatientPosition, global::System.String p_PrivateProtocolFilePath, global::System.String p_Description, global::System.String p_LeftRightFlag, global::System.String p_AgeGroup, global::System.String p_FileVersionNumber, global::System.String p_ExampleFigurePath, global::System.String p_PatientTypeID, global::System.String p_ProcedureID, global::System.String p_PSASID, global::System.String p_ProtocolID, Nullable<global::System.SByte> p_IsFavourite, Nullable<global::System.SByte> p_IsTimed, Nullable<global::System.SByte> p_IsFactoryPredefine, Nullable<global::System.SByte> p_IsNeedInjection, Nullable<global::System.SByte> p_IsLicensed, Nullable<global::System.SByte> p_IsCardicScan, Nullable<global::System.SByte> p_IsPediatric, Nullable<global::System.DateTime> p_CreateDate, global::System.String p_CreateBy, Nullable<global::System.DateTime> p_UpdateDate, global::System.String p_UpdateBy)
        {
            ObjectParameter p_ProtocolNodeUIDParameter;
            if (p_ProtocolNodeUID != null)
            {
                p_ProtocolNodeUIDParameter = new ObjectParameter("p_ProtocolNodeUID", p_ProtocolNodeUID);
            }
            else
            {
                p_ProtocolNodeUIDParameter = new ObjectParameter("p_ProtocolNodeUID", typeof(global::System.String));
            }
    
            ObjectParameter p_VersionUIDParameter;
            if (p_VersionUID != null)
            {
                p_VersionUIDParameter = new ObjectParameter("p_VersionUID", p_VersionUID);
            }
            else
            {
                p_VersionUIDParameter = new ObjectParameter("p_VersionUID", typeof(global::System.String));
            }
    
            ObjectParameter p_ParentProtocolNodeUIDParameter;
            if (p_ParentProtocolNodeUID != null)
            {
                p_ParentProtocolNodeUIDParameter = new ObjectParameter("p_ParentProtocolNodeUID", p_ParentProtocolNodeUID);
            }
            else
            {
                p_ParentProtocolNodeUIDParameter = new ObjectParameter("p_ParentProtocolNodeUID", typeof(global::System.String));
            }
    
            ObjectParameter p_IsDefaultSelectedParameter;
            if (p_IsDefaultSelected.HasValue)
            {
                p_IsDefaultSelectedParameter = new ObjectParameter("p_IsDefaultSelected", p_IsDefaultSelected);
            }
            else
            {
                p_IsDefaultSelectedParameter = new ObjectParameter("p_IsDefaultSelected", typeof(global::System.SByte));
            }
    
            ObjectParameter p_SequenceOrderParameter;
            if (p_SequenceOrder.HasValue)
            {
                p_SequenceOrderParameter = new ObjectParameter("p_SequenceOrder", p_SequenceOrder);
            }
            else
            {
                p_SequenceOrderParameter = new ObjectParameter("p_SequenceOrder", typeof(global::System.Int32));
            }
    
            ObjectParameter p_NodeTypeParameter;
            if (p_NodeType != null)
            {
                p_NodeTypeParameter = new ObjectParameter("p_NodeType", p_NodeType);
            }
            else
            {
                p_NodeTypeParameter = new ObjectParameter("p_NodeType", typeof(global::System.String));
            }
    
            ObjectParameter p_NodeKeyParameter;
            if (p_NodeKey != null)
            {
                p_NodeKeyParameter = new ObjectParameter("p_NodeKey", p_NodeKey);
            }
            else
            {
                p_NodeKeyParameter = new ObjectParameter("p_NodeKey", typeof(global::System.String));
            }
    
            ObjectParameter p_NodeLabelParameter;
            if (p_NodeLabel != null)
            {
                p_NodeLabelParameter = new ObjectParameter("p_NodeLabel", p_NodeLabel);
            }
            else
            {
                p_NodeLabelParameter = new ObjectParameter("p_NodeLabel", typeof(global::System.String));
            }
    
            ObjectParameter p_ExamedBodyPartParameter;
            if (p_ExamedBodyPart != null)
            {
                p_ExamedBodyPartParameter = new ObjectParameter("p_ExamedBodyPart", p_ExamedBodyPart);
            }
            else
            {
                p_ExamedBodyPartParameter = new ObjectParameter("p_ExamedBodyPart", typeof(global::System.String));
            }
    
            ObjectParameter p_ScanTypeSupportedParameter;
            if (p_ScanTypeSupported != null)
            {
                p_ScanTypeSupportedParameter = new ObjectParameter("p_ScanTypeSupported", p_ScanTypeSupported);
            }
            else
            {
                p_ScanTypeSupportedParameter = new ObjectParameter("p_ScanTypeSupported", typeof(global::System.String));
            }
    
            ObjectParameter p_PatientPositionParameter;
            if (p_PatientPosition != null)
            {
                p_PatientPositionParameter = new ObjectParameter("p_PatientPosition", p_PatientPosition);
            }
            else
            {
                p_PatientPositionParameter = new ObjectParameter("p_PatientPosition", typeof(global::System.String));
            }
    
            ObjectParameter p_PrivateProtocolFilePathParameter;
            if (p_PrivateProtocolFilePath != null)
            {
                p_PrivateProtocolFilePathParameter = new ObjectParameter("p_PrivateProtocolFilePath", p_PrivateProtocolFilePath);
            }
            else
            {
                p_PrivateProtocolFilePathParameter = new ObjectParameter("p_PrivateProtocolFilePath", typeof(global::System.String));
            }
    
            ObjectParameter p_DescriptionParameter;
            if (p_Description != null)
            {
                p_DescriptionParameter = new ObjectParameter("p_Description", p_Description);
            }
            else
            {
                p_DescriptionParameter = new ObjectParameter("p_Description", typeof(global::System.String));
            }
    
            ObjectParameter p_LeftRightFlagParameter;
            if (p_LeftRightFlag != null)
            {
                p_LeftRightFlagParameter = new ObjectParameter("p_LeftRightFlag", p_LeftRightFlag);
            }
            else
            {
                p_LeftRightFlagParameter = new ObjectParameter("p_LeftRightFlag", typeof(global::System.String));
            }
    
            ObjectParameter p_AgeGroupParameter;
            if (p_AgeGroup != null)
            {
                p_AgeGroupParameter = new ObjectParameter("p_AgeGroup", p_AgeGroup);
            }
            else
            {
                p_AgeGroupParameter = new ObjectParameter("p_AgeGroup", typeof(global::System.String));
            }
    
            ObjectParameter p_FileVersionNumberParameter;
            if (p_FileVersionNumber != null)
            {
                p_FileVersionNumberParameter = new ObjectParameter("p_FileVersionNumber", p_FileVersionNumber);
            }
            else
            {
                p_FileVersionNumberParameter = new ObjectParameter("p_FileVersionNumber", typeof(global::System.String));
            }
    
            ObjectParameter p_ExampleFigurePathParameter;
            if (p_ExampleFigurePath != null)
            {
                p_ExampleFigurePathParameter = new ObjectParameter("p_ExampleFigurePath", p_ExampleFigurePath);
            }
            else
            {
                p_ExampleFigurePathParameter = new ObjectParameter("p_ExampleFigurePath", typeof(global::System.String));
            }
    
            ObjectParameter p_PatientTypeIDParameter;
            if (p_PatientTypeID != null)
            {
                p_PatientTypeIDParameter = new ObjectParameter("p_PatientTypeID", p_PatientTypeID);
            }
            else
            {
                p_PatientTypeIDParameter = new ObjectParameter("p_PatientTypeID", typeof(global::System.String));
            }
    
            ObjectParameter p_ProcedureIDParameter;
            if (p_ProcedureID != null)
            {
                p_ProcedureIDParameter = new ObjectParameter("p_ProcedureID", p_ProcedureID);
            }
            else
            {
                p_ProcedureIDParameter = new ObjectParameter("p_ProcedureID", typeof(global::System.String));
            }
    
            ObjectParameter p_PSASIDParameter;
            if (p_PSASID != null)
            {
                p_PSASIDParameter = new ObjectParameter("p_PSASID", p_PSASID);
            }
            else
            {
                p_PSASIDParameter = new ObjectParameter("p_PSASID", typeof(global::System.String));
            }
    
            ObjectParameter p_ProtocolIDParameter;
            if (p_ProtocolID != null)
            {
                p_ProtocolIDParameter = new ObjectParameter("p_ProtocolID", p_ProtocolID);
            }
            else
            {
                p_ProtocolIDParameter = new ObjectParameter("p_ProtocolID", typeof(global::System.String));
            }
    
            ObjectParameter p_IsFavouriteParameter;
            if (p_IsFavourite.HasValue)
            {
                p_IsFavouriteParameter = new ObjectParameter("p_IsFavourite", p_IsFavourite);
            }
            else
            {
                p_IsFavouriteParameter = new ObjectParameter("p_IsFavourite", typeof(global::System.SByte));
            }
    
            ObjectParameter p_IsTimedParameter;
            if (p_IsTimed.HasValue)
            {
                p_IsTimedParameter = new ObjectParameter("p_IsTimed", p_IsTimed);
            }
            else
            {
                p_IsTimedParameter = new ObjectParameter("p_IsTimed", typeof(global::System.SByte));
            }
    
            ObjectParameter p_IsFactoryPredefineParameter;
            if (p_IsFactoryPredefine.HasValue)
            {
                p_IsFactoryPredefineParameter = new ObjectParameter("p_IsFactoryPredefine", p_IsFactoryPredefine);
            }
            else
            {
                p_IsFactoryPredefineParameter = new ObjectParameter("p_IsFactoryPredefine", typeof(global::System.SByte));
            }
    
            ObjectParameter p_IsNeedInjectionParameter;
            if (p_IsNeedInjection.HasValue)
            {
                p_IsNeedInjectionParameter = new ObjectParameter("p_IsNeedInjection", p_IsNeedInjection);
            }
            else
            {
                p_IsNeedInjectionParameter = new ObjectParameter("p_IsNeedInjection", typeof(global::System.SByte));
            }
    
            ObjectParameter p_IsLicensedParameter;
            if (p_IsLicensed.HasValue)
            {
                p_IsLicensedParameter = new ObjectParameter("p_IsLicensed", p_IsLicensed);
            }
            else
            {
                p_IsLicensedParameter = new ObjectParameter("p_IsLicensed", typeof(global::System.SByte));
            }
    
            ObjectParameter p_IsCardicScanParameter;
            if (p_IsCardicScan.HasValue)
            {
                p_IsCardicScanParameter = new ObjectParameter("p_IsCardicScan", p_IsCardicScan);
            }
            else
            {
                p_IsCardicScanParameter = new ObjectParameter("p_IsCardicScan", typeof(global::System.SByte));
            }
    
            ObjectParameter p_IsPediatricParameter;
            if (p_IsPediatric.HasValue)
            {
                p_IsPediatricParameter = new ObjectParameter("p_IsPediatric", p_IsPediatric);
            }
            else
            {
                p_IsPediatricParameter = new ObjectParameter("p_IsPediatric", typeof(global::System.SByte));
            }
    
            ObjectParameter p_CreateDateParameter;
            if (p_CreateDate.HasValue)
            {
                p_CreateDateParameter = new ObjectParameter("p_CreateDate", p_CreateDate);
            }
            else
            {
                p_CreateDateParameter = new ObjectParameter("p_CreateDate", typeof(global::System.DateTime));
            }
    
            ObjectParameter p_CreateByParameter;
            if (p_CreateBy != null)
            {
                p_CreateByParameter = new ObjectParameter("p_CreateBy", p_CreateBy);
            }
            else
            {
                p_CreateByParameter = new ObjectParameter("p_CreateBy", typeof(global::System.String));
            }
    
            ObjectParameter p_UpdateDateParameter;
            if (p_UpdateDate.HasValue)
            {
                p_UpdateDateParameter = new ObjectParameter("p_UpdateDate", p_UpdateDate);
            }
            else
            {
                p_UpdateDateParameter = new ObjectParameter("p_UpdateDate", typeof(global::System.DateTime));
            }
    
            ObjectParameter p_UpdateByParameter;
            if (p_UpdateBy != null)
            {
                p_UpdateByParameter = new ObjectParameter("p_UpdateBy", p_UpdateBy);
            }
            else
            {
                p_UpdateByParameter = new ObjectParameter("p_UpdateBy", typeof(global::System.String));
            }
    
            return base.ExecuteFunction("sp_protocol_Update", p_ProtocolNodeUIDParameter, p_VersionUIDParameter, p_ParentProtocolNodeUIDParameter, p_IsDefaultSelectedParameter, p_SequenceOrderParameter, p_NodeTypeParameter, p_NodeKeyParameter, p_NodeLabelParameter, p_ExamedBodyPartParameter, p_ScanTypeSupportedParameter, p_PatientPositionParameter, p_PrivateProtocolFilePathParameter, p_DescriptionParameter, p_LeftRightFlagParameter, p_AgeGroupParameter, p_FileVersionNumberParameter, p_ExampleFigurePathParameter, p_PatientTypeIDParameter, p_ProcedureIDParameter, p_PSASIDParameter, p_ProtocolIDParameter, p_IsFavouriteParameter, p_IsTimedParameter, p_IsFactoryPredefineParameter, p_IsNeedInjectionParameter, p_IsLicensedParameter, p_IsCardicScanParameter, p_IsPediatricParameter, p_CreateDateParameter, p_CreateByParameter, p_UpdateDateParameter, p_UpdateByParameter);
        }
 
        #endregion

3.调用存储过程

其中protocolDB是一个entity对象，sp_protocol_Udpate是存储过程映射过来的方法名。

 protocolDB.sp_protocol_Update(
                                temp.ProtocolNodeUID, 
                                temp.VersionUID,
                                temp.ParentProtocolNodeUID,
                                Convert.ToSByte(temp.IsDefaultSelected),
                                temp.SequenceOrder,
                                temp.NodeType,
                                temp.NodeKey,
                                temp.NodeLabel,
                                temp.ExamedBodyPart,
                                temp.ScanTypeSupported,
                                temp.PatientPosition,
                                temp.PrivateProtocolFilePath,
                                temp.Description,
                                temp.LeftRightFlag,
                                temp.AgeGroup,
                                temp.FileVersionNumber,
                                temp.ExampleFigurePath,
                                temp.PatientTypeID,
                                temp.ProcedureID,
                                temp.PSASID,
                                temp.ProtocolID,
                                Convert.ToSByte(temp.IsFavourite),
                                Convert.ToSByte(temp.IsTimed),
                                Convert.ToSByte(temp.IsFactoryPredefine),
                                Convert.ToSByte(temp.IsNeedInjection),
                                Convert.ToSByte(temp.IsLicensed),
                                Convert.ToSByte(temp.IsCardicScan),
                                Convert.ToSByte(temp.IsPediatric),
                                temp.CreateDate,
                                temp.CreateBy,
                                temp.UpdateDate, 
                                temp.UpdateBy
                        );

4. 注意事项

1）在调用存储过程方法时，有可能跑出一个异常：The specified FunctionImport is not mapped to a store function and cannot be executed.

解决办法，打开Model view, 选择对应的Function Import，然后右击delete，再次右击Add，填入方法名。

http://scottsdalewebstudio.com/blog/functionimport-is-not-mapped-to-a-store-function-error/

2）mysql的tinyint(1)数据类型在EF的存储过程参数中被映射为sbyte，但是EF中的对象却映射为bool。 因此，在调用EF的存储过程方法时，需要转换。

其中temp是个表对象，IsPediatric是映射过来的bool变量，通过Convert.ToSByte完美转换。

Convert.ToSByte(temp.IsPediatric)

3）Mysql的存储过程示例，注意delimiter关键字。

下面是一个使用OUT参数的简单的存储程序的例子。例子为，在 程序被定义的时候，用mysql客户端delimiter命令来把语句定界符从 ;变为//。这就允许用在 程序体中的;定界符被传递到服务器而不是被mysql自己来解释。
mysql> delimiter //
 
mysql> CREATE PROCEDURE simpleproc (OUT param1 INT)
    -> BEGIN
    ->   SELECT COUNT(*) INTO param1 FROM t;
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)
 
mysql> delimiter ;

posted @ 2013-07-03 16:48 muzizongheng 阅读(351) 评论(0) 编辑收藏举报

刷新页面返回顶部

利用存储过程来提高数据库的更新问题

公告