C# SqlConnection.ChangeDatabase方法代码示例

        protected string LookUpEmailBasedOnPhoneName()
        {
            string phone = TextBox3.Text;
            string name = TextBox2.Text;

            try
            {
                SqlConnection connection = new SqlConnection("Data Source = itksqlexp8; Integrated Security = true");
                connection.Open();
                connection.ChangeDatabase("itk485nnrs");
                string query =
                            "Select email from signuptable where cellPhone = '"+phone+"' and firstname='"+name+"'";
                SqlCommand command = new SqlCommand(query, connection);

                var reader = command.ExecuteReader();
                if (reader.Read())
                {
                    emailFromSignUpTable = reader["email"]+"";
                    connection.Close();
                }
                reader.Close();
            }
            catch(Exception ex)
            {
                Response.Write("Some error occured: "+ex.Message);
            }
            return emailFromSignUpTable;
        }

        public void TestCleanup()
        {
            var repositoryType = Instance.GetType();

            if (repositoryType == typeof(JsonRepository))
            {
                if (Directory.Exists(JsonWorkingFolder))
                {
                    Directory.Delete(JsonWorkingFolder, recursive: true);
                }
            }
            else if (repositoryType == typeof(SqlRepository))
            {
                using (var cnn = new SqlConnection(SqlConnectionString))
                using (var cmd = new SqlCommand("EXEC sp_MSforeachtable @command1 = 'TRUNCATE TABLE ?'", cnn))
                {
                    cnn.Open();
                    cnn.ChangeDatabase(SqlDatabaseName);
                    cmd.ExecuteNonQuery();
                }
            }
            else if (repositoryType == typeof(MongoRepository))
            {
                var server = new MongoClient(MongoConnectionString).GetServer();
                server.DropDatabase(MongoDatabaseName);
            }
        }

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                populateCatagories();
                Session["prevAList"] = "";
                Session["prevBList"] = "";
                        SqlConnection dbConnection = new SqlConnection("Data Source=itksqlexp8;Integrated Security=true");
                //string img= "../UserWebForms/";
                dbConnection.Open();
                 dbConnection.ChangeDatabase("nasa_SilentAuction_v1");
                //string sqlQuery = "select * from item where approvalStatus=" +true;
                SqlCommand cmd = dbConnection.CreateCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "select * from item where approvalStatus= 1";


                //string strQuery = "select * from item where approvalStatus=1";
                //SqlCommand cmd = new SqlCommand(strQuery, dbConnection);

                cmd.CommandType = CommandType.Text;
                //cmd.CommandText = CommandText.te
                cmd.Connection = dbConnection;

                DataTable dt = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                // SqlDataReader pendingListDisplay = cmd.ExecuteReader();
                da.Fill(dt);
                DataList1.DataSource = dt;
                DataList1.DataBind();
                dbConnection.Close();

            }
        }

        protected void Page_Load(object sender, EventArgs e)
        {
            // Important: If didn't check this, the list will get re-populated (and the 1st item will be the selected one)!
            if (!IsPostBack) 
            {
                        SqlConnection dbConnection = new SqlConnection("Data Source=itksqlexp8;Integrated Security=true");

                dbConnection.Open();
               dbConnection.ChangeDatabase("nasa_SilentAuction_v1");
                string SQLString = "SELECT DISTINCT categoryName,categoryPrefix FROM category";

                SqlCommand cmd = new SqlCommand(SQLString, dbConnection);

               

                SqlDataReader ddlValues;
                ddlValues = cmd.ExecuteReader();

                DropDownList1.DataSource = ddlValues;
                DropDownList1.DataValueField = "categoryPrefix";
                DropDownList1.DataTextField = "categoryName";
                DropDownList1.DataBind();

                dbConnection.Close();

            }
        }

        public static SqlConnection GetConnection(string sConnection, string sDatabase)
        {
            SqlConnection dbconnection = null;

            try
            {
                dbconnection = new SqlConnection(sConnection);
                dbconnection.Open();

                 if (sDatabase.Trim() != string.Empty)
                {
                    dbconnection.ChangeDatabase(sDatabase);
                }
            }
            catch (System.InvalidOperationException e)
            {
                throw new Exception("GetConnection error: " + e.Message);
            }

            catch (System.Data.SqlClient.SqlException e)
            {
                throw new Exception("GetConnection error2: " + e.Message);
            }

            return dbconnection;
        }

        protected void BindDataToUserList()
        {
            try
            {
                        SqlConnection dbConnection = new SqlConnection("Data Source=itksqlexp8;Integrated Security=true");
                dbConnection.Open();
               dbConnection.ChangeDatabase("nasa_SilentAuction_v1");

                String searchString = "select distinct buyerid from item where buyerid is not null";
                SqlCommand SQLString = new SqlCommand(searchString, dbConnection);
                SqlDataAdapter da = new SqlDataAdapter(SQLString);
                DataTable dt = new DataTable();
                da.Fill(dt);
                DropDownList1.DataSource = dt;
                DropDownList1.DataValueField = "buyerid";
                DropDownList1.DataTextField = "buyerid";
                DropDownList1.DataBind();
                dbConnection.Close();

                //Adding "Please select" option in dropdownlist
                DropDownList1.Items.Insert(0, new System.Web.UI.WebControls.ListItem("Select a buyer", ""));
            }
            catch (SqlException exception)
            {
                System.Diagnostics.Debug.WriteLine("<p>Error code " + exception.Number + ": " + exception.Message + "</p>");
            }
        }

        /// <summary>
        /// becareful
        /// </summary>
        /// <param name="connectionString"></param>
        /// <returns></returns>
        public static Boolean dropDB(String connectionString="")
        {
            try
            {
                SqlConnectionStringBuilder pa = new SqlConnectionStringBuilder(connectionString);
                using (SqlConnection sqlconnection = new
            SqlConnection(connectionString))
                {
                    sqlconnection.Open();
                    // if you used master db as Initial Catalog, there is no need to change database
                    sqlconnection.ChangeDatabase("master");

                    string rollbackCommand = @"ALTER DATABASE ["+pa.InitialCatalog+"] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE";

                    SqlCommand deletecommand = new SqlCommand(rollbackCommand, sqlconnection);

                    deletecommand.ExecuteNonQuery();

                    string deleteCommand = @"DROP DATABASE [" + pa.InitialCatalog + "]";

                    deletecommand = new SqlCommand(deleteCommand, sqlconnection);

                    deletecommand.ExecuteNonQuery();
                    return true;
                }
            }
            catch (Exception ex)
            {
                Debug.WriteLine(ex);
                return false;
            }
        }

        private void buttonConnections_Click(object sender, EventArgs e)
        {
            SqlConnection objCon = new SqlConnection();

            /** option 1: The "HardCoded" way **/
            objCon.ConnectionString = @"Data Source=.\SQLEXPRESS;Initial Catalog = DB1; Integrated Security=True;";

            /** option 2: The "Before .Net v2.0" way**/
            objCon.ConnectionString = ConfigurationSettings.AppSettings["Conn2"];

            /** option 3: The "Common" way  (Note: You must have a reference to the System.Configuration .dll) **/
            objCon.ConnectionString = ConfigurationManager.ConnectionStrings["Conn3"].ConnectionString;

            objCon.StateChange += new StateChangeEventHandler(objCon_StateChange);
            try
            {
                objCon.Open();
                objCon.ChangeDatabase("Northwind");
                MessageBox.Show("Database is now: " + objCon.Database);

            }
            finally
            {
                objCon.Close();
            }
            MessageBox.Show("Database state after Close(): " + objCon.State.ToString());
        }

        void populateDropDown()
        {
                    SqlConnection dbConnection = new SqlConnection("Data Source=itksqlexp8;Integrated Security=true");

            try
            {
                dbConnection.Open();
                dbConnection.ChangeDatabase("nasa_SilentAuction_v1");
                String query = "SELECT * FROM USERS WHERE approvalStatus=1";
                SqlCommand cmd = new SqlCommand(query, dbConnection);

                SqlDataReader data = cmd.ExecuteReader();
                while (data.Read())
                {
                    ListItem u = new ListItem();
                    u.Text = (string)data["userID"];
                    u.Value = (string)data["userID"];
                    buyerIdList.Items.Add(u);
                }
            }
            catch (SqlException exception)
            {
                Response.Write("Error Code: " + exception.Message);
            }
        }

        protected void getBidderDetails()
        {
            string userid = Session["user_id"].ToString();
            try
            {
                        SqlConnection dbConnection = new SqlConnection("Data Source=itksqlexp8;Integrated Security=true");
                dbConnection.Open();
                dbConnection.ChangeDatabase("nasa_SilentAuction_v1");

                String searchString = "select * from users "
                                        + " where userId = @userID; ";

                SqlCommand SQLString = new SqlCommand(searchString, dbConnection);
                SQLString.Parameters.AddWithValue("@userID", userid);

                SqlDataReader idRecords = SQLString.ExecuteReader();
                if (idRecords.Read())
                {
                    bidderName.Text = idRecords["firstName"].ToString().ToUpper();
                    bidderNumber.Text = idRecords["bidder_no"].ToString();
                }

                idRecords.Close();
                dbConnection.Close();
            }
            catch (SqlException exception)
            {
                Response.Write("<p>Error code " + exception.Number
                         + ": " + exception.Message + "</p>");

            }

        }

        protected bool checkIfItemSoldToThisUser()
        {
            bool itemSold = false;
            string userid = Session["user_id"].ToString();
            try
            {
                        SqlConnection dbConnection = new SqlConnection("Data Source=itksqlexp8;Integrated Security=true");
                dbConnection.Open();
                dbConnection.ChangeDatabase("nasa_SilentAuction_v1");

                String searchString = "select * from item where buyerID = @userID";

                SqlCommand SQLString = new SqlCommand(searchString, dbConnection);
                SQLString.Parameters.AddWithValue("@userID", userid);

                SqlDataReader idRecords = SQLString.ExecuteReader();
                if (idRecords.Read())
                {
                    itemSold = true;
                }

                idRecords.Close();
                dbConnection.Close();
            }
            catch (SqlException exception)
            {
                Response.Write("<p>Error code " + exception.Number + ": " + exception.Message + "</p>");
            }
            return itemSold;
        }

        protected void updateUserProfile(object sender, EventArgs e)
        {

                    SqlConnection dbConnection = new SqlConnection("Data Source=itksqlexp8;Integrated Security=true");
            try
            {
                dbConnection.Open();
                dbConnection.ChangeDatabase("nasa_SilentAuction_v1");
                string name = Session["user_id"] as string;
             str = "Update users set firstName='" + fname.Text + "', lastName='" + lname.Text + "', addressLine1='" + addressLine1.Text + "', addressLine2='" + addressLine2.Text + "', city='" + city.Text + "', stateName='" + state.Text + "', phoneHome='" + phoneNumber.Text + "', phncarrier='" + networkProvider.SelectedValue + "', email='" + email.Text + "', password='" + Password1.Text + "' WHERE userID='" + userid1.Text + "'";
                SqlCommand sqlCommand = new SqlCommand(str, dbConnection);
              var x =   sqlCommand.ExecuteNonQuery();


                string message = "Bingo! your profile has been Updated. For confirmation we have sent you an email, Happy Bidding :)" ;
                textMessage = new SMSService(networkProvider.Text, phoneNumber.Text, message);
                textMessage.SendTextMessage(textMessage);


                UpdateReply.Text = "Data Updated Successfully, Please check your inbox :)";
               
            }
            catch (SqlException exception)
            {
                Response.Write("<p>Error code " + exception.Number
                    + ": " + exception.Message + "</p>");
            }
        }

        static void Main(string[] args)
        {
            String sc = "Data Source=.\\sqlexpress;Initial Catalog=MASTER;Integrated Security=true;";

            using (SqlConnection c = new SqlConnection(sc))
            {
                String cmd = "CREATE DATABASE VS2010";

                using (SqlCommand k = new SqlCommand(cmd, c))
                {
                    //SqlCommand faz parte do modelo CONECTADO
                    //ExecuteNonQuery retorna o número de linhas afetadas

                    c.Open();

                    k.ExecuteNonQuery();

                    c.ChangeDatabase("VS2010");

                    k.CommandText = "CREATE TABLE PESSOA (COD_PESSOA INT, NOME_PESSOA VARCHAR(50), SEXO_PESSOA CHAR(1))";

                    k.ExecuteNonQuery();

                    c.Close();
                }
            }

            Console.WriteLine("funcionou");

            Console.ReadKey();
        }

        protected void LookUpForAdminAccess()
        {
            string adminAccess = "";

            try
            {
                SqlConnection connection = new SqlConnection("Data Source = itksqlexp8; Integrated Security = true");
                connection.Open();
                connection.ChangeDatabase("itk485nnrs");
                string tempEmail = Session["loginEmail"].ToString() ;
                string query =
                            "Select access from signuptable where email = '" + tempEmail + "'";
                SqlCommand command = new SqlCommand(query, connection);

                var reader = command.ExecuteReader();
                if (reader.Read())
                {
                    adminAccess = reader["access"] + "";
                    connection.Close();
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                Response.Write("Some error occured: " + ex.Message);
            }
            Session["adminAccess"] = adminAccess;
        }

 protected void Page_Load(object sender, EventArgs e)
 {
     if (Page.IsPostBack)
     {
         Page.Validate();
         if (Page.IsValid)
         {
             SqlConnection dbConnection = new SqlConnection("Data Source=.\\SQLEXPRESS;Integrated Security=true");
             try
             {
                 dbConnection.Open();
                 dbConnection.ChangeDatabase("ConservationSchool");
                 string SQLString = "SELECT * FROM students WHERE studentID=" + studentID.Text;
                 SqlCommand checkIDTable = new SqlCommand(SQLString, dbConnection);
                 SqlDataReader idRecords = checkIDTable.ExecuteReader();
                 if (idRecords.Read())
                 {
                     Response.Redirect("ReturningStudent.aspx?studentID=" + studentID.Text);
                     idRecords.Close();
                 }
                 else
                 {
                     validateMessage.Text = "<p>**Invalid student ID**</p>";
                     idRecords.Close();
                 }
             }
             catch (SqlException exception)
             {
                 Response.Write("<p>Error code " + exception.Number
                     + ": " + exception.Message + "</p>");
             }
         }
     }
 }