/////////////////////////////////////////////////////////////////////////////
|
// File Description : 生成SQL语句
|
// Copyright : joyin
|
// -------------------------------------------------------------------------
|
// Date Created : Mar 26, 2010
|
// Author : jiangxinjun
|
//
|
/////////////////////////////////////////////////////////////////////////////
|
|
using System;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.Data.Common;
|
using HanHe.Common;
|
using HanHe.Entity;
|
using HanHe.Utility.Data;
|
|
namespace HanHe.DAL
|
{
|
/// <summary>
|
/// 生成SQL语句
|
/// </summary>
|
public static class SqlStringHelper
|
{
|
#region CreateInsertSql
|
/// <summary>
|
/// 生成Insert SQL语句
|
/// </summary>
|
/// <param name="tableName">表名</param>
|
/// <param name="db"></param>
|
/// <returns></returns>
|
public static string CreateInsertSql(string tableName, Database db)
|
{
|
string sql = CommonUseSqlFormat.INSERT_SQL;
|
var schema = DAOManager.LoadTableSchema(tableName, db);
|
|
List<string> columns = new List<string>();
|
List<string> parameters = new List<string>();
|
|
foreach (var item in schema)
|
{
|
if (!item.Value.IsIdentity) //Identity的不需要插入
|
{
|
columns.Add(item.Key);
|
parameters.Add("@" + item.Key);
|
}
|
}
|
|
return string.Format(sql, tableName
|
, CollectionUti.Join(",", columns)
|
, CollectionUti.Join(",", parameters));
|
}
|
#endregion
|
|
#region CreateInsertDbCommand
|
/// <summary>
|
/// 创建Insert的DbCommand
|
/// </summary>
|
/// <param name="entity">实体</param>
|
/// <param name="tableName">表名</param>
|
/// <param name="dal">相关的DAL</param>
|
/// <param name="cmd">返回Command</param>
|
/// <returns>是否成功</returns>
|
/// <remarks>会在赋值时，检测值是否大于数据库的字段长度。如果非法插入赋值，就会抛出CheckInvalidValueException异常</remarks>
|
public static SqlExecuteResult CreateInsertDbCommand(BaseEntity entity, string tableName
|
, BaseDAL dal, out DbCommand cmd)
|
{
|
SqlExecuteResult result = null;
|
cmd = dal.DataAccess.GetSqlStringCommand(CreateInsertSql(tableName, dal.DataAccess));
|
|
//设置参数
|
var schema = DAOManager.LoadTableSchema(tableName, dal.DataAccess);
|
|
List<BaseDAL.CommandMapper> mappers = new List<BaseDAL.CommandMapper>();
|
foreach (var item in schema)
|
{
|
mappers.Add(new BaseDAL.CommandMapper(item.Key));
|
}
|
|
var checkInvalids = dal.SetCommandValueWithCheck(entity, mappers, cmd, tableName);
|
|
if (CollectionUti.HasItem(checkInvalids))
|
{
|
result = SqlExecuteResult.Create(checkInvalids);
|
}
|
|
return result;
|
}
|
#endregion
|
|
#region CreateDynamicSearchDbCommand
|
/// <summary>
|
/// 动态查询字段信息
|
/// </summary>
|
public class DynamicSearchColumnInfo
|
{
|
/// <summary>
|
/// 构造函数
|
/// </summary>
|
public DynamicSearchColumnInfo()
|
{
|
}
|
|
private string oper;
|
/// <summary>
|
/// 操作符
|
/// </summary>
|
/// <remarks>默认如果字符串遇到以%开头或结尾的，就用操作符Like；其他的用=</remarks>
|
public string Operator
|
{
|
get
|
{
|
if (!string.IsNullOrEmpty(oper))
|
{
|
return oper;
|
}
|
if(Value == null)
|
{
|
return "=";
|
}
|
string str = Value.ToString();
|
return (!string.IsNullOrEmpty(str) && (str.StartsWith("%") || str.EndsWith("%"))) ? "LIKE" : "=";
|
}
|
set
|
{
|
oper = value;
|
}
|
}
|
|
/// <summary>
|
/// 字段名
|
/// </summary>
|
public string ColumnName { get; set; }
|
|
/// <summary>
|
/// 查询值
|
/// </summary>
|
public object Value { get; set; }
|
|
/// <summary>
|
/// 字段名前缀
|
/// </summary>
|
public string Prefix { get; set; }
|
|
private string whereString;
|
/// <summary>
|
/// 直接Where字符串
|
/// </summary>
|
/// <remarks>如果设置了，则ColumnName设置无效，只做parameterName的作用</remarks>
|
public string WhereString
|
{
|
get
|
{
|
return this.whereString;
|
}
|
set
|
{
|
whereString = value;
|
}
|
}
|
}
|
|
/// <summary>
|
/// 拼凑动态查询Sql
|
/// </summary>
|
/// <param name="sqlMainSql">Sql主句，用“{0}”标识组合条件放置的地方。如果没有，则直接拼接Where条件语句到语句最后</param>
|
/// <param name="colInfos">搜索栏位信息</param>
|
/// <param name="db">数据库访问工具类</param>
|
/// <returns>Sql命令对象</returns>
|
public static DbCommand CreateDynamicSearchDbCommand(string sqlMainSql
|
, List<DynamicSearchColumnInfo> colInfos, Database db)
|
{
|
//先创建一个临时的DbCommand
|
DbCommand cmd = db.GetSqlStringCommand(sqlMainSql);
|
|
//如果所有值都为空，不需要添加条件，会默认添加一个“1=1”的条件进去。
|
string whereStr = " 1=1 ";
|
|
if (CollectionUti.HasItem(colInfos))
|
{
|
List<string> cacheParameters = new List<string>();
|
|
List<string> whereStrs = new List<string>();
|
foreach (var item in colInfos)
|
{
|
DbType? dbType = GetDbType(item.Value);
|
if (dbType.HasValue)
|
{
|
//查找ParaName
|
string paraName = GetParameterName(item.ColumnName, cacheParameters);
|
|
if (string.IsNullOrEmpty(item.WhereString))
|
{
|
//自动创建Where条件语句
|
|
//添加rtrim，避免char类型形成的空格导致查询不匹配
|
string trimFunction = (dbType.Value == DbType.Int32
|
|| dbType.Value == DbType.Decimal
|
|| dbType.Value == DbType.Int64
|
|| dbType.Value == DbType.Double) ? "" : "RTRIM";
|
|
if (string.IsNullOrEmpty(item.Prefix))
|
{
|
whereStrs.Add(string.Format(" {3}([{0}]) {1} @{2}"
|
, item.ColumnName, item.Operator, paraName, trimFunction));
|
}
|
else
|
{
|
whereStrs.Add(string.Format(" {4}({2}.[{0}]) {1} @{3}"
|
, item.ColumnName, item.Operator, item.Prefix, paraName, trimFunction));
|
}
|
}
|
else
|
{
|
//自定义的条件语句
|
whereStrs.Add(item.WhereString);
|
}
|
|
db.AddInParameter(cmd, paraName, dbType.Value, item.Value);
|
}
|
}
|
|
cacheParameters.Clear();
|
|
if (whereStrs.Count > 0)
|
{
|
whereStr = CollectionUti.Join(" AND ", whereStrs);
|
whereStrs.Clear();
|
}
|
|
//拼凑Sql
|
if (sqlMainSql.IndexOf("{0}") < 0)
|
{
|
//如果没有{0}，自动加上Where {0}
|
sqlMainSql += " WHERE {0} ";
|
}
|
}
|
|
//重新设置Command
|
cmd.CommandText = string.Format(sqlMainSql, whereStr);
|
|
return cmd;
|
}
|
|
/// <summary>
|
/// 根据字段名获取对应的参数名称
|
/// </summary>
|
/// <param name="columnName">字段名称</param>
|
/// <param name="cacheParameter">参数缓存列表，避免同样的字段返回同样的参数名称</param>
|
/// <returns>参数名称</returns>
|
private static string GetParameterName(string columnName, List<string> cacheParameter)
|
{
|
if (!cacheParameter.Contains(columnName))
|
{
|
cacheParameter.Add(columnName);
|
return columnName;
|
}
|
else
|
{
|
//参数名後面+1
|
string paraName = columnName + (cacheParameter.FindAll(_str => { return _str == columnName; }).Count + 1).ToString();
|
cacheParameter.Add(columnName);
|
return paraName;
|
}
|
}
|
|
private static DbType? GetDbType(object val)
|
{
|
DbType? dbType = default(DbType?);
|
if (val != null && !string.IsNullOrEmpty(val.ToString()))
|
{
|
if (val is int || val is int?)
|
{
|
dbType = DbType.Int32;
|
}
|
else if (val is decimal || val is decimal?)
|
{
|
dbType = DbType.Decimal;
|
}
|
else if (val is double || val is double?)
|
{
|
dbType = DbType.Double;
|
}
|
else if (val is long || val is long?)
|
{
|
dbType = DbType.Int64;
|
}
|
else if (val is DateTime || val is DateTime?)
|
{
|
dbType = DbType.DateTime;
|
}
|
else
|
{
|
dbType = DbType.String;
|
}
|
}
|
return dbType;
|
}
|
#endregion
|
}
|
}
|
