Ejemplo n.º 1
0
        /// <summary>
        /// 按主键删除
        /// </summary>
        /// <param name="keyValues">主键值</param>
        /// <returns>删除是否成功</returns>
        public bool Delete(string[] keyValues)
        {
            if (primaryKeys.Length != keyValues.Length)
            {
                return(false);
            }

            string             condition = "";
            List <DbParameter> pars      = new List <DbParameter>();

            for (int i = 0; i < keyValues.Length; i++)
            {
                condition += primaryKeys[i] + "=@" + primaryKeys[i] + " AND ";
                pars.Add(SQLFactory.CreateParameter(primaryKeys[i], keyValues[i]));
            }

            string sql = String.Format(deleteSQL, tableName, condition.Substring(0, condition.LastIndexOf("AND")));

            try
            {
                if (DBTran == null)
                {
                    return(DBHelper.ExecuteNonQuery(System.Data.CommandType.Text, sql, pars) > 0);
                }
                else
                {
                    return(DBHelper.ExecuteNonQuery(DBTran.Transaction, System.Data.CommandType.Text, sql, pars) > 0);
                }
            }
            catch
            {}
            return(false);
        }
Ejemplo n.º 2
0
        /// <summary>
        /// 获取UpdateSQL语句
        /// </summary>
        /// <param name="entity">实体对象</param>
        /// <param name="allowNull">是否允许值为null的属性(true:为null的属性赋空值;false:忽略为null的属性不做处理,保持数据库原样)</param>
        private string getUpdateSQL(EntityBase entity, OptType allowNull)
        {
            condition = "";

            foreach (DataField field in EntityUtil.GetDataFields(entity))//构造SQL参数集合
            {
                pars.Add(SQLFactory.CreateParameter(field.FieldName, field.FieldValue));
                //pars.Add(new SqlParameter(field.FieldName, field.FieldValue));

                if (EntityUtil.GetPrimaryKey(tableName).Contains(field.FieldName))
                {
                    condition += field.FieldName + "=@" + field.FieldName + " AND ";
                }
                else
                {
                    if (!field.IsIdentity)
                    {
                        if (field.FieldValue == null && allowNull != OptType.UpdateAllowedNull)
                        {
                            continue;
                        }
                        else
                        {
                            fs.Append(field.FieldName + "=@" + field.FieldName + ",");
                        }
                    }
                }
            }

            return(String.Format(updateSQL, tableName, fs.ToString().Trim(','), condition.Substring(0, condition.LastIndexOf("AND"))));//生成UPDATE语句
        }
Ejemplo n.º 3
0
        /// <summary>
        /// 根据主键查询(联合主键情况)
        /// </summary>
        /// <param name="keyValues">主键对应的值</param>
        /// <returns>实体对象</returns>
        public T FindByPK(string[] keyValues)
        {
            if (primaryKeys.Length != keyValues.Length)
            {
                return(null);
            }

            string             condition = "";
            List <DbParameter> pars      = new List <DbParameter>();

            for (int i = 0; i < primaryKeys.Length; i++)
            {
                condition += primaryKeys[i] + "=@" + primaryKeys[i] + " AND ";
                pars.Add(SQLFactory.CreateParameter(primaryKeys[i], keyValues[i]));
            }
            condition = condition.Substring(0, condition.LastIndexOf("AND"));
            string sql = String.Format(selectSQL, "*", tableName, condition);

            T model = default(T);

            try
            {
                model = DataConvert <T> .ToEntity(DBHelper.Query(sql, pars).Rows[0]);

                //关联对象加载
                EntityUtil.SetAssociation(model);
            }
            catch { }

            return(model);
        }
Ejemplo n.º 4
0
        /// <summary>
        /// 用户自定义sql语句查询
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <param name="parms">查询参数列表</param>
        /// <returns>查询对象集合</returns>
        public static List <T> QueryEx(String sql, List <DbParameter> parms = null)
        {
            DataTable dt       = SQLFactory.CreateDBHelper().Query(sql, parms);
            List <T>  entities = NDolls.Data.Util.DataConvert <T> .ToEntities(dt);

            return(entities);
        }
Ejemplo n.º 5
0
        /// <summary>
        /// 用户自定义sql语句批量执行(非查询语句)
        /// </summary>
        /// <param name="sql">非查询sql语句集合</param>
        /// <returns>是否成功</returns>
        public static bool Excute(List <String> sqls)
        {
            DbConnection conn = SQLFactory.CreateDBConnection();

            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            DbTransaction tran = conn.BeginTransaction();

            try
            {
                foreach (String sql in sqls)
                {
                    SQLFactory.CreateDBHelper().ExecuteNonQuery(tran, System.Data.CommandType.Text, sql, null);
                }

                tran.Commit();
                return(true);
            }
            catch
            {
                return(false);
            }
        }
