C# SqlConnectionStringBuilder.ToString方法代码示例

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

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

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

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

        }

        /// <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 = "123user!";

            ecsb.ProviderConnectionString = sqlcsb.ToString();

            result = new Apps4KidsEntities(ecsb.ConnectionString);
            return result;
        }

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

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

        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 [email protected]"res://*/ShoeShopEntity.csdl|res://*/ShoeShopEntity.ssdl|res://*/ShoeShopEntity.msl";

            entity = new SHOESHOPEntities(entityBuilder.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();
 }