public static string CreateGetAllAndPart(int action, string table_name, List <SqlColumnInfo> colList, string model_name, string db_name) { int index = 0; var down_all_Model = (action | (int)action_type.export_all) == (int)action_type.export_all; var down_selelct_Model = (action | (int)action_type.export_select) == (int)action_type.export_select; if (down_all_Model || down_selelct_Model) { StringBuilder resultContent = new StringBuilder(); #region 导出全部 if (down_all_Model) { StringBuilder queryWhereContent = new StringBuilder(); StringBuilder queryListParams = new StringBuilder(); var queryList = Cache_VMData.GetVMList(table_name, VMType.Query, colList.ToNotMainIdList()); foreach (var item in queryList) { queryListParams.AppendFormat("{0} {1},", item.DbType.ToMsSqlClassType(), item.Name); if (index == 0) { if (item.DbType.ToLower() == "int") { queryWhereContent.AppendFormat(" if ({0} > 0)\r\n", item.Name); } else if (item.DbType.ToLower() == "tinyint") { queryWhereContent.AppendFormat(" if ({0} >= 0)\r\n", item.Name); } else if (item.DbType.ToLower() == "datetime" || item.DbType.ToLower() == "datetime") { queryWhereContent.AppendFormat(" if ({0} != DateTime.MinValue)\r\n", item.Name); } else { queryWhereContent.AppendFormat(" if (!string.IsNullOrEmpty({0}))\r\n", item.Name); } } else { if (item.DbType.ToLower() == "int" || item.DbType.ToLower() == "bigint") { queryWhereContent.AppendFormat(" if ({0} > 0)\r\n", item.Name); } else if (item.DbType.ToLower() == "tinyint") { queryWhereContent.AppendFormat(" if ({0} >= 0)\r\n", item.Name); } else if (item.DbType.ToLower() == "datetime" || item.DbType.ToLower() == "datetime") { queryWhereContent.AppendFormat(" if ({0} != DateTime.MinValue)\r\n", item.Name); } else { queryWhereContent.AppendFormat(" if (!string.IsNullOrEmpty({0}))\r\n", item.Name); } } queryWhereContent.Append(" {\r\n"); queryWhereContent.AppendFormat(" listParams.Add(new SqlParameter(\"@{0}\", {1}) {{ Value = {0} }});\r\n", item.Name, item.DbType.ToMsSqlDbType()); queryWhereContent.AppendFormat(" whereStr += \" and {0}=@{0} \";\r\n", item.Name); queryWhereContent.Append(" }\r\n"); queryWhereContent.AppendLine(); index++; } var assignContent = new StringBuilder(); foreach (var item in colList) { assignContent.AppendFormat(" model.{0} = sqldr[\"{0}\"] == DBNull.Value ? {1} : {2};\r\n", item.Name, ExtendMethod.ToDefaultValue(item.DbType), ExtendMethod.ToDefaultDBValue(item.DbType, item.Name)); } string template = @" public List<{0}> GetAll({3}) {{ string whereStr = string.Empty; List<SqlParameter> listParams = new List<SqlParameter>(); {1} string selectAllSql = string.Format(""select * from {5} where 1=1 {{0}}"", whereStr); List<{0}> result = new List<{0}>(); using (SqlConnection sqlcn = ConnectionFactory.{2}) {{ using(SqlDataReader sqldr = SqlHelper.ExecuteDataReader(sqlcn, CommandType.Text, selectAllSql, listParams.ToArray())) {{ while(sqldr.Read()) {{ {0} model = new {0}(); {4} result.Add(model); }} }} }} return result; }} "; string queryCountParams = queryListParams.Length > 0 ? queryListParams.ToString().Substring(0, queryListParams.Length - 1) : queryListParams.ToString(); resultContent.AppendFormat(template, table_name, queryWhereContent.ToString(), db_name, queryCountParams, assignContent.ToString(), table_name); } #endregion #region 导出选中 if (down_selelct_Model) { StringBuilder selectSqlContent = new StringBuilder(); selectSqlContent.Append(" string selectSql = string.Format(@\"select * from\r\n"); selectSqlContent.Append(" " + table_name + " where " + colList.ToKeyId() + " in ({1})\r\n"); selectSqlContent.Append(" {0};\", whereStr, idArrayStr);\r\n"); var assignContent = new StringBuilder(); foreach (var item in colList) { assignContent.AppendFormat(" model.{0} = sqldr[\"{0}\"] == DBNull.Value ? {1} : {2};\r\n", item.Name, ExtendMethod.ToDefaultValue(item.DbType), ExtendMethod.ToDefaultDBValue(item.DbType, item.Name)); } string template = @" public List<{0}> GetPartAll(List<string> idList) {{ var idArrayStr = string.Join("","", (from f in idList select ""'""+ f + ""'"").ToArray()); string whereStr = string.Empty; List<SqlParameter> listParams = new List<SqlParameter>(); {3} List<{0}> result = new List<{0}>(); using (SqlConnection sqlcn = ConnectionFactory.{1}) {{ using(SqlDataReader sqldr = SqlHelper.ExecuteDataReader(sqlcn, CommandType.Text, selectSql, listParams.ToArray())) {{ while(sqldr.Read()) {{ {0} model = new {0}(); {2} result.Add(model); }} }} }} return result; }} "; resultContent.AppendFormat(template, table_name, db_name, assignContent.ToString(), selectSqlContent.ToString()); } #endregion return(resultContent.ToString()); } else { return(string.Empty); } }
public static string CreateQueryListMethod() { StringBuilder queryWhereContent = new StringBuilder(); StringBuilder queryListParams = new StringBuilder(); var showModel = PageCache.GetCmd("主显示"); if (showModel != null) { int index = 0; foreach (var item in showModel.AttrList) { string attribute = item.ColName; queryListParams.AppendFormat("{0} {1},", item.DbType.ToMsSqlClassType(), attribute); if (index == 0) { if (item.DbType.ToLower() == "int" || item.DbType.ToLower() == "bigint" || item.DbType.ToLower() == "decimal" || item.DbType.ToLower() == "float" || item.DbType.ToLower() == "double") { queryWhereContent.AppendFormat("if ({0} >= 0)\r\n", attribute); } else if (item.DbType.ToLower() == "tinyint") { queryWhereContent.AppendFormat("if ({0} >= 0)\r\n", attribute); } else if (item.DbType.ToLower() == "datetime" || item.DbType.ToLower() == "date") { queryWhereContent.AppendFormat("if ({0} != DateTime.MinValue)\r\n", attribute); } else { queryWhereContent.AppendFormat("if (!string.IsNullOrEmpty({0}))\r\n", attribute); } } else { if (item.DbType.ToLower() == "int" || item.DbType.ToLower() == "bigint" || item.DbType.ToLower() == "decimal" || item.DbType.ToLower() == "float" || item.DbType.ToLower() == "double") { queryWhereContent.AppendFormat("\t\t\tif ({0} >= 0)\r\n", attribute); } else if (item.DbType.ToLower() == "tinyint") { queryWhereContent.AppendFormat("\t\t\tif ({0} >= 0)\r\n", attribute); } else if (item.DbType.ToLower() == "datetime" || item.DbType.ToLower() == "date") { queryWhereContent.AppendFormat("\t\t\tif ({0} != DateTime.MinValue)\r\n", attribute); } else { queryWhereContent.AppendFormat("\t\t\tif (!string.IsNullOrEmpty({0}))\r\n", attribute); } } queryWhereContent.Append("\t\t\t{\r\n"); queryWhereContent.AppendFormat("\t\t\t\tlistParams.Add(new SqlParameter(\"@{0}\", {1}) {{ Value = {0} }});\r\n", attribute, item.DbType.ToMsSqlDbType()); queryWhereContent.AppendFormat("\t\t\t\twhereStr += \" and {0}=@{0} \";\r\n", attribute); queryWhereContent.Append("\t\t\t}\r\n"); queryWhereContent.AppendLine(); index++; } StringBuilder selectSqlContent = new StringBuilder(); selectSqlContent.Append("\tstring selectSql = string.Format(@\"select * from " + PageCache.TableName + " where 1=1 {0} limit {1},{2};\", whereStr, ((page - 1) * pageSize), pageSize);\r\n"); var assignContent = new StringBuilder(); if (!showModel.AttrList.Exists(p => p.ColName == PageCache.KeyId)) { assignContent.AppendFormat(" model.{0} = sqldr[\"{0}\"] == DBNull.Value ? {1} : {2};\r\n", PageCache.KeyId, ExtendMethod.ToDefaultValue(PageCache.KeyId_DbType), ExtendMethod.ToDefaultDBValue(PageCache.KeyId_DbType, PageCache.KeyId)); } foreach (var item in showModel.AttrList) { string attribute = item.ColName; assignContent.AppendFormat(" model.{0} = sqldr[\"{0}\"] == DBNull.Value ? {1} : {2};\r\n", attribute, ExtendMethod.ToDefaultValue(item.DbType), ExtendMethod.ToDefaultDBValue(item.DbType, attribute)); } 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.ExecuteDataReader(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, PageCache.TableName_Model, queryListParams.ToString(), queryWhereContent.ToString(), PageCache.DatabaseName, queryCountParams, assignContent.ToString(), PageCache.TableName, selectSqlContent.ToString())); } else { return(string.Empty); } }
public static string CreateQueryListMethod(int action, string table_name, List <SqlColumnInfo> colList, string model_name, string db_name) { StringBuilder queryWhereContent = new StringBuilder(); StringBuilder queryListParams = new StringBuilder(); if ((action & (int)action_type.query_list) == (int)action_type.query_list) { int index = 0; var queryList = Cache_VMData.GetVMList(table_name, VMType.Query, colList.ToNotMainIdList()); foreach (var item in queryList) { queryListParams.AppendFormat("{0} {1},", item.DbType.ToMsSqlClassType(), item.Name); if (index == 0) { if (item.DbType.ToLower() == "int" || item.DbType.ToLower() == "bigint" || item.DbType.ToLower() == "decimal" || item.DbType.ToLower() == "float" || item.DbType.ToLower() == "double") { queryWhereContent.AppendFormat("if ({0} >= 0)\r\n", item.Name); } else if (item.DbType.ToLower() == "tinyint") { queryWhereContent.AppendFormat("if ({0} >= 0)\r\n", item.Name); } else if (item.DbType.ToLower() == "datetime" || item.DbType.ToLower() == "date") { queryWhereContent.AppendFormat("if ({0} != DateTime.MinValue)\r\n", item.Name); } else { queryWhereContent.AppendFormat("if (!string.IsNullOrEmpty({0}))\r\n", item.Name); } } else { if (item.DbType.ToLower() == "int" || item.DbType.ToLower() == "bigint" || item.DbType.ToLower() == "decimal" || item.DbType.ToLower() == "float" || item.DbType.ToLower() == "double") { queryWhereContent.AppendFormat("\r\n\t\t\tif ({0} >= 0)\r\n", item.Name); } else if (item.DbType.ToLower() == "tinyint") { queryWhereContent.AppendFormat("\r\n\t\t\tif ({0} >= 0)\r\n", item.Name); } else if (item.DbType.ToLower() == "datetime" || item.DbType.ToLower() == "date") { queryWhereContent.AppendFormat("\r\n\t\t\tif ({0} != DateTime.MinValue)\r\n", item.Name); } else { queryWhereContent.AppendFormat("\r\n\t\t\tif (!string.IsNullOrEmpty({0}))\r\n", item.Name); } } queryWhereContent.Append("\t\t\t{\r\n"); queryWhereContent.AppendFormat("\t\t\t\tlistParams.Add(new SqlParameter(\"@{0}\", {1}) {{ Value = {0} }});\r\n", item.Name, item.DbType.ToMsSqlDbType()); queryWhereContent.AppendFormat("\t\t\t\twhereStr += \" and {0}=@{0} \";\r\n", item.Name); queryWhereContent.Append("\t\t\t}"); queryWhereContent.AppendLine(); index++; } StringBuilder selectSqlContent = new StringBuilder(); selectSqlContent.Append("string selectSql = string.Format(@\"select * from " + table_name + " where 1=1 {0} limit {1},{2};\", whereStr, ((page - 1) * pageSize), pageSize);\r\n"); var assignContent = new StringBuilder(); foreach (var item in colList) { assignContent.AppendFormat(" model.{0} = sqldr[\"{0}\"] == DBNull.Value ? {1} : {2};\r\n", item.Name, ExtendMethod.ToDefaultValue(item.DbType), ExtendMethod.ToDefaultDBValue(item.DbType, item.Name)); } 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.ExecuteDataReader(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, table_name, queryListParams.ToString(), queryWhereContent.ToString(), db_name, queryCountParams, assignContent.ToString(), table_name, selectSqlContent.ToString())); } else { return(string.Empty); } }
public static string CreateGetAllAndPart() { int index = 0; var down_all_Model = PageCache.GetCmd("导出全部"); var down_selelct_Model = PageCache.GetCmd("导出选中"); if (down_all_Model != null || down_selelct_Model != null) { StringBuilder resultContent = new StringBuilder(); #region 导出全部 if (down_all_Model != null) { StringBuilder queryWhereContent = new StringBuilder(); StringBuilder queryListParams = new StringBuilder(); foreach (var item in down_all_Model.AttrList) { string attribute = item.ColName; queryListParams.AppendFormat("{0} {1},", item.DbType.ToMsSqlClassType(), attribute); if (index == 0) { if (item.DbType.ToLower() == "int") { queryWhereContent.AppendFormat(" if ({0} > 0)\r\n", attribute); } else if (item.DbType.ToLower() == "tinyint") { queryWhereContent.AppendFormat(" if ({0} >= 0)\r\n", attribute); } else if (item.DbType.ToLower() == "datetime" || item.DbType.ToLower() == "datetime") { queryWhereContent.AppendFormat(" if ({0} != DateTime.MinValue)\r\n", attribute); } else { queryWhereContent.AppendFormat(" if (!string.IsNullOrEmpty({0}))\r\n", attribute); } } else { if (item.DbType.ToLower() == "int" || item.DbType.ToLower() == "bigint") { queryWhereContent.AppendFormat(" if ({0} > 0)\r\n", attribute); } else if (item.DbType.ToLower() == "tinyint") { queryWhereContent.AppendFormat(" if ({0} >= 0)\r\n", attribute); } else if (item.DbType.ToLower() == "datetime" || item.DbType.ToLower() == "datetime") { queryWhereContent.AppendFormat(" if ({0} != DateTime.MinValue)\r\n", attribute); } else { queryWhereContent.AppendFormat(" if (!string.IsNullOrEmpty({0}))\r\n", attribute); } } queryWhereContent.Append(" {\r\n"); queryWhereContent.AppendFormat(" listParams.Add(new MySqlParameter(\"@{0}\", {1}) {{ Value = {0} }});\r\n", attribute, item.DbType.ToMySqlDbType()); queryWhereContent.AppendFormat(" whereStr += \" and {0}=@{0} \";\r\n", attribute); queryWhereContent.Append(" }\r\n"); queryWhereContent.AppendLine(); index++; } var assignContent = new StringBuilder(); foreach (var item in down_all_Model.AttrList) { string attribute = item.ColName; assignContent.AppendFormat(" model.{0} = sqldr[\"{0}\"] == DBNull.Value ? {1} : {2};\r\n", attribute, ExtendMethod.ToDefaultValue(item.DbType), ExtendMethod.ToDefaultDBValue(item.DbType, attribute)); } string template = @" public List<{0}> GetAll({3}) {{ string whereStr = string.Empty; List<MySqlParameter> listParams = new List<MySqlParameter>(); {1} string selectAllSql = string.Format(""select * from {5} where 1=1 {{0}}"", whereStr); List<{0}> result = new List<{0}>(); using (MySqlConnection sqlcn = ConnectionFactory.{2}) {{ using(MySqlDataReader sqldr = MySqlHelper2.ExecuteDataReader(sqlcn, CommandType.Text, selectAllSql, listParams.ToArray())) {{ while(sqldr.Read()) {{ {0} model = new {0}(); {4} result.Add(model); }} }} }} return result; }} "; string queryCountParams = queryListParams.Length > 0 ? queryListParams.ToString().Substring(0, queryListParams.Length - 1) : queryListParams.ToString(); resultContent.AppendFormat(template, PageCache.TableName_Model, queryWhereContent.ToString(), PageCache.DatabaseName, queryCountParams, assignContent.ToString(), PageCache.TableName); } #endregion #region 导出选中 if (down_selelct_Model != null) { StringBuilder selectSqlContent = new StringBuilder(); selectSqlContent.Append(" string selectSql = string.Format(@\"select * from\r\n"); selectSqlContent.Append(" " + PageCache.TableName + " where " + PageCache.KeyId + " in ({1})\r\n"); selectSqlContent.Append(" {0};\", whereStr, idArrayStr);\r\n"); var assignContent = new StringBuilder(); foreach (var item in down_selelct_Model.AttrList) { string colName = item.ColName; assignContent.AppendFormat(" model.{0} = sqldr[\"{0}\"] == DBNull.Value ? {1} : {2};\r\n", colName, ExtendMethod.ToDefaultValue(item.DbType), ExtendMethod.ToDefaultDBValue(item.DbType, colName)); } string template = @" public List<{0}> GetPartAll(List<string> idList) {{ var idArrayStr = string.Join("","", (from f in idList select ""'""+ f + ""'"").ToArray()); string whereStr = string.Empty; List<MySqlParameter> listParams = new List<MySqlParameter>(); {3} List<{0}> result = new List<{0}>(); using (MySqlConnection sqlcn = ConnectionFactory.{1}) {{ using(MySqlDataReader sqldr = MySqlHelper2.ExecuteDataReader(sqlcn, CommandType.Text, selectSql, listParams.ToArray())) {{ while(sqldr.Read()) {{ {0} model = new {0}(); {2} result.Add(model); }} }} }} return result; }} "; resultContent.AppendFormat(template, PageCache.TableName_Model, PageCache.DatabaseName, assignContent.ToString(), selectSqlContent.ToString()); } #endregion return(resultContent.ToString()); } else { return(string.Empty); } }