/// <summary>
        /// 
        /// </summary>
        /// <typeparam name="TEntity"></typeparam>
        /// <param name="connectionName"></param>
        /// <param name="dr"></param>
        /// <returns></returns>
        public SqlCommandObject GenerateCommand(string connectionName,string tableName, System.Data.DataRow dr)
        {
            List<SqlCommandParameter> paramList = new List<SqlCommandParameter>();
            SqlCommandObject cmd = new SqlCommandObject();

            //生成WHERE子句
            var tableFields = EntityHelper.GetTableFields(connectionName, tableName);

            int count = tableFields.Count(p => p.IsPrimaryKey);
            if (count < 1)
                throw new FieldNotFoundException(string.Format("主键在表中没有定义,表名为:{0}", tableName));

            if (count > 1)
            {
                throw new FieldNotFoundException(string.Format("实体类不支持复合主键,表名为:{0}", tableName));
            }

            string primaryKey = tableFields.FirstOrDefault(p => p.IsPrimaryKey).Name;

            if (!dr.Table.Columns.Contains(primaryKey))
            {
                throw new FieldNotFoundException(string.Format("主键列\"{0}\"在查询结果中不存在,无法生成Delete脚本.", primaryKey));
            }

            StringBuilder whereBuilder = new StringBuilder();

            foreach (var field in tableFields)
            {
                string fieldName = field.Name.Trim() ;
                if (field.IsPrimaryKey)
                {
                    if (dr.Table.Columns.Contains(fieldName))
                    {
                        whereBuilder.Append(string.Format("[{0}]=@Orig_{1} AND ", fieldName, fieldName));
                        paramList.Add(new SqlCommandParameter() { Name = "@Orig_{0}".FormatWith(fieldName), DataType = field.DataType, Value = dr[fieldName, DataRowVersion.Original] });
                    }
                }
            }

            string where = whereBuilder.ToString();

            int index = where.LastIndexOf("AND");
            if (index >= 0)
                where = where.Substring(0, index);

            cmd.CommandText = string.Format("DELETE {0} WHERE {1}", tableName, where);
            cmd.Parameters = paramList.ToArray();
            return cmd;
        }
Esempio n. 2
0
 public OperationResult ExecuteNonQueryByTransaction(string serviceName, string connectionName, SqlCommandObject[] sqlScrips)
 {
     try
     {
         Database db = DatabaseFactory.CreateDatabase(serviceName, connectionName);
         using (var connection = db.GetNewOpenConnection())
         {
             using (var trans = connection.BeginTransaction())
             {
                 try
                 {
                     int result = 0;
                     foreach (var item in sqlScrips)
                     {
                         using (var cmd = db.GetSqlStringCommandFromSqlCommandObject(item))
                         {
                             result += db.ExecuteNonQuery(cmd, trans);
                         }
                     }
                     trans.Commit();
                     return new OperationResult() { Data = result, IsSucess = true };
                 }
                 catch
                 {
                     trans.Rollback();
                     throw;
                 }
             }
         }
     }
     catch (Exception ex)
     {
         return new OperationResult() { Message = ex.GetAllMessage(), IsSucess = false };
     }
 }
Esempio n. 3
0
 public OperationResult ExecuteNonQuery(string serviceName, string connectionName, SqlCommandObject[] sqlScrips)
 {
     try
     {
         Database db = DatabaseFactory.CreateDatabase(serviceName, connectionName);
         int result = 0;
         foreach (var item in sqlScrips)
         {
             using (var cmd = db.GetSqlStringCommandFromSqlCommandObject(item))
             {
                 result += db.ExecuteNonQuery(cmd);
             }
         }
         return new OperationResult() { Data = result, IsSucess = true };
     }
     catch (Exception ex)
     {
         return new OperationResult() { Message = ex.GetAllMessage(), IsSucess = false };
     }
 }
