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;
}
