C#和.NET 2.0中非常轻量级的数据访问层

• 下载演示项目 - 98 KB

引言

在业余时间，我为朋友和家人编写了许多小型应用程序。所有这些小型应用程序的共同点是它们都会从数据库中获取一些数据，将数据显示给用户，然后将更改保存回数据库。

在设计程序时，我会将其分解为不同的层。大多数时候，我有三个逻辑层：用户界面层、业务逻辑和数据访问层。我通常从类图开始，只有在对类图满意之后，我才会构建表和存储过程来访问来自/到表的这些数据。用户界面通常由用户决定。

随着我构建的这些小型应用程序越来越多，我意识到我在数据访问层上花费了大量时间，因为我不得不创建映射以便从数据库中的表中填充业务对象，而且由于每个应用程序都有不同的业务逻辑和业务对象，我最终不得不从头开始编写数据访问层。因此，为了让我的生活更轻松，我决定构建一个通用的数据访问辅助类，它可以被重用于我所有的项目中，只需很少或无需更改。

为了能够用数据库中的数据填充对象，业务对象需要具有带有GET和SET方法的公共属性。然后，使用反射，我可以查询对象中的公共属性，如果属性名称与表中的字段名称匹配，那么对象就会被该字段的数据填充。

有时属性和数据库中的字段不同，或者对象可以拥有比数据库中的字段更多的属性，所以我决定提供两种从数据库填充对象的方式：

• 使用一个映射类，该类提供关于哪个属性映射到哪个字段的信息，以及
• 使用自定义属性修饰属性，以显示属性映射到哪个字段。

第一步是构建一个映射类。这是一个非常简单的类，它包含一个字符串集合。第一个字符串的格式为property=field，其中property是对象的属性名称，field是数据库中字段的名称。所以，实现后的映射类如下：

/// <summary>
/// This class holds information about mapping a database field to a 
/// object property.
/// </summary>
public class Mapper
{
    private List<string> mMappingInfo;

    /// <summary>
    /// Default constructor
    /// </summary>
    public Mapper()
    {
        mMappingInfo = new List<string>();
    }


    /// <summary>
    /// Add mapping information. This method can be used to add more than 
    /// one mapping at a time.
    /// You could use it like: mapper.Add("property1=field1", 
    ///     "property2=field2", "property3=field3", ...)
    /// </summary>
    /// <param name="mappings">mapping information in format 
    //     "[property name]=[field name]"</param>
    public void Add(params string[] mappings)
    {
        foreach (string map in mappings)
            mMappingInfo.Add(map);
    }


    /// <summary>
    /// Return mapping information held in this class as string array
    /// </summary>
    public string[] MappingInformation
    {
        get
        {
            string[] mappings = new string[mMappingInfo.Count];
            mMappingInfo.CopyTo(mappings);

            return mappings;
        }
    }


    /// <summary>
    /// Indexer property. By providing the name it returns the mapping info
    /// for that property.
    /// If the mapping information for the provided property does not exist,
    /// the indexer 
    /// return null. 
    /// You could use it like: string mapInfo = mapper["property1"];
    /// </summary>
    /// <param name="propertyName">the name of the property to 

return 
    /// mapping information</param>
    /// <returns>mapping information for the property 

provided</returns>
    public string this[string propertyName]
    {
        get
        {
            foreach (string map in mMappingInfo)
            {
                string[] spplitedString = map.Split('=');
                if (spplitedString[0] == propertyName)
                    return map;
            }

            return null;
        }
    }


    /// <summary>
    /// Another indexer property. This property returns mapping information,
    /// that is stored in the list, in order.
    /// </summary>
    /// <param name="index">the index</param>
    /// <returns>mapping information</returns>
    public string this[int index]
    {
        get
        {
            if (index < mMappingInfo.Count)
                return mMappingInfo[index];
            else
                return null;
        }
    }

    /// <summary>
    /// Get the property name from the mapping information
    /// </summary>
    /// <param name="map">mapping information</param>
    /// <returns>the name of the property from the provided mapping 
    /// information</returns>
    public static string GetProperty(string map)
    {
        // split the mapping info and return the name of the property
        string[] spplitedString = map.Split('=');
        return spplitedString[0];
    }


    /// <summary>
    /// Get the field name from the mapping information
    /// </summary>
    /// <param name="map">mapping information</param>
    /// <returns>the name of the field from the provided mapping 
    /// information</returns>
    public static string GetField(string map)
    {
        // split the mapping info and return the name of the field
        string[] spplitedString = map.Split('=');
        return spplitedString[1];
    }
}

接下来要实现的是用于将属性映射到数据库字段的自定义属性。这同样是一个非常简单的属性，用于存储字段的名称。实现如下：

/// <summary>
/// Specifies the name of the field in the table that the property maps to 
/// </summary>
[AttributeUsage(AttributeTargets.Property)]
    public class DBFieldAttribute : Attribute
    {
        private string mFieldName;

        /// <summary>
        /// constructor
        /// </summary>
        /// <param name="fieldName">name of the field that the 

property will
        /// be mapped to</param>
        public DBFieldAttribute(string fieldName)
        {
            mFieldName = fieldName;
        }

        public string FieldName
        {
            get { return mFieldName; }
        }
    }

