/// <summary>
/// Insert语句生成
/// </summary>
/// <typeparam name="T">Model泛型</typeparam>
/// <typeparam name="P">sql参数类型，SqlParameter/OracleParameter</typeparam>
/// <param name="model">实例</param>
/// <param name="parameterList">参数数组</param>
/// <returns>Insert语句</returns>
public string InsertSqlGenerate <T>(T model, ref List <DbParameter> parameterList)
{
string cacheSQL = SQLDataCache.GetHashTableData(model, CRUDEnum.INSERT);
string result = string.Empty;
if (!string.IsNullOrEmpty(cacheSQL))
{
result = cacheSQL;
}
else
{
result = "insert into {0}({2}) values({1})";
result = string.Format(result, TableGenerator.Table <T>(), ParameterGenerator.ParameterString <T>(), TableGenerator.AllColumn <T>());
}
List <DbParameter> paramList = SQLDataCache.GetParameterCacheData <DbParameter>(model);;
if (paramList == null)
{
parameterList = ParameterGenerator.ParametersArray <T>(model);
SQLDataCache.ModelParameterPush <DbParameter>(model, parameterList);
}
else
{
parameterList.AddRange(paramList.ToArray());
ParameterGenerator.ModelValueToParamListPush(parameterList, model);
}
return(result);
}
/// <summary>
/// 生成更新语句
/// </summary>
/// <typeparam name="T">泛型T Model</typeparam>
/// <param name="model">实例</param>
/// <param name="parameterList">参数数组</param>
/// <returns></returns>
/// <remarks>
/// eg:UPDATE USERS SET NAME = @NAME WHERE ID = @ID
/// </remarks>
public string UpdateSqlGenerate <T>(T model, ref List <DbParameter> parameterList)
{
string cacheSQL = SQLDataCache.GetHashTableData(model, CRUDEnum.UDPATE);
string result = string.Empty;
if (!string.IsNullOrEmpty(cacheSQL))
{
result = cacheSQL;
}
else
{
result = "update {0} set {1} where {2} = {3}";
result = string.Format(result, TableGenerator.Table <T>(), ParameterGenerator.ParameterStringWithoutKey <T>(), ParameterGenerator.KeyString <T>(), ParameterGenerator.KeyParameterString <T>());
SQLDataCache.ModelCURDHandleSQLPush(model, CRUDEnum.UDPATE, result);
}
List <DbParameter> paramList = SQLDataCache.GetParameterCacheData <DbParameter>(model);
if (paramList == null)
{
parameterList = ParameterGenerator.ParametersArray <T>(model);
SQLDataCache.ModelParameterPush <DbParameter>(model, parameterList);
}
else
{
parameterList.AddRange(paramList.ToArray());
ParameterGenerator.ModelValueToParamListPush(parameterList, model);
}
return(result);
}
/// <summary>
/// 生成FindByKey查询语句 SELECT * FROM {0} WHERE {1} = {2}
/// </summary>
/// <typeparam name="T">泛型T Model</typeparam>
/// <typeparam name="P">sql参数，SqlParameter/OracleParameter</typeparam>
/// <param name="model">实例</param>
/// <param name="parameterList">参数数组</param>
/// <returns>SELECT * FROM {0} WHERE {1} = {2}</returns>
public string SelectBykeySqlGenerate <T>(T model, ref DbParameter parameter)
{
string cacheSQL = SQLDataCache.GetHashTableData(model, CRUDEnum.GETBYID);
string result = string.Empty;
if (!string.IsNullOrEmpty(cacheSQL))
{
result = cacheSQL;
}
else
{
result = "select * from {0} where {1} = {2}";
result = string.Format(result, TableGenerator.Table <T>(), ParameterGenerator.KeyString <T>(), ParameterGenerator.KeyParameterString <T>());
SQLDataCache.ModelCURDHandleSQLPush(model, CRUDEnum.GETBYID, result);
}
List <DbParameter> paramList = SQLDataCache.GetParameterCacheData <DbParameter>(model);
parameter = paramList != null?paramList.ToArray()[0] : ParameterGenerator.KeyParameterArray <T>(model);
// 从缓存中取出后需对键列重新赋值
parameter.Value = model.GetType().GetProperty(parameter.ParameterName).GetValue(model, null);
//SQLDataCache.ModelParameterPush<DbParameter>(model, parameter);
return(result);
}
/// <summary>
/// 生成查询全表记录数语句 SELECT COUNT(0) FROM TABLE WHERE 1=1
/// </summary>
/// <typeparam name="T">泛型T Model</typeparam>
/// <returns>SELECT COUNT(0) FROM TABLE1 WHERE 1=1</returns>
public string CountSqlGenerate <T>() where T : new()
{
object model = new T();
string cacheSQL = SQLDataCache.GetHashTableData(model, CRUDEnum.COUNT);
string result = string.Empty;
if (!string.IsNullOrEmpty(cacheSQL))
{
result = cacheSQL;
}
else
{
result = "select count(0) from {0} where 1=1";
result = string.Format(result, TableGenerator.Table <T>());
SQLDataCache.ModelCURDHandleSQLPush(model, CRUDEnum.COUNT, result);
}
return(result);
}
/// <summary>
/// 生成删除语句
/// </summary>
/// <typeparam name="T">泛型T Model</typeparam>
/// <param name="model">实例</param>
/// <param name="parameter">参数数组</param>
/// <returns></returns>
public string DeleteSqlGenerate <T>(T model, ref DbParameter parameter)
{
string cacheSQL = SQLDataCache.GetHashTableData(model, CRUDEnum.DELETE);
string result = string.Empty;
if (!string.IsNullOrEmpty(cacheSQL))
{
result = cacheSQL;
}
else
{
result = "delete from {0} where {1} = {2}";
result = string.Format(result, TableGenerator.Table <T>(), ParameterGenerator.ParameterString <T>(), TableGenerator.AllColumn <T>());
}
List <DbParameter> paramList = SQLDataCache.GetParameterCacheData <DbParameter>(model);
parameter = paramList != null?paramList.ToArray()[0] : ParameterGenerator.KeyParameterArray <T>(model);
// 从缓存中取出后需对键列重新赋值
parameter.Value = model.GetType().GetProperty(parameter.ParameterName).GetValue(model, null);
return(result);
}