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);
    10. var row = ((DataRowView)buffered_TableView.CurrentRow.DataBoundItem).Row;
    11. var source = (DataTable)buffered_TableView.DataSource;
    12. row.Delete();
    13. }
    15. // Update the Database and reload the DataTable at the end
    16. private void btn_Commit_Click(object sender, EventArgs e)
    17. {
    18. _lastPressed = LastPressed.COMMIT;
    19. moveActiveOperationPanel(sender);
    21. var table = (DataTable)buffered_TableView.DataSource;
    22. _tcn.boldUpdate(ref table);
    24. displayData(_currentTable);
    25. }


    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");
    6. string select = $"SELECT * FROM {table.TableName};";
    8. SQLiteDataAdapter sqlDa = null;
    9. SQLiteCommandBuilder builder = null;
    10. try
    11. {
    12. sqlDa = new SQLiteDataAdapter();
    13. sqlDa.SelectCommand = new SQLiteCommand(select, _db.Connection);
    14. builder = new SQLiteCommandBuilder(sqlDa);
    16. sqlDa.UpdateCommand = builder.GetUpdateCommand();
    17. sqlDa.ContinueUpdateOnError = false;
    18. sqlDa.Update(table);
    20. return true;
    21. } catch (Exception ex) {
    22. var a = ex.Message;
    23. return false;
    24. } finally {
    25. try {
    26. if (!global_db_state_open)
    27. _db.Connection.Close();
    28. if (builder != null)
    29. builder.Dispose();
    30. if (sqlDa != null)
    31. sqlDa.Dispose();
    32. } catch { }
    33. }
    34. }





    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;
    9. int i = 0;
    10. foreach (DataRow row in parent.Rows)
    11. {
    12. if (row == r)
    13. return i;
    15. i++;
    16. }
    17. return -1;
    18. }
    20. public static List<DataRow> modifiedRows(this DataTable table, DataRowState state)
    21. {
    22. if (table.Rows.Count==0)
    23. return new List<DataRow>();
    25. List<DataRow> modified = new List<DataRow>();
    26. foreach (DataRow row in table.Rows) {
    27. if ( (row.RowState & state) == DataRowState.Added
    28. || (row.RowState & state) == DataRowState.Deleted
    29. || (row.RowState & state) == DataRowState.Modified
    30. || (row.RowState & state) == DataRowState.Detached
    31. || (row.RowState & state) == DataRowState.Unchanged
    32. ) modified.Add(row);
    33. }
    34. return modified;
    35. }
    36. }


    Ausschnitt aus dem Button code:

    C#-Quellcode

    1. // Get DataTable from GridView Datasource
    2. var table = (DataTable)buffered_TableView.DataSource;
    4. // Get all modified DataRows by calling the modifiedRows() extension Method
    5. var modified = table.modifiedRows(
    6. DataRowState.Modified
    7. | DataRowState.Deleted
    8. | DataRowState.Added
    9. );
    11. // Get the indizes of the modified rows
    12. 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;
    9. int i = 0;
    10. foreach (DataRow row in parent.Rows)
    11. {
    12. if (row == r)
    13. return i;
    15. i++;
    16. }
    17. return -1;
    18. }
    20. public static List<DataRow> modifiedRows(this DataTable table, DataRowState state)
    21. {
    22. if (table.Rows.Count==0)
    23. return new List<DataRow>();
    25. List<DataRow> modified = new List<DataRow>();
    26. foreach (DataRow row in table.Rows) {
    27. if ( (row.RowState & state) == DataRowState.Added
    28. || (row.RowState & state) == DataRowState.Deleted
    29. || (row.RowState & state) == DataRowState.Modified
    30. || (row.RowState & state) == DataRowState.Detached
    31. || (row.RowState & state) == DataRowState.Unchanged
    32. ) modified.Add(row);
    33. }
    34. return modified;
    35. }
    37. public static List<EditDataRow> copyAndReject(this DataTable table, DataRowState state)
    38. {
    39. List<EditDataRow> copies = new List<EditDataRow>();
    40. List<DataRow> modified = table.modifiedRows(state);
    41. if (!modified.Any())
    42. return new List<EditDataRow>();
    44. for (int i=0; i<modified.Count; i++)
    45. {
    46. var row = new EditDataRow();
    47. var rowState = modified[i].RowState;
    48. if (rowState == DataRowState.Modified) {
    49. row.newItemArray = modified[i].ItemArray;
    50. }
    51. if (rowState == DataRowState.Deleted || rowState == DataRowState.Unchanged || rowState == DataRowState.Modified) {
    52. modified[i].RejectChanges();
    53. modified[i].AcceptChanges();
    54. }
    56. row.parent = table;
    57. row.rowState = rowState;
    58. row.itemArray = modified[i].ItemArray;
    59. if (rowState != DataRowState.Added && rowState != DataRowState.Detached)
    60. row.index = modified[i].getIndex();
    61. else
    62. row.index = table.Rows.Count;
    63. copies.Add(row);
    64. }
    65. return copies;
    66. }



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




    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. }
    11. public CharCountException(string message)
    12. : base (message)
    13. {
    14. throw new CharCountException(message);
    15. }
    17. public CharCountException(string message, Exception inner)
    18. : base (message, inner)
    19. {
    20. throw new CharCountException(message, inner);
    21. }
    23. public CharCountException(int max, int inputLength)
    24. {
    25. throw new CharCountException(
    26. $"Value exceeded the allowed maximum length.{Environment.NewLine}" +
    27. $"Maximum length: {max}{Environment.NewLine}"+
    28. $"Passed length: {inputLength}");
    29. }
    30. }
    31. #endregion
    33. #region TableObjectMissingException
    34. [Serializable]
    35. public sealed class TableObjectMissingException : Exception
    36. {
    37. public TableObjectMissingException()
    38. {
    39. throw new TableObjectMissingException(
    40. "Missing an object of Type ITableObject");
    41. }
    43. public TableObjectMissingException(string message)
    44. : base(message)
    45. {
    46. throw new TableObjectMissingException(message);
    47. }
    49. public TableObjectMissingException(string message, Exception inner)
    50. : base (message, inner)
    51. {
    52. throw new TableObjectMissingException(message, inner);
    53. }
    55. public TableObjectMissingException(string inputQuery, string missingObject)
    56. {
    57. throw new TableObjectMissingException(
    58. $"The input does not contain the required table object!{Environment.NewLine}" +
    59. $"Input: {inputQuery}{Environment.NewLine}"+
    60. $"Required Object: {missingObject}");
    61. }
    62. }
    63. #endregion
    65. #region UnsupportedDataRowStateException
    66. [Serializable]
    67. public sealed class UnsupportedDataRowStateException : Exception
    68. {
    69. public UnsupportedDataRowStateException()
    70. {
    71. throw new UnsupportedDataRowStateException(
    72. "This type of DataRowState is not supported by classes of the ITableObject");
    73. }
    75. public UnsupportedDataRowStateException(string message)
    76. : base(message)
    77. {
    78. throw new UnsupportedDataRowStateException(message);
    79. }
    81. public UnsupportedDataRowStateException(string message, Exception inner)
    82. : base (message, inner)
    83. {
    84. throw new UnsupportedDataRowStateException(message, inner);
    85. }
    87. public UnsupportedDataRowStateException(System.Data.DataRowState state)
    88. {
    89. throw new UnsupportedDataRowStateException(
    90. $"The DataRowState {state} is not supported by objects of type ITableObject");
    91. }
    92. }
    93. #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. }
    9. _lastPressed = LastPressed.COMMIT;
    10. moveActiveOperationPanel(sender);
    12. // Get DataTable from GridView Datasource
    13. var table = (DataTable)buffered_TableView.DataSource;
    16. // Get array of EditDataRow by calling the copyAndReject Extension
    17. // This will return an array of a struct that contains all neccessary DataRow information
    18. // We can then process the EditDataRows to create Queries for each table
    19. // Queries will be created by ITableObject.buildQuery()
    20. // This function needs a RowState enum to determine which query needs to be generated
    21. var modified = table.copyAndReject(
    22. DataRowState.Modified
    23. | DataRowState.Deleted
    24. | DataRowState.Added
    25. ).ToArray();
    27. // generateQueries will check the current Table enum
    28. // It will then create an string array that contains all queries
    29. // which the user more or less executed in the DataGridView control
    30. string[] queries = generateQueries(modified);
    32. // Use the boldCommit() function of the MySQLite TableConnector
    33. // This will execute all queries without a transaction chain!
    34. // Changes will be done until it catches any errors
    35. //
    36. // NOTE: boldComit(query, true) => true means that all queries will be logged in a file
    37. _tcn.boldCommit(queries, true);
    39. // Reject all changes just to be sure that we do not change anything
    40. // that we do not want to change
    41. table.RejectChanges();
    43. // Display the new data by calling the displayData() function
    44. // This will load the whole Table after each commit click!
    45. displayData(_currentTable);
    46. }
    49. private string[] generateQueries(EditDataRow[] rows)
    50. {
    51. switch (_currentTable)
    52. {
    53. case DisplayOption.COST_CENTER:
    54. return generateCenterQueries(rows);
    55. case DisplayOption.MNET:
    56. return generateMnetQueries(rows);
    57. case DisplayOption.PHONE:
    58. return generatePhoneQueries(rows);
    59. case DisplayOption.PHONE_USER_MAP:
    60. return generatePUMapQueries(rows);
    61. case DisplayOption.USER:
    62. return generateUserQueries(rows);
    63. default:
    64. return new string[0];
    65. }
    66. }
    69. private string[] generateUserQueries(EditDataRow[] rows)
    70. {
    71. List<string> queries = new List<string>();
    72. for (int i=0; i<rows.Length; i++)
    73. {
    74. string query = string.Empty;
    75. UserObject obj = null;
    77. if (rows[i].rowState == DataRowState.Modified && rows[i].newItemArray != null)
    78. {
    79. var oldUser = new UserObject(
    80. (int)rows[i].itemArray[0],
    81. rows[i].itemArray[1].ToString(),
    82. rows[i].itemArray[2].ToString(),
    83. rows[i].itemArray[3].ToString(),
    84. (int)rows[i].itemArray[4]);
    86. obj = new UserObject(
    87. (int)rows[i].newItemArray[0],
    88. rows[i].newItemArray[1].ToString(),
    89. rows[i].newItemArray[2].ToString(),
    90. rows[i].newItemArray[3].ToString(),
    91. (int)rows[i].newItemArray[4]);
    93. query = obj.buildQuery(DataRowState.Modified, oldUser);
    94. }
    95. else
    96. {
    97. obj = new UserObject(
    98. (int)rows[i].itemArray[0],
    99. rows[i].itemArray[1].ToString(),
    100. rows[i].itemArray[2].ToString(),
    101. rows[i].itemArray[3].ToString(),
    102. (int)rows[i].itemArray[4]);
    104. query = obj.buildQuery(rows[i].rowState);
    105. }
    106. if (!string.IsNullOrEmpty(query))
    107. queries.Add(query);
    108. }
    109. return queries.ToArray();
    110. }



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