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);
}