由于此属性只能用于属性，因此该类被AttributeUsage(AttributeTargets.Property)属性修饰。

现在，最后要做的就是编写用于从数据库检索数据的辅助类。已实现的类如下：

public class DBHelper
{
    /// <summary>
    /// Generic method. Gets an object of type T from the data reader. It 
    /// uses mapping information provided to read a field from the reader, 
    /// and gets the property name and sets the value of the property with 
    /// the data which are held in database field
    /// </summary>
    /// <typeparam name="T>The type of object to be 

instantiated</typeparam>
    /// <param name="rdr">Data Reader where the data will be 

read from</param>
    /// <param name="mappings">mapping 

information</param>
    /// <returns>an instance of type T with the properties populated from 
    /// database</returns>
private static T GetItemFromReader<T>(IDataReader rdr, Mapper mappings) 
    where T : class
{
    Type type = typeof(T); 
    T item = Activator.CreateInstance<T>(); // create an instance of the 
                                            // type provided
    foreach(string map in mappings.MappingInformation)
    {
        // for each mapping information 
        string property = Mapper.GetProperty(map);
        string field = Mapper.GetField(map);

        PropertyInfo propInfo = type.GetProperty(property);//get the property
                                                         //by name

        if (Convert.IsDBNull(rdr[field])) // data in database is null, so do
                                          // not set the value of the property
            continue;

        if (propInfo.PropertyType == rdr[field].GetType()) 
        // if the property and database field are the same
            propInfo.SetValue(item, rdr[field], null); // set the value of 
                                                       // property
        else
        {
            // change the type of the data in table to that of property and 
            // set the value of the property
            propInfo.SetValue(item, Convert.ChangeType(rdr[field], 
                propInfo.PropertyType), null); 
        }
    }
    return item;
}

/// <summary>
/// Generic method. Gets an object of type T from the data reader. It uses 
/// attribute information 
/// applied to a property to read a field from the reader, and gets the 
/// property name and sets
/// the value of the property with the data which are held in database field
/// </summary>
/// <typeparam name="T">The type of object to be 

instantiated</typeparam>
/// <param name="rdr">Data Reader where the data will be read 

from</param>
/// <returns>an instance of type T with the properties populated from 
/// database</returns>
private static T GetItemFromReader<T>(IDataReader rdr) where T : class
{
    Type type = typeof(T);
    T item = Activator.CreateInstance<T>();
    PropertyInfo[] properties = type.GetProperties();

    foreach (PropertyInfo property in properties)
    {
        // for each property declared in the type provided check if the 
        // property is decorated with the DBField attribute
        if (Attribute.IsDefined(property, typeof(DBFieldAttribute)))
        {
            DBFieldAttribute attrib = (DBFieldAttribute)Attribute.
                GetCustomAttribute(property, typeof(DBFieldAttribute));

            if (Convert.IsDBNull(rdr[attrib.FieldName])) 
            // data in database is null, so do not set the value of the 
            // property
                continue;

            if (property.PropertyType == rdr[attrib.FieldName].GetType()) 
            // if the property and database field are the same
                property.SetValue(item, rdr[attrib.FieldName], null); 
                // set the value of property
            else
            {
                // change the type of the data in table to that of property 
                // and set the value of the property
                property.SetValue(item, Convert.ChangeType(
                    rdr[attrib.FieldName], property.PropertyType), null);
            }
        }
    }

    return item;
}

/// <summary>
/// Get one object from the database by using the attribute information
/// </summary>
/// <typeparam name="T">the type of object the collection will 

hold</typeparam>
/// <param name="command">DbCommand that is used to read data 

from the 
///  database</param>
/// <returns>populated object from the database</returns>
public static T ReadObject<T>(IDbCommand command) where T : class
{
    IDataReader reader = command.ExecuteReader();
    if (reader.Read())
        return GetItemFromReader<T>(reader);
    else
        return default(T);
}

/// <summary>
/// Get one object from the database by using the mapping information 
/// provided by Mapper class
/// </summary>
/// <typeparam name="T">the type of object the collection will 

hold</typeparam>
/// <param name="command">DbCommand that is used to read data 

from the 
/// database</param>
/// <returns>populated object from the database</returns>
public static T ReadObject<T>(IDbCommand command, Mapper mappingInfo) 
    where T : class
{
    IDataReader reader = command.ExecuteReader();
    if (reader.Read())
        return GetItemFromReader<T>(reader, mappingInfo);
    else
        return default(T);
}

/// <summary>
/// Get a collection of objects from the database by using the attribute 
/// information
/// </summary>
/// <typeparam name="T>the type of object the collection will 

hold</typeparam>
/// <param name="command">DbCommand that is used to read data 

from the 
/// database</param>
/// <returns>a collection of populated objects from the 

database</returns>
public static List<T> ReadCollection<T>(IDbCommand command) where T 

: class
{
    List<T> collection = new List<T>();
    IDataReader reader = command.ExecuteReader();
    while (reader.Read())
    {
        T item = GetItemFromReader<T>(reader);
        collection.Add(item);
    }

    return collection;
}

/// <summary>
/// Get a collection of objects from the database by using the mapping 
/// information provided 
/// by Mapper class
/// </summary>
/// <typeparam name="T">the type of object the collection will 

hold</typeparam>
/// <param name="command">DbCommand that is used to read data 

from the 
/// database</param>
/// <returns>a collection of populated objects from the 

database</returns>
public static List<T> ReadCollection<T>(IDbCommand command, 
    Mapper mappingInfo) where T : class
{
    List<T> collection = new List<T>();
    IDataReader reader = command.ExecuteReader();
    while (reader.Read())
    {
        T item = GetItemFromReader<T>(reader, mappingInfo);
        collection.Add(item);
    }

    return collection;
}
}

