public static AvailabilityDatabase GetDb(string agName, string dbName, SMO.Server server)
{
AvailabilityGroup ag = server.AvailabilityGroups[agName];
AvailabilityDatabase db = ag.AvailabilityDatabases[dbName];
return(db);
}
public void CreateDatabase()
{
var connectoinStringBuilder = new SqlConnectionStringBuilder(ConnectionString);
string dbName = connectoinStringBuilder.InitialCatalog;
connectoinStringBuilder.InitialCatalog = string.Empty;
using (var connection = new SqlConnection(connectoinStringBuilder.ToString()))
{
try
{
var serverConnection = new ServerConnection(connection);
var server = new Microsoft.SqlServer.Management.Smo.Server(serverConnection);
var db = new Database(server, dbName);
db.Create();
}
catch (Exception e)
{
throw new Exception(string.Format("Ошибка при создании БД - {0}", e));
}
finally
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
}
}
public static bool GetDbJoinedState(string agName, string dbName, SMO.Server server)
{
AvailabilityDatabase db = GetDb(agName, dbName, server);
db.Refresh(); // database state is server side property, so the SMO object needs to be refreshed to get the correct value.
return(db.IsJoined);
}
private void Initialize()
{
if (_smoServer == null)
{
try
{
// Build connection string
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
builder.ApplicationName = "mssql-webapi";
builder.DataSource = this._host + "," + this._port;
builder.UserID = this._username;
builder.Password = this._password;
builder.ConnectTimeout = 30;
builder.IntegratedSecurity = false;
builder.InitialCatalog = "master";
SqlConnection sqlConnection = new SqlConnection(builder.ConnectionString);
SMOCommon.ServerConnection serverConnection = new SMOCommon.ServerConnection(sqlConnection);
_smoServer = new SMO.Server(serverConnection);
}
catch (SqlException e)
{
throw e;
}
}
}
/// <summary>
/// 数据库操作类
/// </summary>
/// <param name="server"></param>
/// <param name="userId"></param>
/// <param name="password"></param>
/// <param name="database"></param>
public GenerateScripts(string server, string userId, string password, string database)
{
_Connection = new Microsoft.SqlServer.Management.Common.ServerConnection(server, userId, password);
_Server = new Microsoft.SqlServer.Management.Smo.Server(_Connection);
_Scripter = GetScripter(_Server);
_Database = _Server.Databases[database];
}
public bool AssignSQLUser(string servername, string username, string password, string LoginUsername, string LoginPassword, string DBName)
{
try
{
var serverconnection = CreateServerConnection(servername, username, password);
var srv = new Microsoft.SqlServer.Management.Smo.Server(serverconnection);
Database db = new Database(srv, DBName);
var database = srv.Databases.OfType <Database>().Where(x => x.Name == DBName).Single();
var login = srv.Logins.OfType <Login>().Where(x => x.Name == LoginUsername).SingleOrDefault();
var user = db.Users.OfType <User>()
.Where(u => u.Name == LoginUsername)
.SingleOrDefault();
if (user == null)
{
user = new User(database, LoginUsername);
user.Login = login.Name;
user.Create();
}
serverconnection.Disconnect();
return(true);
}
catch (Exception)
{
return(false);
throw;
}
}
public void GenerateScripts(ArrayList DBlist)
{
string filename = String.Empty;
ServerConnection svrconn = new ServerConnection(serverName);
svrconn.LoginSecure = false;
svrconn.Login = userName;
svrconn.Password = password;
Server srv = new Microsoft.SqlServer.Management.Smo.Server(svrconn);
foreach (string dbnam in DBlist)
{
GenerateTableScript(srv, dbnam.ToString());
GenerateViewsScript(srv, dbnam.ToString());
GenerateStoredprocedureScript(srv, dbnam.ToString());
GenerateUserDefinedFunctionsScript(srv, dbnam.ToString());
GenerateApplicationRolesScript(srv, dbnam.ToString());
GenerateDatabaseRolesScript(srv, dbnam.ToString());
GenerateSecuritySchemaScript(srv, dbnam.ToString());
GenerateUsersScript(srv, dbnam.ToString());
GenerateDatabaseTriggersScript(srv, dbnam.ToString());
}
}
public JobFetcher(ServerConnection connection)
{
System.Diagnostics.Debug.Assert(connection != null, "ServerConnection is null");
this.enumerator = new Enumerator();
this.connection = connection;
this.server = new SMO.Server(connection);
}
/// <summary>
/// Initializes a new instance of the ScriptExecutor class
/// </summary>
/// <param name="server">The server to which the executor must connect</param>
/// <param name="database">The database against which the scripts must be executed</param>
/// <param name="config">The configuratoin</param>
public ScriptExecutor(Server server, Database database, ScriptExecutorConfiguration config)
{
if (server == null)
{
throw new ArgumentNullException("server", "Server cannot be null");
}
if (database == null)
{
throw new ArgumentNullException("database", "Database cannot be null");
}
if (config == null)
{
throw new ArgumentNullException("config", "Config cannot be null");
}
if (config.ExecutionSequence.Count < 1)
{
throw new ArgumentOutOfRangeException("config", "Connfig.ExecutionSequence cannot be empty. At least one script needs to be defined");
}
this.server = server;
this.database = database;
this.config = config;
}
/// <summary>
/// Get a SMO Server object that is connected to the connection
/// </summary>
/// <param name="ci">Conenction info</param>
/// <returns>Smo Server object for the connection</returns>
public static Microsoft.SqlServer.Management.Smo.Server GetSmoServer(IManagedConnection mc)
{
SqlOlapConnectionInfoBase ci = mc.Connection;
if (ci == null)
{
throw new ArgumentNullException("ci");
}
SMO.Server server = null;
// see what type of connection we have been passed
SqlConnectionInfoWithConnection ciWithCon = ci as SqlConnectionInfoWithConnection;
if (ciWithCon != null)
{
server = new SMO.Server(ciWithCon.ServerConnection);
}
else
{
SqlConnectionInfo sqlCi = ci as SqlConnectionInfo;
if (sqlCi != null)
{
server = new SMO.Server(new ServerConnection(sqlCi));
}
}
if (server == null)
{
throw new InvalidOperationException();
}
return(server);
}
/// <summary>
/// Returns true if current user has given permission on given server.
/// </summary>
/// <param name="server"></param>
/// <param name="permissionName"></param>
/// <returns></returns>
public static bool HasPermissionOnServer(SMO.Server server, string permissionName)
{
return(Convert.ToBoolean(server.ConnectionContext.ExecuteScalar(
string.Format(CultureInfo.InvariantCulture,
"SELECT HAS_PERMS_BY_NAME(null, null, '{0}');",
permissionName))));
}
public static void LoadFromSMO(MSMO.Server instance, Guid instanceID, string instanceName)
{
#if TRACE
long startTicks = VNC.AppLog.Trace2("Enter", LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 0);
#endif
foreach (MSMO.ServerRole serverRole in instance.Roles)
{
//try
//{
// if (serverRole.IsFixedRole)
// {
// continue; // Skip Fixed Roles
// }
//}
//catch (Exception ex)
//{
// // Not available on SQL 2000 :(
//}
GetInfoFromSMO(serverRole, instanceID, instanceName);
}
#if TRACE
VNC.AppLog.Trace2("Exit", LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 1, startTicks);
#endif
}
private void Initialize()
{
if (_smoServer == null)
{
try
{
// Build connection string
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
builder.ApplicationName = "mssql-restapi";
builder.DataSource = this.Host + "," + this.Port;
builder.InitialCatalog = this.Database;
builder.UserID = this.Username;
builder.Password = this.Password;
builder.MultipleActiveResultSets = true; // required for SQL Azure
builder.ConnectTimeout = 30;
builder.ConnectRetryCount = 3;
builder.ConnectRetryInterval = 15;
builder.IntegratedSecurity = false;
// Create a SMO connection
SqlConnection sqlConnection = new SqlConnection(builder.ConnectionString);
SMOCommon.ServerConnection serverConnection = new SMOCommon.ServerConnection(sqlConnection);
_smoServer = new SMO.Server(serverConnection);
}
catch (SqlException e)
{
throw e;
}
}
}
/// <summary>
/// Gets the endpoint url of an AR.
/// </summary>
/// <param name="arName">Availability replica name</param>
/// <param name="agName">Availability grorup name</param>
/// <param name="server">smo server where this has to be executed</param>
/// <returns>Endpoint url of an Availability Replica</returns>
public static string GetAREndpointUrl(string arName, string agName, SMO.Server server)
{
AvailabilityGroup ag = server.AvailabilityGroups[agName];
AvailabilityReplica ar = ag.AvailabilityReplicas[arName];
return(ar.EndpointUrl);
}
// helper that runs query
public void ExecuteQuery(int cpuCount, int workloadCount)
{
bool hasTraffic = false;
try
{
if (workloadCount == 10)
{
StartPartialTraffic();
hasTraffic = true;
}
else if (workloadCount == 100)
{
StartFullTraffic();
hasTraffic = true;
}
}catch (Exception e)
{
Console.WriteLine("Exception catched during simulate traffic. Please check your traffic simulator client");
Console.WriteLine(e);
return;
}
int failoverCount = replicas.FindIndex(t => t.Name == primary.Name);
// find current primary's index from replica list
while (failoverCount < 21)
{
// pick new primary
int primaryIndex = (failoverCount + 1) % replicas.Count;
Smo.Server newPrimary = replicas[primaryIndex];
Console.WriteLine("Checking AG Synced");
while (!IsAGSynchronized())
{
Thread.Sleep(10);
}
DateTime beforeFailover = DateTime.Now;
string primaryName = primary.Name;
AGHelper.FailoverToServer(newPrimary, agName, AGHelper.ActionType.ManualFailover);
primary = newPrimary;
Console.WriteLine("AG: {0} failover to {1}. ", agName, primary.Name);
while (!IsAGSynchronized())
{
Thread.Sleep(10);
}
DateTime afterFailover = DateTime.Now;
TimeSpan failoverInterval = afterFailover - beforeFailover;
Console.WriteLine("Failover takes {0}", failoverInterval.TotalSeconds);
InsertFailoverReport(beforeFailover, afterFailover, dbCount, cpuCount, workloadCount, primaryName);
failoverCount += 1;
Thread.Sleep(new TimeSpan(0, 2, 0));
}
Console.WriteLine("Thread Stop");
if (hasTraffic)
{
DrainTraffic();
}
}
/// <summary>
/// Gets endpoint url for a given server. If endpoint doesn't exist, it will be created.
/// </summary>
/// <param name="server">SMO server for which endpoint url needs to be retrieved.</param>
/// <returns>Endpoinurl in string format.</returns>
public static string GetHadrEndpointUrl(SMO.Server server)
{
Endpoint ep = ARHelper.CreateHadrEndpoint(server);
return(string.Format(@"TCP://{0}:{1}", System.Net.Dns.GetHostEntry(server.ComputerNamePhysicalNetBIOS).HostName.ToString(),
ep.Protocol.Tcp.ListenerPort.ToString()));
}
/// <summary>
/// Checks whether the given replica is synchronized or not.
/// </summary>
/// <param name="arName">Replica that needs to be checked</param>
/// <param name="agName">Availability Group name</param>
/// <param name="server">SMO Server on which check.</param>
/// <returns>True if the given replica become synchronized, otherwise false.</returns>
public static bool IsReplicaSynchronized(string arName, string agName, SMO.Server server)
{
AvailabilityReplica ar = server.AvailabilityGroups[agName].AvailabilityReplicas[arName];
ar.Refresh();
return(ar.RollupSynchronizationState.Equals(AvailabilityReplicaRollupSynchronizationState.Synchronized));
}
//public static async Task<bool> CreateSQLMonitorLogin(string instanceName, out string message)
//{
// bool result = false;
// try
// {
// MSMO.Server server = SMO.Server.GetFromSMO(instanceName);
// MSMO.Login newLogin = new MSMO.Login(server, Data.Config.SQLInformationAgent_NTLoginName);
// newLogin.LoginType = MSMO.LoginType.WindowsUser;
// //newLogin.DefaultDatabase = "master";
// newLogin.Create();
// newLogin.AddToRole(Data.Config.SQLInformationAgent_ServerRole);
// message = "Success";
// result = true;
// //server.Logins.Add(newLogin);
// }
// catch (Microsoft.SqlServer.Management.Common.ConnectionFailureException ex)
// {
// VNC.AppLog.Warning(ex, LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 140);
// message = "Connection Failure";
// }
// catch (Exception ex)
// {
// VNC.AppLog.Error(ex, LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 141);
// message = ex.Message;
// }
// return result;
//}
public static bool CreateSQLMonitorLogin(string instanceName, out string message)
{
bool result = false;
try
{
MSMO.Server server = SMO.Server.GetFromSMO(instanceName);
MSMO.Login newLogin = new MSMO.Login(server, Data.Config.SQLInformationAgent_NTLoginName);
newLogin.LoginType = MSMO.LoginType.WindowsUser;
//newLogin.DefaultDatabase = "master";
newLogin.Create();
newLogin.AddToRole(Data.Config.SQLInformationAgent_ServerRole);
message = "Success";
result = true;
//server.Logins.Add(newLogin);
}
catch (Microsoft.SqlServer.Management.Common.ConnectionFailureException ex)
{
VNC.AppLog.Warning(ex, LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 140);
message = "Connection Failure";
}
catch (Exception ex)
{
VNC.AppLog.Error(ex, LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 141);
message = ex.Message;
}
return(result);
}
public void CreateDatabase()
{
var connectoinStringBuilder = new SqlConnectionStringBuilder(ConnectionString);
string dbName = connectoinStringBuilder.InitialCatalog;
connectoinStringBuilder.InitialCatalog = string.Empty;
using (var connection = new SqlConnection(connectoinStringBuilder.ToString()))
{
try
{
var serverConnection = new ServerConnection(connection);
var server = new Microsoft.SqlServer.Management.Smo.Server(serverConnection);
var db = new Database(server, dbName);
db.Create();
}
catch (Exception e)
{
throw new Exception(string.Format("Ошибка при создании БД - {0}", e));
}
finally
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
}
}
public void DropDatabaseIfExist()
{
var connectoinStringBuilder = new SqlConnectionStringBuilder(ConnectionString);
string dbName = connectoinStringBuilder.InitialCatalog;
connectoinStringBuilder.InitialCatalog = string.Empty;
using (var connection = new SqlConnection(connectoinStringBuilder.ToString()))
{
try
{
var serverConnection = new ServerConnection(connection);
var server = new Microsoft.SqlServer.Management.Smo.Server(serverConnection);
if (server.Databases.Contains(dbName))
{
server.KillDatabase(dbName);
}
}
finally
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
}
}
public static void LogBackup(string fileShare, SMO.Server sourceServer, string dbName)
{
string backupFilePath;
string fileName = string.Format(backupFileNameTemplate, dbName, BackupActionType.Log.ToString());
backupFilePath = Path.Combine(fileShare, fileName);
//delete the backup file
File.Delete(backupFilePath);
try
{
BackupDeviceItem backupDeviceItem = new BackupDeviceItem(backupFilePath, DeviceType.File);
//backup the database from the source server
Backup backup = new Backup();
backup.Action = BackupActionType.Log;
backup.Database = dbName;
backup.Devices.Add(backupDeviceItem);
backup.Incremental = true;
backup.LogTruncation = BackupTruncateLogType.Truncate;
backup.SqlBackup(sourceServer);
}
catch (Exception ex)
{
//if an exception happens, delete the file
File.Delete(backupFilePath);
Console.WriteLine("transilent backup failed");
}
}
//TEST
private void btnTestConnection_Click(object sender, EventArgs e)
{
string onnectionString = string.Format("Data Source={0}; User ID={1}; Password={2};", txtSQLServer.Text, txtUsername.Text, txtPassword.Text);
try
{
SQLHelper helper = new SQLHelper(onnectionString);
if (helper.IsConnection)
{
MessageBox.Show("Test connection successful", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
//populate dropdown with all databases
if (txtSQLServer.Text != null && txtSQLServer.Text != "")
{
ServerConnection srvcon = new ServerConnection(txtSQLServer.Text);
srvcon.LoginSecure = false;
srvcon.Login = txtUsername.Text;
srvcon.Password = txtPassword.Text;
Microsoft.SqlServer.Management.Smo.Server srv = new Microsoft.SqlServer.Management.Smo.Server(srvcon);
foreach (Database db in srv.Databases)
{
comboDatabase.Items.Add(db.Name);
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
/// <summary>
/// Store current values in DatabaseInfo history table.
/// </summary>
/// <param name="instance"></param>
/// <param name="database"></param>
private void TakeSnapShot(string instanceName, SQLInformation.Data.ApplicationDataSet.DatabasesRow databaseRow)
{
//SMO.Server server = SMOH.SMOD.GetServer(instanceName);
SMO.Server server = SQLInformation.SMO.Helper.GetServer(instanceName);
SQLInformation.Data.ApplicationDataSetTableAdapters.DatabaseInfoTableAdapter tableAdapter = new SQLInformation.Data.ApplicationDataSetTableAdapters.DatabaseInfoTableAdapter();
tableAdapter.Connection.ConnectionString = SQLInformation.Data.Config.SQLMonitorDBConnection;
int dbID = databaseRow.ID_DB;
SMO.Database db = server.Databases.ItemById(dbID);
SQLInformation.Data.ApplicationDataSet.DatabaseInfoRow newSnapShot = Common.ApplicationDataSet.DatabaseInfo.NewDatabaseInfoRow();
newSnapShot.SnapShotDate = DateTime.Now;
newSnapShot.Database_ID = databaseRow.ID;
newSnapShot.Instance_ID = databaseRow.Instance_ID;
newSnapShot.IndexSpaceUsage = db.IndexSpaceUsage;
newSnapShot.DataSpaceUsage = db.DataSpaceUsage;
newSnapShot.Size = db.Size;
newSnapShot.SpaceAvailable = db.SpaceAvailable;
Common.ApplicationDataSet.DatabaseInfo.AddDatabaseInfoRow(newSnapShot);
tableAdapter.Update(Common.ApplicationDataSet.DatabaseInfo);
}
} // end LoadCmbDBServer
#endregion
#region LoadCmbDB
/// <summary>
/// Loads associated Databases for the specified SQL Server server into the DB combobox.
/// </summary>
private void LoadCmbDB()
{
try
{
SMO.Server SqlServerObject;
if (_chkDBTrustConn.Checked)
{
SqlServerObject = new SMO.Server();
SqlServerObject.ConnectionContext.LoginSecure = _chkDBTrustConn.Checked;
SqlServerObject.ConnectionContext.ServerInstance = _cmbDBServer.Text;
}
else
{
ServerConnection svrConn = new ServerConnection(_cmbDBServer.Text, _txtDBUser.Text, _txtDBPassword.Text);
SqlServerObject = new SMO.Server(svrConn);
}
SqlServerObject.ConnectionContext.Connect();
// Clear the DB combobox and fill it with the associated non-System databases for the Server
_cmbDBDB.Items.Clear();
foreach (SMO.Database database in SqlServerObject.Databases)
{
if (!database.IsSystemObject)
{
_cmbDBDB.Items.Add(database.Name);
} // end if
} // end foreach
} // end try
catch (Exception ex)
{
RtlMessageBox.Show(ex.Message, GUILibStrings.captException);
}// end catch
} // end LoadCmbDB
/// <summary>
/// Save any changes
/// </summary>
/// <returns>True if any changes were commited</returns>
public bool ApplyChanges()
{
bool changesMade = UpdateSourceSchedule();
// save the changes.
if (this.alreadyCreated)
{
source.Alter();
}
else
{
source.Create();
// retrieving source.ID after creation would throw if the
// server was in CaptureSql mode. This is because the schedule
// id is not generated while capturing sql. Thus, we only query
// id and set the created flag to true only when the smo object
// is actually created and not scripted.
Microsoft.SqlServer.Management.Smo.Server svr = null;
if (this.parentJob != null && this.parentJob.Parent != null && this.parentJob.Parent.Parent != null)
{
svr = this.parentJob.Parent.Parent as Microsoft.SqlServer.Management.Smo.Server;
}
if (svr == null || SqlExecutionModes.CaptureSql != (SqlExecutionModes.CaptureSql & svr.ConnectionContext.SqlExecutionModes))
{
this.id = source.ID;
this.Created = true;
}
}
return(changesMade);
}
// TODO. Make this look like LoadStoredProcedures
private static void LoadExtendedProperties(SQLInformation.Data.ApplicationDataSet.DatabasesRow database)
{
var instanceName = from item in Common.ApplicationDataSet.Instances
where item.ID == database.Instance_ID
select item.Name_Instance;
SMO.Server server = new SMO.Server((string)instanceName.First());
server.ConnectionContext.LoginSecure = false; // SQL Authentication
server.ConnectionContext.Login = "******";
server.ConnectionContext.Password = "******";
server.ConnectionContext.ConnectTimeout = 10; // Seconds
SMO.ExtendedPropertyCollection extendedProps = server.Databases[database.Name_Database].ExtendedProperties;
foreach (SMO.ExtendedProperty prop in extendedProps)
{
Console.WriteLine(string.Format("EP Name:{0} Value:{1}", prop.Name, prop.Value));
}
try { database.EP_Area = (string)extendedProps["EP_Area"].Value; }
catch (Exception) { database.EP_Area = "[Not Set]"; }
try { database.EP_DBApprover = (string)extendedProps["EP_DBApprover"].Value; }
catch (Exception) { database.EP_DBApprover = "[Not Set]"; }
try { database.EP_DRTier = (string)extendedProps["EP_DRTier"].Value; }
catch (Exception) { database.EP_DRTier = "[Not Set]"; }
try { database.EP_PrimaryDBContact = (string)extendedProps["EP_PrimaryDBContact"].Value; }
catch (Exception) { database.EP_PrimaryDBContact = "[Not Set]"; }
try { database.EP_Team = (string)extendedProps["EP_Team"].Value; }
catch (Exception) { database.EP_Team = "[Not Set]"; }
}
public Server(string connectionString, string credentialName = null)
{
_connectionString = connectionString;
SqlConnection = new SqlConnection(_connectionString);
_server = new Microsoft.SqlServer.Management.Smo.Server(new ServerConnection(SqlConnection));
_credentialName = credentialName;
}
static void Main(string[] args)
{
var connection = new SqlClient.SqlConnection(args[0]);
connection.Open();
Console.WriteLine("connected");
var serverConnection = new Common.ServerConnection(connection);
var server = new Smo.Server(serverConnection);
var db = new Smo.Database(server, "master");
Console.WriteLine(db.ToString());
var results = db.ExecuteWithResults("SELECT * FROM sys.tables");
DoQuery(db);
while (true)
{
Console.WriteLine("Want to try again?");
var key = Console.ReadKey(true);
if (key.KeyChar.Equals('n'))
{
break;
}
try
{
DoQuery(db);
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
}
}
/// <summary>
/// Join the databases to an Availability Group.
/// </summary>
/// <param name="dbNames"></param>
/// <param name="agName"></param>
/// <param name="server"></param>
public static void JoinAG(IEnumerable <string> dbNames, string agName, SMO.Server server)
{
foreach (string dbName in dbNames)
{
JoinAG(dbName, agName, server);
}
}
public static void Main(string[] args)
{
//Connect to the local, default instance of SQL Server.
Microsoft.SqlServer.Management.Smo.Server srv = new Microsoft.SqlServer.Management.Smo.Server();
//Display all the configuration options.
foreach (ConfigProperty p in srv.Configuration.Properties)
{
Console.WriteLine(p.DisplayName);
}
Console.WriteLine("There are " + srv.Configuration.Properties.Count.ToString() + " configuration options.");
//Display the maximum and minimum values for ShowAdvancedOptions.
int min = srv.Configuration.ShowAdvancedOptions.Minimum;
int max = srv.Configuration.ShowAdvancedOptions.Maximum;
Console.WriteLine("Minimum and Maximum values are " + min + " and " + max + ".");
int configvalue = srv.Configuration.ShowAdvancedOptions.ConfigValue;
//Modify the value of ShowAdvancedOptions and run the Alter method.
srv.Configuration.ShowAdvancedOptions.ConfigValue = 0;
srv.Configuration.Alter();
//Display when the change takes place according to the IsDynamic property.
if (srv.Configuration.ShowAdvancedOptions.IsDynamic == true)
{
Console.WriteLine("Configuration option has been updated.");
}
else
{
Console.WriteLine("Configuration option will be updated when SQL Server is restarted.");
}
// Recover setting value
srv.Configuration.ShowAdvancedOptions.ConfigValue = configvalue;
srv.Configuration.Alter();
}
/// <summary>
/// Adds a database to an Availability Group.
/// </summary>
/// <param name="agName">Availability Group name</param>
/// <param name="databaseName">Database name to be added to AG</param>
/// <param name="server">smo server</param>
public static void AddDatabaseToAG(string agName, string databaseName, Smo.Server server)
{
AvailabilityGroup ag = server.AvailabilityGroups[agName];
AvailabilityDatabase db = new AvailabilityDatabase(ag, databaseName);
ag.AvailabilityDatabases.Add(db);
ag.AvailabilityDatabases.Refresh();
}
private void DeleteTestDatabase()
{
var connectionString = ConfigurationManager.ConnectionStrings["Test"].ConnectionString;
using (var sqlConnection = new SqlConnection(connectionString))
{
var serverConnection = new smoCommon.ServerConnection(sqlConnection);
var server = new smoManagement.Server(serverConnection);
if(server.Databases.Contains("StoreSpike_TEST"))
server.KillDatabase("StoreSpike_TEST");
}
}
private void button2_Click(object sender, EventArgs e)
{
string _instance = "localhost";
string _repertoireSauvegarde = @"C:\Documents and Settings\Guillaume\SQLSave";
string _horodatage = DateTime.Now.ToString("yyyyMMdd_hhmmss");
smoCommon.ServerConnection sc = new smoCommon.ServerConnection(_instance);
sc.Connect();
smo.Server myServer = new smo.Server(sc);
foreach (smo.Database myDb in myServer.Databases)
{
if (myDb.Name == "GEST_INFIRMERIE")
{
smo.Backup myBackup = new smo.Backup();
myBackup.Database = myDb.Name;
// Définit le type de sauvegarde à effectuer (base ou log)
myBackup.Action = smo.BackupActionType.Database;
// Sauvegarde FULL = false, Sauvegarde DIFF = true
myBackup.Incremental = false;
// Activation de la compression de la sauvegarde
myBackup.CompressionOption = smo.BackupCompressionOptions.Default;
// Ajout du device. Ici il s'agit d'un fichier mais on pourrait envisager une sauvegarde sur bande
myBackup.Devices.AddDevice(_repertoireSauvegarde + myDb.Name + "_" + _horodatage + ".bak", smo.DeviceType.File);
try
{
myBackup.SqlBackup(myServer);
Console.WriteLine(myDb.Name + " sauvegardée à " + DateTime.Now.ToString());
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}
sc.Disconnect();
}
protected void connect_Click(object sender, EventArgs e)
{
try
{
string dbName = string.Format(@".\SQLEXPRESS");
List<string> cboDBs = new List<string>();
cboDBs.Add("---Select a database---");
Microsoft.SqlServer.Management.Smo.Server server =
new Microsoft.SqlServer.Management.Smo.Server(dbName);
foreach (Database db in server.Databases)
{
cboDBs.Add(db.Name);
}
dbNameDropDown.DataSource = cboDBs;
dbNameDropDown.DataBind();
dbNameDropDown.Enabled = true;
}
catch (Exception exep)
{
throw new Exception("Could not connect to database engine,Please check connection"+exep);
}
}
public void DropDatabaseIfExist()
{
var connectoinStringBuilder = new SqlConnectionStringBuilder(ConnectionString);
string dbName = connectoinStringBuilder.InitialCatalog;
connectoinStringBuilder.InitialCatalog = string.Empty;
using (var connection = new SqlConnection(connectoinStringBuilder.ToString()))
{
try
{
var serverConnection = new ServerConnection(connection);
var server = new Microsoft.SqlServer.Management.Smo.Server(serverConnection);
if (server.Databases.Contains(dbName))
{
server.KillDatabase(dbName);
}
}
finally
{
if (connection.State == ConnectionState.Open)
connection.Close();
}
}
}
/// <summary>
/// Performs the action of this task.
/// </summary>
protected override void InternalExecute()
{
if (string.IsNullOrEmpty(this.UserName))
{
this.LogTaskMessage(MessageImportance.Low, "Using a Trusted Connection");
this.trustedConnection = true;
}
ServerConnection con = new ServerConnection { LoginSecure = this.trustedConnection, ServerInstance = this.MachineName, NonPooledConnection = this.NoPooling };
if (this.statementTimeout >= 0)
{
con.StatementTimeout = this.statementTimeout;
}
if (!string.IsNullOrEmpty(this.UserName))
{
con.Login = this.UserName;
}
if (!string.IsNullOrEmpty(this.UserPassword))
{
con.Password = this.UserPassword;
}
this.sqlServer = new SMO.Server(con);
switch (this.TaskAction)
{
case "Attach":
this.Attach();
break;
case "Detach":
this.Detach();
break;
case "GetInfo":
this.GetInfo();
break;
case "SetOffline":
this.SetOffline();
break;
case "SetOnline":
this.SetOnline();
break;
case "GetConnectionCount":
this.GetConnectionCount();
break;
case "Backup":
this.Backup();
break;
case "Restore":
this.Restore();
break;
case "Delete":
this.Delete();
break;
case "Script":
this.Script();
break;
case "ScriptData":
this.ScriptData();
break;
case "Rename":
this.Rename();
break;
case "Create":
this.Create();
break;
case "DeleteBackupHistory":
this.DeleteBackupHistory();
break;
case "CheckExists":
this.CheckExists();
break;
case "VerifyBackup":
this.VerifyBackup();
break;
default:
this.Log.LogError(string.Format(CultureInfo.CurrentCulture, "Invalid TaskAction passed: {0}", this.TaskAction));
return;
}
// Release the connection if we are not using pooling.
if (this.NoPooling)
{
this.sqlServer.ConnectionContext.Disconnect();
}
}
public void bgwValidateConnection_DoWorkHandler(object sender, DoWorkEventArgs e)
{
try
{
List<CheckedListItem> backupTables = e.Argument as List<CheckedListItem>;
Smo.Server srvDestination = new Smo.Server(Destination.ServerConnection);
// 如果Db不存在
if (!srvDestination.Databases.Contains(Destination.Database))
{
#region Create Database and Copy Table sechma
Smo.Database newdb = new Smo.Database(srvDestination, Destination.Database);
newdb.Create();
Smo.Server srvSource = new Smo.Server(Source.ServerConnection);
Smo.Database dbSource = srvSource.Databases[Source.Database];
Smo.Transfer transfer = new Smo.Transfer(dbSource);
transfer.CopyAllUsers = true;
transfer.CopyAllObjects = true;
transfer.CopyAllTables = false;
transfer.CopyData = false;
transfer.CopySchema = true;
transfer.Options.WithDependencies = true;
transfer.Options.DriAll = true;
transfer.Options.ContinueScriptingOnError = false;
// Create all table when database not exist
foreach (var tbl in dbSource.Tables)
{
transfer.ObjectList.Add(tbl);
}
transfer.DestinationServer = Destination.Server;
transfer.DestinationDatabase = newdb.Name;
transfer.DestinationLoginSecure = Destination.LoginSecurity;
if (!Destination.LoginSecurity)
{
transfer.DestinationLogin = Destination.UserId;
transfer.DestinationPassword = Destination.Password;
}
transfer.TransferData();
#endregion
#region Get Source Data and Filter
DataSet ds = DbHelper.CopySechmaFromDatabase(Source.ConnectionString());
List<string> sortTables = DbHelper.Fill(Source.ConnectionString(), ds, backupTables.Select(b => b.Name).ToList());
if (ds.Tables["Jobs"].Rows.Count > 0)
{
ds.Tables["Jobs"].Rows.Cast<DataRow>().LastOrDefault().Delete(); // Always delete last record.
ds.Tables["Jobs"].AcceptChanges();
if (IsDateFiltration)
{
ds.Tables["Jobs"].Rows.Cast<DataRow>().Where(j => (DateTime)j["Date"] < DateFrom || (DateTime)j["Date"] > DateTo).ToList().ForEach(j => j.Delete());
}
foreach (var tbl in sortTables)
{
ds.Tables[tbl].AcceptChanges();
}
}
#endregion
#region Execute SqlBulk Copy
foreach (string tbl in sortTables)
{
DbHelper.ExecuteSqlBulk(Destination.ConnectionString(), ds.Tables[tbl]);
this.Progress += 100 / backupTables.Count;
if (DbHelper.SqlBulkLog.Count > 0)
{
this.Log += DbHelper.SqlBulkLog.LastOrDefault() + Environment.NewLine;
}
}
#endregion
}
else
{
if (CheckVersion())
{
// TODO: Job 這邊無法判斷
#region Get Source Data and Filter date range
DataSet ds = DbHelper.CopySechmaFromDatabase(Source.ConnectionString());
List<string> sortTables = DbHelper.Fill(Source.ConnectionString(), ds, backupTables.Select(b => b.Name).ToList());
if (ds.Tables["Jobs"].Rows.Count > 0)
{
ds.Tables["Jobs"].Rows.Cast<DataRow>().LastOrDefault().Delete(); // Always delete last record.
ds.Tables["Jobs"].AcceptChanges();
if (IsDateFiltration)
{
ds.Tables["Jobs"].Rows.Cast<DataRow>().Where(j => (DateTime)j["Date"] < DateFrom || (DateTime)j["Date"] > DateTo).ToList().ForEach(j => j.Delete());
}
foreach (var tbl in sortTables)
{
ds.Tables[tbl].AcceptChanges();
}
}
#endregion
#region Get destination PK list of table exists and modify for merge
// filter override table don't modify key
List<string> overrideTable = backupTables.Where(b => b.IsOverride == true).Select(b => b.Name).ToList();
foreach (string tbl in sortTables)
{
if (!overrideTable.Contains(tbl))
{
string keycolumn = DbHelper.PrimaryKeyColumn(Destination.ConnectionString(), tbl);
string sql = string.Format("Select TOP 1 {0} From {1} Order By {0} DESC", keycolumn, tbl);
int? lastkey = (int?)(DbHelper.ReadOne(Destination.ConnectionString(), sql));
if (lastkey != null)
{
int newkey = (int)lastkey + 1;
int row = ds.Tables[tbl].Rows.Count;
ds.Tables[tbl].Columns[keycolumn].ReadOnly = false;
#region Delete Duplicate First
for (int i = row - 1; i >= 0; i--)
{
// check duplicate
string sqlCheckDataDuplicate;
int? duplicateNum;
switch (tbl)
{
case "MCS":
// NOTE: MCS sechma will appear many wrong situation.
string sName = ds.Tables[tbl].Rows[i]["sName"].ToString();
string pkMCS = ds.Tables[tbl].Rows[i]["pkMCS"].ToString();
sqlCheckDataDuplicate = string.Format("Select Count(*) From MCS Where sName='{0}' AND pkMCS='{1}'", sName, pkMCS);
duplicateNum = (int?)(DbHelper.ReadOne(Destination.ConnectionString(), sqlCheckDataDuplicate));
if (duplicateNum > 0)
{
ds.Tables[tbl].Rows[i].Delete();
}
break;
default:
break;
}
ds.Tables[tbl].AcceptChanges();
}
#endregion
#region Change KEY
row = ds.Tables[tbl].Rows.Count;
for (int i = row - 1; i >= 0; i--)
{
ds.Tables[tbl].Rows[i][keycolumn] = newkey + i;
}
#endregion
ds.Tables[tbl].AcceptChanges();
}
}
}
ds.AcceptChanges();
#endregion
#region Delete override table data
List<string> clearTable = backupTables.Where(b => b.IsOverride == true).Select(b => b.Name).ToList();
DeleteDestinationOverride(clearTable);
#endregion
#region Execute SqlBulk Copy
foreach (string tbl in sortTables)
{
DbHelper.ExecuteSqlBulk(Destination.ConnectionString(), ds.Tables[tbl]);
this.Progress += 100 / backupTables.Count;
if (DbHelper.SqlBulkLog.Count > 0)
{
this.Log += DbHelper.SqlBulkLog.LastOrDefault() + Environment.NewLine;
}
}
#endregion
}
else
{
this.Log += "*** Database upgrade failed ***" + Environment.NewLine;
e.Result = false;
}
}
}
catch (Exception ex)
{
this.Log += "An error occurred during backup database." + Environment.NewLine;
this.Log += ex.Message.ToString() + Environment.NewLine;
e.Result = false;
}
// DONE: 1. Check destination database exsits.
// DONE: 2. If No date range, use transfer copy all database.
// DONE: 2-1. If use date range, DataTable.Select(); filter Jobs key (klKey) and filter another table has FK by Jobs (fkJobKey, klJobKey)
// DONE: 2-2. Use Sqlbulk copy datatable
// DONE: 3. If YES Check db version
// CheckVersion();
// DONE: 3-1. Source > Destination => upgrade scripts
// DONE: 3-2. Source == Destination => Run step 4 for merge data.
// DONE: 3-3. Source < Destination => false; alert message and block;
// DONE: 4. Deal table releationship PK/FK to create DataSet & Datatable from Source.
// DONE: 5. If table of ObservTable selected get record of Destination last PK int.
// List<Record> Record.LastKey, Record.TableName, Record.PKColumnName
// TODO: 6. DataSet.Fill(); get data and filter date range.
// TODO: 7. Use Sqlbulk copy datatable.
}
public SqlServer2008(string ServerName)
{
_srv = new SMO.Server(ServerName);
}
private void ExecuteScript(string script, string connectionString)
{
using (var connection = new SqlConnection(connectionString))
{
try
{
var server = new Microsoft.SqlServer.Management.Smo.Server(new ServerConnection(connection));
server.ConnectionContext.ExecuteNonQuery(script);
}
catch (Exception e)
{
throw new Exception(string.Format("Ошибка при создании структуры БД - {0}", e));
}
finally
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
}
}
protected void EncryptObjects()
{
Microsoft.SqlServer.Management.Smo.Server SMOServer = new Microsoft.SqlServer.Management.Smo.Server(new ServerConnection(new System.Data.SqlClient.SqlConnection(GetConnectionString())));
Database db;
db = SMOServer.Databases[Database];
foreach (StoredProcedure s in db.StoredProcedures)
{
if (!s.IsSystemObject)
{
s.TextMode = false;
s.IsEncrypted = true;
s.Alter();
}
}
foreach (View v in db.Views)
{
if (!v.IsSystemObject)
{
v.TextMode = false;
v.IsEncrypted = true;
v.Alter();
}
}
foreach (UserDefinedFunction u in db.UserDefinedFunctions)
{
if (!u.IsSystemObject)
{
u.TextMode = false;
u.IsEncrypted = true;
u.Alter();
}
}
foreach (Trigger t in db.Triggers)
{
if (!t.IsSystemObject)
{
t.TextMode = false;
t.IsEncrypted = true;
t.Alter();
}
}
}
protected void ProcessScript(string Script)
{
try
{
if (SMOServer == null)
{
SMOServer = new Microsoft.SqlServer.Management.Smo.Server();
SMOServer.ConnectionContext.NonPooledConnection = true;
SMOServer.ConnectionContext.AutoDisconnectMode = AutoDisconnectMode.DisconnectIfPooled;
}
if (Script.Contains("CREATE DATABASE"))
{
//Replace the dbname here becuase it is a parm in the script.
Script = Script.Replace("[[DB_NAME]]", Database);
Script = Script.Replace("[[DB_PATH]]", DatabasePath);
//In this case, we need to build a new connection string, pointing to master.
string temp = this.Database;
this.Database = "master";
ServerConnection conn = new ServerConnection(new System.Data.SqlClient.SqlConnection(GetConnectionString()));
SMOServer.ConnectionContext.ConnectionString = conn.ConnectionString;
db = SMOServer.Databases["master"];
this.Database = temp;
SMOServer = new Microsoft.SqlServer.Management.Smo.Server();
SMOServer.ConnectionContext.NonPooledConnection = true;
SMOServer.ConnectionContext.AutoDisconnectMode = AutoDisconnectMode.DisconnectIfPooled;
SMOServer.ConnectionContext.ConnectionString = conn.ConnectionString;
if (Script.Contains("DROP DATABASE"))
{
if (SMOServer.Databases.Contains(Database) && Script.Contains("--##KILL ALL PROCESSES##--"))
{
SMOServer.KillAllProcesses(Database);
}
}
//Database is not yet created. We will NOT delete the database in this step. That can be added to the script.
//We don't want to cause undo stress if someone clicks install.
db.ExecuteNonQuery(Script);
}
else
{
ServerConnection conn = new ServerConnection(new System.Data.SqlClient.SqlConnection(GetConnectionString()));
SMOServer.ConnectionContext.ConnectionString = conn.ConnectionString;
// SMOServer.ConnectionContext.ConnectTimeout = _Timeout;
SMOServer.ConnectionContext.StatementTimeout = _Timeout;
//Connect to the DB Requested
SMOServer.ConnectionContext.BeginTransaction();
try
{
db = SMOServer.Databases[Database];
db.ExecuteNonQuery(Script);
SMOServer.ConnectionContext.CommitTransaction();
}
catch (Exception err)
{
WriteStatus("Error: " + err.Message);
try
{
SMOServer.ConnectionContext.RollBackTransaction();
}
catch (Exception err1)
{
WriteStatus("Error: " + err1.Message);
}
throw err;
}
}
}//try
catch (Exception err)
{
MMDBLogFile.Log(err);
WriteStatus("Error: " + err.Message);
throw (err);
}
finally
{
SMOServer.ConnectionContext.Disconnect();
db = null;
SMOServer = null;
}
}
public void BackupDatabase()
{
try
{
Microsoft.SqlServer.Management.Smo.Server SMOServer = new Microsoft.SqlServer.Management.Smo.Server(new ServerConnection(new System.Data.SqlClient.SqlConnection(GetConnectionString())));
if (!Directory.Exists(_BackupPath))
{
Directory.CreateDirectory(BackupPath);
}
string filename = _BackupPath;
filename += @"\" + _Database + @"_" + digits(DateTime.Now.Year.ToString(), 4) +
digits(DateTime.Now.Month.ToString(), 2) +
digits(DateTime.Now.Day.ToString(), 2) +
digits(DateTime.Now.Hour.ToString(), 2) +
digits(DateTime.Now.Minute.ToString(), 2) +
digits(DateTime.Now.Second.ToString(), 2) +
@".bak";
Backup backup = new Backup();
backup.Action = BackupActionType.Database;
backup.Database = _Database;
backup.Devices.Add(new BackupDeviceItem(filename, DeviceType.File));
backup.Initialize = true;
backup.Checksum = true;
backup.ContinueAfterError = true;
backup.Incremental = false;
backup.LogTruncation = BackupTruncateLogType.Truncate;
// Perform backup
backup.SqlBackup(SMOServer);
}
catch (Exception err)
{
MMDB.Core.MMDBLogFile.Log(err);
throw (err);
}
}
public static Microsoft.SqlServer.Management.Smo.Server GetServerConnection(string dbServerName, string loginID, string password, bool useNtAuthentication)
{
Microsoft.SqlServer.Management.Smo.Server srv = new Microsoft.SqlServer.Management.Smo.Server(dbServerName);
if (useNtAuthentication)
{
srv.ConnectionContext.LoginSecure = true;
}
else
{
srv.ConnectionContext.LoginSecure = false;
srv.ConnectionContext.Login = loginID;
srv.ConnectionContext.Password = password;
}
return srv;
}
private void SetupFunctions(CSSDataContext db)
{
var path = ConfigurationManager.AppSettings["TestRoot"];
var script = File.ReadAllText(Path.Combine(path, "CreateScript.sql"));
var connection = db.Connection as SqlConnection;
var serverConnection = new ServerConnection(connection);
var server = new SMO.Server(serverConnection);
server.ConnectionContext.ExecuteNonQuery(script);
}
/// <summary>
/// Performs the action of this task.
/// </summary>
protected override void InternalExecute()
{
if (string.IsNullOrEmpty(this.UserName))
{
this.LogTaskMessage(MessageImportance.Low, "Using a Trusted Connection");
this.trustedConnection = true;
}
ServerConnection con = new ServerConnection { LoginSecure = this.trustedConnection, ServerInstance = this.MachineName, NonPooledConnection = this.NoPooling };
if (!string.IsNullOrEmpty(this.UserName))
{
con.Login = this.UserName;
}
if (!string.IsNullOrEmpty(this.UserPassword))
{
con.Password = this.UserPassword;
}
this.sqlServer = new SMO.Server(con);
switch (this.TaskAction)
{
case "GetInfo":
this.GetInfo();
break;
case "GetConnectionCount":
this.GetConnectionCount();
break;
default:
this.Log.LogError(string.Format(CultureInfo.CurrentCulture, "Invalid TaskAction passed: {0}", this.TaskAction));
return;
}
// Release the connection if we are not using pooling.
if (this.NoPooling)
{
this.sqlServer.ConnectionContext.Disconnect();
}
}
private void RunBackup()
{
// Flow 1 Destination Dataabase not exists.
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(_source_connstring);
/* SOURCE Database */
string s_server = builder["Server"].ToString();
string s_username = builder["User ID"].ToString();
string s_pwd = builder["Password"].ToString();
string s_db = cmbSourceDatabases.SelectedItem.ToString();
/* DESTINATION Database */
builder = new SqlConnectionStringBuilder(_target_connstring);
string d_server = builder["Server"].ToString();
string d_username = builder["User ID"].ToString();
string d_pwd = builder["Password"].ToString();
string d_db = txtBackupDatabaseName.Text.Trim();
ServerConnection conn;
if (string.IsNullOrEmpty(s_username))
{
conn = new ServerConnection(s_server);
}
else
{
conn = new ServerConnection(s_server, s_username, s_pwd);
}
Smo.Server source_srv = new Smo.Server(conn);
Smo.Database source_db = source_srv.Databases[s_db];
Smo.Transfer transfer = new Smo.Transfer(source_db);
transfer.CopyAllUsers = true;
transfer.CreateTargetDatabase = false;
transfer.CopyAllObjects = false;
transfer.CopyAllTables = false;
transfer.CopyData = true;
// transfer.CopySchema = true;
transfer.Options.WithDependencies = true;
transfer.Options.DriAll = true;
transfer.Options.ContinueScriptingOnError = false;
foreach (var tbl in _tables.Where(x => x.IsChecked == true))
{
transfer.ObjectList.Add(source_db.Tables[tbl.Name]);
}
//use following code if want to create destination databaes runtime
ServerConnection d_conn;
if (string.IsNullOrEmpty(d_username))
{
d_conn = new ServerConnection(d_server);
}
else
{
d_conn = new ServerConnection(d_server, d_username, d_pwd);
}
Smo.Server destination_srv = new Smo.Server(d_conn);
// When database not exists backup all
if (!destination_srv.Databases.Contains(d_db))
{
// transfer.CreateTargetDatabase = true;
transfer.DestinationLoginSecure = false;
transfer.DestinationServer = d_server;
if (!string.IsNullOrEmpty(d_username) && !string.IsNullOrEmpty(d_pwd))
{
transfer.DestinationLogin = d_username;
transfer.DestinationPassword = d_pwd;
}
else
{
transfer.DestinationLoginSecure = true;
}
transfer.DestinationDatabase = d_db;
Smo.Database newdb = new Smo.Database(destination_srv, d_db);
newdb.Create();
if (!(bool)chkBackupDateRange.IsChecked)
{
transfer.ScriptTransfer();
transfer.TransferData();
}
else
{
transfer.CopySchema = true;
transfer.CopyData = false;
transfer.ScriptTransfer();
transfer.TransferData();
// has data range
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Suppress))
{
// 大量寫入
// Solve Somethiing
// http://msdn.microsoft.com/zh-tw/library/aa561924%28v=bts.10%29.aspx
// http://www.died.tw/2009/04/msdtc.html
//
using (SqlConnection bulk_conn = new SqlConnection(this._target_connstring))
{
// step 1 check target tables
List<string> tableList = new List<string>();
try
{
bulk_conn.Open();
}
catch (SqlException exp)
{
throw new InvalidOperationException("Data could not be read", exp);
}
SqlCommand cmd = new SqlCommand();
cmd.Connection = bulk_conn;
cmd.CommandText = "SELECT name FROM sys.tables WHERE is_ms_shipped = 0";
SqlDataReader dr = cmd.ExecuteReader();
tableList.Clear();
while (dr.Read())
{
tableList.Add(dr[0].ToString());
}
// Jobs 和 MCS 一定要有所以排除 下面會直接跑。
tableList.Remove("Jobs");
tableList.Remove("MCS");
dr.Close();
bulk_conn.Close();
// TODO: data always full
Dictionary<string, DataTable> dts = new Dictionary<string, DataTable>();
using (SqlConnection c = new SqlConnection(this._source_connstring))
{
c.Open();
// 1. 先取得 Jobs 完成 Jobs 轉移 要先撈Job才知道時間 所以流程為
// get Jobs -> get MCS -> write MCS -> write Jobs
string query_filter_datarange = string.Format("SELECT * FROM Jobs Where Date Between '{0}' and '{1}'", dpFrom.SelectedDate.Value.ToShortDateString(), dpTo.SelectedDate.Value.ToShortDateString());
using (SqlDataAdapter da = new SqlDataAdapter(query_filter_datarange, c))
{
dts["Jobs"] = new DataTable();
da.Fill(dts["Jobs"]);
}
var jobKeys = from job in dts["Jobs"].AsEnumerable()
select job.Field<int>("klKey");
string condiction = string.Join(",", jobKeys.Select(j => j.ToString()).ToArray());
// 後面都是跟著這個條件
condiction = condiction.Trim();
// 2-0. 因為 Jobs KEY 綁 MCS 所以要先撈MCS
string query_mcs = string.Format("Select * From MCS Where pkMCS IN ({0})",condiction);
using (SqlDataAdapter da = new SqlDataAdapter(query_mcs, c))
{
dts["MCS"] = new DataTable();
da.Fill(dts["MCS"]);
}
// write MCS
using (SqlBulkCopy mySbc = new SqlBulkCopy(this._target_connstring, SqlBulkCopyOptions.KeepIdentity))
{
// bulk_conn.Open();
//設定
mySbc.BatchSize = 10000; //批次寫入的數量
mySbc.BulkCopyTimeout = 60; //逾時時間
//處理完後丟出一個事件,或是說處理幾筆後就丟出事件
//mySbc.NotifyAfter = DTableList.Rows.Count;
//mySbc.SqlRowsCopied += new SqlRowsCopiedEventHandler(mySbc_SqlRowsCopied);
// 更新哪個資料表
mySbc.DestinationTableName = "MCS";
foreach (var item in dts["MCS"].Columns.Cast<DataColumn>())
{
mySbc.ColumnMappings.Add(item.ColumnName, item.ColumnName);
}
//開始寫入
mySbc.WriteToServer(dts["MCS"]);
//完成交易
//scope.Complete();
}
// write Jobs
using (SqlBulkCopy mySbc = new SqlBulkCopy(this._target_connstring, SqlBulkCopyOptions.KeepIdentity))
{
// bulk_conn.Open();
//設定
mySbc.BatchSize = 10000; //批次寫入的數量
mySbc.BulkCopyTimeout = 60; //逾時時間
//處理完後丟出一個事件,或是說處理幾筆後就丟出事件
//mySbc.NotifyAfter = DTableList.Rows.Count;
//mySbc.SqlRowsCopied += new SqlRowsCopiedEventHandler(mySbc_SqlRowsCopied);
// 更新哪個資料表
mySbc.DestinationTableName = "Jobs";
foreach (var item in dts["Jobs"].Columns.Cast<DataColumn>())
{
mySbc.ColumnMappings.Add(item.ColumnName, item.ColumnName);
}
//開始寫入
mySbc.WriteToServer(dts["Jobs"]);
//完成交易
//scope.Complete();
}
// 2. 撈出所有選取的 Table 欄位 為了判斷誰有 klJobKey fkJobKey
foreach (var tableName in tableList)
{
dts[tableName] = new DataTable();
string query = "";
using (SqlConnection c1 = new SqlConnection(this._source_connstring))
{
// 2-1. 先撈出所有欄位
SqlCommand command = new SqlCommand();
command.Connection = c1;
string query_column = string.Format("Select * From syscolumns Where id=OBJECT_ID(N'{0}')", tableName);
command.CommandText = query_column;
c1.Open();
string fkName = "";
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
string cName = reader["name"].ToString();
if (cName == "fkJobKey")
{
fkName = "fkJobKey";
break;
}
if (cName == "klJobKey")
{
fkName = "klJobKey";
break;
}
}
// 2-2. 判斷欄位有FK的SQL query statement 加入條件。
if (fkName == "fkJobKey")
{
query = string.Format("Select * From [{0}] Where fkJobKey IN ({1}) ", tableName, condiction);
}
else if (fkName == "klJobKey")
{
query = string.Format("Select * From [{0}] Where klJobKey IN ({1}) ", tableName, condiction);
}
else
{
query = string.Format("Select * From [{0}]", tableName);
}
}
using (SqlDataAdapter da = new SqlDataAdapter(query, c1))
{
da.Fill(dts[tableName]);
}
}
// 3. 如果 FK 有 Job Key 過濾移除
/// || dts[tableName].Columns.Contains("klJobKey")
//if (dts[tableName].Columns.Contains("fkJobKey"))
//{
// // var rows = dts[tableName].Select("fkJobKey in ")
// var dealtable = from tbl in dts[tableName].AsEnumerable()
// select tbl.Field<int>("fkJobKey");
// var filtration = dealtable.Except(jobKeys);
// var rows = from tbl in dts[tableName].AsEnumerable()
// where filtration.Any(f => f == tbl.Field<int>("fkJobKey"))
// select tbl;
// foreach (DataRow r in rows.ToArray())
// {
// dts[tableName].Rows.Remove(r);
// }
//}
//if (dts[tableName].Columns.Contains("klJobKey"))
//{
// // var rows = dts[tableName].Select("fkJobKey in ")
// var dealtable = from tbl in dts[tableName].AsEnumerable()
// select tbl.Field<int>("klJobKey");
// var filtration = dealtable.Except(jobKeys);
// var rows = from tbl in dts[tableName].AsEnumerable()
// where filtration.Any(f => f == tbl.Field<int>("klJobKey"))
// select tbl;
// foreach (DataRow r in rows.ToArray())
// {
// dts[tableName].Rows.Remove(r);
// }
//}
// 4. 跑轉移
using (SqlBulkCopy mySbc = new SqlBulkCopy(this._target_connstring, SqlBulkCopyOptions.KeepIdentity))
{
// bulk_conn.Open();
//設定
mySbc.BatchSize = 10000; //批次寫入的數量
mySbc.BulkCopyTimeout = 60; //逾時時間
//處理完後丟出一個事件,或是說處理幾筆後就丟出事件
//mySbc.NotifyAfter = DTableList.Rows.Count;
//mySbc.SqlRowsCopied += new SqlRowsCopiedEventHandler(mySbc_SqlRowsCopied);
// 更新哪個資料表
mySbc.DestinationTableName = tableName;
foreach (var item in dts[tableName].Columns.Cast<DataColumn>())
{
mySbc.ColumnMappings.Add(item.ColumnName, item.ColumnName);
}
//開始寫入
mySbc.WriteToServer(dts[tableName]);
}
}
}
}
//完成交易
scope.Complete();
}
}
MessageBox.Show("Done");
}
else
{
// database exists but:
//
#region * situation 1 : no [table] no [data range]
// step 1. 一樣用transfer傳遞結構開表格
// transfer.CreateTargetDatabase = true;
transfer.DestinationLoginSecure = false;
transfer.DestinationServer = d_server;
if (!string.IsNullOrEmpty(d_username) && !string.IsNullOrEmpty(d_pwd))
{
transfer.DestinationLogin = d_username;
transfer.DestinationPassword = d_pwd;
}
else
{
transfer.DestinationLoginSecure = true;
}
transfer.DestinationDatabase = d_db;
// step 2 判斷哪些 Table 沒有的 先靠 transfer 開
int intTableToMove = transfer.ObjectList.Count;
using (SqlConnection connCheckTable = new SqlConnection(this._target_connstring))
{
connCheckTable.Open();
SqlCommand cmdCheckTable = new SqlCommand();
cmdCheckTable.Connection = connCheckTable;
cmdCheckTable.CommandText = "SELECT name FROM sys.tables WHERE is_ms_shipped = 0";
// target 已經有的 Table 先拉掉
using (var dr = cmdCheckTable.ExecuteReader())
{
while (dr.Read())
{
transfer.ObjectList.Remove(source_db.Tables[dr[0].ToString()]);
}
}
}
// 表示完全沒有 Table 空有DB 也沒有日期限制 就直接全部複製。
if (transfer.ObjectList.Count == 0 && !((bool)chkBackupDateRange.IsChecked))
{
transfer.ScriptTransfer();
transfer.TransferData();
}
else //否則就把target不存在的Table都開完 資料再一次處理
{
transfer.CopySchema = true;
transfer.CopyData = false;
transfer.ScriptTransfer();
// transfer.TransferData();
//step 3. 開始搬資料 分成有DateRange和沒有的
if (!((bool)chkBackupDateRange.IsChecked)) //沒有時間條件的話
{
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Suppress))
{
using (SqlConnection bulk_conn = new SqlConnection(this._target_connstring))
{
// step 1 check target tables
List<string> tableList = new List<string>();
try
{
bulk_conn.Open();
}
catch (SqlException exp)
{
throw new InvalidOperationException("Data could not be read", exp);
}
SqlCommand cmd = new SqlCommand();
cmd.Connection = bulk_conn;
cmd.CommandText = "SELECT name FROM sys.tables WHERE is_ms_shipped = 0";
SqlDataReader dr = cmd.ExecuteReader();
tableList.Clear();
while (dr.Read())
{
tableList.Add(dr[0].ToString());
}
// Jobs 和 MCS 一定要有所以排除 下面會直接跑。
//tableList.Remove("Jobs");
//tableList.Remove("MCS");
dr.Close();
bulk_conn.Close();
// TODO: 2013/4/13 把所有 Dictionary 換成 Dataset
// step 2 取得所有target上的資料。
DataSet dsTarget = new DataSet(); // 取得 Target上面所有的 Table資料
Dictionary<string, int> keyTarget = new Dictionary<string, int>();// 取得Target 上面 有資料的 Table 最後一個KEY 作為換KEY的起始值
foreach (string tableName in tableList)
{
string queryGetTargetNowData = string.Format("Select * From {0}", tableName);
using (SqlDataAdapter da = new SqlDataAdapter(queryGetTargetNowData, bulk_conn))
{
da.FillSchema(dsTarget, SchemaType.Source, tableName);
da.Fill(dsTarget, tableName);
}
string keyColumnName = dsTarget.Tables[tableName].Columns[0].ColumnName;
// TODO: 取得FK的加入ForeignKeyConstraint限制
foreach (var i in source_db.Tables[tableName].ForeignKeys)
{
string x = i.ToString();
}
// dsTarget.Tables[tableName].Constraints
int targetJobsLastKey = dsTarget.Tables[tableName].AsEnumerable().LastOrDefault().Field<int>(keyColumnName);
if (targetJobsLastKey != 0)
{
keyTarget.Add(tableName, targetJobsLastKey);
}
}
// step 3. Target上面有資料的就換KEY 先把大家的KEY都換一輪 再來改參考的FK
//foreach (KeyValuePair<string, int> key in keyTarget)
//{
// int startKey = key.Value;
// foreach (DataRow item in dtsTarget[key.Key].Rows)
// {
// startKey++;
// item[0] = startKey;
// }
//}
// step 4 . 大家都換完KEY 開始換 FK
// step 5 . 換完 FK 排順序寫入 MCS -> JOBS -> etc
}
}
}
else // 有加時間範圍的
{
}
}
#endregion
// * situation 2 : no [table] exists [data range]
// * situation 3 : exists [table] no [data range]
// * situation 4 : exists [table] exists [data range]
}
//Smo.Server srv = new Smo.Server();
//// really you would get these from config or elsewhere:
////srv.ConnectionContext.Login = "******";
////srv.ConnectionContext.Password = "******";
//srv.ConnectionContext.ServerInstance = @"(localdb)\v11.0";
//string dbName = "TEST";
//Smo.Database db = new Smo.Database();
//db = srv.Databases[dbName];
//StringBuilder sb = new StringBuilder();
//List<SechmaModel> sms = new List<SechmaModel>();
//foreach (Smo.Table tbl in db.Tables)
//{
// SechmaModel model = new SechmaModel();
// Smo.ScriptingOptions options = new Smo.ScriptingOptions();
// options.ClusteredIndexes = true;
// options.Default = true;
// options.DriAll = true;
// options.Indexes = true;
// options.IncludeHeaders = true;
// StringCollection coll = tbl.Script(options);
// foreach (string str in coll)
// {
// sb.Append(str);
// sb.Append(Environment.NewLine);
// }
//}
//System.IO.StreamWriter fs = System.IO.File.CreateText("c:\\temp\\output.txt");
//fs.Write(sb.ToString());
//fs.Close();
//Step2. Build Table & Save FK Table
//Step3. SQL Statement
}