在c#如何创建数据库?

[yumanqing]

事情是这样的:
在一个系统里面，其中有一个功能是用户自己创建帐套,也就是新建一个数据库,这个数据库中有100多个表，还有存储过程,视图等，有一个已经建立好的模板数据库,

我有两个想法:
1.直接将现有的数据库的MDF LDF文件附加到数据库中,附加的名字动态改变下
2.用数据库脚本实现,只是有这个想法，不知道如何实现，况且脚本也太多了

所以请各位大哥指点一下，怎么实现，特别是做过类型用友软件的建立帐套功能的人指点下，谢谢！

[yumanqing]

注：是SQL Server 数据库

[格拉]

看见个星星!
偶是新手!

[格拉]

有脚本直接在查询分析器中执行不行吗?

[suyiming]

执行sql脚本 是个办法

using System;
using System.Data;
using System.Collections;
using System.Xml;
using System.IO;
using System.Text;
using System.Diagnostics;
 
namespace sym
{
    public class ZZConsole
    {
        [STAThread]
        static void Main(string[] args)
        {
            string sqlQuery = "osql.exe /uSa /p123 /s192.192.132.229 /dNorthWind /i yoursql.sql";
            string strRst = ExeCommand(sqlQuery);
            Console.WriteLine(strRst);
            Console.ReadLine();
        }
        
        public static string ExeCommand(string commandText)
        {
            Process p = new Process();
            p.StartInfo.FileName = "cmd.exe";
            p.StartInfo.UseShellExecute = false;
            p.StartInfo.RedirectStandardInput = true;
            p.StartInfo.RedirectStandardOutput = true;
            p.StartInfo.RedirectStandardError = true;
            p.StartInfo.CreateNoWindow = true;
            string strOutput = null;
            try
            {
                p.Start();
                p.StandardInput.WriteLine(commandText);
                p.StandardInput.WriteLine("exit");
                strOutput = p.StandardOutput.ReadToEnd();
                p.WaitForExit();
                p.Close();
            }
            catch(Exception e)
            {
                strOutput = e.Message;
            }
            return strOutput;
        }
    }
}
 
对于osql命名的参数如下：
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
 
用法: osql              [-U login id]          [-P password]
 [-S server]            [-H hostname]          [-E trusted connection]
 [-d use database name] [-l login timeout]     [-t query timeout]
 [-h headers]           [-s colseparator]      [-w columnwidth]
 [-a packetsize]        [-e echo input]        [-I Enable Quoted Identifiers]
 [-L list servers]      [-c cmdend]            [-D ODBC DSN name]
 [-q "cmdline query"]   [-Q "cmdline query" and exit]
 [-n remove numbering] [-m errorlevel]
 [-r msgs to stderr]    [-V severitylevel]
 [-i inputfile]         [-o outputfile]
 [-p print statistics] [-b On error batch abort]
 [-X[1] disable commands [and exit with warning]]
 [-O use Old ISQL behavior disables the following]
 [-? show syntax summary]

[myjce]

1、模版数据库先备份成备份文件，创建时用备份文件恢复
2、“直接将现有的数据库的MDF LDF文件附加到数据库中,附加的名字动态改变下”
这个得先将MDF LDF文件复制一次，再附加，因为一对MDF LDF文件只能附加一次啊

[yumanqing]

继续等待。。。

[眼里进了砂]

既然已经有了模板了，可以用改名db恢复的办法直接生成一个新的库。
一种可以直接使用

string strSql = "BACKUP DATABASE [" + dbName + @"] TO DISK = '" + filePath + "'";
string strSql = "RESTORE DATABASE [" + dbName + @"] FROM DISK= '" + filePath + "'";

或者使用SQLDMO

public void BackUpDataBase(string sqlServerName, string sqlUsername, string sqlPassword, string databaseName, string filePath, string backUpSetName, string backUpDescription)
        {
            Backup oBackup = new BackupClass();
            SQLServer oSQLServer = new SQLServerClass();
            try
            {
                //oSQLServer.LoginSecure = false;
                //oSQLServer.Connect("localhost", "sa", "1234");
                //oBackup.Action = SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;
                //oBackup.Database = "Northwind";
                //oBackup.Files = @"d:\Northwind.bak";
                //oBackup.BackupSetName = "Northwind";
                //oBackup.BackupSetDescription = "MsSqlDataBaseBackUp";
                //oBackup.Initialize = true;
                //oBackup.SQLBackup(oSQLServer);

                oSQLServer.LoginSecure = false;
                oSQLServer.Connect(sqlServerName, sqlUsername, sqlPassword);
                oBackup.Action = SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;
                oBackup.Database = databaseName;
                oBackup.Files = filePath;
                oBackup.BackupSetName = backUpSetName;
                oBackup.BackupSetDescription = backUpDescription;
                oBackup.Initialize = true;
                oBackup.SQLBackup(oSQLServer);
            }
            catch
            {
                throw;
            }
            finally
            {
                oSQLServer.DisConnect();
            }
        }


public void RestoreSqlDataBase(string sqlServerName, string sqlUsername, string sqlPassword, string databaseName, string filePath)
        {
            Restore oRestore = new RestoreClass();
            SQLServer oSQLServer = new SQLServerClass();
            try
            {
                //oSQLServer.LoginSecure = false;
                //oSQLServer.Connect("localhost", "sa", "1234");
                //oRestore.Action = SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
                //oRestore.Database = "Northwind";
                //oRestore.Files = @"d:\Northwind.bak";
                //oRestore.FileNumber = 1;
                //oRestore.ReplaceDatabase = true;
                //oRestore.SQLRestore(oSQLServer);

                oSQLServer.LoginSecure = false;
                oSQLServer.Connect(sqlServerName, sqlUsername, sqlPassword);
                oRestore.Action = SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
                oRestore.Database = databaseName;
                oRestore.Files = filePath;
                oRestore.FileNumber = 1;
                oRestore.ReplaceDatabase = true;
                oRestore.SQLRestore(oSQLServer);

            }
            catch
            {
                throw;
            }
            finally
            {
                oSQLServer.DisConnect();
            }
        }

[fenlin1985]

bangding