C# 执行项目中sql文件
受人之托，写个鸡肋博客~，for 二黑 ，PS：黑不是你的错，又黑又胖就是你不对了~
1、创建SqlHelper类
查看代码
/// <summary>
/// ado.net方式执行sql语句
/// Print.Y
/// </summary>
public class SqlHelper
{
/// <summary>
/// 执行sql语句，进行查询操作（推荐）
/// </summary>
/// <param name="sqlStr">sql语句</param>
/// <param name="parm">参集合数</param>
/// <returns>dataset结果集</returns>
public static DataSet ExcuteSqlDataSet(string sqlStr, string conStr, params SqlParameter[] param)
{
try
{
conStr = string.IsNullOrWhiteSpace(conStr) ? Connection.SqlConnetionStr() : conStr;
using (SqlConnection conn = new SqlConnection(conStr))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sqlStr;
if (param != null)
{
foreach (var item in param)
{
if (!string.IsNullOrWhiteSpace(item.Value.ToString_()))
{
cmd.Parameters.Add(item);
}
else
{
item.Value = DBNull.Value;
cmd.Parameters.Add(item);
}
}
}
//cmd.Parameters.AddRange(parm);
cmd.CommandTimeout = 180;
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet dataset = new DataSet();
//cmd.Prepare();
adapter.Fill(dataset);
cmd.Parameters.Clear();
return dataset;
}
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 执行sql语句，进行查询操作（推荐）
/// </summary>
/// <param name="sqlStr">sql语句</param>
/// <param name="parm">参集合数</param>
/// <returns>dataset结果集</returns>
public static DataSet ExcuteSqlDataSet2(string sqlStr, string conStr, List<SqlParameter> param)
{
try
{
conStr = string.IsNullOrWhiteSpace(conStr) ? Connection.SqlConnetionStr() : conStr;
using (SqlConnection conn = new SqlConnection(conStr))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sqlStr;
if (param != null)
{
foreach (var item in param)
{
if (!string.IsNullOrWhiteSpace(item.Value.ToString_()))
{
cmd.Parameters.Add(item);
}
else
{
item.Value = DBNull.Value;
cmd.Parameters.Add(item);
}
}
}
//cmd.Parameters.AddRange(parm);
cmd.CommandTimeout = 180;
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet dataset = new DataSet();
//cmd.Prepare();
adapter.Fill(dataset);
cmd.Parameters.Clear();
return dataset;
}
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 执行sql语句，执行增删改操作
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="param">参数集合</param>
/// <returns>受影响条数</returns>
public static int ExecuteNonQuery(string sql, string conStr, params SqlParameter[] param)
{
try
{
conStr = string.IsNullOrWhiteSpace(conStr) ? Connection.SqlConnetionStr() : conStr;
int n = -1;
using (SqlConnection con = new SqlConnection(conStr))
{
using (SqlCommand cmd = new SqlCommand(sql, con))
{
if (param != null)
{
foreach (var item in param)
{
if (!string.IsNullOrWhiteSpace(item.Value.ToString_()))
{
cmd.Parameters.Add(item);
}
else
{
item.Value = DBNull.Value;
cmd.Parameters.Add(item);
}
}
}
//cmd.Parameters.AddRange(param);
con.Open();
n = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
}
return n;
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 执行sql语句，执行增删改操作
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="param">参数集合</param>
/// <returns>受影响条数</returns>
public static int ExecuteNonQuery2(string sql, string conStr, List<SqlParameter> param)
{
try
{
conStr = string.IsNullOrWhiteSpace(conStr) ? Connection.SqlConnetionStr() : conStr;
int n = -1;
using (SqlConnection con = new SqlConnection(conStr))
{
using (SqlCommand cmd = new SqlCommand(sql, con))
{
if (param != null)
{
foreach (var item in param)
{
if (!string.IsNullOrWhiteSpace(item.Value.ToString_()))
{
cmd.Parameters.Add(item);
}
else
{
item.Value = DBNull.Value;
cmd.Parameters.Add(item);
}
}
}
//cmd.Parameters.AddRange(param);
con.Open();
cmd.CommandTimeout = 300;
n = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
}
return n;
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// SqlBulkCopy批量插入数据
/// </summary>
/// <param name="connectionStr">链接字符串</param>
/// <param name="dataTableName">表名</param>
/// <param name="sourceDataTable">数据源</param>
/// <param name="batchSize">一次事务插入的行数</param>
public static void BulkCopy(string connectionStr, string dataTableName, DataTable sourceDataTable, int batchSize = 100000)
{
connectionStr = string.IsNullOrWhiteSpace(connectionStr) ? Connection.SqlConnetionStr() : connectionStr;
using (SqlConnection connection = new SqlConnection(connectionStr))
{
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(connectionStr, SqlBulkCopyOptions.UseInternalTransaction))
{
try
{
sqlBulkCopy.DestinationTableName = dataTableName;
sqlBulkCopy.BatchSize = batchSize;
for (int i = 0; i < sourceDataTable.Columns.Count; i++)
{
sqlBulkCopy.ColumnMappings.Add(sourceDataTable.Columns[i].ColumnName, sourceDataTable.Columns[i].ColumnName);
}
sqlBulkCopy.WriteToServer(sourceDataTable);
}
catch (Exception ex)
{
throw ex;
}
}
}
}
/// <summary>
/// 执行存储过程，返回DataSet
/// </summary>
/// <param name="storedProcedure"></param>
/// <param name="conStr"></param>
/// <param name="param"></param>
/// <returns></returns>
public static DataSet ExcuteStoredProcedure(string storedProcedure, string conStr, List<SqlParameter> param)
{
try
{
conStr = string.IsNullOrWhiteSpace(conStr) ? Connection.SqlConnetionStr() : conStr;
using (SqlConnection conn = new SqlConnection(conStr))
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure; //指定执行存储过程操作
cmd.CommandText = storedProcedure; //存储过程名称
if (param != null)
{
foreach (var item in param)
{
if (!string.IsNullOrWhiteSpace(item.Value.ToString_()))
{
cmd.Parameters.Add(item);
}
else
{
item.Value = DBNull.Value;
cmd.Parameters.Add(item);
}
}
}
cmd.CommandTimeout = 180;
//方式一，查询回来的结果需要显示在DataGrid之类的控件上
DataSet ds = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.Fill(ds);
return ds;
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 执行存储过程，返回DataSet
/// </summary>
/// <param name="storedProcedure"></param>
/// <param name="conStr"></param>
/// <param name="param"></param>
/// <returns></returns>
public static DataSet ExcuteStoredProcedure2(string storedProcedure, string conStr, List<SqlParameter> param, out int pageCount)
{
try
{
conStr = string.IsNullOrWhiteSpace(conStr) ? Connection.SqlConnetionStr() : conStr;
using (SqlConnection conn = new SqlConnection(conStr))
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure; //指定执行存储过程操作
cmd.CommandText = storedProcedure; //存储过程名称
if (param != null)
{
foreach (var item in param)
{
if (!string.IsNullOrWhiteSpace(item.Value.ToString_()))
{
cmd.Parameters.Add(item);
}
else
{
item.Value = DBNull.Value;
cmd.Parameters.Add(item);
}
}
}
cmd.CommandTimeout = 180;
;
cmd.Parameters.Add("@PageCount", SqlDbType.Int).Direction = ParameterDirection.Output;
//方式一，查询回来的结果需要显示在DataGrid之类的控件上
DataSet ds = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.Fill(ds);
pageCount = (int)cmd.Parameters["@PageCount"].Value;
return ds;
}
}
catch (Exception ex)
{
throw ex;
}
}
}2、创建文件执行类
查看代码
/// <summary>
/// SQL语句执行帮助
/// Print.Y
/// 2019-01-28
/// </summary>
public class SQLSentenceHelper
{
/// <summary>
/// 执行.sql文件中的语句
/// </summary>
/// <param name="SQLSentenceName">SQL语句所在的文件名</param>
/// <param name="SQLConnection">SQL链接字符串，如果null则默认链接</param>
/// <param name="SQLParameterList">SQL参数，List集合</param>
/// <returns></returns>
public static DataSet Execute(string SQLSentenceName, string SQLConnection, List<SqlParameter> SQLParameterList)
{
try
{
string sqlStr = GetContent(SQLSentenceName);
return SqlHelper.ExcuteSqlDataSet2(sqlStr, SQLConnection, SQLParameterList);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 执行.sql文件中的语句
/// </summary>
/// <param name="SQLSentenceName">SQL语句所在的文件名</param>
/// <param name="SQLConnection">SQL链接字符串，如果null则默认链接</param>
/// <param name="SQLParameterList">SQL参数，List集合</param>
/// <param name="ParamDic">SQL语句文件中参数，字典类型</param>
/// <returns></returns>
public static DataSet Execute(string SQLSentenceName, string SQLConnection, List<SqlParameter> SQLParameterList, Dictionary<string, string> ParamDic)
{
try
{
string sqlStr = GetContent(SQLSentenceName);
foreach (var item in ParamDic)
{
sqlStr = sqlStr.Replace(item.Key, item.Value);
}
return SqlHelper.ExcuteSqlDataSet2(sqlStr, SQLConnection, SQLParameterList);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 执行.sql文件中的语句
/// </summary>
/// <param name="SQLSentenceName">SQL语句所在的文件名</param>
/// <param name="SQLConnection">SQL链接字符串，如果null则默认链接</param>
/// <param name="SQLParameterList">SQL参数，List集合</param>
/// <param name="ParamDic">SQL语句文件中参数，字典类型</param>
/// <returns></returns>
public static int ExecuteNonQuery(string SQLSentenceName, string SQLConnection, List<SqlParameter> SQLParameterList, Dictionary<string, string> ParamDic)
{
try
{
string sqlStr = GetContent(SQLSentenceName);
foreach (var item in ParamDic)
{
sqlStr = sqlStr.Replace(item.Key, item.Value);
}
return SqlHelper.ExecuteNonQuery2(sqlStr, SQLConnection, SQLParameterList);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 执行.sql文件中的语句
/// </summary>
/// <param name="SQLSentenceName">SQL语句所在的文件名</param>
/// <param name="SQLConnection">SQL链接字符串，如果null则默认链接</param>
/// <param name="SQLParameterList">SQL参数，List集合</param>
/// <returns></returns>
public static int ExecuteNonQuery(string SQLSentenceName, string SQLConnection, List<SqlParameter> SQLParameterList)
{
try
{
string sqlStr = GetContent(SQLSentenceName);
return SqlHelper.ExecuteNonQuery2(sqlStr, SQLConnection, SQLParameterList);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 根据SQL语句文件名或者文件内容
/// </summary>
/// <param name="SQLSentenceName">SQL语句所在的文件名</param>
/// <returns></returns>
private static string GetContent(string SQLSentenceName)
{
string SQLSentenceFolderPath = null;
if (HttpContext.Current == null)
{
SQLSentenceFolderPath = System.AppDomain.CurrentDomain.BaseDirectory + "SQLSentence";//Debug/SQLSentence
}
else
{
SQLSentenceFolderPath = HttpContext.Current.Server.MapPath("/Content/SQLSentence");
}
DirectoryInfo directoryInfo = new DirectoryInfo(SQLSentenceFolderPath);
FileInfo[] fileInfoArry = directoryInfo.GetFiles();
var files = fileInfoArry.Where(e => e.Name.ToLower() == SQLSentenceName.ToLower()).ToList();
if (files.Count() != 1)
{
return "";
}
return File.ReadAllText(files[0].FullName);
}
}这块就注意一个点就行，GetContent方法中区分控制台程序与web程序，因为这两种类型获取项目根目录方式不同，这块做了简单兼容。
3、调用
方式1 - 需要SqlParameter
DataTable dt = SQLSentenceHelper.Execute("PrintYQuery.sql", null, new List<SqlParameter> { new SqlParameter("@ChinaName", item), new SqlParameter("@TypeStr", "危险品") }).Tables[0];方式2 - 需要拼接sql
这种方式就是sql需要代码逻辑判断拼接，这种也是用替代符简单做了一个参数。
List<SqlParameter> sqlParameterList = new List<SqlParameter>();//参数化
Dictionary<string, string> paramDic = new Dictionary<string, string>();//字典类型，sql语句拼接
paramDic.Add("@PrintYParam01@", whereIn);
int affectedRows = SQLSentenceHelper.ExecuteNonQuery("PrintYQuery.sql", null, sqlParameterList, paramDic);sql文件放在 bin\Debug\SQLSentence 目录下即可，文件以.sql结尾，sql文件中语句按照正常写法就行。
WHERE @PrintYPararm01@ --前后双引号，实现SQL语句拼接
WHERE ID=@ID --正常参数化写法
