private string GetViolatedConstraint(System.Data.DataTable dataTable) { System.Text.StringBuilder sb = new System.Text.StringBuilder(); if (dataTable.HasErrors) { foreach (DataRow row in dataTable.GetErrors()) { sb.AppendLine(row.RowError); } } return("In table " + dataTable.TableName + " " + sb.ToString()); }
public void TestRowErrors () { DataTable table = new DataTable (); DataColumn col1 = table.Columns.Add ("col1", typeof (int)); DataColumn col2 = table.Columns.Add ("col2", typeof (int)); DataColumn col3 = table.Columns.Add ("col3", typeof (int)); col1.AllowDBNull = false; table.Constraints.Add ("uc", new DataColumn[] {col2,col3}, false); table.BeginLoadData (); table.Rows.Add (new object[] {null,1,1}); table.Rows.Add (new object[] {1,1,1}); try { table.EndLoadData (); Assert.Fail ("#0"); } catch (ConstraintException) {} Assert.IsTrue (table.HasErrors, "#1"); DataRow[] rows = table.GetErrors (); Assert.AreEqual (2, rows.Length, "#2"); Assert.AreEqual ("Column 'col1' does not allow DBNull.Value.", table.Rows [0].RowError, "#3"); Assert.AreEqual ("Column 'col2, col3' is constrained to be unique. Value '1, 1' is already present." , table.Rows [1].RowError, "#4"); Assert.AreEqual (table.Rows [0].RowError, table.Rows [0].GetColumnError (0), "#5"); Assert.AreEqual (table.Rows [1].RowError, table.Rows [0].GetColumnError (1), "#6"); Assert.AreEqual (table.Rows [1].RowError, table.Rows [0].GetColumnError (2), "#7"); Assert.AreEqual ("", table.Rows [1].GetColumnError (0), "#8"); Assert.AreEqual (table.Rows [1].RowError, table.Rows [1].GetColumnError (1), "#9"); Assert.AreEqual (table.Rows [1].RowError, table.Rows [1].GetColumnError (2), "#10"); }
/// <summary> /// Сохранить каналы в БД /// </summary> private bool UpdateCnls(DataTable dataTable, string descr) { int updRows = 0; int errRows = 0; DataRow[] rowsInError = null; SqlCeDataAdapter sqlAdapter = dataTable.ExtendedProperties["DataAdapter"] as SqlCeDataAdapter; updRows = sqlAdapter.Update(dataTable); if (dataTable.HasErrors) { rowsInError = dataTable.GetErrors(); errRows = rowsInError.Length; } if (errRows == 0) { writer.WriteLine(string.Format(descr, updRows)); } else { writer.WriteLine(string.Format(descr, updRows) + ". " + string.Format(AppPhrases.ErrorsCount, errRows)); foreach (DataRow row in rowsInError) writer.WriteLine(string.Format(AppPhrases.CnlError, row[0], row.RowError)); } return errRows == 0; }
/// <summary> /// Update the pending insert, update and delete from the datatable to the database. /// <para>Note that the option ContinueUpdateOnError of the data adapter is set to true so the rows with errors will not be saved.</para> /// <para>Test out from there for datatable errors if the result is false.</para> /// </summary> /// <param name="dt">the datatable to update</param> /// <param name="sqlCommandText">usually the sql string used to fill the datatable</param> /// <returns></returns> public bool DataTableUpdate(DataTable dt, string sqlCommandText) { errorMessage = ""; DbConnection cn = CreateConnection(); try { DbCommand cm = CreateCommand(cn, sqlCommandText); DbDataAdapter da = CreateDataAdapter(cm); da.ContinueUpdateOnError = true; DbCommandBuilder cb = CreateCommandBuilder(da); cb.ConflictOption = ConflictOption.OverwriteChanges; da.InsertCommand = cb.GetInsertCommand(); da.InsertCommand.CommandTimeout = timeOut; try { da.UpdateCommand = cb.GetUpdateCommand(); } catch (Exception ex) { if (dt.PrimaryKey.Length == 0) throw new Exception(ex.Message); string tableName = sqlCommandText; tableName = tableName.Replace("\r", " "); tableName = tableName.Replace("\n", " "); tableName = tableName.Replace("\t", " "); tableName = tableName.Substring(sqlCommandText.ToUpper().IndexOf(" FROM ") + 6); if (tableName.IndexOf(" ") > 0) tableName = tableName.Substring(0, tableName.IndexOf(" ")); int k = 1; string updateQuery = "update " + tableName + " set "; for (int i = 0; i < dt.Columns.Count; i++) { updateQuery += dt.Columns[i].ColumnName + "=" + ProviderParam + "p" + k.ToString() + ","; k++; } updateQuery = RTrunc(updateQuery, 1); updateQuery += " where "; for (int i = 0; i < dt.PrimaryKey.Length; i++) { updateQuery += dt.PrimaryKey[i].ColumnName + "=" + ProviderParam + "p" + k.ToString() + " and "; k++; } updateQuery = RTrunc(updateQuery, 5); DbCommand updateCommand = CreateCommand(updateQuery); k = 1; for (int i = 0; i < dt.Columns.Count; i++) { DbParameter p = updateCommand.CreateParameter(); p.ParameterName = ProviderParam + "p" + k.ToString(); p.SourceColumn = dt.Columns[i].ColumnName; updateCommand.Parameters.Add(p); k++; } for (int i = 0; i < dt.PrimaryKey.Length; i++) { DbParameter p = updateCommand.CreateParameter(); p.ParameterName = ProviderParam + "p" + k.ToString(); p.SourceColumn = dt.PrimaryKey[i].ColumnName; updateCommand.Parameters.Add(p); k++; } da.UpdateCommand = updateCommand; } da.UpdateCommand.CommandTimeout = timeOut; try { da.DeleteCommand = cb.GetDeleteCommand(); } catch (Exception ex) { if (dt.PrimaryKey.Length == 0) throw new Exception(ex.Message); string tableName = sqlCommandText; tableName = tableName.Replace("\r", " "); tableName = tableName.Replace("\n", " "); tableName = tableName.Replace("\t", " "); tableName = tableName.Substring(sqlCommandText.ToUpper().IndexOf(" FROM ") + 6); if (tableName.IndexOf(" ") > 0) tableName = tableName.Substring(0, tableName.IndexOf(" ")); int k = 1; string deleteQuery = "delete " + tableName + " where "; for (int i = 0; i < dt.PrimaryKey.Length; i++) { deleteQuery += dt.PrimaryKey[i].ColumnName + "=" + ProviderParam + "p" + k.ToString() + " and "; k++; } deleteQuery = RTrunc(deleteQuery, 5); DbCommand deleteCommand = CreateCommand(deleteQuery); k = 1; for (int i = 0; i < dt.PrimaryKey.Length; i++) { DbParameter p = deleteCommand.CreateParameter(); p.ParameterName = ProviderParam + "p" + k.ToString(); p.SourceColumn = dt.PrimaryKey[i].ColumnName; deleteCommand.Parameters.Add(p); k++; } da.DeleteCommand = deleteCommand; } da.DeleteCommand.CommandTimeout = timeOut; cn.Open(); da.Update(dt); if (dt.HasErrors) { DataRow[] rw = dt.GetErrors(); for (int i = 0; i < rw.Length; i++) { errorMessage += "\r\n" + rw[i].RowError + "\r\n"; for (int j = 0; j < dt.Columns.Count; j++) errorMessage += dt.Columns[j].ColumnName + "=" + rw[i][j].ToString() + "\r\n"; } if (logEvents) LogEvent("ERROR", "Error while updating data\r\n" + errorMessage); return false; } else return true; } catch (Exception ex) { errorMessage = ex.Message; if (logEvents) LogEvent("ERROR", sqlCommandText + "\r\n/\r\n" + ex.ToString(), true); return false; } finally { cn.Close(); cn.Dispose(); } }
private string GetDataTableErrors(DataTable dataTable) { StringBuilder err = new StringBuilder("GetDataTableErrors: "); if (!String.IsNullOrEmpty(dataTable.TableName)) { err.Append(dataTable.TableName); } err.Append(Environment.NewLine); DataRow[] rowsInError; if (dataTable.HasErrors) { rowsInError = dataTable.GetErrors(); for (int i = 0; i < rowsInError.Length; i++) { foreach (DataColumn myCol in dataTable.Columns) { string s = rowsInError[i].GetColumnError(myCol); if (!String.IsNullOrEmpty(s)) { err.Append(myCol.ColumnName + ": "); err.Append(s); err.Append(";"); err.Append(Environment.NewLine); } } // Clear the row errors rowsInError[i].ClearErrors(); } } return err.ToString(); }
/// <summary> /// Allows the programmer to run a query against the Database. /// </summary> /// <param name="sql">The SQL to run</param> /// <returns>A DataTable containing the result set.</returns> public DataTable GetDataTable(SqliteCommand command) { DataTable dt = new DataTable(); try { lock (_lock) { SqliteDataReader reader = command.ExecuteReader(); try { dt.Load(reader); } catch (System.Exception ex) { GenLib.Log.LogService.LogException("Error while executing a query: " + command.CommandText, ex); foreach (DataRow row in dt.GetErrors()) Trace.WriteLine(row.RowError); } finally { reader.Close(); } } } catch (Exception ex) { GenLib.Log.LogService.LogException("Error while executing a query: " + command.CommandText, ex); throw; } return dt; }
//~SqliteDatabase() //{ // CloseConnection(); //} /// <summary> /// Allows the programmer to run a query against the Database. /// </summary> /// <param name="sql">The SQL to run</param> /// <returns>A DataTable containing the result set.</returns> public DataTable GetDataTable(string sql) { DataTable dt = new DataTable(); try { lock (_lock) { if (!String.IsNullOrEmpty(sql)) _dataTableCommand.CommandText = sql; SqliteDataReader reader = _dataTableCommand.ExecuteReader(); try { dt.Load(reader); } catch (System.Exception ex) { GenLib.Log.LogService.LogException("Error while executing a query: " + sql, ex); foreach (DataRow row in dt.GetErrors()) Trace.WriteLine(row.RowError); } finally { reader.Close(); } } } catch (Exception ex) { GenLib.Log.LogService.LogException("Error while executing a query: " + sql, ex); throw; } return dt; }
/// <summary> /// Builds a string out of DataColumns that have errors and gives the name of the /// column where the first error is stored. /// /// </summary> /// <param name="ADataTable">DataTable in which the DataColumn errors or all DataRows /// should be iterated.</param> /// <param name="AErrorMessages">String containing all DataColumns that have errors, /// separated by two CR+LF's.</param> /// <param name="AFirstErrorControlName">Name of the DataColumn where the first error is /// stored. /// </param> /// <returns>void</returns> public static void IterateErrorsInData(DataTable ADataTable, out String AErrorMessages, out String AFirstErrorControlName) { DataRow[] ErrorRows; int ErrorCounter; String ErrorMessages; String FirstErrorControlName; AErrorMessages = ""; AFirstErrorControlName = ""; ErrorRows = ADataTable.GetErrors(); for (ErrorCounter = 0; ErrorCounter <= ErrorRows.Length; ErrorCounter += 1) { IterateErrorsInData(ErrorRows[ErrorCounter], out ErrorMessages, out FirstErrorControlName); // MessageBox.Show('TDataBinding.IterateErrorsInData(DataTable).FirstErrorControlName: ' + FirstErrorControlName); AErrorMessages = AErrorMessages + ErrorMessages; if (ErrorCounter == 0) { AFirstErrorControlName = FirstErrorControlName; } } }
public void GetErrors () { DataTable table = new DataTable (); DataColumn col = new DataColumn (); col.ColumnName = "Id"; col.DataType = Type.GetType ("System.Int32"); table.Columns.Add (col); col = new DataColumn (); col.ColumnName = "Name"; col.DataType = Type.GetType ("System.String"); table.Columns.Add (col); DataRow row = table.NewRow (); row ["Id"] = 147; row ["name"] = "Abc"; row.RowError = "Error#1"; table.Rows.Add (row); AssertEquals ("#A01", 1, table.GetErrors ().Length); AssertEquals ("#A02", "Error#1", (table.GetErrors ())[0].RowError); }
public static void PrintTableErrors(DataTable table) { // Test if the table has errors. If not, skip it. //if(table.HasErrors) { // Get an array of all rows with errors. var rowsInError = table.GetErrors(); // Print the error of each column in each row. for(int i = 0; i < rowsInError.Length; i++) { foreach(DataColumn column in table.Columns) { Console.WriteLine(column.ColumnName + " " + rowsInError[i].GetColumnError(column)); } // Clear the row errors rowsInError[i].ClearErrors(); } } }
/// <summary> /// Сохранить изменения таблицы в БД /// </summary> public static bool UpdateData(DataTable dataTable, out string errMsg) { try { if (dataTable != null) { SqlCeDataAdapter adapter = dataTable.ExtendedProperties["DataAdapter"] as SqlCeDataAdapter; if (adapter != null) { adapter.Update(dataTable); if (dataTable.HasErrors) { DataRow[] rowsInError = dataTable.GetErrors(); StringBuilder sb = new StringBuilder(); foreach (DataRow row in rowsInError) { string rowError = TranlateErrorMessage(row.RowError, dataTable); row.RowError = rowError; sb.AppendLine(rowError); } errMsg = AppPhrases.UpdateDataError + ":\r\n" + sb.ToString().TrimEnd(); return false; } } } errMsg = ""; return true; } catch (Exception ex) { errMsg = AppPhrases.UpdateDataError + ":\r\n" + ex.Message; return false; } }
private void btnImport_Click(object sender, EventArgs e) { // импорт выбранной таблицы из формата DAT StreamWriter writer = null; bool writeLog = chkImportLog.Checked; bool logCreated = false; string logFileName = AppData.ExeDir + "ScadaAdminImport.txt"; try { Tables.TableInfo tableInfo = cbTable.SelectedItem as Tables.TableInfo; if (tableInfo != null && AppData.Connected) { string fileName = txtFileName.Text.Trim(); if (writeLog) { writer = new StreamWriter(logFileName, false, Encoding.Default); logCreated = true; string title = DateTime.Now.ToString("G", Localization.Culture) + " " + AppPhrases.ImportTitle; writer.WriteLine(title); writer.WriteLine(new string('-', title.Length)); writer.WriteLine(AppPhrases.ImportTable + tableInfo.Name + " (" + tableInfo.Header + ")"); writer.WriteLine(AppPhrases.ImportFile + fileName); writer.WriteLine(); } // загрузка импортируемой таблицы BaseAdapter baseAdapter = new BaseAdapter(); DataTable srcTable = new DataTable(); baseAdapter.FileName = fileName; try { baseAdapter.Fill(srcTable, true); } catch (Exception ex) { throw new Exception(AppPhrases.LoadTableError + ":\r\n" + ex.Message); } if (writeLog) { writer.WriteLine(AppPhrases.SrcTableFields); writer.WriteLine(new string('-', AppPhrases.SrcTableFields.Length)); if (srcTable.Columns.Count > 0) { foreach (DataColumn column in srcTable.Columns) writer.WriteLine(column.ColumnName + " (" + column.DataType + ")"); } else { writer.WriteLine(AppPhrases.NoFields); } writer.WriteLine(); } // определение схемы таблицы БД DataTable destTable = new DataTable(tableInfo.Name); Tables.FillTableSchema(destTable); if (writeLog) { writer.WriteLine(AppPhrases.DestTableFields); writer.WriteLine(new string('-', AppPhrases.DestTableFields.Length)); if (destTable.Columns.Count > 0) { foreach (DataColumn column in destTable.Columns) writer.WriteLine(column.ColumnName + " (" + column.DataType + ")"); } else { writer.WriteLine(AppPhrases.NoFields); } writer.WriteLine(); } // установка контроля идентификаторов string firstColumnName = destTable.Columns.Count > 0 ? destTable.Columns[0].ColumnName : ""; bool firstColumnIsID = gbIDs.Enabled && (firstColumnName.EndsWith("ID") || firstColumnName.EndsWith("Num") || firstColumnName == "CnlStatus") && destTable.Columns[0].DataType == typeof(int); bool checkMinID = chkStartID.Checked && firstColumnIsID; bool checkMaxID = chkFinalID.Checked && firstColumnIsID; bool shiftID = chkNewStartID.Checked && firstColumnIsID; bool shiftDef = false; // смещение определено bool checkID = checkMaxID || checkMinID || shiftID; int minID = checkMinID ? Convert.ToInt32(numStartID.Value) : 0; int maxID = checkMaxID ? Convert.ToInt32(numFinalID.Value) : 0; int newStartID = shiftID ? Convert.ToInt32(numNewStartID.Value) : 0; int shift = 0; // заполнение таблицы БД foreach (DataRow row in srcTable.Rows) { DataRow newRow = destTable.NewRow(); bool rowIsOk = true; foreach (DataColumn column in destTable.Columns) { int ind = srcTable.Columns.IndexOf(column.ColumnName); if (ind >= 0 && column.DataType == srcTable.Columns[ind].DataType) { object val = row[ind]; if (ind == 0 && checkID && val != null && val != DBNull.Value) { // проверка идентификатора int id = (int)val; if (checkMinID && id < minID || checkMaxID && id > maxID) { rowIsOk = false; break; } if (shiftID && !shiftDef) { shift = newStartID - id; shiftDef = true; } newRow[column] = id + shift; } else newRow[column] = val; } } if (rowIsOk) destTable.Rows.Add(newRow); } // сохранение информации в БД int updRows = 0; int errRows = 0; DataRow[] rowsInError = null; try { SqlCeDataAdapter sqlAdapter = destTable.ExtendedProperties["DataAdapter"] as SqlCeDataAdapter; updRows = sqlAdapter.Update(destTable); } catch (Exception ex) { throw new Exception(AppPhrases.WriteDBError + ":\r\n" + ex.Message); } if (destTable.HasErrors) { rowsInError = destTable.GetErrors(); errRows = rowsInError.Length; } string msg; if (errRows == 0) { msg = string.Format(AppPhrases.ImportCompleted, updRows); ScadaUtils.ShowInfo(updRows > 0 ? msg + AppPhrases.RefreshRequired : msg); } else { msg = string.Format(AppPhrases.ImportCompletedWithErr, updRows, errRows); AppData.ErrLog.WriteAction(msg, Log.ActTypes.Error); ScadaUtils.ShowError(updRows > 0 ? msg + AppPhrases.RefreshRequired : msg); } if (writeLog) { writer.WriteLine(AppPhrases.ImportResult); writer.WriteLine(new string('-', AppPhrases.ImportResult.Length)); writer.WriteLine(msg); if (errRows > 0) { writer.WriteLine(); writer.WriteLine(AppPhrases.ImportErrors); writer.WriteLine(new string('-', AppPhrases.ImportErrors.Length)); foreach (DataRow row in rowsInError) { if (firstColumnIsID) { object objVal = row[0]; string strVal = objVal == null || objVal == DBNull.Value ? "NULL" : objVal.ToString(); writer.Write(firstColumnName + " = " + strVal + " : "); } writer.WriteLine(row.RowError); } } } } } catch (Exception ex) { string errMsg = AppPhrases.ImportError + ":\r\n" + ex.Message; try { if (writeLog) writer.WriteLine(errMsg); } catch { } AppUtils.ProcError(errMsg); } finally { try { writer.Close(); } catch { } } if (writeLog && logCreated) Process.Start(logFileName); }
/// <summary> /// Allows the programmer to run a query against the Database. /// </summary> /// <param name="sql">The SQL to run</param> /// <returns>A DataTable containing the result set.</returns> public DataTable GetDataTable(SqliteCommand command) { DataTable dt = new DataTable(); try { lock (_lock) { SqliteDataAdapter adapter =new SqliteDataAdapter(command); try { adapter.Fill(dt); } catch (System.Exception ex) { GenLib.Log.LogService.LogException("Error while executing a query: " + command.CommandText, ex); foreach (DataRow row in dt.GetErrors()) Trace.WriteLine(row.RowError); } } } catch (Exception ex) { GenLib.Log.LogService.LogException("Error while executing a query: " + command.CommandText, ex); throw; } return dt; }
//~SqliteDatabase() //{ // CloseConnection(); //} /// <summary> /// Allows the programmer to run a query against the Database. /// </summary> /// <param name="sql">The SQL to run</param> /// <returns>A DataTable containing the result set.</returns> public DataTable GetDataTable(string sql) { DataTable dt = new DataTable(); try { lock (_lock) { if (!String.IsNullOrEmpty(sql)) _dataTableCommand.CommandText = sql; SqliteDataAdapter adapter =new SqliteDataAdapter(_dataTableCommand); try { adapter.Fill(dt); } catch (System.Exception ex) { GenLib.Log.LogService.LogException("Error while executing a query: " + sql, ex); foreach (DataRow row in dt.GetErrors()) Trace.WriteLine(row.RowError); } } } catch (Exception ex) { GenLib.Log.LogService.LogException("Error while executing a query: " + sql, ex); throw; } return dt; }
private int ActualizaBase(DataTable conjuntoDeDatos) { try { if (conjuntoDeDatos == null) return -1; // Comprobar errores DataRow[] RenglonesMal = conjuntoDeDatos.GetErrors(); // Si no hay errores se actualiza la base de // datos. En otro caso se avisa al usuario if (RenglonesMal.Length == 0) { int numeroDeRenglones = miAdaptador.Update(conjuntoDeDatos); conjuntoDeDatos.AcceptChanges(); Error = ""; misDatos = conjuntoDeDatos; return numeroDeRenglones; } else { Error = ""; foreach (DataRow renglon in RenglonesMal) { foreach (DataColumn columna in renglon.GetColumnsInError()) { Error += renglon.GetColumnError(columna) + "\n"; } } conjuntoDeDatos.RejectChanges(); misDatos = conjuntoDeDatos; return -1; } } catch (Exception ex) { Error = ex.Message; return -1; } }
public DataTable GetDataTable(SQLiteCommand command, params SQLiteParameter[] sqlParam) { if (command == null) throw new ArgumentNullException("command"); using (SQLiteConnection connection = new SQLiteConnection(_dbConnection)) { connection.Open(); command.Connection = connection; if (sqlParam != null) { foreach (var o in sqlParam) { command.Parameters.Add(o); } } using (SQLiteDataReader reader = command.ExecuteReader()) { DataTable result = new DataTable(); try { result.Load(reader); } catch { var err = result.GetErrors(); throw; } return result; } } }
public void GetErrors () { DataTable table = new DataTable (); DataColumn col = new DataColumn (); col.ColumnName = "Id"; col.DataType = typeof (int); table.Columns.Add (col); col = new DataColumn (); col.ColumnName = "Name"; col.DataType = typeof (string); table.Columns.Add (col); DataRow row = table.NewRow (); row ["Id"] = 147; row ["name"] = "Abc"; row.RowError = "Error#1"; table.Rows.Add (row); Assert.AreEqual (1, table.GetErrors ().Length, "#A01"); Assert.AreEqual ("Error#1", (table.GetErrors ())[0].RowError, "#A02"); }
private static void FixTable(DataTable DataTable, IDbConnection conn) { if (DataTable.HasErrors) { Trace.WriteLine(string.Format("Fixing table {0}...", DataTable.TableName)); foreach (DataRow r in DataTable.GetErrors()) { if (r.RowError.Length > 0) { Trace.WriteLine("Fixing row error: " + r.RowError); } else { Trace.WriteLine("Fixing row error: (no error description)"); } GisaDataSetHelperRule.Current.FixRow(GisaDataSetHelper.GetInstance(), r, conn); } } }