法一：通常用法
public DataTable ExecuteQuery(string sql)
{
string connStr = @"server=SIKU;database=newssystem;uid=sa;pwd=123456";
sqlconnection conn=new sqlconnection(connStr);
conn.open();
string sql="select * from category";
sqlcommand cmd=new sqlcommand(sql,conn);
sqlDataReader sdr=new sqlDataReader();
DataTable dt=new DataTable();
dt.Load(sdr);
sdr.close();
conn.close();
return dt;
}
法二：（当需要多次连接数据库时，会浪费资源，因此需要整合。利用try---catch）
在SQLHelper中输入：
public class SQLHelper
{ //将字符串先定义
private SqlConnection conn = null;
private SqlCommand cmd = null;
private SqlDataReader sdr = null;
public SQLHelper()
{
string connStr = @"server=SIKU;database=newssystem;uid=sa;pwd=123456";
conn = new SqlConnection(connStr);
}
/// <summary>
/// 获取数据库链接
/// </summary>
/// <returns></returns>
private SqlConnection GetConn()
{
//如果conn的状态是连接关闭,就打开数据库连接。
if (conn .State ==ConnectionState .Closed )
{
conn.Open();
}
return conn;
}
/// <summary>
/// 该方法执行传入的增删改SQL语句
/// </summary>
/// <param name="sql">要执行的SQL语句</param>
/// <returns>返回更新的记录数</returns>
public int ExecuteNonQuery(string sql)
{
int res ;
try
{
cmd = new SqlCommand(sql, GetConn());//此为亮点
res = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (conn.State ==ConnectionState .Open)
{
conn.Close();
}
}
return res;
}
法三：使用Using
public class SQLHelper
{
private SqlConnection conn = null;
private SqlCommand cmd = null;
private SqlDataReader sdr = null;
public SQLHelper()
{
string connStr = @"server=SIKU;database=newssystem;uid=sa;pwd=123456";
conn = new SqlConnection(connStr);
}
/// <summary>
/// 获取数据库链接
/// </summary>
/// <returns></returns>
private SqlConnection GetConn()
{
if (conn .State ==ConnectionState .Closed )//如果conn的状态是连接关闭
{
conn.Open();
}
return conn;
}
public DataTable ExecuteQuery(string sql)
{
DataTable dt = new DataTable();
cmd = new SqlCommand(sql, GetConn ());
//使用Using
using (sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection ))
{
dt.Load(sdr);
}
return dt;
}
}