封装数据操纵语句

封装类
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 System.Data.SqlClient;

namespace 封装数据库数据操纵数据
{
    /// <summary>
    /// 数据操纵语句的封装
    /// </summary>
    public class SQLHelp
    {
        //获取数据库连接语句
        private static string constr = ConfigurationManager.ConnectionStrings["SQLDBConnectionStr"].ConnectionString;
        private static SqlConnection con = null;
        private static SqlCommand cmd = null;

        /// <summary>
        /// ExectueNoQuery
        /// </summary>
        /// <param name="sqlstr">SQL语句</param>
        /// <param name="parameters">参数</param>
        /// <returns></returns>
        public static int ExectueNoQuery(string sqlstr, params SqlParameter[] parameters)
        {
            using (con = new SqlConnection(constr))
            {
                con.Open();
                cmd = con.CreateCommand();
                cmd.CommandText = sqlstr;
                foreach (SqlParameter parameter in parameters)
                {
                    cmd.Parameters.Add(parameter);
                }
                return cmd.ExecuteNonQuery();

            }
        }

        /// <summary>
        /// ExecuteScalar
        /// </summary>
        /// <param name="sqlstr"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static object ExecuteScalar(string sqlstr, params SqlParameter[] parameters)
        {
            using (con = new SqlConnection(constr))
            {
                con.Open();
                cmd = con.CreateCommand();
                cmd.CommandText = sqlstr;
                foreach (SqlParameter paramter in parameters)
                {
                    cmd.Parameters.Add(paramter);
                }
                return cmd.ExecuteScalar();
            }
        }

        public static SqlDataReader ExecuteReader(string sqlstr, params SqlParameter[] parameters)
        {
            using (con = new SqlConnection(constr))
            {
                con.Open();
                cmd = con.CreateCommand();
                cmd.CommandText = sqlstr;
                foreach (SqlParameter parameter in parameters)
                {
                    cmd.Parameters.Add(parameter);
                }
                return cmd.ExecuteReader();
            }
        }

        public static DataTable GetDateSet(string sqlstr, params SqlParameter[] parameters)
        {
            using (con = new SqlConnection(constr))
            {
                con.Open();
                cmd = con.CreateCommand();
                cmd.CommandText = sqlstr;
                foreach (SqlParameter parameter in parameters)
                {
                    cmd.Parameters.Add(parameter);
                }
                DataSet ds = new DataSet();
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                adapter.Fill(ds);
                return ds.Tables[0];
            }
        }


    }
}

调用

using System;
using System.Data;
using System.Configuration;
using System.Collections;
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 System.Data.SqlClient;

namespace 封装数据库数据操纵数据
{
    public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void btnEnq_Click(object sender, EventArgs e)
        {

            int i = SQLHelp.ExectueNoQuery("insert into T_PersonTest  (FUserName,FPassWord,FErrorTimes) values (@FUserName,@FPassWord,@FErrorTimes)", new SqlParameter("FUserName", "shinyhz"), new SqlParameter("FPassWord", "888"), new SqlParameter("FErrorTimes", "0"));

            ClientMessage(i.ToString());
        }

        /// <summary>
        /// 页面弹出框
        /// </summary>
        /// <param name="message"></param>
        protected void ClientMessage(string message)
        {
            Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script>alert('" + message + "')</script>");
        }

        protected void btnEs_Click(object sender, EventArgs e)
        {

            string i = Convert.ToString(SQLHelp.ExecuteScalar("select count(*) from t_persontest"));
            ClientMessage(i);
        }

        protected void btnEr_Click(object sender, EventArgs e)
        {
            SqlDataReader dr = SQLHelp.ExecuteReader("select * from t_persontest where FUserName=@FUserName", new SqlParameter("FUserName", "admin"));
            if (dr.Read())
            {
                ClientMessage("有数据");
            }
        }

        protected void btnDs_Click(object sender, EventArgs e)
        {
            DataTable dt = SQLHelp.GetDateSet("select * from t_persontest");
            if (dt.Rows.Count <= 0)
            {
                ClientMessage("没有数据");
            }
            else
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DataRow dr = dt.Rows[i];
                    ClientMessage(dr["FUserName"].ToString());
                }
            }
        }

        /// <summary>
        /// 登录
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void btnSumbit_Click(object sender, EventArgs e)
        {
            DataTable dt = SQLHelp.GetDateSet("select * from t_persontest where FUserName=@FUserName", new SqlParameter("FUserName", txtUserName.Text));
            if (dt.Rows.Count <= 0)
            {
                ClientMessage("无此帐号!");
            }
            else
            {
               int errorTimes= Convert.ToInt32( dt.Rows[0]["FErrorTimes"]);
               if (errorTimes >= 3)
               {
                   ClientMessage("帐号已被锁定！");
               }
               else
               {
                   //int passWord = Convert.ToInt32( dt.Rows[0]["FPassWord"]);
                   string passWord=dt.Rows[0]["FPassWord"].ToString();
                   if (txtPassWord.Text.Trim() == passWord)
                   {
                       SQLHelp.ExectueNoQuery("update t_persontest set FErrorTimes=@FErrorTimes where FUserName=@FUserName", new SqlParameter("FErrorTimes", "0"), new SqlParameter("FUserName", txtUserName.Text));
                       ClientMessage("登录成功");
                   }
                   else
                   {
                       SQLHelp.ExectueNoQuery("update t_persontest set FErrorTimes=FErrorTimes+1 where FUserName=@FUserName", new SqlParameter("FUserName", txtUserName.Text));
                       ClientMessage("密码错误");
                   }
               }
            }
        }
    }
}