生成数据库创建脚本
是否可以从.NET生成SQL Server数据库的数据库创建脚本?
我正在使用C#,我想为我的应用程序创建一些安装程序项目,我可以在其上选择现有数据库,生成创建脚本并在另一个SQL服务器实例上运行它们。
对的,这是可能的。 使用SMO很容易做到这一点,请参阅用于脚本操作的Transfer类和用于数据库操作的Database类(创建,删除等)。 用法如下:
private StringCollection GetTransferScript(Database database) { var transfer = new Transfer(database); transfer.CopyAllObjects = true; transfer.CopyAllSynonyms = true; transfer.CopyData = false; // additional options transfer.Options.WithDependencies = true; transfer.Options.DriAll = true; transfer.Options.Triggers = true; transfer.Options.Indexes = true; transfer.Options.SchemaQualifyForeignKeysReferences = true; transfer.Options.ExtendedProperties = true; transfer.Options.IncludeDatabaseRoleMemberships = true; transfer.Options.Permissions = true; transfer.PreserveDbo = true; // generates script return transfer.ScriptTransfer(); }
如果你想用c#代码动态创建数据库,那么这里是代码:
你也可以这样做:
String Connectionstring = CCMMUtility.CreateConnectionString(false, txt_DbDataSource.Text, "master", "sa", "happytimes", 1000); SqlConnection con = new SqlConnection(); con.ConnectionString = Connectionstring; bool resultdbexistencx = CCMMUtility.CheckDatabaseExists(con, txt_DbName.Text); if (!resultdbexistencx) { // if not exists create it check the user name for sub-admin avialibe or not. if (txt_DbName.Text.Trim() == string.Empty) return; string strDbCreate; strDbCreate = "CREATE DATABASE " + txt_DbName.Text + " ON PRIMARY " + "(NAME = " + txt_DbName.Text + "_Data, " + "FILENAME = 'D:\" + txt_DbName.Text + "Data.mdf', " + "SIZE = 4MB, MAXSIZE = 10GB, FILEGROWTH = 100%) " + "LOG ON (NAME = " + txt_DbName.Text + "_Log, " + "FILENAME = 'D:\" + txt_DbName.Text + ".ldf', " + "SIZE = 4MB, " + "MAXSIZE = 10GB, " + "FILEGROWTH = 100%)"; SqlConnection sqlconn = new SqlConnection(Connectionstring); SqlCommand cmd = new SqlCommand(strDbCreate, sqlconn); try { sqlconn.Open(); sqlconn.ChangeDatabase("master"); cmd.ExecuteNonQuery(); } catch (Exception ex) { Int32 dbRollbackResult = RollBackTheWholetransaction(txt_DbName.Text.Trim(), Convert.ToInt32(HospitalResult)); if (dbRollbackResult == 1) { Response.Write(ex.Message); lblMessage.DisplayMessage(StatusMessages.ErrorMessage, "There is some problem while generating the database or database name doesn't avialible."); } }
这是“RollBackTheWholetransaction”方法的代码:
private Int32 RollBackTheWholetransaction(String DbName, Int32 HospitalId) { Int32 result = 0; try { String Connectionstring = CCMMUtility.CreateConnectionString(false, txt_DbDataSource.Text, "master", "sa", "happytimes", 1000); SqlConnection con = new SqlConnection(); con.ConnectionString = Connectionstring; String sqlCommandText = "ALTER DATABASE [" + DbName + "] SET SINGLE_USER WITH ROLLBACK IMMEDIATE"; String sqlCommandText1 = "DROP DATABASE [" + DbName + "]"; if (con.State == ConnectionState.Closed) { con.Open(); SqlConnection.ClearPool(con); con.ChangeDatabase("master"); SqlCommand sqlCommand = new SqlCommand(sqlCommandText, con); sqlCommand.ExecuteNonQuery(); SqlCommand sqlCommand1 = new SqlCommand(sqlCommandText1, con); sqlCommand1.ExecuteNonQuery(); ClsHospitals objHospiitals = new ClsHospitals(); String resultDbdelete = objHospiitals.DeleteHospital(HospitalId, Session["devSuperAdmin"].ToString()); if (resultDbdelete == "1") { result = 1; } else { result = 2; } } else { SqlConnection.ClearPool(con); con.ChangeDatabase("master"); SqlCommand sqlCommand = new SqlCommand(sqlCommandText, con); sqlCommand.ExecuteNonQuery(); SqlCommand sqlCommand1 = new SqlCommand(sqlCommandText1, con); sqlCommand1.ExecuteNonQuery(); } con.Close(); con.Dispose(); result = 1; } catch (Exception ex) { result = 0; } return result; }
这里是检查数据库中db存在的代码:
public static bool CheckDatabaseExists(SqlConnection tmpConn, string databaseName) { string sqlCreateDBQuery; bool result = false; try { // tmpConn = new SqlConnection("server=(local)\SQLEXPRESS;Trusted_Connection=yes"); sqlCreateDBQuery = string.Format("SELECT database_id FROM sys.databases WHERE Name = '{0}'", databaseName); using (tmpConn) { using (SqlCommand sqlCmd = new SqlCommand(sqlCreateDBQuery, tmpConn)) { if (tmpConn.State == System.Data.ConnectionState.Open) { tmpConn.Close(); tmpConn.Dispose(); } tmpConn.Open(); tmpConn.ChangeDatabase("master"); int databaseID = (int)sqlCmd.ExecuteScalar(); tmpConn.Close(); result = (databaseID > 0); } } } catch (Exception ex) { result = false; } return result; }
它的工作代码,希望它也适合你….
您必须自己编写代码来创建自己的安装程序。 那里有框架使它更容易。
我建议你看看WiX,使用它很容易,你可以做很多事情。 可以集成在Visual Studio中
上述就是C#学习教程:生成数据库创建脚本分享的全部内容,如果对大家有所用处且需要了解更多关于C#学习教程,希望大家多多关注—计算机技术网(www.ctvol.com)!
本文来自网络收集,不代表计算机技术网立场,如涉及侵权请联系管理员删除。
ctvol管理联系方式QQ:251552304
本文章地址:https://www.ctvol.com/cdevelopment/989585.html