.net 中存储过程的使用

这段时间做了一个小网站，代码中设计到了存储过程的使用和MD5加密，保留一点代码这里以后“复用”，呵呵！

配置文件加入：
<configuration>
 <appSettings>
  <add key="SQLCONNECTIONSTRING" value=" server = (local); uid = sa; pwd = ; database = db"></add>
 </appSettings>
<connectionStrings/>

//数据库连接类：
DataBase.cs
  public class DataBase : IDisposable
    {
        public DataBase()
        {
            //
            // TODO: 在此处添加构造函数逻辑
            //
        }

        //连接数据源
        public static SqlConnection conn =new SqlConnection(
                    ConfigurationSettings.AppSettings["SQLCONNECTIONSTRING"].ToString());

        /// <summary>
        /// 打开数据库连接
        /// </summary>
        public void Open()
        {
            //设置数据库连接字符串
            if (conn == null)
            {
                conn = new SqlConnection(
                    ConfigurationSettings.AppSettings["SQLCONNECTIONSTRING"].ToString());
            }

            //打开数据库连接
            if (conn.State == ConnectionState.Closed)
            {
                try
                {
                    conn.Open();
                }
                catch (Exception error)
                {
                    throw new Exception(error.Message);
                }
            }
        }

        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        public void Close()
        {
            //判断连接状态是否打开
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
            }
        }

        /// <summary>
        /// 释放资源
        /// </summary>
        public void Dispose()
        {
            //确认连接是否关闭
            if (conn != null)
            {
                conn.Dispose();
                conn = null;
            }
        }
 
 /// <summary>
        /// 用户加密函数
        /// </summary>
        public static String Encrypt(string password)
        {
            Byte[] clearBytes = new UnicodeEncoding().GetBytes(password);
            Byte[] hashedBytes = ((HashAlgorithm)CryptoConfig.CreateFromName("MD5")).ComputeHash(clearBytes);

            return BitConverter.ToString(hashedBytes);
        }

   }

1.带有输出参数的存储过程：
create proc sp_AddNewUser  ----添加新用户
(
 @name varchar(50),
 @pwd varchar(255),
 @remarks nvarchar(250),
 @returns int output
)
as
begin tran
insert into UserInfo(userName, userPwd,downLoadTimes,remarks)
values(@name, @pwd,0, @remarks)

if(@@ERROR > 0)
 begin
  set @returns = -1   ----添加失败
  rollback
 end
else
 begin
  set @returns = 0    ----添加成功
  commit
 end
go

        /// <summary>
        ///判断管理添加新用户是否成功
        /// </summary>
        /// <param name="username">用户名</param>
        /// <param name="userpwd">用户密码</param>
        /// <param name="userremarks">用户备注</param>
        /// <returns>TRUE:添加成功 FALSE:添加失败</returns>
        public bool AddUser(string username, string userpwd, string userremarks)
        {
            bool blAdduser = false;//存储返回值

            DataBase db = new DataBase();
            userpwd = DataBase.Encrypt(userpwd); //加密后的密码

            db.Open();//打开数据库连接

            //将用户信息添加到数据库
            SqlCommand sqlcmd = new SqlCommand();
            sqlcmd.Connection = DataBase.conn;
            sqlcmd.CommandType = CommandType.StoredProcedure;
            sqlcmd.CommandText = "sp_AddNewUser";

            //添加存储过程需要的参数
            SqlParameter sqlparam;
            sqlparam = new SqlParameter("@name", SqlDbType.VarChar, 50);
            sqlparam.Direction = ParameterDirection.Input;
            sqlparam.Value = username;
            sqlcmd.Parameters.Add(sqlparam);

            sqlparam = new SqlParameter("@pwd", SqlDbType.VarChar, 255);
            sqlparam.Direction = ParameterDirection.Input;
            sqlparam.Value = userpwd;
            sqlcmd.Parameters.Add(sqlparam);

            sqlparam = new SqlParameter("@remarks", SqlDbType.NVarChar, 250);
            sqlparam.Direction = ParameterDirection.Input;
            sqlparam.Value = userremarks;
            sqlcmd.Parameters.Add(sqlparam);

            sqlparam = new SqlParameter("@returns", SqlDbType.Int, 4);
            sqlparam.Direction = ParameterDirection.Output;
            sqlcmd.Parameters.Add(sqlparam);

            //执行存储过程将新用户插入到数据库
            try
            {
                sqlcmd.ExecuteNonQuery();

                int returntemp = Convert.ToInt16(sqlcmd.Parameters["@returns"].Value);
                if (returntemp < 0)
                {
                    //添加失败   
                    blAdduser = false;
                }
                else if (returntemp == 0)
                {
                    //添加成功
                    blAdduser = true;

                }

            }
            catch (Exception ex)
            {
                throw new Exception("访问数据库出错!"
                        + ex.Message);
            }
            finally
            {
                //关闭数据库连接并释放资源
                db.Close();
                db.Dispose();
            }
            return blAdduser;
        }

2.带有返回值的存储过程
create proc sp_deleteUser ---删除用户
(
 @name varchar(50)
)
as

declare @returns int ---返回值

begin tran
delete
from UserInfo
where userName = @name
if(@@Error > 0)
 begin
   set @returns = -1   ---删除失败
   rollback
 end
else
 begin
   set @returns = 0    ---删除成功
   commit
 end
return @returns
go

        /// <summary>
        /// 删除用户
        /// </summary>
        /// <param name="username">用户名</param>
        /// <returns>执行成功：TURE 否则：FALSE</returns>
        public bool DeleteUser(string username)
        {
            DataBase db = new DataBase();
            bool bldelete = false;

            db.Open();//打开数据库连接

            //准备访问数据库
            SqlCommand sqlcmd = new SqlCommand();
            sqlcmd.Connection = DataBase.conn;
            sqlcmd.CommandType = CommandType.StoredProcedure;
            sqlcmd.CommandText = "sp_DeleteUser";

            //设置存储过程参数
            SqlParameter sqlparam;
            sqlparam = new SqlParameter("@name", SqlDbType.VarChar, 50);
            sqlparam.Direction = ParameterDirection.Input;
            sqlparam.Value = username;
            sqlcmd.Parameters.Add(sqlparam);

            sqlparam = new SqlParameter("@returns", SqlDbType.Int, 4);
            sqlparam.Direction = ParameterDirection.ReturnValue;
            sqlcmd.Parameters.Add(sqlparam);

            //执行存储过程从数据库删除用户
            try
            {
                sqlcmd.ExecuteNonQuery();

                //得到存储过程返回值
                int returns = Convert.ToInt16(sqlcmd.Parameters["@returns"].Value);

                if (returns < 0)
                {
                    bldelete = false;
                }
                else
                {
                    bldelete = true;
                }

            }
            catch (Exception ex)
            {
                //显示错误信息
                throw new Exception("访问数据库出错!" + ex.Message);
            }
            finally
            {
                db.Close();
                db.Dispose();
            }

            return bldelete;
        }