以编程方式安装数据库 [英] Programatically Database Installation

问题描述

大家好，

尝试安装我的项目时出现错误.这是一个Windows应用程序.我想做的是；
如果数据库存在，则应用程序将使用SQL Server上的现有数据库，但如果不存在，它将创建数据库并在应用程序上使用它.到目前为止我所做的；
该程序可以成功创建数据库，但是如果存在现有数据库，则不会让我安装它.
我得到的错误是在GetSql()方法上.由于该数据库存在，因此会引发类似``请更改数据库名称.它已经存在''的错误.
在Install()方法上，我检查数据库是否存在，但是检查它如何进入"if"语句并执行AddDBtable()方法.
BTW在"Sql.txt"上，它包含必要数据库的脚本.

而且我无法调试它，因为它在安装时不会允许我这样做.
代码；

Hello all,

I am having an error while I am trying to install my project. It is a windows application. What I am trying to do is;
If the database exists the application will use the existing database on the SQL server, but if not it will create the database and use it on the application. What I have done so far;
The program can create the db successfully but if there is an existing db it wont let me install it.
It error that I am getting is on the GetSql() method. Because the db exists, it is throwing an error like ''please change the db name.It already exists.''.
On the Install() method I check it if the db exists or not but some how it is go in to the "if" statement and executes the AddDBtable() method.
BTW on the ''Sql.txt'' it contains the script for the necessary db.

And I cant debug it because it wont let me do it while installation.
The code;

[RunInstaller(true)]
    public partial class ProjectInstaller : Installer
    {
        System.Data.SqlClient.SqlConnection masterConnection = new System.Data.SqlClient.SqlConnection();
        public ProjectInstaller()
        {
            InitializeComponent();
        }
        private string GetSql(string Name)
        {

            try
            {
                masterConnection.ConnectionString = @"Data Source=.\SQLEXPRESS;Initial Catalog=master;Integrated Security=True";
                System.Data.SqlClient.SqlCommand Command = new System.Data.SqlClient.SqlCommand("CREATE DATABASE AMAZON_PRODUCT_DB", masterConnection);
                Command.Connection.Open();
                Command.ExecuteNonQuery();
                Command.Connection.Close();
                Command.Dispose();
                // Reads the contents of the embedded file.
                StreamReader reader = new StreamReader("./SQLAuto.txt");
                return reader.ReadToEnd();

            }
            catch (Exception ex)
            {
               System.Windows.Forms.MessageBox.Show("In GetSQL: " + ex.Message);

               throw ex;
            }
        }

        private void ExecuteSql(string Sql)
        {
            masterConnection.ConnectionString = @"Data Source=.\SQLEXPRESS;Initial Catalog=AMAZON_PRODUCT_DB;Integrated Security=True";
            System.Data.SqlClient.SqlCommand Command = new System.Data.SqlClient.SqlCommand(Sql, masterConnection);
            Command.Connection.Open();
            try
            {
                Command.ExecuteNonQuery();
            }
            finally
            {
                // Closing the connection should be done in a Finally block
                Command.Connection.Close();
                Command.Dispose();
            }
        }

        protected void AddDBTable()
        {

            try
            {
                ExecuteSql(GetSql("sql.txt"));

            }
            catch (Exception ex)
            {
                // Reports any errors and abort.
                System.Windows.Forms.MessageBox.Show("In exception handler: " + ex.Message);
                throw ex;
            }
        }


        public override void Install(System.Collections.IDictionary stateSaver)
        {
            base.Install(stateSaver);
            string connString = @"Data Source=.\SQLEXPRESS;Initial Catalog=master;Integrated Security=True";

            string cmdText = "select * from master.dbo.sysdatabases where name='AMAZON_PRODUCT_DB'";
            //string cmdText = "select * from sys.databases where name ='" + AMAZON_PRODUCT_DB + "'";
            bool bRet = false;

            using (SqlConnection sqlConnection = new SqlConnection(connString))
            {
                sqlConnection.Open();

                using (SqlCommand sqlCmd = new SqlCommand(cmdText, sqlConnection))
                {
                    int nRet = sqlCmd.ExecuteNonQuery();
                    if (nRet <= 0)
                    {
                        bRet = false;
                    }
                    else
                    {
                        bRet = true;
                    }
                }
            }
            if (!bRet)
            {
                AddDBTable();
                masterConnection.Close();
                masterConnection.Dispose();
            }

推荐答案

我检查了您的代码并发现

I reviewed your code and found that

sqlCmd.ExecuteNonQuery();

返回-1.由于它不查询数据库，因此不会创建任何结果.此方法主要用于在数据库中插入值.

我更改了以下代码行

is returning -1. Since it does not query database therefore no outcome is created. This method is mainly used to insert values in a database.

I changed the following lines of codes

int nRet = sqlCmd.ExecuteNonQuery();
if (nRet == 0)

与以下行，它工作正常.

with the following line and it is working fine.

if (String.IsNullOrEmpty(sqlCmd.ExecuteScalar().ToString()))

希望这可以回答您的问题

Hope this may answer your question

这篇关于以编程方式安装数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！