在asp.net中连接数据库进行数据库操作时,一般要4、5行代码以上。定义这个定义那个,打开然后又关闭,这样效率很低,且不易维护。这里把这些操作封装成一个类,每次调用完成数据库操作只需要两行代码。
在这个类之前,先写一个类:
然后开始写操作数据库的类:
在调用时,只需要两行代码,如:
ExecuteSQL newExecute = new ExecuteSQL();
DataTable dt = newExecute.Filldt(conn.myConn(), TextBox1.Text);
在这个类里只写了几种常用的方法,可以根据自己的需要增加修改——用了类,感觉~挺好的!
在这个类之前,先写一个类:
using
System;
using System.Data;
using System.Data.SqlClient;
/**/ /// <summary>
/// conn 数据库连接类
/// </summary>
public class conn
{
public conn()
{
//
// TODO: 在此处添加构造函数逻辑
//
}
/**//// <summary>
/// 静态方法返回一个SQL数据库连接
/// </summary>
/// <returns>返回一个SqlConnection对象</returns>
public static SqlConnection myConn()
{
SqlConnection SqlConn = new SqlConnection("user id=sa;password=123;Database =test;data source=jeff;");
return SqlConn;
}
}
这里只有一个静态方法,返回一个SqlConnection对象,连接数据库的语句在这边一次写下简单易修改。
using System.Data;
using System.Data.SqlClient;
/**/ /// <summary>
/// conn 数据库连接类
/// </summary>
public class conn
{
public conn()
{
//
// TODO: 在此处添加构造函数逻辑
//
}
/**//// <summary>
/// 静态方法返回一个SQL数据库连接
/// </summary>
/// <returns>返回一个SqlConnection对象</returns>
public static SqlConnection myConn()
{
SqlConnection SqlConn = new SqlConnection("user id=sa;password=123;Database =test;data source=jeff;");
return SqlConn;
}
}
然后开始写操作数据库的类:
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;
/**/ /// <summary>
/// ExecuteSQL 的摘要说明
/// </summary>
public class ExecuteSQL
{
public ExecuteSQL()
{
//
// TODO: 在此处添加构造函数逻辑
//
}
/**//// <summary>
/// 执行插入、删除、更新等不需要返回值的SQL语句
/// </summary>
/// <param name="SQL">用以执行的SQL语句</param>
/// <param name="Connection">数据库连接对象</param>
/// <returns>操作成功返回true,否则返回false</returns>
public bool NonQuery(string SQL,SqlConnection Connection)
{
try
{
Connection.Open();
SqlCommand myComm = new SqlCommand(SQL,Connection );
myComm.ExecuteNonQuery();
Connection.Close();
return true;
}
catch
{
return false;
}
finally
{
Connection.Dispose();
}
}
/**//// <summary>
/// 判断用户名密码是否正确
/// </summary>
/// <param name="Connection">数据库连接对象</param>
/// <param name="Name">用户名</param>
/// <param name="Password">密码</param>
/// <returns>用户名密码匹配时返回true,否则返回false</returns>
public bool ifExist(SqlConnection Connection,string Name,string Password)
{
try
{
Connection.Open();
String SQL = "SELECT * FROM tab_user WHERE user_name='" + Name + "' AND user_pwd='" + Password + "'";
SqlCommand myComm = new SqlCommand(SQL, Connection);
SqlDataReader myReader = myComm.ExecuteReader();
if (myReader.Read())
return true;
else
return false;
}
catch
{
return false;
}
finally
{
Connection.Close();
Connection.Dispose();
}
}
/**//// <summary>
/// 根据SQL语句判断查询结果是否为空
/// </summary>
/// <param name="Connection">数据库连接对象</param>
/// <param name="SQL">查询的SQL语句</param>
/// <returns>查询结果不为空时返回true,否则返回false</returns>
public bool ifExist(SqlConnection Connection, string SQL)
{
try
{
Connection.Open();
SqlCommand myComm = new SqlCommand(SQL, Connection);
SqlDataReader myReader = myComm.ExecuteReader();
if (myReader.Read())
return true;
else
return false;
}
catch
{
return false;
}
finally
{
Connection.Close();
Connection.Dispose();
}
}
/**//// <summary>
/// 根据SQL语句返回一个DataTable
/// </summary>
/// <param name="Connection">数据库连接对象</param>
/// <param name="SQL">执行的SQL语句</param>
/// <returns>返回一个DataTable</returns>
public DataTable Filldt(SqlConnection Connection, string SQL)
{
try
{
DataTable dt=new DataTable();
SqlDataAdapter myAdapter = new SqlDataAdapter(SQL, Connection);
myAdapter.Fill(dt);
return dt;
}
catch
{
return null;
}
finally
{
Connection.Close();
Connection.Dispose();
}
}
}
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;
/**/ /// <summary>
/// ExecuteSQL 的摘要说明
/// </summary>
public class ExecuteSQL
{
public ExecuteSQL()
{
//
// TODO: 在此处添加构造函数逻辑
//
}
/**//// <summary>
/// 执行插入、删除、更新等不需要返回值的SQL语句
/// </summary>
/// <param name="SQL">用以执行的SQL语句</param>
/// <param name="Connection">数据库连接对象</param>
/// <returns>操作成功返回true,否则返回false</returns>
public bool NonQuery(string SQL,SqlConnection Connection)
{
try
{
Connection.Open();
SqlCommand myComm = new SqlCommand(SQL,Connection );
myComm.ExecuteNonQuery();
Connection.Close();
return true;
}
catch
{
return false;
}
finally
{
Connection.Dispose();
}
}
/**//// <summary>
/// 判断用户名密码是否正确
/// </summary>
/// <param name="Connection">数据库连接对象</param>
/// <param name="Name">用户名</param>
/// <param name="Password">密码</param>
/// <returns>用户名密码匹配时返回true,否则返回false</returns>
public bool ifExist(SqlConnection Connection,string Name,string Password)
{
try
{
Connection.Open();
String SQL = "SELECT * FROM tab_user WHERE user_name='" + Name + "' AND user_pwd='" + Password + "'";
SqlCommand myComm = new SqlCommand(SQL, Connection);
SqlDataReader myReader = myComm.ExecuteReader();
if (myReader.Read())
return true;
else
return false;
}
catch
{
return false;
}
finally
{
Connection.Close();
Connection.Dispose();
}
}
/**//// <summary>
/// 根据SQL语句判断查询结果是否为空
/// </summary>
/// <param name="Connection">数据库连接对象</param>
/// <param name="SQL">查询的SQL语句</param>
/// <returns>查询结果不为空时返回true,否则返回false</returns>
public bool ifExist(SqlConnection Connection, string SQL)
{
try
{
Connection.Open();
SqlCommand myComm = new SqlCommand(SQL, Connection);
SqlDataReader myReader = myComm.ExecuteReader();
if (myReader.Read())
return true;
else
return false;
}
catch
{
return false;
}
finally
{
Connection.Close();
Connection.Dispose();
}
}
/**//// <summary>
/// 根据SQL语句返回一个DataTable
/// </summary>
/// <param name="Connection">数据库连接对象</param>
/// <param name="SQL">执行的SQL语句</param>
/// <returns>返回一个DataTable</returns>
public DataTable Filldt(SqlConnection Connection, string SQL)
{
try
{
DataTable dt=new DataTable();
SqlDataAdapter myAdapter = new SqlDataAdapter(SQL, Connection);
myAdapter.Fill(dt);
return dt;
}
catch
{
return null;
}
finally
{
Connection.Close();
Connection.Dispose();
}
}
}
在调用时,只需要两行代码,如:
ExecuteSQL newExecute = new ExecuteSQL();
DataTable dt = newExecute.Filldt(conn.myConn(), TextBox1.Text);
在这个类里只写了几种常用的方法,可以根据自己的需要增加修改——用了类,感觉~挺好的!