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());
}
}
}
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;
}
}
}
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;
}
}
}
public bool Test()
{
_cnn = GetAuxiliarCnnString();
if (string.IsNullOrEmpty(_cnn.DataSource))
{
MessageBox.Show("Ingrese servidor de SQL.-", Fwk.GuidPk.Properties.Resources.ProductTitle);
cmbServer.Focus();
return(false);
}
if (string.IsNullOrEmpty(_cnn.InitialCatalog))
{
MessageBox.Show("Seleccione o ingrese una base de datos.-", Fwk.GuidPk.Properties.Resources.ProductTitle);
cmbDataBases.Focus();
return(false);
}
if (!_cnn.WindowsAutentification)
{
if (string.IsNullOrEmpty(_cnn.User))
{
MessageBox.Show("Ingrese usuario.-", Fwk.GuidPk.Properties.Resources.ProductTitle);
txtUserName.Focus();
return(false);
}
}
SqlConnection sqlConnection = new SqlConnection(_cnn.ToString());
Microsoft.SqlServer.Management.Common.ServerConnection serverConnection =
new Microsoft.SqlServer.Management.Common.ServerConnection(sqlConnection);
try
{
_Server = new Server(serverConnection);
//_Server.Databases[_cnn.InitialCatalog].Tables.Refresh();
////iterate over all Databases
//foreach (Database db in _Server.Databases)
//{
MessageBox.Show("Coneccion exitosa.- a " + _Server.Information.Product.ToString(), Fwk.GuidPk.Properties.Resources.ProductTitle);
//}
}
catch (Exception ex)
{
MessageBox.Show(Fwk.CodeGenerator.HelperFunctions.GetAllMessageException(ex), Fwk.GuidPk.Properties.Resources.ProductTitle);
return(false);
}
return(true);
}
private static Program RetrieveSqlPath()
{
using (var connection = new SqlConnection("Data Source=.;Integrated Security=SSPI"))
{
var serverConnection = new Microsoft.SqlServer.Management.Common.ServerConnection(connection);
var server = new Server(serverConnection);
return(new Program {
defaultDataPath = string.IsNullOrEmpty(server.Settings.DefaultFile) ? server.MasterDBPath : server.Settings.DefaultFile,
defaultLogPath = string.IsNullOrEmpty(server.Settings.DefaultLog) ? (string)server.MasterDBLogPath : (string)server.Settings.DefaultLog
});
}
}
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 btnTestConnection_Click(object sender, EventArgs e)
{
_cnn = GetAuxiliarCnnString();
if (string.IsNullOrEmpty(_cnn.DataSource))
{
MessageBox.Show("Ingrese servidor de SQL.-", "Fwk wizard");
cmbServer.Focus();
return;
}
if (string.IsNullOrEmpty(_cnn.InitialCatalog))
{
MessageBox.Show("Seleccione o ingrese una base de datos.-", "Fwk wizard");
cmbDataBases.Focus();
return;
}
if (!_cnn.WindowsAutentification)
{
if (string.IsNullOrEmpty(_cnn.User))
{
MessageBox.Show("Ingrese usuario.-", "Fwk wizard");
txtUserName.Focus();
return;
}
}
SqlConnection sqlConnection = new SqlConnection(_cnn.ToString());
Microsoft.SqlServer.Management.Common.ServerConnection serverConnection =
new Microsoft.SqlServer.Management.Common.ServerConnection(sqlConnection);
try
{
_Server = new Server(serverConnection);
////iterate over all Databases
foreach (Database db in _Server.Databases)
{
MessageBox.Show("Coneccion exitosa.-", "Fwk wizard");
return;
}
}
catch (Exception ex)
{
MessageBox.Show(HelperFunctions.GetAllMessageException(ex), "Fwk wizard");
}
}
internal DbVersioning Connect()
{
DbConnection = new Microsoft.SqlServer.Management.Common.ServerConnection(parameters["DataSource"], parameters["User"], parameters["Password"]);
// server
DbServer = new Microsoft.SqlServer.Management.Smo.Server(DbConnection);
// database
DbName = DbServer.Databases[parameters["Database"]];
DbScripter = new Scripter(DbServer);
return(this);
}
private JobScheduleData ExtractScheduleDataFromXml(XmlDocument xmlDoc)
{
JobScheduleData jobscheduledata = new JobScheduleData();
string stringNewScheduleMode = null;
string serverName = String.Empty;
string scheduleUrn = String.Empty;
STParameters param = new STParameters();
bool bStatus = true;
param.SetDocument(xmlDoc);
bStatus = param.GetParam("servername", ref serverName);
bStatus = param.GetParam("urn", ref scheduleUrn);
bStatus = param.GetParam("itemtype", ref stringNewScheduleMode);
if ((stringNewScheduleMode != null) && (stringNewScheduleMode.Length > 0))
{
return(jobscheduledata); // new schedule
}
Microsoft.SqlServer.Management.Common.ServerConnection connInfo =
new Microsoft.SqlServer.Management.Common.ServerConnection(serverName);
Enumerator en = new Enumerator();
Request req = new Request();
req.Urn = scheduleUrn;
DataTable dt = en.Process(connInfo, req);
if (dt.Rows.Count == 0)
{
return(jobscheduledata);
}
DataRow dr = dt.Rows[0];
jobscheduledata.Enabled = Convert.ToBoolean(dr["IsEnabled"], System.Globalization.CultureInfo.InvariantCulture);
jobscheduledata.Name = Convert.ToString(dr["Name"], System.Globalization.CultureInfo.InvariantCulture);
jobscheduledata.FrequencyTypes = (FrequencyTypes)Convert.ToInt32(dr["FrequencyTypes"], System.Globalization.CultureInfo.InvariantCulture);
jobscheduledata.FrequencyInterval = Convert.ToInt32(dr["FrequencyInterval"], System.Globalization.CultureInfo.InvariantCulture);
jobscheduledata.FrequencySubDayTypes = (FrequencySubDayTypes)Convert.ToInt32(dr["FrequencySubDayTypes"], System.Globalization.CultureInfo.InvariantCulture);
jobscheduledata.FrequencyRelativeIntervals = (FrequencyRelativeIntervals)Convert.ToInt32(dr["FrequencyRelativeIntervals"], System.Globalization.CultureInfo.InvariantCulture);
jobscheduledata.FrequencyRecurranceFactor = Convert.ToInt32(dr["FrequencyRecurrenceFactor"], System.Globalization.CultureInfo.InvariantCulture);
jobscheduledata.ActiveStartDate = Convert.ToDateTime(dr["ActiveStartDate"], System.Globalization.CultureInfo.InvariantCulture);
jobscheduledata.ActiveEndDate = Convert.ToDateTime(dr["ActiveEndDate"], System.Globalization.CultureInfo.InvariantCulture);
return(jobscheduledata);
}
public void sFillListAvailableDatabasesSqlAuth(string pServerName, string pLogIn = "", string pPassword = "")
{
try {
Microsoft.SqlServer.Management.Common.ServerConnection vConn = new Microsoft.SqlServer.Management.Common.ServerConnection(pServerName, pLogIn, pPassword);
Microsoft.SqlServer.Management.Smo.Server vServer = new Microsoft.SqlServer.Management.Smo.Server(vConn);
vConn.Connect();
foreach (Database vDB2 in vServer.Databases)
{
TXT_Database.Items.Add(vDB2.Name);
}
} catch (Exception vEx) {
return;
}
}
/// <summary>
/// Chạy lệnh sql đầy đủ (execution full client-side SQL scripts)
/// </summary>
/// <param name="script"></param>
/// <param name="dbName"></param>
public static void ExecuteSqlScript(string script, string dbName)
{
try
{
_sqlConnection = CreateConnection("");
_sqlConnection.ChangeDatabase(dbName);
var serverConnection = new Microsoft.SqlServer.Management.Common.ServerConnection(_sqlConnection);
var server = new Microsoft.SqlServer.Management.Smo.Server(serverConnection);
server.ConnectionContext.ExecuteNonQuery(script);
}
catch (Exception ex)
{
throw ex;
}
finally
{
_sqlConnection.Close();
}
}
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();
}
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();
}
public bool fTestConnection(bool pReturnMsg = true)
{
try {
Microsoft.SqlServer.Management.Common.ServerConnection vConn = new Microsoft.SqlServer.Management.Common.ServerConnection(TXT_ServerName.Text, TXT_Login.Text, TXT_Password.Text);
Microsoft.SqlServer.Management.Smo.Server vServer = new Microsoft.SqlServer.Management.Smo.Server(vConn);
vConn.Connect();
if (pReturnMsg)
{
if (System.Threading.Thread.CurrentThread.CurrentCulture.TwoLetterISOLanguageName == "ar")
{
MessageBox.Show("تم اختبار الوصله بنجاح", "??????");
}
else if (System.Threading.Thread.CurrentThread.CurrentCulture.TwoLetterISOLanguageName == "en")
{
MessageBox.Show("Test connection succeeded", "Test");
}
}
return(true);
} catch (Exception ex) {
MessageBox.Show(ex.Message, "Error");
return(false);
}
}
private void btnTestConnection_Click(object sender, EventArgs e)
{
_cnn = GetAuxiliarCnnString();
if (string.IsNullOrEmpty(_cnn.DataSource))
{
MessageBox.Show("Ingrese servidor de SQL.-", "Fwk wizard");
cmbServer.Focus();
return;
}
if (string.IsNullOrEmpty(_cnn.InitialCatalog))
{
MessageBox.Show("Seleccione o ingrese una base de datos.-","Fwk wizard");
cmbDataBases.Focus();
return;
}
if (!_cnn.WindowsAutentification)
{
if (string.IsNullOrEmpty(_cnn.User))
{
MessageBox.Show("Ingrese usuario.-", "Fwk wizard");
txtUserName.Focus();
return;
}
}
SqlConnection sqlConnection = new SqlConnection(_cnn.ToString());
Microsoft.SqlServer.Management.Common.ServerConnection serverConnection =
new Microsoft.SqlServer.Management.Common.ServerConnection(sqlConnection);
try
{
_Server = new Server(serverConnection);
////iterate over all Databases
foreach (Database db in _Server.Databases)
{
MessageBox.Show("Coneccion exitosa.-", "Fwk wizard");
return;
}
}
catch (Exception ex)
{
MessageBox.Show(HelperFunctions.GetAllMessageException(ex), "Fwk wizard");
}
}
public bool Test()
{
_cnn = GetAuxiliarCnnString();
if (string.IsNullOrEmpty(_cnn.DataSource))
{
MessageBox.Show("Ingrese servidor de SQL.-", Fwk.GuidPk.Properties.Resources.ProductTitle);
cmbServer.Focus();
return false;
}
if (string.IsNullOrEmpty(_cnn.InitialCatalog))
{
MessageBox.Show("Seleccione o ingrese una base de datos.-", Fwk.GuidPk.Properties.Resources.ProductTitle);
cmbDataBases.Focus();
return false;
}
if (!_cnn.WindowsAutentification)
{
if (string.IsNullOrEmpty(_cnn.User))
{
MessageBox.Show("Ingrese usuario.-", Fwk.GuidPk.Properties.Resources.ProductTitle);
txtUserName.Focus();
return false;
}
}
SqlConnection sqlConnection = new SqlConnection(_cnn.ToString());
Microsoft.SqlServer.Management.Common.ServerConnection serverConnection =
new Microsoft.SqlServer.Management.Common.ServerConnection(sqlConnection);
try
{
_Server = new Server(serverConnection);
//_Server.Databases[_cnn.InitialCatalog].Tables.Refresh();
////iterate over all Databases
//foreach (Database db in _Server.Databases)
//{
MessageBox.Show("Coneccion exitosa.- a " + _Server.Information.Product.ToString(), Fwk.GuidPk.Properties.Resources.ProductTitle);
//}
}
catch (Exception ex)
{
MessageBox.Show(Fwk.CodeGenerator.HelperFunctions.GetAllMessageException(ex), Fwk.GuidPk.Properties.Resources.ProductTitle);
return false;
}
return true;
}