Esempio n. 4
0
 /// <summary>
 /// 
 /// </summary>
 /// <param name="groupId"></param>
 /// <param name="commandText"></param>
 /// <param name="parameters"></param>
 public void Execute(int groupId, string commandText, params object[] parameters)
 {
     SqlCommandObject obj = new SqlCommandObject();
     obj.CommandText = commandText;
     obj.GroupId = groupId;
     obj.ParameterValues = parameters.ToArray();
     this.Execute(obj);
 }
Esempio n. 5
0
 /// <summary>
 /// 
 /// </summary>
 /// <param name="sqlObj"></param>
 public void Execute(SqlCommandObject sqlObj)
 {
     this.sqlObjects.Add(sqlObj);
 }
        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="TEntity"></typeparam>
        /// <param name="connectionName"></param>
        /// <param name="dr"></param>
        /// <returns></returns>
        public SqlCommandObject GenerateCommand(string connectionName, string tableName, System.Data.DataRow dr)
        {
            List<SqlCommandParameter> paramList = new List<SqlCommandParameter>();
            SqlCommandObject cmd = new SqlCommandObject();

            StringBuilder fieldsBuilder = new StringBuilder();
            StringBuilder fieldParamsBuilder = new StringBuilder();

            //生成字段列表
            var tableFields = EntityHelper.GetTableFields(connectionName, tableName);

            int count = tableFields.Count(p => p.IsPrimaryKey);
            if (count < 1)
                throw new FieldNotFoundException(string.Format("主键在表中没有定义,表名为:{0}", tableName));

            if (count > 1)
            {
                throw new FieldNotFoundException(string.Format("实体类不支持复合主键,表名为:{0}", tableName));
            }

            string primaryKey = tableFields.FirstOrDefault(p => p.IsPrimaryKey).Name;

            if (!dr.Table.Columns.Contains(primaryKey))
            {
                throw new FieldNotFoundException(string.Format("主键列\"{0}\"在查询结果中不存在,无法生成Insert脚本.", primaryKey));
            }

            foreach (var field in tableFields)
            {
                string fieldName = field.Name.Trim();

                if (dr.Table.Columns.Contains(fieldName) && !field.IsIdentity && !field.IsVersionNumber)
                {
                    fieldsBuilder.Append(string.Format("[{0}], ", fieldName));

                    if (dr[fieldName] != null && dr[fieldName] != DBNull.Value)
                    {
                        fieldParamsBuilder.Append(string.Format("@Curr_{0}, ", fieldName));
                        paramList.Add(new SqlCommandParameter() { Name = "@Curr_{0}".FormatWith(fieldName), DataType = field.DataType, Value = dr[fieldName] });
                    }
                    else
                    {
                        if (field.InsertDefaultValue != null && field.InsertDefaultValue != DBNull.Value)
                        {
                            var paramValue = DefaultValueHelper.GetValue(field.InsertDefaultValue);
                            fieldParamsBuilder.Append(string.Format("{0}, ", paramValue));
                        }
                        else
                        {
                            fieldParamsBuilder.Append(string.Format("@Curr_{0}, ", fieldName));
                            paramList.Add(new SqlCommandParameter() { Name = "@Curr_{0}".FormatWith(fieldName), DataType = field.DataType, Value = DBNull.Value });
                        }
                    }
                }
            }

            string fields = fieldsBuilder.ToString();
            string fieldParams = fieldParamsBuilder.ToString();

            if (!string.IsNullOrWhiteSpace(fields) && fields.LastIndexOf(",") >= 0)
            {
                fields = fields.Substring(0, fields.LastIndexOf(","));
                fieldParams = fieldParams.Substring(0, fieldParams.LastIndexOf(","));
            }
            cmd.CommandText = string.Format(" INSERT {0} ({1}) VALUES({2})", tableName, fields, fieldParams);
            cmd.Parameters = paramList.ToArray();
            return cmd;
        }
