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);
}
private void EnsureConnectionIsAlive(ref SqlServerConnection connection, string query)
{
try {
using (var command = connection.CreateCommand()) {
command.CommandText = query;
command.ExecuteNonQuery();
}
}
catch (Exception exception) {
if (SqlHelper.ShouldRetryOn(exception))
{
SqlLog.Warning(exception, Strings.LogGivenConnectionIsCorruptedTryingToRestoreTheConnection);
if (!TryReconnect(ref connection, query))
{
SqlLog.Error(exception, Strings.LogConnectionRestoreFailed);
throw;
}
SqlLog.Info(Strings.LogConnectionSuccessfullyRestored);
}
else
{
throw;
}
}
}
private static bool IsAzure(SqlServerConnection connection)
{
using (var command = connection.CreateCommand()) {
command.CommandText = "SELECT @@VERSION";
return(((string)command.ExecuteScalar()).Contains("Azure"));
}
}
private static async Task <List <dynamic> > GetRows(
string query,
Func <QC.SqlDataReader, dynamic> rowConverter)
{
var result = new List <dynamic>();
var connectionString = Environment.GetEnvironmentVariable("GIFTS_DB_ADO_NET_CONNECTION_STRING");
using (var connection = new QC.SqlConnection(connectionString))
{
await connection.OpenAsync();
using (var command = new QC.SqlCommand())
{
command.Connection = connection;
command.CommandType = DT.CommandType.Text;
command.CommandText = query;
QC.SqlDataReader reader = await command.ExecuteReaderAsync();
while (reader.Read())
{
result.Add(rowConverter(reader));
}
}
}
return(result);
}
public static async Task <MicrosoftData.SqlConnection> CreateMicrosoftDataSqlConnectionAsync()
{
var sqlConnection = new MicrosoftData.SqlConnection(TestConfiguration.SqlConnectionString);
await sqlConnection.OpenAsync();
return(sqlConnection);
}
public static int CUD(string strsql)
{
using (SqlConnection conn = new SqlConnection(strconn))
{
conn.Open();
SqlCommand comm = new SqlCommand(strsql, conn);
return(comm.ExecuteNonQuery());
}
}
public static async Task TruncateTransactionalOutboxTableAsync(this MicrosoftData.SqlConnection sqlConnection)
{
//CLEAR the Table for Integration Tests to validate:
await using var sqlCmd = new MicrosoftData.SqlCommand(
SqlCommands.TruncateTransactionalOutbox,
sqlConnection
);
await sqlCmd.ExecuteNonQueryAsync().ConfigureAwait(false);
}
public static DataTable GetTable(string strsql)
{
using (SqlConnection conn = new SqlConnection(strconn))
{
var dt = new DataTable();
SqlDataAdapter msda = new SqlDataAdapter(strsql, conn);
msda.Fill(dt);
return(dt);
}
}
public static int CUD_Proc(string pname, SqlParameter[] paras = null)
{
using (SqlConnection conn = new SqlConnection(strconn))
{
SqlCommand comm = new SqlCommand(pname, conn);
comm.CommandType = CommandType.StoredProcedure;
if (paras != null)
{
comm.Parameters.AddRange(paras);
}
return(comm.ExecuteNonQuery());
}
}
// 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);
}
private static Microsoft.Data.SqlClient.SqlBulkCopy GetSqlBulkCopy(Microsoft.Data.SqlClient.SqlConnection sqlConnection, IDbContextTransaction transaction, BulkConfig config)
{
var sqlBulkCopyOptions = config.SqlBulkCopyOptions;
if (transaction == null)
{
return(new Microsoft.Data.SqlClient.SqlBulkCopy(sqlConnection, sqlBulkCopyOptions, null));
}
else
{
var sqlTransaction = (Microsoft.Data.SqlClient.SqlTransaction)transaction.GetUnderlyingTransaction(config);
return(new Microsoft.Data.SqlClient.SqlBulkCopy(sqlConnection, sqlBulkCopyOptions, sqlTransaction));
}
}
public static DataTable GetTable_Proc(string pname, SqlParameter[] paras = null)
{
using (SqlConnection conn = new SqlConnection(strconn))
{
var dt = new DataTable();
SqlCommand comm = new SqlCommand(pname, conn);
comm.CommandType = CommandType.StoredProcedure;
if (paras != null)
{
comm.Parameters.AddRange(paras);
}
SqlDataAdapter msda = new SqlDataAdapter(comm);
msda.Fill(dt);
return(dt);
}
}
private async Task OpenWithCheckAsync(string checkQueryString, CancellationToken cancellationToken)
{
bool connectionChecked = false;
bool restoreTriggered = false;
while (!connectionChecked)
{
cancellationToken.ThrowIfCancellationRequested();
await base.OpenAsync(cancellationToken).ConfigureAwait(false);
try {
using (var command = underlyingConnection.CreateCommand()) {
command.CommandText = checkQueryString;
await command.ExecuteNonQueryAsync(cancellationToken).ConfigureAwait(false);
}
connectionChecked = true;
}
catch (Exception exception)
{
if (SqlHelper.ShouldRetryOn(exception))
{
if (restoreTriggered)
{
SqlLog.Error(exception, Strings.LogConnectionRestoreFailed);
throw;
}
SqlLog.Warning(exception, Strings.LogGivenConnectionIsCorruptedTryingToRestoreTheConnection);
var newConnection = new SqlServerConnection(underlyingConnection.ConnectionString);
try
{
underlyingConnection.Close();
underlyingConnection.Dispose();
}
catch { }
underlyingConnection = newConnection;
restoreTriggered = true;
continue;
}
else
{
throw;
}
}
}
}
// 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 void OpenWithCheck(string checkQueryString)
{
bool connectionChecked = false;
bool restoreTriggered = false;
while (!connectionChecked)
{
base.Open();
try {
using (var command = underlyingConnection.CreateCommand()) {
command.CommandText = checkQueryString;
command.ExecuteNonQuery();
}
connectionChecked = true;
}
catch (Exception exception) {
if (SqlHelper.ShouldRetryOn(exception))
{
if (restoreTriggered)
{
SqlLog.Error(exception, Strings.LogConnectionRestoreFailed);
throw;
}
SqlLog.Warning(exception, Strings.LogGivenConnectionIsCorruptedTryingToRestoreTheConnection);
var newConnection = new SqlServerConnection(underlyingConnection.ConnectionString);
try
{
underlyingConnection.Close();
underlyingConnection.Dispose();
}
catch { }
underlyingConnection = newConnection;
restoreTriggered = true;
continue;
}
else
{
throw;
}
}
}
}
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);
}
private static ErrorMessageParser CreateMessageParser(SqlServerConnection connection)
{
bool isEnglish;
using (var command = connection.CreateCommand()) {
command.CommandText = "SELECT @@LANGID";
isEnglish = command.ExecuteScalar().ToString() == "0";
}
var templates = new Dictionary <int, string>();
using (var command = connection.CreateCommand()) {
command.CommandText = MessagesQuery;
using (var reader = command.ExecuteReader())
while (reader.Read())
{
templates.Add(reader.GetInt32(0), reader.GetString(1));
}
}
return(new ErrorMessageParser(templates, isEnglish));
}
private static async Task <int> ExecuteUpdate(string update, Dictionary <string, object> parameters)
{
var connectionString = Environment.GetEnvironmentVariable("GIFTS_DB_CONNECTION_STRING");
using (var connection = new QC.SqlConnection(connectionString))
{
await connection.OpenAsync();
using (var command = new QC.SqlCommand())
{
command.Connection = connection;
command.CommandType = DT.CommandType.Text;
command.CommandText = update;
foreach (var item in parameters)
{
command.Parameters.AddWithValue(item.Key, item.Value);
}
return(await command.ExecuteNonQueryAsync());
}
}
}
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);
}
}
public partial SomeType WithEncryptionMicrosoftSql(MSqlConnection connection, int id, string name);
public SubjectRepositoryQuery(IConfiguration configuration)
{
_context = new SqlConnection(configuration["ConnectionStrings:QueryConnection"]);
}
//private static bool TryReconnect(ref SqlServerConnection connection, string checkConnectionQuery)
//{
// try {
// var newConnection = new SqlServerConnection(connection.ConnectionString);
// try {
// connection.Close();
// connection.Dispose();
// }
// catch { }
// connection = newConnection;
// connection.Open();
// using (var command = connection.CreateCommand()) {
// command.CommandText = checkConnectionQuery;
// command.ExecuteNonQuery();
// }
// return true;
// }
// catch (Exception) {
// return false;
// }
//}
// Constructors
public Connection(SqlDriver driver, bool checkConnection)
: base(driver)
{
underlyingConnection = new SqlServerConnection();
checkConnectionIsAlive = checkConnection;
}
