C# SqlConnection.Close方法代码示例

            //Test SQL Connection
            public static bool TestConnection()
            {
                SqlConnection con = new SqlConnection(ConnectionString);
                con.Open();

                if (con.State == ConnectionState.Open)
                {
                    con.Close();
                    return true;
                }
                else
                {
                    con.Close();
                    return false;
                }
            }

        protected void Fill_User_Header()
        {
            DataView view = null;
            SqlConnection con;
            SqlCommand cmd = new SqlCommand();
            DataSet ds     = new DataSet();
            DataTable dt   = new DataTable();
            System.Configuration.Configuration rootWebConfig = System.Web.Configuration.WebConfigurationManager.OpenWebConfiguration("/BitOp");
            System.Configuration.ConnectionStringSettings connString;
            connString = rootWebConfig.ConnectionStrings.ConnectionStrings["BopDBConnectionString"];
            con            = new SqlConnection(connString.ToString());
            cmd.Connection = con;
            con.Open();
            string sql = @"SELECT Fecha_Desde, Inicio_Nombre, Region, Supervisor
                         FROM Criterios
                         WHERE Criterio_ID = " + @Criterio_ID;
            SqlDataAdapter da = new SqlDataAdapter(sql, con);
            da.Fill(ds);
            dt = ds.Tables[0];
            view = new DataView(dt);
            foreach (DataRowView row in view)
            {
                Lbl_Fecha_Desde.Text    = row["Fecha_Desde"].ToString("dd-MM-yyyy");
                Lbl_Inicio_Descrip.Text = row["Inicio_Nombre"].ToString();
                Lbl_Region.Text         = row["Region"].ToString();
                Lbl_Supervisor.Text     = row["Supervisor"].ToString();
             }

            con.Close();
        }

        private void submitButtonH_Click(object sender, EventArgs e)
        {
            //If this is valid then close the panel and show the info
               //Check if this barcode exists in the database
               //This code is the same in ever single station other than what data it should pull
               SqlConnection connection = new SqlConnection(Properties.Settings.Default.TigerCheckProductionConnectionString);
               SqlCommand ifExists = new SqlCommand("IF Exists(Select 1 From TigerCheckProduction.dbo.PatientRecords WHERE [ID_Num] = @barcodeNum) Select 1 Else Select 0", connection);
               ifExists.Parameters.AddWithValue("@barcodeNum", barcodeTextBox.Text);
               connection.Open();
               if (Convert.ToInt32(ifExists.ExecuteScalar()) == 1)
               {

                    barcodePanel.Visible = false;
                    //Since it exists, make a new patient object and load the data

                    patient.getPatientData(Convert.ToInt32(barcodeTextBox.Text));

                    Hearing.Text = patient.Hearing.ToString();
               }
               else
               {
                    MessageBox.Show("Invalid barcode number");
               }
               connection.Close();
        }

        /// <summary>
        /// Tests that using the same connection string results in the same pool\internal connection and a different string results in a different pool\internal connection
        /// </summary>
        /// <param name="connectionString"></param>
        private static void BasicConnectionPoolingTest(string connectionString)
        {
            SqlConnection connection = new SqlConnection(connectionString);
            connection.Open();
            InternalConnectionWrapper internalConnection = new InternalConnectionWrapper(connection);
            ConnectionPoolWrapper connectionPool = new ConnectionPoolWrapper(connection);
            connection.Close();

            SqlConnection connection2 = new SqlConnection(connectionString);
            connection2.Open();
            Assert.True(internalConnection.IsInternalConnectionOf(connection2), "New connection does not use same internal connection");
            Assert.True(connectionPool.ContainsConnection(connection2), "New connection is in a different pool");
            connection2.Close();

            SqlConnection connection3 = new SqlConnection(connectionString + ";App=SqlConnectionPoolUnitTest;");
            connection3.Open();
            Assert.False(internalConnection.IsInternalConnectionOf(connection3), "Connection with different connection string uses same internal connection");
            Assert.False(connectionPool.ContainsConnection(connection3), "Connection with different connection string uses same connection pool");
            connection3.Close();

            connectionPool.Cleanup();
            SqlConnection connection4 = new SqlConnection(connectionString);

            connection4.Open();
            Assert.True(internalConnection.IsInternalConnectionOf(connection4), "New connection does not use same internal connection");
            Assert.True(connectionPool.ContainsConnection(connection4), "New connection is in a different pool");
            connection4.Close();
        }

 public void Method(string connection, string command)
 {
     SqlConnection conn = new SqlConnection(connection);
     SqlCommand comm = new SqlCommand(command);
     //Do something
     conn.Close();
 }

		public static async Task<List<string>> GetPickerData (string make)
		{
               List<string> result = new List<string>();

               using (var conn = new SqlConnection("Server=tcp:willies.database.windows.net,1433;Database=Willies Database;User [email protected];Password=Williescycles1;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"))
               {
                    try
                    {
                         conn.Open();
                    }
                    catch (Exception e)
                    {
                         
                    }

                    SqlDataReader myReader = null;
                    SqlCommand myCommand = new SqlCommand("SELECT DISTINCT YR FROM Parts Where Make LIKE 'H%'", conn);
                    myReader = myCommand.ExecuteReader();

                    while (myReader.Read())
                    {
                         result.Add(myReader.GetString(0));
                    }

                    conn.Close();

               }
               return result;
		}

 //use for getting a single integer data
 public static int GetID(string SelectStatement)
 {
     SqlConnection con = new SqlConnection(ConnectionString);
     con.Open();
     SqlCommand com = new SqlCommand(SelectStatement, con);
     int id = Convert.ToInt32(com.ExecuteScalar());
     con.Close();
     return id;
 }

            //use for UPDATE, DELETE
            public static void Execute(string SqlStatement)
            {
                SqlConnection con = new SqlConnection(ConnectionString);
                con.Open();

                SqlCommand com = new SqlCommand(SqlStatement, con);

                com.ExecuteNonQuery();
                con.Close(); //dagdag
            }

        private static void NextConnection(ref SqlConnection con, Randomizer rand)
        {
            if (con != null)
            {
                con.Close();
            }

            string connString = _connectionStrings[rand.Next(_connectionStrings.Length)];

            con = new SqlConnection(connString);
            con.Open();
        }

            // Get list of all of the Users
            public static List<User> listAll()
            {
                try
                {
                    List<User> users = new List<User>();
                    SqlConnection sqlConnection1 = new SqlConnection(Globals.connectionString);
                    SqlCommand cmd = new SqlCommand();
                    SqlDataReader reader;

                    // Possible columns: sid, status, createdate, updatedate, accdate, totcpu, totio, spacelimitl, timelimit, resultlimit, name, dbname, password, denylogin, hasaccess, instname, isntgroup, isntuser, sysadmin, securityadmin, serveradmin, setupadmin, processadmin, diskadmin,dbcreator, bulkadmin, loginname
                    cmd.CommandText = @"SELECT sl.sid, sl.status, sl.name, sl.createdate, sl.updatedate, sl.accdate FROM sys.syslogins sl
                                    join sys.sql_logins sql on sl.sid=sql.sid
                where sql.is_disabled = 0 and sl.password IS NOT NULL and sl.hasaccess = 1 and sl.isntname = 0 and not sl.name like '##%##' and sl.name not like 'sa';";
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = sqlConnection1;

                    sqlConnection1.Open();

                    reader = cmd.ExecuteReader();
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            //int sid = reader.GetInt16(0);
                            int sid = 0;
                            int status = reader.GetInt16(1);
                            String name = reader.GetString(2);
                            DateTime createdate = reader.GetDateTime(3);
                            DateTime updatedate = reader.GetDateTime(4);
                            DateTime accdate = reader.GetDateTime(5);
                            users.Add(new User(sid, status, name, createdate, updatedate, accdate));
                        }
                    }
                    reader.Close();
                    sqlConnection1.Close();
                    return users;
                }
                catch (Exception ex)
                {
                    return new List<User>();
                }
            }

 //  測試連線資料庫
 private bool TestConnection()
 {
     bool result = false;
     SqlConnection conn = null;
     // 錯誤處理
     try
     {
         conn = new SqlConnection(ConnectionString);
         // 開啟
         conn.Open();
         // 關閉
         conn.Close();
         // 完成
         result = true;
     }
     catch { }
     conn = null;
     // 傳回
     return result;
 }

        public static void ConnectToSQLWithInstanceNameTest()
        {
            SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(DataTestUtility.TcpConnStr);

            bool proceed = true;
            string dataSourceStr = builder.DataSource.Replace("tcp:", "");
            string[] serverNamePartsByBackSlash = dataSourceStr.Split('\\');
            if (!dataSourceStr.Contains(",") && serverNamePartsByBackSlash.Length == 2)
            {
                string hostname = serverNamePartsByBackSlash[0];
                proceed = !String.IsNullOrWhiteSpace(hostname) && IsBrowserAlive(hostname);
            }

            if(proceed)
            {
                using (SqlConnection connection = new SqlConnection(builder.ConnectionString))
                {
                    connection.Open();
                    connection.Close();
                }
            }
        }

    public static MultipleCommands(string[] cmds, SqlConnection scon)
    {
        SqlCommand cmd = new SqlCommand();
        SqlCommand.Connection = scon;

        try
        {
            foreach (string c in cmds)
            {
                try
                {
                    scon.Open()
                    cmd.CommandText = c;
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "";
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    scon.Close();
                    scon.Dispose();
                }
            }
            return 1;
        }
        catch (Exception ex)
        {
            throw ex;
            return 0;
        }
        finally
        {
            cmd.Dispose()
        }
    }

        public static void ExecuteTest()
        {
            SqlCommand com = new SqlCommand("select * from Orders");
            SqlConnection con = new SqlConnection(DataTestUtility.TcpConnStr);

            com.Connection = con;

            con.Open();

            Task<SqlDataReader> readerTask = com.ExecuteReaderAsync();
            bool taskCompleted = readerTask.Wait(TaskTimeout);
            Assert.True(taskCompleted, "FAILED: ExecuteReaderAsync Task did not complete successfully.");

            SqlDataReader reader = readerTask.Result;

            int rows;
            for (rows = 0; reader.Read(); rows++) ;

            Assert.True(rows == 830, string.Format("FAILED: ExecuteTest reader had wrong number of rows. Expected: {0}. Actual: {1}", 830, rows));

            reader.Dispose();
            con.Close();
        }

        private static void TestMARSSessionPooling(string caseName, string connectionString, CommandType commandType,
                                           ExecuteType executeType, ReaderTestType readerTestType, GCType gcType)
        {
            SqlCommand[] cmd = new SqlCommand[CONCURRENT_COMMANDS];
            SqlDataReader[] gch = new SqlDataReader[CONCURRENT_COMMANDS];

            using (SqlConnection con = new SqlConnection(connectionString))
            {
                con.Open();

                for (int i = 0; i < CONCURRENT_COMMANDS; i++)
                {
                    // Prepare all commands
                    cmd[i] = con.CreateCommand();
                    switch (commandType)
                    {
                        case CommandType.Text:
                            cmd[i].CommandText = _COMMAND_SQL;
                            cmd[i].CommandTimeout = 120;
                            break;
                        case CommandType.StoredProcedure:
                            cmd[i].CommandText = _COMMAND_RPC;
                            cmd[i].CommandTimeout = 120;
                            cmd[i].CommandType = CommandType.StoredProcedure;
                            break;
                    }
                }

                for (int i = 0; i < CONCURRENT_COMMANDS; i++)
                {
                    switch (executeType)
                    {
                        case ExecuteType.ExecuteScalar:
                            cmd[i].ExecuteScalar();
                            break;
                        case ExecuteType.ExecuteNonQuery:
                            cmd[i].ExecuteNonQuery();
                            break;
                        case ExecuteType.ExecuteReader:
                            if (readerTestType != ReaderTestType.ReaderGC)
                                gch[i] = cmd[i].ExecuteReader();

                            switch (readerTestType)
                            {
                                case ReaderTestType.ReaderClose:
                                    {
                                        gch[i].Dispose();
                                        break;
                                    }
                                case ReaderTestType.ReaderDispose:
                                    gch[i].Dispose();
                                    break;
                                case ReaderTestType.ReaderGC:
                                    gch[i] = null;
                                    WeakReference weak = OpenReaderThenNullify(cmd[i]);
                                    GC.Collect();

                                    if (gcType == GCType.Wait)
                                    {
                                        GC.WaitForPendingFinalizers();
                                        Assert.False(weak.IsAlive, "Error - target still alive!");
                                    }
                                    break;
                                case ReaderTestType.ConnectionClose:
                                    GC.SuppressFinalize(gch[i]);
                                    con.Close();
                                    con.Open();
                                    break;
                                case ReaderTestType.NoCloses:
                                    GC.SuppressFinalize(gch[i]);
                                    break;
                            }
                            break;
                    }

                    if (readerTestType != ReaderTestType.NoCloses)
                    {
                        con.Close();
                        con.Open(); // Close and open, to re-assure collection!
                    }

                    SqlCommand verificationCmd = con.CreateCommand();

                    verificationCmd.CommandText = COMMAND_STATUS;
                    using (SqlDataReader rdr = verificationCmd.ExecuteReader())
                    {
                        rdr.Read();
                        int connections = (int)rdr.GetValue(0);
                        rdr.NextResult();
                        rdr.Read();
                        int requests = (int)rdr.GetValue(0);

                        switch (executeType)
                        {
                            case ExecuteType.ExecuteScalar:
                            case ExecuteType.ExecuteNonQuery:
                                // 1 for connection, 1 for command
                                Assert.True(connections == 2, "Failure - incorrect number of connections for ExecuteScalar! #connections: " + connections);

                                // only 1 executing
//.........这里部分代码省略.........