SqlHelper类
public class SqlHelper
{
//获取数据库连接
static string sqlStr = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
///
/// 获取数据集
///
/// 要执行的Sql语句
/// Sql语句中的参数
///
public static DataTable ExecuteDataTable(string sql,params
SqlParameter [] param)
{
DataTable table = new DataTable();
using (SqlConnection con =new SqlConnection(sqlStr))
{
SqlDataAdapter adapter = new SqlDataAdapter(sql,con);
// 不为空 就添加参数
if(param.Length != 0)
{
adapter.SelectCommand.Parameters.AddRange(param);
}
adapter.Fill(table);
}
return table;
}
///
/// 返回受影响行数
///
///
///
///
public static int ExecuteNonquery(string sql, params
SqlParameter[] param)
{
int i = -1;
using (SqlConnection con = new SqlConnection(sqlStr))
{
con.Open();
SqlCommand com = new SqlCommand(sql,con);
com.Parameters.AddRange(param);
i = com.ExecuteNonQuery();
}
return i;
}
///
/// 返回首行首列
///
/// sql语句
/// sql中的参数
///
public static object ExecuteScalar(string sql,params
SqlParameter[] param)
{
using (SqlConnection con = new SqlConnection(sqlStr))
{
con.Open();
SqlCommand com = new SqlCommand(sql,con);
if(param.Length != 0)
{
com.Parameters.AddRange(param);
}
return com.ExecuteScalar();
}
}
///
/// 流方式返回数据
///
///
///
///
public static SqlDataReader ExecuteReader(string sql,params
SqlParameter[] param)
{
SqlDataReader reader;
SqlConnection con = new SqlConnection(sqlStr);
using (SqlCommand com = new SqlCommand(sql, con))
{
com.Parameters.AddRange(param);
con.Open();
//CommandBehavior.CloseConnection
//如果关闭SqlDataReader，SqlConnection也随之关闭
reader = com.ExecuteReader(CommandBehavior.CloseConnection);
}
return reader;
}
}
表现类
///
/// 通过id删用户
///
///
///
public int deleteUserById(int id)
{
string sql = "delete from Student where sid = @id";
SqlParameter p = new SqlParameter("@id",id);
return SqlHelper.ExecuteNonquery(sql,p);
}
//初始化数据
public DataTable initDataTable()
{
label1.Text = "总人数："+initDataTableCount();
string sql = " select * from Student";
return SqlHelper.ExecuteDataTable(sql);
}
//显示总人数
public string initDataTableCount()
{
string sql = "select COUNT(*) from Student";
return SqlHelper.ExecuteScalar(sql).ToString();
}
//流方式显示结果集
private void button1_Click_1(object sender, EventArgs e)
{
string sql = "select * from Student";
SqlDataReader reader = SqlHelper.ExecuteReader(sql);
while (reader.Read())
{
Console.WriteLine(reader["sName"].ToString());
}
reader.Close();
}