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());
}
}
}
// Method to retrieve all the models from the DB
public List <DbModelEmail> GetAllEmailDataFromDb()
{
List <DbModelEmail> list = new List <DbModelEmail>();
using (var connection = new MSDSC.SqlConnection("Server=(localdb)\\MSSQLLocalDB;Database=DBEmailScheduler;Trusted_Connection=True;"))
{
connection.Open();
if (connection.State.Equals(SD.ConnectionState.Open))
{
using (var command = new MSDSC.SqlCommand())
{
command.Connection = connection;
command.CommandType = SD.CommandType.Text;
command.CommandText = @"
SELECT * FROM dbo.tb_emails;
" ;
MSDSC.SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
bool IsOpened = (reader.GetInt32(2) != 0) ? true : false;
bool IsFirstEmailSent = (reader.GetInt32(3) != 0) ? true : false;
DbModelEmail dbModelEmail = new DbModelEmail(reader.GetString(1), IsOpened, IsFirstEmailSent, reader.GetInt32(4));
list.Add(dbModelEmail);
}
}
}
}
return(list);
}
public Microsoft.Data.SqlClient.SqlConnection AbrirConexion()
{
if (conex.State == ConnectionState.Closed)
{
conex.Open();
}
return(conex);
}
public static int CUD(string strsql)
{
using (SqlConnection conn = new SqlConnection(strconn))
{
conn.Open();
SqlCommand comm = new SqlCommand(strsql, conn);
return(comm.ExecuteNonQuery());
}
}
private SqlServerConnection CreateAndOpenConnection(string connectionString, SqlDriverConfiguration configuration)
{
var connection = new SqlServerConnection(connectionString);
if (!configuration.EnsureConnectionIsAlive)
{
connection.Open();
SqlHelper.ExecuteInitializationSql(connection, configuration);
return(connection);
}
var testQuery = (string.IsNullOrEmpty(configuration.ConnectionInitializationSql))
? CheckConnectionQuery
: configuration.ConnectionInitializationSql;
connection.Open();
EnsureConnectionIsAlive(ref connection, testQuery);
return(connection);
}
// Method to check if the DB is connectable/pingable
public bool IsConnectable()
{
using (var connection = new MSDSC.SqlConnection("Server=(localdb)\\MSSQLLocalDB;Database=DBEmailScheduler;Trusted_Connection=True;"))
{
connection.Open();
if (connection.State.Equals(SD.ConnectionState.Open))
{
return(true);
}
}
return(false);
}
// Method to update the DB with new model parameteres on the basis of email
public void UpdateDb(DbModelEmail model)
{
using (var connection = new MSDSC.SqlConnection("Server=(localdb)\\MSSQLLocalDB;Database=DBEmailScheduler;Trusted_Connection=True;"))
{
connection.Open();
if (connection.State.Equals(SD.ConnectionState.Open))
{
using (var command = new MSDSC.SqlCommand())
{
MSDSC.SqlParameter param;
command.Connection = connection;
command.CommandType = SD.CommandType.Text;
command.CommandText = @"
UPDATE dbo.tb_emails
SET isFirstEmailSent = @isFirstEmailSent, isOpened = @isEmailOpened, remainingReminderDays = @remainingReminderDays
WHERE emailID = @emailID" ;
int IsOpened = (model.IsOpened == true) ? 1 : 0;
int IsFirstEmailSent = (model.IsFirstEmailSent == true) ? 1 : 0;
param = new MSDSC.SqlParameter("@isFirstEmailSent", SD.SqlDbType.Int);
param.Value = IsFirstEmailSent;
command.Parameters.Add(param);
param = new MSDSC.SqlParameter("@isEmailOpened", SD.SqlDbType.Int);
param.Value = IsOpened;
command.Parameters.Add(param);
param = new MSDSC.SqlParameter("@remainingReminderDays", SD.SqlDbType.Int);
param.Value = model.RemainingReminderDays;
command.Parameters.Add(param);
param = new MSDSC.SqlParameter("@emailID", SD.SqlDbType.NVarChar);
param.Value = model.Email;
command.Parameters.Add(param);
int affectedRows = command.ExecuteNonQuery();
System.Console.WriteLine("DB Updated! Number of rows affected : " + affectedRows);
}
}
}
}
private static bool TryReconnect(ref SqlServerConnection connection, string query)
{
try {
var newConnection = new SqlServerConnection(connection.ConnectionString);
try {
connection.Close();
connection.Dispose();
}
catch { }
connection = newConnection;
connection.Open();
using (var command = connection.CreateCommand()) {
command.CommandText = query;
command.ExecuteNonQuery();
}
return(true);
}
catch (Exception)
{
return(false);
}
}
