SqlServer存储过程结构规范范

SqlServer存储过程结构规范范
 


本系统生成的SqlServer存储过程代码遵循以下规范 
A、表主键可以采用自动int类型，也可以用字符型作为主键char(10)，用字符型数据作为表主键的优点在于方便数据库，字符型主键生成可以通过本站提供的存储过程产生[查看此存储过程]；
B、对数据添加、更新、删除操作存储过程中采用事务管理来保证操作的完整性，对从数据库中读取数据操作不采用事务管理；
C、对每个存储过程中均有错误管理，在对数据库操作出错时向应用程序抛出错误；

以下是一个向数据库添加数据操作的存储过程：

CREATE PROCEDURE dbo.p_my_DocumentAdd
(
  @DocumentID AS char(10) out,
  @Title AS VarChar(100),
  @Comment AS Text,
  @Hits AS Int,
  @Type AS VarChar(20),
  @Gender AS TinyInt
)
AS
BEGIN
  BEGIN TRAN--开始事务
        --生成主键
  exec p_my_GetTableNextID 'Document','D',10,@DocumentID output  //没有定义主键时生成主键
        INSERT INTO Document (
        DocumentID,
        Title,
        Comment,
        Hits,
        Type,
        Gender)
    VALUES(
        @DocumentID,
        @Title,
        @Comment,
        @Hits,
        @Type,
        @Gender)
    --出错处理
    IF (@@error!=0)
        BEGIN
            RAISERROR 20000 'p_my_DocumentAdd: Cannot insert data into  p_my_DocumentAdd '--向应用程序抛出错误
            ROLLBACK TRAN--回滚事务
            Return (1)--设置返回值
        End
    COMMIT TRAN--如果没有错误则提交事务
END

