///*************************************************************************/
|
///*
|
///* 文件名 ：GenerateSqlCmdBase.cs
|
///*
|
///* 程序说明 : SQL命令生成器基类
|
///* 原创作者 ：孙中吕
|
///*
|
///* Copyright 2006-2021 C/S框架网 www.csframework.com
|
///*
|
///**************************************************************************/
|
|
using CSFrameworkV5.Core.Common;
|
using System;
|
using System.Collections;
|
using System.Data;
|
using System.Data.Common;
|
using System.Text;
|
|
namespace CSFrameworkV5.Core.CodeGenerator
|
{
|
/// <summary>
|
/// 当SQL命令生成器生成代码后触发的事件
|
/// </summary>
|
/// <param name="insertSQL">已生成插入记录的SQL</param>
|
/// <param name="deleteSQL">已生成删除记录的SQL</param>
|
/// <param name="updateSQL">已生成更新记录的SQL</param>
|
public delegate void GeneratedSQLEventHandle(string insertSQL,
|
string deleteSQL, string updateSQL);
|
|
/// <summary>
|
/// SQL命令生成器基类
|
/// </summary>
|
public class GenerateSqlCmdBase : IGenerateSqlCommand
|
{
|
protected ICommandFactory _Factory;
|
|
public GenerateSqlCmdBase(ICommandFactory factory)
|
{
|
_Factory = factory;
|
}
|
|
//Insert SQL语句
|
protected string _sqlInsert = string.Empty;
|
|
//Update SQL语句
|
protected string _sqlUpdate = string.Empty;
|
|
//Delete SQL语句
|
protected string _sqlDelete = string.Empty;
|
|
//Insert SQL命令
|
protected DbCommand _cmdInsert = null;
|
|
//Update SQL命令
|
protected DbCommand _cmdUpdate = null;
|
|
//Delete SQL命令
|
protected DbCommand _cmdDelete = null;
|
|
/// <summary>
|
/// 单号字段名
|
/// </summary>
|
protected string _DocNoFieldName = string.Empty;
|
|
/// <summary>
|
/// 主键字段名,复合主键用分号";"分开.
|
/// </summary>
|
protected string _PrimaryFieldName = string.Empty;
|
|
/// <summary>
|
/// 外键字段名,复合外键用分号";"分开.
|
/// </summary>
|
protected string _ForeignFieldName = string.Empty;
|
|
/// <summary>
|
/// 是否主表
|
/// </summary>
|
protected bool _IsSummary = true;
|
|
/// <summary>
|
/// 是否使用并发功能的表
|
/// </summary>
|
/// <returns></returns>
|
public bool IsConcurrentTable()
|
{
|
return _ConcurrentAttribute != null;
|
}
|
|
protected ORM_ConcurrentAttribute _ConcurrentAttribute = null;
|
|
/// <summary>
|
/// 获取单号字段名
|
/// </summary>
|
/// <returns></returns>
|
public string GetDocNoFieldName()
|
{
|
return _DocNoFieldName;
|
}
|
|
/// <summary>
|
/// 获取主键字段名
|
/// </summary>
|
/// <returns></returns>
|
public string GetPrimaryFieldName()
|
{
|
return _PrimaryFieldName;
|
}
|
|
/// <summary>
|
/// 获取外键字段名
|
/// </summary>
|
/// <returns></returns>
|
public string GetForeignFieldName()
|
{
|
return _ForeignFieldName;
|
}
|
|
/// <summary>
|
/// 是否主表
|
/// </summary>
|
/// <returns></returns>
|
public bool IsSummary()
|
{
|
return _IsSummary;
|
}
|
|
/// <summary>
|
/// 生成插入记录的SQL命令
|
/// </summary>
|
/// <param name="tran">事务</param>
|
/// <returns></returns>
|
public DbCommand GetInsertCommand(DbTransaction tran)
|
{
|
_cmdInsert.CommandText = CodeSafeHelper.GetSafeSQL(_sqlInsert);
|
_cmdInsert.Connection = tran.Connection;
|
_cmdInsert.Transaction = tran;
|
return _cmdInsert;
|
}
|
|
/// <summary>
|
/// 生成更新记录的SQL命令
|
/// </summary>
|
/// <param name="tran"></param>
|
/// <returns></returns>
|
public DbCommand GetUpdateCommand(DbTransaction tran)
|
{
|
_cmdUpdate.CommandText = CodeSafeHelper.GetSafeSQL(_sqlUpdate);
|
_cmdUpdate.Connection = tran.Connection;
|
_cmdUpdate.Transaction = tran;
|
return _cmdUpdate;
|
}
|
|
|
/// <summary>
|
/// 生成删除记录的SQL命令
|
/// </summary>
|
/// <param name="tran"></param>
|
/// <returns></returns>
|
public DbCommand GetDeleteCommand(DbTransaction tran)
|
{
|
_cmdDelete.CommandText = CodeSafeHelper.GetSafeSQL(_sqlDelete);
|
_cmdDelete.Connection = tran.Connection;
|
_cmdDelete.Transaction = tran;
|
return _cmdDelete;
|
}
|
|
#region 生成SQL语句
|
|
/// <summary>
|
/// 生成插入记录用SQL语句
|
/// </summary>
|
/// <param name="tableName">表名</param>
|
/// <param name="keyName">主键</param>
|
/// <param name="fields">字段列表</param>
|
/// <returns></returns>
|
public string GenerateInsertSql(string tableName, string keyName,
|
IList fields)
|
{
|
try
|
{
|
var sb = new StringBuilder();
|
sb.Append("INSERT INTO " + tableName + " ( ");
|
foreach (ColumnProperty colProper in fields)
|
if (fields.IndexOf(colProper) < fields.Count - 1)
|
sb.Append(colProper.ColumnName + ",");
|
else
|
sb.Append(colProper.ColumnName);
|
sb.Append(" ) VALUES ( ");
|
foreach (ColumnProperty colProper in fields)
|
if (fields.IndexOf(colProper) < fields.Count - 1)
|
sb.Append(_Factory.ParamSymboName +
|
colProper.ColumnName + ",");
|
else
|
sb.Append(
|
_Factory.ParamSymboName + colProper.ColumnName);
|
sb.Append(" )");
|
return sb.ToStringEx();
|
}
|
catch
|
{
|
return string.Empty;
|
}
|
}
|
|
/// <summary>
|
/// 生成更新记录用SQL语句
|
/// </summary>
|
/// <param name="tableName">表名</param>
|
/// <param name="keyName">主键</param>
|
/// <param name="fields">字段列表</param>
|
/// <returns></returns>
|
public string GenerateUpdateSql(string tableName, string keyName,
|
IList fields)
|
{
|
//生成SQL格式(标准)：UPDATE TABLE_NAME SET F1=@F1 WHERE KEY=@KEY
|
//生成SQL格式(控制并发)：UPDATE TABLE_NAME SET F1=@F1 WHERE KEY=@KEY AND TS=@TS
|
//生成SQL格式(复合主键)：UPDATE TABLE_NAME SET F1=@F1 WHERE KEY1=@KEY1 AND KEY2=@KEY2
|
try
|
{
|
var isConcurrent = false;
|
var sb = new StringBuilder();
|
sb.Append("UPDATE " + tableName + " SET ");
|
foreach (ColumnProperty colProper in fields)
|
{
|
//是否并发字段
|
isConcurrent = IsConcurrentTable() &&
|
_ConcurrentAttribute.TimestampFieldName ==
|
colProper.ColumnName;
|
|
//主键及并发字段不拼接SQL脚本
|
if (colProper.IsPrimaryKey || isConcurrent) continue;
|
|
if (fields.IndexOf(colProper) == fields.Count - 1) //最后一个参数
|
sb.Append(colProper.ColumnName + "=" +
|
_Factory.ParamSymboName +
|
colProper.ColumnName);
|
else
|
sb.Append(colProper.ColumnName + "=" +
|
_Factory.ParamSymboName +
|
colProper.ColumnName + ",");
|
}
|
|
//删除最后面多余的调号
|
sb = new StringBuilder(sb.ToStringEx().TrimEnd(','));
|
|
//添加Where条件：记录主键
|
sb.Append(" WHERE 1=1 " + SplitKeyName(keyName)); //主键
|
|
//添加并发字段的WHERE 条件
|
if (IsConcurrentTable())
|
sb.Append(" AND " + GetConncurrentKeyName()); //并发
|
|
return sb.ToStringEx();
|
}
|
catch
|
{
|
return string.Empty;
|
}
|
}
|
|
/// <summary>
|
/// 生成删除记录用SQL语句
|
/// </summary>
|
/// <param name="tableName">表名</param>
|
/// <param name="keyName">主键</param>
|
/// <param name="field">字段列表</param>
|
/// <returns></returns>
|
public string GenerateDeleteSql(string tableName, string keyName)
|
{
|
try
|
{
|
var sb = new StringBuilder();
|
sb.Append("DELETE FROM " + tableName + " WHERE 1=1 " +
|
SplitKeyName(keyName)); //复合主键处理
|
return sb.ToStringEx();
|
}
|
catch
|
{
|
return string.Empty;
|
}
|
}
|
|
#endregion
|
|
/// <summary>
|
/// 复合主键处理
|
/// </summary>
|
/// <param name="keyName">字段名，多个字段用分号分开</param>
|
/// <returns></returns>
|
protected virtual string SplitKeyName(string keyName)
|
{
|
var strBuilder = new StringBuilder();
|
var arrayStr = keyName.Split(';');
|
foreach (var tempKeyName in arrayStr)
|
{
|
if (tempKeyName == "") continue;
|
strBuilder.Append(" AND " + tempKeyName + "=" +
|
_Factory.ParamSymboName + tempKeyName);
|
}
|
|
return strBuilder.ToStringEx();
|
}
|
|
/// <summary>
|
/// 取并发操作的字段名
|
/// </summary>
|
/// <returns></returns>
|
protected virtual string GetConncurrentKeyName()
|
{
|
if (IsConcurrentTable())
|
return _ConcurrentAttribute.TimestampFieldName + "=" +
|
_Factory.ParamSymboName +
|
_ConcurrentAttribute.TimestampFieldName;
|
else
|
return "";
|
}
|
}
|
}
|
