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