数据访问层——表操作的封装

一个对数据库操作的封装类
调试环境:Visual Stdio 2005,C#.net
/// <summary>
/// 表数据基本操作
/// </summary>
public class DBTableOpt
{
    protected DBRecordInfo record;
    /// <summary>
    /// 构造函数,必须指定记录信息
    /// 您可以直接使用类名访问方法，设计者设计了两套实现方案
    /// </summary>
    public DBTableOpt(DBRecordInfo record)
    {
        this.record = record;
    }
    #region 插入操作
    /// <summary>
    /// TableBaseOpt: 插入操作
    /// </summary>
    public static int Insert(DBRecordInfo record)
    {
        int effectNum = 0;
        string fields = "", values = "";
        SqlParameter[] paraItem = GetItemParameters(record);
        SqlConnection conn = null;
        SqlCommand command = new SqlCommand();
        SQLServerDAL dctrl = new SQLServerDAL();
        for (int i = 1; i < paraItem.Length; i++)
        {
            if (record.FieldValue != null && record.FieldValue.ToString().Trim() != "")
            {
                fields += record.FieldName + ", ";
                values += paraItem.ParameterName + ", ";
                paraItem.Value = record.FieldValue;
                command.Parameters.Add(paraItem);
            }
        }
        if (fields == "" || values == "")
            return 0;
        string INSERT_CONTRACT_SQL = "insert into " + record.TableName + "(" + fields.Substring(0, fields.Length - 2) + ") values (" +
            values.Substring(0, values.Length - 2) + ")";
        dctrl.connectionData(ref conn);
        command.Connection = conn;
        command.CommandText = INSERT_CONTRACT_SQL;
        try
        {
            conn.Open();
            effectNum = command.ExecuteNonQuery();
        }
        catch
        {
            effectNum = -1;
            //throw;
        }
        finally
        {
            command.Dispose();
            conn.Close();
        }
        return effectNum;
    }
    /// <summary>
    /// TableBaseOpt: 插入操作
    /// </summary>
    public int Insert()
    {
        return Insert(this.record);
    }
    #endregion
    #region 删除操作
    /// <summary>
    /// TableBaseOpt: 删除操作
    /// </summary>
    public static int Delete(string tableName, string condition)
    {
        int effectNum = 0;
        string DELETE_CONTRACT_SQL = "delete from " + tableName;
        SqlConnection con = null;
        SQLServerDAL dctrl = new SQLServerDAL();
        SqlCommand command = new SqlCommand();
        if (condition != null && condition.Trim() != "")
            DELETE_CONTRACT_SQL += " where " + condition;
        dctrl.connectionData(ref con);
        command.Connection = con;
        command.CommandText = DELETE_CONTRACT_SQL;
        try
        {
            con.Open();
            effectNum = command.ExecuteNonQuery();
        }
        catch
        {
            effectNum = -1;
            //throw;
        }
        finally
        {
            command.Dispose();
            con.Close();
        }
        return effectNum;
    }
    /// <summary>
    /// TableBaseOpt: 删除操作
    /// </summary>
    public int Delete(string condition)
    {
        return Delete(this.record.TableName, condition);
    }
    #endregion
    #region 修改操作
    /// <summary>
    /// Edit的原始操作,屏蔽
    /// </summary>
    private static int Edit(string tableName, string condition, string changeStr)
    {
        int effectNum = -1;
        string DELETE_CONTRACT_SQL = "update " + tableName + " set ";
        SqlConnection con = null;
        SQLServerDAL dctrl = new SQLServerDAL();
        SqlCommand command = new SqlCommand();
        DELETE_CONTRACT_SQL += changeStr;
        if (condition != null && condition.Trim() != "")
            DELETE_CONTRACT_SQL += " where " + condition;
        dctrl.connectionData(ref con);
        command.Connection = con;
        command.CommandText = DELETE_CONTRACT_SQL;
        try
        {
            con.Open();
            effectNum = command.ExecuteNonQuery();
        }
        catch
        {
            effectNum = -1;
            //throw;
        }
        finally
        {
            command.Dispose();
            con.Close();
        }
        return effectNum;
    }
    /// <summary>
    /// TableBaseOpt: 修改操作
    /// change保存了所有的操作，格式为{{fieldName,fieldValue}, {fieldName,fieldValue}, ...}
    /// </summary>
    public static int Edit(string tableName, string condition, string[][] change)
    {
        string changstr = "";
        foreach (string[] chgCmpr in change)
        {
            changstr += chgCmpr[0] + " = " + chgCmpr[1] + " , ";
        }
        return Edit(tableName, condition, changstr.Substring(0, changstr.Length - 2));
    }
    /// <summary>
    /// TableBaseOpt: 修改操作
    /// change保存了所有的操作，格式为{{fieldName,fieldValue}, {fieldName,fieldValue}, ...}
    /// </summary>
    public int Edit(string condition, string[][] change)
    {
        return Edit(record.TableName, condition, change);
    }
    /// <summary>
    /// TableBaseOpt: 修改操作
    /// 根据record记录的修改信息更新表
    /// </summary>
    public static int Edit(DBRecordInfo record, string condition)
    {
        string changeMsg = "";
        for (int i = 0; i < record.Length; i++)
        {
            if (record.IsChanged)
            {
                if (record.FieldType == SqlDbType.Char || record.FieldType == SqlDbType.DateTime || record.FieldType == SqlDbType.NChar ||
                    record.FieldType == SqlDbType.NText || record.FieldType == SqlDbType.NVarChar || record.FieldType == SqlDbType.SmallDateTime ||
                    record.FieldType == SqlDbType.Text || record.FieldType == SqlDbType.VarChar || record.FieldType == SqlDbType.Xml)
                    changeMsg += record.FieldName + " = '" + record.FieldValue.ToString().Trim() + "' , ";
                else
                    changeMsg += record.FieldName + " = " + record.FieldValue + " , ";
            }
        }
        if (changeMsg == "")
            return 0;
        return Edit(record.TableName, condition, changeMsg.Substring(0, changeMsg.Length - 2));
    }
    /// <summary>
    /// TableBaseOpt: 修改操作
    /// 根据record记录的修改信息更新表
    /// </summary>
    public int Edit(string condition)
    {
        return Edit(this.record, condition);
    }
    #endregion
    #region 选择操作
    /// <summary>
    /// TableBaseOpt: 选择操作
    /// </summary>
    public static DataTable Select(string field, string table, string condition, string groupBy, string having, string order)
    {
        DataTable dt = null;
        string SELECT_CONTRACT_SQL = "Select ";
        if (field != null && field.Trim() != "")
            SELECT_CONTRACT_SQL += field;
        else
            SELECT_CONTRACT_SQL += "*";
        SELECT_CONTRACT_SQL += " From " + table;
        if (condition != null && condition.Trim() != "")
            SELECT_CONTRACT_SQL += " Where " + condition;
        if (groupBy != null && groupBy.Trim() != "")
            SELECT_CONTRACT_SQL += " Group by " + groupBy;
        if (having != null && having.Trim() != "")
            SELECT_CONTRACT_SQL += " Having " + having;
        if (order != null && order.Trim() != "")
            SELECT_CONTRACT_SQL += " Order By " + order;
        SQLServerDAL dctrl = new SQLServerDAL();
        try
        {
            dctrl.connectionData();
            dt = dctrl.returnRecordSet(SELECT_CONTRACT_SQL).Tables[0];
        }
        catch
        {
            dt = null;
            //throw;
        }
        finally
        {
            dctrl.closeConnect();
        }
        return dt;
    }
    /// <summary>
    /// TableBaseOpt: 选择操作
    /// </summary>
    public DataTable Select(string field, string condition, string groupBy, string having, string order)
    {
        return Select(field, this.record.TableName, condition, groupBy, having, order);
    }
    /// <summary>
    /// TableBaseOpt: 选择操作
    /// </summary>
    public DataTable Select(string condition, string groupBy, string having, string order)
    {
        return Select(null, this.record.TableName, condition, groupBy, having, order);
    }
    #endregion
    /// <summary>
    ///  TableBaseOpt: 获取记录参数信息
    /// </summary>
    protected static SqlParameter[] GetItemParameters(DBRecordInfo record)
    {
        SqlParameter[] paras = new SqlParameter[record.Length];
        for (int i = 0; i < record.Length; i++)
            paras = new SqlParameter("@" + record.FieldName, record.FieldType, record.FieldSize);
        return paras;
    }
    /// <summary>
    ///  TableBaseOpt: 获取记录参数信息
    /// </summary>
    protected SqlParameter[] GetItemParameters()
    {
        return GetItemParameters(this.record);
    }
}