| SqlServer存储过程结构规范范 |
|
|
CREATE PROCEDURE dbo.p_my_DocumentAdd | |
(参考储存过程与类生成工具）
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