Interface Base Data Access

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

namespace DBTypelessDEMO 
{ 
    class Program 
    { 
        static void Main(string[] args) 
        { 
          
            OracleConnection conn = new OracleConnection(); 
            DataAccessLayer da = new DataAccessLayer(); 
            DataTable dt= da.GetUserInfoTable(conn); 
            string name = da.GetUserNameByID("123123"); 
            List list = da.GetUserList(conn); 
        } 
    } 
  
    public class DataAccessLayer 
    { 
        //DEMO1 
        public string GetUserNameByID(IDbConnection conn, string id) 
        { 
            string sql = "SELECT name FROM user WHERE id = :id"; 
            IDbCommand cmd = PrepareCommand(conn, sql); 
            AddWithValue(cmd, ":id", id); 

            ConnectionState state = conn.State; 
            try 
            { 
                if (state == ConnectionState.Closed) 
                { 
                    conn.Open(); 
                } 
                IDataReader dr = cmd.ExecuteReader(); 
            
                if (dr.Read()) 
                { 
                   return ProcRecord(dr); 
                } 
                return string.Empty; 
            } 
            catch (Exception ex) 
            { 

                throw ex; 
            } 
            finally 
            { 
                if (state == ConnectionState.Closed) 
                { 
                    conn.Close(); 
                } 
            } 
        } 
        //DEMO2 
        public List GetUserList(IDbConnection conn) 
        { 
            string sql = "SELECT name FROM user"; 
            IDbCommand cmd = PrepareCommand(conn, sql); 
          

            ConnectionState state = conn.State; 
            try 
            { 
                if (state == ConnectionState.Closed) 
                { 
                    conn.Open(); 
                } 
                IDataReader dr = cmd.ExecuteReader(); 
            
                List result = new List(); 

                while (dr.Read()) 
                { 
                    ProcRecord(dr,result); 
                } 
                return result; 
            } 
            catch (Exception ex) 
            { 
                throw ex; 
            } 
            finally 
            { 
                if (state == ConnectionState.Closed) 
                { 
                    conn.Close(); 
                } 
            } 
        } 
        //DEMO3 
        public DataTable GetUserInfoTable(IDbConnection conn) 
        { 
            string sql = "SELECT * FROM user"; 
            IDbCommand cmd = PrepareCommand(conn, sql); 
            DataTable dt = new DataTable(); 
            Fill(cmd, dt); 
            return dt; 
        } 


        private string ProcRecord(IDataRecord record) 
        { 
            if (record["Name"] == DBNull.Value || record["Name"] == null) 
            { 
                return string.Empty; 
            } 
            else 
            { 
                return Convert.ToString(record["Name"]); 
            } 
        } 
        private void ProcRecord(IDataRecord record,IList list) 
        { 
            if (record["Name"] == DBNull.Value || record["Name"] == null) 
            { 
                list.Add( string.Empty); 
            } 
            else 
            { 
                list.Add( Convert.ToString(record["Name"])); 
            } 
        } 

        //產生Command 
        private IDbCommand PrepareCommand(IDbConnection conn,string sql) 
        { 
            //準備 IDbCommand 
            IDbCommand cmd = conn.CreateCommand(); 
            cmd.CommandText =sql; 
            return cmd; 
        } 

        //新增Parameter 
        private IDbDataParameter AddWithValue(IDbCommand cmd, string paraName, object value) 
        { 
            IDbDataParameter para = cmd.CreateParameter(); 
            para.ParameterName = paraName; 
            para.Value = value; 
            cmd.Parameters.Add(value); 
            return para; 
        } 

        //Adapter 中的Fill功能 
        private void Fill(IDbCommand cmd, DataTable dt) 
        { 
            IDbConnection conn = cmd.Connection; 
            if (conn == null) 
            { 
                throw new NullReferenceException("conn"); 
            } 
            ConnectionState state = conn.State; 
            try 
            { 
                if (state == ConnectionState.Closed) 
                { 
                    conn.Open(); 
                } 
                using (IDataReader dr = cmd.ExecuteReader()) 
                { 
                    dt.Load(dr); 
                } 
            } 
            catch (Exception ex) 
            { 
                throw ex; 
            } 
            finally 
            { 
                if (state == ConnectionState.Closed) 
                { 
                    conn.Close(); 
                } 
            } 
        } 
    } 
}