加载中…
个人资料
微博
  • 博客等级：
  • 博客积分：
  • 博客访问：
  • 关注人气：
  • 获赠金笔：0支
  • 赠出金笔：0支
  • 荣誉徽章：
正文 字体大小：大 中 小

三种SQLHelper详细用法

(2012-02-13 15:02:19)
标签：

it

 分类： database

http://apps.hi.baidu.com/share/detail/5531961

http://topic.csdn.net/u/20080925/17/647c91c7-bfd6-402a-b6ba-409d8a4d8881.html

 

 

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using SQLHelper;
using System.Text;
using System.Data.SqlClient;
/// <summary>
/// UserInfo 的摘要说明
/// </summary>
namespace BLL
{
    public class UserInfo
    {
        #region 增
        /// <summary>
        /// 创建一个新用户
        /// </summary>
        /// <param name="username">用户名称</param>
        /// <param name="password">用户密码</param>
        /// <param name="email">电子邮件</param>
        /// <param name="question">密码问题</param>
        /// <param name="answer">问题答案</param>
        /// <returns></returns>
        public int createUser(string username, string password, int power, string email, string question, string answer)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("insert into UserInfo ([UserName],[Password],[Power],[Email],[Question],[Answer]) values ");
            sb.Append(" (@UserName,@Password,@Power,@Email,@Question,@Answer)");
            SqlParameter[] param =
                                    {
                                        DBHelper.GetParameter("@UserName",SqlDbType.NVarChar,50,"[UserName]",username),
                                        DBHelper.GetParameter("@Password",SqlDbType.NVarChar,50,"[Password]",password),
                                        DBHelper.GetParameter("@Power",SqlDbType.Int,4,"[Power]",power),
                                        DBHelper.GetParameter("@Email",SqlDbType.NVarChar,50,"[Email]",email),
                                        DBHelper.GetParameter("@Question",SqlDbType.NVarChar,50,"[Question]",question),
                                        DBHelper.GetParameter("@Answer",SqlDbType.NVarChar,50,"[Answer]",answer)
                                    };
            return DBHelper.ExecuteSql(sb.ToString(), param);
        }
        #endregion
        #region 删
        /// <summary>
        /// 删除一个用户
        /// </summary>
        /// <param name="userid">用户编号</param>
        /// <returns></returns>
        public int deleteUser(int userid)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("delete from UserInfo where [UserID]=@UserID");
            SqlParameter[] param = { DBHelper.GetParameter("@UserID", SqlDbType.Int, 4, "[UserID]", userid) };
            return DBHelper.ExecuteSql(sb.ToString(), param);
        }
        #endregion
        #region 改
        /// <summary>
        /// 修改密码
        /// </summary>
        /// <param name="userid">用户编号</param>
        /// <param name="newpassword">新密码</param>
        /// <returns></returns>
        public int updateUserPassword(int userid, string newpassword)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("update UserInfo set [Password]=@newpassword where [UserID]=@UserID");
            SqlParameter[] param =
                                   {
                                       DBHelper.GetParameter("@UserID", SqlDbType.Int, 4, "[UserID]", userid) ,
                                       DBHelper.GetParameter("@newpassword",SqlDbType.NVarChar,50,"[Password]",newpassword)
                                   };
            return DBHelper.ExecuteSql(sb.ToString(), param);
        }
        /// <summary>
        /// 修改用户资料
        /// </summary>
        /// <param name="userid">用户编号</param>
        /// <param name="password">用户密码</param>
        /// <param name="email">电子邮件</param>
        /// <param name="question">密码问题</param>
        /// <param name="answer">问题答案</param>
        /// <returns></returns>
        public int updateUser(int userid, string password, string email, string question, string answer)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("update UserInfo set [Password]=@Password,[Email]=@Email,[Question]=@Question,");
            sb.Append("[Answer]=@Answer where [UserID]=@UserID");
            SqlParameter[] param =
                                    {
                                        DBHelper.GetParameter("@UserID",SqlDbType.Int,4,"[UserID]",userid),
                                        DBHelper.GetParameter("@Password",SqlDbType.NVarChar,50,"[Password]",password),
                                        DBHelper.GetParameter("@Email",SqlDbType.NVarChar,50,"[Email]",email),
                                        DBHelper.GetParameter("@Question",SqlDbType.NVarChar,50,"[Question]",question),
                                        DBHelper.GetParameter("@Answer",SqlDbType.NVarChar,50,"[Answer]",answer)
                                    };
            return DBHelper.ExecuteSql(sb.ToString(), param);
        }
        #endregion
        #region 查
        /// <summary>
        /// 找回用户密码
        /// </summary>
        /// <param name="username">用户名</param>
        /// <param name="question">密码问题</param>
        /// <param name="answer">问题答案</param>
        /// <returns></returns>
        public string researchPassword(string username, string question, string answer)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("select [Password] from UserInfo where [UserName]=@UserName ");
            sb.Append("and [Question]=@Question and [Answer]=@Answer");
            SqlParameter[] param =
                                    {
                                        DBHelper.GetParameter("@UserName",SqlDbType.NVarChar,50,"[UserName]",username),
                                        DBHelper.GetParameter("@Question",SqlDbType.NVarChar,50,"[Question]",question),
                                        DBHelper.GetParameter("@Answer",SqlDbType.NVarChar,50,"[Answer]",answer)
                                    };
            DataTable table = DBHelper.ExecuteDt(sb.ToString(), param);
            if (table.Rows.Count > 0)
            {
                return table.Rows[0]["Password"].ToString();
            }
            else
            {
                return "";
            }
        }
        /// <summary>
        /// 获得一个用户资料
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public DataTable getUser(int userid)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("select [UserID],[UserName],[Password],[Power],[Email],[Question],[Answer] ");
            sb.Append("from UserInfo where [UserID]=@UserID");
            SqlParameter[] param = { DBHelper.GetParameter("@UserID", SqlDbType.Int, 4, "[UserID]", userid) };
            return DBHelper.ExecuteDt(sb.ToString(), param);
        }
        #endregion
        public UserInfo()
        {
            //
            // TODO: 在此处添加构造函数逻辑
            //
        }
    }
}