(参考储存过程与类生成工具）


using System;

 /// <summary>
 /// CBase 的摘要说明。
 /// </summary>
 public class CBase
 {
  public  int  ISDebug =1;
  protected string strConnectString;
  protected string strLastError;
  protected int strRETURN_VALUE;
  
  public CBase()
  {
   //
   // TODO: 在此处添加构造函数逻辑
   //
   if (System.Web.HttpContext.Current.Application["ConnectString"]!=null)
   {
    strConnectString=System.Web.HttpContext.Current.Application["ConnectString"].ToString();
   }
  }

  public  string ConnectionString
  {
   set
   {
    if(value !=null)
     this.strConnectString=value;
   }
  }
  public string LastError
  {
   get{return strLastError;}
  }
  public void ErrorLog(string ErrorMessage,string FunctionName )
  {
  }
  public int RETURN_VALUE
  { 
   get{return strRETURN_VALUE;}
   set{strRETURN_VALUE=value;}
  }
 }


// ----------------------------------------------------
//This code was generated by a www.dbo.cn
//Class:CAuthorBase
//Copyright (c) <Copyright></Copyright>
//Description:
//
//对Table Author 的数据映射
//Author     : <Author></Author>
//Date       :2005-3-11 16:09:22
//----------------------------------------------------
//
using System;
public class CAuthorBase : CBase {
   
    private bool FGender;
   
    private DateTime FBirthday;
   
    private DateTime FCheckintime;
   
    private Byte FStatus;
   
    private String FEmail;
   
    private String FAuthorID;
   
    private String FName;
   
    private String FPassword;
   
    // 性别
    public bool Gender {
        get {
            return FGender;
        }
        set {
            FGender = value;
        }
    }
   
    // 生日
    public DateTime Birthday {
        get {
            return FBirthday;
        }
        set {
            FBirthday = value;
        }
    }
   
    // 添加时间
    public DateTime Checkintime {
        get {
            return FCheckintime;
        }
        set {
            FCheckintime = value;
        }
    }
   
    // 状态
    public Byte Status {
        get {
            return FStatus;
        }
        set {
            FStatus = value;
        }
    }
   
    // 邮箱
    public String Email {
        get {
            return FEmail;
        }
        set {
            FEmail = value;
        }
    }
   
    public String AuthorID {
        get {
            return FAuthorID;
        }
        set {
            FAuthorID = value;
        }
    }
   
    // 姓名
    public String Name {
        get {
            return FName;
        }
        set {
            FName = value;
        }
    }
   
    // 密码
    public String Password {
        get {
            return FPassword;
        }
        set {
            FPassword = value;
        }
    }
}

// ----------------------------------------------------
//This code was generated by a www.dbo.cn
//Class:CAuthor
//Copyright (c) <Copyright></Copyright>
//Description:
//
//对Table Author 的数据映射
//Author     : <Author></Author>
//Date       :2005-3-11 16:19:31
//----------------------------------------------------
//
using System.Data.SqlClient;
using System.Data;
using System.Collections;
using System;

public class CAuthor : CAuthorBase {
   
    // 构造函数
    public CAuthor() {
    }
   
    // 构造函数：参数为数据库连接字符串
    public CAuthor(String ConnectStringValue) {
        if ((ConnectStringValue != "")) {
            ConnectionString = ConnectStringValue;
        }
    }
   
    // 增加一条记录
//如果执行成功，返回 True ，如果失败，返回 False, 并把错误信息赋予 LastError.
//需要输入的参数：
//Gender
//Birthday
//AuthorID
//Email
//Name
//Password
//使用示范：
//CAuthor xAuthor= new CAuthor();
//xAuthor.Gender=Gender;
//xAuthor.Birthday=Birthday;
//xAuthor.AuthorID=AuthorID;
//xAuthor.Email=Email;
//xAuthor.Name=Name;
//xAuthor.Password=Password;
//if(xAuthor.Add())
//{
//   在这里执行正确时下一步的动作
//}
//else
//{
//  在这里处理错误时下一步的动作
//}
//
    public bool Add() {
        System.Data.SqlClient.SqlConnection conn;
        bool blnResult;
        if ((strConnectString  != "")) {
            conn = new System.Data.SqlClient.SqlConnection(strConnectString);
            try {
                conn.Open();
                System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("p_my_AuthorAdd", conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                System.Data.SqlClient.SqlParameter RETURN_VALUEParam = cmd.Parameters.Add("@RETURN_VALUE", System.Data.SqlDbType.Int, 4);
                RETURN_VALUEParam.Direction = System.Data.ParameterDirection.ReturnValue;
                System.Data.SqlClient.SqlParameter GenderParam = cmd.Parameters.Add("@Gender", System.Data.SqlDbType.Bit);
                GenderParam.Value = Gender;
                System.Data.SqlClient.SqlParameter BirthdayParam = cmd.Parameters.Add("@Birthday", System.Data.SqlDbType.DateTime);
                BirthdayParam.Value = Birthday;
                System.Data.SqlClient.SqlParameter AuthorIDParam = cmd.Parameters.Add("@AuthorID", System.Data.SqlDbType.VarChar, 20);
                AuthorIDParam.Value = AuthorID;
                System.Data.SqlClient.SqlParameter EmailParam = cmd.Parameters.Add("@Email", System.Data.SqlDbType.VarChar, 50);
                EmailParam.Value = Email;
                System.Data.SqlClient.SqlParameter NameParam = cmd.Parameters.Add("@Name", System.Data.SqlDbType.VarChar, 20);
                NameParam.Value = Name;
                System.Data.SqlClient.SqlParameter PasswordParam = cmd.Parameters.Add("@Password", System.Data.SqlDbType.VarChar, 20);
                PasswordParam.Value = Password;
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                blnResult = true;
            }
            catch (System.Data.SqlClient.SqlException ex) {
                blnResult = false;
                if ((ISDebug  == 0)) {
                    strLastError = ex.Message;
                    this.ErrorLog(ex.Message, "CAuthor/Add");
                }
                else {
                    throw new System.Exception((ex.Message + ex.StackTrace));
                }
            }
            catch (System.Exception ex) {
                blnResult = false;
                if ((ISDebug  == 0)) {
                    strLastError = ex.Message;
                    this.ErrorLog(ex.Message, "CAuthor/Add");
                }
                else {
                    throw new System.Exception((ex.Message + ex.StackTrace));
                }
            }
            finally {
                conn.Close();
            }
        }
        else {
            strLastError = "错误信息: 数据库连接字符串尚未赋值！";
            blnResult = false;
        }
        return blnResult;
    }
   
    // 更新记录
//如果执行成功，返回 True ，如果失败，返回 False, 并把错误信息赋予 LastError.
//需要输入的参数：
//Gender
//Birthday
//AuthorID
//Email
//Name
//Password
//使用示范：
//CAuthor xAuthor= new CAuthor();
//xAuthor.Gender=Gender;
//xAuthor.Birthday=Birthday;
//xAuthor.AuthorID=AuthorID;
//xAuthor.Email=Email;
//xAuthor.Name=Name;
//xAuthor.Password=Password;
//if(xAuthor.Update())
//{
//   在这里执行正确时下一步的动作
//}
//else
//{
//  在这里处理错误时下一步的动作
//}
//
    public bool Update() {
        System.Data.SqlClient.SqlConnection conn;
        bool blnResult;
        if ((strConnectString  != "")) {
            conn = new System.Data.SqlClient.SqlConnection(strConnectString);
            try {
                conn.Open();
                System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("p_my_AuthorUpdate", conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                System.Data.SqlClient.SqlParameter RETURN_VALUEParam = cmd.Parameters.Add("@RETURN_VALUE", System.Data.SqlDbType.Int, 4);
                RETURN_VALUEParam.Direction = System.Data.ParameterDirection.ReturnValue;
                System.Data.SqlClient.SqlParameter GenderParam = cmd.Parameters.Add("@Gender", System.Data.SqlDbType.Bit);
                GenderParam.Value = Gender;
                System.Data.SqlClient.SqlParameter BirthdayParam = cmd.Parameters.Add("@Birthday", System.Data.SqlDbType.DateTime);
                BirthdayParam.Value = Birthday;
                System.Data.SqlClient.SqlParameter AuthorIDParam = cmd.Parameters.Add("@AuthorID", System.Data.SqlDbType.VarChar, 20);
                AuthorIDParam.Value = AuthorID;
                System.Data.SqlClient.SqlParameter EmailParam = cmd.Parameters.Add("@Email", System.Data.SqlDbType.VarChar, 50);
                EmailParam.Value = Email;
                System.Data.SqlClient.SqlParameter NameParam = cmd.Parameters.Add("@Name", System.Data.SqlDbType.VarChar, 20);
                NameParam.Value = Name;
                System.Data.SqlClient.SqlParameter PasswordParam = cmd.Parameters.Add("@Password", System.Data.SqlDbType.VarChar, 20);
                PasswordParam.Value = Password;
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                blnResult = true;
            }
            catch (System.Data.SqlClient.SqlException ex) {
                blnResult = false;
                if ((ISDebug  == 0)) {
                    strLastError = ex.Message;
                    this.ErrorLog(ex.Message, "CAuthor/Update");
                }
                else {
                    throw new System.Exception((ex.Message + ex.StackTrace));
                }
            }
            catch (System.Exception ex) {
                blnResult = false;
                if ((ISDebug  == 0)) {
                    strLastError = ex.Message;
                    this.ErrorLog(ex.Message, "CAuthor/Update");
                }
                else {
                    throw new System.Exception((ex.Message + ex.StackTrace));
                }
            }
            finally {
                conn.Close();
            }
        }
        else {
            strLastError = "错误信息: 数据库连接字符串尚未赋值！";
            blnResult = false;
        }
        return blnResult;
    }
   
    // 删除记录
 //如果执行成功，返回 True ，如果失败，返回 False, 并把错误信息赋予 LastError.
 //需要输入的参数：
 //AuthorID
 //使用示范：
 //CAuthor xAuthor= new CAuthor();
 //xAuthor.AuthorID=AuthorID;
 //if(xAuthor.Delete())
 //{
 //   在这里执行正确时下一步的动作
 //}
 //else
 //{
 //  在这里处理错误时下一步的动作
 //}
 //
    public bool Delete() {
        System.Data.SqlClient.SqlConnection conn;
        bool blnResult;
        if ((strConnectString  != "")) {
            conn = new System.Data.SqlClient.SqlConnection(strConnectString);
            try {
                conn.Open();
                System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("p_my_AuthorDelete", conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                System.Data.SqlClient.SqlParameter RETURN_VALUEParam = cmd.Parameters.Add("@RETURN_VALUE", System.Data.SqlDbType.Int, 4);
                RETURN_VALUEParam.Direction = System.Data.ParameterDirection.ReturnValue;
                System.Data.SqlClient.SqlParameter AuthorIDParam = cmd.Parameters.Add("@AuthorID", System.Data.SqlDbType.VarChar, 20);
                AuthorIDParam.Value = AuthorID;
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                blnResult = true;
            }
            catch (System.Data.SqlClient.SqlException ex) {
                blnResult = false;
                if ((ISDebug  == 0)) {
                    strLastError = ex.Message;
                    this.ErrorLog(ex.Message, "CAuthor/Delete");
                }
                else {
                    throw new System.Exception((ex.Message + ex.StackTrace));
                }
            }
            catch (System.Exception ex) {
                blnResult = false;
                if ((ISDebug  == 0)) {
                    strLastError = ex.Message;
                    this.ErrorLog(ex.Message, "CAuthor/Delete");
                }
                else {
                    throw new System.Exception((ex.Message + ex.StackTrace));
                }
            }
            finally {
                conn.Close();
            }
        }
        else {
            strLastError = "错误信息: 数据库连接字符串尚未赋值！";
            blnResult = false;
        }
        return blnResult;
    }
   
    // 加载一条记录
//如果执行成功，返回 True 把对象的各属性赋值，如果失败，返回 False,并把错误信息赋予 LastError.
//需要输入的参数：
//AuthorID
//使用示范：
//CAuthor xAuthor= new CAuthor();
//xAuthor.AuthorID=AuthorID;
//if(xAuthor.Load())
//{
//   在这里执行正确时下一步的动作
//}
//else
//{
//  在这里处理错误时下一步的动作
//}
//
    public bool Load() {
        System.Data.SqlClient.SqlConnection conn;
        bool blnResult;
        System.Data.SqlClient.SqlDataReader reader;
        if ((strConnectString  != "")) {
            conn = new System.Data.SqlClient.SqlConnection(strConnectString);
            try {
                conn.Open();
                System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("p_my_AuthorLoad", conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                System.Data.SqlClient.SqlParameter RETURN_VALUEParam = cmd.Parameters.Add("@RETURN_VALUE", System.Data.SqlDbType.Int, 4);
                RETURN_VALUEParam.Direction = System.Data.ParameterDirection.ReturnValue;
                System.Data.SqlClient.SqlParameter AuthorIDParam = cmd.Parameters.Add("@AuthorID", System.Data.SqlDbType.VarChar, 20);
                AuthorIDParam.Value = AuthorID;
                reader = cmd.ExecuteReader();
                if (reader.Read()) {
                    Gender = ((bool)(reader["Gender"]));
                    Birthday = ((DateTime)(reader["Birthday"]));
                    Checkintime = ((DateTime)(reader["Checkintime"]));
                    Status = ((Byte)(reader["Status"]));
                    Email = ((String)(reader["Email"]));
                    AuthorID = ((String)(reader["AuthorID"]));
                    Name = ((String)(reader["Name"]));
                    Password = ((String)(reader["Password"]));
                    blnResult = true;
                }
                else {
                    blnResult = false;
                    strLastError = "无记录。";
                }
                cmd.Dispose();
            }
            catch (System.Data.SqlClient.SqlException ex) {
                blnResult = false;
                if ((ISDebug  == 0)) {
                    strLastError = ex.Message;
                    this.ErrorLog(ex.Message, "CAuthor/Load");
                }
                else {
                    throw new System.Exception((ex.Message + ex.StackTrace));
                }
            }
            catch (System.Exception ex) {
                blnResult = false;
                if ((ISDebug  == 0)) {
                    strLastError = ex.Message;
                    this.ErrorLog(ex.Message, "CAuthor/Load");
                }
                else {
                    throw new System.Exception((ex.Message + ex.StackTrace));
                }
            }
            finally {
                conn.Close();
            }
        }
        else {
            strLastError = "错误信息: 数据库连接字符串尚未赋值！";
            blnResult = false;
        }
        return blnResult;
    }
   
    // 读取列表
//如果执行成功，返回 CAuthorCollection集合对象 ，如果失败，返回 Nothing,并把错误信息赋予 LastError.
//需要输入的参数：
//使用示范：
//CAuthor xAuthor= new CAuthor();
//CAuthorCollection xList;
//xList=xAuthor.List();
//if(xList! = null)
//{
//   在这里执行正确时下一步的动作
//}
//else
//{
//  在这里处理错误时下一步的动作
//}
//
    public CAuthorCollection List() {
        System.Data.SqlClient.SqlConnection conn;
        System.Data.SqlClient.SqlDataReader reader;
        CAuthor xItem;
        CAuthorCollection xList;
        bool IsExist;
        if ((strConnectString  != "")) {
            conn = new System.Data.SqlClient.SqlConnection(strConnectString);
            try {
                conn.Open();
                System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("p_my_AuthorList", conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                System.Data.SqlClient.SqlParameter RETURN_VALUEParam = cmd.Parameters.Add("@RETURN_VALUE", System.Data.SqlDbType.Int, 4);
                RETURN_VALUEParam.Direction = System.Data.ParameterDirection.ReturnValue;
                xList = new CAuthorCollection();
                reader = cmd.ExecuteReader();
                for (IsExist = reader.Read(); IsExist; IsExist = reader.Read()) {
                    xItem = new CAuthor();
                    xItem.Gender = ((bool)(reader["Gender"]));
                    xItem.Birthday = ((DateTime)(reader["Birthday"]));
                    xItem.Checkintime = ((DateTime)(reader["Checkintime"]));
                    xItem.Status = ((Byte)(reader["Status"]));
                    xItem.Email = ((String)(reader["Email"]));
                    xItem.AuthorID = ((String)(reader["AuthorID"]));
                    xItem.Name = ((String)(reader["Name"]));
                    xItem.Password = ((String)(reader["Password"]));
                    xList.Add(xItem);      //加入对象,使用如：xList[i].Gender
                }
                cmd.Dispose();
            }
            catch (System.Data.SqlClient.SqlException ex) {
                xList = null;
                if ((ISDebug  == 0)) {
                    strLastError = ex.Message;
                    this.ErrorLog(ex.Message, "CAuthor/List");
                }
                else {
                    throw new System.Exception((ex.Message + ex.StackTrace));
                }
            }
            catch (System.Exception ex) {
                xList = null;
                if ((ISDebug  == 0)) {
                    strLastError = ex.Message;
                    this.ErrorLog(ex.Message, "CAuthor/List");
                }
                else {
                    throw new System.Exception((ex.Message + ex.StackTrace));
                }
            }
            finally {
                conn.Close();
            }
        }
        else {
            strLastError = "错误信息: 数据库连接字符串尚未赋值！";
            xList = null;
        }
        return xList;
    }
}
public class CAuthorCollection : CollectionBase {
   
    public CAuthor this[Int32 index] {
        get {
            return ((CAuthor)(List[index]));
        }
        set {
            List[index] = value;          //list表示集合实例本身
        }
    }
   
    public void Add(CAuthor item) {
        if ((item == null)) {
            throw new System.Exception("不能添加元素");
        }
        else {
            try {
                List.Add(item);
            }
            catch (NotSupportedException ex) {
                throw new System.Exception((ex.Message + ex.StackTrace));
            }
        }
    }
}

 
//web页
 Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        '请先将例子中的数据库在你的机器上进行还源，然后通过Global.asax配置数据库连接信息
        Dim xAuthor As New CAuthor '新建对象
        xAuthor.Name = "名称" '将属性赋值给对象
        xAuthor.Password = "密码"
        xAuthor.Birthday = "1999-1-1"
        xAuthor.AuthorID = "authorid"
        xAuthor.Email = "xxx@xx.com"
        If xAuthor.Add Then '调用方法
            Response.Write("添加成功")
        Else
            Response.Write(xAuthor.LastError)
        End If
    End Sub

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/zjz/p/203705.html