using System;
|
using System.Collections.Generic;
|
using System.Linq;
|
using System.Text;
|
using System.Threading.Tasks;
|
using System.Web.Script.Serialization;
|
|
namespace HH.WMS.Utils
|
{
|
public class SqlManager
|
{
|
#region 根据JSON字符串 获得表名、查询条件、排序
|
/// <summary>
|
/// 根据JSON字符串 获得表名、查询条件、排序 [分页调用]
|
/// </summary>
|
/// <param name="jsonString"></param>
|
/// <param name="tabName">表名[分页调用]</param>
|
/// <param name="strWhere">查询条件[分页调用]</param>
|
/// <param name="orderBy">排序[分页调用]</param>
|
/// <returns>不分页的SQL语句[不分页获得]</returns>
|
public static string GetSqlString_Page(string jsonString, out string tabName, out string strWhere, out string orderBy)
|
{
|
JavaScriptSerializer js = new JavaScriptSerializer();
|
Dictionary<string, string>[] dicArray = js.Deserialize<Dictionary<string, string>[]>(jsonString);
|
|
//不分页查询
|
StringBuilder strSql = new StringBuilder();
|
|
//定义表名称
|
string tableName = string.Empty;
|
//定义查询条件
|
StringBuilder sqlWhere = new StringBuilder();
|
sqlWhere.Append(" WHERE 1=1 ");
|
//定义排序
|
string orderby = string.Empty;
|
|
foreach (var item in dicArray)
|
{
|
Dictionary<string, string> dic = item;
|
|
string name = dic["name"].ToString();
|
//如果是表名称
|
if (name.Equals("tableName"))
|
{
|
tableName = dic["value"] + " ";
|
}
|
//如果是查询条件
|
else if (name.Equals("select"))
|
{
|
string colmn_id = dic["value"].ToString();
|
string colmn_value = dic["text"].ToString();
|
string colmn_key = dic["key"].ToString();
|
string colmn_type = dic["type"].ToString();
|
if (!string.IsNullOrEmpty(colmn_value))
|
{
|
sqlWhere.Append(GetWhereUnit(colmn_id, colmn_value, colmn_key, colmn_type));
|
}
|
}
|
//如果是排序
|
else if (name.Equals("orderby"))
|
{
|
string orderby_colmn = dic["value"].ToString();
|
string sort = dic["type"].ToString();
|
|
orderby = " order by " + orderby_colmn + " " + sort;
|
}
|
}
|
tabName = tableName;
|
strWhere = sqlWhere.ToString();
|
orderBy = orderby;
|
|
|
strSql.AppendLine("SELECT * FROM " + tableName + sqlWhere.ToString() + orderby);
|
|
return strSql.ToString();
|
}
|
#endregion
|
|
#region 获得查询条件
|
/// <summary>
|
/// 获得查询条件
|
/// </summary>
|
/// <param name="colmn_id">字段名</param>
|
/// <param name="colmn_value">字段值</param>
|
/// <param name="colmn_key">查询时的关键字( '=' 'like')</param>
|
/// <param name="colmn_type">字段类型 char varchar int</param>
|
/// <returns></returns>
|
private static string GetWhereUnit(string colmn_id, string colmn_value, string colmn_key, string colmn_type)
|
{
|
string sqlString = string.Empty;
|
|
switch (colmn_key)
|
{
|
case "=":
|
sqlString = " AND " + colmn_id + "=" + GetValue(colmn_value, colmn_type);
|
break;
|
case "!=":
|
sqlString = " AND " + colmn_id + " != " + GetValue(colmn_value, colmn_type);
|
break;
|
case "like":
|
sqlString = " AND " + colmn_id + " like '%" + colmn_value + "%' ";
|
break;
|
default:
|
break;
|
}
|
return sqlString;
|
}
|
#endregion
|
|
#region 判断是否需要加单引号
|
/// <summary>
|
/// 判断是否需要加单引号
|
/// </summary>
|
/// <param name="colmn_value"></param>
|
/// <param name="colmn_type"></param>
|
/// <returns></returns>
|
private static string GetValue(string colmn_value, string colmn_type)
|
{
|
string value = string.Empty;
|
switch (colmn_type.ToLower())
|
{
|
case "varchar":
|
value = " '" + colmn_value + "' ";
|
break;
|
case "char":
|
value = " '" + colmn_value + "' ";
|
break;
|
case "int":
|
value = colmn_value;
|
break;
|
default:
|
break;
|
}
|
return value;
|
}
|
#endregion
|
}
|
}
|