using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Text;
using System.Collections.Generic;
using System.Data.SqlClient;
using SQLHelper;
using Model;
/// <summary>
/// AdminSQL 的摘要说明
/// </summary>
namespace DAL
{
    public class AdminSQL
    {
        /// <summary>
        /// 添加
        /// </summary>
        /// <param name="admin"></param>
        /// <returns></returns>
        public int Add_Admin(Admin admin)
        {
             SQLHelper.SQLHelper sqlHelper = new SQLHelper.SQLHelper();
             SqlParameter[] ParamList ={
                 sqlHelper.CreateInParam("@UserName",SqlDbType.NVarChar,50,admin.UserName),
                 sqlHelper.CreateInParam("@Password",SqlDbType.NVarChar,50,admin.Password),
                 sqlHelper.CreateInParam("@LastLoginIP",SqlDbType.NVarChar,50,admin.LastLoginIP),
                 sqlHelper.CreateInParam("@LastLoginTime",SqlDbType.DateTime,8,admin.LastLoginTime)
             };
             try
             {
                 return(sqlHelper.RunProc("Add_Admin", ParamList));
             }
             catch (Exception ex)
             {
                 SystemError.CreateErrorLog(ex.Message);
                 throw new Exception(ex.Message, ex);
             }
        }
        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="admin"></param>
        public void Update_Admin(Admin admin)
        {
            SQLHelper.SQLHelper sqlHelper = new SQLHelper.SQLHelper();
            SqlParameter[] ParamList ={
                 sqlHelper.CreateInParam("@ID",SqlDbType.Int,4,admin.ID),
                 sqlHelper.CreateInParam("@UserName",SqlDbType.NVarChar,50,admin.UserName),
                 sqlHelper.CreateInParam("@Password",SqlDbType.NVarChar,50,admin.Password),
                 sqlHelper.CreateInParam("@LastLoginIP",SqlDbType.NVarChar,50,admin.LastLoginIP),
                 sqlHelper.CreateInParam("@LastLoginTime",SqlDbType.DateTime,8,admin.LastLoginTime)
             };
            try
            {
                sqlHelper.RunProc("Update_Admin", ParamList);
            }
            catch (Exception ex)
            {
                SystemError.CreateErrorLog(ex.Message);
                throw new Exception(ex.Message, ex);
            }
        }
        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="nID"></param>
        public void Delete_Admin(int nID)
        {
             SQLHelper.SQLHelper sqlHelper = new SQLHelper.SQLHelper();
             SqlParameter[] ParamList ={ sqlHelper.CreateInParam("@ID",SqlDbType.Int,4,nID)};
             try
             {
                 sqlHelper.RunProc("Delete_Admin", ParamList);
             }
             catch (Exception ex)
             {
                 SystemError.CreateErrorLog(ex.Message);
                 throw new Exception(ex.Message, ex);
             }
        }
        /// <summary>
        /// 查看全部,返回包含所有记录.
        /// </summary>
        /// <returns></returns>
        public List<Admin> Get_Admin()
        {
            SQLHelper.SQLHelper sqlHelper = new SQLHelper.SQLHelper();
            SqlDataReader rec = null;
            try
            {
                sqlHelper.RunProc("Get_Admin", out rec);
            }
            catch (Exception ex)
            {
                SystemError.CreateErrorLog(ex.Message);
                throw new Exception(ex.Message, ex);
            }
            List<Admin> List_Admin = new List<Admin>();
            while (rec.Read())
            {
                Admin admin = new Admin();
                admin.ID = Int32.Parse(rec["ID"].ToString());
                admin.UserName = rec["UserName"].ToString();
                admin.Password = rec["Password"].ToString();
                admin.LastLoginIP = rec["LastLoginIP"].ToString();
                admin.LastLoginTime = DateTime.Parse(rec["LastLoginTime"].ToString());
                List_Admin.Add(admin);
                admin = null;
            }
            rec.Close();
            return List_Admin;
        }
        /// <summary>
        /// 查看单个,输入一个用户名,返回一个包含全部信息的类.
        /// </summary>
        /// <param name="nID"></param>
        /// <returns></returns>
        public Admin Get_SingAdmin(string sUserName)
        {
            SQLHelper.SQLHelper sqlHelper = new SQLHelper.SQLHelper();
            //参数列表                  //SqlHelper的创建参数方法(参数名,对应字段;参数类型;参数值)
            SqlParameter[] ParamList ={ sqlHelper.CreateInParam("@UserName", SqlDbType.NVarChar,50,sUserName) };
            SqlDataReader rec = null;
            try
            {
                //执行存储过程(存储过程名,参数列表,输出参数)
                sqlHelper.RunProc("Get_SingAdmin", ParamList, out rec);
            }
            catch (Exception ex)
            {
                SystemError.CreateErrorLog(ex.Message);
                throw new Exception(ex.Message, ex);
            }
            //Model,一个类对应数据库中一个表
            Admin admin = new Admin();
            while (rec.Read())
            {
                admin.ID = Int32.Parse(rec["ID"].ToString());//int类型转换
                admin.UserName = rec["UserName"].ToString();
                admin.Password = rec["Password"].ToString();
                admin.LastLoginIP = rec["LastLoginIP"].ToString();
                admin.LastLoginTime = DateTime.Parse(rec["LastLoginTime"].ToString());
            }
            rec.Close();
            return admin;
        }
        /// <summary>
        /// 用户是否存在
        /// </summary>
        /// <param name="sUserName"></param>
        /// <returns></returns>
        public bool Login_Admin(string sUserName)
        {
            SQLHelper.SQLHelper sqlHelper = new SQLHelper.SQLHelper();
            SqlParameter[] ParamList ={ sqlHelper.CreateInParam("@UserName", SqlDbType.NVarChar, 50, sUserName) };
            SqlDataReader rec = null;
            try
            {
                sqlHelper.RunProc("Login_Admin", ParamList, out rec);
            }
            catch (Exception ex)
            {
                SystemError.CreateErrorLog(ex.Message);
                throw new Exception(ex.Message, ex);
            }
            bool Bool = false;
            while (rec.Read())
            {
                if (sUserName == rec["UserName"].ToString())
                {
                    Bool = true;
                }
            }
            rec.Close();
            return Bool;
        }
    }
}

 

 

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using SQLConstructor.DAL;
using System.Collections;
/// <summary>
/// Test 的摘要说明
/// </summary>
namespace SQLConstructor.BLL
{
    public class Test
    {
        #region 私有成员
        private int _Id;
        private string _Name;
        private string _Pwd;
        private string _Title;
        private string _Content;
        private DateTime _TTime;
        private bool _Checkout;
        private float _Price;
        #endregion
        #region 属性
        public int Id
        {
            get
            {
                return this._Id;
            }
            set
            {
                this._Id = value;
            }
        }
        #endregion
        public Test()
        {
            //
            // TODO: 在此处添加构造函数逻辑
            //
        }
        #region 方法
        /// <summary>
        /// 取得全部
        /// </summary>
        public DataTable getAllUser()
        {
            string sql = "";
            sql = "select * from test";
            Database db = new Database();
            return db.GetDataTable(sql);
        }
        /// <summary>
        /// 取得信息
        /// </summary>
        public DataRow getUser(int Id)
        {
            string sql = "";
            sql = "select * from test where Id = '" + Id + "'";
            Database db = new Database();
            return db.GetDataRow(sql);
        }
        /// <summary>
        /// 插入记录
        /// </summary>
        /// <param name="userinfo">信息表</param>
        /// <returns></returns>
        public int createUser(Hashtable userinfo)
        {
            Database db = new Database();        //实例化一个Database类
            return db.Insert("[test]", userinfo);    //利用Database类的Inser方法，插入数据
        }
        #endregion
    }
}


喜欢

0

阅读┊ 收藏 ┊ 喜欢▼ ┊打印┊举报/Report
  

新浪BLOG意见反馈留言板　欢迎批评指正

新浪简介 | About Sina | 广告服务 | 联系我们 | 招聘信息 | 网站律师 | SINA English | 产品答疑

Copyright © 1996 - 2022 SINA Corporation, All Rights Reserved

新浪公司 版权所有