using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace WordRead
{
class SqlHelper
{
private static string connStr = ConfigurationManager.ConnectionStrings["dbConnStr"].ConnectionString;
//封装方法的原则:把不变的放在方法里,把变化的放在参数里
public static int ExecuteNonQuery(string sql)
{
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
return cmd.ExecuteNonQuery();
}
}
}
public static object ExecuteScalar(string sql)
{
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
return cmd.ExecuteScalar();
}
}
}
/// <summary>
/// 只用来执行查询结果比较少的sql
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static DataSet ExecuteDataSet(string sql)
{
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet dataset = new DataSet();
adapter.Fill(dataset);
return dataset;
}
}
}
}
}
改进后的:
public static int ExecuteNonQuery(string sql, 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, 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();
}
}
}
/// <summary>
/// 只用来执行查询结果比较少的sql
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static DataSet ExecuteDataSet(string sql, 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;
}
}
}
public static DataTable ExecuteDataTable(string sql)
{
using (SqlConnection conn = new SqlConnection(connStr))
{
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];
}
}
}