C#/ASP.NET完善的DBHelper,配套Model生成器

1.前话

寒假实在太闲了,所以闲在粗俗地去探听“大数额”这个新定义,这几年四处都以说啊异常数量时代的,不能不被自家感觉到讶异啊。
特别数额有甚用?随便谷歌百度一雅堆我吗不多说了。
自身好之明是,单个数据价值微乎其微,但当数据量极大时,那么即便可由此截取统计海量数据来进行剖析,以此得出好来价之结果。

例如:

谷歌公司之觅数据库,这数据库数据量是宏大的,数据类也是高大的,它存储方世界各地用户使用谷歌时键入的摸关键词。

故此当我们针对那个进行特别数目挖掘,对找关键词中带有“药”的重点字展开统计分析,就好间接得出各个国家国民健康水平,病种分布,某病种发病的区域比重等,通过大数额挖掘,提取有用之音讯,然后得出了大生出价的消息。

以这种数据库数据量极大,且多为分布式存储,直接遍历分析困难,且大耗时。

因此出现了化解好数目如何扒的问题的艺——Hadoop


支持Oracle、MSSQL、MySQL、SQLite四栽数据库,支持工作,支持对象关系映射;已当多独品类被实际应用。

2.Hadoop是什么

本身直接度娘一截吧:

澳门美高梅手机网站 1

Hadoop是一个由Apache基金会所开发的分布式系统基础架构。

用户可在未打听分布式底层细节的情事下,开发分布式程序。充分利用集群的威力进行快捷运算和仓储。

Hadoop实现了一个分布式文件系统(Hadoop Distributed File
System),简称HDFS。HDFS有高容错性的性状,并且计划用来部署在物美价廉的(low-cost)硬件及;而且她提供高吞吐量(high
throughput)来做客应用程序的数目,适合那些负有超大数据集(large data
set)的应用程序。HDFS放宽了(relax)POSIX的要求,可以以流的花样拜访(streaming
access)文件系统中之多少。

Hadoop的框架最核心的筹划虽是:HDFS和MapReduce。HDFS为海量的数提供了储存,则MapReduce为海量的数码提供了匡。

总之,Hadoop是由于Java编写的相同学框架,它解决不行数据挖掘的问题,而我辈只需要编写相应的MapReduce程序即可。

好啊,其他的也未介绍了(其实别的自我耶不明了),先吧Hadoop整个运行框架搭建筑起来吧。

提拔:你需要肯定之linux基础 🙂


并未语法糖,学习成本几乎也0,拿来就是用。

3.框架搭建(Linux环境,Hadoop2.6.4)

官方文档入口 http://hadoop.apache.org/docs/r2.6.4/

Hadoop有三种植运行模式

  • 本机模式(一尊主机)
  • 私自分布模式(一雅主机)
  • 统统分布模式(多台主机,即集群)

本机模式直接解压,修改${HADOOP_PREFIX}/etc/hadoop/hadoop-env.sh,添加export JAVA_HOME=/usr/java/latest即可。

伪分布模式也直接就官方文档一步步布局即可。

私自分布模式是单机配置,将该单机作为master配置后的Hadoop程序文件夹分发给其他作为slaver的主机,增加部署内容即可实现全分布模式配置。


DBHelper类完整代码:

3.0 准备

以只有发生相同台笔记本,要效仿了分布模式就得动虚拟机了。
我使用的凡Oracle VirtualBox,这是一律款款免费之软件。

依配置实例需要之镜像相应安排如下:

hostname ip地址 系统
master 192.168.1.113 CentOS7
slaver01 192.168.1.114 CentOS7
slaver02 192.168.1.115 CentOS7

倘前所说,分发伪分布模式配置文件并加对应安排内容即可兑现全分布模式配置。

为此自只是待配置当master的主机,然后拷贝多片份系统镜像修改该hostname和ip地址即可。

同样高主机别名master作为全分布集群的NameNode主机,其他别名slaver*作为DataNode主机。

修改文件/etc/hostname

master

修改文件/etc/hosts

192.168.1.113   master
192.168.1.114   slaver01  
192.168.1.115   slaver02 

澳门美高梅手机网站 2澳门美高梅手机网站 3

3.1 安装Java配置环境(略)


using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.Objects.DataClasses;
using System.Data.OracleClient;
using System.Data.SqlClient;
using System.Data.SQLite;
using System.Reflection;
using System.Text;
using System.Text.RegularExpressions;
using Models;
using MySql.Data.MySqlClient;

/* ---------------------------------------------
 * 作    者:suxiang
 * 创建日期:2016年11月23日
 * --------------------------------------------- */

namespace DBUtil
{
    /// <summary>
    /// 数据库操作类
    /// </summary>
    public static class DBHelper
    {
        #region 变量
        /// <summary>
        /// 数据库类型
        /// </summary>
        private static string m_DBType = ConfigurationManager.AppSettings["DBType"];
        /// <summary>
        /// 数据库类型
        /// </summary>
        private static bool m_AutoIncrement = ConfigurationManager.AppSettings["AutoIncrement"].ToLower() == "true" ? true : false;
        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        private static string m_ConnectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString();
        /// <summary>
        /// 事务
        /// </summary>
        [ThreadStatic]
        private static DbTransaction m_Tran;
        /// <summary>
        /// 带参数的SQL插入和修改语句中,参数前面的符号
        /// </summary>
        private static string m_ParameterMark = GetParameterMark();
        #endregion

        #region 生成变量
        #region 生成 IDbCommand
        /// <summary>
        /// 生成 IDbCommand
        /// </summary>
        private static DbCommand GetCommand()
        {
            DbCommand command = null;

            switch (m_DBType)
            {
                case "oracle":
                    command = new OracleCommand();
                    break;
                case "mssql":
                    command = new SqlCommand();
                    break;
                case "mysql":
                    command = new MySqlCommand();
                    break;
                case "sqlite":
                    command = new SQLiteCommand();
                    break;
            }

            return command;
        }
        /// <summary>
        /// 生成 IDbCommand
        /// </summary>
        private static DbCommand GetCommand(string sql, DbConnection conn)
        {
            DbCommand command = null;

            switch (m_DBType)
            {
                case "oracle":
                    command = new OracleCommand(sql);
                    command.Connection = conn;
                    break;
                case "mssql":
                    command = new SqlCommand(sql);
                    command.Connection = conn;
                    break;
                case "mysql":
                    command = new MySqlCommand(sql);
                    command.Connection = conn;
                    break;
                case "sqlite":
                    command = new SQLiteCommand(sql);
                    command.Connection = conn;
                    break;
            }

            return command;
        }
        #endregion

        #region 生成 IDbConnection
        /// <summary>
        /// 生成 IDbConnection
        /// </summary>
        private static DbConnection GetConnection()
        {
            DbConnection conn = null;

            switch (m_DBType)
            {
                case "oracle":
                    conn = new OracleConnection(m_ConnectionString);
                    break;
                case "mssql":
                    conn = new SqlConnection(m_ConnectionString);
                    break;
                case "mysql":
                    conn = new MySqlConnection(m_ConnectionString);
                    break;
                case "sqlite":
                    conn = new SQLiteConnection(m_ConnectionString);
                    break;
            }

            return conn;
        }
        #endregion

        #region 生成 IDbDataAdapter
        /// <summary>
        /// 生成 IDbDataAdapter
        /// </summary>
        private static DbDataAdapter GetDataAdapter(DbCommand cmd)
        {
            DbDataAdapter dataAdapter = null;

            switch (m_DBType)
            {
                case "oracle":
                    dataAdapter = new OracleDataAdapter();
                    dataAdapter.SelectCommand = cmd;
                    break;
                case "mssql":
                    dataAdapter = new SqlDataAdapter();
                    dataAdapter.SelectCommand = cmd;
                    break;
                case "mysql":
                    dataAdapter = new MySqlDataAdapter();
                    dataAdapter.SelectCommand = cmd;
                    break;
                case "sqlite":
                    dataAdapter = new SQLiteDataAdapter();
                    dataAdapter.SelectCommand = cmd;
                    break;
            }

            return dataAdapter;
        }
        #endregion

        #region 生成 m_ParameterMark
        /// <summary>
        /// 生成 m_ParameterMark
        /// </summary>
        private static string GetParameterMark()
        {
            switch (m_DBType)
            {
                case "oracle":
                    return ":";
                case "mssql":
                    return "@";
                case "mysql":
                    return "@";
                case "sqlite":
                    return ":";
            }
            return ":";
        }
        #endregion

        #region 生成 DbParameter
        /// <summary>
        /// 生成 DbParameter
        /// </summary>
        private static DbParameter GetDbParameter(string name, object vallue)
        {
            DbParameter dbParameter = null;

            switch (m_DBType)
            {
                case "oracle":
                    dbParameter = new OracleParameter(name, vallue);
                    break;
                case "mssql":
                    dbParameter = new SqlParameter(name, vallue);
                    break;
                case "mysql":
                    dbParameter = new MySqlParameter(name, vallue);
                    break;
                case "sqlite":
                    dbParameter = new SQLiteParameter(name, vallue);
                    break;
            }

            return dbParameter;
        }
        #endregion
        #endregion

