C# DbConnection类代码示例

 public static DbParameter[] DeriveParameters(DbConnection dbConnection, string spName)
 {
     if (dbConnection == null)
     {
         throw new ArgumentNullException("dbConn");
     }
     if (string.IsNullOrEmpty(spName))
     {
         throw new ArgumentNullException("spName");
     }
     DbCommand command = null;
     using (DbConnection connection = (DbConnection) ((ICloneable) dbConnection).Clone())
     {
         command = connection.CreateCommand();
         command.CommandText = spName;
         command.CommandType = CommandType.StoredProcedure;
         connection.Open();
         if (!(command is SqlCommand))
         {
             throw new NotSupportedException();
         }
         SqlCommandBuilder.DeriveParameters((SqlCommand) command);
         connection.Close();
     }
     if ((command.Parameters.Count > 0) && (command.Parameters[0].Direction == ParameterDirection.ReturnValue))
     {
         command.Parameters.RemoveAt(0);
     }
     DbParameter[] array = new DbParameter[command.Parameters.Count];
     command.Parameters.CopyTo(array, 0);
     return array;
 }

 public byte[] GetBytes(DbConnection connection)
 {
     using (var contentStream = GetContentStream(connection))
     {
         return contentStream.Length == 0 ? null : new BinaryReader(contentStream).ReadBytes((int)contentStream.Length);
     }
 }

    protected void Page_Load(object sender, EventArgs e)
    {
        //DbConnection 인스턴스 초기화
        DbConnection DbConn = new DbConnection();
        SqlConnection conn = DbConn.DbConn();

        ////프로시저명 
        //string SpName = "SP_BoardList";
        //SqlCommand cmd = new SqlCommand(SpName, conn);
        //cmd.CommandType = CommandType.StoredProcedure;

        ////SqlDataReader 사용
        //SqlDataReader objDr = cmd.ExecuteReader();
        //this.List.DataSource = objDr;
        //this.List.DataBind();

        //SqlDataAdapter 사용
        SqlDataAdapter da = new SqlDataAdapter("SP_BoardList", conn);
        da.SelectCommand.CommandType = CommandType.StoredProcedure;

        //DataSet 사용
        DataSet ds = new DataSet();
        da.Fill(ds, "BoardList");
        List.DataSource = ds;
        List.DataBind();
    }

        public void AddFingerprint(FingerprintCard card, Image newprint)
        {
            using (var newconnect = new DbConnection())
            {

                if (!newconnect.Connect()) throw new CannotConnectToDatabaseException();

                string query;
                if (card.FingerprintList.Count == 0) //card is empty => register new card
                {
                    query = "insert into usertbl values(\"" + card.guid + "\",\""
                    + card.FirstName + "\",\"" + card.LastName + "\");";
                    newconnect.SendQueryAndClose(query);
                }

                query = "insert into fprinttbl values(?File,\""
                              + card.guid + "\");";

                byte[] rawimg = SerializeImage(newprint);
                MySqlParameter pFile = new MySqlParameter("?File", rawimg);
                newconnect.SendQueryAndClose(query, pFile);
                card.FingerprintList.Add(newprint);
            }

            CachedDB.UpdateCard(card);
        }

        /// <summary>
        /// Constructor
        /// </summary>
        /// <param name="Engine">The string name, from the GetDatabaseEngine() method</param>
        /// <param name="Host">The Database server IP Address</param>
        /// <param name="Port">The Database server Port Number</param>
        /// <param name="DatabaseName">The name of the database</param>
        /// <param name="User">A username, with database privliages</param>
        /// <param name="Pass">The password to the User</param>
        public DatabaseDriver(string Engine, string Host, int Port, string DatabaseName, string User, string Pass)
        {
            // Set class variables, and create a new connection builder
            this.DatabaseEngine = GetDatabaseEngine(Engine);
            DbConnectionStringBuilder Builder;

            if (this.DatabaseEngine == DatabaseEngine.Sqlite)
            {
                string FullPath = Path.Combine(MainForm.Root, DatabaseName + ".sqlite3");
                IsNewDatabase = !File.Exists(FullPath) || new FileInfo(FullPath).Length == 0;
                Builder = new SQLiteConnectionStringBuilder();
                Builder.Add("Data Source", FullPath);
                Connection = new SQLiteConnection(Builder.ConnectionString);
            }
            else if (this.DatabaseEngine == DatabaseEngine.Mysql)
            {
                IsNewDatabase = false;
                Builder = new MySqlConnectionStringBuilder();
                Builder.Add("Server", Host);
                Builder.Add("Port", Port);
                Builder.Add("User ID", User);
                Builder.Add("Password", Pass);
                Builder.Add("Database", DatabaseName);
                Connection = new MySqlConnection(Builder.ConnectionString);
            }
            else
            {
                throw new Exception("Invalid Database type.");
            }
        }

        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 async Task<dynamic> GetAll()
        {
            try
            {
                var dbRef = new DbConnection();
                var client = dbRef.GetClient();
                //Create the DB if it does not exist.
                if (client.Database.GetAsync().Result.Error == "not_found")
                {
                    await dbRef.CreateDB();
                    //load with initial data
                    await client.Documents.PostAsync("{\"text\":\"Sample 1\"}");
                    await client.Documents.PostAsync("{\"text\":\"Sample 2\"}");
                    await client.Documents.PostAsync("{\"text\":\"Sample 3\"}");
                }
                //Query for all docs including full content of the docs.
                var query = new QueryViewRequest("_all_docs").Configure(query1 => query1.IncludeDocs(true));

                //GET
                RawResponse response = await client.Views.QueryRawAsync(query);
                if (response.IsSuccess)
                {
                    return response.Content;
                }
                else
                {
                    return "{\"msg\": \"Failure to GET. Status Code: " + response.StatusCode + ". Reason: " + response.Reason + "\"}";
                }
            }
            catch (Exception e)
            {
                return "{\"msg\": \"Failure to GET: " + e + "\"}";
            }
        }

        public async Task<string> update(ToDoItem item)
        {
            try
            {
                var dbRef = new DbConnection();
                var client = dbRef.GetClient();
                //setup updated fields.
                var data = "{\"text\":\"" + item.text + "\"}";

                //Update
                var response = await client.Documents.PutAsync(item.id, item.rev, data);
                if (response.IsSuccess)
                {
                    return "{\"id\":\"" + response.Id + "\",\"rev\":\"" + response.Rev + "\",\"text\":\"" + item.text + "\"}";
                }
                else
                {
                    return "{\"msg\": \"Failure to PUT. Status Code: " + response.StatusCode + ". Reason: " + response.Reason + "\"}";
                }

            }
            catch (Exception e)
            {
                return "{\"msg\": \"Failure to PUT." + e + "\"}";
            }
        }

 private DbCommand CreateCommand(string commandText, DbConnection connection)
 {
     DbCommand command = factory.CreateCommand();
     command.Connection = connection;
     command.CommandText = commandText;
     return command;
 }

        public async Task<dynamic> Create(ToDoItem item)
        {
            try
            {
                //Reference the Cloudant db.
                var myCouch = new DbConnection();
                var client = myCouch.GetClient();

                // Post/Insert to Cloudant using myCouch
                var response = await client.Documents.PostAsync("{\"text\":\"" + item.text + "\"}");
                if (response.IsSuccess)
                {
                    return "{\"id\":\"" + response.Id + "\",\"rev\":\"" + response.Rev + "\",\"text\":\"" + item.text + "\"}";
                }
                else
                {
                    return "{\"msg\": \"Failure to POST. Status Code: " + response.StatusCode + ". Reason: " + response.Reason + "\"}";
                }

            }
            catch (Exception e)
            {
                return "{\"msg\": \"Failure to POST: " + e + "\"}";
            }
        }

 public static ArrayList retrieveConversation(LoggedUser user, Host host)
 {
     ArrayList conversation = new ArrayList(new ArrayList());
     DbConnection db = new DbConnection();
     SqlConnection connection = db.OpenConnection();
     int conversation_id = 0;
     String query = "SELECT id FROM Conversation WHERE [email protected] AND [email protected]";
     SqlCommand command = new SqlCommand(query, connection);
     command.Parameters.AddWithValue("@host", host.loginName);
     command.Parameters.AddWithValue("@user", user.loginName);
     SqlDataReader reader = command.ExecuteReader();
     if (reader.Read())
     {
         conversation_id = reader.GetInt32(0);
     }
     db.CloseConnection();
     db.OpenConnection();
     String query1 = "SELECT id,content,initiator,message_read FROM Message WHERE [email protected]";
     SqlCommand command1 = new SqlCommand(query1, connection);
     command1.Parameters.AddWithValue("@id", conversation_id);
     SqlDataReader reader1 = command1.ExecuteReader();
     while (reader1.Read())
     {
         int id = reader1.GetInt32(0); 
         String message = reader1.GetString(1);
         String initiator = reader1.GetString(2);
         int read = reader1.GetInt32(3);
         ArrayList array = new ArrayList();
         array.Add(message); array.Add(initiator); array.Add(read); array.Add(id);
         conversation.Add(array);
     }
     db.CloseConnection();
     return conversation;
 }

        public DatabaseDriver()
        {
            this.DatabaseEngine = Config.GetDatabaseEngine();

            DbConnectionStringBuilder Builder;

            if (this.DatabaseEngine == DatabaseEngine.Sqlite)
            {
                Builder = new SQLiteConnectionStringBuilder();
                string FullPath = Path.Combine(Utils.AssemblyPath, Config.GetType<string>("Database", "Database") + ".sqlite3");
                IsNewDatabase = !File.Exists(FullPath) || new FileInfo(FullPath).Length == 0;

                Builder.Add("Data Source", FullPath);

                Connection = new SQLiteConnection(Builder.ConnectionString);
            }
            else if (this.DatabaseEngine == DatabaseEngine.Mysql)
            {
                Builder = new MySqlConnectionStringBuilder();

                Builder.Add("Server", Config.GetType<string>("Database", "Hostname"));
                Builder.Add("Port", Config.GetType<int>("Database", "Port"));
                Builder.Add("User ID", Config.GetType<string>("Database", "Username"));
                Builder.Add("Password", Config.GetType<string>("Database", "Password"));
                Builder.Add("Database", Config.GetType<string>("Database", "Database"));

                Connection = new MySqlConnection(Builder.ConnectionString);
            }
            else
            {
                throw new Exception("Invalid Database type.");
            }
        }

        public FingerprintCard[] GetFingerprints()
        {

            FingerprintCard[] resultarray = CachedDB.getCacheDb(); //getCache
            if (resultarray != null) return resultarray;

            using (var newconnect = new DbConnection())
            {

                if (!newconnect.Connect()) throw new CannotConnectToDatabaseException();

                string query = "select * from usertbl;";
                MySqlDataReader reader = newconnect.SendQuery(query);
                List<FingerprintCard> fingerprintlist = new List<FingerprintCard>();

                while (reader.Read())
                {
                    FingerprintCard newcard = new FingerprintCard(reader.GetValue(1).ToString(), reader.GetValue(2).ToString());
                    string guidstr = (string)reader.GetValue(0);
                    newcard.guid = new Guid(guidstr);
                    newcard.FingerprintList = GetFingerprintList(newcard.guid);
                    fingerprintlist.Add(newcard);
                }
                reader.Close();
                resultarray = fingerprintlist.ToArray();
            }
            CachedDB.UpdateCache(resultarray);
            return resultarray;
        }

    public Boolean saveImageInDb()
    {
        DbConnection db = new DbConnection();
        SqlConnection connection = db.OpenConnection();
        String query = "INSERT INTO Images (name, contentType, property, userLogin, data) VALUES (@name, @contentType, @property, @user, @data);";
        SqlCommand command = new SqlCommand(query, connection);
        command.Parameters.AddWithValue("@name", name);
        command.Parameters.AddWithValue("@contentType", contentType);
        if (propertyId != -1)
        {
            command.Parameters.AddWithValue("@property", propertyId);
        }
        else
        {
            command.Parameters.AddWithValue("@property", "NULL");
        }
        command.Parameters.AddWithValue("@user", user);
        command.Parameters.AddWithValue("@data", bytes);

        try
        {
            System.Diagnostics.Debug.Write(command.CommandText);
            command.ExecuteNonQuery();
            db.CloseConnection();
            return true;
        }
        catch (Exception ex)
        {
            System.Diagnostics.Debug.Write(ex.Message);
            db.CloseConnection();
            return false;
        }
    }

        public override void DoJob()
        {
            StringBuilder sb = new StringBuilder(); // Result of the query.

            sb.AppendFormat("JobType: {0}", GetName()).AppendLine();
            
            /*
             * TODO: Properly read in connection information here.
             */
            DbConnectorInfo sourceInfo = new DbConnectorInfo("localhost\\SQLEXPRESS", "NORTHWND", "sa", "Conestoga1");
            DbConnection sourceConnection = new DbConnection(sourceInfo, "MS");

            Dictionary<string, string> keys = new Dictionary<string, string>();
            keys.Add("Customers", "CustomerID");
            keys.Add("Orders", "OrderID");

            List<string> joins = new List<string>();
            joins.Add("full");

            sb.Append("Connecting to source...").AppendLine();
            sb.AppendFormat("DbConnectorInfo:").AppendLine();
            sb.AppendFormat("server: {0}", sourceInfo.server).AppendLine();
            sb.AppendFormat("database: {0}", sourceInfo.database).AppendLine();
            sb.AppendFormat("userid: {0}", sourceInfo.userid).AppendLine();

            /*
             * Open the source and destination databases.
             */
            if (!sourceConnection.ConnectionOpen)
            {
                sourceConnection.OpenDBConnection();
            }

            /*
             * Pull data from BioLinks.
             * TODO: Figure out wtf this method even takes.
             */
            DataTable sourceData = sourceConnection.pullData(keys, joins, null, null);

            /*
             * Log the query.
             */
            foreach (DataRow row in sourceData.Rows)
            {
                foreach (DataColumn col in sourceData.Columns)
                {
                    sb.AppendFormat("{0} ", row[col]);    
                }

                sb.AppendLine();
            }

            Logger.logMessage(sb.ToString(), AppDomain.CurrentDomain.BaseDirectory + AppDomain.CurrentDomain.RelativeSearchPath + "\\Events2.txt");

            /*
             * Close the source and destination databases.
             */
            sourceConnection.CloseDBConnection();
        }