public void ScmFileBasedCompare(string srcConString, string targetConString) { var src = new SqlConnectionStringBuilder(srcConString); var bacPacFile = GetDacFileName(srcConString) + ".dacpac"; var dtVersion = DateTime.Now.ToString("yyyy.MM.dd.HHmmss"); //if Bac file not exists create new var dacService = new Microsoft.SqlServer.Dac.DacServices(srcConString); dacService.Extract(bacPacFile, src.InitialCatalog, "Test Application", Version.Parse(dtVersion)); var sourceDacpac = new SchemaCompareDacpacEndpoint(bacPacFile); var target = new SqlConnectionStringBuilder(targetConString); var targetDatabase = new SchemaCompareDatabaseEndpoint(targetConString); var comparison = new SchemaComparison(sourceDacpac, targetDatabase); // Persist comparison file to disk in Schema Compare (.scmp) format comparison.SaveToFile(@"C:\temp\mycomparison.scmp"); // Load comparison from Schema Compare (.scmp) file comparison = new SchemaComparison(@"C:\temp\mycomparison.scmp"); SchemaComparisonResult comparisonResult = comparison.Compare(); foreach (var d in comparisonResult.Differences) { Console.WriteLine(d.SourceObject.GetScript()); } // Find the change to table1 and exclude it. //foreach (SchemaDifference difference in comparisonResult.Differences) //{ // if (difference.TargetObject.Name != null && // difference.TargetObject.Name.HasName && // difference.TargetObject.Name.Parts[1] == "DbConnections") // { // comparisonResult.Exclude(difference); // break; // } //} // Publish the changes to the target database //SchemaComparePublishResult publishResult = comparisonResult.PublishChangesToTarget(); var publishResult = comparisonResult.GenerateScript("."); Console.WriteLine(publishResult.MasterScript); Console.WriteLine(publishResult.Script); Console.WriteLine(publishResult.Success ? "Publish succeeded." : "Publish failed."); }
public void ExtractDAC(string destServer, string databaseName, Version version, string extractFolderPath) { try { // Console.WriteLine(destServer.Information.Version); Microsoft.SqlServer.Dac.DacServices dacUnit = new Microsoft.SqlServer.Dac.DacServices(destServer); // DacExtractionUnit dacUnit = new DacExtractionUnit(destServer, databaseName, databaseName, new Version(version)); DirectoryInfo dir = new DirectoryInfo(extractFolderPath); if (!dir.Exists) { dir.Create(); } string dacFilePath = dir.FullName + @"\" + databaseName + ".dacpac"; if (File.Exists(dacFilePath)) { File.Delete(dacFilePath); } dacUnit.Extract(dacFilePath, databaseName, databaseName, version); filem = dacFilePath; // dacUnit.ExportBacpac(dacFilePath, databaseName); // dacUnit.Extract(dacFilePath); // Console.WriteLine("extract " + databaseName + "successfully"); } catch (IOException) { Console.WriteLine("please check extract folder"); Console.WriteLine("extract " + databaseName + "failed"); } catch (Exception EXC) { Console.WriteLine("extract " + databaseName + "failed"); } }
public static bool drdb(string ipname, string dbname, string user, string password, string filename) { bool ret = false; SqlConnection sqlConnection = new SqlConnection("Data Source=" + ipname + ";Initial Catalog=" + dbname + ";Persist Security Info=True;User ID=" + user + ";Password="******";min pool size=1;max pool size=50;Pooling=true"); Stream fs = null; Microsoft.SqlServer.Dac.DacPackage dactype = null; try { // ServerConnection serverconn = new ServerConnection(sqlConnection); // serverconn.Connect(); string dacfile = filename; fs = File.Open(dacfile, FileMode.OpenOrCreate) as Stream; dactype = Microsoft.SqlServer.Dac.DacPackage.Load(fs, DacSchemaModelStorageType.Memory); string BNAME = dactype.Name; Version vs = dactype.Version; // Deploy the DAC and create the database. // string dacName = ""; // bool evaluateTSPolicy = true; // Microsoft.SqlServer.Management.Sdk.Sfc.SqlStoreConnection sqlStoreConnection = new Microsoft.SqlServer.Management.Sdk.Sfc.SqlStoreConnection(serverconn.SqlConnectionObject); // sqlStoreConnection.Connect(); Microsoft.SqlServer.Dac.DacServices newDacStore = new Microsoft.SqlServer.Dac.DacServices(sqlConnection.ConnectionString); //Microsoft.SqlServer.Dac.DacAzureDatabaseSpecification dbversion = new DacAzureDatabaseSpecification(); // Microsoft.SqlServer.Dac.DeploymentPropertyAliasAttribute deployProperties = new Microsoft.SqlServer.Dac.DeploymentPropertyAliasAttribute("mydata"); //先卸载,然后重新安装部署 //newDacStore.Unregister("Database1"); // PublishOptions pop=new PublishOptions(); // DacDeployOptions ddp = new DacDeployOptions(); // pop.GenerateDeploymentScript = false; // pop.GenerateDeploymentReport = false; DacDeployOptions dacde = new DacDeployOptions(); dacde.CommandTimeout = 300; // dacde.DatabaseSpecification.Edition = DacAzureEdition.Basic; dacde.CreateNewDatabase = true; dacde.AllowIncompatiblePlatform = true; // newDacStore.Unregister("Database1"); newDacStore.Deploy(dactype, BNAME, true, dacde, null); fs.Close(); fs.Dispose(); //dactype.Dispose(); // newDacStore.Publish(dactype,"Database1",pop); // newDacStore.Deploy(dactype, "Database1", false, null, null); // serverconn.Cancel(); // serverconn.Disconnect(); // sqlStoreConnection.Disconnect(); } catch (Exception error) { if (dactype != null) { dactype = null; } if (fs != null) { fs.Close(); fs.Dispose(); } MessageBox.Show(error.Message); ret = false; } return(ret); }
private void button1_Click(object sender, EventArgs e) { if (string.IsNullOrEmpty(txbServer.Text)) { MessageBox.Show("服务器信息不能为空", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } if (string.IsNullOrEmpty(txbUserName.Text)) { MessageBox.Show("数据库名不能为空", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } if (string.IsNullOrEmpty(txbPwd.Text)) { MessageBox.Show("密码信息不能为空", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } if (string.IsNullOrEmpty(txtfile.Text)) { MessageBox.Show("选择文件名不能为空", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } SqlConnection sqlConnection = new SqlConnection("Data Source=" + this.txbServer.Text + ";Initial Catalog=" + "master" + ";Persist Security Info=True;User ID=" + txbUserName.Text + ";Password="******";min pool size=1;max pool size=50;Pooling=true"); Stream fs = null; try { button1.Enabled = false; this.richTextBox1.Clear(); this.richTextBox1.AppendText("开始发布中,请稍候。。。。。"); string dacfile = this.txtfile.Text; fs = File.Open(dacfile, FileMode.OpenOrCreate) as Stream; Microsoft.SqlServer.Dac.DacPackage dactype = Microsoft.SqlServer.Dac.DacPackage.Load(fs, DacSchemaModelStorageType.Memory); string BNAME = this.txbDbName.Text;// dactype.Name; // Version vs = dactype.Version; Microsoft.SqlServer.Dac.DacServices newDacStore = new Microsoft.SqlServer.Dac.DacServices(sqlConnection.ConnectionString); //先卸载,然后重新安装部署 DacDeployOptions dacde = new DacDeployOptions(); dacde.CommandTimeout = 300; dacde.CreateNewDatabase = true; dacde.AllowIncompatiblePlatform = true; newDacStore.Deploy(dactype, BNAME, true, dacde, null); fs.Close(); fs.Dispose(); this.richTextBox1.AppendText("架构发布完成\n\r"); this.richTextBox1.AppendText("开始导入数据中,稍候。。。。。。\n\r"); dodata(BNAME); this.richTextBox1.AppendText("导入数据结束"); button1.Enabled = true; AppConfig.ConfigSetValue("DBUnitTest2000.exe", "MyCon", "Data Source=" + txbServer.Text + ";Initial Catalog=" + BNAME + ";Persist Security Info=True;User ID=" + txbUserName.Text + ";Password="******";min pool size=1;max pool size=50;Pooling=true"); } catch (Exception error) { if (fs != null) { fs.Close(); fs.Dispose(); } button1.Enabled = true; MessageBox.Show(error.Message); } }
/// <summary> /// 发布数据库到服务器 /// </summary> /// <param name="ipname"></param> /// <param name="dbname"></param> /// <param name="user"></param> /// <param name="password"></param> /// <param name="filename"></param> /// <returns></returns> private bool DeployDb(string ipname, string dbname, string user, string password, string filename) { bool ret = false; string sqlConnection = "Data Source=" + ipname + ";Initial Catalog=master;Persist Security Info=True;User ID=" + user + ";Password="******";min pool size=1;max pool size=50;Pooling=true"; Stream fs = null; Microsoft.SqlServer.Dac.DacPackage dactype = null; try { string dacfile = filename; fs = File.Open(dacfile, FileMode.OpenOrCreate) as Stream; dactype = Microsoft.SqlServer.Dac.DacPackage.Load(fs, DacSchemaModelStorageType.Memory); string BNAME = dactype.Name; //数据库名 Version vs = dactype.Version; //版本 Microsoft.SqlServer.Dac.DacServices newDacStore = new Microsoft.SqlServer.Dac.DacServices(sqlConnection); DacDeployOptions dacde = new DacDeployOptions(); dacde.CommandTimeout = 300; // dacde.DatabaseSpecification.Edition = DacAzureEdition.Basic; //dacde.CreateNewDatabase = true; dacde.CreateNewDatabase = false; dacde.AllowIncompatiblePlatform = true; dacde.BlockOnPossibleDataLoss = false; // newDacStore.Unregister("Database1"); newDacStore.Deploy(dactype, BNAME, true, dacde, null); fs.Close(); fs.Dispose(); dactype.Dispose(); ret = true; } catch (Exception error) { if (dactype != null) { dactype = null; } if (fs != null) { fs.Close(); fs.Dispose(); } MessageBox.Show(error.Message); ret = false; } return(ret); // DacPackage dp = Microsoft.SqlServer.Dac.DacPackage.Load("d:\\mysqldb.dacpac"); // dactype.Unpack("d:\\dp"); //string d = dactype.Description; //Version v = dactype.Version; //string n = dactype.Name; //Microsoft.SqlServer.Dac.Model.ModelLoadOptions mlo = new Microsoft.SqlServer.Dac.Model.ModelLoadOptions(); //mlo.ModelStorageType = DacSchemaModelStorageType.Memory; //var model = Microsoft.SqlServer.Dac.Model.TSqlModel.LoadFromDacpac(fs, mlo); //foreach (var s in new Microsoft.SqlServer.Dac.Model.ModelTypeClass[] { Microsoft.SqlServer.Dac.Model.ModelSchema.Table, Microsoft.SqlServer.Dac.Model.ModelSchema.View, Microsoft.SqlServer.Dac.Model.ModelSchema.Procedure }) //{ // var allTables = model.GetObjects(Microsoft.SqlServer.Dac.Model.DacQueryScopes.UserDefined, s); // var tableScripts = from t in allTables // select t; // foreach (var x in tableScripts) // { // string on = x.ObjectType.Name;//类型 // string nm = x.Name.Parts[1];//名字 // string jb = x.GetScript(); // // int id=0; // foreach (var c in x.GetChildren()) // { // // writer.WriteStartElement("QueryObjectCols"); // // writer.WriteElementString("Title",c.Name.Parts[2]); // // writer.WriteElementString("ObjColID",id++.ToString()); // // writer.WriteElementString("Field",c.Name.Parts[2]); // string field = c.Name.Parts[2].ToString(); // // string fieldtype = c.ObjectType.ToString(); // string valtype = c.GetReferenced(Microsoft.SqlServer.Dac.Model.Column.DataType).First().Name.Parts[0]; // // writer.WriteElementString("Width","80"); // //writer.WriteElementString("Sortable","1"); // // if(s==Microsoft.SqlServer.Dac.Model.ModelSchema.Procedure)//如果是存储过程 // // writer.WriteElementString("ValueType",c.GetReferenced(Microsoft.SqlServer.Dac.Model.Parameter.DataType).First().Name.Parts[0]); // //else // // writer.WriteElementString("ValueType",c.GetReferenced().First().GetReferenced(Microsoft.SqlServer.Dac.Model.Column.DataType).First().Name.Parts[0]); // //writer.WriteElementString("ShowOrder","1"); // } // } //} // Microsoft.SqlServer.Dac.TSqlModelUtils.CalculatePlatformCompatibility() // var model = new Microsoft.SqlServer.Dac.TSqlModelUtils.;// (@"D:\kljob\CardLan\CardLanDB\bin\Debug\cardlandb.dacpac"); // string j = dactype.PostDeploymentScript; }