yabaaaaa.a

人事管理系统

using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;
using System.Configuration;

namespace DBManage
{
    public static class DBOperate
    {
        private static string connStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
        private static string dboOwner = ConfigurationManager.ConnectionStrings["DataBaseOwner"].ToString();

        /// <summary>
        /// 输入表名(或视图名)和约束条件，返回表中所有行
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public static DataSet GetModels(string tableName, string strWhere)
        {
            DataSet dataset = new DataSet();
            string sql = string.Format("select * from {0}", tableName);
            if (!string.IsNullOrEmpty(strWhere))
            {
                sql += " where " + strWhere;
            }
            return GetModels(sql);
        }

        /// <summary>
        /// 返回一行或多行数据，该方法执行存储过程。
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="list"></param>
        /// <returns></returns>
        public static DataSet GetModels(string sql, SqlParameter[] parameters)
        {
            DataSet dataset = new DataSet();
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                SqlCommand command = new SqlCommand(dboOwner + sql, conn);
                command.CommandType = CommandType.StoredProcedure;

                foreach (SqlParameter parameter in parameters)
                {
                    command.Parameters.Add(parameter);
                }

                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = command;

                conn.Open();
                da.Fill(dataset);
            }
            return dataset;
        }

        /// <summary>
        /// 返回一行或多行数据，该方法执行单一SQL语句。
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static DataSet GetModels(string sql)
        {
            DataSet dataset = new DataSet();
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                SqlCommand command = new SqlCommand(sql, conn);

                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = command;

                conn.Open();
                da.Fill(dataset);
            }
            return dataset;
        }

        /// <summary>
        /// 获得操作影响的行数（例如：删除、修改、增加），该方法调用存储过程。
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="list"></param>
        /// <returns></returns>
        public static int UpdateModel(string sql, SqlParameter[] parameters)
        {
            int rs = 0;
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                SqlCommand command = new SqlCommand(dboOwner + sql, conn);
                command.CommandType = CommandType.StoredProcedure;

                foreach (SqlParameter parameter in parameters)
                {
                    command.Parameters.Add(parameter);
                }

                conn.Open();
                rs = command.ExecuteNonQuery();
            }
            return rs;
        }

        /// <summary>
        /// 获得操作影响的行数（例如：删除、修改、增加），该方法执行单一SQL语句。
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static int UpdateModel(string sql)
        {
            int rs = 0;
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                SqlCommand command = new SqlCommand(sql, conn);
                conn.Open();
                rs = command.ExecuteNonQuery();
            }
            return rs;
        }


        /// <summary>
        /// 查询单一的一个数据（某一行的某一列），该方法调用存储过程。
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="list"></param>
        /// <returns></returns>
        public static object getOneData(string sql, SqlParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                SqlCommand command = new SqlCommand(dboOwner + sql, conn);
                command.CommandType = CommandType.StoredProcedure;

                foreach (SqlParameter parameter in parameters)
                {
                    command.Parameters.Add(parameter);
                }

                conn.Open();
                return command.ExecuteScalar();
            }
        }

        /// <summary>
        /// 查询单一的一个数据（某一行的某一列），该方法执行单一SQL语句。
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static object getOneData(string sql)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                SqlCommand command = new SqlCommand(sql, conn);
                conn.Open();
                return command.ExecuteScalar();
            }
        }

        /// <summary>
        /// 该查询执行分页功能，输入表名，页码，每页数据容量，排序字段，筛选条件，返回DataSet；
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="orderColumnsName"></param>
        /// <param name="whereStr"></param>
        /// <returns></returns>
        public static DataSet GetModels(string tableName, int pageIndex, int pageSize, string orderColumnsName, string strWhere)
        {
            DataSet dataset = new DataSet();
            string sql = @"select * from
                        (
                        select *,row_number() over(order by {0}) as rowid From {1}";
                        
            if (!string.IsNullOrEmpty(strWhere))
            {
                sql += " and " + strWhere;
            } 
            sql+=") as rank where rowid>{2} and rowid <={3}";
            if (!string.IsNullOrEmpty(strWhere))
            {
                sql += " and " + strWhere;
            } 
            sql=string.Format(sql, orderColumnsName, tableName, ((pageIndex - 1) * pageSize), (pageIndex * pageSize));
            return GetModels(sql);
        }

        /// <summary>
        /// 输入表名和筛选条件，获得数据行数量
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public static int GetTotalCount(string tableName, string strWhere)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                string sql = string.Format("select count(*) from {0} ", tableName);
                if (!string.IsNullOrEmpty(strWhere))
                {
                    sql = sql + " where " + strWhere;
                }
                SqlCommand command = new SqlCommand(sql, conn);
                conn.Open();
                return Convert.ToInt32(command.ExecuteScalar());
            }
        }
    }
}