예제 #1
0
 /// <summary>
 /// 解析Update命令定义集合
 /// </summary>
 /// <param name="upcmd">更新命令结构</param>
 /// <returns>返回解析后的sql集合</returns>
 public static List<string> __ParseUpdateDataTable(UpdateCommandCondition upcmd, ConnectionModes cnnmode)
 {
     var sqlText = new List<string>();
     var upFields = new UpdateFieldConditionCollections();
     switch (upcmd.UpdateCommandType)
     {
         case UpdateCommandType.Insert:
             //组合insert sql
             var insertData = upcmd.DataSource.GetChanges(DataRowState.Added);
             if (insertData != null)
                 foreach (DataRow dr in insertData.Rows)
                 {
                     upFields.Clear();
                     foreach (var tbCol in tabColumns[upcmd.DataSource.TableName.ToUpper()])
                         upFields.Add(tbCol.ColumnName, dr[tbCol.ColumnName]);
                     sqlText.Add(__ParseInsertSQL(upcmd.DataSource.TableName.ToUpper(), upFields, cnnmode));
                 }
             break;
         case UpdateCommandType.Update:
             //组合update sql
             var updateData = upcmd.DataSource.GetChanges(DataRowState.Modified);
             if (updateData != null)
                 foreach (DataRow dr in updateData.Rows)
                 {
                     upFields.Clear();
                     foreach (var tbCol in tabColumns[upcmd.DataSource.TableName.ToUpper()])
                         upFields.Add(tbCol.ColumnName, dr[tbCol.ColumnName]);
                     //获取主键
                     sqlText.Add(
                         __ParseUpdateSQL(upcmd.DataSource.TableName.ToUpper(), upFields,
                                          GetKeyColumnToQueryGroup(upcmd.DataSource, dr), cnnmode));
                 }
             break;
         case UpdateCommandType.Delete:
             //组合delete sql
             var deleteData = upcmd.DataSource.GetChanges(DataRowState.Deleted);
             if (deleteData != null)
             {
                 deleteData.RejectChanges();
                 sqlText.AddRange(from DataRow dr in deleteData.Rows
                                  select
                                      __ParseDeleteSQL(upcmd.DataSource.TableName,
                                                       GetKeyColumnToQueryGroup(upcmd.DataSource, dr), cnnmode));
             }
             break;
         default:
             sqlText.Clear();
             var allData = upcmd.DataSource.GetChanges();
             if (allData != null)
                 foreach (DataRow dr in allData.Rows)
                 {
                     switch (dr.RowState)
                     {
                         case DataRowState.Added:
                             upFields.Clear();
                             foreach (var tbCol in tabColumns[upcmd.DataSource.TableName.ToUpper()])
                                 upFields.Add(tbCol.ColumnName, dr[tbCol.ColumnName]);
                             sqlText.Add(__ParseInsertSQL(upcmd.DataSource.TableName.ToUpper(), upFields, cnnmode));
                             break;
                         case DataRowState.Deleted:
                             sqlText.Add(__ParseDeleteSQL(upcmd.DataSource.TableName,
                                                          GetKeyColumnToQueryGroup(upcmd.DataSource, dr), cnnmode));
                             break;
                         case DataRowState.Modified:
                             upFields.Clear();
                             foreach (var tbCol in tabColumns[upcmd.DataSource.TableName.ToUpper()])
                                 upFields.Add(tbCol.ColumnName, dr[tbCol.ColumnName]);
                             //获取主键
                             sqlText.Add(
                                 __ParseUpdateSQL(upcmd.DataSource.TableName.ToUpper(), upFields,
                                                  GetKeyColumnToQueryGroup(upcmd.DataSource, dr), cnnmode));
                             break;
                     }
                 }
             break;
     }
     return sqlText;
 }
예제 #2
0
        /// <summary>
        ///   解析update
        /// </summary>
        /// <param name = "tableName">物理表名</param>
        /// <param name = "updateFields">更新字段</param>
        /// <param name = "queryGroup">查询集合</param>
        /// <returns>解析后的sql</returns>
        public static string __ParseUpdateSQL(string tableName, UpdateFieldConditionCollections updateFields,
                                             QueryGroupConditionCollections queryGroup, ConnectionModes cnnmode)
        {
            var sqlBuilder = new StringBuilder();
            if (string.IsNullOrEmpty(tableName))
                throw new Exception("无效的表名称!");

            sqlBuilder.AppendFormat("Update {0} ", tableName);
            if (updateFields == null || updateFields.Count < 1)
                throw new Exception("无效的更新字段参数!");
            sqlBuilder.Append(" set ");
            for (var n = 0; n < updateFields.Count; n++)
            {
                var upField = updateFields[n];
                sqlBuilder.AppendFormat(" {0}{1}{2} ", upField.FieldName, GetOperatorType(upField.Operator),
                                        FormatSQLValue(upField.Value, cnnmode));
                if (n < updateFields.Count - 1)
                    sqlBuilder.AppendFormat(" , ");
            }
            if (queryGroup != null && queryGroup.Count > 0)
            {
                sqlBuilder.AppendFormat(" where ");
                for (var i = 0; i < queryGroup.Count; i++)
                {
                    var qry = queryGroup[i];
                    sqlBuilder.AppendFormat(" (");
                    for (var j = 0; j < qry.FieldConditions.Count; j++)
                    {
                        var field = qry.FieldConditions[j];
                        sqlBuilder.AppendFormat("( {0} {1} {2} )", field.FieldName, GetOperatorType(field.Operator),
                                                FormatSQLValue(field.Value, cnnmode));
                        if (j < qry.FieldConditions.Count - 1)
                            sqlBuilder.AppendFormat(" {0} ", GetWhereUnionType(field.NextUnionType));
                    }
                    sqlBuilder.AppendFormat(") ");
                    if (i < (queryGroup.Count - 1))
                        sqlBuilder.AppendFormat(" {0} ", GetWhereUnionType(qry.NextUnionType));
                }
            }
            Console.WriteLine("create sql:" + sqlBuilder);
            return sqlBuilder.ToString();
        }
