事情是这样的:
在一个系统里面,其中有一个功能是用户自己创建帐套,也就是新建一个数据库,这个数据库中有100多个表,还有存储过程,视图等,有一个已经建立好的模板数据库,我有两个想法:
1.直接将现有的数据库的MDF LDF文件附加到数据库中,附加的名字动态改变下
2.用数据库脚本实现,只是有这个想法,不知道如何实现,况且脚本也太多了所以请各位大哥指点一下,怎么实现,特别是做过类型用友软件的建立帐套功能的人指点下,谢谢!
在一个系统里面,其中有一个功能是用户自己创建帐套,也就是新建一个数据库,这个数据库中有100多个表,还有存储过程,视图等,有一个已经建立好的模板数据库,我有两个想法:
1.直接将现有的数据库的MDF LDF文件附加到数据库中,附加的名字动态改变下
2.用数据库脚本实现,只是有这个想法,不知道如何实现,况且脚本也太多了所以请各位大哥指点一下,怎么实现,特别是做过类型用友软件的建立帐套功能的人指点下,谢谢!
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]
2、“直接将现有的数据库的MDF LDF文件附加到数据库中,附加的名字动态改变下”
这个得先将MDF LDF文件复制一次,再附加,因为一对MDF LDF文件只能附加一次啊
一种可以直接使用string strSql = "BACKUP DATABASE [" + dbName + @"] TO DISK = '" + filePath + "'";
string strSql = "RESTORE DATABASE [" + dbName + @"] FROM DISK= '" + filePath + "'";或者使用SQLDMOpublic 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();
}
}