/// <summary>
/// function in use。List<string> parameterNames, List<object> parameterValues
/// </summary> VALUES
/// <param name="strSQL"></param>
/// <param name="filter"></param>
/// <returns></returns>
public override string ExecuteParamSQL(string TableName, FilterData filter)
{
StringBuilder sb = new StringBuilder(0);
sb.Append("INSERT INTO ");
sb.Append(TableName);
sb.Append(" ( ");
for (int i = 0; i < filter.ParameterNames.Count; i++)
{
sb.Append(filter.ParameterNames[i]);
sb.Append(",");
}
StringHelper.RemoveLastStrSplit(sb, ",");
sb.Append(" )");
sb.Append(" VALUES ( ");
for (int i = 0; i < filter.ParameterNames.Count; i++)
{
sb.Append(":");
sb.Append(filter.ParameterNames[i]);
sb.Append(",");
}
StringHelper.RemoveLastStrSplit(sb, ",");
sb.Append(" ) ");
return(ExecuteParamSQL(sb.ToString(), filter.ParameterNames, filter.ParameterValues));
}
/// <summary>
/// 反序列化查询信息
/// </summary>
/// <param name="objBase64String"></param>
public void DeSerialData(string objBase64String)
{
byte[] Content = Convert.FromBase64String(objBase64String);
MemoryStream ms = new MemoryStream();
try
{
ms.Write(Content, 0, Content.Length);
ms.Position = 0;
BinaryFormatter m_BinaryFormatter = new BinaryFormatter();
FilterData obj = (FilterData)m_BinaryFormatter.Deserialize(ms);
ms.Close();
this._filterExpression = obj.FilterExpression;
this._parameterNames = obj.ParameterNames;
this._parameterValues = obj.ParameterValues;
this._compareTypes = obj.CompareTypes;
}
catch (Exception ex)
{
ms.Close();
throw ex;
}
}
/// <summary>
/// 注意 inValues.Count == 0 时，filter.FilterExpression 为 1=1，直接使用会把所有行查询出来
/// </summary>
/// <param name="colName"></param>
/// <param name="inValues"></param>
/// <returns></returns>
public static FilterData GetInFilter4singleColName(string colName, List <string> inValues)
{
FilterData filter = new FilterData();
if (inValues.Count == 0)
{
return(filter);
}
var sb = new StringBuilder(colName + " IN (");
for (int i = 0; i < inValues.Count; i++)
{
sb.Append(":" + colName + i.ToString() + ",");
filter.ParameterNames.Add(colName + i.ToString());
filter.ParameterValues.Add(inValues[i]);
}
if (sb.ToString().EndsWith(","))
{
sb.Remove(sb.ToString().Length - 1, 1); // 去除多余的,
}
sb.Append(") ");
filter.FilterExpression = sb.ToString();
return(filter);
}
/// <summary>
/// 连接 filter1 和 filter2；不能用于 inFilter
/// </summary>
/// <param name="filter1"></param>
/// <param name="filter2"></param>
/// <param name="filterCompare"></param>
/// <param name="filterLink"></param>
public static void ConcatNoneDateFilter(ref FilterData filter1, FilterData filter2, LinkType filterLink)
{
for (int i = 0; i < filter2.ParameterNames.Count; i++)
{
filter1.AppendFilter(filter2.ParameterNames[i], filter2.ParameterNames[i], filter2.ParameterValues[i], filter2.CompareTypes[i], filterLink);
}
}
public static FilterData GetOrgFilter(List <string> orgItems)
{
FilterData filter = new FilterData();
StringBuilder sb = new StringBuilder("ORG_ID IN (");
for (int i = 0; i < orgItems.Count; i++)
{
sb.Append(":ORG_ID" + i.ToString() + ",");
filter.ParameterNames.Add("ORG_ID" + i.ToString());
filter.ParameterValues.Add(orgItems[i]);
}
sb.Append("' ') OR ORG_ID IS NULL");
filter.FilterExpression = sb.ToString();
return(filter);
}
/// <summary>
/// 用于批量删除
/// </summary>
/// <param name="listFilter"></param>
/// <param name="m_LinkType"></param>
/// <returns></returns>
public static FilterData List2Filter(List <FilterData> listFilter, LinkType m_LinkType)
{
StringBuilder sb = new StringBuilder();
FilterData filter = new FilterData();
string str_LinkType = "";
if (listFilter.Count == 0)
{
return(filter);
}
if (listFilter.Count == 1)
{
return(listFilter[0]);
}
if (m_LinkType == LinkType.AND)
{
str_LinkType = " and ";
}
if (m_LinkType == LinkType.OR)
{
str_LinkType = " or ";
}
for (int i = 0; i < listFilter.Count; i++)
{
sb.Append(listFilter[i].FilterExpression);
sb.Append(str_LinkType);
for (int j = 0; j < listFilter[i].ParameterNames.Count; j++)
{
filter.ParameterNames.Add(listFilter[i].ParameterNames[j]);
filter.ParameterValues.Add(listFilter[i].ParameterValues[j]);
filter.CompareTypes.Add(listFilter[i].CompareTypes[j]);
}
}
if (sb.ToString().EndsWith(str_LinkType))
{
sb.Remove(sb.ToString().Length - 1 - str_LinkType.Length, str_LinkType.Length);
}
filter.FilterExpression = sb.ToString();
return(filter);
}
/// <summary>
/// 执行参数化查询
/// </summary>
/// <param name="filter"></param>
/// <returns></returns>
public DataSet SelectByPara_SQL(FilterData filter, bool isNvarChar = true)
{
string strSQL = filter.FilterExpression;
this.oraCmd.CommandText = strSQL;
this.oraCmd.CommandType = CommandType.Text;
var ds = new DataSet();
var parameterNames = filter.ParameterNames;
var parameterValues = filter.ParameterValues;
this.oraCmd.Parameters.Clear();
for (int i = 0; i < parameterNames.Count; i++)
{
var parameter = new OracleParameter(parameterNames[i], parameterValues[i]);
if (isNvarChar)
{
parameter.OracleType = OracleType.NVarChar;
}
this.oraCmd.Parameters.Add(parameter);
}
this.oraAdapter.SelectCommand = this.oraCmd;
try
{
this.oraAdapter.Fill(ds);
}
catch (Exception ex)
{
NLogHelper.Error(ex);
ds = null;
}
return(ds);
}
public abstract string ExecuteParamSQL(string tableName, FilterData filter);
public abstract DataSet SelectBySQL(string tableName, FilterData filter);
/// <summary>
/// 执行带参数的查询语句
/// </summary>
/// <param name="tableName"></param>
/// <param name="filter"></param>
/// <returns></returns>
public override DataSet SelectBySQL(string tableName, FilterData filter)
{
return(SelectBySQL(tableName, filter.FilterExpression, filter.ParameterNames, filter.ParameterValues));
}