/// <summary>
/// 自动更新窗体数据
/// </summary>
/// <param name="Controls">控件集合</param>
/// <returns></returns>
public List <IBCommand> AutoUpdateIBFormData(ControlCollection Controls)
{
List <IBCommand> ibCommandList = GetIBFormData(Controls, this.DAO);
int result = 0;
foreach (object item in ibCommandList)
{
IBCommand command = (IBCommand)item;
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)
{
//2013-3-8 已经不需要克隆，看CommandDB
////参数已经被使用，必须克隆一份
//IDataParameter[] paras = new IDataParameter[command.Parameters.Length];
//int index = 0;
//foreach (IDataParameter old in command.Parameters)
//{
// paras[index++] = (IDataParameter)((ICloneable)old).Clone();
//}
////
//result = DAO.ExecuteNonQuery(command.InsertCommand, CommandType.Text, paras);
result = DAO.ExecuteNonQuery(command.InsertCommand, CommandType.Text, command.Parameters);
}
}
else
{
object id = 0;
result = DAO.ExecuteInsertQuery(command.InsertCommand, CommandType.Text, command.Parameters, ref id);
command.InsertedID = Convert.ToInt32(id);
}
if (result <= 0 && _CheckRowUpdateCount)
{
throw new Exception("在更新表" + command.TableName + "中未取得受影响的行数，数据错误信息：" + DAO.ErrorMessage);
}
}
return(ibCommandList);
}
/// <summary>
/// 收集窗体中的智能控件，组合成能够直接用于数据库插入和更新 查询的 SQL语句
/// 一个窗体中可以同时处理多个表的数据操作
/// 如果控件的数据属性设置为只读，那么该控件的值不会更新到数据库；如果该控件的数据属性设置为主键，那么更新语句将附带该条件
/// 邓太华 2008.1.15
/// </summary>
/// <returns>
/// ArrayList 中的成员为 IBCommand 对象，包含具体的CRUD SQL
///</returns>
public static List<IBCommand> GetIBFormData(ControlCollection Controls)
{
List<IDataControl> IBControls = new List<IDataControl>();
findIBControls(IBControls ,Controls );
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;
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!="" )
{
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 )
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 ();
}
}
}
//非只读的数据才更新
if(cValue!="")
{
//2010.1.25 取消 ibCtr.PrimaryKey 不能更新的限制，例如可以让GUID主键列可以更新
//如果是自增列，设置该列的控件属性为 只读属性即可。
if (!ibCtr.ReadOnly)
{
strFields+=ibCtr.LinkProperty +",";
strValues+=cValue+",";
strUpdate+=ibCtr.LinkProperty+"="+cValue+",";
}
if (ibCtr.PrimaryKey) //只要是主键就作为更新的条件
{
strCondition += " And " + ibCtr.LinkProperty + "=" + cValue;
if (ibCtr.SysTypeCode == System.TypeCode.Int32)
ID = int.Parse(cValue);
else
ID = -2;//主键可能是非数字型
}
}
}
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;
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 (temp.Trim() != "," && 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;
}
/// <summary>
/// 收集窗体中的智能控件，组合成能够直接用于数据库插入和更新 查询的 SQL语句
/// 一个窗体中可以同时处理多个表的数据操作
/// 如果控件的数据属性设置为只读，那么该控件的值不会更新到数据库；如果该控件的数据属性设置为主键，那么更新语句将附带该条件
/// 邓太华 2008.1.15
/// </summary>
/// <returns>
/// ArrayList 中的成员为 IBCommand 对象，包含具体的CRUD SQL
///</returns>
public static List <IBCommand> GetIBFormData(ControlCollection Controls, CommonDB DB)
{
List <IDataControl> IBControls = new List <IDataControl>();
findIBControls(IBControls, Controls);
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;
List <IDataParameter> paraList = 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 cValue = string.Empty;
object ctlValue = ibCtr.GetValue();
//非只读的数据才更新
if (ctlValue != DBNull.Value)
{
cValue = DB.GetParameterChar + "P" + paraIndex++;
IDataParameter para = DB.GetParameter(cValue, ctlValue);
if (ibCtr.SysTypeCode == System.TypeCode.String || ibCtr.SysTypeCode == System.TypeCode.Empty)
{
if (ibCtr is DataTextBox)
{
int maxStringLength = ((DataTextBox)ibCtr).MaxLength;
((IDbDataParameter)para).Size = maxStringLength;
}
}
paraList.Add(para);
//2010.1.25 取消 ibCtr.PrimaryKey 不能更新的限制，例如可以让GUID主键列可以更新
//如果是自增列，设置该列的控件属性为 只读属性即可。
if (!ibCtr.ReadOnly)
{
strFields += ibCtr.LinkProperty + ",";
strValues += cValue + ",";
strUpdate += ibCtr.LinkProperty + "=" + cValue + ",";
}
if (ibCtr.PrimaryKey) //只要是主键就作为更新的条件
{
strCondition += " And " + ibCtr.LinkProperty + "=" + cValue;
if (ibCtr.SysTypeCode == System.TypeCode.Int32)
{
ID = int.Parse(ctlValue.ToString());
}
else
{
ID = -2;//主键可能是非数字型
}
}
}
}
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.Parameters = paraList.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 (temp.Trim() != "," && 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);
}