Ejemplo n.º 6
0
        /// <summary>
        /// 根据Dictionary获取Update的SQL语句赋值部分(取mode中不为null的属性)
        /// </summary>
        protected String getSetValueSql(Dictionary <String, Object> dic, List <DbParameter> pars)
        {
            StringBuilder fs = new StringBuilder(); //Update赋值相关部分

            foreach (String key in dic.Keys)        //构造SQL参数集合
            {
                if (dic[key] != null && dic[key].ToString() != "")
                {
                    fs.Append(key + "=@" + key + ",");
                    pars.Add(SQLFactory.CreateParameter(key, dic[key].ToString()));
                }
            }

            return(fs.ToString().TrimEnd(','));
        }
Ejemplo n.º 7
0
        private string getDeleteSQL(EntityBase entity)
        {
            string pk = EntityUtil.GetPrimaryKey(tableName);

            string[] pks = pk.Split(',');
            pars      = new List <DbParameter>();
            condition = "";

            for (int i = 0; i < pks.Length; i++)
            {
                condition += pks[i] + "=@" + pks[i] + " AND ";
                pars.Add(SQLFactory.CreateParameter(pks[i], EntityUtil.GetValueByField(entity, pks[i])));
                //pars.Add(new SqlParameter(pks[i], EntityUtil.GetValueByField(entity, pks[i])));
            }

            return(String.Format(deleteSQL, tableName, condition.Substring(0, condition.LastIndexOf(','))));
        }
Ejemplo n.º 8
0
        /// <summary>
        /// 开启事务处理
        /// </summary>
        public void TransactionOpen()
        {
            Guid sid = Guid.NewGuid();

            ts.SID = sid;

            if (ts.SConnection == null)
            {
                ts.SConnection = SQLFactory.CreateDBConnection();
            }

            if (ts.SConnection.State != System.Data.ConnectionState.Open)
            {
                ts.SConnection.Open();
            }

            ts.STransaction = ts.SConnection.BeginTransaction();

            tranConnectionDic.Add(sid, ts);//开启批量事务操作,将TranSession存入字典
        }
Ejemplo n.º 9
0
        private string getCreateSQL(EntityBase entity)
        {
            string identitySql = "";

            foreach (DataField field in EntityUtil.GetDataFields(entity)) //构造SQL参数集合
            {
                if (field.FieldValue != null && !field.IsIdentity)        //字段值不为空 且 不是标识
                {
                    pars.Add(SQLFactory.CreateParameter(field.FieldName, field.FieldValue));
                    fs.Append(field.FieldName + ",");
                    vs.Append("@" + field.FieldName + ",");
                }
                else if (field.FieldValue != null && !field.IsIdentity)//如果有标识字段,返回最新标识值
                {
                    identitySql  = ";";
                    identitySql += SQL.ResourceManager.GetString(DataConfig.DatabaseType + "Identity").Replace("{0}", EntityUtil.GetTableName(entity.GetType()));
                }
            }

            return(String.Format(insertSQL, tableName, fs.ToString().TrimEnd(','), vs.ToString().TrimEnd(',')) + identitySql);
        }
Ejemplo n.º 10
0
        /// <summary>
        /// 按主键删除
        /// </summary>
        /// <param name="keyValue">主键值</param>
        /// <returns>删除是否成功</returns>
        public bool Delete(string keyValue)
        {
            string             sql  = String.Format(deleteSQL, tableName, primaryKey + "=@" + primaryKey);
            List <DbParameter> pars = new List <DbParameter>();

            pars.Add(SQLFactory.CreateParameter(primaryKey, keyValue));

            try
            {
                if (DBTran == null)
                {
                    return(DBHelper.ExecuteNonQuery(System.Data.CommandType.Text, sql, pars) > 0);
                }
                else
                {
                    return(DBHelper.ExecuteNonQuery(DBTran.Transaction, System.Data.CommandType.Text, sql, pars) > 0);
                }
            }
            catch
            {}
            return(false);
        }
Ejemplo n.º 11
0
        /// <summary>
        /// 根据Model获取Update的SQL语句赋值部分(取mode中不为null的属性)
        /// </summary>
        protected String getSetValueSql(T model, List <DbParameter> pars)
        {
            StringBuilder fs = new StringBuilder();                      //Update赋值相关部分

            foreach (DataField field in EntityUtil.GetDataFields(model)) //构造SQL参数集合
            {
                if (!field.IsIdentity)
                {
                    if (field.FieldValue == null || (field.FieldType == "datetime" && field.FieldValue.ToString().Contains("0001")))
                    {
                        continue;
                    }
                    else
                    {
                        fs.Append(field.FieldName + "=@" + field.FieldName + ",");
                        pars.Add(SQLFactory.CreateParameter(field.FieldName, field.FieldValue));
                    }
                }
            }

            return(fs.ToString().TrimEnd(','));
        }
