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 --正常参数化写法