目录
一、版本1.0
创建SqlHelper类
封装方法的原则：把不变的放到方法里，把变化的放到参数中。
1、方法封装
添加数据库连接字符串
private static string connString = ConfigurationManager.ConnectionStrings["dbConnstr"].ConnectionString;（1）ExecuteNonQuery方法
/// <summary>
/// 执行 Transact-SQL 语句并返回受影响的行数
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static int ExecuteNonQuery(string sql)
{
using (SqlConnection conn = new SqlConnection(connString))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
return cmd.ExecuteNonQuery();
}
}
}（2）ExecuteScalar方法
/// <summary>
/// 执行查询，并返回查询所返回的结果集中第一行的第一列
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static object ExecuteScalar(string sql)
{
using (SqlConnection conn = new SqlConnection(connString))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
return cmd.ExecuteScalar();
}
}
} （3）ExecuteDataTable方法
/// <summary>
/// 只用来执行查询结果比较少的sql
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static DataTable ExecuteDataTable(string sql)
{
using (SqlConnection conn = new SqlConnection(connString))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet dataset = new DataSet();
adapter.Fill(dataset);
return dataset.Tables[0]; //可以查询很多表，默认第一个
}
}
}2、调用测试
界面
（1）插入数据
private void btnExecuteNonQuery_V1_Click(object sender, EventArgs e)
{
SqlHelper.ExecuteNonQuery("insert into tbl_student(Name,Age,Comment) output inserted.ID values('NonQuery1',20,'WoMan')");
MessageBox.Show("执行完成");
}（2）查询数据
private void btnExecuteScalar_V1_Click(object sender, EventArgs e)
{
int i = (int)SqlHelper.ExecuteScalar("select count(*) from tbl_student where Age>100");
MessageBox.Show("Age>100的数据共：" + i.ToString() + "行数据");
}
（3）DataTable查询
private void btnExecuteDataTable_V1_Click(object sender, EventArgs e)
{
DataTable table = SqlHelper.ExecuteDataTable("select * from tbl_student where Age<30");
DataRowCollection rows = table.Rows;
for (int i = 0; i < rows.Count; i++)
{
DataRow row = rows[i];
int age = (int)row["Age"];
string name = (string)row["Name"];
string comment = (string)row["Comment"];
MessageBox.Show(name + "," + age.ToString() + "," + comment);
}
}
二、版本2.0
创建SqlHelper2类，添加SqlParameter数组参数。作用：将需要读写数进行参数化。
1、方法封装
添加数据库连接字符串
private static string connString = ConfigurationManager.ConnectionStrings["dbConnstr"].ConnectionString;（1）ExecuteNonQuery方法
/// <summary>
/// 执行 Transact-SQL 语句并返回受影响的行数
/// </summary>
/// <param name="sql">执行的sql语句</param>
/// <param name="parameters">sql语句中的参数</param>
/// <returns></returns>
public static int ExecuteNonQuery(string sql,SqlParameter[] parameters)
{
using (SqlConnection conn = new SqlConnection(connString))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
/*
*方法1
foreach (SqlParameter param in parameters)
{
cmd.Parameters.Add(param);
}
*
*/
//方法2
cmd.Parameters.AddRange(parameters); //AddRange添加的是数组
return cmd.ExecuteNonQuery();
}
}
}（2）ExecuteScalar方法
/// <summary>
/// 执行查询，并返回查询所返回的结果集
/// </summary>
/// <param name="sql">执行的sql语句</param>
/// <param name="parameters">sql语句中的参数</param>
/// <returns></returns>
public static object ExecuteScalar(string sql, SqlParameter[] parameters)
{
using (SqlConnection conn = new SqlConnection(connString))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
//方法2
cmd.Parameters.AddRange(parameters); //AddRange添加的是数组
return cmd.ExecuteScalar();
}
}
}（3）ExecuteDataTable方法
/// <summary>
/// 只用来执行查询结果比较少的sql
/// </summary>
/// <param name="sql">执行的sql语句</param>
/// <param name="parameters">sql语句中的参数</param>
/// <returns></returns>
public static DataTable ExecuteDataTable(string sql, SqlParameter[] parameters)
{
using (SqlConnection conn = new SqlConnection(connString))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
//方法2
cmd.Parameters.AddRange(parameters); //AddRange添加的是数组
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet dataset = new DataSet();
adapter.Fill(dataset);
return dataset.Tables[0]; //可以查询很多表，默认第一个
}
}
}2、调用测试
界面
（1）插入数据
无
（2）查询数据
无
（3）DataTable查询
private void btnExecuteDataTable_V2_Click(object sender, EventArgs e)
{
//如果没有需要传递的参数
//DataTable table = SqlHelper2.ExecuteDataTable("select * from tbl_student where Age<30",new SqlParameter[0]);
DataTable table = SqlHelper2.ExecuteDataTable("select * from tbl_student where Age<@age"
, new SqlParameter[]{new SqlParameter("age",30)});
DataRowCollection rows = table.Rows;
for (int i = 0; i < rows.Count; i++)
{
DataRow row = rows[i];
int age = (int)row["Age"];
string name = (string)row["Name"];
string comment = (string)row["Comment"];
MessageBox.Show(name + "," + age.ToString() + "," + comment);
}
}
三、版本3.0
创建SqlHelper3类，使用params长度可变参数方法。参数也可以不传，不传递参数那么SqlParameter[]长度即为0。
添加数据库连接字符串
private static string connString = ConfigurationManager.ConnectionStrings["dbConnstr"].ConnectionString;1、方法封装
（1）ExecuteNonQuery方法
/// <summary>
/// 执行 Transact-SQL 语句并返回受影响的行数
/// </summary>
/// <param name="sql">执行的sql语句</param>
/// <param name="parameters">sql语句中的参数</param>
/// <returns></returns>
public static int ExecuteNonQuery(string sql, params SqlParameter[] parameters)
{
using (SqlConnection conn = new SqlConnection(connString))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
/*
*方法1
foreach (SqlParameter param in parameters)
{
cmd.Parameters.Add(param);
}
*
*/
//方法2
cmd.Parameters.AddRange(parameters); //AddRange添加的是数组
return cmd.ExecuteNonQuery();
}
}
}（2）ExecuteScalar方法
/// <summary>
/// 执行查询，并返回查询所返回的结果集
/// </summary>
/// <param name="sql">执行的sql语句</param>
/// <param name="parameters">sql语句中的参数</param>
/// <returns></returns>
public static object ExecuteScalar(string sql, params SqlParameter[] parameters)
{
using (SqlConnection conn = new SqlConnection(connString))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
//方法2
cmd.Parameters.AddRange(parameters); //AddRange添加的是数组
return cmd.ExecuteScalar();
}
}
}（3）ExecuteDataTable方法
/// <summary>
/// 只用来执行查询结果比较少的sql
/// </summary>
/// <param name="sql">执行的sql语句</param>
/// <param name="parameters">sql语句中的参数</param>
/// <returns></returns>
public static DataTable ExecuteDataTable(string sql, params SqlParameter[] parameters)
{
using (SqlConnection conn = new SqlConnection(connString))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
//方法2
cmd.Parameters.AddRange(parameters); //AddRange添加的是数组
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet dataset = new DataSet();
adapter.Fill(dataset);
return dataset.Tables[0]; //可以查询很多表，默认第一个
}
}
}2、调用测试
界面
（1）插入数据
（2）查询数据
（3）DataTable查询
代码
private void btnExecuteDataTable_V3_Click(object sender, EventArgs e)
{
DataTable table = SqlHelper3.ExecuteDataTable("select * from tbl_student where Age<@age or Name=@name"
, new SqlParameter[] { new SqlParameter("age", 30),new SqlParameter("name","ppp") });
DataRowCollection rows = table.Rows;
for (int i = 0; i < rows.Count; i++)
{
DataRow row = rows[i];
int age = (int)row["Age"];
string name = (string)row["Name"];
string comment = (string)row["Comment"];
MessageBox.Show(name + "," + age.ToString() + "," + comment);
}
}
四、长度可变参数样例程序
1、长度不变参数
代码
static int Sum(int[] sums)
{
int result = 0;
foreach(int i in sums)
{
result += i;
}
return result;
}Main中调用
static void Main(string[] args)
{
int i = Sum(new int[] { 3, 5, 9, 12 });
Console.WriteLine(i);
Console.ReadKey();
}运行
2、添加长度可变参数
修改后的代码
static int Sum(params int[] sums)
{
int result = 0;
foreach(int i in sums)
{
result += i;
}
return result;
}main中调用
static void Main(string[] args)
{
int i = Sum(new int[] { 3, 5, 9, 12 });
Console.WriteLine(i);
int j = Sum(new int[] { 3, 5, 9, 12, 11, 30 });
Console.WriteLine(j);
Console.ReadKey();
}运行
3、总结
（1）长度可变参数，用于不确定具体要求的参数中。
（2）params的数组参数，放在最后一个参数位置。
如：
static int Sum(string str,params int[] sums)
{
int result = 0;
foreach(int i in sums)
{
result += i;
}
return result;
}工程下载
https://download.csdn.net/download/panjinliang066333/85193267
创作不易，请多多支持
扫一扫
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
