使用c代码asp.net核心运行sql脚本

q35jwt9p  于 2021-08-09  发布在  Java
关注(0)|答案(2)|浏览(424)
public class ClassApp
    {
        static string ServerName = "LAPTOP-xxxxxxRK\\SQLEXPRESS";
        static string DatabaseName = "NewDbInstance";
        private SqlConnection tmpConn;

        public void CreateDb()
        {
            SqlConnection tmpConn;
            string sqlCreateDBQuery;
            tmpConn = new SqlConnection();
            tmpConn.ConnectionString = "SERVER = " + ServerName + "; Trusted_Connection=True;";
            sqlCreateDBQuery = " CREATE DATABASE " + DatabaseName;

            SqlCommand myCommand = new SqlCommand(sqlCreateDBQuery, tmpConn);
            try
            {
                tmpConn.Open();
                Console.WriteLine(sqlCreateDBQuery);
                var result = myCommand.ExecuteNonQuery();
                Console.WriteLine(result);
                Console.WriteLine("Database has been created successfully!");

                if (result == -1)
                {
                    RunScript(DatabaseName);
                }
            }
            catch(Exception ex)
            {
                Console.WriteLine(ex.Message);
            }

        }

        public void RunScript(string dbName)
        {
            FileInfo file = new FileInfo("C:\\Users\\ADMIN\\script.sql");
            string script = file.OpenText().ReadToEnd();
            script = script.Replace("GO", "");

            SqlConnection tmpConn;
            tmpConn = new SqlConnection();
            tmpConn.ConnectionString = "Server=LAPTOP-xxxxxxRK\\SQLEXPRESS;" + "Database=" + dbName + "; Trusted_Connection=True;";
            tmpConn.Open();
            SqlCommand myCommandnew = new SqlCommand(script, tmpConn);
            var resultnew = myCommandnew.ExecuteNonQuery();   //Error comes here
            Console.WriteLine(resultnew);
            tmpConn.Close();
        }

所以我有两个问题
当我用c运行这个sql文件时,我得到了一个错误
“create schema”必须是查询批处理中的第一条语句。
如何解决这个问题?
如果数据库不存在,如何修改代码,比如创建数据库?

az31mfrm

az31mfrm1#

运行脚本检查数据库是否存在

DECLARE @dbname nvarchar(128)
    SET @dbname = N'DatabaseName'

    IF (EXISTS (SELECT name 
    FROM master.dbo.sysdatabases 
    WHERE ('[' + name + ']' = @dbname 
    OR name = @dbname)))
qzlgjiam

qzlgjiam2#

发生此错误的原因是,某些sql ddl命令必须作为sql批处理的第一条语句执行,而sql批处理在ssms中由“go”语句分隔。
要运行包含“go”语句的脚本,请使用microsoft.sqlserver.smo库,而不是解析它们并创建其他错误。它将把脚本作为单独的批处理,而不会有从已经存在的脚本中删除“go”的麻烦。
您的代码看起来非常类似:

using (SqlConnection connection = ...)
        {
            Server server = new Server(new ServerConnection(connection));

            try
            {
                server.ConnectionContext.ExecuteNonQuery(script);
            }
            catch (Exception e) {...}

相关问题