DBHelper是一个静态类，这意味着它不需要被实例化，并且所有方法也都是静态的。它之所以是泛型的，是因为您提供了要从数据库加载的对象类型，然后该类会创建对象并从读取的表中填充其公共属性（即已用DBField属性修饰的属性或提供了映射信息）。

从上面的代码可以看出，我们使用了泛型约束，这意味着泛型类型T必须是一个对象（引用类型），而不能是值类型（如int、float、byte等基本类型，或也是值类型的struct）。上面的类还使用反射来检查属性是否设置了DBField，如果设置了，代码将读取属性，从属性中获取字段名称，并从表中读取数据。

使用代码

使用上面的类非常容易，从下面的示例中可以看出。假设我们有一个包含人员详细信息的表，其定义如下：

以及一个如下代码的Person类：

public class Person
{
    private int mID;
    private string mName;
    private string mSurname;
    private DateTime mDob;
    private string mProfession;

    public Person()
    {
    }


    [DBField("ID")]
    public int ID
    {
        get { return mID; }
        set { mID = value; }
    }

    [DBField("Name")]
    public string Name
    {
        get { return mName; }
        set { mName = value; }
    }

    [DBField("Surname")]
    public string Surname
    {
        get { return mSurname; }
        set { mSurname = value; }
    }

    [DBField("DOB")]
    public DateTime DateOfBirth
    {
        get { return mDob; }
        set { mDob = value; }
    }

    [DBField("Profession")]
    public string Profession
    {
        get { return mProfession; }
        set { mProfession = value; }
    }


    public int CalculateAge()
    {
        int age = DateTime.Now.Year - mDob.Year;
        return age;
    }
}

从Person类的代码中可以看出，公共属性被DBField属性修饰。每个属性都对应表中的字段名。现在，要从表中读取数据并返回填充的对象，我们将如下使用DBHelper类：

public List<Person> GetAllPersonsFromDB()
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        SqlCommand command = new SqlCommand("Select * from Persons order 

by 
            _Surname", connection);
        connection.Open();
        List<Person> persons = 

DBHelper.ReadCollection<Person>(command);
        return persons;
    }
}

或者，如果您需要检索一个特定的对象，您可以使用以下代码：

public Person GetPersonByID(int id)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        SqlCommand command = new SqlCommand("Select * from Persons where 

ID 
            _= @ID", connection);
        SqlParameter param = command.Parameters.Add("@ID", 

SqlDbType.Int);
        param.Value = id;

        connection.Open();
        Person person = DBHelper.ReadObject<Person>(command);
        return person;
    }
}

如果出于某种原因，您需要从另一个表中获取数据，并且该表的字段命名方式与属性不同，我们需要通过使用Mapper类向DBHelper提供映射信息。

示例：如果我们想从另一个表中读取数据，并且表定义如下：

要从上表读取数据，我们需要如下向DBHelper提供映射信息：

public List<Person> GetAllPersonsFromDB()
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        SqlCommand command = new SqlCommand("Select * from Persons order 

by 
            _Surname", connection);
        Mapper mapper = new Mapper();
        
        // Provide the mapping information in format: "[Property Name]=
        // [Field Name]" for the appropriate fields 
        mapper.Add("ID=ID", "Name=FirstName", 

"Surname=Surname", 
            _"Profession=Profession", 

"DateOfBirth=DateOfBirth");

        connection.Open();
        List<Person> persons = 

DBHelper.ReadCollection<Person>(command, 
            _mapper);
        return persons;
    }
}

从上面的示例可以看出，DBHelper类可以在不同的项目中使用，以便以方便的方式从数据库中获取数据并将其转换为对象。所以第一步是声明具有默认构造函数（无参构造函数）的适当类，并提供带get/set方法的公共属性，然后使用属性上的DBField属性将它们映射到数据库字段，或使用Mapper类提供映射信息。

这个类可以扩展以支持将对象存储到数据库。

历史

我已更改源代码，因此现在 DBHelper类可用于将业务对象插入/更新到数据库。我还创建了一个小型演示程序（附加到源解决方案）。