SQLiteDataAdapter ignoriert Table Constraints

  • C#
  • .NET (FX) 4.5–4.8

Es gibt 1 Antwort in diesem Thema. Der letzte Beitrag () ist von ErfinderDesRades.

    SQLiteDataAdapter ignoriert Table Constraints

    Hallo Leute,

    ich habe ein DataGridView Control, welches bearbeitet werden kann,
    die änderungen im Grid werden auf das darunter liegende DataTable Objekt übertragen.

    Per SQLiteDataAdapter führe ich dann die änderungen auf die Datenbank aus.
    Problem ist, dass das DataAdapter Objekt jegliche Contraints außer kraft setzt...

    Beispiel mit 2 Tabellen:
    Spoiler anzeigen

    Table tbl_CostCenter:
    - CostCenterPK ( VARCHAR(12) UNIQUE NOT NULL PRIMARY KEY)
    - Description (TEXT)

    Table tbl_Employee
    - [Standard Felder wie Name Abteilung etc...]
    - CostCenterFK (FOREIGN KEY NOT NULL)


    Versuche ich in der Datenbank direkt eine Kostenstelle zu löschen, erhalte ich einen Fehler wegen des FOREIGN KEY NOT NULL Constraints.
    Das ist klasse.
    Lösche ich per SQLiteDataAdapter wird dies komplett ignoriert und der Datensatz wird einfach aus der Tabelle tbl_CostCenter gelöscht.

    Hier zusätzlich noch ein bisschen Code welcher ausgeführt wird um die Datenbank zu updaten:
    Spoiler anzeigen

    Button Click events die die Änderungen gültig machen:
    Spoiler anzeigen

    C#-Quellcode

    1. // Deletes the current Row from the DataGridView DataSource
    2. // IMPORTANT NOTE: Do not use AcceptChanges() on DataTable yet.
    3. // The SQLDataAdapter will not recognize the changes made
    4. // due to the RowState Property of DataTable
    5. private void btn_Remove_Click(object sender, EventArgs e)
    6. {
    7. _lastPressed = LastPressed.DELETE;
    8. moveActiveOperationPanel(sender);
    9. var row = ((DataRowView)buffered_TableView.CurrentRow.DataBoundItem).Row;
    10. var source = (DataTable)buffered_TableView.DataSource;
    11. row.Delete();
    12. }
    13. // Update the Database and reload the DataTable at the end
    14. private void btn_Commit_Click(object sender, EventArgs e)
    15. {
    16. _lastPressed = LastPressed.COMMIT;
    17. moveActiveOperationPanel(sender);
    18. var table = (DataTable)buffered_TableView.DataSource;
    19. _tcn.boldUpdate(ref table);
    20. displayData(_currentTable);
    21. }


    boldUpdate Methode:
    Spoiler anzeigen

    C#-Quellcode

    1. public bool boldUpdate(ref System.Data.DataTable table)
    2. {
    3. if (!connected())
    4. throw new SQLiteException(SQLiteErrorCode.CantOpen, "Die Datenbank konnte nicht geöffnet werden");
    5. string select = $"SELECT * FROM {table.TableName};";
    6. SQLiteDataAdapter sqlDa = null;
    7. SQLiteCommandBuilder builder = null;
    8. try
    9. {
    10. sqlDa = new SQLiteDataAdapter();
    11. sqlDa.SelectCommand = new SQLiteCommand(select, _db.Connection);
    12. builder = new SQLiteCommandBuilder(sqlDa);
    13. sqlDa.UpdateCommand = builder.GetUpdateCommand();
    14. sqlDa.ContinueUpdateOnError = false;
    15. sqlDa.Update(table);
    16. return true;
    17. } catch (Exception ex) {
    18. var a = ex.Message;
    19. return false;
    20. } finally {
    21. try {
    22. if (!global_db_state_open)
    23. _db.Connection.Close();
    24. if (builder != null)
    25. builder.Dispose();
    26. if (sqlDa != null)
    27. sqlDa.Dispose();
    28. } catch { }
    29. }
    30. }





    EDIT 2019-02.08:
    Spoiler anzeigen

    Ich gehe vom DataAdapter weg und mache es etwas "aufwändiger", aber dafür funktioniert es wenigstens.
    Per DataTable Extension Class hole ich mir jetzt alle modifizierten DataRows des DataTable.
    Spoiler anzeigen

    Extension:

    C#-Quellcode

    1. public static class DataTableExtension
    2. {
    3. public static int getIndex(this DataRow r)
    4. {
    5. DataTable parent = r.Table;
    6. if (parent == null)
    7. return -1;
    8. int i = 0;
    9. foreach (DataRow row in parent.Rows)
    10. {
    11. if (row == r)
    12. return i;
    13. i++;
    14. }
    15. return -1;
    16. }
    17. public static List<DataRow> modifiedRows(this DataTable table, DataRowState state)
    18. {
    19. if (table.Rows.Count==0)
    20. return new List<DataRow>();
    21. List<DataRow> modified = new List<DataRow>();
    22. foreach (DataRow row in table.Rows) {
    23. if ( (row.RowState & state) == DataRowState.Added
    24. || (row.RowState & state) == DataRowState.Deleted
    25. || (row.RowState & state) == DataRowState.Modified
    26. || (row.RowState & state) == DataRowState.Detached
    27. || (row.RowState & state) == DataRowState.Unchanged
    28. ) modified.Add(row);
    29. }
    30. return modified;
    31. }
    32. }


    Ausschnitt aus dem Button code:

    C#-Quellcode

    1. // Get DataTable from GridView Datasource
    2. var table = (DataTable)buffered_TableView.DataSource;
    3. // Get all modified DataRows by calling the modifiedRows() extension Method
    4. var modified = table.modifiedRows(
    5. DataRowState.Modified
    6. | DataRowState.Deleted
    7. | DataRowState.Added
    8. );
    9. // Get the indizes of the modified rows
    10. var indizes = modified.Select( m => m.getIndex()).ToList();



    Danach teile ich nach Art der Modifikation auf und entscheide somit welche SQL Queries generiert werden müssen.
    SELECT, UPDATE, DELETE, INSERT werden jeweils in eigenen Transactions ausgeführt.
    Waren die DELETE Statements also inkorrekt oder "verboten" werden ansonsten alle anderen normal durchgeführt.



    Edit 2019-02-18:

    Hallo Leute,
    ich habe das oben beschriebene Problem gelöst, indem ich den DataAdapter mehr oder weniger selbst gemacht habe.
    Ich verwende 2 DataTable Extensions um die geänderten Rows in einem Struct zu erhalten.
    Per class, welche den jeweiligen Table darstellt, erstelle ich mir meine Queries genauso wie ich sie brauche.

    ! Ab hier nur noch Code !
    DataTable Extensions:
    Spoiler anzeigen

    C#-Quellcode

    1. public static class DataTableExtension
    2. {
    3. public static int getIndex(this DataRow r)
    4. {
    5. DataTable parent = r.Table;
    6. if (parent == null)
    7. return -1;
    8. int i = 0;
    9. foreach (DataRow row in parent.Rows)
    10. {
    11. if (row == r)
    12. return i;
    13. i++;
    14. }
    15. return -1;
    16. }
    17. public static List<DataRow> modifiedRows(this DataTable table, DataRowState state)
    18. {
    19. if (table.Rows.Count==0)
    20. return new List<DataRow>();
    21. List<DataRow> modified = new List<DataRow>();
    22. foreach (DataRow row in table.Rows) {
    23. if ( (row.RowState & state) == DataRowState.Added
    24. || (row.RowState & state) == DataRowState.Deleted
    25. || (row.RowState & state) == DataRowState.Modified
    26. || (row.RowState & state) == DataRowState.Detached
    27. || (row.RowState & state) == DataRowState.Unchanged
    28. ) modified.Add(row);
    29. }
    30. return modified;
    31. }
    32. public static List<EditDataRow> copyAndReject(this DataTable table, DataRowState state)
    33. {
    34. List<EditDataRow> copies = new List<EditDataRow>();
    35. List<DataRow> modified = table.modifiedRows(state);
    36. if (!modified.Any())
    37. return new List<EditDataRow>();
    38. for (int i=0; i<modified.Count; i++)
    39. {
    40. var row = new EditDataRow();
    41. var rowState = modified[i].RowState;
    42. if (rowState == DataRowState.Modified) {
    43. row.newItemArray = modified[i].ItemArray;
    44. }
    45. if (rowState == DataRowState.Deleted || rowState == DataRowState.Unchanged || rowState == DataRowState.Modified) {
    46. modified[i].RejectChanges();
    47. modified[i].AcceptChanges();
    48. }
    49. row.parent = table;
    50. row.rowState = rowState;
    51. row.itemArray = modified[i].ItemArray;
    52. if (rowState != DataRowState.Added && rowState != DataRowState.Detached)
    53. row.index = modified[i].getIndex();
    54. else
    55. row.index = table.Rows.Count;
    56. copies.Add(row);
    57. }
    58. return copies;
    59. }



    EditDataRow Struct:
    Spoiler anzeigen

    C#-Quellcode

    1. public struct EditDataRow
    2. {
    3. public object[] itemArray{ get; set; }
    4. public object[] newItemArray { get; set; }
    5. public int index { get; set; }
    6. public DataTable parent { get; set; }
    7. public DataRowState rowState { get; set; }
    8. }



    Beispiel Table class welche Queries generiert:
    Spoiler anzeigen

    C#-Quellcode

    1. public interface ITableObject
    2. {
    3. string buildSelect();
    4. string buildSelectThis();
    5. string buildInsert();
    6. string buildDelete();
    7. string buildQuery(System.Data.DataRowState state);
    8. }
    9. class UserObject : ITableObject
    10. {
    11. private int _userPK;
    12. private string _firstname;
    13. private string _lastname;
    14. private string _initials;
    15. private int _centerFK;
    16. public UserObject()
    17. {
    18. }
    19. public UserObject(int userPK, string firstname, string lastname, string initials, int centerFK)
    20. {
    21. UserPK = userPK;
    22. Firstname = firstname;
    23. Lastname = lastname;
    24. Initials = initials;
    25. CenterFK = centerFK;
    26. }
    27. public string buildQuery(System.Data.DataRowState state)
    28. {
    29. if (state == DataRowState.Modified)
    30. return string.Empty;
    31. switch (state)
    32. {
    33. case System.Data.DataRowState.Added:
    34. return buildInsert();
    35. case System.Data.DataRowState.Deleted:
    36. return buildDelete();
    37. case System.Data.DataRowState.Modified:
    38. throw new TableObjectMissingException("A object of Type 'UserObject' is required for updating a table!");
    39. case System.Data.DataRowState.Unchanged:
    40. throw new UnsupportedDataRowStateException(state);
    41. case System.Data.DataRowState.Detached:
    42. throw new UnsupportedDataRowStateException(state);
    43. default:
    44. return buildSelectThis();
    45. }
    46. }
    47. public string buildQuery(System.Data.DataRowState state, UserObject old)
    48. {
    49. switch (state)
    50. {
    51. case System.Data.DataRowState.Added:
    52. return buildInsert();
    53. case System.Data.DataRowState.Deleted:
    54. return buildDelete();
    55. case System.Data.DataRowState.Modified:
    56. return buildUpdate(old);
    57. case System.Data.DataRowState.Unchanged:
    58. throw new UnsupportedDataRowStateException(state);
    59. case System.Data.DataRowState.Detached:
    60. throw new UnsupportedDataRowStateException(state);
    61. default:
    62. return buildSelectThis();
    63. }
    64. }
    65. public string buildSelect()
    66. {
    67. return "SELECT User_PK, Firstname, Lastname, Initials, Center_FK FROM tbl_User ORDER BY Lastname, Firstname;";
    68. }
    69. public string buildSelectThis()
    70. {
    71. return
    72. "SELECT * " +
    73. "FROM tbl_User " +
    74. $"WHERE User_PK = {_userPK} AND " +
    75. $"Firstname = '{_firstname}' AND " +
    76. $"Lastname = '{_lastname}' AND " +
    77. $"Initials = '{_initials}' AND " +
    78. $"Center_FK = {_centerFK};";
    79. }
    80. public string buildUpdate(UserObject old)
    81. {
    82. string query =
    83. $"UPDATE tbl_User "+
    84. $"SET Firstname = '{_firstname}', "+
    85. $"Lastname = '{_lastname}', "+
    86. $"Initials = '{_initials}', "+
    87. $"Center_FK = {_centerFK} "+
    88. $"WHERE User_PK = {old.UserPK} AND "+
    89. $"Firstname = '{old._firstname}' AND "+
    90. $"Lastname = '{old._lastname}' AND "+
    91. $"Initials = '{old._initials}' AND "+
    92. $"Center_FK = {old.CenterFK};";
    93. return query;
    94. }
    95. public string buildInsert()
    96. {
    97. string query =
    98. "INSERT OR IGNORE INTO tbl_User ("+
    99. "Firstname, "+
    100. "Lastname, "+
    101. "Initials, "+
    102. "Center_FK "+
    103. ") VALUES ("+
    104. $"'{_firstname}',"+
    105. $"'{_lastname}',"+
    106. $"'{_initials}',"+
    107. $"{_centerFK});";
    108. return query;
    109. }
    110. public string buildDelete()
    111. {
    112. return
    113. "DELETE FROM tbl_User "+
    114. $"WHERE User_PK = {_userPK} AND "+
    115. $"Firstname = '{_firstname}' AND "+
    116. $"Lastname = '{_lastname}' AND "+
    117. $"Initials = '{_initials}' AND "+
    118. $"Center_FK = {_centerFK};";
    119. }
    120. public int UserPK
    121. {
    122. get
    123. {
    124. return _userPK;
    125. }
    126. private set
    127. {
    128. _userPK = value;
    129. }
    130. }
    131. public string Firstname
    132. {
    133. get
    134. {
    135. return _firstname;
    136. }
    137. set
    138. {
    139. if (value.Length > 50)
    140. throw new CharCountException(50, value.Length);
    141. _firstname = value;
    142. }
    143. }
    144. public string Lastname
    145. {
    146. get
    147. {
    148. return _lastname;
    149. }
    150. set
    151. {
    152. if (value.Length > 100)
    153. throw new CharCountException(100, value.Length);
    154. _lastname = value;
    155. }
    156. }
    157. public string Initials
    158. {
    159. get
    160. {
    161. return _initials;
    162. }
    163. set
    164. {
    165. if (value.Length > 10)
    166. throw new CharCountException(10, value.Length);
    167. _initials = value;
    168. }
    169. }
    170. public int CenterFK
    171. {
    172. get
    173. {
    174. return _centerFK;
    175. }
    176. set
    177. {
    178. _centerFK = value;
    179. }
    180. }
    181. }




    Exceptions:
    Spoiler anzeigen

    C#-Quellcode

    1. #region CharCountException
    2. [Serializable]
    3. public sealed class CharCountException : Exception
    4. {
    5. public CharCountException()
    6. {
    7. throw new CharCountException(
    8. "Char count of parameter exceeded its allowed maximum");
    9. }
    10. public CharCountException(string message)
    11. : base (message)
    12. {
    13. throw new CharCountException(message);
    14. }
    15. public CharCountException(string message, Exception inner)
    16. : base (message, inner)
    17. {
    18. throw new CharCountException(message, inner);
    19. }
    20. public CharCountException(int max, int inputLength)
    21. {
    22. throw new CharCountException(
    23. $"Value exceeded the allowed maximum length.{Environment.NewLine}" +
    24. $"Maximum length: {max}{Environment.NewLine}"+
    25. $"Passed length: {inputLength}");
    26. }
    27. }
    28. #endregion
    29. #region TableObjectMissingException
    30. [Serializable]
    31. public sealed class TableObjectMissingException : Exception
    32. {
    33. public TableObjectMissingException()
    34. {
    35. throw new TableObjectMissingException(
    36. "Missing an object of Type ITableObject");
    37. }
    38. public TableObjectMissingException(string message)
    39. : base(message)
    40. {
    41. throw new TableObjectMissingException(message);
    42. }
    43. public TableObjectMissingException(string message, Exception inner)
    44. : base (message, inner)
    45. {
    46. throw new TableObjectMissingException(message, inner);
    47. }
    48. public TableObjectMissingException(string inputQuery, string missingObject)
    49. {
    50. throw new TableObjectMissingException(
    51. $"The input does not contain the required table object!{Environment.NewLine}" +
    52. $"Input: {inputQuery}{Environment.NewLine}"+
    53. $"Required Object: {missingObject}");
    54. }
    55. }
    56. #endregion
    57. #region UnsupportedDataRowStateException
    58. [Serializable]
    59. public sealed class UnsupportedDataRowStateException : Exception
    60. {
    61. public UnsupportedDataRowStateException()
    62. {
    63. throw new UnsupportedDataRowStateException(
    64. "This type of DataRowState is not supported by classes of the ITableObject");
    65. }
    66. public UnsupportedDataRowStateException(string message)
    67. : base(message)
    68. {
    69. throw new UnsupportedDataRowStateException(message);
    70. }
    71. public UnsupportedDataRowStateException(string message, Exception inner)
    72. : base (message, inner)
    73. {
    74. throw new UnsupportedDataRowStateException(message, inner);
    75. }
    76. public UnsupportedDataRowStateException(System.Data.DataRowState state)
    77. {
    78. throw new UnsupportedDataRowStateException(
    79. $"The DataRowState {state} is not supported by objects of type ITableObject");
    80. }
    81. }
    82. #endregion




    Button Click Event Code:
    Spoiler anzeigen

    C#-Quellcode

    1. // Update the Database and reload the DataTable at the end
    2. private void btn_Commit_Click(object sender, EventArgs e)
    3. {
    4. if (_currentTable == DisplayOption.MNET) {
    5. MessageBox.Show("Alle verändernden Aktionen sind auf dieser Tabelle gesperrt");
    6. return;
    7. }
    8. _lastPressed = LastPressed.COMMIT;
    9. moveActiveOperationPanel(sender);
    10. // Get DataTable from GridView Datasource
    11. var table = (DataTable)buffered_TableView.DataSource;
    12. // Get array of EditDataRow by calling the copyAndReject Extension
    13. // This will return an array of a struct that contains all neccessary DataRow information
    14. // We can then process the EditDataRows to create Queries for each table
    15. // Queries will be created by ITableObject.buildQuery()
    16. // This function needs a RowState enum to determine which query needs to be generated
    17. var modified = table.copyAndReject(
    18. DataRowState.Modified
    19. | DataRowState.Deleted
    20. | DataRowState.Added
    21. ).ToArray();
    22. // generateQueries will check the current Table enum
    23. // It will then create an string array that contains all queries
    24. // which the user more or less executed in the DataGridView control
    25. string[] queries = generateQueries(modified);
    26. // Use the boldCommit() function of the MySQLite TableConnector
    27. // This will execute all queries without a transaction chain!
    28. // Changes will be done until it catches any errors
    29. //
    30. // NOTE: boldComit(query, true) => true means that all queries will be logged in a file
    31. _tcn.boldCommit(queries, true);
    32. // Reject all changes just to be sure that we do not change anything
    33. // that we do not want to change
    34. table.RejectChanges();
    35. // Display the new data by calling the displayData() function
    36. // This will load the whole Table after each commit click!
    37. displayData(_currentTable);
    38. }
    39. private string[] generateQueries(EditDataRow[] rows)
    40. {
    41. switch (_currentTable)
    42. {
    43. case DisplayOption.COST_CENTER:
    44. return generateCenterQueries(rows);
    45. case DisplayOption.MNET:
    46. return generateMnetQueries(rows);
    47. case DisplayOption.PHONE:
    48. return generatePhoneQueries(rows);
    49. case DisplayOption.PHONE_USER_MAP:
    50. return generatePUMapQueries(rows);
    51. case DisplayOption.USER:
    52. return generateUserQueries(rows);
    53. default:
    54. return new string[0];
    55. }
    56. }
    57. private string[] generateUserQueries(EditDataRow[] rows)
    58. {
    59. List<string> queries = new List<string>();
    60. for (int i=0; i<rows.Length; i++)
    61. {
    62. string query = string.Empty;
    63. UserObject obj = null;
    64. if (rows[i].rowState == DataRowState.Modified && rows[i].newItemArray != null)
    65. {
    66. var oldUser = new UserObject(
    67. (int)rows[i].itemArray[0],
    68. rows[i].itemArray[1].ToString(),
    69. rows[i].itemArray[2].ToString(),
    70. rows[i].itemArray[3].ToString(),
    71. (int)rows[i].itemArray[4]);
    72. obj = new UserObject(
    73. (int)rows[i].newItemArray[0],
    74. rows[i].newItemArray[1].ToString(),
    75. rows[i].newItemArray[2].ToString(),
    76. rows[i].newItemArray[3].ToString(),
    77. (int)rows[i].newItemArray[4]);
    78. query = obj.buildQuery(DataRowState.Modified, oldUser);
    79. }
    80. else
    81. {
    82. obj = new UserObject(
    83. (int)rows[i].itemArray[0],
    84. rows[i].itemArray[1].ToString(),
    85. rows[i].itemArray[2].ToString(),
    86. rows[i].itemArray[3].ToString(),
    87. (int)rows[i].itemArray[4]);
    88. query = obj.buildQuery(rows[i].rowState);
    89. }
    90. if (!string.IsNullOrEmpty(query))
    91. queries.Add(query);
    92. }
    93. return queries.ToArray();
    94. }



    Dieser Beitrag wurde bereits 5 mal editiert, zuletzt von „Petersilie“ ()