.net执行SQL脚本文件

关注

原创最新推荐文章于 2021-02-06 12:55:49 发布 782 阅读 AI 写同款· GEO 优化 ›

在线音乐平台（asp.net+sql server）含sql文件在线音乐平台（asp.net+sql server）含sql文件在线音乐平台（asp.net+sql server）含sql文件立即下载

//执行脚本文件
protected string proceedSQLScripts(string connectionString, string pathToScriptFile)

    {
        List<string> statements = new List<string>();

        using (Stream stream = File.OpenRead(pathToScriptFile))
        using (StreamReader reader = new StreamReader(stream))
        {
            string statement = string.Empty;
            while ((statement = readNextStatementFromStream(reader)) != null)
            {
                statements.Add(statement);
            }
        }
        try
        {
            //TODO run in transaction
            foreach (string stmt in statements)
            {
                using (SqlConnection conn = new SqlConnection(connectionString))
                {
                    conn.Open();
                    SqlCommand command = new SqlCommand(stmt, conn);
                    command.ExecuteNonQuery();
                    conn.Close();
                }
            }
        }
        catch (Exception ex)
        {
            return ex.Message;
        }
        return string.Empty;
    }

    protected string readNextStatementFromStream(StreamReader reader)
    {
        StringBuilder sb = new StringBuilder();

        string lineOfText;

        while (true)
        {
            lineOfText = reader.ReadLine();
            if (lineOfText == null)
            {
                if (sb.Length > 0)
                    return sb.ToString();
                else
                    return null;
            }

            if (lineOfText.TrimEnd().ToUpper() == "GO")
                break;

            sb.Append(lineOfText + Environment.NewLine);
        }

        return sb.ToString();
    }

收录于