//内部使用的方法 #region /// <summary> /// 字段是否为空,并且是否是值字段 /// /// </summary> /// <param Name="Column"></param> /// <returns>如果是,返回带问号的字段类型</returns> public static string getColumnIsNull(oracleDBHelper.DBColumn column) { //字段不为空,并且类型是值类型,就的加上问号,代表值类型可以为空 if (column.ColumnIsNull == "y" && column.ColumnIsValueType) { return(column.ColumnCSharpType + "?"); } else { return(column.ColumnCSharpType); } }
/// <summary> /// 给参数数组赋值 /// idList.Add(codeParam.Id); /// nameList.Add(codeParam.Name); /// valueList.Add(codeParam.Value); /// fk_code_typeList.Add(codeParam.Fk_code_type); /// zfbzList.Add(codeParam.Zfbz); /// remarkList.Add(codeParam.Remark); /// </summary> /// <param name="columns"></param> /// <returns></returns> public static StringBuilder addParamArrayAssignment(List <oracleDBHelper.DBColumn> columns, string paramName) { StringBuilder sb = new StringBuilder(); for (int i = 0; i < columns.Count; i++) { oracleDBHelper.DBColumn column = columns[i]; sb.Append(@" " + column.ColumnName + "List.Add(" + paramName + @"." + Common.fristCharToUpper(column.ColumnName) + @"); "); } return(sb); }
/// <summary> /// 声明参数数组 /// List<string> idList = new List<string>(); /// List<string> nameList = new List<string>(); /// List<string> valueList = new List<string>(); /// List<string> fk_code_typeList = new List<string>(); /// List<string> zfbzList = new List<string>(); /// List<string> remarkList = new List<string>(); /// </summary> /// <param name="columns"></param> /// <returns></returns> public static StringBuilder addStatementParamArray(List <oracleDBHelper.DBColumn> columns) { StringBuilder sb = new StringBuilder(); for (int i = 0; i < columns.Count; i++) { oracleDBHelper.DBColumn column = columns[i]; sb.Append(@" List<" + Common.getColumnIsNull(column) + @"> " + column.ColumnName + "List = new List<" + Common.getColumnIsNull(column) + @">(); "); } return(sb); }
/// <summary> /// 选中字段的下拉列表中的项的事件 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void comboBox_columns_SelectedIndexChanged(object sender, EventArgs e) { try { oracleDBHelper.DBColumn column = (oracleDBHelper.DBColumn)comboBox_columns.SelectedItem; textBox_columnType.Text = column.ColumnOracleType; textBox_columnRemark.Text = column.ColumnComments; textBox_columnLength.Text = column.ColumnLength; textBox_columnTempTableName.Text = "TT_" + column.ColumnName.ToUpper(); } catch (Exception ex) { MessageBox.Show(ex.Message); } }
/// <summary> /// 拼接update语句(数组值的) /// </summary> /// <param name="columns"></param> /// <param name="tableName"></param> /// <returns></returns> public static StringBuilder addSqlUpdateForArray(List <oracleDBHelper.DBColumn> columns, string tableName, string paramName) { StringBuilder sb = new StringBuilder(); sb.Append(@" StringBuilder sql = new StringBuilder(""UPDATE " + tableName + @" SET "");"); for (int i = 1; i < columns.Count; i++) { oracleDBHelper.DBColumn column = columns[i]; sb.Append(@" this.isComma(sql); sql.Append("" " + column.ColumnName.ToUpper() + @"=:" + column.ColumnName + @" ""); OracleParameter p" + Common.fristCharToUpper(column.ColumnName) + @" = new OracleParameter("":" + column.ColumnName + @""", OracleDbType." + column.ColumnODACParamType + @"); p" + Common.fristCharToUpper(column.ColumnName) + @".Direction = ParameterDirection.Input; p" + Common.fristCharToUpper(column.ColumnName) + @".Value = " + columns[i].ColumnName + @"List.ToArray(); sqlparams.Add(p" + Common.fristCharToUpper(column.ColumnName) + @"); "); } return(sb); }
/// <summary> /// 主表选择下拉列表选择事件 /// </summary> /// <param Name="sender"></param> /// <param Name="e"></param> private void comboBox_tables_SelectedIndexChanged(object sender, EventArgs e) { try { oracleDBHelper.DBTable table = (oracleDBHelper.DBTable)comboBox_tables.SelectedItem; table.Columns = oracleDBHelper.getColumns(ci.getConnectionString(), table.TableName); List <oracleDBHelper.DBColumn> columnList = table.Columns; for (int i = 0; i < columnList.Count; i++) { oracleDBHelper.DBColumn column = columnList[i]; if (column.ColumnName.Length > 19) { throw new Exception(table.TableName + " 表的 " + column.ColumnName + " 字段名称过大,字段名称不能大于19个字符,请修改表字段名!"); } } richTextBox_code.Text = "请选择代码生成类型..."; textBox_ClassNamePrefix.Text = Common.fristCharToUpper(table.TableName.ToLower()); textBox1_命名空间.Text = table.TableComment; if (comboBox_codeType.SelectedItem != null && comboBox_codeType.SelectedItem.ToString() == "多表连接方法") { if (comboBox_tables.SelectedIndex == -1) { return; } comboBox_columns.Items.Clear(); //生成高级查询方法 List <oracleDBHelper.DBColumn> columns = table.Columns; for (int i = 0; i < columns.Count; i++) { comboBox_columns.Items.Add(columns[i]); } comboBox_columns.DisplayMember = "ColumnName"; } } catch (Exception ex) { MessageBox.Show(ex.Message); } }
/// <summary> /// oracle生成DAL的查询关联对象的高级方法 /// </summary> /// <param Name="TableName">表名称</param> /// <param Name="className">类名</param> /// <param Name="classRemark">类说明</param> /// <param Name="classRemark">参数类型</param> /// <param Name="paramName">参数名称</param> /// <returns>接口实现代码</returns> public static string generatorOracleDALImpFind(oracleDBHelper.DBTable table, string paramType, string paramName, List <oracleDBHelper.TempTabel> ttList, string datagridviewName) { List <oracleDBHelper.DBColumn> thisTableColumns = table.Columns; string tableName = table.TableName; StringBuilder sb = new StringBuilder(); #region datagridview隐藏基本字段 sb.Append(@" #region UI,datagridview隐藏基本字段,start //隱藏列start //" + datagridviewName + @".Columns[""ID""].Visible = false;//隐藏此列 //" + datagridviewName + @".Columns[""ID""].HeaderText = ""主键"";//设置列名 //" + datagridviewName + @".Columns[""ID""].DisplayIndex = 0;//列的顺序 //" + datagridviewName + @".Columns[""CSRQ""].DefaultCellStyle.Format = Model.Common.AppConfigHelper.GetAppConfigFordateFormat();//从配置文件中取时间格式设置到此列(用于时间列) if (null == " + datagridviewName + @".DataSource) { return; } "); for (int i = 0; i < thisTableColumns.Count; i++) { oracleDBHelper.DBColumn column = thisTableColumns[i]; if (column.ColumnCSharpType == "DateTime") { sb.Append(@" " + datagridviewName + @".Columns[" + paramType + "." + column.ColumnName.ToUpper() + @"].Visible = false;//" + column.ColumnComments + @" " + datagridviewName + @".Columns[" + paramType + "." + column.ColumnName.ToUpper() + @"].HeaderText = """ + column.ColumnComments + @"""; " + datagridviewName + @".Columns[" + paramType + "." + column.ColumnName.ToUpper() + @"].DefaultCellStyle.Format = Model.Common.AppConfigHelper.GetAppConfigFordateFormat();//从配置文件中取时间格式设置到此列(用于时间列) "); } else { sb.Append(@" " + datagridviewName + @".Columns[" + paramType + "." + column.ColumnName.ToUpper() + @"].Visible = false;//" + column.ColumnComments + @" " + datagridviewName + @".Columns[" + paramType + "." + column.ColumnName.ToUpper() + @"].HeaderText = """ + column.ColumnComments + @"""; "); } } sb.Append(@" #endregion UI,datagridview隐藏基本字段,end "); #endregion #region 实体类字段转换 sb.Append(@" #region UI,新增的实体类字段转换(隐藏原来的英文字段,新增一个中文字段,中文字段中放入转换过后的值) "); for (int i = 0; i < ttList.Count; i++) { oracleDBHelper.TempTabel tt = ttList[i]; string columnComments = tt.Column.ColumnComments; if (tt.ColumnType == oracleDBHelper.TempTabel.字段外键类型.代码类型) { sb.Append(@" SkinDataGridViewHelper.dataGridViewObjectConvertToValue(" + datagridviewName + @", """ + tt.ObjectName + @""", """ + tt.ObjectName + @"Name"", """ + columnComments + @""", CommonCode.实体类名称.基础_代码字典实体类名); "); } else if (tt.ColumnType == oracleDBHelper.TempTabel.字段外键类型.表外键类型) { sb.Append(@" SkinDataGridViewHelper.dataGridViewObjectConvertToValue(" + datagridviewName + @", """ + tt.ObjectName + @""", """ + tt.ObjectName + @"Name"", """ + columnComments + @""", CommonCode.实体类名称." + tt.RelationTable.TableComment + @"实体类名); "); } } sb.Append(@" #endregion UI,新增的实体类字段转换(隐藏原来的英文字段,新增一个中文字段,中文字段中放入转换过后的值) "); sb.Append(@" #region UI,新增的实体类字段如果不想转换显示,可以直接隐藏,不必转换 "); for (int i = 0; i < ttList.Count; i++) { oracleDBHelper.TempTabel tt = ttList[i]; string columnComments = tt.Column.ColumnComments; sb.Append(@" //" + datagridviewName + @".Columns[""" + tt.ObjectName + @"""].Visible = false;//" + columnComments + @" "); } sb.Append(@" #endregion UI,新增的实体类字段如果不想转换显示,可以直接隐藏,不必转换 //控件结束时候处理的一些事件 SkinDataGridViewHelper.endDataGridView(" + datagridviewName + @"); "); #endregion #region 实体类中新增的相关联对象 sb.Append(@" #region model,实体类新增的对象,start "); for (int i = 0; i < ttList.Count; i++) { oracleDBHelper.TempTabel tt = ttList[i]; if (tt.ColumnType == oracleDBHelper.TempTabel.字段外键类型.代码类型) { sb.Append(@" /// <summary> /// " + tt.Column.ColumnComments + @"对象 /// </summary> private Model.JC.Jc_code " + tt.ObjectName + @"; /// <summary> /// " + tt.Column.ColumnComments + @"对象 /// </summary> public Model.JC.Jc_code " + tt.ObjectGetSetMethodName + @" { get { return " + tt.ObjectName + @"; } set { " + tt.ObjectName + @" = value; } } "); } else if (tt.ColumnType == oracleDBHelper.TempTabel.字段外键类型.表外键类型) { sb.Append(@" /// <summary> /// " + tt.Column.ColumnComments + @"对象 /// </summary> private Model." + Common.getNameSpace(tt.RelationTable.TableName, Common.fristCharToUpper(tt.RelationTable.TableName.ToLower())) + " " + tt.ObjectName + @"; /// <summary> /// " + tt.Column.ColumnComments + @"对象 /// </summary> public Model." + Common.getNameSpace(tt.RelationTable.TableName, Common.fristCharToUpper(tt.RelationTable.TableName.ToLower())) + " " + tt.ObjectGetSetMethodName + @" { get { return " + tt.ObjectName + @"; } set { " + tt.ObjectName + @" = value; } } "); } else { throw new Exception("未知的字段关联类型"); } } sb.Append(@" #endregion model,实体类新增的对象,end "); #endregion #region BLL层接口 sb.Append(@" #region BLL层的interface,接口,start /// <summary> /// 根据主键,得到一条记录以及它的相关联对象 /// </summary> ///<param name=""id"">主键</param> " + paramType + @" getContactObjectById(string id); /// <summary> /// 得到所有关联对象,不带分页参数,显示根据条件得到的所有数据 /// 第一个参数是查询参数,如果查询参数为null则查询所有 /// 第二个参数是模糊查询参数,提供模糊查询功能,只适用于string类型的字段 /// 第三个参数是排序的参数,传入排序的对象的集合,如果传null,则默认使用ID字段进行排序 /// </summary> ///<param name=""" + paramName + @""">查询参数</param> ///<param name=""" + paramName + @"Like"">模糊查询参数</param> ///<param name=""sortList"">排序参数对象的集合</param> /// <returns>List<object>,索引0为数据,索引1为分页对象</returns> List<object> getListContactObject(" + paramType + " " + paramName + @"," + paramType + " " + paramName + @"Like,List<SortInfo> sortList); /// <summary> /// 得到所有关联对象,带分页参数,给分页参数指定pagesize得到第一页的数据或者不指定分页参数,系统使用默认的pagesize值 /// 第一个参数是查询参数,如果查询参数为null则查询所有 /// 第二个参数是分页参数,如果page参数为null,方法则会new一个默认的page,根据page中的pagesize参数查询出相应的条数 /// 第三个参数是模糊查询参数,提供模糊查询功能,只适用于string类型的字段 /// 第四个参数是排序的参数,传入排序的对象的集合,如果传null,则默认使用ID字段进行排序 /// </summary> ///<param name=""" + paramName + @""">查询参数</param> ///<param name=""" + paramName + @"Like"">模糊查询参数</param> ///<param name=""sortList"">排序参数对象的集合</param> /// <returns>List<object>,索引0为数据,索引1为分页对象</returns> List<object> getListContactObjectByPage(" + paramType + " " + paramName + @", Model.Common.PageInfo page," + paramType + " " + paramName + @"Like,List<SortInfo> sortList); #endregion BLL层的interface,接口,end "); #endregion #region BLL层实现 string dalVariableName = Common.fristCharToLower(paramType) + "Dal";//DAL的变量名 sb.Append(@" #region BLL层的implement,实现,start /// <summary> /// 根据主键,得到一条记录以及它的相关联对象 /// </summary> ///<param name=""id"">主键</param> public " + paramType + @" getContactObjectById(string id) { return " + dalVariableName + @".findContactObjectsById(id); } /// <summary> /// 得到所有关联对象,不带分页参数,显示根据条件得到的所有数据 /// 第一个参数是查询参数,如果查询参数为null则查询所有 /// 第二个参数是模糊查询参数,提供模糊查询功能,只适用于string类型的字段 /// 第三个参数是排序的参数,传入排序的对象的集合,如果传null,则默认使用ID字段进行排序 /// </summary> ///<param name=""" + paramName + @""">查询参数</param> ///<param name=""" + paramName + @"Like"">模糊查询参数</param> ///<param name=""sortList"">排序参数对象的集合</param> /// <returns>List<object>,索引0为数据,索引1为分页对象</returns> public List<object> getListContactObject(" + paramType + " " + paramName + @"," + paramType + " " + paramName + @"Like,List<SortInfo> sortList) { return " + dalVariableName + @".findContactObjects(" + paramName + @",null," + paramName + @"Like,sortList); } /// <summary> /// 得到所有关联对象,带分页参数,给分页参数指定pagesize得到第一页的数据或者不指定分页参数,系统使用默认的pagesize值 /// 第一个参数是查询参数,如果查询参数为null则查询所有 /// 第二个参数是分页参数,如果page参数为null,方法则会new一个默认的page,根据page中的pagesize参数查询出相应的条数 /// 第三个参数是模糊查询参数,提供模糊查询功能,只适用于string类型的字段 /// 第四个参数是排序的参数,传入排序的对象的集合,如果传null,则默认使用ID字段进行排序 /// </summary> ///<param name=""" + paramName + @""">查询参数</param> ///<param name=""" + paramName + @"Like"">模糊查询参数</param> ///<param name=""sortList"">排序参数对象的集合</param> /// <returns>List<object>,索引0为数据,索引1为分页对象</returns> public List<object> getListContactObjectByPage(" + paramType + " " + paramName + @",Model.Common.PageInfo page," + paramType + " " + paramName + @"Like,List<SortInfo> sortList) { if (null==page) { page = new Model.Common.PageInfo(); page.AllCount = " + dalVariableName + @".getCount(" + paramName + @"," + paramName + @"Like); } return " + dalVariableName + @".findContactObjects(" + paramName + @", page," + paramName + @"Like,sortList); } "); sb.Append(@" #endregion BLL层的implement,实现,end "); #endregion #region DAL层接口 sb.Append(@" #region DAL层的interface,接口,start /// <summary> /// 查找指定ID的用户,得到相关联的外键对象的方法 /// </summary> /// <remarks>如果id为null、id重复、id未找到就抛异常</remarks> /// <returns>" + paramType + @"</returns> " + paramType + @" findContactObjectsById(string id); /// <summary> /// 根据用户传入的参数查询得到相关联的外键对象方法(id参数排除在外,如需查询id请使用findById方法) /// 第一个参数是查询参数,如果查询参数为null则查询所有 /// 第二个参数是分页参数,传入带数据总行数的分页对象得到默认前PageSiz条数据(取决于page对象中的值),如果传null,则查出所有数据 /// 第二个参数提供模糊查询功能,只适用于string类型的字段 /// 第四个参数是排序的参数,传入排序的对象的集合,如果传null,则默认使用ID字段进行排序 /// </summary> /// <param name=""" + paramName + @"""></param> /// <param name=""page"">分页对象</param> ///<param name=""" + paramName + @"Like"">模糊查询参数</param> ///<param name=""sortList"">排序参数对象的集合</param> /// <returns>满足条件的所有" + paramType + @"对象集合list</returns> List<object> findContactObjects(" + paramType + " " + paramName + @", Model.Common.PageInfo page," + paramType + " " + paramName + @"Like,List<SortInfo> sortList); #endregion DAL层的interface,接口,end "); #endregion #region DAL层实现 sb.Append(@" #region DAL层的implement,实现,start /// <summary> /// 把dataset中的一行转换为model, 包括相关联的外键对象 /// </summary> /// <param name=""row""></param> /// <returns></returns> private " + paramType + @" dataSetContactObjectsToModel(DataRow row) { " + paramType + " " + paramName + @" = this.dataSetToModel(row); "); #region 循环外键表和CODE字段的dataset转为list for (int i = 0; i < ttList.Count; i++) { oracleDBHelper.TempTabel tt = ttList[i]; if (tt.ColumnType == oracleDBHelper.TempTabel.字段外键类型.表外键类型) { string fkClassName = Common.fristCharToUpper(tt.RelationTable.TableName.ToLower()); string startEndString = Common.getStartEndString(tt.RelationTable.TableName.ToUpper()); oracleDBHelper.DBTable fkTable = tt.RelationTable; sb.Append(@" #region 添加" + fkClassName + @"类别实体对象 " + fkClassName + @" " + tt.ObjectName + @" = new " + fkClassName + @"(); "); for (int j = 0; j < fkTable.Columns.Count; j++)//处理字段类型 { oracleDBHelper.DBColumn fkColumn = fkTable.Columns[j]; string fkColumnGetSet = Common.fristCharToUpper(fkColumn.ColumnName); if (fkColumn.ColumnCSharpType == "string")//如果此字段的类型为string,使用Convert.toString().tirm() { sb.Append("" + tt.ObjectName + "." + fkColumnGetSet + @" = Convert.ToString(row[""" + startEndString + @"_" + fkColumn.ColumnName.ToUpper() + @"""]).Trim(); "); } else if (fkColumn.ColumnIsNull == "y") //如果此字段可以为空,就使用convertDBNullValue转换 { sb.Append("" + tt.ObjectName + "." + fkColumnGetSet + @" = (" + fkColumn.ColumnCSharpType + @"?)this.convertDBNullValue(row[""" + startEndString + @"_" + fkColumn.ColumnName.ToUpper() + @"""]); "); //类型转换的判断 } else//默认的 { sb.Append("" + tt.ObjectName + "." + fkColumnGetSet + @" = (" + fkColumn.ColumnCSharpType + @")row[""" + startEndString + @"_" + fkColumn.ColumnName.ToUpper() + @"""]; ");//类型转换的判断 } } sb.Append(@" " + paramName + "." + tt.ObjectGetSetMethodName + @" = " + tt.ObjectName + @"; #endregion 添加" + fkClassName + @"类别实体对象 "); } else if (tt.ColumnType == oracleDBHelper.TempTabel.字段外键类型.代码类型) { sb.Append(@" //添加代码类别实体对象," + tt.Column.ColumnComments + @"对象 Jc_code " + tt.ObjectName + @" = new Jc_code(); " + tt.ObjectName + @".Id = Convert.ToString(row[""" + tt.ColumnName + @"_ID""]).Trim(); " + tt.ObjectName + @".Name = Convert.ToString(row[""" + tt.ColumnName + @"_NAME""]).Trim(); " + tt.ObjectName + @".Value = Convert.ToString(row[""" + tt.ColumnName + @"_VALUE""]).Trim(); " + tt.ObjectName + @".Fk_code_type = Convert.ToString(row[""" + tt.ColumnName + @"_FKCODETYPE""]).Trim(); " + tt.ObjectName + @".Zfbz = Convert.ToString(row[""" + tt.ColumnName + @"_ZFBZ""]).Trim(); " + tt.ObjectName + @".Remark = Convert.ToString(row[""" + tt.ColumnName + @"_REMARK""]).Trim(); " + paramName + "." + Common.fristCharToUpper(tt.ObjectName) + @" = " + tt.ObjectName + @"; "); } else { throw new Exception("未知的字段关联类型"); } } #endregion 循环外键表和CODE字段的dataset转为list sb.Append(@" return " + paramName + @"; } "); sb.Append(@" /// <summary> /// 查找指定ID的用户,得到相关联的外键对象的方法 /// </summary> /// <remarks>如果id为null、id重复、id未找到就抛异常</remarks> /// <returns>" + paramType + @"</returns> public " + paramType + @" findContactObjectsById(string id) { if (""""==id||null==id) { throw new Exception(""主键ID不能为空,请赋值""); } StringBuilder sql = new StringBuilder(""SELECT A.* ""); //新建方法findContactObjectsById,在此加入多表连接要显示的项start //sql.Append("",B.字段一,B.字段二,B.字段三""); "); #region 循环添加外键关联显示的字段 for (int i = 0; i < ttList.Count; i++) { oracleDBHelper.TempTabel tt = ttList[i]; if (tt.ColumnType == oracleDBHelper.TempTabel.字段外键类型.代码类型) { sb.Append(@" sql.Append(@"" ," + tt.TempTableName + ".ID AS " + tt.ColumnName + "_ID," + tt.TempTableName + ".NAME AS " + tt.ColumnName + "_NAME," + tt.TempTableName + ".VALUE AS " + tt.ColumnName + "_VALUE," + tt.TempTableName + ".FK_CODE_TYPE AS " + tt.ColumnName + "_FKCODETYPE," + tt.TempTableName + ".ZFBZ AS " + tt.ColumnName + "_ZFBZ," + tt.TempTableName + ".REMARK AS " + tt.ColumnName + @"_REMARK ""); "); } else if (tt.ColumnType == oracleDBHelper.TempTabel.字段外键类型.表外键类型) { string startEndString = Common.getStartEndString(tt.RelationTable.TableName.ToUpper()); sb.Append(@" sql.Append(@"""); oracleDBHelper.DBTable fkTale = tt.RelationTable; for (int j = 0; j < fkTale.Columns.Count; j++) { oracleDBHelper.DBColumn fkColumn = fkTale.Columns[j]; sb.Append(@" ,TT_" + tt.RelationTable.TableName + "." + fkColumn.ColumnName.ToUpper() + " AS " + startEndString + "_" + fkColumn.ColumnName.ToUpper());//SQL语句,全部字母用大写 } sb.Append(@" ""); "); } else { throw new Exception("未知的字段关联类型"); } } #endregion sb.Append(@" //新建方法,在此加入多表连接要显示的项end sql.Append("" FROM " + tableName + @" A ""); //新建一个方法,在此加入多表连接的语句start //sql.Append(""LEFT JOIN 外键表名 B ON A.外键=B.ID""); "); #region 循环添加外键的连接语句 for (int i = 0; i < ttList.Count; i++) { oracleDBHelper.TempTabel tt = ttList[i]; if (tt.ColumnType == oracleDBHelper.TempTabel.字段外键类型.代码类型) { sb.Append(@" sql.Append(@"" LEFT JOIN (SELECT X.* FROM JC_CODE X WHERE X.FK_CODE_TYPE IN (SELECT Y.ID FROM JC_CODE_TYPE Y WHERE Y.VALUE='"" + Model.Common.CommonCode.代码类型值." + tt.ColumnCodeTypeName + @" + @""')) " + tt.TempTableName + @" ON A." + tt.ColumnName + @" = " + tt.TempTableName + @".VALUE ""); "); } else if (tt.ColumnType == oracleDBHelper.TempTabel.字段外键类型.表外键类型) { sb.Append(@" sql.Append(@"" LEFT JOIN " + tt.RelationTable.TableName.ToUpper() + " TT_" + tt.RelationTable.TableName + @" ON A." + tt.ColumnName + " = TT_" + tt.RelationTable.TableName.ToUpper() + @".ID "); sb.Append(@" ""); "); } else { throw new Exception("未知的字段关联类型"); } } #endregion sb.Append(@" //新建一个方法,在此加入多表连接的语句end sql.Append("" WHERE A.ID=:id ""); OracleParameter pId = new OracleParameter("":id"", OracleDbType.Varchar2); pId.Value = id; DataSet ds = OracleHelper.getDataSet(sql.ToString(), pId); DataRowCollection rows = ds.Tables[0].Rows; if (rows.Count > 1) { throw new Exception(""此ID的数据有多个,主键不允许重复,请检查数据""); } if (rows.Count == 0) { throw new Exception(""此ID的数据不存在,请检查数据""); } return this.dataSetContactObjectsToModel(rows[0]); } /// <summary> /// 根据用户传入的参数查询(id参数排除在外,如需查询id请使用findById方法) /// 第一个参数是查询参数,如果查询参数为null则查询所有 /// 第二个参数是分页参数,传入带数据总行数的分页对象得到默认前PageSiz条数据(取决于page对象中的值),如果传null,则查出所有数据 /// 第三个参数提供模糊查询功能,只适用于string类型的字段 /// 第四个参数是排序的参数,传入排序的对象的集合,如果传null,则默认使用ID字段进行排序 /// </summary> /// <param name=""" + paramName + @"""></param> /// <param name=""page"">分页对象</param> ///<param name=""" + paramName + @"Like"">模糊查询参数</param> ///<param name=""sortList"">排序参数对象的集合</param> /// <returns>满足条件的所有" + paramType + @"对象集合list</returns> public List<object> findContactObjects(" + paramType + " " + paramName + @", Model.Common.PageInfo page," + paramType + " " + paramName + @"Like,List<SortInfo> sortList) { //如果没有传查询条件参数,就new一个空的" + paramType + @"对象,标示查询所有数据 if (null == " + paramName + @") { " + paramName + " = new " + paramType + @"(); } if (null!=" + paramName + @".Id&&""""!=" + paramName + @".Id) { throw new Exception(""此方法不允许指定参数ID,如果需要根据ID查询数据,请调用findById方法;错误出自:find""); } List<" + paramType + "> " + paramName + "List = new List<" + paramType + @">();//接收查询出的" + paramType + @"对象集合 List<OracleParameter> sqlparams = new List<OracleParameter>();//保存参数的集合 StringBuilder sql = new StringBuilder(""SELECT A.* ""); //新建方法findAllContactObjects(" + paramType + " " + paramName + @", Model.Common.PageInfo page),在此加入多表连接要显示的项start //sql.Append("",B.字段一,B.字段二,B.字段三""); "); #region 循环添加外键关联显示的字段 for (int i = 0; i < ttList.Count; i++) { oracleDBHelper.TempTabel tt = ttList[i]; if (tt.ColumnType == oracleDBHelper.TempTabel.字段外键类型.代码类型) { sb.Append(@" sql.Append(@"" ," + tt.TempTableName + ".ID AS " + tt.ColumnName + "_ID," + tt.TempTableName + ".NAME AS " + tt.ColumnName + "_NAME," + tt.TempTableName + ".VALUE AS " + tt.ColumnName + "_VALUE," + tt.TempTableName + ".FK_CODE_TYPE AS " + tt.ColumnName + "_FKCODETYPE," + tt.TempTableName + ".ZFBZ AS " + tt.ColumnName + "_ZFBZ," + tt.TempTableName + ".REMARK AS " + tt.ColumnName + @"_REMARK ""); "); } else if (tt.ColumnType == oracleDBHelper.TempTabel.字段外键类型.表外键类型) { string startEndString = Common.getStartEndString(tt.RelationTable.TableName.ToUpper()); sb.Append(@" sql.Append(@"""); oracleDBHelper.DBTable fkTale = tt.RelationTable; for (int j = 0; j < fkTale.Columns.Count; j++) { oracleDBHelper.DBColumn fkColumn = fkTale.Columns[j]; sb.Append(@" ,TT_" + tt.RelationTable.TableName + "." + fkColumn.ColumnName.ToUpper() + " AS " + startEndString + "_" + fkColumn.ColumnName.ToUpper());//SQL语句,全部字母用大写 } sb.Append(@" ""); "); } else { throw new Exception("未知的字段关联类型"); } } #endregion sb.Append(@" //新建方法,在此加入多表连接要显示的项end sql.Append("" FROM " + tableName + @" A ""); //新建一个方法,在此加入多表连接的语句start ////sql.Append("" LEFT JOIN 表名 B ON A.外键=B.ID""); "); #region 循环添加外键的连接语句 for (int i = 0; i < ttList.Count; i++) { oracleDBHelper.TempTabel tt = ttList[i]; if (tt.ColumnType == oracleDBHelper.TempTabel.字段外键类型.代码类型) { sb.Append(@" sql.Append(@"" LEFT JOIN (SELECT X.* FROM JC_CODE X WHERE X.FK_CODE_TYPE IN (SELECT Y.ID FROM JC_CODE_TYPE Y WHERE Y.VALUE='"" + Model.Common.CommonCode.代码类型值." + tt.ColumnCodeTypeName + @" + @""')) " + tt.TempTableName + @" ON A." + tt.ColumnName + @" = " + tt.TempTableName + @".VALUE ""); "); } else if (tt.ColumnType == oracleDBHelper.TempTabel.字段外键类型.表外键类型) { sb.Append(@" sql.Append(@"" LEFT JOIN " + tt.RelationTable.TableName.ToUpper() + " TT_" + tt.RelationTable.TableName + @" ON A." + tt.ColumnName + " = TT_" + tt.RelationTable.TableName.ToUpper() + @".ID "); sb.Append(@" ""); "); } else { throw new Exception("未知的字段关联类型"); } } #endregion sb.Append(@" //新建一个方法,在此加入多表连接的语句end "); #region 循环主表的条件判断添加 for (int i = 1; i < thisTableColumns.Count; i++) { if (thisTableColumns[i].ColumnCSharpType == "DateTime") { sb.Append(@" if (DateTime.MinValue != " + paramName + "." + Common.fristCharToUpper(thisTableColumns[i].ColumnName) + " &&null != " + paramName + "." + Common.fristCharToUpper(thisTableColumns[i].ColumnName) + @") { this.isAnd(sql); this.isWhere(sql); sql.Append("" A." + thisTableColumns[i].ColumnName.ToUpper() + "=:" + thisTableColumns[i].ColumnName + @" ""); OracleParameter p" + Common.fristCharToUpper(thisTableColumns[i].ColumnName) + @" = new OracleParameter("":" + thisTableColumns[i].ColumnName + @""", OracleDbType." + thisTableColumns[i].ColumnODACParamType + @");"); sb.Append(@" p" + Common.fristCharToUpper(thisTableColumns[i].ColumnName) + @".Value = " + paramName + "." + Common.fristCharToUpper(thisTableColumns[i].ColumnName) + @";"); sb.Append(@" sqlparams.Add(p" + Common.fristCharToUpper(thisTableColumns[i].ColumnName) + @"); } "); } else if (thisTableColumns[i].ColumnCSharpType == "decimal") { sb.Append(@" if (null != " + paramName + "." + Common.fristCharToUpper(thisTableColumns[i].ColumnName) + @") { this.isAnd(sql); this.isWhere(sql); sql.Append("" " + thisTableColumns[i].ColumnName.ToUpper() + "=:" + thisTableColumns[i].ColumnName + @" ""); OracleParameter p" + Common.fristCharToUpper(thisTableColumns[i].ColumnName) + @" = new OracleParameter("":" + thisTableColumns[i].ColumnName + @""", OracleDbType." + thisTableColumns[i].ColumnODACParamType + @");"); sb.Append(@" p" + Common.fristCharToUpper(thisTableColumns[i].ColumnName) + @".Value = " + paramName + "." + Common.fristCharToUpper(thisTableColumns[i].ColumnName) + @";"); sb.Append(@" sqlparams.Add(p" + Common.fristCharToUpper(thisTableColumns[i].ColumnName) + @"); } "); } else { sb.Append(@" if (null != " + paramName + "." + Common.fristCharToUpper(thisTableColumns[i].ColumnName) + @" && """" != " + paramName + "." + Common.fristCharToUpper(thisTableColumns[i].ColumnName) + @") { this.isAnd(sql); this.isWhere(sql); sql.Append("" A." + thisTableColumns[i].ColumnName.ToUpper() + "=:" + thisTableColumns[i].ColumnName + @" ""); OracleParameter p" + Common.fristCharToUpper(thisTableColumns[i].ColumnName) + @" = new OracleParameter("":" + thisTableColumns[i].ColumnName + @""", OracleDbType." + thisTableColumns[i].ColumnODACParamType + @");"); sb.Append(@" p" + Common.fristCharToUpper(thisTableColumns[i].ColumnName) + @".Value = " + paramName + "." + Common.fristCharToUpper(thisTableColumns[i].ColumnName) + @";"); sb.Append(@" sqlparams.Add(p" + Common.fristCharToUpper(thisTableColumns[i].ColumnName) + @"); } "); } } #endregion //循环本表的条件判断 string paramNameLike = paramName + "Like"; //xxParamLike sb.Append(@" if(" + paramNameLike + @"!=null) { "); #region 循环添加主表的模糊查询的条件参数 for (int i = 1; i < thisTableColumns.Count; i++) { if (thisTableColumns[i].ColumnCSharpType == "string") { sb.Append(@" if (null != " + paramNameLike + "." + Common.fristCharToUpper(thisTableColumns[i].ColumnName) + @" && """" != " + paramNameLike + "." + Common.fristCharToUpper(thisTableColumns[i].ColumnName) + @") { this.isAnd(sql); this.isWhere(sql); sql.Append("" A." + thisTableColumns[i].ColumnName.ToUpper() + " LIKE '%'||:" + thisTableColumns[i].ColumnName + @"||'%' ""); OracleParameter p" + Common.fristCharToUpper(thisTableColumns[i].ColumnName) + @" = new OracleParameter("":" + thisTableColumns[i].ColumnName + @""", OracleDbType." + thisTableColumns[i].ColumnODACParamType + @");"); sb.Append(@" p" + Common.fristCharToUpper(thisTableColumns[i].ColumnName) + @".Direction = ParameterDirection.Input;"); sb.Append(@" p" + Common.fristCharToUpper(thisTableColumns[i].ColumnName) + @".Value = " + paramNameLike + "." + Common.fristCharToUpper(thisTableColumns[i].ColumnName) + @";"); sb.Append(@" sqlparams.Add(p" + Common.fristCharToUpper(thisTableColumns[i].ColumnName) + @"); } "); } } #endregion sb.Append(@" } "); #region 循环外键表的条件判断 for (int i = 0; i < ttList.Count; i++) { oracleDBHelper.TempTabel tt = ttList[i]; if (tt.ColumnType == oracleDBHelper.TempTabel.字段外键类型.表外键类型) { oracleDBHelper.DBTable fkTable = tt.RelationTable; string fkTableObjectName = paramName + "." + tt.ObjectGetSetMethodName;//外键表对象的完全限定名 sb.Append(@" if(null!=" + fkTableObjectName + @") { "); for (int j = 0; j < fkTable.Columns.Count; j++) { oracleDBHelper.DBColumn fkColumn = fkTable.Columns[j]; string fkColumnGetSet = Common.fristCharToUpper(fkColumn.ColumnName.ToLower()); string columnObjectName = paramName + "." + tt.ObjectGetSetMethodName + "." + fkColumnGetSet;//字段的完全限定名 if (fkColumn.ColumnCSharpType == "DateTime") { sb.Append(@" if (DateTime.MinValue != " + columnObjectName + " &&null != " + columnObjectName + @") { this.isAnd(sql); this.isWhere(sql); sql.Append("" TT_" + tt.RelationTable.TableName.ToUpper() + "." + fkColumn.ColumnName.ToUpper() + "=:" + fkColumn.ColumnName + @" ""); OracleParameter p" + Common.fristCharToUpper(fkColumn.ColumnName) + @" = new OracleParameter("":" + fkColumn.ColumnName + @""", OracleDbType." + fkColumn.ColumnODACParamType + @");"); sb.Append(@" p" + Common.fristCharToUpper(fkColumn.ColumnName) + @".Value = " + columnObjectName + @";"); sb.Append(@" sqlparams.Add(p" + Common.fristCharToUpper(fkColumn.ColumnName) + @"); } "); } else if (fkColumn.ColumnCSharpType == "decimal") { sb.Append(@" if (null != " + columnObjectName + @") { this.isComma(sql); sql.Append("" TT_" + tt.RelationTable.TableName.ToUpper() + "." + fkColumn.ColumnName.ToUpper() + "=:" + fkColumn.ColumnName + @" ""); OracleParameter p" + Common.fristCharToUpper(fkColumn.ColumnName) + @" = new OracleParameter("":" + fkColumn.ColumnName + @""", OracleDbType." + fkColumn.ColumnODACParamType + @");"); sb.Append(@" p" + Common.fristCharToUpper(fkColumn.ColumnName) + @".Value = " + columnObjectName + @";"); sb.Append(@" sqlparams.Add(p" + Common.fristCharToUpper(fkColumn.ColumnName) + @"); } "); } else { sb.Append(@" if (null != " + columnObjectName + " &&null != " + columnObjectName + @") { this.isAnd(sql); this.isWhere(sql); sql.Append("" TT_" + tt.RelationTable.TableName.ToUpper() + "." + fkColumn.ColumnName.ToUpper() + "=:" + fkColumn.ColumnName + @" ""); OracleParameter p" + Common.fristCharToUpper(fkColumn.ColumnName) + @" = new OracleParameter("":" + fkColumn.ColumnName + @""", OracleDbType." + fkColumn.ColumnODACParamType + @");"); sb.Append(@" p" + Common.fristCharToUpper(fkColumn.ColumnName) + @".Value = " + columnObjectName + @";"); sb.Append(@" sqlparams.Add(p" + Common.fristCharToUpper(fkColumn.ColumnName) + @"); } "); } } sb.Append(@" } "); } } #endregion // sb.Append(@" // if(" + paramNameLike + @"!=null) // { //"); // #region 循环添加外键表的模糊查询的条件参数 // for (int i = 0; i < ttList.Count; i++) // { // oracleDBHelper.TempTabel tt = ttList[i]; // if (tt.ColumnType == oracleDBHelper.TempTabel.字段外键类型.表外键类型) // { // oracleDBHelper.DBTable fkTable = tt.RelationTable; // string fkTableObjectName = paramName + "." + tt.ObjectGetSetMethodName;//外键表对象的完全限定名 // sb.Append(@" // if(null!=" + fkTableObjectName + @") // { //"); // for (int j = 0; j < fkTable.Columns.Count; j++) // { // oracleDBHelper.DBColumn fkColumn = fkTable.Columns[j]; // string fkColumnGetSet = Common.fristCharToUpper(fkColumn.ColumnName.ToLower()); // string columnObjectName = paramName + "." + tt.ObjectGetSetMethodName + "." + fkColumnGetSet;//字段的完全限定名 // if(fkColumn.ColumnCSharpType=="string") // { // sb.Append(@" // if (null != " + columnObjectName + " &&null != " + columnObjectName + @") // { // this.isAnd(sql); // this.isWhere(sql); // sql.Append("" TT_" + tt.RelationTable.TableName.ToUpper() + "." + fkColumn.ColumnName.ToUpper() + " LIKE '%'||:" + fkColumn.ColumnName + @"||'%' ""); // OracleParameter p" + Common.fristCharToUpper(fkColumn.ColumnName) + @" = new OracleParameter("":" + fkColumn.ColumnName + @""", OracleDbType." + fkColumn.ColumnODACParamType + @");"); // sb.Append(@" // p" + Common.fristCharToUpper(fkColumn.ColumnName) + @".Value = " + columnObjectName + @";"); // sb.Append(@" // sqlparams.Add(p" + Common.fristCharToUpper(fkColumn.ColumnName) + @"); // } // "); // } // } // sb.Append(@" // } //"); // } // } // #endregion // sb.Append(@" // } //"); sb.Append(@" this.b_isWhere = false; //排序 this.addSort(sql, sortList); //*******分页start1*********如果分页对象为空,则全部数据查出,如果不为空 List<object> returnList = new List<object>();//返回的List,索引0为List<" + paramType + @">对象,索引1为Model.Common.PageInfo分页信息对象 if (null != page) { string sqlParam = sql.ToString(); sql = new StringBuilder(@"" SELECT * FROM ( SELECT A.*, ROWNUM RN FROM (""+sqlParam+@"") A WHERE ROWNUM <= :maxIndex ) WHERE RN >= :minIndex ""); sqlparams.Add(new OracleParameter(""maxIndex"", page.MaxIndex)); sqlparams.Add(new OracleParameter(""minIndex"", page.MinIndex)); } //*******分页end1********* DataSet ds = OracleHelper.getDataSet(sql.ToString(), sqlparams.ToArray()); DataRowCollection rows = ds.Tables[0].Rows; //把每一行数据转换为一个对象放入List<" + paramType + @">中 for (int i = 0; i < rows.Count; i++) { DataRow row = rows[i]; " + paramName + @"List.Add(this.dataSetContactObjectsToModel(row)); //" + paramName + @"List.Add(this.dataSetToModel(row)); } //*******分页start2*********如果没有传入分页对象,则新建一个分页对象当做返回值 if (null == page) { page = new Model.Common.PageInfo(); page.AllCount = rows.Count; } returnList.Add(" + paramName + "List);//把List<" + paramType + @">放入List<object>中第一个 returnList.Add(page);//把page对象放入List<object>中第二个 //*******分页end2********* return returnList; } #endregion DAL层的implement,实现,end "); #endregion return(sb.ToString()); }
/// <summary> /// oracle生成model代码 /// </summary> /// <param Name="dataBaseType">数据库类型</param> /// <param Name="TableName">表名</param> /// <param Name="className">类名</param> /// <param Name="classRemark">类说明</param> /// <returns></returns> public static string generatorOracleModel(string dataBaseType, oracleDBHelper.DBTable table, string className, string classRemark, string lastNameSpace) { List <oracleDBHelper.DBColumn> columns = table.Columns; string tableName = table.TableName; StringBuilder sb = new StringBuilder(); sb.Append(@" using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace Model." + Common.getNameSpace(tableName, null) + @" { /// <summary> ///" + dataBaseType + @"数据库 ///" + tableName + @"表的实体类 ///" + lastNameSpace + @" ///此类由代码生成器生成 ///生成日期:" + DateTime.Now + @" /// " + classRemark + @" " + Common.myRemark + @" /// </summary> public class " + className + @":Base.IBaseModel { /// <summary> /// 表备注 /// </summary> public static readonly string 表备注 = """ + table.TableComment + @"""; /// <summary> /// 数据库表名 /// </summary> public static readonly string TABEL_NAME = """ + tableName.ToUpper() + @"""; "); #region 循环添加静态字段 for (int i = 0; i < columns.Count; i++) { oracleDBHelper.DBColumn column = new oracleDBHelper.DBColumn(); column = columns[i]; sb.Append(@" /// <summary> /// " + column.ColumnComments + @"(数据库字段名) /// </summary> public static readonly string " + column.ColumnName.ToUpper() + @" = """ + column.ColumnName.ToUpper() + @""";"); } #endregion #region object转本model的方法 sb.Append(@" /// <summary> /// 把object对象转换为" + className + @"对象 /// </summary> /// <param name=""obj""></param> /// <returns></returns> public static Model." + Common.getNameSpace(tableName, null) + @"." + className + " ConvertTo" + className + @"(object obj) { if (null==obj) { return new Model." + Common.getNameSpace(tableName, null) + @"." + className + @"(); } return (Model." + Common.getNameSpace(tableName, null) + @"." + className + @")obj; } "); #endregion #region 循环添加属性的方法 for (int i = 0; i < columns.Count; i++) { oracleDBHelper.DBColumn column = new oracleDBHelper.DBColumn(); column = columns[i]; sb.Append(@" /// <summary> /// " + column.ColumnComments + @"(对象属性名) /// </summary> private " + Common.getColumnIsNull(column) + @" " + column.ColumnName + @"; /// <summary> /// " + column.ColumnComments + @"(对象属性名) /// </summary> public " + Common.getColumnIsNull(column) + @" " + Common.fristCharToUpper(column.ColumnName) + @" { get { return this." + column.ColumnName + @"; } set { this." + column.ColumnName + @" = value; } } "); } sb.Append(@" // //*****************在此之后添加关联对象 } } "); #endregion return(sb.ToString()); }
/// <summary> /// oracle生成DAL的implement代码 /// </summary> /// <param Name="TableName">表名称</param> /// <param Name="className">类名</param> /// <param Name="classRemark">类说明</param> /// <param Name="classRemark">参数类型</param> /// <param Name="paramName">参数名称</param> /// <returns>接口实现代码</returns> public static string generatorOracleDALImp(string dataBaseType, oracleDBHelper.DBTable table, string className, string classRemark, string paramType, string paramName, string lastNameSpace) { List <oracleDBHelper.DBColumn> columns = table.Columns; string tableName = table.TableName; string paramNameList = paramName + "List"; //xxParamList string paramNameCondition = paramName + "Condition"; //xxParamConditon string paramNameUpdate = paramName + "Update"; //xxParamUpdate string paramNameLike = paramName + "Like"; //xxParamLike StringBuilder sb = new StringBuilder(); sb.Append(@" using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data; using DAL.Common; using System.Transactions;//引入事务 using Oracle.DataAccess.Client;//引入oracle驱动 using Model." + Common.getNameSpace(tableName, null) + @"; using Model.Common; namespace DAL." + Common.getNameSpace(tableName, lastNameSpace) + @" { /// <summary> ///数据库访问层的" + className + @"实现类 ///此类由代码生成器生成 ///表名:" + tableName + @" ///生成日期:" + DateTime.Now + @" /// " + classRemark + @" " + Common.myRemark + @" /// </summary> public class " + className + "DALImp :Base.BasDAL," + className + @"DAL { /// <summary> /// 插入一条数据(如果需要供事务使用就传入OracleConnection对象,如果不供就传入null,方法自动生成OracleConnection对象) /// 主键为null时,自动生成主键guid,一般不直接调用此方法,请调用save方法进行单条数据的插入和修改 /// </summary> /// <param name=""" + paramName + @""">要插入的" + paramName + @"对象</param> /// <param name=""con"">Oracle连接对象(为null时自动生成)</param> /// <returns>是否添加成功(影响的行数为1时成功)</returns> public bool insert(" + paramType + " " + paramName + @",OracleConnection con) { if (""""==" + paramName + ".Id||null==" + paramName + @".Id) { " + paramName + @".Id = Model.Common.GUIDHelper.getGuid(); } List<OracleParameter> sqlparams = new List<OracleParameter>(); "); #region 循环添加insert语句中表字段名的方法(新增) sb.Append(Common.addSqlInsert(columns, tableName)); #endregion #region 循环添加条件参数的方法(新增) sb.Append(Common.addParamInsertForSingle(columns, paramName)); #endregion sb.Append(@" bool result = false; int flag = OracleHelper.ExecuteNonQuery(sql.ToString(), con, sqlparams.ToArray()); if (1 == flag) { result = true; } return result; } /// <summary> /// 插入多条数据(如果需要供事务使用就传入OracleConnection对象,如果不供就传入null,方法自动生成OracleConnection对象,并且自动使用事务) /// 主键为null时,自动生成主键guid /// </summary> /// <param name=""" + paramNameList + @""">要插入的" + paramName + @"对象的集合</param> /// <param name=""con"">Oracle连接对象(为null时自动生成)</param> /// <returns>是否添加成功(影响的行数等于保存的对象的数量时成功)</returns> public bool inertBatch(List<" + paramType + @"> " + paramNameList + @", OracleConnection con) { if (" + paramNameList + @".Count == 0) { return true; } "); #region 循环,声明参数的数组变量 sb.Append(Common.addStatementParamArray(columns)); #endregion #region 循环给参数的数组赋值 sb.Append(@" for (int i = 0; i < " + paramNameList + @".Count; i++) { " + paramType + " " + paramName + @" = " + paramNameList + @"[i]; if ("""" == " + paramName + @".Id || null == " + paramName + @".Id) { " + paramName + @".Id = Model.Common.GUIDHelper.getGuid(); } "); sb.Append(Common.addParamArrayAssignment(columns, paramName)); #endregion sb.Append(@" } List<OracleParameter> sqlparams = new List<OracleParameter>(); "); #region 循环添加insert语句中表字段名的方法(新增) sb.Append(Common.addSqlInsert(columns, tableName)); #endregion #region 循环添加条件参数的方法(新增) sb.Append(Common.addParamInsertForArray(columns, paramNameList)); #endregion sb.Append(@" bool result = false; int flag = -1; flag = OracleHelper.ExecuteBatch(sql.ToString(), con, " + paramNameList + @".Count, sqlparams.ToArray()); if (" + paramNameList + @".Count == flag) { result = true; } return result; } /// <summary> /// 删除指定ID的一条数据(如果需要供事务使用就传入OracleConnection对象,如果不供就传入null,方法自动生成OracleConnection对象) /// </summary> /// <param Name=""id"">要删除的数据的guid</param> /// <param Name=""con"">Oracle连接对象(为null时自动生成)</param> /// <returns>是否删除成功(影响的行数为1时成功)</returns> public bool deleteById(string id,OracleConnection con) { if ("""" == id || null == id) { throw new Exception(""ID不允许为空,请赋值后调用;错误出自:deleteById""); } StringBuilder sql = new StringBuilder(""DELETE " + tableName + @" WHERE ID=:id""); OracleParameter pId = new OracleParameter("":id"", OracleDbType.Varchar2); pId.Value = id; bool result = false; int flag = OracleHelper.ExecuteNonQuery(sql.ToString(), con, pId); if (1 == flag) { result = true; } else if (0 == flag) { throw new Exception(""删除了"" + flag + ""条数据,请检查ID是否在数据库中存在;错误出自:deleteById""); } else if (flag > 1) { throw new Exception(""删除了"" + flag + ""条数据,数据库中主键重复;错误出自:deleteById""); } return result; } /// <summary> /// 删除指定id集合的多条数据(如果需要供事务使用就传入OracleConnection对象,如果不供就传入null,方法自动生成OracleConnection对象,并且自动使用事务) /// </summary> /// <param name=""idlist"">要删除的数据的guid的集合</param> /// <param name=""con"">Oracle连接对象(为null时自动生成)</param> /// <returns>是否删除成功(影响的行数等于要删除的id的数量时成功)</returns> public bool deleteByIdBatch(List<string> idList, OracleConnection con) { if (null==idList||0==idList.Count) { throw new Exception(""ID的集合不允许为空或者数量为0,请赋值后调用;错误出自:deleteByIdBatch""); } StringBuilder sql = new StringBuilder(""DELETE " + tableName + @" WHERE ID=:id""); OracleParameter pId = new OracleParameter("":id"", OracleDbType.Varchar2); pId.Value = idList.ToArray(); bool result = false; int flag = OracleHelper.ExecuteBatch(sql.ToString(), con,idList.Count, pId); if (idList.Count == flag) { result = true; } return result; } /// <summary> /// 根据删除条件,删除多条数据(如果需要供事务使用就传入OracleConnection对象,如果不供就传入null,方法自动生成OracleConnection对象) /// 不指定ID,如果需要根据ID来删除数据,请调用deleteById或deleteByIdBatch方法 /// </summary> /// <param name=""" + paramNameCondition + @""">删除的条件</param> /// <param name=""con"">Oracle连接对象(为null时自动生成)</param> /// <returns>无法确定删除的行数(影响行数大于等于0就算成功)</returns> public bool deleteByOtherCondition(" + paramType + @" " + paramNameCondition + @", OracleConnection con) { if (null!=" + paramNameCondition + @".Id&&""""!=" + paramNameCondition + @".Id) { throw new Exception(""不允许指定参数的ID,如果要根据ID删除数据请调用deleteById或者deleteByIdBatch方法;错误出自:deleteByOtherCondition""); } List<OracleParameter> sqlparams = new List<OracleParameter>(); StringBuilder sql = new StringBuilder(""DELETE " + tableName + @" ""); "); #region 循环添加删除的条件 sb.Append(Common.addWhereParamAssignmentForSingle(columns, paramNameCondition)); #endregion sb.Append(@" this.b_isWhere = false; bool result = false; int flag = OracleHelper.ExecuteNonQuery(sql.ToString(), con, sqlparams.ToArray()); if (flag>=0) { result = true; } return result; } /// <summary> /// 修改指定ID的一条数据(自动生成OracleConnection对象,直接修改) /// 一般不直接调用此方法,请调用save方法进行插入和修改 /// </summary> /// <remarks>ID为空抛出异常</remarks> /// <param name=""" + paramName + @"""></param> /// <returns>影响的行数</returns> public bool updateById(" + paramType + " " + paramName + @",OracleConnection con) { if ("""" == " + paramName + @".Id || null == " + paramName + @".Id) { throw new Exception(""ID不允许为空,请赋值后调用;错误出自:updateById""); } List<OracleParameter> sqlparams = new List<OracleParameter>(); "); #region 循环添加条件参数(更新) sb.Append(Common.addSqlUpdateForSingle(columns, tableName, paramName)); #endregion sb.Append(@" sql.Append("" WHERE ID=:id ""); OracleParameter pId = new OracleParameter("":id"", OracleDbType.Varchar2); pId.Value = " + paramName + @".Id; sqlparams.Add(pId); this.b_isComma = false; bool result = false; int flag = OracleHelper.ExecuteNonQuery(sql.ToString(), con, sqlparams.ToArray()); if (1 == flag) { result = true; } else if (0 == flag) { throw new Exception(""更新"" + flag + ""条数据,请检查ID是否在数据库中存在;错误出自:updateById""); } else if (flag > 1) { throw new Exception(""更新了"" + flag + ""条数据,数据库中主键重复;错误出自:updateById""); } return result; } /// <summary> /// 根据List中的ID修改多条数据(如果需要供事务使用就传入OracleConnection对象,如果不供就传入null,方法自动生成OracleConnection对象,并且自动使用事务) /// </summary> /// <param name=""" + paramNameList + @""">要修改的" + paramType + @"对象集合</param> /// <param name=""con"">Oracle连接对象(为null时自动生成)</param> /// <returns>是否删除成功(影响的行数等于要修改的对象数量时成功)</returns> public bool updateByIdBatch(List<" + paramType + @"> " + paramNameList + @", OracleConnection con) { "); #region 循环,声明参数的数组变量 sb.Append(Common.addStatementParamArray(columns)); #endregion #region 循环给参数的数组赋值 sb.Append(@" for (int i = 0; i < " + paramNameList + @".Count; i++) { " + paramType + " " + paramName + @" = " + paramNameList + @"[i]; if ("""" == " + paramName + @".Id || null == " + paramName + @".Id) { throw new Exception(""下标为"" + i + ""的数据,ID不能为空,请赋值后调用;错误出自:updateByIdBatch""); } "); sb.Append(Common.addParamArrayAssignment(columns, paramName)); #endregion sb.Append(@" } List<OracleParameter> sqlparams = new List<OracleParameter>(); "); #region 循环添加条件参数(更新) sb.Append(Common.addSqlUpdateForArray(columns, tableName, paramName)); #endregion sb.Append(@" sql.Append("" WHERE ID=:id ""); OracleParameter pId = new OracleParameter("":id"", OracleDbType.Varchar2); pId.Value = idList.ToArray(); sqlparams.Add(pId); this.b_isComma = false; bool result = false; int flag = OracleHelper.ExecuteBatch(sql.ToString(), con," + paramNameList + @".Count, sqlparams.ToArray()); if (" + paramNameList + @".Count == flag) { result = true; } return result; } /// <summary> /// 根据更新的条件,更新数据(如果需要供事务使用就传入OracleConnection对象,如果不供就传入null,方法自动生成OracleConnection对象) /// 不指定ID,如果要根据ID来更新数据,请使用updateForId方法 /// </summary> /// <param name=""" + paramNameCondition + @""">条件对象</param> /// <param name=""" + paramNameUpdate + @""">要更新的数据对象</param> /// <param name=""con"">Oracle连接对象(为null时自动生成)</param> /// <returns>影响的行数不确定(不为-1就正确)</returns> public bool updateByOtherCondition(" + paramType + @" " + paramNameCondition + @", " + paramType + @" " + paramNameUpdate + @", OracleConnection con) { if ((null!=" + paramNameCondition + @".Id&&""!=" + paramNameCondition + @".Id)||(null!=" + paramNameUpdate + @".Id&&""!=" + paramNameUpdate + @".Id)) { throw new Exception(""此方法不允许指定参数的ID,数据库中的ID不允许修改,如需根据ID修改数据,请调用updateById或updateByIdBatch方法;错误出自:updateByOtherCondition""); } List<OracleParameter> sqlparams = new List<OracleParameter>(); //添加要修改的值 StringBuilder sql = new StringBuilder(""UPDATE " + tableName + @" SET "");"); for (int i = 1; i < columns.Count; i++) { if (columns[i].ColumnCSharpType == "DateTime") { sb.Append(@" if (DateTime.MinValue != " + paramNameUpdate + @"." + Common.fristCharToUpper(columns[i].ColumnName) + " &&null != " + paramNameUpdate + @"." + Common.fristCharToUpper(columns[i].ColumnName) + @") { this.isComma(sql); sql.Append("" " + columns[i].ColumnName.ToUpper() + "=:" + columns[i].ColumnName + @" ""); OracleParameter p" + Common.fristCharToUpper(columns[i].ColumnName) + @" = new OracleParameter("":" + columns[i].ColumnName + @""", OracleDbType." + columns[i].ColumnODACParamType + @");"); sb.Append(@" p" + Common.fristCharToUpper(columns[i].ColumnName) + @".Direction = ParameterDirection.Input;"); sb.Append(@" p" + Common.fristCharToUpper(columns[i].ColumnName) + @".Value = " + paramNameUpdate + @"." + Common.fristCharToUpper(columns[i].ColumnName) + @";"); sb.Append(@" sqlparams.Add(p" + Common.fristCharToUpper(columns[i].ColumnName) + @"); } "); } else if (columns[i].ColumnCSharpType == "decimal") { sb.Append(@" if (null != " + paramNameUpdate + "." + Common.fristCharToUpper(columns[i].ColumnName) + @") { this.isComma(sql); sql.Append("" " + columns[i].ColumnName.ToUpper() + "=:" + columns[i].ColumnName + @" ""); OracleParameter p" + Common.fristCharToUpper(columns[i].ColumnName) + @" = new OracleParameter("":" + columns[i].ColumnName + @""", OracleDbType." + columns[i].ColumnODACParamType + @");"); sb.Append(@" p" + Common.fristCharToUpper(columns[i].ColumnName) + @".Direction = ParameterDirection.Input;"); sb.Append(@" p" + Common.fristCharToUpper(columns[i].ColumnName) + @".Value = " + paramNameUpdate + "." + Common.fristCharToUpper(columns[i].ColumnName) + @";"); sb.Append(@" sqlparams.Add(p" + Common.fristCharToUpper(columns[i].ColumnName) + @"); } "); } else { sb.Append(@" if (null != " + paramNameUpdate + "." + Common.fristCharToUpper(columns[i].ColumnName) + @" && """"!= " + paramNameUpdate + "." + Common.fristCharToUpper(columns[i].ColumnName) + @") { this.isComma(sql); sql.Append("" " + columns[i].ColumnName.ToUpper() + "=:" + columns[i].ColumnName + @" ""); OracleParameter p" + Common.fristCharToUpper(columns[i].ColumnName) + @" = new OracleParameter("":" + columns[i].ColumnName + @""", OracleDbType." + columns[i].ColumnODACParamType + @");"); sb.Append(@" p" + Common.fristCharToUpper(columns[i].ColumnName) + @".Direction = ParameterDirection.Input;"); sb.Append(@" p" + Common.fristCharToUpper(columns[i].ColumnName) + @".Value = " + paramNameUpdate + "." + Common.fristCharToUpper(columns[i].ColumnName) + @";"); sb.Append(@" sqlparams.Add(p" + Common.fristCharToUpper(columns[i].ColumnName) + @"); } "); } } sb.Append(@" //添加条件的参数 "); for (int i = 1; i < columns.Count; i++) { oracleDBHelper.DBColumn column = columns[i]; if (columns[i].ColumnCSharpType == "DateTime") { sb.Append(@" if (DateTime.MinValue != " + paramNameCondition + "." + Common.fristCharToUpper(columns[i].ColumnName) + " &&null != " + paramNameCondition + "." + Common.fristCharToUpper(columns[i].ColumnName) + @") { this.isAnd(sql); this.isWhere(sql); sql.Append("" " + columns[i].ColumnName.ToUpper() + "=:" + columns[i].ColumnName + @" ""); OracleParameter p" + Common.fristCharToUpper(columns[i].ColumnName) + @" = new OracleParameter("":" + columns[i].ColumnName + @""", OracleDbType." + columns[i].ColumnODACParamType + @");"); sb.Append(@" p" + Common.fristCharToUpper(columns[i].ColumnName) + @".Direction = ParameterDirection.Input;"); sb.Append(@" p" + Common.fristCharToUpper(columns[i].ColumnName) + @".Value = " + paramNameCondition + "." + Common.fristCharToUpper(columns[i].ColumnName) + @";"); sb.Append(@" sqlparams.Add(p" + Common.fristCharToUpper(columns[i].ColumnName) + @"); } "); } else if (columns[i].ColumnCSharpType == "decimal") { sb.Append(@" if (null != " + paramNameCondition + "." + Common.fristCharToUpper(columns[i].ColumnName) + @") { this.isAnd(sql); this.isWhere(sql); sql.Append("" " + columns[i].ColumnName.ToUpper() + "=:" + columns[i].ColumnName + @" ""); OracleParameter p" + Common.fristCharToUpper(columns[i].ColumnName) + @" = new OracleParameter("":" + columns[i].ColumnName + @""", OracleDbType." + columns[i].ColumnODACParamType + @");"); sb.Append(@" p" + Common.fristCharToUpper(columns[i].ColumnName) + @".Direction = ParameterDirection.Input;"); sb.Append(@" p" + Common.fristCharToUpper(columns[i].ColumnName) + @".Value = " + paramNameCondition + "." + Common.fristCharToUpper(columns[i].ColumnName) + @";"); sb.Append(@" sqlparams.Add(p" + Common.fristCharToUpper(columns[i].ColumnName) + @"); } "); } else { sb.Append(@" if (null != " + paramNameCondition + "." + Common.fristCharToUpper(columns[i].ColumnName) + @" && """"!= " + paramNameCondition + "." + Common.fristCharToUpper(columns[i].ColumnName) + @") { this.isAnd(sql); this.isWhere(sql); sql.Append("" " + columns[i].ColumnName.ToUpper() + "=:" + columns[i].ColumnName + @" ""); OracleParameter p" + Common.fristCharToUpper(columns[i].ColumnName) + @" = new OracleParameter("":" + columns[i].ColumnName + @""", OracleDbType." + columns[i].ColumnODACParamType + @");"); sb.Append(@" p" + Common.fristCharToUpper(columns[i].ColumnName) + @".Direction = ParameterDirection.Input;"); sb.Append(@" p" + Common.fristCharToUpper(columns[i].ColumnName) + @".Value = " + paramNameCondition + "." + Common.fristCharToUpper(columns[i].ColumnName) + @";"); sb.Append(@" sqlparams.Add(p" + Common.fristCharToUpper(columns[i].ColumnName) + @"); } "); } } sb.Append(@" this.b_isComma = false; this.b_isWhere = false; bool result = false; int flag = OracleHelper.ExecuteNonQuery(sql.ToString(), con, sqlparams.ToArray()); if (flag>=0) { result = true; } return result; } /// <summary> /// 查找指定ID的用户 /// </summary> /// <remarks>id为null、id重复、id未找到,抛异常</remarks> /// <returns>" + paramType + @"</returns> public " + paramType + @" findById(string id) { if (""""==id||null==id) { throw new Exception(""ID不能为空,请赋值后调用;错误出自:findById""); } StringBuilder sql = new StringBuilder(""SELECT A.* ""); //新建方法findContactObjectsById,在此加入多表连接要显示的项start //sql.Append("",B.字段一,B.字段二,B.字段三""); //新建方法,在此加入多表连接要显示的项end sql.Append("" FROM " + tableName + @" A ""); //新建一个方法,在此加入多表连接的语句start //sql.Append(""LEFT JOIN 外键表名 B ON A.外键=B.ID""); //新建一个方法,在此加入多表连接的语句end sql.Append("" WHERE A.ID=:id ""); OracleParameter pId = new OracleParameter("":id"", OracleDbType.Varchar2); pId.Direction = ParameterDirection.Input; pId.Value = id; DataSet ds = OracleHelper.getDataSet(sql.ToString(), pId); DataRowCollection rows = ds.Tables[0].Rows; if (rows.Count > 1) { throw new Exception(""此ID的数据有多个,主键不允许重复,请检查数据;错误出自:findById""); } if (rows.Count == 0) { throw new Exception(""此ID的数据不存在,请检查数据;错误出自:findById""); } return this.dataSetToModel(rows[0]); } /// <summary> /// 根据用户传入的参数查询(id参数排除在外,如需查询id请使用findById方法) /// 第一个参数是查询参数,如果查询参数为null则查询所有 /// 第二个参数是分页参数,传入带数据总行数的分页对象得到默认前PageSiz条数据(取决于page对象中的值),如果传null,则查出所有数据 /// 第三个参数提供模糊查询功能,只适用于string类型的字段 /// 第四个参数是排序的参数,传入排序的对象的集合,如果传null,则默认使用ID字段进行排序 /// </summary> /// <param name=""" + paramName + @"""></param> /// <param name=""page"">分页对象</param> ///<param name=""" + paramName + @"Like"">模糊查询参数</param> ///<param name=""sortList"">排序参数对象的集合</param> /// <returns>满足条件的所有" + paramType + @"对象集合list</returns> public List<object> find(" + paramType + " " + paramName + @", Model.Common.PageInfo page," + paramType + " " + paramNameLike + @",List<SortInfo> sortList) { //如果没有传查询条件参数,就new一个空的" + paramType + @"对象,标示查询所有数据 if (null == " + paramName + @") { " + paramName + " = new " + paramType + @"(); } if (null!=" + paramName + @".Id&&""""!=" + paramName + @".Id) { throw new Exception(""此方法不允许指定参数ID,如果需要根据ID查询数据,请调用findById方法;错误出自:find""); } List<" + paramType + "> " + paramName + "List = new List<" + paramType + @">();//接收查询出的" + paramType + @"对象集合 List<OracleParameter> sqlparams = new List<OracleParameter>();//保存参数的集合 StringBuilder sql = new StringBuilder(""SELECT A.* ""); //新建方法findContactObjects(" + paramType + " " + paramName + @", Model.Common.PageInfo page),在此加入多表连接要显示的项start //sql.Append("",B.字段一,B.字段二,B.字段三""); //新建方法,在此加入多表连接要显示的项end sql.Append("" FROM " + tableName + @" A ""); //新建一个方法,在此加入多表连接的语句start ////sql.Append("" LEFT JOIN 表名 B ON A.外键=B.ID""); //新建一个方法,在此加入多表连接的语句end "); #region 循环添加条件参数(查找) for (int i = 1; i < columns.Count; i++) { if (columns[i].ColumnCSharpType == "DateTime") { sb.Append(@" if (DateTime.MinValue != " + paramName + "." + Common.fristCharToUpper(columns[i].ColumnName) + " &&null != " + paramName + "." + Common.fristCharToUpper(columns[i].ColumnName) + @") { this.isAnd(sql); this.isWhere(sql); sql.Append("" A." + columns[i].ColumnName.ToUpper() + "=:" + columns[i].ColumnName + @" ""); OracleParameter p" + Common.fristCharToUpper(columns[i].ColumnName) + @" = new OracleParameter("":" + columns[i].ColumnName + @""", OracleDbType." + columns[i].ColumnODACParamType + @");"); sb.Append(@" p" + Common.fristCharToUpper(columns[i].ColumnName) + @".Direction = ParameterDirection.Input;"); sb.Append(@" p" + Common.fristCharToUpper(columns[i].ColumnName) + @".Value = " + paramName + "." + Common.fristCharToUpper(columns[i].ColumnName) + @";"); sb.Append(@" sqlparams.Add(p" + Common.fristCharToUpper(columns[i].ColumnName) + @"); } "); } else if (columns[i].ColumnCSharpType == "decimal") { sb.Append(@" if (null != " + paramName + "." + Common.fristCharToUpper(columns[i].ColumnName) + @") { this.isAnd(sql); this.isWhere(sql); sql.Append("" A." + columns[i].ColumnName.ToUpper() + "=:" + columns[i].ColumnName + @" ""); OracleParameter p" + Common.fristCharToUpper(columns[i].ColumnName) + @" = new OracleParameter("":" + columns[i].ColumnName + @""", OracleDbType." + columns[i].ColumnODACParamType + @");"); sb.Append(@" p" + Common.fristCharToUpper(columns[i].ColumnName) + @".Direction = ParameterDirection.Input;"); sb.Append(@" p" + Common.fristCharToUpper(columns[i].ColumnName) + @".Value = " + paramName + "." + Common.fristCharToUpper(columns[i].ColumnName) + @";"); sb.Append(@" sqlparams.Add(p" + Common.fristCharToUpper(columns[i].ColumnName) + @"); } "); } else { sb.Append(@" if (null != " + paramName + "." + Common.fristCharToUpper(columns[i].ColumnName) + @" && """" != " + paramName + "." + Common.fristCharToUpper(columns[i].ColumnName) + @") { this.isAnd(sql); this.isWhere(sql); sql.Append("" A." + columns[i].ColumnName.ToUpper() + "=:" + columns[i].ColumnName + @" ""); OracleParameter p" + Common.fristCharToUpper(columns[i].ColumnName) + @" = new OracleParameter("":" + columns[i].ColumnName + @""", OracleDbType." + columns[i].ColumnODACParamType + @");"); sb.Append(@" p" + Common.fristCharToUpper(columns[i].ColumnName) + @".Direction = ParameterDirection.Input;"); sb.Append(@" p" + Common.fristCharToUpper(columns[i].ColumnName) + @".Value = " + paramName + "." + Common.fristCharToUpper(columns[i].ColumnName) + @";"); sb.Append(@" sqlparams.Add(p" + Common.fristCharToUpper(columns[i].ColumnName) + @"); } "); } } #endregion sb.Append(@" if(" + paramNameLike + @"!=null) { "); #region 循环添加模糊查询的条件参数 for (int i = 1; i < columns.Count; i++) { if (columns[i].ColumnCSharpType == "string") { sb.Append(@" if (null != " + paramNameLike + "." + Common.fristCharToUpper(columns[i].ColumnName) + @" && """" != " + paramNameLike + "." + Common.fristCharToUpper(columns[i].ColumnName) + @") { this.isAnd(sql); this.isWhere(sql); sql.Append("" A." + columns[i].ColumnName.ToUpper() + " LIKE '%'||:" + columns[i].ColumnName + @"||'%' ""); OracleParameter p" + Common.fristCharToUpper(columns[i].ColumnName) + @" = new OracleParameter("":" + columns[i].ColumnName + @""", OracleDbType." + columns[i].ColumnODACParamType + @");"); sb.Append(@" p" + Common.fristCharToUpper(columns[i].ColumnName) + @".Direction = ParameterDirection.Input;"); sb.Append(@" p" + Common.fristCharToUpper(columns[i].ColumnName) + @".Value = " + paramNameLike + "." + Common.fristCharToUpper(columns[i].ColumnName) + @";"); sb.Append(@" sqlparams.Add(p" + Common.fristCharToUpper(columns[i].ColumnName) + @"); } "); } } #endregion sb.Append(@" } "); sb.Append(@" this.b_isWhere = false; this.addSort(sql, sortList);//排序 //*******分页start1*********如果分页对象为空,则全部数据查出,如果不为空 List<object> returnList = new List<object>();//返回的List,索引0为List<" + paramType + @">对象,索引1为Model.Common.PageInfo分页信息对象 if (null != page) { string sqlParam = sql.ToString(); sql = new StringBuilder(@"" SELECT * FROM ( SELECT A.*, ROWNUM RN FROM (""+sqlParam+@"") A WHERE ROWNUM <= :maxIndex ) WHERE RN >= :minIndex ""); sqlparams.Add(new OracleParameter(""maxIndex"", page.MaxIndex)); sqlparams.Add(new OracleParameter(""minIndex"", page.MinIndex)); } //*******分页end1********* DataSet ds = OracleHelper.getDataSet(sql.ToString(), sqlparams.ToArray()); DataRowCollection rows = ds.Tables[0].Rows; //把每一行数据转换为一个对象放入List<" + paramType + @">中 for (int i = 0; i < rows.Count; i++) { DataRow row = rows[i]; //" + paramName + @"List.Add(this.dataSetContactObjectsToModel(row)); " + paramName + @"List.Add(this.dataSetToModel(row)); } //*******分页start2*********如果没有传入分页对象,则新建一个分页对象当做返回值 if (null == page) { page = new Model.Common.PageInfo(); page.AllCount = rows.Count; } returnList.Add(" + paramName + "List);//把List<" + paramType + @">放入List<object>中第一个 returnList.Add(page);//把page对象放入List<object>中第二个 //*******分页end2********* return returnList; } /// <summary> /// 根据条件得到集合的数量 /// 第二个条件是模糊查询的条件 /// </summary> /// <returns>int</returns> public int getCount(" + paramType + " " + paramName + @"," + paramType + " " + paramNameLike + @") { //如果没有传查询条件参数,就new一个空的" + paramType + @"对象,标示查询所有数据 if (null == " + paramName + @") { " + paramName + " = new " + paramType + @"(); } List<OracleParameter> sqlparams = new List<OracleParameter>();//保存参数的集合 StringBuilder sql = new StringBuilder(""SELECT COUNT(ID) FROM " + tableName + @" A"");"); #region 循环字段添加条件参数(得到集合的数量) for (int i = 1; i < columns.Count; i++) { if (columns[i].ColumnCSharpType == "DateTime") { sb.Append(@" if (DateTime.MinValue != " + paramName + "." + Common.fristCharToUpper(columns[i].ColumnName) + " &&null != " + paramName + "." + Common.fristCharToUpper(columns[i].ColumnName) + @") { this.isAnd(sql); this.isWhere(sql); sql.Append("" A." + columns[i].ColumnName.ToUpper() + "=:" + columns[i].ColumnName + @" ""); OracleParameter p" + Common.fristCharToUpper(columns[i].ColumnName) + @" = new OracleParameter("":" + columns[i].ColumnName + @""", OracleDbType." + columns[i].ColumnODACParamType + @");"); sb.Append(@" p" + Common.fristCharToUpper(columns[i].ColumnName) + @".Value = " + paramName + "." + Common.fristCharToUpper(columns[i].ColumnName) + @";"); sb.Append(@" sqlparams.Add(p" + Common.fristCharToUpper(columns[i].ColumnName) + @"); } "); } else if (columns[i].ColumnCSharpType == "decimal") { sb.Append(@" if (null != " + paramName + "." + Common.fristCharToUpper(columns[i].ColumnName) + @") { this.isComma(sql); sql.Append("" " + columns[i].ColumnName.ToUpper() + "=:" + columns[i].ColumnName + @" ""); OracleParameter p" + Common.fristCharToUpper(columns[i].ColumnName) + @" = new OracleParameter("":" + columns[i].ColumnName + @""", OracleDbType." + columns[i].ColumnODACParamType + @");"); sb.Append(@" p" + Common.fristCharToUpper(columns[i].ColumnName) + @".Value = " + paramName + "." + Common.fristCharToUpper(columns[i].ColumnName) + @";"); sb.Append(@" sqlparams.Add(p" + Common.fristCharToUpper(columns[i].ColumnName) + @"); } "); } else { sb.Append(@" if (null != " + paramName + "." + Common.fristCharToUpper(columns[i].ColumnName) + @" && """" != " + paramName + "." + Common.fristCharToUpper(columns[i].ColumnName) + @") { this.isAnd(sql); this.isWhere(sql); sql.Append("" A." + columns[i].ColumnName.ToUpper() + "=:" + columns[i].ColumnName + @" ""); OracleParameter p" + Common.fristCharToUpper(columns[i].ColumnName) + @" = new OracleParameter("":" + columns[i].ColumnName + @""", OracleDbType." + columns[i].ColumnODACParamType + @");"); sb.Append(@" p" + Common.fristCharToUpper(columns[i].ColumnName) + @".Value = " + paramName + "." + Common.fristCharToUpper(columns[i].ColumnName) + @";"); sb.Append(@" sqlparams.Add(p" + Common.fristCharToUpper(columns[i].ColumnName) + @"); } "); } } #endregion sb.Append(@" if(" + paramNameLike + @"!=null) { "); #region 循环添加模糊查询的条件参数 for (int i = 1; i < columns.Count; i++) { if (columns[i].ColumnCSharpType == "string") { sb.Append(@" if (null != " + paramNameLike + "." + Common.fristCharToUpper(columns[i].ColumnName) + @" && """" != " + paramNameLike + "." + Common.fristCharToUpper(columns[i].ColumnName) + @") { this.isAnd(sql); this.isWhere(sql); sql.Append("" A." + columns[i].ColumnName.ToUpper() + " LIKE '%'||:" + columns[i].ColumnName + @"||'%' ""); OracleParameter p" + Common.fristCharToUpper(columns[i].ColumnName) + @" = new OracleParameter("":" + columns[i].ColumnName + @""", OracleDbType." + columns[i].ColumnODACParamType + @");"); sb.Append(@" p" + Common.fristCharToUpper(columns[i].ColumnName) + @".Direction = ParameterDirection.Input;"); sb.Append(@" p" + Common.fristCharToUpper(columns[i].ColumnName) + @".Value = " + paramNameLike + "." + Common.fristCharToUpper(columns[i].ColumnName) + @";"); sb.Append(@" sqlparams.Add(p" + Common.fristCharToUpper(columns[i].ColumnName) + @"); } "); } } #endregion sb.Append(@" } "); sb.Append(@" this.b_isWhere = false; return Int32.Parse(OracleHelper.ExecuteScalar(sql.ToString(),sqlparams.ToArray()).ToString()); } /// <summary> /// 通用的保存方法,根据用户传入的参数,有ID为修改,没有ID为新增(手动生成OracleConnection对象,此方法用于事务,使用事务必须使用同一个OracleConnection对象) /// </summary> /// <param name=""" + paramName + @"""></param> /// <returns></returns> public bool save(" + paramType + @" " + paramName + @",OracleConnection con) { if ("""" != " + paramName + ".Id && null != " + paramName + @".Id) { //修改 return this.updateById(" + paramName + @",con); } else { //新增 return this.insert(" + paramName + @",con); } } /// <summary> /// 把dataset中的一行转换为model /// </summary> /// <param name=""row""></param> /// <returns></returns> private " + paramType + @" dataSetToModel(DataRow row) { " + paramType + " " + paramName + " = new " + paramType + @"(); "); #region 循环字段(dataset转换为modelList) for (int i = 0; i < columns.Count; i++) //处理字段类型 { if (columns[i].ColumnCSharpType == "string") //如果此字段的类型为string,使用Convert.toString().tirm() { sb.Append("" + paramName + "." + Common.fristCharToUpper(columns[i].ColumnName) + @" = Convert.ToString(row[""" + columns[i].ColumnName.ToUpper() + @"""]).Trim(); "); } else if (columns[i].ColumnIsNull == "y") //如果此字段可以为空,就使用convertDBNullValue转换 { sb.Append("" + paramName + "." + Common.fristCharToUpper(columns[i].ColumnName) + @" = (" + columns[i].ColumnCSharpType + @"?)this.convertDBNullValue(row[""" + columns[i].ColumnName.ToUpper() + @"""]); "); //类型转换的判断 } else//默认的 { sb.Append("" + paramName + "." + Common.fristCharToUpper(columns[i].ColumnName) + @" = (" + columns[i].ColumnCSharpType + @")row[""" + columns[i].ColumnName.ToUpper() + @"""]; ");//类型转换的判断 } } #endregion sb.Append(@" return " + paramName + @"; } //*******************在此以后添加新的方法start //*******************在此以后添加新的方法end } } "); return(sb.ToString()); }
/// <summary> /// 添加构建sql中where条件下的参数,带判断是否为空值的(数组值的) /// if (null != jc_codeUpdate.Zfbz && "" != jc_codeUpdate.Zfbz) /// { /// this.isComma(sql); /// sql.Append(" ZFBZ=:zfbzValue "); /// OracleParameter pZfbzValue = new OracleParameter(":zfbzValue", OracleDbType.NChar); /// pZfbzValue.Value = jc_codeUpdate.Zfbz; /// sqlparams.Add(pZfbzValue); /// } /// </summary> /// <param name="columns"></param> /// <param name="paramName"></param> /// <returns></returns> public static StringBuilder addWhereParamAssignmentForArray(List <oracleDBHelper.DBColumn> columns, string paramName, string paramNameList) { StringBuilder sb = new StringBuilder(); for (int i = 1; i < columns.Count; i++) { oracleDBHelper.DBColumn column = columns[i]; if (columns[i].ColumnCSharpType == "DateTime") { sb.Append(@" if (DateTime.MinValue != " + paramName + "." + Common.fristCharToUpper(columns[i].ColumnName) + " &&null != " + paramName + "." + Common.fristCharToUpper(columns[i].ColumnName) + @") { this.isAnd(sql); this.isWhere(sql); sql.Append("" " + columns[i].ColumnName.ToUpper() + "=:" + columns[i].ColumnName + @" ""); OracleParameter p" + Common.fristCharToUpper(columns[i].ColumnName) + @" = new OracleParameter("":" + columns[i].ColumnName + @""", OracleDbType." + columns[i].ColumnODACParamType + @");"); sb.Append(@" p" + Common.fristCharToUpper(columns[i].ColumnName) + @".Direction = ParameterDirection.Input;"); sb.Append(@" p" + Common.fristCharToUpper(columns[i].ColumnName) + @".Value = " + paramName + "." + Common.fristCharToUpper(columns[i].ColumnName) + @";"); sb.Append(@" sqlparams.Add(p" + Common.fristCharToUpper(columns[i].ColumnName) + @"); } "); } else if (columns[i].ColumnCSharpType == "decimal") { sb.Append(@" if (null != " + paramName + "." + Common.fristCharToUpper(columns[i].ColumnName) + @") { this.isAnd(sql); this.isWhere(sql); sql.Append("" " + columns[i].ColumnName.ToUpper() + "=:" + columns[i].ColumnName + @" ""); OracleParameter p" + Common.fristCharToUpper(columns[i].ColumnName) + @" = new OracleParameter("":" + columns[i].ColumnName + @""", OracleDbType." + columns[i].ColumnODACParamType + @");"); sb.Append(@" p" + Common.fristCharToUpper(columns[i].ColumnName) + @".Direction = ParameterDirection.Input;"); sb.Append(@" p" + Common.fristCharToUpper(columns[i].ColumnName) + @".Value = " + paramName + "." + Common.fristCharToUpper(columns[i].ColumnName) + @";"); sb.Append(@" sqlparams.Add(p" + Common.fristCharToUpper(columns[i].ColumnName) + @"); } "); } else { sb.Append(@" if (null != " + paramName + "." + Common.fristCharToUpper(columns[i].ColumnName) + @" && """"!= " + paramName + "." + Common.fristCharToUpper(columns[i].ColumnName) + @") { this.isAnd(sql); this.isWhere(sql); sql.Append("" " + columns[i].ColumnName.ToUpper() + "=:" + columns[i].ColumnName + @" ""); OracleParameter p" + Common.fristCharToUpper(columns[i].ColumnName) + @" = new OracleParameter("":" + columns[i].ColumnName + @""", OracleDbType." + columns[i].ColumnODACParamType + @");"); sb.Append(@" p" + Common.fristCharToUpper(columns[i].ColumnName) + @".Direction = ParameterDirection.Input;"); sb.Append(@" p" + Common.fristCharToUpper(columns[i].ColumnName) + @".Value = " + paramName + ".ToArray();"); sb.Append(@" sqlparams.Add(p" + Common.fristCharToUpper(columns[i].ColumnName) + @"); } "); } } return(sb); }