|
数据库SQL脚本:
USE Gamedb
Go
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME = \'ACCOUNTId\')
DROP TABLE ACCOUNTID
GO
CREATE TABLE ACCOUNTID
(
UID INT NOT NULL,
UNAME VARCHAR(30) NOT NULL,
UPASSWORD VARCHAR(30) NOT NULL,
USEX CHAR(5) NOT NULL,
UEMAIL VARCHAR(30) NOT NULL,
UREGDATE DATETIME NOT NULL
)
GO
ALTER TABLE ACCOUNTID ADD
CONSTRAINT PK_ACCOUNTID PRIMARY KEY NONCLUSTERED(UID),
CONSTRAINT DF_ACCOUNTID DEFAULT(GETDATE()) FOR UREGDATE,
CONSTRAINT CK_ACCOUNTID CHECK(USEX =\'男\' OR USEX=\'女\')
GO
/*--数据存储过程--*/
USE Gamedb
GO
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME = \'PROC_ACCOUNTID_REGUSER\')
DROP PROCEDURE PROC_ACCOUNTID_REGUSER
GO
CREATE PROCEDURE PROC_ACCOUNTID_REGUSER
@Msg VARCHAR(100) OUTPUT,
@NAME VARCHAR(30),
@PASSWD VARCHAR(30),
@SEX CHAR(5),
@EMAIL VARCHAR(30)
AS
DECLARE @REINT INT
SELECT @REINT=COUNT(UNAME) FROM ACCOUNTID WHERE UNAME=@NAME
IF @REINT>0
BEGIN
SELECT @Msg=\'帐号已存在!\'
RETURN
END
BEGIN TRANSACTION
INSERT INTO ACCOUNTID(UNAME,UPASSWORD,USEX,UEMAIL) VALUES(@NAME,@PASSWD,@SEX,@EMAIL)
IF @@ERROR<>0
BEGIN
ROLLBACK TRANSACTION
SELECT @Msg=\'创建帐号失败!\'
RETURN
END
ELSE
BEGIN
COMMIT TRANSACTION
SELECT @Msg=\'创建帐号成功!\'
RETURN
END
GO
/*--测试存储过程--*/
DECLARE @MSG VARCHAR(100)
EXEC PROC_ACCOUNTID_REGUSER @MSG OUTPUT,@NAME=\'HERO\',@PASSWD=\'123\',@SEX=\'男\',@EMAIL=\'1234\'
SELECT @MSG
GO
C#代码操作取返回值
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data.Sql;
using System.Data.SqlTypes;
namespace GameClient
{
public class Connectdb
{
public SqlConnection Conndb()
{
string connstr = \"ersist Security Info=false;Network Library=dbmssocn;Data Source=(local);Initial Catalog=Gamedb;User Id=saassword=sa密码\";
SqlConnection conn = new SqlConnection(connstr);
return conn;
}
public string RegId(string name,string pwd,string sex,string eml)
{
string str=null;
Connectdb con = new Connectdb();
SqlConnection conn=con.Conndb();
try
{
//调用带参和有输出值的SQL存储过程
SqlCommand cmd = new SqlCommand(\"ROC_ACCOUNTID_REGUSER\", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
SqlParameter spt = new SqlParameter(\"@Msg\", System.Data.SqlDbType.VarChar, 100);
spt.Direction = System.Data.ParameterDirection.Output;
cmd.Parameters.Add(spt);
SqlParameter spt1 = new SqlParameter(\"@NAME\", System.Data.SqlDbType.VarChar, 30);
spt1.Value = name;
cmd.Parameters.Add(spt1);
SqlParameter spt2 = new SqlParameter(\"@PASSWD\", System.Data.SqlDbType.VarChar, 30);
spt2.Value = pwd;
cmd.Parameters.Add(spt2);
SqlParameter spt3 = new SqlParameter(\"@SEX\", System.Data.SqlDbType.Char, 5);
spt3.Value = sex;
cmd.Parameters.Add(spt3);
SqlParameter spt4 = new SqlParameter(\"@EMAIL\", System.Data.SqlDbType.VarChar, 30);
spt4.Value = eml;
cmd.Parameters.Add(spt4);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
str = (string)(cmd.Parameters[\"@Msg\"].Value);
return str;
}
catch(SqlException e)
{
Console.WriteLine(e.Message);
return \"\"+e.Message;
}
}
}
}
以上是本人初学时写的代码。希望对初学者了解 存储过程的操作有所帮助 |
|