public static string CreateQueryListMethod(BootstrapModel 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)
{
if (item.DBType.ToLower() == "int")
{
queryWhereContent.AppendFormat("if ({0} > 0)\r\n", field);
}
else if (item.DBType.ToLower() == "tinyint")
{
queryWhereContent.AppendFormat("if ({0} >= 0)\r\n", field);
}
else if (item.DBType.ToLower() == "datetime" || item.DBType.ToLower() == "date")
{
queryWhereContent.AppendFormat("if ({0} != DateTime.MinValue)\r\n", field);
}
else
{
queryWhereContent.AppendFormat("if (!string.IsNullOrEmpty({0}))\r\n", field);
}
}
else
{
if (item.DBType.ToLower() == "int")
{
queryWhereContent.AppendFormat("\t\t\tif ({0} > 0)\r\n", field);
}
else if (item.DBType.ToLower() == "tinyint")
{
queryWhereContent.AppendFormat("\t\t\tif ({0} >= 0)\r\n", field);
}
else if (item.DBType.ToLower() == "datetime" || item.DBType.ToLower() == "date")
{
queryWhereContent.AppendFormat("\t\t\tif ({0} != DateTime.MinValue)\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 MySqlParameter(\"@{0}\", {1}) {{ Value = {2} }});\r\n", item.ColumnName, item.DBType.ToMySqlDbType(), 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 " + model.TableName + " where 1=1 {0} limit {1},{2};\", whereStr, ((page - 1) * pageSize), 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<MySqlParameter> listParams = new List<MySqlParameter>();
{2}
{7}
List<{0}> result = new List<{0}>();
using (MySqlConnection sqlcn = ConnectionFactory.{3})
{{
using(MySqlDataReader sqldr = MySqlHelper2.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<MySqlParameter> listParams = new List<MySqlParameter>();
{2}
string selectCountSql = string.Format(""select count(0) from {6} where 1=1 {{0}}"", whereStr);
using (MySqlConnection sqlcn = ConnectionFactory.{3})
{{
return Convert.ToInt32(MySqlHelper2.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()));
}
public static string CreateGetAllAndPart(BootstrapModel 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)
{
if (item.DBType.ToLower() == "int")
{
queryWhereContent.AppendFormat(" if ({0} > 0)\r\n", field);
}
else if (item.DBType.ToLower() == "tinyint")
{
queryWhereContent.AppendFormat(" if ({0} >= 0)\r\n", field);
}
else if (item.DBType.ToLower() == "datetime" || item.DBType.ToLower() == "datetime")
{
queryWhereContent.AppendFormat(" if ({0} != DateTime.MinValue)\r\n", field);
}
else
{
queryWhereContent.AppendFormat(" if (!string.IsNullOrEmpty({0}))\r\n", field);
}
}
else
{
if (item.DBType.ToLower() == "int")
{
queryWhereContent.AppendFormat(" if ({0} > 0)\r\n", field);
}
else if (item.DBType.ToLower() == "tinyint")
{
queryWhereContent.AppendFormat(" if ({0} >= 0)\r\n", field);
}
else if (item.DBType.ToLower() == "datetime" || item.DBType.ToLower() == "datetime")
{
queryWhereContent.AppendFormat(" if ({0} != DateTime.MinValue)\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()));
}
