968 lines
43 KiB
C#
968 lines
43 KiB
C#
using System;
|
||
using System.Collections.Generic;
|
||
using System.Text;
|
||
using CYQ.Data.Table;
|
||
using System.Data;
|
||
using CYQ.Data.Tool;
|
||
using System.Data.Common;
|
||
using CYQ.Data.UI;
|
||
|
||
|
||
namespace CYQ.Data.SQL
|
||
{
|
||
/// <summary>
|
||
/// 数据操作语句类
|
||
/// </summary>
|
||
internal partial class SqlCreate
|
||
{
|
||
private static List<string> _autoidItems = new List<string>();
|
||
/// <summary>
|
||
/// oracle序列名称
|
||
/// </summary>
|
||
internal string AutoID
|
||
{
|
||
get
|
||
{
|
||
string key = string.Format(AppConfig.DB.AutoID, TableName);
|
||
if (!_autoidItems.Contains(key))
|
||
{
|
||
//检测并自动创建。
|
||
Tool.DBTool.CheckAndCreateOracleSequence(key, _action.dalHelper.ConnName, _action.Data.PrimaryCell.ColumnName, TableName);
|
||
_autoidItems.Add(key);
|
||
}
|
||
return key;
|
||
}
|
||
}
|
||
/// <summary>
|
||
/// 是否允许执行SQL操作（仅对Insert和Update有效；如果SQL语法错误，则拒绝执行）
|
||
/// </summary>
|
||
internal bool isCanDo = true;
|
||
/// <summary>
|
||
/// 更新操作的附加表达式。
|
||
/// </summary>
|
||
internal string updateExpression = null;
|
||
/// <summary>
|
||
/// 指定查询的列。
|
||
/// </summary>
|
||
internal object[] selectColumns = null;
|
||
private string TableName
|
||
{
|
||
get
|
||
{
|
||
return SqlFormat.Keyword(_action.TableName, _action.DataBaseType);
|
||
}
|
||
}
|
||
private MAction _action;
|
||
internal SqlCreate(MAction action)
|
||
{
|
||
_action = action;
|
||
}
|
||
|
||
#region 组合Sql语句
|
||
#region SQL语句处理
|
||
internal string GetDeleteToUpdateSql(object whereObj)
|
||
{
|
||
string editTime = GetEditTimeSql();
|
||
return "update " + SqlFormat.Keyword(TableName, _action.dalHelper.DataBaseType) + " set " + editTime + SqlFormat.Keyword(AppConfig.DB.DeleteField, _action.dalHelper.DataBaseType) + "=[#TRUE] where " + FormatWhere(whereObj);
|
||
}
|
||
private string GetEditTimeSql()
|
||
{
|
||
string editTime = AppConfig.DB.EditTimeFields;
|
||
if (!string.IsNullOrEmpty(editTime))
|
||
{
|
||
foreach (string item in editTime.Split(','))
|
||
{
|
||
if (!string.IsNullOrEmpty(item.Trim()))
|
||
{
|
||
if (_action.Data.Columns.Contains(item) && (_action.Data[item].IsNullOrEmpty || _action.Data[item].State < 2))
|
||
{
|
||
return SqlFormat.Keyword(item, _action.dalHelper.DataBaseType) + "='" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "',";//如果存在更新列
|
||
}
|
||
}
|
||
}
|
||
}
|
||
return string.Empty;
|
||
}
|
||
internal string GetDeleteSql(object whereObj)
|
||
{
|
||
return "delete from " + SqlFormat.Keyword(TableName, _action.dalHelper.DataBaseType) + " where " + FormatWhere(whereObj);
|
||
}
|
||
/// <summary>
|
||
/// 返回插入的字符串
|
||
/// </summary>
|
||
/// <returns>结果如:insert into tableName(id,Name,Value) values(@id,@Name,@Value)</returns>
|
||
internal string GetInsertSql()
|
||
{
|
||
isCanDo = false;
|
||
StringBuilder _TempSql = new StringBuilder();
|
||
StringBuilder _TempSql2 = new StringBuilder();
|
||
_TempSql.Append("insert into " + SqlFormat.Keyword(TableName, _action.dalHelper.DataBaseType) + "(");
|
||
_TempSql2.Append(" Values(");
|
||
string endChar = ")";
|
||
MDataCell primaryCell = _action.Data[_action.Data.Columns.FirstPrimary.ColumnName];
|
||
DataGroupType group = DataType.GetGroup(primaryCell.Struct.SqlType);
|
||
string defaultValue = Convert.ToString(primaryCell.Struct.DefaultValue);
|
||
if (primaryCell.IsNullOrEmpty && (group == DataGroupType.Guid || (group == 0 && (primaryCell.Struct.MaxSize <= 0 || primaryCell.Struct.MaxSize >= 36) &&
|
||
(defaultValue == "" || defaultValue == "newid" || defaultValue == SqlValue.Guid))))//guid类型
|
||
{
|
||
primaryCell.Value = Guid.NewGuid();
|
||
}
|
||
MDataCell cell = null;
|
||
bool insertAutoIncrement = false;
|
||
for (int i = 0; i < _action.Data.Count; i++)
|
||
{
|
||
cell = _action.Data[i];
|
||
if (cell.Struct.IsAutoIncrement)
|
||
{
|
||
if (!_action.AllowInsertID) continue;//跳过自增列。
|
||
else insertAutoIncrement = true;
|
||
}
|
||
if (cell.IsNull && !cell.Struct.IsCanNull && cell.Struct.DefaultValue == null)
|
||
{
|
||
string err = AppConst.HR + string.Format("error : {0} {1} can't be insert null", TableName, cell.ColumnName) + AppConst.BR;
|
||
Log.Write(err, LogType.DataBase);
|
||
_action.dalHelper.DebugInfo.Append(err);
|
||
_action.dalHelper.RecordsAffected = -2;
|
||
isCanDo = false;
|
||
break;
|
||
}
|
||
if (cell.State > 0)
|
||
{
|
||
_TempSql.Append(SqlFormat.Keyword(cell.ColumnName, _action.DataBaseType) + ",");
|
||
if (_action.DataBaseType == DataBaseType.MsSql && cell.Struct.SqlTypeName != null && cell.Struct.SqlTypeName.EndsWith("hierarchyId"))
|
||
{
|
||
_TempSql2.Append("HierarchyID::Parse('" + cell.StringValue + "')");
|
||
}
|
||
else
|
||
{
|
||
if (_action.DataBaseType == DataBaseType.FoxPro)
|
||
{
|
||
string value = "\"" + cell.StringValue + "\",";
|
||
if (cell.Struct.SqlType == SqlDbType.DateTime)
|
||
{
|
||
value = "{^" + cell.StringValue + "},";
|
||
}
|
||
//不支持参数化
|
||
_TempSql2.Append(value);
|
||
}
|
||
else
|
||
{
|
||
object value = cell.Value;
|
||
DbType dbType = DataType.GetDbType(cell.Struct.SqlType.ToString(), _action.DataBaseType);
|
||
if (dbType == DbType.String && cell.StringValue == "")
|
||
{
|
||
if (_action.DataBaseType == DataBaseType.Oracle && !cell.Struct.IsCanNull)
|
||
{
|
||
value = " ";//Oracle not null 字段，不允许设置空值。
|
||
}
|
||
if (_action.DataBaseType == DataBaseType.MySql && cell.Struct.MaxSize == 36)
|
||
{
|
||
value = DBNull.Value;//MySql 的char36 会当成guid处理，不能为空，只能为null。
|
||
}
|
||
}
|
||
_TempSql2.Append(_action.dalHelper.Pre + cell.ColumnName + ",");
|
||
_action.dalHelper.AddParameters(_action.dalHelper.Pre + cell.ColumnName, value, dbType, cell.Struct.MaxSize, ParameterDirection.Input);
|
||
}
|
||
}
|
||
isCanDo = true;
|
||
}
|
||
}
|
||
switch (_action.dalHelper.DataBaseType)
|
||
{
|
||
case DataBaseType.Oracle:
|
||
if (!_action.AllowInsertID && DataType.GetGroup(primaryCell.Struct.SqlType) == DataGroupType.Number)
|
||
{
|
||
_TempSql.Append(primaryCell.ColumnName + ",");
|
||
_TempSql2.Append(AutoID + ".nextval,");
|
||
}
|
||
break;
|
||
case DataBaseType.PostgreSQL:
|
||
if (_action.AllowInsertID && primaryCell.Struct.IsAutoIncrement && !primaryCell.IsNullOrEmpty)
|
||
{
|
||
endChar = ") OVERRIDING SYSTEM VALUE ";
|
||
}
|
||
break;
|
||
}
|
||
|
||
string sql = _TempSql.ToString().TrimEnd(',') + endChar + _TempSql2.ToString().TrimEnd(',') + ")";
|
||
switch (_action.dalHelper.DataBaseType)
|
||
{
|
||
case DataBaseType.PostgreSQL:
|
||
if (primaryCell.Struct.IsAutoIncrement && !_action.AllowInsertID && group == DataGroupType.Number)
|
||
{
|
||
string key = Convert.ToString(primaryCell.Struct.DefaultValue);
|
||
if (!string.IsNullOrEmpty(key))
|
||
{
|
||
key = key.Replace("nextval", "currval");//nextval('sequence_name'::regclass);||nextval('"sequence_name"'::regclass);
|
||
string[] items = key.Split('\'');
|
||
key = items[0] + "'" + SqlFormat.Keyword(items[1].Trim('\"'), DataBaseType.PostgreSQL) + "'" + items[2];
|
||
sql = sql + "; select " + key + " as OutPutValue";
|
||
}
|
||
else
|
||
{
|
||
sql = sql + " RETURNING " + SqlFormat.Keyword(primaryCell.ColumnName, DataBaseType.PostgreSQL);
|
||
}
|
||
}
|
||
else if (!primaryCell.IsNullOrEmpty)
|
||
{
|
||
sql += string.Format("; select '{0}' as OutPutValue", SqlFormat.Keyword(primaryCell.StringValue, DataBaseType.PostgreSQL));
|
||
}
|
||
break;
|
||
case DataBaseType.DaMeng:
|
||
if (primaryCell.Struct.IsAutoIncrement && !_action.AllowInsertID && group == DataGroupType.Number)
|
||
{
|
||
sql = sql + " ;select @@IDENTITY as OutPutValue;";
|
||
}
|
||
break;
|
||
case DataBaseType.MsSql:
|
||
case DataBaseType.Sybase:
|
||
if (primaryCell.Struct.IsAutoIncrement && !_action.AllowInsertID && group == DataGroupType.Number)
|
||
{
|
||
if (_action.dalHelper.DataBaseType == DataBaseType.Sybase)
|
||
{
|
||
sql = sql + " select @@IDENTITY as OutPutValue";
|
||
}
|
||
else if (_action.dalHelper.DataBaseType == DataBaseType.MsSql)
|
||
{
|
||
sql += " select cast(scope_identity() as bigint) as OutPutValue";//改成bigint避免转换数据溢出
|
||
}
|
||
}
|
||
else if (!primaryCell.IsNullOrEmpty)
|
||
{
|
||
sql += string.Format(" select '{0}' as OutPutValue", primaryCell.Value);
|
||
}
|
||
if (insertAutoIncrement)//_action.AllowInsertID && !_action.dalHelper.IsOpenTrans && primaryCell.Struct.IsAutoIncrement)//非批量操作时
|
||
{
|
||
sql = "set identity_insert " + SqlFormat.Keyword(TableName, _action.dalHelper.DataBaseType) + " on " + sql + " set identity_insert " + SqlFormat.Keyword(TableName, _action.dalHelper.DataBaseType) + " off";
|
||
}
|
||
break;
|
||
//if (!(Parent.AllowInsertID && !primaryCell.IsNull)) // 对于自行插入id的，跳过，主操作会自动返回id。
|
||
//{
|
||
// sql += ((groupID == 1 && (primaryCell.IsNull || primaryCell.ToString() == "0")) ? " select cast(scope_identity() as int) as OutPutValue" : string.Format(" select '{0}' as OutPutValue", primaryCell.Value));
|
||
//}
|
||
//case DalType.Oracle:
|
||
// sql += string.Format("BEGIN;select {0}.currval from dual; END;", Autoid);
|
||
// break;
|
||
}
|
||
return sql;
|
||
}
|
||
/// <summary>
|
||
/// 返回不包括Where条件的字符串
|
||
/// </summary>
|
||
/// <returns>结果如:Update tableName set Name=@Name,Value=@Value</returns>
|
||
internal string GetUpdateSql(object whereObj)
|
||
{
|
||
isCanDo = false;
|
||
StringBuilder _TempSql = new StringBuilder();
|
||
_TempSql.Append("Update " + SqlFormat.Keyword(TableName, _action.dalHelper.DataBaseType) + " set ");
|
||
if (!string.IsNullOrEmpty(updateExpression))
|
||
{
|
||
_TempSql.Append(SqlCompatible.Format(updateExpression, _action.DataBaseType) + ",");
|
||
updateExpression = null;//取完值后清除值。
|
||
isCanDo = true;
|
||
}
|
||
string editTime = GetEditTimeSql();//内部判断该字段没有值才会更新。
|
||
if (!string.IsNullOrEmpty(editTime))
|
||
{
|
||
_TempSql.Append(editTime);//自带尾,号
|
||
}
|
||
MDataCell cell = null;
|
||
for (int i = 0; i < _action.Data.Count; i++)
|
||
{
|
||
cell = _action.Data[i];
|
||
if (cell.Struct.IsPrimaryKey || cell.Struct.IsAutoIncrement)
|
||
{
|
||
continue;//跳过自增或主键列。
|
||
}
|
||
|
||
if (cell.State > 1 && (cell.Struct.IsCanNull || !cell.IsNull))
|
||
{
|
||
if (cell.Struct.SqlType == SqlDbType.Timestamp && (_action.DataBaseType == DataBaseType.MsSql || _action.DataBaseType == DataBaseType.Sybase))
|
||
{
|
||
//更新时间戳不允许更新。
|
||
continue;
|
||
}
|
||
if (_action.DataBaseType == DataBaseType.MsSql && cell.Struct.SqlTypeName != null && cell.Struct.SqlTypeName.EndsWith("hierarchyId"))
|
||
{
|
||
_TempSql.Append(SqlFormat.Keyword(cell.ColumnName, _action.DataBaseType) + "=HierarchyID::Parse('" + cell.StringValue + "')" + ",");
|
||
}
|
||
else
|
||
{
|
||
if (_action.DataBaseType == DataBaseType.FoxPro)
|
||
{
|
||
string value = "\"" + cell.StringValue + "\",";
|
||
if (cell.Struct.SqlType == SqlDbType.DateTime)
|
||
{
|
||
value = "{^" + cell.StringValue + "},";
|
||
}
|
||
//不支持参数化
|
||
_TempSql.Append(SqlFormat.Keyword(cell.ColumnName, _action.DataBaseType) + "=" + value);
|
||
}
|
||
else
|
||
{
|
||
object value = cell.Value;
|
||
DbType dbType = DataType.GetDbType(cell.Struct.SqlType.ToString(), _action.DataBaseType);
|
||
if (dbType == DbType.String && cell.StringValue == "")
|
||
{
|
||
if (_action.DataBaseType == DataBaseType.Oracle && !cell.Struct.IsCanNull)
|
||
{
|
||
value = " ";//Oracle not null 字段，不允许设置空值。
|
||
}
|
||
if (_action.DataBaseType == DataBaseType.MySql && cell.Struct.MaxSize == 36)
|
||
{
|
||
value = DBNull.Value;//MySql 的char36 会当成guid处理，不能为空，只能为null。
|
||
}
|
||
}
|
||
_action.dalHelper.AddParameters(_action.dalHelper.Pre + cell.ColumnName, value, dbType, cell.Struct.MaxSize, ParameterDirection.Input);
|
||
_TempSql.Append(SqlFormat.Keyword(cell.ColumnName, _action.DataBaseType) + "=" + _action.dalHelper.Pre + cell.ColumnName + ",");
|
||
}
|
||
}
|
||
isCanDo = true;
|
||
}
|
||
}
|
||
if (!isCanDo)
|
||
{
|
||
string err = AppConst.HR + "warn : " + TableName + " can't find the data can be updated!";
|
||
Log.Write(err, LogType.Warn);
|
||
_action.dalHelper.DebugInfo.Append(err);
|
||
}
|
||
//switch (_action.dalHelper.dalType)
|
||
//{
|
||
// case DalType.Oracle:
|
||
// case DalType.SQLite:
|
||
// _TempSql = _TempSql.Replace("[", "").Replace("]", "");
|
||
// break;
|
||
// case DalType.MySql:
|
||
// _TempSql = _TempSql.Replace("[", "`").Replace("]", "`");
|
||
// break;
|
||
//}
|
||
_TempSql = _TempSql.Remove(_TempSql.Length - 1, 1);
|
||
_TempSql.Append(" where " + FormatWhere(whereObj));
|
||
return _TempSql.ToString();
|
||
}
|
||
internal string GetCountSql(object whereObj)
|
||
{
|
||
string where = FormatWhere(whereObj);
|
||
if (!string.IsNullOrEmpty(where))
|
||
{
|
||
where = " where " + where;
|
||
where = RemoveOrderBy(where);
|
||
}
|
||
|
||
return "select count(*) from " + SqlFormat.Keyword(TableName, _action.dalHelper.DataBaseType) + where;
|
||
}
|
||
internal string GetExistsSql(object whereObj)
|
||
{
|
||
return GetTopOneSql(whereObj, "1");
|
||
}
|
||
internal string GetTopOneSql(object whereObj)
|
||
{
|
||
return GetTopOneSql(whereObj, null);
|
||
}
|
||
private string GetTopOneSql(object whereObj, string customColumn)
|
||
{
|
||
string columnNames = !string.IsNullOrEmpty(customColumn) ? customColumn : GetColumnsSql();
|
||
switch (_action.dalHelper.DataBaseType)
|
||
{
|
||
case DataBaseType.Txt:
|
||
case DataBaseType.Xml:
|
||
case DataBaseType.Sybase:
|
||
//return "set rowcount 1 select " + columnNames + " from " + TableName + " where " + FormatWhere(whereObj) + " set rowcount 0";
|
||
case DataBaseType.MsSql:
|
||
case DataBaseType.Access:
|
||
case DataBaseType.Excel:
|
||
case DataBaseType.DaMeng:
|
||
case DataBaseType.KingBaseES:
|
||
return "select top 1 " + columnNames + " from " + SqlFormat.Keyword(TableName, _action.dalHelper.DataBaseType) + " where " + FormatWhere(whereObj);
|
||
case DataBaseType.FoxPro://需要有order by
|
||
return "select top 1 " + columnNames + " from " + SqlFormat.Keyword(TableName, _action.dalHelper.DataBaseType) + " where " + AddOrderByWithCheck(FormatWhere(whereObj), _action.Data.PrimaryCell.ColumnName);
|
||
case DataBaseType.Oracle:
|
||
return "select " + columnNames + " from " + SqlFormat.Keyword(TableName, _action.dalHelper.DataBaseType) + " where rownum=1 and " + FormatWhere(whereObj);
|
||
case DataBaseType.SQLite:
|
||
case DataBaseType.MySql:
|
||
case DataBaseType.PostgreSQL:
|
||
return "select " + columnNames + " from " + SqlFormat.Keyword(TableName, _action.dalHelper.DataBaseType) + " where " + FormatWhere(whereObj) + " limit 1";
|
||
case DataBaseType.FireBird:
|
||
return "select first 1 " + columnNames + " from " + SqlFormat.Keyword(TableName, _action.dalHelper.DataBaseType) + " where " + FormatWhere(whereObj);
|
||
}
|
||
return (string)Error.Throw(string.Format("GetTopOneSql:{0} No Be Support Now!", _action.dalHelper.DataBaseType.ToString()));
|
||
}
|
||
internal string GetBindSql(object whereObj, object text, object value)
|
||
{
|
||
if (whereObj != null && Convert.ToString(whereObj).Length > 0)
|
||
{
|
||
whereObj = " where " + FormatWhere(whereObj);
|
||
}
|
||
string t = SqlFormat.Keyword(Convert.ToString(text), _action.dalHelper.DataBaseType);
|
||
string v = SqlFormat.Keyword(Convert.ToString(value), _action.dalHelper.DataBaseType);
|
||
string key = t != v ? (v + "," + t) : t;
|
||
return string.Format("select distinct {0} from {1} {2}", key, SqlFormat.Keyword(TableName, _action.dalHelper.DataBaseType), FormatWhere(whereObj));
|
||
}
|
||
internal string GetMaxID()
|
||
{
|
||
switch (_action.dalHelper.DataBaseType)
|
||
{
|
||
case DataBaseType.Oracle:
|
||
return string.Format("select {0}.currval from dual", AutoID);
|
||
default:
|
||
//case DalType.MsSql:
|
||
//case DalType.Sybase:
|
||
//case DalType.MySql:
|
||
//case DalType.SQLite:
|
||
//case DalType.Access:
|
||
//string columnName = _action.Data.Columns.FirstPrimary.ColumnName;
|
||
//string tableName = TableName;
|
||
////if (_action.dalHelper.DataBaseType == DataBaseType.PostgreSQL)
|
||
////{
|
||
//columnName = SqlFormat.Keyword(columnName, _action.dalHelper.DataBaseType);
|
||
//tableName = SqlFormat.Keyword(tableName, _action.dalHelper.DataBaseType);
|
||
// }
|
||
return string.Format("select max({0}) from {1}", SqlFormat.Keyword(_action.Data.Columns.FirstPrimary.ColumnName, _action.dalHelper.DataBaseType), SqlFormat.Keyword(TableName, _action.dalHelper.DataBaseType));
|
||
|
||
}
|
||
// return (string)Error.Throw(string.Format("GetMaxid:{0} No Be Support Now!", _action.dalHelper.dalType.ToString()));
|
||
}
|
||
internal string GetSelectTableName(ref string where)
|
||
{
|
||
string tName = _action.TableName;
|
||
int i = tName.LastIndexOf(')');
|
||
if (i > -1 && _action.DataBaseType == DataBaseType.Oracle)
|
||
{
|
||
tName = tName.Substring(0, i + 1);
|
||
}
|
||
if (selectColumns == null || selectColumns.Length == 0)//没有指定要组合查询。
|
||
{
|
||
return SqlFormat.Keyword(tName, _action.DataBaseType);
|
||
}
|
||
string whereOnly = string.Empty;
|
||
if (!string.IsNullOrEmpty(where))
|
||
{
|
||
int orderbyIndex = where.ToLower().IndexOf("order by");
|
||
if (orderbyIndex > -1)
|
||
{
|
||
whereOnly = " where " + where.Substring(0, orderbyIndex - 1);//-1是去掉空格
|
||
where = "1=1 " + where.Substring(orderbyIndex);
|
||
}
|
||
else
|
||
{
|
||
whereOnly = " where " + where;
|
||
where = string.Empty;
|
||
}
|
||
whereOnly = SqlFormat.RemoveWhereOneEqualsOne(whereOnly);
|
||
}
|
||
string sql = "(select " + GetColumnsSql() + " from " + SqlFormat.Keyword(TableName, _action.DataBaseType) + " " + whereOnly + ") v";
|
||
//if (_action.DalType != DalType.Oracle) // Oracle 取消了存储过程。
|
||
//{
|
||
// sql += "v";
|
||
//}
|
||
return sql;
|
||
}
|
||
|
||
internal string GetColumnsSql()
|
||
{
|
||
if (selectColumns == null || selectColumns.Length == 0)
|
||
{
|
||
return "*";
|
||
}
|
||
string v_Columns = string.Empty;
|
||
string columnName = string.Empty;
|
||
foreach (object column in selectColumns)
|
||
{
|
||
|
||
columnName = column.ToString().Trim();
|
||
if (columnName.IndexOf(' ') > -1 || columnName == "*")
|
||
{
|
||
v_Columns += columnName + ",";
|
||
}
|
||
else
|
||
{
|
||
int i = _action.Data.Columns.GetIndex(columnName);//兼容字段映射
|
||
if (i > -1)
|
||
{
|
||
v_Columns += SqlFormat.Keyword(_action.Data.Columns[i].ColumnName, _action.dalHelper.DataBaseType) + ",";
|
||
}
|
||
else
|
||
{
|
||
_action.dalHelper.DebugInfo.Append(AppConst.HR + "warn : " + TableName + " no contains column " + columnName + AppConst.BR);
|
||
}
|
||
}
|
||
}
|
||
if (v_Columns == string.Empty)
|
||
{
|
||
return "*";
|
||
}
|
||
return v_Columns.TrimEnd(',');
|
||
}
|
||
/// <summary>
|
||
/// 获取主键组合的Where条件。
|
||
/// </summary>
|
||
/// <returns></returns>
|
||
internal string GetPrimaryWhere()
|
||
{
|
||
return GetWhere(_action.DataBaseType, _action.Data.JointPrimaryCell);
|
||
}
|
||
#endregion
|
||
#region 共用函数
|
||
|
||
internal string AddOrderByWithCheck(string where, string primaryKey)
|
||
{
|
||
if (SqlFormat.NotKeyword(primaryKey).ToLower() != "id")
|
||
{
|
||
if (string.IsNullOrEmpty(where))
|
||
{
|
||
where = "1=1";
|
||
}
|
||
where = AddOrderBy(where, primaryKey, _action.DataBaseType);
|
||
}
|
||
return where;
|
||
}
|
||
internal string FormatWhere(object whereObj)
|
||
{
|
||
string where = GetWhereFromObj(whereObj);
|
||
return FormatWhere(where, _action.Data.Columns, _action.DataBaseType, _action.dalHelper.Com);
|
||
}
|
||
private string GetWhereFromObj(object whereObj)
|
||
{
|
||
if (whereObj == null)
|
||
{
|
||
return string.Empty;
|
||
}
|
||
else if (whereObj is String || (whereObj is ValueType && !(whereObj is Enum)))
|
||
{
|
||
return Convert.ToString(whereObj);
|
||
}
|
||
//else if (whereObj is IField)
|
||
//{
|
||
// return SqlFormat.GetIFieldSql(whereObj);
|
||
//}
|
||
MDataCell cell = null;
|
||
if (whereObj is Enum)
|
||
{
|
||
cell = _action.Data[(int)whereObj];
|
||
}
|
||
else if (whereObj is MDataCell)
|
||
{
|
||
cell = whereObj as MDataCell;
|
||
}
|
||
else
|
||
{
|
||
string propName = MBindUI.GetID(whereObj);
|
||
if (!string.IsNullOrEmpty(propName))
|
||
{
|
||
_action.UI.Get(whereObj, null, null);
|
||
cell = _action.Data[propName];
|
||
}
|
||
}
|
||
string where = string.Empty;
|
||
if (cell != null)
|
||
{
|
||
#region 从单元格里取值。
|
||
if (cell.IsNullOrEmpty)
|
||
{
|
||
isCanDo = false;
|
||
_action.dalHelper.RecordsAffected = -2;
|
||
_action.dalHelper.DebugInfo.Append(AppConst.HR + "error : " + cell.ColumnName + " can't be null" + AppConst.BR);
|
||
return "1=2 and " + cell.ColumnName + " is null";
|
||
}
|
||
switch (_action.dalHelper.DataBaseType)
|
||
{
|
||
case DataBaseType.Txt:
|
||
case DataBaseType.Xml:
|
||
switch (DataType.GetGroup(cell.Struct.SqlType))
|
||
{
|
||
case DataGroupType.Number:
|
||
case DataGroupType.Bool:
|
||
where = cell.ColumnName + "=" + cell.Value;
|
||
break;
|
||
default:
|
||
where = cell.ColumnName + "='" + cell.Value + "'";
|
||
break;
|
||
}
|
||
break;
|
||
default:
|
||
string cName = cell.ColumnName;
|
||
if (cell.Struct.MDataColumn != null)
|
||
{
|
||
cName = SqlFormat.Keyword(cell.ColumnName, cell.Struct.MDataColumn.DataBaseType);
|
||
}
|
||
where = cName + "=" + _action.dalHelper.Pre + cell.ColumnName;
|
||
_action.dalHelper.AddParameters(cell.ColumnName, cell.Value, DataType.GetDbType(cell.Struct.ValueType), cell.Struct.MaxSize, ParameterDirection.Input);
|
||
break;
|
||
}
|
||
#endregion
|
||
}
|
||
return where;
|
||
}
|
||
#endregion
|
||
|
||
#endregion
|
||
}
|
||
internal partial class SqlCreate
|
||
{
|
||
internal static string FormatWhere(string where, MDataColumn mdc, DataBaseType dalType, DbCommand com)
|
||
{
|
||
if (string.IsNullOrEmpty(where))
|
||
{
|
||
return string.Empty;
|
||
}
|
||
if (mdc == null || mdc.Count == 0) { return "error : Column.Count=0"; }
|
||
where = SqlFormat.Compatible(where.TrimEnd(), dalType, com == null || com.Parameters.Count == 0);
|
||
if (dalType == DataBaseType.MySql)
|
||
{
|
||
where = SqlFormat.FormatMySqlBit(where, mdc);
|
||
}
|
||
else if (dalType == DataBaseType.Oracle)
|
||
{
|
||
where = SqlFormat.FormatOracleDateTime(where, mdc);
|
||
}
|
||
string lowerWhere = where.ToLower().TrimStart();
|
||
if (lowerWhere.StartsWith("order by"))
|
||
{
|
||
where = "1=1 " + where;
|
||
}
|
||
else if (lowerWhere.IndexOfAny(new char[] { '=', '>', '<' }) == -1 && !lowerWhere.Contains(" like ") && !lowerWhere.Contains(" between ")
|
||
&& !lowerWhere.Contains(" in ") && !lowerWhere.Contains(" in(") && !lowerWhere.Contains(" is "))
|
||
{
|
||
//检测是否带order by 'xxxx order by xxx desc'
|
||
int index = where.Replace("\n", " ").IndexOf(" order by ", StringComparison.OrdinalIgnoreCase);//考虑可能换行的情况xxxx\r\norder by xxx
|
||
string orderBy = string.Empty;
|
||
if (index > -1)
|
||
{
|
||
orderBy = where.Substring(index + 1, where.Length - index - 1);
|
||
where = where.Substring(0, index);
|
||
}
|
||
if (mdc.JointPrimary.Count > 1 && where.Contains(";"))
|
||
{
|
||
#region 多个主键
|
||
StringBuilder sb = new StringBuilder();
|
||
string[] items = where.Split(',');
|
||
MDataRow row = mdc.ToRow("row");
|
||
for (int i = 0; i < items.Length; i++)
|
||
{
|
||
string item = items[i];
|
||
if (!string.IsNullOrEmpty(item))
|
||
{
|
||
string[] values = item.Split(';');
|
||
for (int j = 0; j < row.JointPrimaryCell.Count; j++)
|
||
{
|
||
if (j < values.Length)
|
||
{
|
||
row.JointPrimaryCell[j].Value = values[j];
|
||
}
|
||
}
|
||
if (i != 0)
|
||
{
|
||
sb.Append(" or ");
|
||
}
|
||
sb.Append("(" + GetWhere(dalType, row.JointPrimaryCell) + ")");
|
||
}
|
||
}
|
||
where = sb.ToString();
|
||
if (items.Length == 1)
|
||
{
|
||
where = where.Trim('(', ')');
|
||
}
|
||
items = null;
|
||
#endregion
|
||
}
|
||
else
|
||
{
|
||
#region 单个主键
|
||
|
||
MCellStruct ms = mdc.FirstPrimary;
|
||
|
||
string[] items = where.Split(',');
|
||
if (items.Length == 1)
|
||
{
|
||
//只处理单个值的情况
|
||
DataGroupType primaryGroup = DataType.GetGroup(ms.SqlType);//优先匹配主键
|
||
switch (primaryGroup)
|
||
{
|
||
case DataGroupType.Guid:
|
||
bool isOK = false;
|
||
if (where.Length == 36)
|
||
{
|
||
try
|
||
{
|
||
new Guid(where);
|
||
isOK = true;
|
||
}
|
||
catch
|
||
{
|
||
}
|
||
}
|
||
if (!isOK)
|
||
{
|
||
ms = mdc.FirstUnique;
|
||
}
|
||
break;
|
||
case DataGroupType.Number:
|
||
long v;
|
||
if (!long.TryParse(where.Trim('\''), out v))
|
||
{
|
||
ms = mdc.FirstUnique;
|
||
}
|
||
break;
|
||
}
|
||
|
||
string columnName = SqlFormat.Keyword(ms.ColumnName, dalType);
|
||
where = GetWhereEqual(DataType.GetGroup(ms.SqlType), columnName, where, dalType);
|
||
}
|
||
else
|
||
{
|
||
List<string> lists = new List<string>(items.Length);
|
||
lists.AddRange(items);
|
||
where = GetWhereIn(ms, lists, dalType);
|
||
}
|
||
#endregion
|
||
}
|
||
if (!string.IsNullOrEmpty(orderBy))
|
||
{
|
||
where = where + " " + orderBy;
|
||
}
|
||
}
|
||
|
||
return where;
|
||
}
|
||
private static string GetWhereEqual(DataGroupType group, string columnName, string where, DataBaseType dalType)
|
||
{
|
||
if (string.IsNullOrEmpty(where))
|
||
{
|
||
return string.Empty;
|
||
}
|
||
if (group != 0)
|
||
{
|
||
where = where.Trim('\'');
|
||
}
|
||
columnName = SqlFormat.Keyword(columnName, dalType);
|
||
if (group == DataGroupType.Number)//int 类型的，主键不为bit型。
|
||
{
|
||
where = columnName + "=" + where;
|
||
}
|
||
else
|
||
{
|
||
if (group == DataGroupType.Guid)
|
||
{
|
||
|
||
switch (dalType)
|
||
{
|
||
case DataBaseType.Access:// Access的GUID类型的更新，必须带｛｝包含。
|
||
where = "{" + where.Trim('{', '}') + "}";
|
||
break;
|
||
case DataBaseType.SQLite://SQLite 以16字节存储，需要转换才能查询。
|
||
return columnName + "=x'" + StaticTool.ToGuidByteString(where) + "'";
|
||
}
|
||
}
|
||
where = columnName + "='" + where + "'";
|
||
}
|
||
return where;
|
||
}
|
||
|
||
|
||
|
||
internal static string RemoveOrderBy(string where)
|
||
{
|
||
int index = where.ToLower().IndexOf("order by");
|
||
if (index > 0)
|
||
{
|
||
where = where.Substring(0, index);
|
||
}
|
||
return where;
|
||
}
|
||
|
||
|
||
internal static string AddOrderBy(string where, string primaryKey, DataBaseType dalType)
|
||
{
|
||
if (where.IndexOf("order by", StringComparison.OrdinalIgnoreCase) == -1)
|
||
{
|
||
where += " order by " + SqlFormat.Keyword(primaryKey, dalType) + " asc";
|
||
}
|
||
else if (where.IndexOf(" asc", StringComparison.OrdinalIgnoreCase) == -1 && where.IndexOf(" desc", StringComparison.OrdinalIgnoreCase) == -1)
|
||
{
|
||
//有order by 但没 asc
|
||
where += " asc";
|
||
}
|
||
return where;
|
||
}
|
||
internal static string GetWhere(DataBaseType dalType, List<MDataCell> cells)
|
||
{
|
||
return GetWhere(dalType, true, cells);
|
||
}
|
||
/// <summary>
|
||
/// 根据元数据列组合where条件。
|
||
/// </summary>
|
||
/// <returns></returns>
|
||
internal static string GetWhere(DataBaseType dalType, bool isAnd, List<MDataCell> cells)
|
||
{
|
||
string where = "";
|
||
MDataCell cell;
|
||
for (int i = 0; i < cells.Count; i++)
|
||
{
|
||
cell = cells[i];
|
||
if (i > 0)
|
||
{
|
||
where += (isAnd ? " and " : " or ");
|
||
}
|
||
DataGroupType group = DataType.GetGroup(cell.Struct.SqlType);
|
||
string columnName = SqlFormat.Keyword(cell.ColumnName, dalType);
|
||
switch (group)
|
||
{
|
||
case DataGroupType.Number:
|
||
where += columnName + "=" + (cell.IsNullOrEmpty ? -9999 : cell.Value);
|
||
break;
|
||
case DataGroupType.Bool:
|
||
where += columnName + "=" + (cell.Value.ToString().ToLower() == "true" ? SqlValue.True : SqlValue.False);
|
||
break;
|
||
default:
|
||
|
||
if (group == DataGroupType.Guid)
|
||
{
|
||
string guid = cell.StringValue;
|
||
if (string.IsNullOrEmpty(guid) || guid.Length != 36)
|
||
{
|
||
return "1=2--('" + guid + "' is not guid)";
|
||
}
|
||
if (dalType == DataBaseType.Access)
|
||
{
|
||
where += columnName + "='{" + guid + "}'";
|
||
}
|
||
else if (dalType == DataBaseType.SQLite)
|
||
{
|
||
where += columnName + "=x'" + StaticTool.ToGuidByteString(guid) + "'";
|
||
}
|
||
else
|
||
{
|
||
where += columnName + "='" + guid + "'";
|
||
}
|
||
}
|
||
else if (group == DataGroupType.Date && dalType == DataBaseType.Oracle) // Oracle的日期时间要转类型
|
||
{
|
||
if (cell.Struct.SqlType == SqlDbType.Timestamp)
|
||
{
|
||
where += columnName + "=to_timestamp('" + cell.StringValue + "','yyyy-MM-dd HH24:MI:ss.ff')";
|
||
}
|
||
else
|
||
{
|
||
where += columnName + "=to_date('" + cell.StringValue + "','yyyy-mm-dd hh24:mi:ss')";
|
||
}
|
||
}
|
||
else if (group == 0 && dalType == DataBaseType.MsSql && cell.Struct.SqlTypeName != null && cell.Struct.SqlTypeName.EndsWith("hierarchyId"))
|
||
{
|
||
where += columnName + "=HierarchyID::Parse('" + cell.StringValue + "')";
|
||
}
|
||
else
|
||
{
|
||
//处理HID=HierarchyID::Parse('/1/2/3/')
|
||
where += columnName + "='" + cell.Value + "'";
|
||
}
|
||
break;
|
||
}
|
||
}
|
||
return where;
|
||
}
|
||
|
||
internal static string GetWhereIn(MCellStruct ms, List<string> items, DataBaseType dalType)
|
||
{
|
||
if (items == null || items.Count == 0)
|
||
{
|
||
return "1=2";
|
||
}
|
||
|
||
StringBuilder sb = new StringBuilder();
|
||
sb.Append(SqlFormat.Keyword(ms.ColumnName, dalType));
|
||
sb.Append(" In (");
|
||
DataGroupType group = DataType.GetGroup(ms.SqlType);
|
||
string item;
|
||
for (int i = 0; i < items.Count; i++)
|
||
{
|
||
item = items[i];
|
||
if (string.IsNullOrEmpty(item))
|
||
{
|
||
continue;
|
||
}
|
||
if (group != DataGroupType.Text)
|
||
{
|
||
item = item.Trim('\'');//不是字母都尝试去掉分号
|
||
}
|
||
if (dalType == DataBaseType.Oracle && i > 999 && i % 1000 == 0)//oracle 列表中的最大表达数为1000
|
||
{
|
||
sb.Remove(sb.Length - 1, 1);//移除最后一个,号。
|
||
sb.Append(") or " + SqlFormat.Keyword(ms.ColumnName, dalType) + " In (");
|
||
}
|
||
if (!string.IsNullOrEmpty(item))
|
||
{
|
||
if (group == DataGroupType.Number)
|
||
{
|
||
sb.Append(item + ",");
|
||
}
|
||
else
|
||
{
|
||
if (group == DataGroupType.Guid && dalType == DataBaseType.SQLite)
|
||
{
|
||
sb.Append("x'" + StaticTool.ToGuidByteString(item) + "',");
|
||
}
|
||
else if (group == DataGroupType.Date && dalType == DataBaseType.Oracle)
|
||
{
|
||
if (ms.SqlType == SqlDbType.Timestamp)
|
||
{
|
||
sb.Append("to_timestamp('" + item + "','yyyy-MM-dd HH24:MI:ss.ff'),");
|
||
}
|
||
else
|
||
{
|
||
sb.Append("to_date('" + item + "','yyyy-mm-dd hh24:mi:ss'),");
|
||
}
|
||
}
|
||
else
|
||
{
|
||
sb.Append("'" + item + "',");
|
||
}
|
||
}
|
||
}
|
||
}
|
||
return sb.ToString().TrimEnd(',') + ")";
|
||
}
|
||
|
||
internal static string MySqlBulkCopySql = "SET GLOBAL local_infile=1;LOAD DATA LOCAL INFILE '{0}' INTO TABLE {1} CHARACTER SET utf8 FIELDS TERMINATED BY '{2}' LINES TERMINATED BY '|\r\n|' {3}";
|
||
internal static string OracleBulkCopySql = "LOAD DATA INFILE '{0}' APPEND INTO TABLE {1} FIELDS TERMINATED BY '{2}' OPTIONALLY ENCLOSED BY '\"' {3}";
|
||
internal static string OracleSqlldr = " userid={0} control='{1}'";//sqlldr
|
||
internal static string TruncateTable = "truncate table {0}";
|
||
/// <summary>
|
||
/// 获得批量导入的列名。
|
||
/// </summary>
|
||
/// <param name="mdc"></param>
|
||
/// <param name="keepID"></param>
|
||
/// <param name="dalType"></param>
|
||
/// <returns></returns>
|
||
internal static string GetColumnName(MDataColumn mdc, bool keepID, DataBaseType dalType)
|
||
{
|
||
StringBuilder sb = new StringBuilder();
|
||
sb.Append("(");
|
||
foreach (MCellStruct ms in mdc)
|
||
{
|
||
if (!keepID && ms.IsAutoIncrement)
|
||
{
|
||
continue;
|
||
}
|
||
sb.Append(SqlFormat.Keyword(ms.ColumnName, dalType));
|
||
if (dalType == DataBaseType.Oracle && DataType.GetGroup(ms.SqlType) == DataGroupType.Date)
|
||
{
|
||
//日期
|
||
sb.Append(" DATE \"YYYY-MM-DD HH24:MI:SS\" NULLIF (" + ms.ColumnName + "=\"NULL\")");
|
||
}
|
||
sb.Append(",");
|
||
}
|
||
return sb.ToString().TrimEnd(',') + ")";
|
||
}
|
||
|
||
internal static string SqlToViewSql(string sqlObj)
|
||
{
|
||
string sql = sqlObj.ToLower().Trim();
|
||
if (sql.StartsWith("select ") || (sql.Contains(" ") && sql[0] != '('))
|
||
{
|
||
sqlObj = "(" + sqlObj + ") v";
|
||
}
|
||
return sqlObj.Trim();
|
||
}
|
||
}
|
||
}
|