class SqlHelper
{
//这个是将连接数据库的字符串写到配置文件中的
private static string connStr = ConfigurationManager.ConnectionStrings["dbConnStr"].ConnectionString;
// 我们当然也可以这样写(string sql, SqlParameter[] parameters)不过这样的话每次调用即使没有参数也要写个空参数
//用params 是可变参数,使用它时一定要放到最后面,这样编译器会先匹配前面的参数,还有对应的参数一定要是数组
public static int ExecuteNonQuery(string sql, params SqlParameter[] parameters)
{
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
//foreach (SqlParameter param in parameters)
//{
// cmd.Parameters.Add(param);
//}
cmd.Parameters.AddRange(parameters);
return cmd.ExecuteNonQuery();
}
}
}
public static object ExecuteScalar(string sql, params SqlParameter[] parameters)
{
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
cmd.Parameters.AddRange(parameters);
return cmd.ExecuteScalar();
}
}
}
//改进一下上面的，因为返回的是一张表
public static DataTable ExecuteDataTable(string sql,params SqlParameter[] parameters)
{
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
cmd.Parameters.AddRange(parameters);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet dataset = new DataSet();
adapter.Fill(dataset);
return dataset.Tables[0];
}
}
}
//我们就可以这样调用了
private void button3_Click(object sender, RoutedEventArgs e)
{
// 没有使用params ...SqlHelper.ExecuteNonQuery("insert into admin(name,password,rank) values('kkeek','123456',1)",new SqlParameter[0]);
SqlHelper.ExecuteNonQuery("insert into admin(name,password,rank) values('kkeek','123456',1)");
}
private void button4_Click(object sender, RoutedEventArgs e)
{
DataTable ds = SqlHelper.ExecuteDataTable("select * from admin where rank=@rank", new SqlParameter[] { new SqlParameter("@rank", 2)});
foreach (DataRow row in ds.Rows)
{
string name = (string)row["name"];
MessageBox.Show(name);
}
}