C# DbConnection.Open方法代码示例

        private void BindGrid(DbConnection conn)
        {
            conn.Open();
                _schema = new DataSet();
                var schema = conn.GetSchema();

                foreach (DataRow dataRow in schema.Rows)
                {
                    var tableName = dataRow["CollectionName"].ToString();
                    if(!_schema.Tables.Contains(tableName))
                    {
                        var dt = conn.GetSchema(tableName);
                        dt.TableName = tableName;
                        _schema.Tables.Add(dt);
                    }
                }
                conn.Close();
                dgSchema.DataSource = _schema.Tables[0];

                cbTable.DataSource = _schema.Tables[0];
                cbTable.DisplayMember = "CollectionName";
                cbTable.ValueMember = "CollectionName";
                _previousWidth = dgSchema.Width;
                _previousState = WindowState;
        }

 public object ExecScalar(DbConnection conn, string sql, params IDataParameter[] p)
 {
     //MySqlConnection conn = GetConnection();
     DbCommand cmd = new MySqlCommand(sql);
     cmd.Connection = conn;
     if (conn.State != ConnectionState.Open)
         conn.Open();
     object obj = cmd.ExecuteScalar();
     //cmd.Connection.Close();
     if (obj != null && obj != DBNull.Value) return obj;
     return null;
 }

 public int ExecNonQuery(DbConnection conn, string sql, params IDataParameter[] p)
 {
     // MySqlConnection conn = GetConnection();
     DbCommand cmd = new MySqlCommand(sql);
     if (p != null)
         cmd.Parameters.AddRange(p);
     cmd.Connection = conn;
     if (conn.State != ConnectionState.Open)
         conn.Open();
     int i = cmd.ExecuteNonQuery();
     //  cmd.Connection.Close();
     return i;
 }

 public override void Connect_To_Database(StorageConfig config)
 {
     myConfig = config as MySQLConfig;
     if (myConfig == null)
         throw new Exception("Database Config is NULL");
     try
     {
         myDBConn = new MySqlConnection(ConnectionString);
         myDBConn.Open();
         if (myDBConn.State != System.Data.ConnectionState.Open)
             throw new Exception("Unable to Open Database. Storage:" + config.Name);               
     }
     catch (Exception ex)
     {
         throw ex;
     }
 }

 /*
 * Purpose : makes a connection with MySql server
 * Preconditions : connection string must exists
 * Postconditions : Exception
 * Input parameters : None
 * returns : IDbConnection
 * Date created : 06.07.2015
 * Date last changed : 07.07.2015
 * Author (e-mail) : Matea [email protected]
 */
 public static DbConnection provideConnection()
 {
     lock (syncRoot)
     {
         if (instance == null)
         {
             instance = new Connection();
             string str = getConnString();
             try
             {
                 conn = new MySqlConnection();
                 conn.ConnectionString = str;
                 conn.Open();
             }
             catch (Exception ex)
             {
                 throw ex;
             }
         }
     }
     return conn;
 }

 public void Send()
 {
     _dbConnection = new DbConnection();
     _dbConnection.Open();
     _data = _dbConnection.GenerateResponse(_request.Id);
 }

            public DbHelper(int tenant, ConnectionStringSettings connectionString)
            {
                this.tenant = tenant;
                factory = connectionString.ProviderName == "System.Data.SQLite" ? GetSQLiteFactory() : GetMySqlFactory();
                builder = factory.CreateCommandBuilder();
                connect = factory.CreateConnection();
                connect.ConnectionString = connectionString.ConnectionString;
                connect.Open();

                if (factory.GetType().Name == "MySqlClientFactory")
                {
                    CreateCommand("set @@session.sql_mode = concat(@@session.sql_mode, ',NO_AUTO_VALUE_ON_ZERO')").ExecuteNonQuery();
                }

                columns = connect.GetSchema("Columns");

                whereExceptions["calendar_calendar_item"] = " where calendar_id in (select id from calendar_calendars where tenant = " + tenant + ") ";
                whereExceptions["calendar_calendar_user"] = " where calendar_id in (select id from calendar_calendars where tenant = " + tenant + ") ";
                whereExceptions["calendar_event_item"] = " where event_id in (select id from calendar_events where tenant = " + tenant + ") ";
                whereExceptions["calendar_event_user"] = " where event_id in (select id from calendar_events where tenant = " + tenant + ") ";
                whereExceptions["crm_entity_contact"] = " where contact_id in (select id from crm_contact where tenant_id = " + tenant + ") ";
                whereExceptions["crm_entity_tag"] = " where tag_id in (select id from crm_tag where tenant_id = " + tenant + ") ";
                whereExceptions["files_folder_tree"] = " where folder_id in (select id from files_folder where tenant_id = " + tenant + ") ";
                whereExceptions["forum_answer_variant"] = " where answer_id in (select id from forum_answer where tenantid = " + tenant + ")";
                whereExceptions["forum_topic_tag"] = " where topic_id in (select id from forum_topic where tenantid = " + tenant + ")";
                whereExceptions["forum_variant"] = " where question_id in (select id from forum_question where tenantid = " + tenant + ")";
                whereExceptions["projects_project_participant"] = " where project_id in (select id from projects_projects where tenant_id = " + tenant + ")";
                whereExceptions["projects_following_project_participant"] = " where project_id in (select id from projects_projects where tenant_id = " + tenant + ")";
                whereExceptions["projects_project_tag"] = " where project_id in (select id from projects_projects where tenant_id = " + tenant + ")";
                whereExceptions["projects_project_tag_change_request"] = " where project_id in (select id from projects_projects where tenant_id = " + tenant + ")";
                whereExceptions["tenants_tenants"] = " where id = " + tenant;
                whereExceptions["webstudio_widgetstate"] = " where widgetcontainerid in (select id from webstudio_widgetcontainer where tenantid = " + tenant + ")";
                whereExceptions["core_usersecurity"] = " where userid in (select id from core_user where tenant = " + tenant + ")";
                whereExceptions["core_acl"] = " where tenant = " + tenant + " or tenant = -1";
                whereExceptions["core_subscription"] = " where tenant = " + tenant + " or tenant = -1";
                whereExceptions["core_subscriptionmethod"] = " where tenant = " + tenant + " or tenant = -1";
            }

        private void PrepareCommand(DbCommand cmd, DbConnection conn, SqlTransaction trans, string cmdText, DbParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null)
                cmd.Transaction = trans;
            cmd.CommandType = CommandType.Text;//cmdType;
            if (cmdParms != null)
            {

                foreach (DbParameter parameter in cmdParms)
                {
                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                        (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    cmd.Parameters.Add(parameter);
                }
            }
        }

 private void CheckAndEstablishReadConnection()
 {
     if (readDBConn == null)
     {
         readDBConn = new MySqlConnection(ConnectionString);
         readDBConn.Open();
         readDBConn.ChangeDatabase(myConfig.Database);
     } 
 }

 protected override string GetDbProviderManifestToken(DbConnection connection)
 {
     // we need the connection option to determine what version of the server
     // we are connected to
     bool shouldClose = false;
     if (connection.State == ConnectionState.Closed)
     {
         connection.Open();
         shouldClose = true;
     }
     string version = connection.ServerVersion;
     if (shouldClose)
         connection.Close();
     if (version.StartsWith("6")) return "6.0";
     if (version.StartsWith("5.0")) return "5.0";
     if (version.StartsWith("5")) return "5.1";
     throw new NotSupportedException("Versions of MySQL prior to 5.0 are not currently supported");
 }

        /// <summary>
        /// 生成要执行的命令
        /// </summary>
        private static void PrepareCommand(DbCommand cmd, DbConnection conn, DbTransaction trans, CommandType cmdType,
            string cmdText, DbParameter[] cmdParms)
        {
            // 如果存在参数，则表示用户是用参数形式的SQL语句，可以替换
            if (cmdParms != null && cmdParms.Length > 0)
                cmdText = cmdText.Replace("@", "?").Replace(":", "?");

            if (conn.State != ConnectionState.Open)
                conn.Open();

            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null)
                cmd.Transaction = trans;
            cmd.CommandType = cmdType;

            if (cmdParms != null)
            {
                foreach (DbParameter parm in cmdParms)
                {
                    // 如果存在参数，则表示用户是用参数形式的SQL语句，可以替换
                    parm.ParameterName = parm.ParameterName.Replace("@", "?").Replace(":", "?");
                    if (parm.Value == null)
                        parm.Value = DBNull.Value;
                    cmd.Parameters.Add(parm);
                }
            }
        }

        protected override string GetDbProviderManifestToken(DbConnection connection)
        {
            // we need the connection option to determine what version of the server
            // we are connected to
            bool shouldClose = false;
            if (connection.State == ConnectionState.Closed)
            {
                connection.Open();
                shouldClose = true;
            }
            double version = double.Parse(connection.ServerVersion.Substring(0, 3));
            
            if (shouldClose)
                connection.Close();

            if (version < 5.0) throw new NotSupportedException("Versions of MySQL prior to 5.0 are not currently supported");
            if (version < 5.1) return "5.0";
            if (version < 5.5) return "5.1";
            return "5.5";            
        }

        private DbConnection ConnectDB()
        {
            if (CacheCon == null)
            {
                try
                {

                    WriteDebugMessage("Connecting to " + CurrentConString);

                    //CacheCon = new MySqlConnection(CurrentConString);
                    CacheCon = CreateInnerConnection(CurrentConString);

                    CacheCon.Open();

                }

                catch (Exception ex)
                {
                    //throw new ApplicationException("Database connection failed- Connection String:\"" + CurrentConString + "\"; inner exception Message:\"" + ex.Message);
                    WriteDebugMessage("Exception:" + GetExceptionData(ex));
                    throw;
                }

                //Also, select the JobClockDB, for MySQL connections...
                //add a handler to the INI file, this is an attempt to allow for configuration information to be persisted back and forth to and from the db.
                //JobClockConfig.OurINI.BeforeRetrieveValue += new INIFile.RetrieveValueFunc(OurINI_BeforeRetrieveValue);
                //JobClockConfig.OurINI.BeforeSetValue += new INIFile.SetValueFunc(OurINI_BeforeSetValue);
                if (Configuration.DatabaseType.ToUpper().Trim() == "MYSQL")
                {
                    String seldb = Configuration.DatabaseName;
                    DbCommand seldbcmd = CacheCon.CreateCommand();
                    seldbcmd.CommandText = "USE `" + seldb + "`";
                    try
                    {
                        seldbcmd.ExecuteNonQuery(); //yip!

                        //clear empty messages.
                       // String clearempty = "DELETE FROM MESSAGELOG WHERE TRIM(Message)=\"\"";
                        //seldbcmd.CommandText = clearempty;
                       // seldbcmd.ExecuteNonQuery();
                        UpdateUserTableAddActiveField();

                    }
                    catch (Exception dbexception)
                    {
                        //exception...
                        WriteDebugMessage("Error USE-ing Database named \"" + seldb + "\"; Error:" + dbexception.ToString());

                    }
                }

            }
            else
            {
                //if it's not null, verify that it is still valid, otherwise we could get a "MySQL Server has gone away" exception
                // when an attempt is made to execute.
                //we verify that it is still alive by issuing a simple command (SELECT 1), and catching the error that will occur
                //if the connection is dead.
                try
                {
                    DbCommand issueselect = CacheCon.CreateCommand();
                    issueselect.CommandText = "SELECT 1";
                    issueselect.ExecuteNonQuery();

                }
                catch (MySqlException mse)
                {
                    //bugfix Jan 12 2012 8:15 AM: not responding error
                  if(mse.Message.Contains("gone away"))
                    {
                        //close, and re-open.
                        //we set the value to null and call this routine recursively,haha.
                        CacheCon = null;
                        //CacheCon will now be set in the recursive call, too.
                        return ConnectDB();
                    }
                  else
                  {
                      LogAdmin("Unexpected MySqlException:" + mse.ToString());
                  }

                }

            }
            return CacheCon;
        }

        private static void PrepareCommand(DbCommand cmd, DbConnection conn, DbTransaction trans, CommandType cmdType, string cmdText, DbParameter[] cmdParms)
        {
            //conn.ConnectionString = connString;
            conn.ConnectionString = ConfigurationManager.ConnectionStrings["connStringName"].ConnectionString;

            try
            {
                #region  长城添加，测试当前网络是否正常
                string MIp = ConfigurationManager.ConnectionStrings["connStringName"].ConnectionString;
                MIp = MIp.Substring(0, MIp.IndexOf(';'));
                MIp=MIp.Substring(MIp.IndexOf('=')+1);
                if (!HDIC_DB.TestConnection(MIp, 1433, 3000))
                {
                    //throw new Exception();
                    HDICSoft.Message.HDIC_Message.ShowWarnDialog(null, "数据库连接失败,请检查服务器或者网络是否正常.");
                    return;
                }

                #endregion

                if (conn.State != ConnectionState.Open)
                    conn.Open();
            }
            //catch (System.Exception ex)
            //{
            //    HDICSoft.Message.HDIC_Message.ShowWarnDialog(null, "数据库连接失败,请检查服务器或者网络是否正常.");
            //}
            catch
            {
                HDICSoft.Message.HDIC_Message.ShowWarnDialog(null, "数据库连接失败,请检查服务器或者网络是否正常.");
                //throw new Exception();
            }

            cmd.Connection = conn;
            cmd.CommandText = cmdText;

            if (trans != null)
                cmd.Transaction = trans;

            cmd.CommandType = cmdType;

            if (cmdParms != null)
            {
                foreach (DbParameter parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
        }

 /// <summary>
 /// This checks if a table exists in a database. 
 /// 
 /// Creds to baavgai and Nakor on this very sly extension method
 /// </summary>
 /// <param name="conn"></param>
 /// <param name="tableName"></param>
 /// <param name="owner"></param>
 /// <param name="database"></param>
 /// <returns></returns>
 public static bool TableExists(DbConnection conn, string tableName, string owner, string database)
 {
     DataTable dt;
     try
     {
         conn.Open();
         dt = conn.GetSchema("Tables", new string[] { database, owner, tableName });
     }
     finally
     {
         conn.Close();
     }
     return (dt == null) ? false : dt.Rows.Count > 0;
 }