在项目中关于执行SQL语句的底层方法的通用类很多,但是很少有提供执行SQL脚本的的方法,曾经在项目中有功能需要能直接执行SQL脚本的方法,
经过项目的实践检验,方法比较实用,现在将代码贴出来给大家分享下:
/// <summary>
/// 执行SQL语句脚本文件(带注释,带Go) /// </summary> /// <param name="sqlFileName">sql脚本文件路径</param> public static int ExecuteSQLFile(String sqlFileName) { int icount = 0; using (SqlConnection connecction = new SqlConnection(connectionString)) { FileStream stream = new FileStream(sqlFileName, FileMode.Open); StreamReader reader = new StreamReader(stream, Encoding.GetEncoding("gb2312")); try { SqlCommand command = connecction.CreateCommand(); connecction.Open(); //读取文件 StringBuilder builder = new StringBuilder(); String strLine = ""; while ((strLine = reader.ReadLine()) != null) { if (strLine.Trim().ToUpper() != @"GO") { builder.AppendLine(strLine); } else { command.CommandText = builder.ToString(); icount = command.ExecuteNonQuery(); builder.Remove(0, builder.Length); } } command.CommandText = builder.ToString(); icount = command.ExecuteNonQuery(); builder.Remove(0, builder.Length); reader.Close(); stream.Close(); return icount; } catch { reader.Close(); stream.Close(); icount = 0; return icount; } } }