/// <summary>
/// Condition value setting
/// 条件値設定
/// </summary>
/// <param name="column"></param>
/// <param name="attribute"></param>
/// <param name="value"></param>
/// <param name="equal"></param>
private void AddConditionValue(string column, DataAccessAttribute attribute, string value, bool equal)
{
value = this.EscapeSingleQuotation(value.ToString());
if (equal)
{
WhereSql.AppendLine(string.Format("{0}.{1} = '{2}'", attribute.TableName, column, value.ToString()));
}
else
{
WhereSql.AppendLine(string.Format("{0}.{1} != '{2}'", attribute.TableName, column, value.ToString()));
}
}
/// <summary>
/// Condition value (in) setting
/// 条件値(in)設定
/// </summary>
/// <param name="column"></param>
/// <param name="attribute"></param>
/// <param name="values"></param>
/// <param name="equal"></param>
private void AddInConditionValue(string column, DataAccessAttribute attribute, string[] values, bool equal)
{
for (int i = 0; i < values.Length; i++)
{
values[i] = EscapeSingleQuotation(values[i]);
}
if (equal)
{
WhereSql.AppendLine(string.Format("{0}.{1} in ({2})", attribute.TableName, column, string.Format("'{0}'", string.Join("','", values))));
}
else
{
WhereSql.AppendLine(string.Format("{0}.{1} not in ({2})", attribute.TableName, column, string.Format("'{0}'", string.Join("','", values))));
}
}
/// <summary>
/// And Or statement setting
/// And Or 文設定
/// </summary>
/// <param name="andOr"></param>
private void AddOperator(bool isAnd)
{
if (string.IsNullOrEmpty(WhereSql.ToString()))
{
WhereSql.AppendLine("where");
}
else
{
if (isAnd)
{
WhereSql.AppendLine("and");
}
else
{
WhereSql.AppendLine("or");
}
}
}
/// <summary>
/// 修改记录
/// </summary>
public void gsUpdate(string InfoId, SortedList FieldValueList, SortedList ForeignKeyList)
{
string str;
int i;
string tempFieldName;
string tempFieldValue;
string WhereSql;
try
{
this.DataOperator.ExecuteSql("set quoted_identifier off");
//根据外键值和ID设置定位语句
WhereSql = " where ";
for (i = 0; i <= ForeignKeyList.Count - 1; i++)
{
object tempValue;
tempFieldName = ForeignKeyList.GetKey(i).ToString();
tempValue = ForeignKeyList.GetByIndex(i);
if (tempValue.GetType().FullName == "System.String")
{
tempFieldValue = "'" + tempValue.ToString() + "'";
}
else
{
tempFieldValue = tempValue.ToString();
}
WhereSql = WhereSql + tempFieldName + "=" + tempFieldValue + " and ";
}
WhereSql = WhereSql.Substring(0, WhereSql.LastIndexOf("and"));
//逐个更新非主键字段
for (i = 0; i <= FieldValueList.Count - 1; i++)
{
object tempValue;
tempFieldName = FieldValueList.GetKey(i).ToString();
tempValue = FieldValueList.GetByIndex(i);
if (tempValue == null)
{
tempFieldValue = null;
}
else
{
if (tempValue.GetType().FullName == "System.String")
{
tempFieldValue = '"' + tempValue.ToString() + '"';
}
else
{
tempFieldValue = tempValue.ToString();
}
}
//不是主键
if (!ForeignKeyList.ContainsKey(tempFieldName))
{
str = "set quoted_identifier off update " + InfoId + " set " + tempFieldName + "=" + tempFieldValue + WhereSql;
this.DataOperator.ExecuteSql(str);
}
}
}
catch (Exception e)
{
throw new Exception(e.Message);
}
}
/// <summary>
/// 添加新记录（处理Id);
/// </summary>
public void gsAddNew(string InfoId, SortedList FieldValueList, SortedList ForeignKeyList)
{
string str;
long nId;
int i;
string FieldNameSql;
string FieldValueSql;
string tempFieldName;
string tempFieldValue;
string WhereSql;
try
{
//1.获取当前的Id
nId = this.GetAddId(InfoId, ForeignKeyList);
string IdName = InfoId + "ID";
IdName = IdName.ToUpper();
//ID加到外键值集合中
ForeignKeyList.Add(IdName, nId);
if (!ForeignKeyList.ContainsKey("ZA0100"))
{
ForeignKeyList.Add("ZA0100", FieldValueList["ZA0100"].ToString());
}
FieldNameSql = "";
FieldValueSql = "";
WhereSql = " where ";
//2.根据ID,主键值插入新记录
for (i = 0; i <= ForeignKeyList.Count - 1; i++)
{
object tempValue;
tempFieldName = ForeignKeyList.GetKey(i).ToString();
tempValue = ForeignKeyList.GetByIndex(i);
if (tempValue.GetType().FullName == "System.String")
{
tempFieldValue = "'" + tempValue.ToString() + "'";
}
else
{
tempFieldValue = tempValue.ToString();
}
FieldNameSql = FieldNameSql + tempFieldName + ",";
FieldValueSql = FieldValueSql + tempFieldValue + ",";
WhereSql = WhereSql + tempFieldName + "=" + tempFieldValue + " and ";
}
FieldNameSql = FieldNameSql.Substring(0, FieldNameSql.LastIndexOf(","));
FieldValueSql = FieldValueSql.Substring(0, FieldValueSql.LastIndexOf(","));
WhereSql = WhereSql.Substring(0, WhereSql.LastIndexOf("and") - 1);
//插入新记录
str = "Insert " + InfoId + "(" + FieldNameSql + ") values(" + FieldValueSql + ")";
this.DataOperator.ExecuteSql(str);
//3.逐个更新非主键字段
gsUpdate(InfoId, FieldValueList, ForeignKeyList);
}
catch (Exception e)
{
throw new Exception(e.Message);
}
}