public void ResetDatabase()
{
using (Log.InfoTraceMethodCall("ResetDatabase"))
{
var connectionString = config.Server.GetConnectionString(Zetbox.API.Helper.ZetboxConnectionStringKey);
Assert.That(connectionString.ConnectionString, Is.StringContaining("_test"), "test databases should be marked with '_test' in the connection string");
try
{
Log.Info("Restoring Database");
var cb = new SqlConnectionStringBuilder(connectionString.ConnectionString);
cb.InitialCatalog = "master";
Log.InfoFormat("executing on database [{0}]", cb.ToString());
ExecuteScript(cb.ToString(), "Zetbox.Tests.Utilities.MsSql.BackupRestoreTestDatabase.sql");
Log.Info("Done restoring Database");
// After recreating the database, all connection pools should be cleard
SqlConnection.ClearAllPools();
}
catch (Exception ex)
{
Log.Error("Error while restoring database", ex);
throw;
}
Log.InfoFormat("Current Directory=[{0}]", Environment.CurrentDirectory);
Log.InfoFormat("Using config from [{0}]", config.ConfigFilePath);
}
}
public static TempDatabase Create()
{
var databaseName = typeof (TempDatabase).Namespace.Replace(".", "_");
var databaseFilepath = Path.Combine("c:\\temp", "Sql.mdf");
var connectionString = new SqlConnectionStringBuilder();
connectionString.DataSource = ".\\sqlexpress";
connectionString.InitialCatalog = databaseName;
connectionString.IntegratedSecurity = true;
DatabaseInitialization.DetachDatabase(connectionString.ToString());
if (File.Exists(databaseFilepath))
File.Delete(databaseFilepath);
connectionString.InitialCatalog = "master";
using (var masterDatabaseConnection = new SqlConnection(connectionString.ToString()))
{
masterDatabaseConnection.Open();
var createSqlText = string.Format(@"
CREATE DATABASE [{0}] ON PRIMARY (NAME={0}, FILENAME='{1}')", databaseName, databaseFilepath);
using (var createCommand = new SqlCommand(createSqlText, masterDatabaseConnection))
{
createCommand.ExecuteNonQuery();
}
}
connectionString.InitialCatalog = databaseName;
return new TempDatabase() { Name = databaseName, ConnectionString = connectionString.ToString()};
}
internal async Task <ConnectionResult> CheckDatabaseAvailability(System.Data.SqlClient.SqlConnectionStringBuilder sqlConnectionStringBuilder)
{
try
{
var con = new SqlConnectionStringBuilder(sqlConnectionStringBuilder.ToString());
con.InitialCatalog = string.Empty; //Remove Initial Catalog and try to connect to database
using (var connection = new SqlConnection(con.ToString()))
{
await connection.OpenAsync();
}
try
{
//Now use original Connection String and try to connect to existing Database
using (var connection = new SqlConnection(sqlConnectionStringBuilder.ToString()))
{
await connection.OpenAsync();
}
return(ConnectionResult.DbAlreadyExists);
}
catch
{
return(ConnectionResult.Successful);
}
}
catch
{
return(ConnectionResult.Error);
}
}
static void Main()
{
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder
{
["Data Source"] = "(local)",
["integrated Security"] = true
};
SqlConnection connection = new SqlConnection(builder.ToString());
try
{
SqlCommand cmd = new SqlCommand("CREATE DATABASE MinionsDB", connection);
connection.Open();
cmd.ExecuteNonQuery();
}
catch(Exception e)
{
Console.WriteLine(e.Message);
}
finally
{
connection.Close();
}
builder["initial catalog"] = "MinionsDB";
connection = new SqlConnection(builder.ToString());
try
{
connection.Open();
string createTownsSQL = "CREATE TABLE Towns (Id INT PRIMARY KEY IDENTITY, Name VARCHAR(50), Country VARCHAR(50))";
string createMinionsSQL = "CREATE TABLE Minions (Id INT PRIMARY KEY IDENTITY, Name VARCHAR(50), Age INT, TownId INT, CONSTRAINT FK_Towns FOREIGN KEY (TownId) REFERENCES Towns(Id))";
string createVillainsSQL = "CREATE TABLE Villains (Id INT PRIMARY KEY IDENTITY, Name VARCHAR(50), EvilnessFactor VARCHAR(20))";
string createMinionsVillainsSQL = "CREATE TABLE MinionsVillains(MinionId INT, VillainId INT, CONSTRAINT FK_Minions FOREIGN KEY (MinionId) REFERENCES Minions(Id), CONSTRAINT FK_Villains FOREIGN KEY (VillainId) REFERENCES Villains(Id))";
ExecuteCommand(createTownsSQL, connection);
ExecuteCommand(createMinionsSQL, connection);
ExecuteCommand(createVillainsSQL, connection);
ExecuteCommand(createMinionsVillainsSQL, connection);
string insertTownsSQL = "INSERT INTO Towns (Name, Country) VALUES ('Sofia','Bulgaria'), ('Burgas','Bulgaria'), ('Varna', 'Bulgaria'), ('London','UK'),('Liverpool','UK'),('Ocean City','USA'),('Paris','France')";
string insertMinionsSQL = "INSERT INTO Minions (Name, Age, TownId) VALUES ('bob',10,1),('kevin',12,2),('steward',9,3), ('rob',22,3), ('michael',5,2),('pep',3,2)";
string insertVillainsSQL = "INSERT INTO Villains (Name, EvilnessFactor) VALUES ('Gru','super evil'),('Victor','evil'),('Simon Cat','good'),('Pusheen','super good'),('Mammal','evil')";
string insertMinionsVillainsSQL = "INSERT INTO MinionsVillains VALUES (1,2), (3,1),(1,3),(3,3),(4,1),(2,2),(1,1),(3,4), (1, 4), (1,5), (5, 1), (4,1), (3, 1)";
ExecuteCommand(insertTownsSQL, connection);
ExecuteCommand(insertMinionsSQL, connection);
ExecuteCommand(insertVillainsSQL, connection);
ExecuteCommand(insertMinionsVillainsSQL, connection);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
finally
{
connection.Close();
}
}
public static void DropDb(string conn) {
var cnBuilder = new SqlConnectionStringBuilder(conn);
var dbName = cnBuilder.InitialCatalog;
if (DbExists(cnBuilder.ToString())) {
cnBuilder.InitialCatalog = "master";
ExecSql(cnBuilder.ToString(), "ALTER DATABASE " + dbName + " SET SINGLE_USER WITH ROLLBACK IMMEDIATE");
ExecSql(cnBuilder.ToString(), "drop database " + dbName);
cnBuilder.InitialCatalog = dbName;
ClearPool(cnBuilder.ToString());
}
}
/// <summary>
/// Default constructor.
/// </summary>
/// <param name="intitString"></param>
public pgsql_API(string intitString)
{
// connectionstring=
string[] parameters = intitString.Replace("\r\n","\n").Split('\n');
foreach(string param in parameters){
if(param.ToLower().IndexOf("connectionstring=") > -1){
m_ConStr = param.Substring(17);
}
}
SqlConnectionStringBuilder b = new SqlConnectionStringBuilder(m_ConStr);
string database = b.InitialCatalog;
b.InitialCatalog = "";
using(NpgsqlConnection con = new NpgsqlConnection(b.ToString().ToLower().Replace("data source","server"))){
con.Open();
// See if database exists
try{
con.ChangeDatabase(database);
}
catch{
// Database don't exist, try to create it
try{
con.Close();
con.ConnectionString = b.ToString().ToLower().Replace("data source","server");
con.Open();
NpgsqlCommand cmd = new NpgsqlCommand();
cmd.Connection = con;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "create database \"" + database + "\"";
cmd.ExecuteNonQuery();
con.ChangeDatabase(database);
// Create tables
cmd.CommandText = ResManager.GetText("tables.sql",System.Text.Encoding.Default);
cmd.ExecuteNonQuery();
// Create procedures
cmd.CommandText = ResManager.GetText("procedures.sql",System.Text.Encoding.Default);
cmd.ExecuteNonQuery();
}
catch{
throw new Exception("Database '" + database + "' doesn''t exist ! Create failed, specified user doesn't have enough permisssions to create database ! Create database manually.");
}
}
}
}
private EntityConnectionStringBuilder GetEntityBuilder()
{
SqlConnectionStringBuilder sqlBuilder =
new SqlConnectionStringBuilder();
// Set the properties for the data source.
sqlBuilder.DataSource = serverName;
sqlBuilder.InitialCatalog = databaseName;
sqlBuilder.IntegratedSecurity = true;
//sqlBuilder.UserID = user;
//sqlBuilder.Password = pwd;
// Build the SqlConnection connection string.
string providerString = sqlBuilder.ToString();
// Initialize the EntityConnectionStringBuilder.
EntityConnectionStringBuilder entityBuilder =
new EntityConnectionStringBuilder();
//Set the provider name.
entityBuilder.Provider = providerName;
// Set the provider-specific connection string.
entityBuilder.ProviderConnectionString = providerString;
// Set the Metadata location.
entityBuilder.Metadata = @"res://*/MyEntities.csdl|
res://*/MyEntities.ssdl|
res://*/MyEntities.msl";
return entityBuilder;
}
/// <summary>
/// 创建只读连接字符串
/// </summary>
public static string ProcessConnectionString(string connectionStringName, bool readOnly = false)
{
var gungnir = ConfigurationManager.ConnectionStrings[connectionStringName];
if (gungnir == null)
{
return(null);
}
if (string.Compare(gungnir.ProviderName, "System.Data.SqlClient", StringComparison.OrdinalIgnoreCase) != 0)
{
return(gungnir.ConnectionString);
}
var sb = new System.Data.SqlClient.SqlConnectionStringBuilder(gungnir.ConnectionString);
if (readOnly)
{
sb.ApplicationIntent = System.Data.SqlClient.ApplicationIntent.ReadOnly;
}
sb.MultipleActiveResultSets = true;
return(sb.ToString());
}
// Build connection string to the server
// ADD your SQL MI server details into App.config,
// OR manuall edit the four string values - DataSource, InitialCatalog, UserID, Password with your SQL MI connection details
static private string GetSqlConnectionString(string instance)
{
// Prepare the connection string to Azure SQL Database.
var sqlConnectionSB = new QC.SqlConnectionStringBuilder();
// Change these values to your values.
sqlConnectionSB.DataSource = ConfigurationManager.AppSettings.Get(instance + ".server"); // Server
sqlConnectionSB.InitialCatalog = ConfigurationManager.AppSettings.Get(instance + ".database"); // Database
sqlConnectionSB.UserID = ConfigurationManager.AppSettings.Get(instance + ".username");
sqlConnectionSB.Password = ConfigurationManager.AppSettings.Get(instance + ".pass");
// Adjust these values if you like. (ADO.NET 4.5.1 or later.)
sqlConnectionSB.ConnectRetryCount = 3;
sqlConnectionSB.ConnectRetryInterval = 10; // Seconds.
// Leave these values as they are.
sqlConnectionSB.IntegratedSecurity = false;
sqlConnectionSB.Encrypt = true;
sqlConnectionSB.ConnectTimeout = 30;
// Test the connection string configuration online
// Console.WriteLine(sqlConnectionSB);
return(sqlConnectionSB.ToString());
}
/// <summary>
/// Returns a connection string that can be used to connect to the specified server and database.
/// </summary>
public static string GetConnectionString(string serverName, string database)
{
SqlConnectionStringBuilder connStr = new SqlConnectionStringBuilder(GetCredentialsConnectionString());
connStr.DataSource = serverName;
connStr.InitialCatalog = database;
return connStr.ToString();
}
public MyNetDbService () {
// Create DB Connection String
var connectionString = new SqlConnectionStringBuilder {
DataSource = databaseServer,
InitialCatalog = databaseName,
Encrypt = true,
TrustServerCertificate = false,
UserID = username,
Password = password
};
MyNetDbConnectionString = connectionString.ToString ();
///
// Inititialization Phase
// Get the languages residing in Table mynet.Language (used in the Federation)
// Calculate the ContactID and the AddressID used when inserting Contacts
// Note: Executed during the creation of the First Service Instance
///
if ( init == false ) {
mynetLanguages = getLanguages();
nextContactID = getNextContactId();
nextAddressID = getNextAddressId();
init = true;
}
} // Constructor
private static string GenerateUniqueDatabaseName(string originialConnectionString)
{
SqlConnectionStringBuilder connectionInfo = new SqlConnectionStringBuilder(originialConnectionString);
connectionInfo.InitialCatalog = String.Format(CultureInfo.InvariantCulture, "{0}_{1}", connectionInfo.InitialCatalog, Guid.NewGuid().ToString());
return connectionInfo.ToString();
}
// Implement the Initialize method to get publication
// and subscription information.
public override void Initialize(
string publisher,
string subscriber,
string distributor,
string publisherDB,
string subscriberDB,
string articleName)
{
SqlConnectionStringBuilder pubString = new SqlConnectionStringBuilder();
pubString.InitialCatalog = publisherDB;
pubString.DataSource = publisher;
pubString.IntegratedSecurity = true;
// Create a connection to the Publisher.
connection = new SqlConnection(pubString.ToString());
try
{
// Open the connection to the Publisher.
connection.Open();
}
catch (Exception ex)
{
throw new ApplicationException(Properties.Resources.PublisherConnectFailure,ex);
}
}
public static void Initialize()
{
var connectionString = ConfigurationManager.ConnectionStrings["TestDb"].ConnectionString;
var connectionBuilder = new SqlConnectionStringBuilder(connectionString);
//connect to postgres database to create a new database
var databaseName = connectionBuilder.InitialCatalog;
connectionBuilder.InitialCatalog = "master";
connectionString = connectionBuilder.ToString();
using (var conn = new SqlConnection(connectionString))
{
conn.Open();
using (var cmd = new SqlCommand())
{
cmd.CommandText = string.Format(@"
IF db_id('{0}') IS NULL
BEGIN
CREATE DATABASE {0}
END
", databaseName);
cmd.Connection = conn;
var result = cmd.ExecuteScalar();
}
DropTables(conn, databaseName);
}
}
public ActionResult Step1(SQLInstallViewModel info)
{
ViewData["StepNumber"] = 1;
if (info.UseIntegratedSecurity)
{
ModelState.Remove("DatabaseLogin");
ModelState.Remove("DatabasePassword");
}
if (!ModelState.IsValid)
return View(info);
SqlConnectionStringBuilder connectionString = new SqlConnectionStringBuilder();
connectionString.DataSource = info.DatabaseServer;
connectionString.InitialCatalog = info.DatabaseName;
connectionString.MultipleActiveResultSets = true;
connectionString.IntegratedSecurity = info.UseIntegratedSecurity; ;
if (!info.UseIntegratedSecurity)
{
connectionString.UserID = info.DatabaseLogin;
connectionString.Password = info.DatabasePassword;
}
SqlConnection connection = new SqlConnection(connectionString.ToString());
try
{
connection.Open();
string sql = System.IO.File.ReadAllText(Server.MapPath("~/App_Data/mesoBoardFresh.sql"));
string[] cmds = sql.Split(new string[] { "GO" }, StringSplitOptions.RemoveEmptyEntries);
SqlCommand command = new SqlCommand();
command.Connection = connection;
foreach (string cmd in cmds)
{
command.CommandText = cmd;
command.ExecuteNonQuery();
}
}
catch (Exception ex)
{
TempData[ViewDataKeys.GlobalMessages.Notice] = "Unable to connect to SQL server, check connection information";
TempData[ViewDataKeys.GlobalMessages.Error] = ex.Message;
return View(info);
}
finally
{
connection.Close();
connection.Dispose();
}
Session[SessionSqlInfoKey] = info;
return RedirectToAction("Step2");
}
public static UserWallet CreateDatabase(string path, string password)
{
SqlConnectionStringBuilder sb = new SqlConnectionStringBuilder();
sb.AttachDBFilename = path;
sb.DataSource = @"(LocalDB)\v11.0";
sb.IntegratedSecurity = true;
using (WalletDataContext ctx = new WalletDataContext(sb.ToString()))
using (RNGCryptoServiceProvider rng = new RNGCryptoServiceProvider())
{
byte[] passwordKey = password.ToAesKey();
byte[] masterKey = new byte[32];
byte[] iv = new byte[16];
rng.GetNonZeroBytes(masterKey);
rng.GetNonZeroBytes(iv);
masterKey.AesEncrypt(passwordKey, iv);
Array.Clear(passwordKey, 0, passwordKey.Length);
ctx.Database.Delete();
ctx.Database.Create();
ctx.Keys.Add(new Key
{
Name = Key.MasterKey,
Value = masterKey
});
ctx.Keys.Add(new Key
{
Name = Key.IV,
Value = iv
});
ctx.SaveChanges();
}
UserWallet wallet = OpenDatabase(path, password);
wallet.CreateAccount();
return wallet;
}
public static List<string> GetDatabasesOnServer(string ServerName)
{
List<String> databases = new List<String>();
SqlConnectionStringBuilder connection = new SqlConnectionStringBuilder();
connection.DataSource = ServerName;
//connection.UserID = //get username;
// connection.Password = //get password;
connection.IntegratedSecurity = true;
String strConn = connection.ToString();
//create connection
SqlConnection sqlConn = new SqlConnection(strConn);
//open connection
sqlConn.Open();
//get databases
DataTable tblDatabases = sqlConn.GetSchema("Databases");
//close connection
sqlConn.Close();
//add to list
foreach (DataRow row in tblDatabases.Rows)
{
String strDatabaseName = row["database_name"].ToString();
databases.Add(strDatabaseName);
}
return databases;
}
public static void InitDatabase(string hostname, string username, string password, string database)
{
if (!IsNullOrEmpty(m_ConnectionString))
return;
//Build an SQL connection string
var sqlString = new SqlConnectionStringBuilder()
{
DataSource = hostname,
InitialCatalog = database,
UserID = username,
Password = password,
ApplicationName = "EntityFramework",
MultipleActiveResultSets = true,
PersistSecurityInfo = true
};
//Build an entity framework connection string
var entityString = new EntityConnectionStringBuilder()
{
Provider = "System.Data.SqlClient",
Metadata = "res://*/BluffinMuffinLogs.csdl|res://*/BluffinMuffinLogs.ssdl|res://*/BluffinMuffinLogs.msl",
ProviderConnectionString = sqlString.ToString()
};
m_ConnectionString = entityString.ToString();
Task.Factory.StartNew(LogCommands);
}
public static void CreateIfNotExists(string connectionString)
{
var connectionStringBuilder = new SqlConnectionStringBuilder(connectionString);
var databaseName = connectionStringBuilder.InitialCatalog;
connectionStringBuilder.InitialCatalog = "master";
connectionStringBuilder.ConnectTimeout = 20;
using (var connection = new SqlConnection(connectionStringBuilder.ToString()))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = string.Format(@"
SELECT
name
FROM master.sys.databases
WHERE
name = '{0}'
",
databaseName);
using (var reader = command.ExecuteReader())
{
if (reader.HasRows) return;
}
command.CommandText = string.Format("CREATE DATABASE [{0}] Japanese_CI_AS", databaseName);
command.ExecuteNonQuery();
}
connection.Close();
}
}
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 SqlConnection GetConnection(string initialCatalog = null)
{
string connectionString = Configurator.ConnectionString;
if (string.IsNullOrEmpty(connectionString))
{
string connectionStringName = _docmahConfiguration.ConnectionStringName;
try
{
connectionString = ConfigurationManager.ConnectionStrings[connectionStringName].ConnectionString;
}
catch (NullReferenceException ex)
{
throw new InvalidOperationException("The DocMAH connection string has not been set. Set the connection string using either the docmah element connectionStringName attribute in web.config, or in DocMAHConfig.Configure and call Configure from Application_Start in Global.asax.", ex);
}
}
// Override configuration values based on parameters.
if (!string.IsNullOrEmpty(initialCatalog))
{
var builder = new SqlConnectionStringBuilder(connectionString);
builder.InitialCatalog = initialCatalog;
connectionString = builder.ToString();
}
return new SqlConnection(connectionString);
}
public static void CreateDb(string conn)
{
var cnBuilder = new SqlConnectionStringBuilder(conn);
string dbName = cnBuilder.InitialCatalog;
cnBuilder.InitialCatalog = "master";
ExecSql(cnBuilder.ToString(), "CREATE DATABASE " + dbName);
}
private static void DropAllTable()
{
var sb = new SqlConnectionStringBuilder(ConnectionString);
var initialCatalog = sb.InitialCatalog;
sb.InitialCatalog = "master";
var cn = new SqlConnection(sb.ToString());
try
{
cn.Open();
using (var cmd = cn.CreateCommand())
{
cmd.CommandText = string.Format(@"select * from sys.databases where name = '{0}'", initialCatalog);
cmd.CommandType = CommandType.Text;
using (var reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
Database.MigrateDown(ConnectionString);
}
}
}
}
catch
{
// do nothing.
}
finally
{
cn.Dispose();
}
}
private void btnAddNewStock_Click(object sender, EventArgs e)
{
tb_log.Text = string.Empty;
SqlConnection conn = new SqlConnection();
SqlConnectionStringBuilder sb = new SqlConnectionStringBuilder();
sb.DataSource = tb_server.Text;
sb.InitialCatalog = tb_database.Text;
if (cb_integratedSecurity.Checked == true)
{
sb.IntegratedSecurity = true;
}
else
{
sb.IntegratedSecurity = false;
sb.UserID = tb_username.Text;
sb.Password = tb_password.Text;
}
conn.ConnectionString = sb.ToString();
tb_log.AppendText(DateTime.Now + " - Processing: " + tb_newSymbol.Text + "\n");
StockNames stockNames = new StockNames(conn);
Stock s = new Stock();
s.symbol = tb_newSymbol.Text;
s.name = tb_newName.Text;
s.exchange = cb_exchange.SelectedItem.ToString();
stockNames.CreateNewStock(s);
tb_log.AppendText(DateTime.Now + " - Processed: " + tb_newSymbol.Text + "\n");
tb_log.AppendText(DateTime.Now + " - ***FINISHED PROCESSING***");
}
public DBConnection()
{
// Specify the provider name, server and database.
// Initialize the connection string builder for the
// underlying provider.
SqlConnectionStringBuilder sqlBuilder =
new SqlConnectionStringBuilder();
// Set the properties for the data source.
sqlBuilder.DataSource = serverName;
sqlBuilder.InitialCatalog = databaseName;
sqlBuilder.IntegratedSecurity = true;
// Build the SqlConnection connection string.
string providerString = sqlBuilder.ToString();
// Initialize the EntityConnectionStringBuilder.
EntityConnectionStringBuilder entityBuilder =
new EntityConnectionStringBuilder();
//Set the provider name.
entityBuilder.Provider = providerName;
// Set the provider-specific connection string.
entityBuilder.ProviderConnectionString = providerString;
// Set the Metadata location.
entityBuilder.Metadata =@"res://*/ShoeShopEntity.csdl|res://*/ShoeShopEntity.ssdl|res://*/ShoeShopEntity.msl";
entity = new SHOESHOPEntities(entityBuilder.ToString());
}
public DbConnection CreateDbConnection(DbConnectionParameters dbConnectionParameters)
{
var connectionStringBuilder = new SqlConnectionStringBuilder
{
DataSource = dbConnectionParameters.DbServerUrl,
InitialCatalog = dbConnectionParameters.DbInstanceName,
IntegratedSecurity = true,
MultipleActiveResultSets = true
};
if (!dbConnectionParameters.IsOsUser)
{
connectionStringBuilder.UserID = dbConnectionParameters.ConnectionUserName;
connectionStringBuilder.Password = dbConnectionParameters.ConnectionUserPassword;
connectionStringBuilder.IntegratedSecurity = false;
}
if (dbConnectionParameters.IsLocalDatabase)
{
connectionStringBuilder.AttachDBFilename = String.Format("{0}\\{1}.mdf", dbConnectionParameters.LocalDbFileCatalog, dbConnectionParameters.DbInstanceName);
}
return new SqlConnection(connectionStringBuilder.ToString());
}
private static bool CreateAndStoreConnectionString()
{
using (var dialog = CreateDataConnectionDialog())
{
// If you want the user to select from any of the available data sources, do this:
// DataSource.AddStandardDataSources(dialog);
// OR, if you want only certain data sources to be available
// (e.g. only SQL Server), do something like this instead:
dialog.DataSources.Add(DataSource.SqlDataSource);
//dialog.DataSources.Add(DataSource.SqlFileDataSource);
// The way how you show the dialog is somewhat unorthodox; `dialog.ShowDialog()`
// would throw a `NotSupportedException`. Do it this way instead:
DialogResult userChoice = DataConnectionDialog.Show(dialog);
// Return the resulting connection string if a connection was selected:
if (userChoice == DialogResult.OK)
{
var sqlb = new SqlConnectionStringBuilder(dialog.ConnectionString);
// sqlb.Password = string.Empty;
Properties.Settings.Default["ConnectionString"] = sqlb.ToString();
Properties.Settings.Default.Save();
return true;
}
return false;
}
}
static void Main(string[] args)
{
MongoConnectionStringBuilder builder = new MongoConnectionStringBuilder();
builder.Server = new MongoServerAddress("localhost", 27017);
builder.DatabaseName = "douban";
MongoServer mongo = MongoServer.Create(builder);
mongo.Connect();
var db = mongo.GetDatabase("douban");
SqlConnectionStringBuilder sqlconnbuilder = new SqlConnectionStringBuilder();
sqlconnbuilder.DataSource = "localhost\\s2012";
sqlconnbuilder.InitialCatalog = "douban";
sqlconnbuilder.IntegratedSecurity = true;
SqlConnection sql = new SqlConnection(sqlconnbuilder.ToString());
sql.Open();
var cmd = sql.CreateCommand();
InsertGroup(mongo, db, cmd);
//InsertGroupPost(mongo, db, cmd);
}
public DatabaseInitializer(string connectionString)
{
var csb = new System.Data.SqlClient.SqlConnectionStringBuilder(connectionString);
_targetDatabase = csb.InitialCatalog;
csb.InitialCatalog = "master";
this._masterConnectionString = csb.ToString();
}
/// <summary>
/// Creates a database on the server.
/// </summary>
/// <param name="sqlProviderConnectionString">Connection string</param>
/// <returns>Error</returns>
private string CreateDatabase(string sqlProviderConnectionString)
{
try
{
//parse database name
var builder = new SqlConnectionStringBuilder(sqlProviderConnectionString);
var databaseName = builder.InitialCatalog;
//now create connection string to 'master' dabatase. It always exists.
builder.InitialCatalog = "master";
var masterCatalogConnectionString = builder.ToString();
string query = string.Format("CREATE DATABASE [{0}] COLLATE SQL_Latin1_General_CP1_CI_AS", databaseName);
using (var conn = new SqlConnection(masterCatalogConnectionString))
{
conn.Open();
using (var command = new SqlCommand(query, conn))
{
command.ExecuteNonQuery();
}
}
return string.Empty;
}
catch (Exception ex)
{
return string.Format("An error occured when creating database: {0}", ex.Message);
}
}
/// <summary>
/// Gets the context.
/// </summary>
/// <returns></returns>
public static Apps4KidsEntities GetContext()
{
Apps4KidsEntities result;
if (connectionString != null)
{
return new Apps4KidsEntities(connectionString);
}
EntityConnectionStringBuilder ecsb = new EntityConnectionStringBuilder();
SqlConnectionStringBuilder sqlcsb = new SqlConnectionStringBuilder();
ecsb.Metadata = @"res://*/DataModel.csdl|
res://*/DataModel.ssdl|
res://*/DataModel.msl";
ecsb.Provider = "System.Data.SqlClient";
sqlcsb.InitialCatalog = "Apps4Kids";
sqlcsb.DataSource = "localhost";
sqlcsb.UserID = "sa";
sqlcsb.Password = "******";
ecsb.ProviderConnectionString = sqlcsb.ToString();
result = new Apps4KidsEntities(ecsb.ConnectionString);
return result;
}
public static bool CreateDatabase(string collation)
{
if (DatabaseIsExisted()) return false;
var settings = IoC.Resolve<DataSettings>();
var builder = new SqlConnectionStringBuilder(connectionString ?? settings.ConnectionString);
var dbName = builder.InitialCatalog;
builder.InitialCatalog = "master";
var masterCatalogConnectionString = builder.ToString();
string query = string.Format("CREATE DATABASE [{0}]", dbName);
if (!String.IsNullOrWhiteSpace(collation))
query = string.Format("{0} COLLATE {1}", query, collation);
using (var conn = new SqlConnection(masterCatalogConnectionString))
{
conn.Open();
using (var sqlCmd = new SqlCommand(query, conn))
{
sqlCmd.ExecuteNonQuery();
}
}
_databaseIsExisted = true;
Thread.Sleep(3000);
return true;
}
private void delClick(object sender, EventArgs e)
{
if (MessageBox.Show("Are you sure you want to delete this match?", "Delete", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
Button del = sender as Button;
int i = Array.IndexOf(delete, del);
SqlConnectionStringBuilder connString = new SqlConnectionStringBuilder();
SqlCommand cmd = new SqlCommand();
connString.AttachDBFilename = "C:\\Users\\wolfdhallg\\Documents\\Visual Studio 2013\\Projects\\tryouts1.1\\tryouts1.1\\Database1.mdf";
connString.DataSource = "(LocalDB)\\v11.0";
connString.IntegratedSecurity = true;
SqlConnection conn = new SqlConnection(connString.ToString());
cmd.CommandType = CommandType.Text;
cmd.CommandText = "DELETE Matches WHERE ID = " + matchID[i].ToString();
cmd.Connection = conn;
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
refreshPanel();
}
}
public DatabaseInitializer(string connectionString)
{
var csb = new System.Data.SqlClient.SqlConnectionStringBuilder(connectionString);
_targetDatabase = csb.InitialCatalog;
csb.InitialCatalog = "master";
this._masterConnectionString = csb.ToString();
}
public static string getInputConnection(string SQLserver, string InitialCatalog)
{
SqlConnectionStringBuilder connStringBuilder = new System.Data.SqlClient.SqlConnectionStringBuilder();
connStringBuilder["Data Source"] = SQLserver;
connStringBuilder["Initial Catalog"] = InitialCatalog;
connStringBuilder["integrated Security"] = true;
return(connStringBuilder.ToString());
}
/// <summary>
/// Metodo que genera una nueva conexion
/// </summary>
/// <returns></returns>
private SqlConnection GetConnection()
{
string connection = ConfigurationManager.ConnectionStrings["milano"].ConnectionString;
System.Data.SqlClient.SqlConnectionStringBuilder csb = new System.Data.SqlClient.SqlConnectionStringBuilder(connection);
csb.Password = Encriptar.DesencriptarCadena(csb.Password);
conn = new SqlConnection(csb.ToString());
conn.Open();
return(conn);
}
public void Drop(EJ.Databases database)
{
System.Data.SqlClient.SqlConnectionStringBuilder conStrBuilder = new System.Data.SqlClient.SqlConnectionStringBuilder(database.conStr);
var mydbName = conStrBuilder.InitialCatalog;
conStrBuilder.InitialCatalog = "master";
var db = EntityDB.DBContext.CreateDatabaseService(conStrBuilder.ToString(), EntityDB.DatabaseType.SqlServer);
db.ExecSqlString("if exists(select [dbid] from sysdatabases where [name]='" + mydbName.ToLower() + "') drop database " + mydbName.ToLower());
}
public SqlConnection OpenConection(string instances, string dataBase)
{
var connStrBldr = new System.Data.SqlClient.SqlConnectionStringBuilder();
connStrBldr.DataSource = instances;
connStrBldr.InitialCatalog = dataBase;
connStrBldr.IntegratedSecurity = true;
SqlConexion = new SqlConnection(connStrBldr.ToString());
SqlConexion.Open();
return(SqlConexion);
}
private void button2_Click(object sender, EventArgs e) //delete db
{
var serverName = ".";
Server svr = new Server(serverName);
var dbToDrop = comboBoxDatabases.SelectedItem.ToString();//textBoxDBToDrop.Text;
List <Database> databasesToDelete = new List <Database>();
foreach (Database db in svr.Databases) //delete all databases with entered name
{
if (db.Name == dbToDrop)
{
databasesToDelete.Add(db);
}
}
databasesToDelete.ForEach(x =>
{
if (x.ActiveConnections > 0) //close all connections and drop db
{
string connectionString = svr.ConnectionContext.ToString();
System.Data.SqlClient.SqlConnectionStringBuilder builder = new System.Data.SqlClient.SqlConnectionStringBuilder();
builder["Data Source"] = ".";
builder["integrated Security"] = true;
builder["Initial Catalog"] = dbToDrop;
SqlConnection sqlConn = new SqlConnection(builder.ToString());
Microsoft.SqlServer.Management.Common.ServerConnection serverConn = new Microsoft.SqlServer.Management.Common.ServerConnection(sqlConn);
Server svrSql = new Server(serverConn);
sqlConn.Open();
String sqlCOmmandText = @"
USE master
ALTER DATABASE " + dbToDrop + @" SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE [" + dbToDrop + "]";
SqlCommand sqlCommand = new SqlCommand(sqlCOmmandText, sqlConn);
sqlCommand.ExecuteNonQuery();
sqlConn.Close();
}
else
{
x.Drop();
}
});
comboBoxDatabases.Items.Clear();
populateDatabases();
}
public static string getConnectionStringUDL()
{
System.Data.SqlClient.SqlConnectionStringBuilder sqlString = new System.Data.SqlClient.SqlConnectionStringBuilder()
{
ConnectionString = objDataLayer.ConnectionString,
IntegratedSecurity = true,
MultipleActiveResultSets = true
};
//Build an Entity Framework connection string
System.Data.Entity.Core.EntityClient.EntityConnectionStringBuilder entityString = new System.Data.Entity.Core.EntityClient.EntityConnectionStringBuilder()
{
Provider = (objDataLayer.ProviderName == "SqlClient" ? "System.Data.SqlClient" : objDataLayer.ProviderName),
Metadata = "res://*/GruposEtareosModel.csdl|res://*/GruposEtareosModel.ssdl|res://*/GruposEtareosModel.msl",
ProviderConnectionString = sqlString.ToString()
};
return(entityString.ConnectionString);
}
public void WriteConnectionString(string path, System.Data.SqlClient.SqlConnectionStringBuilder connectionString)
{
XmlDocument XmlDoc = new XmlDocument();
//Loading the Config file
//AppDomain.CurrentDomain.SetupInformation.ConfigurationFile
XmlDoc.Load(path);
foreach (XmlElement xElement in XmlDoc.DocumentElement)
{
if (xElement.Name == "connectionStrings")
{
//setting the conection string
xElement.FirstChild.Attributes[1].Value = connectionString.ToString();
}
}
//writing the connection string in config file
XmlDoc.Save(path);
}
public void Create(EJ.Databases database)
{
System.Data.SqlClient.SqlConnectionStringBuilder conStrBuilder = new System.Data.SqlClient.SqlConnectionStringBuilder(database.conStr);
var mydbName = conStrBuilder.InitialCatalog;
conStrBuilder.InitialCatalog = "master";
var db = EntityDB.DBContext.CreateDatabaseService(conStrBuilder.ToString(), EntityDB.DatabaseType.SqlServer);
/*
* COLLATE Chinese_PRC_CI_AS ，如果没有这句，linux的sql server中文会乱码
* 指定SQL server的排序规则
* Chinese_PRC指的是中国大陆地区，如果是台湾地区则为Chinese_Taiwan
* CI指定不区分大小写，如果要在查询时区分输入的大小写则改为CS
* AS指定区分重音，同样如果不需要区分重音，则改为AI
* COLLATE可以针对整个数据库更改排序规则，也可以单独修改某一个表或者某一个字段的排序规则，指定排序规则很有用，比如用户管理表，需要验证输入的用户名和密码的正确性，一般是要区分大小写的。
*/
db.ExecSqlString("if not exists(select [dbid] from sysdatabases where [name]='" + mydbName.ToLower() + "') create database " + mydbName.ToLower() + " COLLATE Chinese_PRC_CI_AS");
db = EntityDB.DBContext.CreateDatabaseService(database.conStr, EntityDB.DatabaseType.SqlServer);
CreateEasyJobTable(db);
//ado.ExecCommandTextUseSameCon("if not exists(select [dbid] from sysdatabases where [name]='" + txt_databasename.Text + "') create database " + txt_databasename.Text);
}
static private string GetSqlConnectionString()
{
// Prepare the connection string to Azure SQL Database.
var sqlConnectionSB = new QC.SqlConnectionStringBuilder();
// Change these values to your values.
sqlConnectionSB.DataSource = "tcp:rathoreserver.database.windows.net,1433"; //["Server"]
sqlConnectionSB.InitialCatalog = "stock2data"; //["Database"]
sqlConnectionSB.UserID = "aditya"; // "@yourservername" as suffix sometimes.
sqlConnectionSB.Password = "******";
sqlConnectionSB.IntegratedSecurity = false;
// Adjust these values if you like. (ADO.NET 4.5.1 or later.)
sqlConnectionSB.ConnectRetryCount = 3;
sqlConnectionSB.ConnectRetryInterval = 10; // Seconds.
// Leave these values as they are.
sqlConnectionSB.IntegratedSecurity = false;
sqlConnectionSB.Encrypt = true;
sqlConnectionSB.ConnectTimeout = 30;
return(sqlConnectionSB.ToString());
}
