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();
}//刷新
}