public static string CreateGetAllAndPart(EasyUIModel model) { StringBuilder queryWhereContent = new StringBuilder(); StringBuilder queryListParams = new StringBuilder(); int index = 0; foreach (var item in model.SearchColumns) { string field = item.ColumnName.ToFirstLower(); string attribute = item.ColumnName.ToFirstUpper(); queryListParams.AppendFormat("{0} {1},", item.DBType.ToMsSqlClassType(), field); if (index == 0) { queryWhereContent.AppendFormat(" if (!string.IsNullOrEmpty({0}))\r\n", field); } else { queryWhereContent.AppendFormat(" if (!string.IsNullOrEmpty({0}))\r\n", field); } queryWhereContent.Append(" {\r\n"); queryWhereContent.AppendFormat(" listParams.Add(new SqlParameter(\"@{0}\", {1}) {{ Value = {2} }});\r\n", item.ColumnName, item.DBType.ToMsSqlDbType(), field); queryWhereContent.AppendFormat(" whereStr += \" and {0}=@{0} \";\r\n", item.ColumnName.ToFirstUpper()); queryWhereContent.Append(" }\r\n"); queryWhereContent.AppendLine(); index++; } StringBuilder selectSqlContent = new StringBuilder(); selectSqlContent.Append(" string selectSql = string.Format(@\"select * from\r\n"); selectSqlContent.Append(" " + model.TableName + " where " + model.MainKeyIdStr + " in ({1})\r\n"); selectSqlContent.Append(" {0};\", whereStr, idArrayStr);\r\n"); var assignContent = new StringBuilder(); foreach (var item in model.ColumnList) { string field = item.ColumnName.ToFirstLower(); string attribute = item.ColumnName.ToFirstUpper(); assignContent.AppendFormat(" model.{0} = sqldr[\"{1}\"] == DBNull.Value ? {2} : {3};\r\n", attribute, item.ColumnName, ExtendMethod.ToDefaultValue(item.DBType), ExtendMethod.ToDefaultDBValue(item.DBType, item.ColumnName)); } string template = @" public List<{0}> GetPartAll({1}List<string> idList) {{ var idArrayStr = string.Join("","", (from f in idList select ""'""+ f + ""'"").ToArray()); string whereStr = string.Empty; List<SqlParameter> listParams = new List<SqlParameter>(); {2} {7} List<{0}> result = new List<{0}>(); using (SqlConnection sqlcn = ConnectionFactory.{3}) {{ using(SqlDataReader sqldr = SqlHelper.ExecuteReader(sqlcn, CommandType.Text, selectSql, listParams.ToArray())) {{ while(sqldr.Read()) {{ {0} model = new {0}(); {5} result.Add(model); }} }} }} return result; }} public List<{0}> GetAll({4}) {{ string whereStr = string.Empty; List<SqlParameter> listParams = new List<SqlParameter>(); {2} string selectAllSql = string.Format(""select * from {6} where 1=1 {{0}}"", whereStr); List<{0}> result = new List<{0}>(); using (SqlConnection sqlcn = ConnectionFactory.{3}) {{ using(SqlDataReader sqldr = SqlHelper.ExecuteReader(sqlcn, CommandType.Text, selectAllSql, listParams.ToArray())) {{ while(sqldr.Read()) {{ {0} model = new {0}(); {5} result.Add(model); }} }} }} return result; }} "; string queryCountParams = queryListParams.Length > 0 ? queryListParams.ToString().Substring(0, queryListParams.Length - 1) : queryListParams.ToString(); return(string.Format(template, model.TableName.ToFirstUpper(), queryListParams.ToString(), queryWhereContent.ToString(), model.DbName.ToFirstUpper(), queryCountParams, assignContent.ToString(), model.TableName, selectSqlContent.ToString())); }
public static string CreateQueryListMethod(EasyUIModel model) { StringBuilder queryWhereContent = new StringBuilder(); StringBuilder queryListParams = new StringBuilder(); int index = 0; foreach (var item in model.SearchColumns) { string field = item.ColumnName.ToFirstLower(); string attribute = item.ColumnName.ToFirstUpper(); queryListParams.AppendFormat("{0} {1},", item.DBType.ToMsSqlClassType(), field); if (index == 0) { queryWhereContent.AppendFormat("if (!string.IsNullOrEmpty({0}))\r\n", field); } else { queryWhereContent.AppendFormat("\t\t\tif (!string.IsNullOrEmpty({0}))\r\n", field); } queryWhereContent.Append("\t\t\t{\r\n"); queryWhereContent.AppendFormat("\t\t\t\tlistParams.Add(new SqlParameter(\"@{0}\", {1}) {{ Value = {2} }});\r\n", item.ColumnName, item.DBType.ToMsSqlDbType(), field); queryWhereContent.AppendFormat("\t\t\t\twhereStr += \" and {0}=@{0} \";\r\n", item.ColumnName.ToFirstUpper()); queryWhereContent.Append("\t\t\t}\r\n"); queryWhereContent.AppendLine(); index++; } StringBuilder selectSqlContent = new StringBuilder(); selectSqlContent.Append("\tstring selectSql = string.Format(@\"select * from\r\n"); selectSqlContent.AppendFormat("\t (select top 100 percent *,ROW_NUMBER() over(order by {0}) as rownumber from\r\n", model.MainKeyIdStr); selectSqlContent.Append("\t " + model.TableName + " where 1=1 {0}) as T\r\n"); selectSqlContent.Append("\t where rownumber between {1} and {2};\", whereStr, ((page - 1) * pageSize + 1), page * pageSize);\r\n"); var assignContent = new StringBuilder(); foreach (var item in model.ColumnList) { string field = item.ColumnName.ToFirstLower(); string attribute = item.ColumnName.ToFirstUpper(); assignContent.AppendFormat(" model.{0} = sqldr[\"{1}\"] == DBNull.Value ? {2} : {3};\r\n", attribute, item.ColumnName, ExtendMethod.ToDefaultValue(item.DBType), ExtendMethod.ToDefaultDBValue(item.DBType, item.ColumnName)); } string template = @" public List<{0}> QueryList({1}int page, int pageSize) {{ string whereStr = string.Empty; List<SqlParameter> listParams = new List<SqlParameter>(); {2} {7} List<{0}> result = new List<{0}>(); using (SqlConnection sqlcn = ConnectionFactory.{3}) {{ using(SqlDataReader sqldr = SqlHelper.ExecuteReader(sqlcn, CommandType.Text, selectSql, listParams.ToArray())) {{ while(sqldr.Read()) {{ {0} model = new {0}(); {5} result.Add(model); }} }} }} return result; }} public int QueryListCount({4}) {{ string whereStr = string.Empty; List<SqlParameter> listParams = new List<SqlParameter>(); {2} string selectCountSql = string.Format(""select count(0) from {6} where 1=1 {{0}}"", whereStr); using (SqlConnection sqlcn = ConnectionFactory.{3}) {{ return Convert.ToInt32(SqlHelper.ExecuteScalar(sqlcn, CommandType.Text, selectCountSql, listParams.ToArray())); }} }} "; string queryCountParams = queryListParams.Length > 0 ? queryListParams.ToString().Substring(0, queryListParams.Length - 1) : queryListParams.ToString(); return(string.Format(template, model.TableName.ToFirstUpper(), queryListParams.ToString(), queryWhereContent.ToString(), model.DbName.ToFirstUpper(), queryCountParams, assignContent.ToString(), model.TableName, selectSqlContent.ToString())); }