accountdatabaseimp.cs

模拟银行系统的实现

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;

namespace Project1
{
    /// <summary>
    /// The implementation of the AccountDatabase Interface.
    /// Database connection and disconnection functions,
    /// and SQL queries are involved.
    /// </summary>
    public class AccountDatabaseImp : AccountDatabase
    {
        #region AccountDatabase Members

        /// <summary>
        /// Add an account record into the database
        /// </summary>
        /// <param name="ac">An object of Account</param>
        public void add(Account ac)
        {
            string database = "infybank.mdb";
            string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + database;
            OleDbConnection dbConnection = new OleDbConnection(connectionString);
            dbConnection.Open();
            string query = "INSERT INTO Account(AccountNo,CustomerID,AccountType,DateOfOpening,CurrentAmount,Status) VALUES("
                            + ac.iAcNo + ", '" + ac.sCustID + "', '" + ac.sAcType + "', '" + ac.dOpenDate.ToShortDateString()
                            + "', " + ac.dBalance + ", '" + ac.sStatus + "')";
            
            OleDbCommand command = new OleDbCommand(query, dbConnection);
            
            command.ExecuteNonQuery();
            Console.WriteLine("Account added.");
            dbConnection.Close();
        }

        /// <summary>
        /// Remove an account record from the database
        /// </summary>
        /// <param name="ac">An object of Account</param>
        public void remove(Account ac)
        {
            string database = "infybank.mdb";
            string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + database;
            OleDbConnection dbConnection = new OleDbConnection(connectionString);
            dbConnection.Open();
            string query = "DELETE FROM Account WHERE AccountNo = " + ac.iAcNo + "";
            OleDbCommand command = new OleDbCommand(query, dbConnection);
            
            command.ExecuteNonQuery();
            Console.WriteLine("Account deleted.");
            dbConnection.Close();
        }

        /// <summary>
        /// Update an account record in the database
        /// </summary>
        /// <param name="ac">An object of Account</param>
        public void update(Account ac)
        {
            string database = "infybank.mdb";
            string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + database;
            OleDbConnection dbConnection = new OleDbConnection(connectionString);
            dbConnection.Open();
            string query = "UPDATE Account SET CurrentAmount = " + ac.dBalance + " WHERE AccountNo = " + ac.iAcNo + "";
            OleDbCommand command = new OleDbCommand(query, dbConnection);
            
            command.ExecuteNonQuery();
            Console.WriteLine("Account updated.");
            dbConnection.Close();
        }

        /// <summary>
        /// Search for an account record in the database
        /// </summary>
        /// <param name="acNo">The account number of an account record</param>
        /// <returns>An account that has the coresponding account number</returns>
        public Account search(int acNo)
        {
            int No = 0;
            String Type = "";
            String id = "";
            DateTime date = new DateTime(0);
            double amount = 0.0;
            String sta = "";
            int flag = 0;
            
            try
            {
                string database = "infybank.mdb";
                string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + database;
                OleDbConnection dbConnection = new OleDbConnection(connectionString);
                dbConnection.Open();
                string query = "SELECT * FROM Account WHERE AccountNo = " + acNo + "";
                OleDbCommand command = new OleDbCommand(query, dbConnection);
               

                OleDbDataReader reader = command.ExecuteReader();

                if (reader.Read())
                {
                    Console.WriteLine("Account found.");
                    No = reader.GetInt32(0);
                    id = reader.GetString(2).ToString();
                    Type = reader.GetString(1).ToString();
                    date = reader.GetDateTime(3);
                    amount = reader.GetDouble(4);
                    sta = reader.GetString(5).ToString();
                    flag = 1;  
                }
                else
                    flag = 0;
                reader.Close();
                dbConnection.Close();
            }
            catch (OleDbException e)
            {
                Console.WriteLine(e.Message);
            }
            if (flag == 1)
                  return new Account(No, Type, id, date, amount, sta);
            else
                return null;
        }

        #endregion
    }
}