protected void AutoUpdateIBFormDataInner(List <IBCommand> ibCommandList) { int result = 0; foreach (object item in ibCommandList) { IBCommand command = (IBCommand)item; bool insertFlag = false; if (command.InsertedID > 0) { result = DAO.ExecuteNonQuery(command.UpdateCommand, CommandType.Text, command.Parameters);//修改未合并 } else if (command.InsertedID == -2) { result = DAO.ExecuteNonQuery(command.UpdateCommand, CommandType.Text, command.Parameters); if (result <= 0) { insertFlag = true; } } else { insertFlag = true; } if (insertFlag) { object id = 0; result = DAO.ExecuteInsertQuery(command.InsertCommand, CommandType.Text, command.InsertParameters, ref id, null); if (id != DBNull.Value) { try { command.InsertedID = Convert.ToInt32(id); } catch { } } else { command.InsertedID = -2; } } if (result <= 0 && CheckAffectRowCount) { throw new Exception("在更新表" + command.TableName + "中未取得受影响的行数,数据错误信息:" + DAO.ErrorMessage); } } }
/// <summary> /// 获取更新表单数据的智能表单命令,以备将这些控件中的数据保存到数据库 /// <remarks> /// 注意:1,主键数据可以插入数据库,但不可直接更新, /// 如果想更新主键字段,需要另外增加一个数据文本框控件,绑定主键字段名,但不设置主键属性, /// 同时放置一个标签数据控件,设置为主键属性并需要运行是赋值给此控件。 /// 2,自增主键字段对应的数据控件,需要设置主键属性并且不可以更新此控件的值到数据库。 /// </remarks> /// </summary> /// <param name="IBControls"></param> /// <param name="DB"></param> /// <returns></returns> protected static List <IBCommand> GetIBFormDataInner(List <IDataControl> IBControls, CommonDB DB) { List <IBCommand> IBCommands = new List <IBCommand>(); //获取表单中的CRUD 语句。 while (IBControls.Count > 0) { string strTableName = ""; string strInsert = ""; string strFields = ""; string strValues = ""; string strUpdate = ""; string strCondition = ""; int nullCount = 0; int ID = -1; int paraIndex = 0; //edit at 2015.3.7 //定义不同的参数列表,修正Access 等数据库根据参数顺序而不是参数名匹配进行查询的问题 List <IDataParameter> paraUpdateList = new List <IDataParameter>(); //更新,删除参数列表 List <IDataParameter> paraInsertList = new List <IDataParameter>(); //插入参数列表 List <IDataParameter> paraPKs = new List <IDataParameter>(); //主键参数列表 for (int i = 0; i < IBControls.Count; i++) // object objCtr in IBControls) { object objCtr = IBControls[i]; if (objCtr != null) { IDataControl ibCtr = objCtr as IDataControl; //只有非只读的控件才可以更新数据到数据库 if (ibCtr != null) { if (strTableName == "" && ibCtr.LinkObject != "") { strTableName = ibCtr.LinkObject; strInsert = "INSERT INTO [" + strTableName + "]("; strUpdate = "UPDATE [" + strTableName + "] SET "; } //找到当前处理的表,只有非只读的字段可以更新 if (strTableName == ibCtr.LinkObject && ibCtr.LinkProperty != "") { #region 原来获取值得方法 //string cValue=ibCtr.GetValue ().ToString ().Replace ("'","''"); ////dth,2008.4.11 处理字符串类型为空的情况 ////防止SQL注入式攻击 ////不论是否为空都进行字符串类型测试 //if(ibCtr.SysTypeCode==System.TypeCode.String || ibCtr.SysTypeCode==System.TypeCode.Empty) //{ // cValue="'"+ cValue +"'"; //} //else //{ // if(cValue!="") // { // if(ibCtr.SysTypeCode==System.TypeCode.Boolean ) // cValue=(cValue.ToUpper ()=="TRUE"?"1":"0"); // else if(ibCtr.SysTypeCode==System.TypeCode.DateTime ) // { // if (DB.CurrentDBMSType == DBMSType.SQLite) // cValue = "'" + DateTime.Parse(cValue).ToString("s") + "'"; // else // cValue = "'" + cValue + "'";//SQL SERVER 日期格式 // } // else if(ibCtr.SysTypeCode==System.TypeCode.DBNull ) // { // cValue="NULL"; // } // else if(ibCtr.SysTypeCode==System.TypeCode.Object ) // { // //Object 标记不做任何处理,例如可以使用最大值加一获取主键值 // } // else if(!(ibCtr.SysTypeCode==System.TypeCode.String || ibCtr.SysTypeCode==System.TypeCode.Empty)) // { // //如果不是字符串那么试图进行数字转换 // cValue=Convert.ToDouble (cValue).ToString (); // } // } //} #endregion string ctlParaName = string.Empty; object ctlValue = ibCtr.GetValue(); //非只读的数据才更新 if (ctlValue != DBNull.Value) { ctlParaName = DB.GetParameterChar + "P" + paraIndex++; IDataParameter para = DB.GetParameter(ctlParaName, ctlValue); if (ibCtr.SysTypeCode == System.TypeCode.String || ibCtr.SysTypeCode == System.TypeCode.Empty) { if (ibCtr is IDataTextBox) { int maxStringLength = ((IDataTextBox)ibCtr).MaxLength; if (maxStringLength > 0) { ((IDbDataParameter)para).Size = maxStringLength; } } } //2010.1.25 取消 ibCtr.PrimaryKey 不能更新的限制,例如可以让GUID主键列可以更新 //2015.3.7 恢复 ibCtr.PrimaryKey 不能更新的限制, //如果需要更新主键,请设置另外一个控件并将它绑定位主键字段但不设置 PrimaryKey 属性 //如果是自增列,设置该列的控件属性为 只读属性即可。 if (ibCtr.PrimaryKey) //只要是主键就作为更新的条件 { if (!string.IsNullOrEmpty(ctlValue.ToString())) { strCondition += " And [" + ibCtr.LinkProperty + "] = " + ctlParaName; if (ibCtr.SysTypeCode == System.TypeCode.Int32) { ID = int.Parse(ctlValue.ToString()); } else { ID = -2;//主键可能是非数字型 } paraPKs.Add(para); //主键数据也可能需要插入 if (!ibCtr.ReadOnly) { strFields += "[" + ibCtr.LinkProperty + "],"; strValues += ctlParaName + ","; paraInsertList.Add(para); } } } else if (!ibCtr.ReadOnly) { strFields += "[" + ibCtr.LinkProperty + "],"; strValues += ctlParaName + ","; strUpdate += "[" + ibCtr.LinkProperty + "] = " + ctlParaName + ","; paraUpdateList.Add(para); paraInsertList.Add(para); } } } IBControls[i] = null; } } else { nullCount++; } }//end for if (nullCount >= IBControls.Count - 1) { break; } strInsert += strFields.TrimEnd(',') + ") VALUES (" + strValues.TrimEnd(',') + ")"; strUpdate = strUpdate.TrimEnd(',') + " WHERE 1=1 " + strCondition; IBCommand ibcmd = new IBCommand(strTableName); ibcmd.InsertCommand = strInsert; ibcmd.UpdateCommand = strUpdate; //if( ID>0) ibcmd.InsertedID = ID; ibcmd.InsertParameters = paraInsertList.ToArray(); paraUpdateList.AddRange(paraPKs); ibcmd.Parameters = paraUpdateList.ToArray(); IBCommands.Add(ibcmd); }//end while return(IBCommands); }
/// <summary> /// 获取选择和删除查询的SQL语句 /// </summary> /// <param name="IBControls">已经收集的控件集合</param> /// <returns> ArrayList 中的成员为 IBCommand 对象,包含具体的CRUD SQL</returns> public static List <IBCommand> GetSelectAndDeleteCommand(List <IDataControl> IBControls) { List <IBCommand> IBCommands = new List <IBCommand>(); //获取表单中的CRUD 语句。 while (IBControls.Count > 0) { string strTableName = ""; string strSelect = ""; string strDelete = ""; string strFields = ""; //string strValues=""; string strCondition = ""; int nullCount = 0; for (int i = 0; i < IBControls.Count; i++)// object objCtr in IBControls) { object objCtr = IBControls[i]; if (objCtr != null) { IDataControl ibCtr = objCtr as IDataControl; //只有非只读的控件才可以更新数据到数据库 if (ibCtr != null) { if (strTableName == "") { strTableName = ibCtr.LinkObject; strSelect = "SELECT "; strDelete = "DELETE FROM [" + strTableName + "]"; } //找到当前处理的表,只读的字段也可以处理 if (strTableName == ibCtr.LinkObject && ibCtr.LinkObject != "") { string cValue = ibCtr.GetValue().ToString().Replace("'", "''"); if (ibCtr.PrimaryKey) { if (cValue != "") { //防止SQL注入式攻击 cValue = (ibCtr.SysTypeCode == System.TypeCode.String || ibCtr.SysTypeCode == System.TypeCode.DateTime ? "'" + cValue + "'" : Convert.ToDouble(cValue).ToString()); } strCondition += " And [" + ibCtr.LinkProperty + "] = " + cValue; } string temp = "[" + ibCtr.LinkProperty + "],"; if (ibCtr.LinkProperty != "" && strFields.IndexOf(temp) == -1) { strFields += temp; } IBControls[i] = null; } if (ibCtr.LinkObject == "" || ibCtr.LinkProperty == "") { IBControls[i] = null; } } } else { nullCount++; } }//end for if (strFields == "") { break; } strSelect += strFields.TrimEnd(',') + " FROM [" + strTableName + "] WHERE 1=1 " + strCondition; strDelete += " WHERE 1=1 " + strCondition; IBCommand ibcmd = new IBCommand(strTableName); ibcmd.SelectCommand = strSelect; ibcmd.DeleteCommand = strDelete; IBCommands.Add(ibcmd); if (nullCount >= IBControls.Count - 1) { break; } }//end while return(IBCommands); }