dboperhandler.cs

java编写的邮件群发软件

        public object GetValue(string _tableName, string _fieldName)
        {
            this.tableName = _tableName;
            this.fieldName = _fieldName;
            this.SqlCmd = "select " + this.fieldName + " from " + this.tableName;
            if (this.ConditionExpress != string.Empty)
            {
                this.SqlCmd = this.SqlCmd + " where " + this.ConditionExpress;
            }
            this.cmd.CommandText = this.SqlCmd;
            this.GenParameters();
            object ret = cmd.ExecuteScalar();
            if (ret == null) ret = (object)string.Empty;
            return ret;
        }

        /// <summary>
        /// 获取指定表，指定列，指定条件的第一行中符合条件的值的集合。
        /// </summary>
        /// <param name="_tableName">表名称。</param>
        /// <param name="_fieldNames">字段名称,以逗号隔开。</param>
        /// <returns>获取的值。如果为空则返回null。</returns>
        public object[] GetValues(string _tableName, string _fieldNames)
        {
            this.SqlCmd = "select " + _fieldNames + " from " + _tableName;
            if (this.ConditionExpress != string.Empty)
            {
                this.SqlCmd = this.SqlCmd + " where " + this.ConditionExpress;
            }
            this.cmd.CommandText = this.SqlCmd;
            this.GenParameters();
            System.Data.DataSet ds = new System.Data.DataSet();
            this.da.SelectCommand = this.cmd;
            this.da.Fill(ds);
            DataTable dt = ds.Tables[0];
            if (dt.Rows.Count > 0)
            {
                object[] _obj = new object[dt.Columns.Count];
                for (int i = 0; i < dt.Columns.Count; i++)
                    _obj[i] = dt.Rows[0][i];
                return _obj;
            }
            return null;
        }

        /// <summary>
        /// 获取指定表，指定列，指定条件的记录数。
        /// </summary>
        /// <param name="_tableName">表名称。</param>
        /// <param name="_fieldName">字段名称。</param>
        /// <returns>获取的记录数</returns>
        public int GetCount(string _tableName, string _fieldName)
        {
            this.tableName = _tableName;
            this.fieldName = _fieldName;
            this.SqlCmd = "select count(" + this.fieldName + ") from " + this.tableName;
            if (this.ConditionExpress != string.Empty)
            {
                this.SqlCmd = this.SqlCmd + " where " + this.ConditionExpress;
            }
            this.cmd.CommandText = this.SqlCmd;
            this.GenParameters();
            return (int)cmd.ExecuteScalar();
        }

        /// <summary>
        /// 根据参数和ConditionExpress获取DataTable,需要分页时使用,不要ConditionExpress中使用order
        /// </summary>
        /// <param name="_tableName">表名</param>
        /// <param name="_fieldNames">字段名集合,用逗号分开</param>
        /// <param name="_identyColumn">自增字段</param>
        /// <param name="IsIdDesc">是否按照自增字段倒序</param>
        /// <param name="_currentPage">当前页</param>
        /// <param name="pageSize">页大小</param>
        /// <param name="_rowsCount">总记录数</param>
        /// <returns>获取到的DataTable</returns>
        public DataTable GetDataTable(string _tableName, string _fieldNames, string _identyColumn, bool IsIdDesc, int _currentPage, int pageSize, ref int _rowsCount)
        {
            string whereStr = "";
            string sqlStr = " from " + _tableName;
            string orderStr = " order by " + _identyColumn;
            if (IsIdDesc)
                orderStr += " desc";
            if (this.ConditionExpress != string.Empty)
            {
                whereStr = " where " + this.ConditionExpress;
            }
            sqlStr += whereStr;
            this.SqlCmd = "select count(" + _identyColumn + ") " + sqlStr;
            this.cmd.CommandText = this.SqlCmd;
            this.GenParameters();
            _rowsCount = (int)cmd.ExecuteScalar();

            if (_currentPage > 1)
            {
                if (IsIdDesc)
                    sqlStr += " and (" + _identyColumn + " < (select MIN(" + _identyColumn + ") from ";
                else
                    sqlStr += " and (" + _identyColumn + " > (select MAX(" + _identyColumn + ") from ";
                sqlStr += "(select top " + (pageSize * (_currentPage - 1)) + " " + _identyColumn + " from " + _tableName + whereStr + orderStr + ") as t))";
            }
            sqlStr = "select top " + pageSize + " " + _fieldNames + sqlStr + orderStr;
            DataSet ds = new DataSet();
            this.cmd.CommandText = sqlStr;
            this.GenParameters();
            this.da.SelectCommand = this.cmd;
            this.da.Fill(ds);
            return ds.Tables[0];
        }

        /// <summary>
        /// 根据当前指定的SqlCmd获取DataTable。如果ConditionExpress不为空则会将其清空，所以条件表达式需要包含在SqlCmd中。
        /// </summary>
        /// <returns>返回查询结果DataTable。</returns>
        public DataTable GetDataTable()
        {
            System.Data.DataSet ds = this.GetDataSet();
            return ds.Tables[0];
        }
        /// <summary>
        /// 根据当前指定的SqlCmd获取DataSet。如果ConditionExpress不为空则会将其清空，所以条件表达式需要包含在SqlCmd中。
        /// </summary>
        /// <returns>返回查询结果DataSet。</returns>
        public DataSet GetDataSet()
        {
            this.alConditionParameters.Clear();
            this.ConditionExpress = string.Empty;
            this.cmd.CommandText = this.SqlCmd;
            this.GenParameters();
            DataSet ds = new DataSet();
            this.da.SelectCommand = this.cmd;
            this.da.Fill(ds);
            return ds;
        }
        /// <summary>
        /// 对指定表，指定字段执行加一计数，返回计数后的值。条件由ConditionExpress指定。
        /// </summary>
        /// <param name="_tableName">表名称。</param>
        /// <param name="_fieldName">字段名称。</param>
        /// <returns>返回计数后的值。</returns>
        public int Count(string _tableName, string _fieldName)
        {
            this.tableName = _tableName;
            this.fieldName = _fieldName;
            int count = Convert.ToInt32(this.GetValue(this.tableName, this.fieldName));
            count++;
            this.cmd.Parameters.Clear();
            this.cmd.CommandText = string.Empty;
            this.AddFieldItem(_fieldName, count);
            this.Update(this.tableName);
            return count;
        }

        /// <summary>
        /// 对指定表，指定字段执行减一计数，返回计数后的值。条件由ConditionExpress指定。
        /// </summary>
        /// <param name="_tableName">表名称。</param>
        /// <param name="_fieldName">字段名称。</param>
        /// <returns>返回计数后的值。</returns>
        public int Substract(string _tableName, string _fieldName)
        {
            this.tableName = _tableName;
            this.fieldName = _fieldName;
            int count = Convert.ToInt32(this.GetValue(this.tableName, this.fieldName));
            if (count > 0) count--;
            this.cmd.Parameters.Clear();
            this.cmd.CommandText = string.Empty;
            this.AddFieldItem(_fieldName, count);
            this.Update(this.tableName);
            return count;
        }

        /// <summary>
        /// 根据ConditionExpress指定的条件在指定表中删除记录。返回删除的记录数。
        /// </summary>
        /// <param name="_tableName">指定的表名称。</param>
        /// <returns>返回删除的记录数。</returns>
        public int Delete(string _tableName)
        {
            this.tableName = _tableName;
            this.SqlCmd = "delete from " + this.tableName;
            if (this.ConditionExpress != string.Empty)
            {
                this.SqlCmd = this.SqlCmd + " where " + this.ConditionExpress;
            }
            this.cmd.CommandText = this.SqlCmd;
            this.GenParameters();
            return cmd.ExecuteNonQuery();
        }

        /// <summary>
        /// 审核函数。将指定表，指定字段的值进行翻转，如：1->0或0->1。条件由ConditionExpress指定。
        /// </summary>
        /// <param name="_tableName">表名称。</param>
        /// <param name="_fieldName">字段名称。</param>
        /// <returns>返回影响的行数。</returns>
        public int Audit(string _tableName, string _fieldName)
        {
            this.tableName = _tableName;
            this.fieldName = _fieldName;
            this.SqlCmd = "update " + this.tableName + " set " + this.fieldName + "=1-" + this.fieldName;
            if (this.ConditionExpress != string.Empty)
            {
                this.SqlCmd = this.SqlCmd + " where " + this.ConditionExpress;
            }
            this.cmd.CommandText = this.SqlCmd;
            this.GenParameters();
            return cmd.ExecuteNonQuery();
        }

        /// <summary>
        /// 释放资源
        /// </summary>
        public void Dispose()
        {
            conn.Close();
        }

    }

    /// <summary>
    /// 数据表中的字段属性，包括字段名，字段值。
    /// 常用于保存要提交的数据。
    /// </summary>
    public class DbKeyItem
    {
        /// <summary>
        /// 构造函数。
        /// </summary>
        /// <param name="_fieldName">字段名称。</param>
        /// <param name="_fieldValue">字段值。</param>
        public DbKeyItem(string _fieldName, object _fieldValue)
        {
            this.fieldName = _fieldName;
            this.fieldValue = _fieldValue.ToString();
        }
        /// <summary>
        /// 字段名称。
        /// </summary>
        public string fieldName;
        /// <summary>
        /// 字段值。
        /// </summary>
        public string fieldValue;
    }
}