private static string getWhereSelect(string fields, DataRow r)
{
string strRes = string.Empty;
string[] arFields = fields.Split(',');
for (int i = 0; i < arFields.Length; i++)
{
arFields[i] = arFields[i].Trim();
if (arFields[i] == "DATE_TIME")
{
strRes += String.Format(r.Table.Locale, @"DATE_TIME = '{0:o}'", r[arFields[i]]) + @" AND ";
}
else
{
strRes += arFields[i] + @"=" + DbTSQLInterface.ValueToQuery(r[arFields[i]], r[arFields[i]].GetType()) + @" AND ";
}
}
if (strRes.Equals(string.Empty) == false)
{
strRes = strRes.Substring(0, strRes.Length - @" AND ".Length);
}
else
{
;
}
return(strRes);
}
private static string getWhereSelect(string fields, DataRow r)
{
string strRes = string.Empty;
string [] arFields = fields.Split(',');
for (int i = 0; i < arFields.Length; i++)
{
arFields[i] = arFields[i].Trim();
strRes += arFields[i] + @"=" + DbTSQLInterface.ValueToQuery(r[arFields[i]], r[arFields[i]].GetType()) + @" AND ";
}
if (strRes.Equals(string.Empty) == false)
{
strRes = strRes.Substring(0, strRes.Length - @" AND ".Length);
}
else
{
;
}
return(strRes);
}
//Изменение (вставка) в оригинальную таблицу записей измененных (добавленных) в измененную таблицу (обязательно наличие поля: ID)
public static void RecUpdateInsert(ref DbConnection conn, string nameTable, string keyFields, DataTable origin, DataTable data, out int err)
{
err = (int)Error.NO_ERROR;
int j = -1, k = -1;
bool bUpdate = false;
DataRow[] dataRows;
string[] strQuery = new string[(int)DbTSQLInterface.QUERY_TYPE.COUNT_QUERY_TYPE];
string valuesForInsert = string.Empty
, strWhere = string.Empty;
for (j = 0; j < data.Rows.Count; j++)
{
strWhere = getWhereSelect(keyFields, data.Rows[j]);
dataRows = origin.Select(strWhere);
if (dataRows.Length == 0)
{
//INSERT
strQuery[(int)DbTSQLInterface.QUERY_TYPE.INSERT] = string.Empty;
valuesForInsert = string.Empty;
strQuery[(int)DbTSQLInterface.QUERY_TYPE.INSERT] = "INSERT INTO " + nameTable + " (";
for (k = 0; k < data.Columns.Count; k++)
{
strQuery[(int)DbTSQLInterface.QUERY_TYPE.INSERT] += data.Columns[k].ColumnName + ",";
valuesForInsert += DbTSQLInterface.ValueToQuery(data, j, k) + ",";
}
strQuery[(int)DbTSQLInterface.QUERY_TYPE.INSERT] = strQuery[(int)DbTSQLInterface.QUERY_TYPE.INSERT].Substring(0, strQuery[(int)DbTSQLInterface.QUERY_TYPE.INSERT].Length - 1);
valuesForInsert = valuesForInsert.Substring(0, valuesForInsert.Length - 1);
strQuery[(int)DbTSQLInterface.QUERY_TYPE.INSERT] += ") VALUES (";
strQuery[(int)DbTSQLInterface.QUERY_TYPE.INSERT] += valuesForInsert + ")";
DbTSQLInterface.ExecNonQuery(ref conn, strQuery[(int)DbTSQLInterface.QUERY_TYPE.INSERT], null, null, out err);
}
else
{
if (dataRows.Length == 1)
{//UPDATE
bUpdate = false;
strQuery[(int)DbTSQLInterface.QUERY_TYPE.UPDATE] = string.Empty;
for (k = 0; k < data.Columns.Count; k++)
{
if (!(data.Rows[j][k].Equals(origin.Rows[j][k]) == true))
{
if (bUpdate == false)
{
bUpdate = true;
}
else
{
;
}
}
else
{
;
}
strQuery[(int)DbTSQLInterface.QUERY_TYPE.UPDATE] += data.Columns[k].ColumnName + "="; // + data.Rows[j][k] + ",";
strQuery[(int)DbTSQLInterface.QUERY_TYPE.UPDATE] += DbTSQLInterface.ValueToQuery(data, j, k) + ",";
}
if (bUpdate == true)
{//UPDATE
strQuery[(int)DbTSQLInterface.QUERY_TYPE.UPDATE] = strQuery[(int)DbTSQLInterface.QUERY_TYPE.UPDATE].Substring(0, strQuery[(int)DbTSQLInterface.QUERY_TYPE.UPDATE].Length - 1);
strQuery[(int)DbTSQLInterface.QUERY_TYPE.UPDATE] = "UPDATE " + nameTable + " SET " + strQuery[(int)DbTSQLInterface.QUERY_TYPE.UPDATE] + " WHERE " + getWhereSelect(keyFields, data.Rows[j]);
DbTSQLInterface.ExecNonQuery(ref conn, strQuery[(int)DbTSQLInterface.QUERY_TYPE.UPDATE], null, null, out err);
}
else
{
;
}
}
else
{
throw new Exception("Невозможно определить тип изменения таблицы " + nameTable);
}
}
}
}