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); } } } }