예제 #3
0
        /// <summary>
        /// 解析insert
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="updateFields">更新字段结构集合</param>
        /// <returns>返回解析后的sql</returns>
        public static string __ParseInsertSQL(string tableName, UpdateFieldConditionCollections updateFields, ConnectionModes cnnmode)
        {
            var sqlBuilder = new StringBuilder();
            if (string.IsNullOrEmpty(tableName))
                throw new Exception("无效的表名称!");

            sqlBuilder.AppendFormat("Insert Into {0} ", tableName);
            if (updateFields == null || updateFields.Count < 1)
                throw new Exception("无效的更新字段参数!");
            sqlBuilder.Append("(");
            for (var n = 0; n < updateFields.Count; n++)
            {
                var upField = updateFields[n];
                sqlBuilder.AppendFormat(" {0} ", upField.FieldName);
                if (n < updateFields.Count - 1)
                    sqlBuilder.Append(" , ");
            }
            sqlBuilder.Append(")values(");
            for (var n = 0; n < updateFields.Count; n++)
            {
                var upField = updateFields[n];
                sqlBuilder.AppendFormat(" {0} ", FormatSQLValue(upField.Value, cnnmode));
                if (n < updateFields.Count - 1)
                    sqlBuilder.Append(" , ");
            }
            sqlBuilder.Append(")");
            Console.WriteLine("create sql:" + sqlBuilder);
            return sqlBuilder.ToString();
        }
예제 #4
0
 ///<summary>
 ///  更新全部数据
 ///</summary>
 ///<param name = "tableName">表名称</param>
 ///<param name = "updateFields">更新字段</param>
 public void UpdateTableAllRows(string tableName, UpdateFieldConditionCollections updateFields)
 {
     ExecuteSql(AddSQLBuffer(__ParseUpdateSQL(tableName, updateFields, null, this._ConnectionMode)));
 }
예제 #5
0
 ///<summary>
 ///  更新数据
 ///</summary>
 ///<param name = "tableName">表名称</param>
 ///<param name = "updateFields">更新字段</param>
 ///<param name = "query">查询组</param>
 public void UpdateTable(string tableName, UpdateFieldConditionCollections updateFields,
                         QueryGroupCondition query)
 {
     var queryGroup = new QueryGroupConditionCollections { query };
     //return
     UpdateTable(tableName, updateFields, queryGroup);
 }
예제 #6
0
        ///<summary>
        ///  更新数据
        ///</summary>
        ///<param name = "tableName">表名称</param>
        ///<param name = "updateFields">更新字段</param>
        ///<param name = "queryGroup">查询组</param>
        ///<exception cref = "Exception">UpdateTable 方法删除数据必须传入查询组参数!</exception>
        public void UpdateTable(string tableName, UpdateFieldConditionCollections updateFields,
                                QueryGroupConditionCollections queryGroup)
        {
            if (queryGroup == null || queryGroup.Count < 1)
                throw new Exception("UpdateTable  方法更新数据必须传入查询组参数!");
            if (updateFields == null || updateFields.Count < 1)
                throw new Exception("UpdateTable 方法更新数据必须参数更新字段!");

            ExecuteSql(AddSQLBuffer(__ParseUpdateSQL(tableName, updateFields, queryGroup, this._ConnectionMode)));
        }
예제 #7
0
        ///<summary>
        ///  插入数据
        ///</summary>
        ///<param name = "tableName">表名称</param>
        ///<param name = "updateFields">插入字段</param>
        ///<exception cref = "Exception">InsertIntoTable 方法插入数据必须参数更新字段</exception>
        public void InsertIntoTable(string tableName, UpdateFieldConditionCollections updateFields)
        {
            if (updateFields == null || updateFields.Count < 1)
                throw new Exception("InsertIntoTable 方法插入数据必须参数更新字段!");

            ExecuteSql(AddSQLBuffer(__ParseInsertSQL(tableName, updateFields, this._ConnectionMode)));
        }