Ejemplo n.º 12
0
        /// <summary>
        /// 根据主键查询
        /// </summary>
        /// <param name="keyValue">主键对应的值</param>
        /// <returns>实体对象</returns>
        public T FindByPK(string keyValue)
        {
            string sql = String.Format(selectSQL, "*", tableName, primaryKey + "=@" + primaryKey);

            List <DbParameter> pars = new List <DbParameter>();

            pars.Add(SQLFactory.CreateParameter(primaryKey, keyValue));

            T model = default(T);

            try
            {
                model = DataConvert <T> .ToEntity(DBHelper.Query(sql, pars).Rows[0]);

                #region 级联对象加载
                //关联对象加载
                EntityUtil.SetAssociation(model);
                #endregion
            }
            catch { }

            return(model);
        }
Ejemplo n.º 13
0
        /// <summary>
        /// 根据对象主键查询是否存在该对象
        /// </summary>
        /// <param name="keyValue">主键对应的值</param>
        /// <returns>是否存在</returns>
        public bool Exist(T model)
        {
            String[]           pks        = primaryKey.Split(',');
            String             conditions = "";
            List <DbParameter> pars       = new List <DbParameter>();

            foreach (String pk in pks)
            {
                if (conditions != "")
                {
                    conditions += (" AND " + pk + "=@" + pk);
                }
                else
                {
                    conditions += (pk + "=@" + pk);
                }
                pars.Add(SQLFactory.CreateParameter(pk, EntityUtil.GetValueByField(model, pk)));
            }

            string sql = String.Format(selectSQL, "COUNT(*)", tableName, conditions);

            return("0" != DBHelper.ExecuteScalar(System.Data.CommandType.Text, sql, pars).ToString());
        }
Ejemplo n.º 14
0
 /// <summary>
 /// 用户自定义sql语句查询(返回一行一列)
 /// </summary>
 /// <param name="sql">查询sql语句</param>
 /// <returns>查询结果</returns>
 public static object Single(String sql, List <DbParameter> parms = null)
 {
     return(SQLFactory.CreateDBHelper().ExecuteScalar(System.Data.CommandType.Text, sql, parms));
 }
Ejemplo n.º 15
0
 /// <summary>
 /// 构造函数
 /// </summary>
 public DBTransaction()
 {
     this.dbHelper = SQLFactory.CreateDBHelper();
 }
Ejemplo n.º 16
0
 /// <summary>
 /// 用户自定义sql语句查询
 /// </summary>
 /// <param name="sql">查询语句</param>
 /// <param name="parms">查询参数列表</param>
 /// <returns>查询结果集</returns>
 public static DataTable Query(String sql, List <DbParameter> parms = null)
 {
     return(SQLFactory.CreateDBHelper().Query(sql, parms));
 }
Ejemplo n.º 17
0
        /// <summary>
        /// 事务执行
        /// </summary>
        /// <returns>事务执行结果</returns>
        public bool Excute()
        {
            DbConnection  conn = null;
            DbTransaction tran = null;

            if (!tranConnectionDic.ContainsKey(ts.SID))//若未开启全局事务处理
            {
                conn = SQLFactory.CreateDBConnection();
                conn.Open();
                tran = conn.BeginTransaction();
            }
            else//若开启全局事务处理
            {
                conn = ts.SConnection;
                tran = ts.STransaction;
            }

            //数据处理
            try
            {
                foreach (OptEntity item in entities)
                {
                    tableName = EntityUtil.GetTableName(item.Entity.GetType());
                    resetVars();//重置参数(新对象重新赋值)

                    switch (item.Type)
                    {
                    case OptType.Create:    //新增
                        sql = getCreateSQL(item.Entity);
                        break;

                    case OptType.UpdateAllowedNull:    //修改
                        sql = getUpdateSQL(item.Entity, OptType.UpdateAllowedNull);
                        break;

                    case OptType.UpdateIgnoreNull:    //修改
                        sql = getUpdateSQL(item.Entity, OptType.UpdateIgnoreNull);
                        break;

                    case OptType.Save:
                        break;

                    case OptType.Delete:    //删除
                        sql = getDeleteSQL(item.Entity);
                        break;
                    }

                    if (!String.IsNullOrEmpty(sql))
                    {
                        dbHelper.ExecuteNonQuery(tran, System.Data.CommandType.Text, sql, pars);
                    }
                }

                //字典中存在SID:事务操作(多个对象批量操作);字典中不存在SID:非事务操作(单个对象操作)
                if (!tranConnectionDic.ContainsKey(ts.SID))
                {
                    tran.Commit();
                    conn.Close();
                }
                return(true);
            }
            catch (Exception ex)
            {
                if (!tranConnectionDic.ContainsKey(ts.SID))//若未开启全局事务处理
                {
                    tran.Rollback();
                    conn.Close();
                }
                throw (ex);
            }
        }
Ejemplo n.º 18
0
 /// <summary>
 /// 用户自定义sql语句执行(非查询语句)
 /// </summary>
 /// <param name="sql">非查询sql语句</param>
 /// <returns>执行成功的数据行数</returns>
 public static int Excute(String sql)
 {
     return(SQLFactory.CreateDBHelper().ExecuteNonQuery(System.Data.CommandType.Text, sql, null));
 }