        #region 基础方法
        #region  执行简单SQL语句
        #region Exists
        public static bool Exists(string sqlString)
        {
            SqlFilter(ref sqlString);
            using (DbConnection conn = GetConnection())
            {
                using (DbCommand cmd = GetCommand(sqlString, conn))
                {
                    try
                    {
                        conn.Open();
                        object obj = cmd.ExecuteScalar();
                        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                        {
                            return false;
                        }
                        else
                        {
                            return true;
                        }
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                    finally
                    {
                        cmd.Dispose();
                        conn.Close();
                    }
                }
            }
        }
        #endregion

        #region 执行SQL语句,返回影响的记录数
        /// <summary>
        /// 执行SQL语句,返回影响的记录数
        /// </summary>
        /// <param name="sqlString">SQL语句</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteSql(string sqlString)
        {
            SqlFilter(ref sqlString);
            DbConnection conn = m_Tran == null ? GetConnection() : m_Tran.Connection;
            using (DbCommand cmd = GetCommand(sqlString, conn))
            {
                try
                {
                    if (conn.State != ConnectionState.Open) conn.Open();
                    if (m_Tran != null) cmd.Transaction = m_Tran;
                    int rows = cmd.ExecuteNonQuery();
                    return rows;
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
                finally
                {
                    cmd.Dispose();
                    if (m_Tran == null) conn.Close();
                }
            }
        }
        #endregion

        #region 执行一条计算查询结果语句,返回查询结果
        /// <summary>
        /// 执行一条计算查询结果语句,返回查询结果(object)
        /// </summary>
        /// <param name="sqlString">计算查询结果语句</param>
        /// <returns>查询结果(object)</returns>
        public static object GetSingle(string sqlString)
        {
            SqlFilter(ref sqlString);
            using (DbConnection conn = GetConnection())
            {
                using (DbCommand cmd = GetCommand(sqlString, conn))
                {
                    try
                    {
                        if (conn.State != ConnectionState.Open) conn.Open();
                        object obj = cmd.ExecuteScalar();
                        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                        {
                            return null;
                        }
                        else
                        {
                            return obj;
                        }
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                    finally
                    {
                        cmd.Dispose();
                    }
                }
            }
        }
        #endregion

        #region 执行查询语句,返回IDataReader
        /// <summary>
        /// 执行查询语句,返回IDataReader ( 注意:调用该方法后,一定要对IDataReader进行Close )
        /// </summary>
        /// <param name="sqlString">查询语句</param>
        /// <returns>IDataReader</returns>
        public static DbDataReader ExecuteReader(string sqlString)
        {
            SqlFilter(ref sqlString);
            DbConnection conn = GetConnection();
            DbCommand cmd = GetCommand(sqlString, conn);
            try
            {
                if (conn.State != ConnectionState.Open) conn.Open();
                DbDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return myReader;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region 执行查询语句,返回DataSet
        /// <summary>
        /// 执行查询语句,返回DataSet
        /// </summary>
        /// <param name="sqlString">查询语句</param>
        /// <returns>DataSet</returns>
        public static DataSet Query(string sqlString)
        {
            SqlFilter(ref sqlString);
            using (DbConnection conn = GetConnection())
            {
                DataSet ds = new DataSet();
                try
                {
                    conn.Open();
                    using (DbCommand cmd = GetCommand(sqlString, conn))
                    {
                        DbDataAdapter adapter = GetDataAdapter(cmd);
                        adapter.Fill(ds, "ds");
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    conn.Close();
                }
                return ds;
            }
        }
        #endregion

        #region SQL过滤,防注入
        /// <summary>
        /// SQL过滤,防注入
        /// </summary>
        /// <param name="sql">sql</param>
        public static void SqlFilter(ref string sql)
        {
            sql = sql.Trim();
            List<string> keywordList = new List<string>() { 
                "net localgroup",
                "net user",
                "xp_cmdshell",
                "exec",
                "execute",
                "truncate",
                "drop",
                "restore",
                "create",
                "alter",
                "rename",
                "insert",
                "update",
                "delete",
                "select"};
            string ignore = string.Empty;
            string upperSql = sql.ToUpper();
            foreach (string keyword in keywordList)
            {
                if (upperSql.IndexOf(keyword.ToUpper()) == 0)
                {
                    ignore = keyword;
                }
            }
            foreach (string keyword in keywordList)
            {
                if (ignore == "select" && ignore == keyword) continue;
                Regex regex = new Regex(keyword, RegexOptions.IgnoreCase);
                sql = sql.Substring(0, ignore.Length) + regex.Replace(sql.Substring(ignore.Length), string.Empty);
            }
        }
        #endregion
        #endregion

        #region 执行带参数的SQL语句
        #region 执行SQL语句,返回影响的记录数
        /// <summary>
        /// 执行SQL语句,返回影响的记录数
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteSql(string SQLString, params DbParameter[] cmdParms)
        {
            DbConnection conn = m_Tran == null ? GetConnection() : m_Tran.Connection;
            using (DbCommand cmd = GetCommand())
            {
                try
                {
                    PrepareCommand(cmd, conn, m_Tran, SQLString, cmdParms);
                    int rows = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                    return rows;
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    cmd.Dispose();
                    if (m_Tran == null) conn.Close();
                }
            }
        }
        #endregion

        #region 执行查询语句,返回IDataReader
        /// <summary>
        /// 执行查询语句,返回IDataReader ( 注意:调用该方法后,一定要对IDataReader进行Close )
        /// </summary>
        /// <param name="strSQL">查询语句</param>
        /// <returns>IDataReader</returns>
        public static DbDataReader ExecuteReader(string sqlString, params DbParameter[] cmdParms)
        {
            DbConnection conn = GetConnection();
            DbCommand cmd = GetCommand();
            try
            {
                PrepareCommand(cmd, conn, null, sqlString, cmdParms);
                DbDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return myReader;
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }
        #endregion

        #region 执行查询语句,返回DataSet
        /// <summary>
        /// 执行查询语句,返回DataSet
        /// </summary>
        /// <param name="sqlString">查询语句</param>
        /// <returns>DataSet</returns>
        public static DataSet Query(string sqlString, params DbParameter[] cmdParms)
        {
            DbConnection conn = GetConnection();
            DbCommand cmd = GetCommand();
            PrepareCommand(cmd, conn, null, sqlString, cmdParms);
            using (DbDataAdapter da = GetDataAdapter(cmd))
            {
                DataSet ds = new DataSet();
                try
                {
                    da.Fill(ds, "ds");
                    cmd.Parameters.Clear();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    cmd.Dispose();
                    conn.Close();
                }
                return ds;
            }
        }
        #endregion

        #region PrepareCommand
        private static void PrepareCommand(DbCommand cmd, DbConnection conn, DbTransaction trans, string cmdText, DbParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open) conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null) cmd.Transaction = trans;
            cmd.CommandType = CommandType.Text;
            if (cmdParms != null)
            {
                foreach (DbParameter parm in cmdParms)
                {
                    cmd.Parameters.Add(parm);
                }
            }
        }
        #endregion
        #endregion
        #endregion

        #region 增删改查
        #region 获取最大编号
        /// <summary>
        /// 获取最大编号
        /// </summary>
        /// <typeparam name="T">实体Model</typeparam>
        /// <param name="key">主键</param>
        public static int GetMaxID<T>(string key)
        {
            Type type = typeof(T);

            string sql = null;
            switch (m_DBType)
            {
                case "oracle":
                    sql = string.Format("SELECT Max({0}) FROM {1}", key, type.Name);
                    break;
                case "mssql":
                    sql = string.Format("SELECT Max({0}) FROM {1}", key, type.Name);
                    break;
                case "mysql":
                    sql = string.Format("SELECT Max({0}) FROM {1}", key, type.Name);
                    break;
                case "sqlite":
                    sql = string.Format("SELECT Max(cast({0} as int)) FROM {1}", key, type.Name);
                    break;
            }

            using (DbConnection conn = GetConnection())
            {
                using (IDbCommand cmd = GetCommand(sql, conn))
                {
                    try
                    {
                        conn.Open();
                        object obj = cmd.ExecuteScalar();
                        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                        {
                            return 1;
                        }
                        else
                        {
                            return int.Parse(obj.ToString()) + 1;
                        }
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                    finally
                    {
                        cmd.Dispose();
                        conn.Close();
                    }
                }
            }
        }
        #endregion

        #region 添加
        /// <summary>
        /// 添加
        /// </summary>
        public static void Insert(object obj)
        {
            Insert(obj, m_AutoIncrement);
        }
        /// <summary>
        /// 添加
        /// </summary>
        public static void Insert(object obj, bool autoIncrement)
        {
            StringBuilder strSql = new StringBuilder();
            Type type = obj.GetType();
            strSql.Append(string.Format("insert into {0}(", type.Name));

            PropertyInfo[] propertyInfoList = GetEntityProperties(type);
            List<string> propertyNameList = new List<string>();
            int savedCount = 0;
            foreach (PropertyInfo propertyInfo in propertyInfoList)
            {
                if (propertyInfo.GetCustomAttributes(typeof(IsIdAttribute), false).Length > 0 && autoIncrement) return;
                if (propertyInfo.GetCustomAttributes(typeof(IsDBFieldAttribute), false).Length > 0)
                {
                    propertyNameList.Add(propertyInfo.Name);
                    savedCount++;
                }
            }

            strSql.Append(string.Format("{0})", string.Join(",", propertyNameList.ToArray())));
            strSql.Append(string.Format(" values ({0})", string.Join(",", propertyNameList.ConvertAll<string>(a => m_ParameterMark + a).ToArray())));
            DbParameter[] parameters = new DbParameter[savedCount];
            int k = 0;
            for (int i = 0; i < propertyInfoList.Length && savedCount > 0; i++)
            {
                PropertyInfo propertyInfo = propertyInfoList[i];
                if (propertyInfo.GetCustomAttributes(typeof(IsIdAttribute), false).Length > 0 && autoIncrement) return;
                if (propertyInfo.GetCustomAttributes(typeof(IsDBFieldAttribute), false).Length > 0)
                {
                    object val = propertyInfo.GetValue(obj, null);
                    DbParameter param = GetDbParameter(m_ParameterMark + propertyInfo.Name, val == null ? DBNull.Value : val);
                    parameters[k++] = param;
                }
            }

            ExecuteSql(strSql.ToString(), parameters);
        }
        #endregion

        #region 修改
        /// <summary>
        /// 修改
        /// </summary>
        public static void Update(object obj)
        {
            object oldObj = Find(obj, false);
            if (oldObj == null) throw new Exception("无法获取到旧数据");

            StringBuilder strSql = new StringBuilder();
            Type type = obj.GetType();
            strSql.Append(string.Format("update {0} ", type.Name));

            PropertyInfo[] propertyInfoList = GetEntityProperties(type);
            List<string> propertyNameList = new List<string>();
            int savedCount = 0;
            foreach (PropertyInfo propertyInfo in propertyInfoList)
            {
                if (propertyInfo.GetCustomAttributes(typeof(IsDBFieldAttribute), false).Length > 0)
                {
                    object oldVal = propertyInfo.GetValue(oldObj, null);
                    object val = propertyInfo.GetValue(obj, null);
                    if (!object.Equals(oldVal, val))
                    {
                        propertyNameList.Add(propertyInfo.Name);
                        savedCount++;
                    }
                }
            }

            strSql.Append(string.Format(" set "));
            DbParameter[] parameters = new DbParameter[savedCount];
            StringBuilder sbPros = new StringBuilder();
            int k = 0;
            for (int i = 0; i < propertyInfoList.Length && savedCount > 0; i++)
            {
                PropertyInfo propertyInfo = propertyInfoList[i];
                if (propertyInfo.GetCustomAttributes(typeof(IsDBFieldAttribute), false).Length > 0)
                {
                    object oldVal = propertyInfo.GetValue(oldObj, null);
                    object val = propertyInfo.GetValue(obj, null);
                    if (!object.Equals(oldVal, val))
                    {
                        sbPros.Append(string.Format(" {0}={1}{0},", propertyInfo.Name, m_ParameterMark));
                        DbParameter param = GetDbParameter(m_ParameterMark + propertyInfo.Name, val == null ? DBNull.Value : val);
                        parameters[k++] = param;
                    }
                }
            }
            if (sbPros.Length > 0)
            {
                strSql.Append(sbPros.ToString(0, sbPros.Length - 1));
            }
            strSql.Append(string.Format(" where {0}='{1}'", GetIdName(obj.GetType()), GetIdVal(obj).ToString()));

            if (savedCount > 0)
            {
                ExecuteSql(strSql.ToString(), parameters);
            }
        }
        #endregion

        #region 删除
        /// <summary>
        /// 根据Id删除
        /// </summary>
        public static void Delete<T>(int id)
        {
            Type type = typeof(T);
            StringBuilder sbSql = new StringBuilder();
            DbParameter[] cmdParms = new DbParameter[1];
            cmdParms[0] = GetDbParameter(m_ParameterMark + GetIdName(type), id);
            sbSql.Append(string.Format("delete from {0} where {2}={1}{2}", type.Name, m_ParameterMark, GetIdName(type)));

            ExecuteSql(sbSql.ToString(), cmdParms);
        }
        /// <summary>
        /// 根据Id集合删除
        /// </summary>
        public static void BatchDelete<T>(string ids)
        {
            if (string.IsNullOrWhiteSpace(ids)) return;

            Type type = typeof(T);
            StringBuilder sbSql = new StringBuilder();
            string[] idArr = ids.Split(',');
            DbParameter[] cmdParms = new DbParameter[idArr.Length];
            sbSql.AppendFormat("delete from {0} where {1} in (", type.Name, GetIdName(type));
            for (int i = 0; i < idArr.Length; i++)
            {
                cmdParms[i] = GetDbParameter(m_ParameterMark + GetIdName(type) + i, idArr[i]);
                sbSql.AppendFormat("{1}{2}{3},", type.Name, m_ParameterMark, GetIdName(type), i);
            }
            sbSql.Remove(sbSql.Length - 1, 1);
            sbSql.Append(")");

            ExecuteSql(sbSql.ToString(), cmdParms);
        }
        /// <summary>
        /// 根据条件删除
        /// </summary>
        public static void Delete<T>(string conditions)
        {
            if (string.IsNullOrWhiteSpace(conditions)) return;

            Type type = typeof(T);
            StringBuilder sbSql = new StringBuilder();
            SqlFilter(ref conditions);
            sbSql.Append(string.Format("delete from {0} where {1}", type.Name, conditions));

            ExecuteSql(sbSql.ToString());
        }
        #endregion

        #region 获取实体
        #region 根据实体获取实体
        /// <summary>
        /// 根据实体获取实体
        /// </summary>
        private static object Find(object obj, bool readCache = true)
        {
            Type type = obj.GetType();

            object result = Activator.CreateInstance(type);
            bool hasValue = false;
            IDataReader rd = null;

            string sql = string.Format("select * from {0} where {2}='{1}'", type.Name, GetIdVal(obj), GetIdName(obj.GetType()));

            try
            {
                rd = ExecuteReader(sql);

                PropertyInfo[] propertyInfoList = GetEntityProperties(type);

                int fcnt = rd.FieldCount;
                List<string> fileds = new List<string>();
                for (int i = 0; i < fcnt; i++)
                {
                    fileds.Add(rd.GetName(i).ToUpper());
                }

                while (rd.Read())
                {
                    hasValue = true;
                    IDataRecord record = rd;

                    foreach (PropertyInfo pro in propertyInfoList)
                    {
                        if (!fileds.Contains(pro.Name.ToUpper()) || record[pro.Name] == DBNull.Value)
                        {
                            continue;
                        }

                        pro.SetValue(result, record[pro.Name] == DBNull.Value ? null : getReaderValue(record[pro.Name], pro.PropertyType), null);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (rd != null && !rd.IsClosed)
                {
                    rd.Close();
                    rd.Dispose();
                }
            }

            if (hasValue)
            {
                return result;
            }
            else
            {
                return null;
            }
        }
        #endregion

        #region 根据Id获取实体
        /// <summary>
        /// 根据Id获取实体
        /// </summary>
        private static object FindById(Type type, int id)
        {
            object result = Activator.CreateInstance(type);
            IDataReader rd = null;
            bool hasValue = false;

            string sql = string.Format("select * from {0} where {2}='{1}'", type.Name, id, GetIdName(type));

            try
            {
                rd = ExecuteReader(sql);

                PropertyInfo[] propertyInfoList = GetEntityProperties(type);

                int fcnt = rd.FieldCount;
                List<string> fileds = new List<string>();
                for (int i = 0; i < fcnt; i++)
                {
                    fileds.Add(rd.GetName(i).ToUpper());
                }

                while (rd.Read())
                {
                    hasValue = true;
                    IDataRecord record = rd;

                    foreach (PropertyInfo pro in propertyInfoList)
                    {
                        if (!fileds.Contains(pro.Name.ToUpper()) || record[pro.Name] == DBNull.Value)
                        {
                            continue;
                        }

                        pro.SetValue(result, record[pro.Name] == DBNull.Value ? null : getReaderValue(record[pro.Name], pro.PropertyType), null);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (rd != null && !rd.IsClosed)
                {
                    rd.Close();
                    rd.Dispose();
                }
            }

            if (hasValue)
            {
                return result;
            }
            else
            {
                return null;
            }
        }
        #endregion

        #region 根据Id获取实体
        /// <summary>
        /// 根据Id获取实体
        /// </summary>
        public static T FindById<T>(string id) where T : new()
        {
            Type type = typeof(T);
            T result = (T)Activator.CreateInstance(type);
            IDataReader rd = null;
            bool hasValue = false;

            string sql = string.Format("select * from {0} where {2}='{1}'", type.Name, id, GetIdName(type));

            try
            {
                rd = ExecuteReader(sql);

                PropertyInfo[] propertyInfoList = GetEntityProperties(type);

                int fcnt = rd.FieldCount;
                List<string> fileds = new List<string>();
                for (int i = 0; i < fcnt; i++)
                {
                    fileds.Add(rd.GetName(i).ToUpper());
                }

                while (rd.Read())
                {
                    hasValue = true;
                    IDataRecord record = rd;

                    foreach (PropertyInfo pro in propertyInfoList)
                    {
                        if (!fileds.Contains(pro.Name.ToUpper()) || record[pro.Name] == DBNull.Value)
                        {
                            continue;
                        }

                        pro.SetValue(result, record[pro.Name] == DBNull.Value ? null : getReaderValue(record[pro.Name], pro.PropertyType), null);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (rd != null && !rd.IsClosed)
                {
                    rd.Close();
                    rd.Dispose();
                }
            }

            if (hasValue)
            {
                return result;
            }
            else
            {
                return default(T);
            }
        }
        #endregion

        #region 根据sql获取实体
        /// <summary>
        /// 根据sql获取实体
        /// </summary>
        public static T FindBySql<T>(string sql) where T : new()
        {
            Type type = typeof(T);
            T result = (T)Activator.CreateInstance(type);
            IDataReader rd = null;
            bool hasValue = false;

            try
            {
                rd = ExecuteReader(sql);

                PropertyInfo[] propertyInfoList = GetEntityProperties(type);

                int fcnt = rd.FieldCount;
                List<string> fileds = new List<string>();
                for (int i = 0; i < fcnt; i++)
                {
                    fileds.Add(rd.GetName(i).ToUpper());
                }

                while (rd.Read())
                {
                    hasValue = true;
                    IDataRecord record = rd;

                    foreach (PropertyInfo pro in propertyInfoList)
                    {
                        if (!fileds.Contains(pro.Name.ToUpper()) || record[pro.Name] == DBNull.Value)
                        {
                            continue;
                        }

                        pro.SetValue(result, record[pro.Name] == DBNull.Value ? null : getReaderValue(record[pro.Name], pro.PropertyType), null);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (rd != null && !rd.IsClosed)
                {
                    rd.Close();
                    rd.Dispose();
                }
            }

            if (hasValue)
            {
                return result;
            }
            else
            {
                return default(T);
            }
        }
        #endregion
        #endregion

        #region 获取列表
        /// <summary>
        /// 获取列表
        /// </summary>
        public static List<T> FindListBySql<T>(string sql) where T : new()
        {
            List<T> list = new List<T>();
            object obj;
            IDataReader rd = null;

            try
            {
                rd = ExecuteReader(sql);

                if (typeof(T) == typeof(int))
                {
                    while (rd.Read())
                    {
                        list.Add((T)rd[0]);
                    }
                }
                else if (typeof(T) == typeof(string))
                {
                    while (rd.Read())
                    {
                        list.Add((T)rd[0]);
                    }
                }
                else
                {
                    PropertyInfo[] propertyInfoList = (typeof(T)).GetProperties();

                    int fcnt = rd.FieldCount;
                    List<string> fileds = new List<string>();
                    for (int i = 0; i < fcnt; i++)
                    {
                        fileds.Add(rd.GetName(i).ToUpper());
                    }

                    while (rd.Read())
                    {
                        IDataRecord record = rd;
                        obj = new T();


                        foreach (PropertyInfo pro in propertyInfoList)
                        {
                            if (!fileds.Contains(pro.Name.ToUpper()) || record[pro.Name] == DBNull.Value)
                            {
                                continue;
                            }

                            pro.SetValue(obj, record[pro.Name] == DBNull.Value ? null : getReaderValue(record[pro.Name], pro.PropertyType), null);
                        }
                        list.Add((T)obj);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (rd != null && !rd.IsClosed)
                {
                    rd.Close();
                    rd.Dispose();
                }
            }

            return list;
        }
        #endregion

        #region 获取列表
        /// <summary>
        /// 获取列表
        /// </summary>
        public static List<T> FindListBySql<T>(string sql, params DbParameter[] cmdParms) where T : new()
        {
            List<T> list = new List<T>();
            object obj;
            IDataReader rd = null;

            try
            {
                rd = ExecuteReader(sql, cmdParms);

                if (typeof(T) == typeof(int))
                {
                    while (rd.Read())
                    {
                        list.Add((T)rd[0]);
                    }
                }
                else if (typeof(T) == typeof(string))
                {
                    while (rd.Read())
                    {
                        list.Add((T)rd[0]);
                    }
                }
                else
                {
                    PropertyInfo[] propertyInfoList = (typeof(T)).GetProperties();

                    int fcnt = rd.FieldCount;
                    List<string> fileds = new List<string>();
                    for (int i = 0; i < fcnt; i++)
                    {
                        fileds.Add(rd.GetName(i).ToUpper());
                    }

                    while (rd.Read())
                    {
                        IDataRecord record = rd;
                        obj = new T();


                        foreach (PropertyInfo pro in propertyInfoList)
                        {
                            if (!fileds.Contains(pro.Name.ToUpper()) || record[pro.Name] == DBNull.Value)
                            {
                                continue;
                            }

                            pro.SetValue(obj, record[pro.Name] == DBNull.Value ? null : getReaderValue(record[pro.Name], pro.PropertyType), null);
                        }
                        list.Add((T)obj);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (rd != null && !rd.IsClosed)
                {
                    rd.Close();
                    rd.Dispose();
                }
            }

            return list;
        }
        #endregion

        #region 分页获取列表
        /// <summary>
        /// 分页(任意entity,尽量少的字段)
        /// </summary>
        public static PagerModel FindPageBySql<T>(string sql, string orderby, int pageSize, int currentPage) where T : new()
        {
            PagerModel pagerModel = new PagerModel(currentPage, pageSize);

            using (DbConnection connection = GetConnection())
            {
                connection.Open();
                IDbCommand cmd = null;
                StringBuilder sb = new StringBuilder();
                string commandText = null;
                int startRow = 0;
                int endRow = 0;
                switch (m_DBType)
                {
                    case "oracle":
                        #region 分页查询语句
                        commandText = string.Format("select count(*) from ({0}) T", sql);
                        cmd = GetCommand(commandText, connection);
                        pagerModel.totalRows = int.Parse(cmd.ExecuteScalar().ToString());

                        startRow = pageSize * (currentPage - 1);
                        endRow = startRow + pageSize;

                        sb.Append("select * from ( select row_limit.*, rownum rownum_ from (");
                        sb.Append(sql);
                        if (!string.IsNullOrWhiteSpace(orderby))
                        {
                            sb.Append(" ");
                            sb.Append(orderby);
                        }
                        sb.Append(" ) row_limit where rownum <= ");
                        sb.Append(endRow);
                        sb.Append(" ) where rownum_ >");
                        sb.Append(startRow);
                        #endregion
                        break;
                    case "mssql":
                        #region 分页查询语句
                        commandText = string.Format("select count(*) from ({0}) T", sql);
                        cmd = GetCommand(commandText, connection);
                        pagerModel.totalRows = int.Parse(cmd.ExecuteScalar().ToString());

                        startRow = pageSize * (currentPage - 1) + 1;
                        endRow = startRow + pageSize - 1;

                        sb.Append(string.Format(@"
                            select * from 
                            (select ROW_NUMBER() over({1}) as rowNumber, t.* from ({0}) t) tempTable
                            where rowNumber between {2} and {3} ", sql, orderby, startRow, endRow));
                        #endregion
                        break;
                    case "mysql":
                        #region 分页查询语句
                        commandText = string.Format("select count(*) from ({0}) T", sql);
                        cmd = GetCommand(commandText, connection);
                        pagerModel.totalRows = int.Parse(cmd.ExecuteScalar().ToString());

                        startRow = pageSize * (currentPage - 1);

                        sb.Append("select * from (");
                        sb.Append(sql);
                        if (!string.IsNullOrWhiteSpace(orderby))
                        {
                            sb.Append(" ");
                            sb.Append(orderby);
                        }
                        sb.AppendFormat(" ) row_limit limit {0},{1}", startRow, pageSize);
                        #endregion
                        break;
                    case "sqlite":
                        #region 分页查询语句
                        commandText = string.Format("select count(*) from ({0}) T", sql);
                        cmd = GetCommand(commandText, connection);
                        pagerModel.totalRows = int.Parse(cmd.ExecuteScalar().ToString());

                        startRow = pageSize * (currentPage - 1);

                        sb.Append(sql);
                        if (!string.IsNullOrWhiteSpace(orderby))
                        {
                            sb.Append(" ");
                            sb.Append(orderby);
                        }
                        sb.AppendFormat(" limit {0} offset {1}", pageSize, startRow);
                        #endregion
                        break;
                }

                List<T> list = FindListBySql<T>(sb.ToString());
                pagerModel.result = list;
            }

            return pagerModel;
        }
        #endregion

        #region 分页获取列表
        /// <summary>
        /// 分页(任意entity,尽量少的字段)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static PagerModel FindPageBySql<T>(string sql, string orderby, int pageSize, int currentPage, params DbParameter[] cmdParms) where T : new()
        {
            PagerModel pagerModel = new PagerModel(currentPage, pageSize);

            using (DbConnection connection = GetConnection())
            {
                connection.Open();
                IDbCommand cmd = null;
                StringBuilder sb = new StringBuilder();
                string commandText = null;
                int startRow = 0;
                int endRow = 0;
                switch (m_DBType)
                {
                    case "oracle":
                        #region 分页查询语句
                        commandText = string.Format("select count(*) from ({0}) T", sql);
                        cmd = GetCommand(commandText, connection);
                        foreach (DbParameter parm in cmdParms) cmd.Parameters.Add(parm);
                        pagerModel.totalRows = int.Parse(cmd.ExecuteScalar().ToString());

                        startRow = pageSize * (currentPage - 1);
                        endRow = startRow + pageSize;

                        sb.Append("select * from ( select row_limit.*, rownum rownum_ from (");
                        sb.Append(sql);
                        if (!string.IsNullOrWhiteSpace(orderby))
                        {
                            sb.Append(" ");
                            sb.Append(orderby);
                        }
                        sb.Append(" ) row_limit where rownum <= ");
                        sb.Append(endRow);
                        sb.Append(" ) where rownum_ >");
                        sb.Append(startRow);
                        #endregion
                        break;
                    case "mssql":
                        #region 分页查询语句
                        commandText = string.Format("select count(*) from ({0}) T", sql);
                        cmd = GetCommand(commandText, connection);
                        foreach (DbParameter parm in cmdParms) cmd.Parameters.Add(parm);
                        pagerModel.totalRows = int.Parse(cmd.ExecuteScalar().ToString());

                        startRow = pageSize * (currentPage - 1) + 1;
                        endRow = startRow + pageSize - 1;

                        sb.Append(string.Format(@"
                            select * from 
                            (select ROW_NUMBER() over({1}) as rowNumber, t.* from ({0}) t) tempTable
                            where rowNumber between {2} and {3} ", sql, orderby, startRow, endRow));
                        #endregion
                        break;
                    case "mysql":
                        #region 分页查询语句
                        commandText = string.Format("select count(*) from ({0}) T", sql);
                        cmd = GetCommand(commandText, connection);
                        foreach (DbParameter parm in cmdParms) cmd.Parameters.Add(parm);
                        pagerModel.totalRows = int.Parse(cmd.ExecuteScalar().ToString());

                        startRow = pageSize * (currentPage - 1);

                        sb.Append("select * from (");
                        sb.Append(sql);
                        if (!string.IsNullOrWhiteSpace(orderby))
                        {
                            sb.Append(" ");
                            sb.Append(orderby);
                        }
                        sb.AppendFormat(" ) row_limit limit {0},{1}", startRow, pageSize);
                        #endregion
                        break;
                    case "sqlite":
                        #region 分页查询语句
                        commandText = string.Format("select count(*) from ({0}) T", sql);
                        cmd = GetCommand(commandText, connection);
                        foreach (DbParameter parm in cmdParms) cmd.Parameters.Add(parm);
                        pagerModel.totalRows = int.Parse(cmd.ExecuteScalar().ToString());

                        startRow = pageSize * (currentPage - 1);

                        sb.Append(sql);
                        if (!string.IsNullOrWhiteSpace(orderby))
                        {
                            sb.Append(" ");
                            sb.Append(orderby);
                        }
                        sb.AppendFormat(" limit {0} offset {1}", pageSize, startRow);
                        #endregion
                        break;
                }

                List<T> list = FindListBySql<T>(sb.ToString(), cmdParms);
                pagerModel.result = list;
            }

            return pagerModel;
        }


        #endregion

        #region 分页获取列表
        /// <summary>
        /// 分页(任意entity,尽量少的字段)
        /// </summary>
        public static DataSet FindPageBySql(string sql, string orderby, int pageSize, int currentPage, out int totalCount, params DbParameter[] cmdParms)
        {
            DataSet ds = null;

            using (DbConnection connection = GetConnection())
            {
                connection.Open();
                IDbCommand cmd = null;
                StringBuilder sb = new StringBuilder();
                string commandText = null;
                int startRow = 0;
                int endRow = 0;
                totalCount = 0;
                switch (m_DBType)
                {
                    case "oracle":
                        #region 分页查询语句
                        commandText = string.Format("select count(*) from ({0}) T", sql);
                        cmd = GetCommand(commandText, connection);
                        foreach (DbParameter parm in cmdParms) cmd.Parameters.Add(parm);
                        totalCount = int.Parse(cmd.ExecuteScalar().ToString());

                        startRow = pageSize * (currentPage - 1);
                        endRow = startRow + pageSize;

                        sb.Append("select * from ( select row_limit.*, rownum rownum_ from (");
                        sb.Append(sql);
                        if (!string.IsNullOrWhiteSpace(orderby))
                        {
                            sb.Append(" ");
                            sb.Append(orderby);
                        }
                        sb.Append(" ) row_limit where rownum <= ");
                        sb.Append(endRow);
                        sb.Append(" ) where rownum_ >");
                        sb.Append(startRow);
                        #endregion
                        break;
                    case "mssql":
                        #region 分页查询语句
                        commandText = string.Format("select count(*) from ({0}) T", sql);
                        cmd = GetCommand(commandText, connection);
                        foreach (DbParameter parm in cmdParms) cmd.Parameters.Add(parm);
                        totalCount = int.Parse(cmd.ExecuteScalar().ToString());

                        startRow = pageSize * (currentPage - 1) + 1;
                        endRow = startRow + pageSize - 1;

                        sb.Append(string.Format(@"
                            select * from 
                            (select ROW_NUMBER() over({1}) as rowNumber, t.* from ({0}) t) tempTable
                            where rowNumber between {2} and {3} ", sql, orderby, startRow, endRow));
                        #endregion
                        break;
                    case "mysql":
                        #region 分页查询语句
                        commandText = string.Format("select count(*) from ({0}) T", sql);
                        cmd = GetCommand(commandText, connection);
                        foreach (DbParameter parm in cmdParms) cmd.Parameters.Add(parm);
                        totalCount = int.Parse(cmd.ExecuteScalar().ToString());

                        startRow = pageSize * (currentPage - 1);

                        sb.Append("select * from (");
                        sb.Append(sql);
                        if (!string.IsNullOrWhiteSpace(orderby))
                        {
                            sb.Append(" ");
                            sb.Append(orderby);
                        }
                        sb.AppendFormat(" ) row_limit limit {0},{1}", startRow, pageSize);
                        #endregion
                        break;
                    case "sqlite":
                        #region 分页查询语句
                        commandText = string.Format("select count(*) from ({0}) T", sql);
                        cmd = GetCommand(commandText, connection);
                        foreach (DbParameter parm in cmdParms) cmd.Parameters.Add(parm);
                        totalCount = int.Parse(cmd.ExecuteScalar().ToString());

                        startRow = pageSize * (currentPage - 1);

                        sb.Append(sql);
                        if (!string.IsNullOrWhiteSpace(orderby))
                        {
                            sb.Append(" ");
                            sb.Append(orderby);
                        }
                        sb.AppendFormat(" limit {0} offset {1}", pageSize, startRow);
                        #endregion
                        break;
                }

                ds = Query(sql, cmdParms);
            }
            return ds;
        }
        #endregion

        #region getReaderValue 转换数据
        /// <summary>
        /// 转换数据
        /// </summary>
        private static Object getReaderValue(Object rdValue, Type ptype)
        {
            if (ptype == typeof(double))
                return Convert.ToDouble(rdValue);

            if (ptype == typeof(decimal))
                return Convert.ToDecimal(rdValue);

            if (ptype == typeof(int))
                return Convert.ToInt32(rdValue);

            if (ptype == typeof(long))
                return Convert.ToInt64(rdValue);

            if (ptype == typeof(DateTime))
                return Convert.ToDateTime(rdValue);

            if (ptype == typeof(Nullable<double>))
                return Convert.ToDouble(rdValue);

            if (ptype == typeof(Nullable<decimal>))
                return Convert.ToDecimal(rdValue);

            if (ptype == typeof(Nullable<int>))
                return Convert.ToInt32(rdValue);

            if (ptype == typeof(Nullable<long>))
                return Convert.ToInt64(rdValue);

            if (ptype == typeof(Nullable<DateTime>))
                return Convert.ToDateTime(rdValue);

            return rdValue;
        }
        #endregion

        #region 获取主键名称
        /// <summary>
        /// 获取主键名称
        /// </summary>
        public static string GetIdName(Type type)
        {
            PropertyInfo[] propertyInfoList = GetEntityProperties(type);
            foreach (PropertyInfo propertyInfo in propertyInfoList)
            {
                if (propertyInfo.GetCustomAttributes(typeof(IsIdAttribute), false).Length > 0)
                {
                    return propertyInfo.Name;
                }
            }
            return "Id";
        }
        #endregion

        #region 获取主键值
        /// <summary>
        /// 获取主键名称
        /// </summary>
        public static object GetIdVal(object val)
        {
            string idName = GetIdName(val.GetType());
            if (!string.IsNullOrWhiteSpace(idName))
            {
                return val.GetType().GetProperty(idName).GetValue(val, null);
            }
            return 0;
        }
        #endregion

        #region 获取实体类属性
        /// <summary>
        /// 获取实体类属性
        /// </summary>
        private static PropertyInfo[] GetEntityProperties(Type type)
        {
            List<PropertyInfo> result = new List<PropertyInfo>();
            PropertyInfo[] propertyInfoList = type.GetProperties();
            foreach (PropertyInfo propertyInfo in propertyInfoList)
            {
                if (propertyInfo.GetCustomAttributes(typeof(EdmRelationshipNavigationPropertyAttribute), false).Length == 0
                    && propertyInfo.GetCustomAttributes(typeof(BrowsableAttribute), false).Length == 0)
                {
                    result.Add(propertyInfo);
                }
            }
            return result.ToArray();
        }
        #endregion

        #region 获取基类
        /// <summary>
        /// 获取基类
        /// </summary>
        public static Type GetBaseType(Type type)
        {
            while (type.BaseType != null && type.BaseType.Name != typeof(Object).Name)
            {
                type = type.BaseType;
            }
            return type;
        }
        #endregion
        #endregion

        #region 事务
        #region 开始事务
        /// <summary>
        /// 开始事务
        /// </summary>
        public static void BeginTransaction()
        {
            DbConnection conn = GetConnection();
            if (conn.State != ConnectionState.Open) conn.Open();
            m_Tran = conn.BeginTransaction();
        }
        #endregion

        #region 提交事务
        /// <summary>
        /// 提交事务
        /// </summary>
        public static void CommitTransaction()
        {
            if (m_Tran == null) return; //防止重复提交
            DbConnection conn = m_Tran.Connection;
            try
            {
                m_Tran.Commit();
            }
            catch (Exception ex)
            {
                m_Tran.Rollback();
            }
            finally
            {
                if (conn.State == ConnectionState.Open) conn.Close();
                m_Tran.Dispose();
                m_Tran = null;
            }
        }
        #endregion

        #region 回滚事务(出错时调用该方法回滚)
        /// <summary>
        /// 回滚事务(出错时调用该方法回滚)
        /// </summary>
        public static void RollbackTransaction()
        {
            if (m_Tran == null) return; //防止重复回滚
            DbConnection conn = m_Tran.Connection;
            m_Tran.Rollback();
            if (conn.State == ConnectionState.Open) conn.Close();
        }
        #endregion
        #endregion
    }
}

3.2 本机SSH免密码登陆(略)


View Code

3.3 下充斥解压并布置Hadoop路径

  1. 下载Hadoop程序压缩包

  2. 解压至指定目录,例如/usr/local/hadoop2.6.4

  3. 配置/etc/profile ,添加Hadoop目录路径
    export HADOOP_PREFIX=/usr/local/hadoop,并丰富至PATH中
    export PATH=$PATH:$HADOOP_PREFIX


证明:DBHelper中针对工作变量private static
DbTransaction
m_Tran使用了[ThreadStatic]标签,以支撑多用户并发;但是要是是单个用户用多线程并发请求服务器,可能这种办法的数据库事务是休支持之,不过貌似项目没有这种需要,如果来求以HttpContext.Current.Items改写或者其它措施改写。

3.4 Hadoop完全分布模式配置

Web.config配置:

3.4.1 修改${HADOOP_PREFIX}/etc/hadoop/hadoop-env.sh

加上内容 export JAVA_HOME=/usr/java/latest

澳门美高梅手机网站 4澳门美高梅手机网站 5

3.4.2 修改${HADOOP_PREFIX}/etc/hadoop/core-site.xml

<?xml version="1.0" encoding="UTF-8"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>

<configuration>
    <property>
        <name>fs.defaultFS</name>
        <value>hdfs://master:9000</value>
    </property>
    <property>
        <name>hadoop.tmp.dir</name>
        <value>/usr/local/hadoop-2.6.4/tmp</value>
    </property>

</configuration>
<connectionStrings>
  <add name="DefaultConnection"  connectionString="server=localhost;database=netcms3.0;user id=root;password=root;character set=gbk;" />
</connectionStrings>
<appSettings>
  <!--数据库类型-->
  <add key="DBType" value="mysql"/>
  <!--数据库自增-->
  <add key="AutoIncrement" value="false"/>
</appSettings>

3.4.3 修改${HADOOP_PREFIX}/etc/hadoop/hdfs-site.xml

<?xml version="1.0" encoding="UTF-8"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>

<configuration>
    <property>
        <name>dfs.replication</name>
        <value>2</value>
    </property>
    <property>
        <name>dfs.namenode.name.dir</name>
        <value>/usr/local/hadoop-2.6.4/data/namenode</value>
    </property>
    <property>
        <name>dfs.datanode.data.dir</name>
        <value>/usr/local/hadoop-2.6.4/data/datanode</value>
    </property>
</configuration>

View Code

3.4.4 修改${HADOOP_PREFIX}/etc/hadoop/mapred-site.xml

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>

<configuration>
    <property>
        <name>mapreduce.framework.name</name>
        <value>yarn</value>
    </property>
    <property>
        <name>mapreduce.jobhistory.address</name>
        <value>master:10020</value>
    </property>
    <property>
        <name>mapreduce.jobhistory.webapp.address</name>
        <value>master:19888</value>
    </property>
</configuration>

说明:对于SQL Server数据库,通过<add
key=”AutoIncrement” value=”false”/>来安是否动数据库自增。

3.4.5 修改${HADOOP_PREFIX}/etc/hadoop/yarn-site.xml

<?xml version="1.0"?>

<configuration>
    <property>
        <name>yarn.nodemanager.aux-services</name>
        <value>mapreduce_shuffle</value>
    </property>
    <property>
        <description>The hostname of the ResourceManager.</description>
        <name>yarn.resourcemanager.hostname</name>
        <value>master</value>
    </property>
    <property>
        <name>yarn.resourcemanager.address</name>
        <value>${yarn.resourcemanager.hostname}:8032</value>
    </property>
    <property>
        <name>yarn.resourcemanager.scheduler.address</name>
        <value>${yarn.resourcemanager.hostname}:8030</value>
    </property>
    <property>
        <name>yarn.resourcemanager.webapp.address</name>
        <value>${yarn.resourcemanager.hostname}:8088</value>
    </property>
    <property>
        <name>yarn.resourcemanager.webapp.https.address</name>
        <value>${yarn.resourcemanager.hostname}:8090</value>
    </property>
    <property>
        <name>yarn.resourcemanager.resource-tracker.address</name>
        <value>${yarn.resourcemanager.hostname}:8031</value>
    </property>
    <property>
        <name>yarn.resourcemanager.admin.address</name>
        <value>${yarn.resourcemanager.hostname}:8033</value>
    </property>
</configuration>

DBHelper类库需要引用的次序集:

3.4.5 修改${HADOOP_PREFIX}/etc/hadoop/slaves

欠slaves文件要填写DataNode的IP地址,前面早已配备/etc/hosts
因此填写hostname即可

slaver01
slaver02

澳门美高梅手机网站 6

3.5 拷贝多片客master系统镜像

改拷贝出之系hostname分别吗 slaver01 和 slaver02


除VS2012打带的DLL外需要的DLL:

3.6 启动

MySql.Data.dll

3.6.1 首先格式化NameNode

执行命令 hdfs namenode -format

System.Data.SQLite.dll

3.6.2 启动所有服务

上目录${HADOOP_PREFIX}/sbin
执行命令 ./start-all.sh

特别注意,防火墙允许相应端口或者关闭防火墙

浏览器键入 http://192.168.1.113:50070/
可查看Hadoop运行信息,若NameNode启动成功与DataNode也设有列表中验证配置成功。

澳门美高梅手机网站 7

其间Models类库如下:

澳门美高梅手机网站 8

证实:Models目录中之好像及其特性与数据库被的阐发和字段是截然对应之,Models全部是因为生成器生成,并且不允许手动修改。ExtModels目录中的接近是扩张类,主要用于查询及展示,比如表中存的凡code,但若得关联查询任何一样摆放表中的name,就可在这个扩展类中扩张一个用以展示的name字段。Models和ExtModels目录的中类都是partial修饰。

PagerModel类:

澳门美高梅手机网站 9澳门美高梅手机网站 10

using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace Models
{
    /// <summary>
    /// 分页
    /// </summary>
    public class PagerModel
    {
        #region 字段
        /// <summary>
        /// 当前页数
        /// </summary>
        public int page { get; set; }
        /// <summary>
        /// 每页记录数
        /// </summary>
        public int rows { get; set; }
        /// <summary>
        /// 排序字段
        /// </summary>
        public string sort { get; set; }
        /// <summary>
        /// 排序的方式asc,desc
        /// </summary>
        public string order { get; set; }
        /// <summary>
        /// 记录
        /// </summary>
        public object result { get; set; }
        /// <summary>
        /// 记录数
        /// </summary>
        public int totalRows { get; set; }
        #endregion

        #region 构造函数
        public PagerModel()
        {

        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="page">当前页数</param>
        /// <param name="rows">每页记录数</param>
        public PagerModel(int page, int rows)
        {
            this.page = page;
            this.rows = rows;
        }
        #endregion

        #region 扩展字段
        /// <summary>
        /// 总页数
        /// </summary>
        public int pageCount
        {
            get
            {
                return (totalRows - 1) / rows + 1;
            }
        }
        /// <summary>
        /// 上一页
        /// </summary>
        public int prePage
        {
            get
            {
                if (page - 1 > 0)
                {
                    return page - 1;
                }
                return 1;
            }
        }
        /// <summary>
        /// 下一页
        /// </summary>
        public int nextPage
        {
            get
            {
                if (page + 1 < pageCount)
                {
                    return page + 1;
                }
                return pageCount;
            }
        }
        #endregion

    }
}

View Code

IsIdAttribute类:

澳门美高梅手机网站 11澳门美高梅手机网站 12

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Models
{
    /// <summary>
    /// 标识该属性是主健
    /// </summary>
    [Serializable, AttributeUsage(AttributeTargets.Property | AttributeTargets.Class)]
    public class IsIdAttribute : Attribute
    {
    }
}

View Code

IsDBFieldAttribute类:

澳门美高梅手机网站 13澳门美高梅手机网站 14

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Models
{
    /// <summary>
    /// 标识该属性是数据库字段
    /// </summary>
    [Serializable, AttributeUsage(AttributeTargets.Property | AttributeTargets.Class)]
    public class IsDBFieldAttribute : Attribute
    {
    }
}

View Code

Models示例:

澳门美高梅手机网站 15澳门美高梅手机网站 16

using System;
using System.Collections.Generic;
using System.Linq;

namespace Models
{
    /// <summary>
    /// 内容详情
    /// </summary>
    [Serializable]
    public partial class cms_content
    {
        /// <summary>
        /// 编号
        /// </summary>
        [IsId]
        [IsDBField]
        public int id { get; set; }
        /// <summary>
        /// 所属栏目ID
        /// </summary>
        [IsDBField]
        public int? channelId { get; set; }
        /// <summary>
        /// 标题
        /// </summary>
        [IsDBField]
        public string title { get; set; }
        /// <summary>
        /// 内容
        /// </summary>
        [IsDBField]
        public string contents { get; set; }
        /// <summary>
        /// 作者
        /// </summary>
        [IsDBField]
        public string author { get; set; }
        /// <summary>
        /// 阅读次数
        /// </summary>
        [IsDBField]
        public int? readCount { get; set; }
        /// <summary>
        /// 发布时间
        /// </summary>
        [IsDBField]
        public DateTime? publishTime { get; set; }
        /// <summary>
        /// 发布者
        /// </summary>
        [IsDBField]
        public int? publishUserId { get; set; }
        /// <summary>
        /// 审核(0待审1通过2不通过)
        /// </summary>
        [IsDBField]
        public int? audit { get; set; }
        /// <summary>
        /// 审核人
        /// </summary>
        [IsDBField]
        public int? auditUserId { get; set; }
        /// <summary>
        /// 审核时间
        /// </summary>
        [IsDBField]
        public DateTime? auditTime { get; set; }
        /// <summary>
        /// 页面关键词
        /// </summary>
        [IsDBField]
        public string keywords { get; set; }
        /// <summary>
        /// 页面描述
        /// </summary>
        [IsDBField]
        public string description { get; set; }
        /// <summary>
        /// 页面链接
        /// </summary>
        [IsDBField]
        public string pageUrl { get; set; }
        /// <summary>
        /// 内容封面
        /// </summary>
        [IsDBField]
        public string imgUrl { get; set; }
        /// <summary>
        /// 是否链接(0否1是)
        /// </summary>
        [IsDBField]
        public int? isPageUrl { get; set; }
        /// <summary>
        /// 模板(模板文件名,例:content.html)
        /// </summary>
        [IsDBField]
        public string template { get; set; }
        /// <summary>
        /// 推荐(1推荐0不推荐)
        /// </summary>
        [IsDBField]
        public int? recommend { get; set; }
    }
}

View Code

ExtModels示例:

澳门美高梅手机网站 17澳门美高梅手机网站 18

using System;
using System.Collections.Generic;
using System.Linq;

namespace Models
{
    /// <summary>
    /// 内容详情
    /// </summary>
    public partial class cms_content
    {
        /// <summary>
        /// 栏目名称
        /// </summary>
        public string channelName { get; set; }
        /// <summary>
        /// 用户显示名
        /// </summary>
        public string showName { get; set; }
        /// <summary>
        /// 审核状态
        /// </summary>
        public string dispAudit
        {
            get
            {
                switch (this.audit ?? 0)
                {
                    case 0:
                        return "待审核";
                    case 1:
                        return "审核通过";
                    case 2:
                        return "审核不通过";
                }
                return "error";
            }
            set { }
        }
        /// <summary>
        /// 在当前页中的索引
        /// </summary>
        public int curPageSort { get; set; }
        /// <summary>
        /// 是否为空,模板使用,0不为空1为空
        /// </summary>
        public int isNull { get; set; }
    }
}

View Code

怎利用:

证实:支持参数化的增删改查,推荐用参数化的增删改查;非参数化的增删改查过滤了有的数据库重点字为戒SQL注入,但恐怕照样未安全。下面例子中的增长、修改、根据ID删除、根据ID集合批量删减都是参数化的,示例中的查询及其他方法的删减不是参数化的,DBHelper提供了相关的参数化查询与实施SQL。

添加:

澳门美高梅手机网站 19澳门美高梅手机网站 20

/// <summary>
/// 添加
/// </summary>
public void Insert(object obj)
{
    DBHelper.Insert(obj);
}

View Code

说明:SQL
Server数据库可用自增,Oracle数据库可用Sequence,小系统可以以DBHelper自带的GetMaxID方法。

博最充分ID(当然,ID一般以自增,对于并发量极少的系统,或单机系统,为了方便,可以如此做):

澳门美高梅手机网站 21澳门美高梅手机网站 22

/// <summary>
/// GetMaxId
/// </summary>
public int GetMaxId()
{
    return DBHelper.GetMaxID<BS_Template>("id");
}

View Code

修改:

澳门美高梅手机网站 23澳门美高梅手机网站 24

/// <summary>
/// 修改
/// </summary>
public void Update(object obj)
{
    DBHelper.Update(obj);
}

View Code

删除:

根据ID删除:

澳门美高梅手机网站 25澳门美高梅手机网站 26

/// <summary>
/// 删除
/// </summary>
public void Del(int id)
{
    DBHelper.Delete<BS_Template>(id);
}

View Code

因ID集合批量剔除:

澳门美高梅手机网站 27澳门美高梅手机网站 28

/// <summary>
/// 删除
/// </summary>
public void BatchDelete(string ids)
{
    DBHelper.BatchDelete<BS_Template>(ids);
}

View Code

冲规则去:

澳门美高梅手机网站 29澳门美高梅手机网站 30

/// <summary>
/// 删除
/// </summary>
public void Delete(string conditions)
{
    DBHelper.Delete<BS_Template>(conditions);
}

View Code

任何方式的去请用:

public static int ExecuteSql(string sqlString)

根据ID查询实体:

澳门美高梅手机网站 31澳门美高梅手机网站 32

public cms_content Get(int id)
{
    return DBHelper.FindById<cms_content>(id.ToString());
}

View Code

查询:

澳门美高梅手机网站 33澳门美高梅手机网站 34

public List<cms_content> GetListAll()
{
    StringBuilder sql = new StringBuilder(string.Format(@"
        select content.*, channel.title as channelName, user.showName
        from cms_content content
        left join cms_channel channel on channel.id=content.channelId
        left join CMS_sys_user user on user.id=content.publishUserId
        where content.audit=1 
        order by publishTime desc,id desc"));

    return DBHelper.FindListBySql<cms_content>(sql.ToString());
}

View Code

证实:ExtModels下的扩张Model,可以支撑查询数据库中无存的字段,并映射填充到实体类。

分页查询:

澳门美高梅手机网站 35澳门美高梅手机网站 36

public List<cms_content> GetList(ref PagerModel pager, int channelId, string title, int audit)
{
    StringBuilder sql = new StringBuilder(string.Format(@"
        select content.*, channel.title as channelName, user.showName
        from cms_content content
        left join cms_channel channel on channel.id=content.channelId
        left join CMS_sys_user user on user.id=content.publishUserId
        where 1=1 "));

    if (channelId != -1)
    {
        sql.AppendFormat(" and content.channelId = {0}", channelId);
    }
    if (!string.IsNullOrWhiteSpace(title))
    {
        sql.AppendFormat(" and content.title like '%{0}%'", title);
    }
    if (audit != -1)
    {
        sql.AppendFormat(" and content.audit = {0}", audit);
    }

    string orderby = string.Format("order by content.publishTime desc,id desc");
    pager = DBHelper.FindPageBySql<cms_content>(sql.ToString(), orderby, pager.rows, pager.page);
    return pager.result as List<cms_content>;
}

View Code

数据库事务澳门美高梅手机网站:

澳门美高梅手机网站 37澳门美高梅手机网站 38

try
{
    DBHelper.BeginTransaction(); //开启数据库事务

    //在这里写增删改操作

    DBHelper.CommitTransaction(); //提交数据库事务
}catch(Exception ex)
{
    DBHelper.RollbackTransaction(); //回滚数据库事务
}

View Code

DAL层增删改查示例:

澳门美高梅手机网站 39澳门美高梅手机网站 40

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using DBUtil;
using Models;

namespace DAL
{
    /// <summary>
    /// 内容详情管理
    /// </summary>
    public class ContentDal
    {
        #region 获取列表
        /// <summary>
        /// 获取列表
        /// </summary>
        public List<cms_content> GetList(ref PagerModel pager, int channelId, string title, int audit)
        {
            StringBuilder sql = new StringBuilder(string.Format(@"
                select content.*, channel.title as channelName, user.showName
                from cms_content content
                left join cms_channel channel on channel.id=content.channelId
                left join CMS_sys_user user on user.id=content.publishUserId
                where 1=1 "));

            if (channelId != -1)
            {
                sql.AppendFormat(" and content.channelId = {0}", channelId);
            }
            if (!string.IsNullOrWhiteSpace(title))
            {
                sql.AppendFormat(" and content.title like '%{0}%'", title);
            }
            if (audit != -1)
            {
                sql.AppendFormat(" and content.audit = {0}", audit);
            }

            string orderby = string.Format("order by content.publishTime desc,id desc");
            pager = DBHelper.FindPageBySql<cms_content>(sql.ToString(), orderby, pager.rows, pager.page);
            return pager.result as List<cms_content>;
        }
        #endregion

        #region 获取列表
        /// <summary>
        /// 获取列表
        /// </summary>
        /// <param name="pager">分页</param>
        /// <param name="channel">栏目,可以是栏目ID或栏目名称</param>
        /// <param name="where">where语句</param>
        public List<cms_content> GetList(ref PagerModel pager, string channel, string where)
        {
            StringBuilder sql = new StringBuilder(string.Format(@"
                select content.*, channel.title as channelName, user.showName
                from cms_content content
                left join cms_channel channel on channel.id=content.channelId
                left join CMS_sys_user user on user.id=content.publishUserId
                where content.audit=1 "));

            if (!string.IsNullOrWhiteSpace(where))
            {
                sql.AppendFormat(" and {0}", where);
            }

            if (!string.IsNullOrWhiteSpace(channel))
            {
                ChannelDal channelDal = new ChannelDal();
                if (Regex.IsMatch(channel, @"^\d+$")) //数字,即栏目ID
                {
                    string channelIds = channelDal.GetChildIds(Convert.ToInt32(channel));
                    sql.AppendFormat(" and channelId in ({0})", channelIds);
                }
                else //非数字,即栏目名称
                {
                    string channelIds = channelDal.GetChildIds(channel);
                    sql.AppendFormat(" and channelId in ({0})", channelIds);
                }
            }

            string orderby = string.Format("order by publishTime desc,id desc");
            if (pager.rows > 0)
            {
                PagerModel pagerModel = DBHelper.FindPageBySql<cms_content>(sql.ToString(), orderby, pager.rows, pager.page);
                pager.totalRows = pagerModel.totalRows;
                pager.result = pagerModel.result;
                List<cms_content> list = pagerModel.result as List<cms_content>;
                int i = 1;
                list.ForEach(a =>
                {
                    a.curPageSort = i++;
                });
                return pagerModel.result as List<cms_content>;
            }
            else
            {
                return DBHelper.FindListBySql<cms_content>(sql.ToString() + orderby);
            }
        }
        #endregion

        #region 获取列表
        /// <summary>
        /// 获取列表
        /// </summary>
        public List<cms_content> GetListAll()
        {
            StringBuilder sql = new StringBuilder(string.Format(@"
                select content.*, channel.title as channelName, user.showName
                from cms_content content
                left join cms_channel channel on channel.id=content.channelId
                left join CMS_sys_user user on user.id=content.publishUserId
                where content.audit=1 
                order by publishTime desc,id desc"));

            return DBHelper.FindListBySql<cms_content>(sql.ToString());
        }
        #endregion

        #region 获取总数
        /// <summary>
        /// 获取总数
        /// </summary>
        public int GetAllCount()
        {
            StringBuilder sql = new StringBuilder(string.Format(@"
                select count(*)
                from cms_content content
                where content.audit=1"));

            return Convert.ToInt32(DBHelper.GetSingle(sql.ToString()));
        }
        #endregion

        #region 获取
        /// <summary>
        /// 获取
        /// </summary>
        public cms_content Get(int id)
        {
            return DBHelper.FindById<cms_content>(id.ToString());
        }
        /// <summary>
        /// 根据channelId获取一条内容详情
        /// </summary>
        public cms_content GetByChannelId(int channelId)
        {
            return DBHelper.FindBySql<cms_content>(string.Format("select * from cms_content where channelId={0} and audit=1", channelId));
        }
        /// <summary>
        /// 获取
        /// </summary>
        public cms_content GetNext(int id)
        {
            cms_content current = Get(id);
            return DBHelper.FindBySql<cms_content>(string.Format("select * from cms_content where id<{0} and channelId={1} and audit=1 order by id desc limit 0,1", current.id, current.channelId));
        }
        /// <summary>
        /// 获取
        /// </summary>
        public cms_content GetPre(int id)
        {
            cms_content current = Get(id);
            return DBHelper.FindBySql<cms_content>(string.Format("select * from cms_content where id>{0} and channelId={1} and audit=1 order by id asc limit 0,1", current.id, current.channelId));
        }
        #endregion

        #region 添加
        /// <summary>
        /// 添加
        /// </summary>
        public void Insert(cms_content model)
        {
            model.id = DBHelper.GetMaxID<cms_content>("id");
            DBHelper.Insert(model);
        }
        #endregion

        #region 修改
        /// <summary>
        /// 修改
        /// </summary>
        public void Update(cms_content model)
        {
            DBHelper.Update(model);
        }
        #endregion

        #region 删除
        /// <summary>
        /// 删除
        /// </summary>
        public void Del(string ids)
        {
            DBHelper.BatchDelete<cms_content>(ids);
        }
        #endregion

    }
}

View Code

事例代码下载:http://files.cnblogs.com/files/s0611163/DBHelperDemo.zip

说明:例子程序是Winform,该DBHelper也适用于Web项目。

配套Model生成器下载:http://files.cnblogs.com/files/s0611163/Model%E7%94%9F%E6%88%90%E5%99%A8.zip

倘你不清楚自己干什么而描绘DBHelper,为什么没有语法糖,为什么查询和分页查询而使原生SQL,看看下面我们ERP项目蒙之代码,你晤面了解,当然,这个路设计之真的不好:

1、

澳门美高梅手机网站 41澳门美高梅手机网站 42

/// <summary>
/// 统计SQL
/// </summary>
public override string GetReportSql(string formData)
{
    ProductInvoiceModel search = JsonConvert.DeserializeObject<ProductInvoiceModel>(formData);

    StringBuilder sql = new StringBuilder(string.Format(@"
        select * from 
        (select distinct 
        '出库单' as '报表类型',
        pos.billNo as '单据编号',
        pos.projectCode as '项目编号',
        pro.projectName as '项目名称',
        ma.subProject as '项目子项',

        case when cs.conMemberCode is not NULL then cs.conMemberCode else mat.materialCode end as '构件编码',
        case when cmd.memberName is not NULL then cmd.memberName else mat.materialName end as '材料名称',
        case when cs.conMemberCode is not NULL then '构件' else '物料' end as '类型',

        mad.model as '规格型号',
        dic1.dicItem as '单位',
        posd.qty as '数量',

        CONVERT(varchar(100), pos.billDate, 23) as '日期'

        from Pro_ProductOutStorageDet posd
        left join Pro_ProductOutStorage pos on pos.id=posd.parentId

        left join Pro_MatAllotDet mad on posd.matAllotDetId=mad.id
        left join Pro_MatAllot ma on ma.id=mad.parentID and ma.status=2
        left join Pro_MatStock ms on ms.id=mad.matStockId and mad.memberType=1
        left join Pro_ConStock cs on cs.id=mad.matStockId and mad.memberType=0
        left join sys_material mat on mat.materialCode=ms.materailCode

        left join Pro_ProductInstorage pi on pi.billNo=cs.billNo
        left join Pro_ProductInstorageDet pid on pid.parentId=pi.id

        left join Pro_ConMember cm on cm.billCode=pi.conMemberBillNo
        left join Pro_ConMemberDet cmd on cmd.id=pid.conMemberDetId

        left join Pro_Info pro on pos.projectCode=pro.projectNum

        left join sys_dicDetail dic1 on dic1.dicItemcode=mad.unitCode  

        union all

        select distinct 
        'XXX出库单' as '报表类型',
        pos.billNo as '单据编号',
        pos.projectCode as '项目编号',
        pro.projectName as '项目名称',
        '' as '项目子项',

        case when cs.conMemberCode is not NULL then cs.conMemberCode else mat.materialCode end as '构件编码',
        case when cmd.memberName is not NULL then cmd.memberName else mat.materialName end as '材料名称',
        case when cs.conMemberCode is not NULL then '构件' else '物料' end as '类型',

        case when cmd.model is not NULL then cmd.model else mat.model end as '规格型号',
        case when dic1.dicItem  is not NULL then dic1.dicItem  else dic2.dicItem  end as '单位',
        posd.qty as '数量',

        CONVERT(varchar(100), pos.billDate, 23) as '日期'

        from Pro_ProductOut2StorageDet posd
        left join Pro_ProductOut2Storage pos on pos.id=posd.parentId

        left join Pro_MatStock ms on ms.id=posd.matStockId
        left join Pro_ConStock cs on cs.id=posd.conInventoryDetId
        left join sys_material mat on mat.materialCode=ms.materailCode

        left join Pro_ProductInstorage pi on pi.billNo=cs.billNo
        left join Pro_ProductInstorageDet pid on pid.parentId=pi.id

        left join Pro_ConMember cm on cm.billCode=pi.conMemberBillNo
        left join Pro_ConMemberDet cmd on cmd.id=pid.conMemberDetId

        left join Pro_Info pro on pos.projectCode=pro.projectNum

        left join sys_dicDetail dic1 on dic1.dicItemcode=mat.unitCode  
        left join sys_dicDetail dic2 on dic2.dicItemcode=cmd.qtyUnit  

        union all

        select distinct 
        '生产消耗单' as '报表类型',
        pc.billCode as '单据编号',
        pro.projectNum as '项目编号',
        pro.projectName as '项目名称',
        ll.subProject as '项目子项',

        case when cs.conMemberCode is not NULL then cs.conMemberCode else mat.materialCode end as '构件编码',
        case when cmd.memberName is not NULL then cmd.memberName else mat.materialName end as '材料名称',
        case when cs.conMemberCode is not NULL then '构件' else '物料' end as '类型',

        case when cmd.model is not NULL then cmd.model else mat.model end as '规格型号',
        case when dic1.dicItem  is not NULL then dic1.dicItem  else dic2.dicItem  end as '单位',
        pcd.consumeQty as '数量',

        CONVERT(varchar(100), pc.billDate, 23) as '日期'

        from Pro_ProductConsume pc
        left join Pro_ProductConsumeDet pcd on pcd.parentId=pc.id

        left join Pro_LingLiaoDet lld on lld.id=pcd.lingLiaoDetId
        left join Pro_LingLiao ll on ll.id=lld.parentId

        left join Pro_MatStock ms on ms.id=lld.matStockId and lld.memberType=1
        left join Pro_ConStock cs on cs.id=lld.matStockId and lld.memberType=0
        left join sys_material mat on mat.materialCode=ms.materailCode

        left join Pro_ProductInstorage pi on pi.billNo=cs.billNo
        left join Pro_ProductInstorageDet pid on pid.parentId=pi.id

        left join Pro_ConMember cm on cm.billCode=pi.conMemberBillNo
        left join Pro_ConMemberDet cmd on cmd.id=pid.conMemberDetId

        left join Pro_ProTask pt on pt.billCode=ll.proTaskCode
        left join Pro_Info pro on pt.proInfCode=pro.billCode

        left join sys_dicDetail dic1 on dic1.dicItemcode=mat.unitCode  
        left join sys_dicDetail dic2 on dic2.dicItemcode=cmd.qtyUnit  
        ) T where 1=1 "));

    if (!string.IsNullOrEmpty(search.projectName))
    {
        sql.AppendFormat("  and T.项目名称 like '%{0}%' ", search.projectName.Trim());
    }
    if (!string.IsNullOrEmpty(search.projectCode))
    {
        sql.AppendFormat("  and T.项目编号 like '%{0}%' ", search.projectCode.Trim());
    }
    if (!string.IsNullOrEmpty(search.btime) && !string.IsNullOrEmpty(search.etime))
    {
        sql.AppendFormat(" and  T.日期  >=  '{0}'", search.btime);
    }
    if (!string.IsNullOrEmpty(search.etime))
    {
        sql.AppendFormat(" and  T.日期  <=  '{0}'", search.etime);
    }

    return sql.ToString();
}

View Code

说明:查询SQL书写规范:SQL不克写的滥,该换行换行,该对联合对联合。

2、

澳门美高梅手机网站 43澳门美高梅手机网站 44

        public List<MatAllotModel> GeProMatAllotList(ref PagerModel pager, string billCode, string projectNum, string projectName, string remarks, int status)
        {
            string sql = @"select v.*,ISNULL(v.IsFinish,(case when ISNULL(v.pqty,0)= ISNULL(v.mqty,0) then 1 else 0 end)) as IsEnd from (
select ma.*,e.name + '('+isnull(e.mobilephone,'')+')' as billCreatorName,i.projectName,s.storeHouseName as inStoreName, 
(select stuff((select distinct ','+s.storeHouseName from Pro_MatAllotDet mad
join sys_storehouse s on mad.outStorageCode=s.storeHouseCode
where parentId =ma.id for xml path('')),1,1,'')) as outStoreName,I.projectSim,D.dicItem AS typename,ppi.id as InvoiceId,i.projectNum,
(select sum(md.qty) from Pro_MatAllotDet md  where md.parentId =ma.id) as mqty,
(select sum(qty) from Pro_ProductOutStorageDet pod 
left join Pro_ProductOutStorage po on po.id = pod.parentID
where po.matAllotBillCode = ma.billCode) as pqty
from Pro_MatAllot ma
join sys_storehouse s on ma.inStoreCode=s.storeHouseCode
join sys_employee e on ma.billCreator=e.employeeCode
left join Pro_ProductInvoice ppi on ppi.MatAllotBillCode=ma.billCode
left join Pro_Info i on ma.projectCode = i.projectNum
LEFT JOIN sys_dicDetail D ON D.dicItemcode = I.projectType
) as v where 1=1 ";
            if (!string.IsNullOrEmpty(billCode))
            {
                sql += " and v.billCode like '%" + billCode.Trim() + "%'";
            }
            if (!string.IsNullOrEmpty(projectName))
            {
                sql += " and v.projectName like '%" + projectName.Trim() + "%'";
            }
            if (!string.IsNullOrEmpty(projectNum))
            {
                sql += " and v.projectNum like '%" + projectNum.Trim() + "%'";
            }
            if (!string.IsNullOrEmpty(remarks))
            {
                sql += " and v.remarks like '%" + remarks.Trim() + "%'";
            }
            sql += " and v.status=" + status + "";
            List<MatAllotModel> matAllot = EntityHelper.Default.GetPageEntities<MatAllotModel>(pager.page, pager.rows, sql, "id", pager.sort, pager.order);
            pager.totalRows = DBHelper.Default.Count(sql);
            pager.result = matAllot;
            return matAllot;
        }

View Code

3、

澳门美高梅手机网站 45澳门美高梅手机网站 46

public static List<ConStockDialogMod> GetMaterialData(ref PagerModel pager, string memberName, string storeCode, string parentId, string store)
{
    StringBuilder where = new StringBuilder(" and 1=1 ");
    if (!string.IsNullOrEmpty(memberName))              //当物料为空时,查询出所有的结果集,否则查询出此种相似物料的结果集
        where.AppendFormat("  and t3.memberName like '%{0}%' ", memberName.Trim());
    if (!string.IsNullOrEmpty(storeCode))              //当物料为空时,查询出所有的结果集,否则查询出此种相似物料的结果集
        where.AppendFormat("  and SSH.storeHouseCode='{0}' ", storeCode);
    if (!string.IsNullOrEmpty(parentId))              //当物料为空时,查询出所有的结果集,否则查询出此种相似物料的结果集
        where.AppendFormat("  and t3.parentId = {0} ", parentId);
    if (!string.IsNullOrEmpty(store))
        where.AppendFormat("  and SSH.storeHouseName like '%{0}%'  ", store.Trim());
    string sql = @"SELECT PM.id,PM.conMemberCode as conMemberCode,PM.price,(PM.qty-PM.lockQty-PM.usedQty) AS stockNum,
        PM.instoreTime,PM.billNo,PM.storeCode,PM.lockQty,PM.usedQty,SSH.storeHouseName AS storeName,
        t3.allWeight,t3.qtyUnit,t3.weight as weights,t3.memberName,t3.model,t3.qulity, 
        d1.dicItem as allWeightUnitName,d2.dicItem as qtyUnitName,
        d3.dicItem as qulityName,d4.dicItem as weightUnitName,
        (select SUM(pt.qty) from Pro_ProductOutstorageDet pt where pt.conInventoryDetId=PM.id)as outQty  
        from Pro_ConStock PM
        join Pro_ConPurInstore cpd on PM.billNo=cpd.billCode
        join Pro_ConPurInstoreDet cp on cpd.id=cp.parentId and PM.billId=cp.id
        join dbo.Pro_ConGoodsDet t on t.id=cp.conGoodsDetId
        join dbo.Pro_ConPurchaseContractDet t1 on t1.id=t.conContractDetId
        join dbo.Pro_ConPurchasePlanDet t2 on t2.id=t1.conPlanDetID
        join dbo.Pro_ConMemberDet t3 on t3.id=t2.conMemberDetId
        left join sys_storehouse SSH on SSH.storeHouseCode=PM.storeCode
        left join sys_dicDetail d1 on d1.dicItemcode=t3.allWeightUnit
        left join sys_dicDetail d2 on d2.dicItemcode=t3.qtyUnit
        left join sys_dicDetail d3 on d3.dicItemcode=t3.qulity
        left join sys_dicDetail d4 on d4.dicItemcode=t3.weightUnit 
        WHERE  PM.qty-PM.lockQty-PM.usedQty > 0  " + where +
    @" union 
        SELECT PM.id,PM.conMemberCode as conMemberCode,PM.price,(PM.qty-PM.lockQty-PM.usedQty) AS stockNum,
        PM.instoreTime,PM.billNo,PM.storeCode,PM.lockQty,PM.usedQty,
        SSH.storeHouseName AS storeName,
        t3.allWeight,t3.qtyUnit,t3.weight as weights,t3.memberName,t3.model,t3.qulity, 
        d1.dicItem as allWeightUnitName,d2.dicItem as qtyUnitName,
        d3.dicItem as qulityName,d4.dicItem as weightUnitName,
        (select SUM(pt.qty) from Pro_ProductOutstorageDet pt where pt.conInventoryDetId=PM.id)as outQty  
        from Pro_ConStock PM
        join Pro_ProductInstorage cpd on PM.billNo=cpd.billNo
        join Pro_ProductInstorageDet cp on cpd.id=cp.parentId and PM.billId=cp.id
        join dbo.Pro_ConMemberDet t3 on t3.id=cp.conMemberDetId
        left join sys_storehouse SSH on SSH.storeHouseCode=PM.storeCode
        left join sys_dicDetail d1 on d1.dicItemcode=t3.allWeightUnit
        left join sys_dicDetail d2 on d2.dicItemcode=t3.qtyUnit
        left join sys_dicDetail d3 on d3.dicItemcode=t3.qulity
        left join sys_dicDetail d4 on d4.dicItemcode=t3.weightUnit  
        WHERE  PM.qty-PM.lockQty-PM.usedQty > 0 " + where;


    List<ConStockDialogMod> model = EntityHelper.Default.GetPageEntities<ConStockDialogMod>(pager.page, pager.rows, sql.ToString(), "id", pager.sort, pager.order);
    pager.totalRows = DBHelper.Default.Count(sql.ToString());
    pager.result = model;
    return model;

}

View Code

4、

澳门美高梅手机网站 47澳门美高梅手机网站 48

        /// <summary>
        /// Sql
        /// </summary>
        /// <param name="formData"></param>
        /// <returns></returns>
        public override string GetReportSql(string formData)
        {
            TaskAllocationModel search = JsonConvert.DeserializeObject<TaskAllocationModel>(formData);
            StringBuilder where = new StringBuilder(" where 1=1 ");
            if (!string.IsNullOrEmpty(search.projectCode))
            {
                where.AppendFormat("  and ta.projectCode like '%{0}%' ", search.projectCode.Trim());
            }
            if (!string.IsNullOrEmpty(search.projectName))
            {
                where.AppendFormat("  and pro.projectName like '%{0}%' ", search.projectName.Trim());
            }
            string sql = @"select ta.billCode as '单号',pro.projectName as '项目名称',pro.projectNum as '项目编号',ta.desingBillCode as '项目设计任务单',
 CASE ta.violent WHEN '1' THEN '是' ELSE '否' END as '是否加急',
 CASE ta.taskType WHEN '0' THEN '载荷图' WHEN '1' THEN '方案图' WHEN '2' THEN '工程量统计' ELSE '载荷图与工程量统计' END as '任务类型',
ta.desingContent as '设计内容',ta.auditorScore as '审核人打分',
(select e.RealName
from Wf2Operate oper 
left join Wf2Node n on n.id = oper.Wf2NoteID 
left join employee e on e.employeecode = oper.Operator 
where Wf2InstanceID = ta.LastWf2InstanceID and n.Name='设计' and oper.OperationID != -3) as '设计者',
(select e.RealName
from Wf2Operate oper 
left join Wf2Node n on n.id = oper.Wf2NoteID 
left join employee e on e.employeecode = oper.Operator 
where Wf2InstanceID = ta.LastWf2InstanceID and n.Name='校对' and oper.OperationID != -3) as '校对者',
(select e.RealName
from Wf2Operate oper 
left join Wf2Node n on n.id = oper.Wf2NoteID 
left join employee e on e.employeecode = oper.Operator 
where Wf2InstanceID = ta.LastWf2InstanceID and n.Name='审核' and oper.OperationID != -3) as '审核者',
(select e.RealName
from Wf2Operate oper 
left join Wf2Node n on n.id = oper.Wf2NoteID 
left join employee e on e.employeecode = oper.Operator 
where Wf2InstanceID = ta.LastWf2InstanceID and n.Name='审定' and oper.OperationID != -3) as '审定者',
e.name as '创建人',ta.creatTime as '创建时间'
from Pro_TaskAllocation ta
left join Pro_Info pro on ta.projectCode=pro.projectNum
left join sys_employee e on ta.creator=e.employeeCode 
                 " + where;

            return sql.ToString();
        }

View Code

5、

澳门美高梅手机网站 49澳门美高梅手机网站 50

            string sql = string.Format(@"select t.*,t.haveQty as weichukuNum,(t.qty - t.chukuNum) as shenyuNum  from (SELECT o.*,(
 case memberType when 1 then (SELECT (qty- lockQty- usedQty) 
 FROM Pro_MatStock s where s.id=o.matStockId )
 when 0 then ((SELECT (qty- lockQty- usedQty) 
 FROM Pro_ConStock s where s.id=o.matStockId )) else 0 end) as haveQty,
 (case memberType when 1 then (SELECT materialName from sys_material where materialCode = o.code )
 when 0 then (select DISTINCT memberName from Pro_ConMemberDet v LEFT join Pro_ConMember g on g.id=v.parentId where v.memberCode=o.code and g.billCode=ma.conMemberBillNo) else '' end) as materialName
 ,sh.storeHouseName as outStorageName ,
isnull((case memberType when 1 then (select SUM(pt.qty) from Pro_ProductOutstorageDet pt LEFT join Pro_ProductOutstorage p on p.id=pt.parentID where pt.matStockId=o.matStockId and p.status in (1,2))
 when 0 then (select SUM(pt.qty) from Pro_ProductOutstorageDet pt LEFT join Pro_ProductOutstorage p on p.id=pt.parentID where pt.conInventoryDetId=o.matStockId and p.status in (1,2)) else 0 end),0) as chukuNum,
  (case memberType when 1 then (select SUM(ms.qty-ms.lockQty-ms.usedQty) from Pro_MatStock ms where ms.storeCode=ma.inStoreCode AND ms.materailCode =o.code)
 when 0 then (select SUM(cs.qty-cs.lockQty-cs.usedQty) from Pro_ConStock cs where cs.storeCode=ma.inStoreCode AND cs.conMemberCode =o.code) else 0 end) as xiangmukuNum
 from  Pro_MatAllotDet o 
 JOIN Pro_MatAllot ma on o.parentId = ma.id
 join sys_storehouse sh on o.outStorageCode=sh.storeHouseCode  
 where o.parentId in ({0})) t where t.chukuNum < t.qty", parentId);

View Code

 

发表评论

电子邮件地址不会被公开。 必填项已用*标注