Esempio n. 7
0
 /// <summary>
 /// 
 /// </summary>
 /// <param name="connectionName"></param>
 /// <param name="sqlScripts"></param>
 /// <returns></returns>
 public OperationResult ExecuteNonQueryByTransaction(string serviceName, string connectionName, SqlCommandObject[] sqlScripts)
 {
     ChannelFactory<IWcfPortal> cf = GetChannelFactory();
     IWcfPortal svr = GetProxy(cf);
     OperationResult result = null;
     try
     {
         result = svr.ExecuteNonQueryByTransaction(serviceName, connectionName, sqlScripts);
         if (cf != null)
             cf.Close();
     }
     catch
     {
         cf.Abort();
         throw;
     }
     return result;
 }
        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="TEntity"></typeparam>
        /// <param name="connectionName"></param>
        /// <param name="dr"></param>
        /// <returns></returns>
        public SqlCommandObject GenerateCommand(string connectionName, string tableName, System.Data.DataRow dr)
        {
            List<SqlCommandParameter> paramList = new List<SqlCommandParameter>();
            SqlCommandObject cmd = new SqlCommandObject();
            StringBuilder setBuilder = new StringBuilder();
            StringBuilder whereBuilder = new StringBuilder();

            var tableFields = EntityHelper.GetTableFields(connectionName, tableName);

            int count = tableFields.Count(p => p.IsPrimaryKey);
            if (count < 1)
                throw new FieldNotFoundException(string.Format("主键在表中没有定义,表名为:{0}", tableName));

            if (count > 1)
            {
                throw new FieldNotFoundException(string.Format("实体类不支持复合主键,表名为:{0}", tableName));
            }

            string primaryKey = tableFields.FirstOrDefault(p => p.IsPrimaryKey).Name;

            if (!dr.Table.Columns.Contains(primaryKey))
            {
                throw new FieldNotFoundException(string.Format("主键列\"{0}\"在查询结果中不存在,无法生成Update脚本.", primaryKey));
            }

            foreach (var field in tableFields)
            {
                string fieldName = field.Name.Trim();

                if (dr.Table.Columns.Contains(fieldName) && !field.IsIdentity && !field.IsVersionNumber)
                {
                    if (field.IsPrimaryKey)
                    {
                        whereBuilder.Append(string.Format("[{0}] = @Orig_{1} ", fieldName, fieldName));
                        paramList.Add(new SqlCommandParameter() { Name = "@Orig_{0}".FormatWith(fieldName), Value = dr[fieldName, DataRowVersion.Original] });
                    }

                    if (!dr[fieldName].Equals(dr[fieldName, DataRowVersion.Original]))
                    {
                        if (field.UpdateDefaultValue != null && field.UpdateDefaultValue != DBNull.Value)
                        {
                            var paramValue = DefaultValueHelper.GetValue(field.UpdateDefaultValue);
                            setBuilder.Append(string.Format("[{0}] = {1}, ", fieldName, paramValue));
                        }
                        else
                        {
                            setBuilder.Append(string.Format("[{0}] = @Curr_{1}, ", fieldName, fieldName));
                            paramList.Add(new SqlCommandParameter() { Name = "@Curr_{0}".FormatWith(fieldName), DataType = field.DataType, Value = dr[fieldName] });
                        }
                    }
                    else
                    {
                        if (field.UpdateDefaultValue != null && field.UpdateDefaultValue != DBNull.Value)
                        {
                            var paramValue = DefaultValueHelper.GetValue(field.UpdateDefaultValue);
                            setBuilder.Append(string.Format("[{0}] = {1}, ", fieldName, paramValue));
                        }
                    }
                }
            }
            int index = 0;
            string set = setBuilder.ToString();
            index = set.LastIndexOf(",");
            if (index >= 0)
                set = set.Substring(0, index);
            if (!string.IsNullOrWhiteSpace(set))
            {
                cmd.CommandText = string.Format("UPDATE {0} SET {1} WHERE {2}", tableName, set, whereBuilder.ToString());
                cmd.Parameters = paramList.ToArray();
            }
            else
            {
                cmd = null;
            }
            return cmd;
        }