ASP.NET 使用类对数据库进行增删改查操作

这是sqlHelper.cs类,类内里封装了方法

using System;

using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;


/// <summary>
///sqlHelper 的摘要说明
/// </summary>
public class sqlHelper
{
public sqlHelper()
{
//
//TODO: 在此处添加构造函数逻辑
//
}
    public static readonly string sqlstr=ConfigurationManager.ConnectionStrings[“sqlcnn”].ConnectionString;
    /// <summary>
    /// 获取结果集的第一行第一列的结果
    /// </summary>
    /// <param name=”sqlText”>要执行的检索语句</param>
    /// <param name=”paramss”>检索语句中的参数列表</param>
    /// <returns></returns>
    public static object ExecuteScalar(string sqlText,params SqlParameter[] paramss)
    {
        using (SqlConnection sqlcnn=new SqlConnection(sqlstr))
        {
            using (SqlCommand sqlcmm=sqlcnn.CreateCommand())
            {
                sqlcmm.CommandText = sqlText;
                FillParam(paramss, sqlcmm);
                sqlcnn.Open();
                return sqlcmm.ExecuteScalar();
            }
        }
    }//返回查找的第一行第一列
    /// <summary>
    /// 填充Command对象的参数列表
    /// </summary>
    /// <param name=”paramss”>参数列表</param>
    /// <param name=”sqlcmm”>command对象</param>
    private static void FillParam(SqlParameter[] paramss, SqlCommand sqlcmm)
    {
        foreach (SqlParameter param in paramss)
        {
            sqlcmm.Parameters.Add(param);
        }
    }//遍历参数
    /// <summary>
    /// 执行对数据库的增、删、改操作,并返回数据库中受影响的行数
    /// </summary>
    /// <param name=”sqlText”>要执行的insert、update、delete语句</param>
    /// <param name=”paramss”>要执行的语句中的参数列表</param>
    /// <returns></returns>
    public static int ExecuteNonquery(string sqlText,params SqlParameter[] paramss)
    {
        using (SqlConnection sqlcnn=new SqlConnection(sqlstr))
        {
            using (SqlCommand sqlcmm=sqlcnn.CreateCommand())
            {
                sqlcmm.CommandText = sqlText;
                FillParam(paramss,sqlcmm);
                sqlcnn.Open();
                return sqlcmm.ExecuteNonQuery();
            }
        }
    }//返回sqlcmm.ExcuteQuery()
    /// <summary>
    /// 获取检索结果集,返回DataTable
    /// </summary>
    /// <param name=”sqlText”>要执行的检索语句</param>
    /// <param name=”paramss”>检索语句中的参数列表</param>
    /// <returns></returns>
    public static DataTable ExecteTable(string sqlText, params SqlParameter[] paramss)
    {
        using (SqlConnection sqlcnn=new SqlConnection(sqlstr))
        {
            using (SqlCommand sqlcmm=sqlcnn.CreateCommand())
            {
                sqlcmm.CommandText = sqlText;
                FillParam(paramss,sqlcmm);
                sqlcnn.Open();
                using (SqlDataReader reader=sqlcmm.ExecuteReader())
                {
                    DataTable dt = new DataTable();
                    dt.Load(reader);
                    return dt;
                }
            }
        }
    }//返回DataTable

}

下面是增删改查操作:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;


public partial class Caozuo : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {


    }
    protected void btnAdd_Click(object sender, EventArgs e)
    {
        string sqlstr = “insert into Denglu(Sname,Spassword)values(@name,@password)”;
        int i = sqlHelper.ExecuteNonquery(sqlstr,new SqlParameter(“@name”,this.txtName.Text),new SqlParameter(“@password”,this.txtPassword.Text));
        if (i > 0)
        {
            ClientScript.RegisterClientScriptBlock(GetType(), “提示”, “<script>alert(‘添加成功!’)</script>”, false);
        }
        else
        {
            ClientScript.RegisterClientScriptBlock(GetType(),”提示”,”<script>alert(‘添加失败!’)</script>”,false);
        }
    }


    protected void btnDel_Click(object sender, EventArgs e)
    {
        string sqlstr = “delete from Denglu where Sname=@name”;
        int i = sqlHelper.ExecuteNonquery(sqlstr,new SqlParameter(“@name”,this.txtName.Text));
        if (i > 0)
        {
            ClientScript.RegisterClientScriptBlock(GetType(), “提示”, “<script>alert(‘删除成功!’)</script>”, false);
        }
        else
        {
            ClientScript.RegisterClientScriptBlock(GetType(), “提示”, “<script>alert(‘删除失败!’)</script>”, false);
        }
    }//删除
    protected void btnEdit_Click(object sender, EventArgs e)
    {
        string sqlstr = “update Denglu set Spassword=@password where Sname=@name”;
        int i = sqlHelper.ExecuteNonquery(sqlstr,new SqlParameter(“@name”,this.txtName.Text),new SqlParameter(“@password”,this.txtPassword.Text));
        if (i > 0)
        {
            ClientScript.RegisterClientScriptBlock(GetType(), “提示”, “<script>alert(‘修改成功!’)</script>”, false);
        }
        else
        {
            ClientScript.RegisterClientScriptBlock(GetType(), “提示”, “<script>alert(‘修改失败!’)</script>”, false);
        }
    }//更改
    protected void btnLook_Click(object sender, EventArgs e)
    {
        string sqlstr = “select Spassword from Denglu where Sname=@name”;
        DataTable dt = sqlHelper.ExecteTable(sqlstr,new SqlParameter(“@name”,this.txtName.Text.Trim()));
        this.txtPassword.Text = dt.Rows[0][0].ToString();
    }//查找
    protected void btnRefresh_Click(object sender, EventArgs e)
    {
        string sqlstr = “select * from Denglu”;
        DataTable dt = sqlHelper.ExecteTable(sqlstr);
        this.GridView1.DataSource = dt;
        this.GridView1.DataBind();
    }//刷新
}

展开阅读全文

没有更多推荐了,返回首页