Abfragen automatisch vergebener ID in Datenbank

Question

Hallo,

ich habe folgendes Problem. Ich habe eine Master Tabelle, die eine AutoWert Spalte (Identity) hat. Nach dem Speichern eines neuen Datensatzes muss ich die ID (Autowert) haben um weitere Datensätze zu speichern, die auf diese ID aufbauen.

Ich habe folgendes im Netz gefunden, aber irgendwie klappt das nicht. Ich greife mit dem DataAdapter auf eine SQLServer2008 Datenbank zu.

con = new OdbcConnection(ConnectionString());

// Datenbank öffnen
this.con.Open();

// Daten per Datenadapter aktualisieren
this.dateAdapter.Update(this.dtTable);

// Den Id-Wert auslesen
cmd = new OdbcCommand("SELECT SCOPE_IDENTITY()", con);
locintLetzte_angelegte_ID = Convert.ToInt32(cmd.ExecuteScalar());

// Datenbank schliessen
this.con.Close();

Leider erhalte ich folgende Fehlermeldung: Ein Objekt kann nicht von DBNull in andere Typen umgewandelt werden.

d.h. die Funktion gibt mir keinen Wert zurück, und der Convert geht in die Hose. Aber wieso bekomme ich keine ID? Gibt es da vielleicht einen leichteren Weg. Insbesondere, wenn man das Argument Datenbankunabhängigkeit mit bedenkt.

 

Vielen Dank schon mal.

Gruß

Martin

Answer 1

Hi,

Du musst das Statement in derselben Connection absetzen, in der auch der Datensatz hinzugefügt wurde. Sinnvollerweise sofort nach dem INSERT. Also keine neue Instanz der Connection aufbauen, sondern die für den INSRET verwendete Connection an deine Methode übergeben.

BTW: Warum verwendest Du eigentlich Odbc? Wenn möglich, solltest Du eher SqlConnection nehmen.

 

---

Gruß, Stefan
Microsoft MVP - Visual Developer ASP/ASP.NET
http://www.asp-solutions.de/ - Consulting, Development
http://www.aspnetzone.de/ - ASP.NET Zone, die ASP.NET Community

Answer 2

Danke für die Antwort, aber so ganz bin ich da noch nicht im reinen mit. Das klappt nämlich nicht ganz.

Habe jetzt folgendes:

// Den Id-Wert auslesen
cmd.CommandText = "SELECT SCOPE_IDENTITY()";
cmd.Connection = con;
locintLetzte_angelegte_ID = Convert.ToInt32(cmd.ExecuteScalar());

Das klappt aber auch nicht.

Vielleicht liegt es ja an dem DataAdapter. Ich hole mir immer die leere Tabelle mit dem Dataadapter und Commandbuilder aus der Datenbank. Dann füge ich der DataTable in meinem Programm eine DataRow hinzu und machen den Update mit den DataAdapter. Den Rest macht dann der Commandbuilder. Liegt es vielleicht daran, dass der Code oben nicht funktioniert? Ich muss auch ehrlich zugeben, dass die ganzen Datenbankzugriffe nicht so meins sind.

 

Zu Deiner Frage mit dem ODBC.

Ich hoffe damit ein Datenbankunabhängiges Programm zu machen. Mein Programm greift auf eine hinterlegte .dsn Datei zurück, die entweder ein SQLNativeClient ist, oder ein OracleClient. Dadurch erhoffe ich mir, das Programm auf beiden laufen lassen zu können.

^^Soweit der Plan. Ob es 100%ig klappt, weiß ich leider noch nicht.

Answer 3

Zum Thema Datenbankunabhängig schau dir mal folgenden Link an:

http://msdn.microsoft.com/de-de/library/wda6c36e.aspx

Du wirst wahrscheinlich auch schon Probleme mit dem Befehl SELECT SCOPE_IDENTITY()
bekommen, da dieser ja auch schon an einem Datenbank Typ gebunden ist!

Answer 4

Ich bin immer noch dran, das Problem zu lösen, kriege es aber nicht hin.

Habe jetzt folgendes:

    public void InsertData(OdbcCommand loccmd)
    {
      con = new OdbcConnection(ConnectionString());

      // Datenbank öffnen
      this.con.Open();

      loccmd.Connection = con;

      loccmd.ExecuteNonQuery();

      loccmd.CommandText = "SELECT SCOPE_IDENTITY()";
      locintLetzte_angelegte_ID = Convert.ToInt32(loccmd.ExecuteScalar());

      // Datenbank schliessen
      this.con.Close();
    }

Der ODBC Command hat einen Insert SQL, der auch wurderbar einen Datensatz anfügt. Wieso klappt der es nicht, dass ich mit Scope_Identity() den AutoWert raus bekomme?

IDENT_CURRENT('table_name')

das klappt. Da muss ich die Tabelle übergeben und bekomme die ID. Nur ist dies gefährlich, bei Multiuserbetrieb.

Answer 5

Hallo Martin,

Du sollst keine neue Connection öffnen, sondern die dem Command zugewiesene (und noch geöffnete) Connection nehmen.

---

Gruß, Stefan
Microsoft MVP - Visual Developer ASP/ASP.NET
http://www.asp-solutions.de/ - Consulting, Development
http://www.aspnetzone.de/ - ASP.NET Zone, die ASP.NET Community

Answer 6

Hallo Martin,

> die dem Command zugewiesene (und noch geöffnete) Connection ...

damit ist die OdbcCommand.Connection-Eigenschaft gemeint.

Und auch von mir ... : nimm für DB-Unabhängigkeit kein ODBC sondern eher Klassen aus dem System.Data.Common-Namespace, oder Entity SQL.

 

---

ciao Frank

Answer 7

Hallo Leute,

langsam verzweifele ich hier. Eure Lösungen sind ja echt logisch, aber ich habe keine Ahnung, was ich immer noch falsch mache. Also mein übergebener ODBCCommand hat "nur" einen Commandtext und Parameter. In dieser Funktion oben bekommt er dann eine Connection zugewiesen. Die wird geöffnet und die Daten werden reingeschrieben. Ich öffne keine neue Connection. Der ODBCCommand bekommt erst in der Funktion eine Connection, die geöffnet wird. Deswegen verstehe ich nicht, wieso Ihr meint, dass ich eine Neue aufmache.

 

Mittlerweile sieht das ganze so aus: (Bei der Übergabe loccmd hat nur einen Commandtext inkl. Parameter / Keine Connection)

    public void InsertData(OdbcCommand loccmd)
    {
      con = new OdbcConnection(ConnectionString());

      loccmd.Connection = con;

      loccmd.Connection.Open();

      loccmd.ExecuteNonQuery();

      loccmd.CommandText = "SELECT SCOPE_IDENTITY()";
      locintLetzte_angelegte_ID = Convert.ToInt32(loccmd.ExecuteScalar());

      // Datenbank schliessen
      this.con.Close();
    }

Das ist der SQL:

"INSERT INTO HVP_Planmassnahmen (IDPlanung, Kostenstelle, Sachkonto, Betrag, IDVerteilung, Schluesselzuw, Kommentar) VALUES (?, ?, ?, ?, ?, ?, ?);SELECT IDPlanmassnahme = SCOPE_IDENTITY()"

Vielleicht liegt der Fehler ja da drin.

 

Danke für die System.Data.Common Tipps. Das schaue ich mir am Wochenende an. Scheint eine saubere Lösung zu sein.

Answer 8

Zusatz:

mit @@IDENTITY geht es. Ist das aber auch wirklich ein Lösung, mit der man im Multiuser Betrieb arbeiten kann?

Des Weiteren wollte ich noch nachfragen, wie diese Funktion z.B. in Oracle heißt. Oder gibt es da vielleicht einen brauchbaren Link, den ich mir durchlesen kann, wie man solche Problem sauber in verschiedenen Datenbanken lösen kann. Die unabhängigkeit werde ich mal mit dem System.Data.Common versuchen. Scheint ne gute Sache zu sein.

Answer 9

Hallo Martin,

SCOPE_IDENTITY muss im selben Gültigkeitsbereich wie die vorangehende Anweisung abgefragt werden. Laut Dokumentation ist ein Gültigkeitsbereich eine gespeicherte Prozedur, ein Trigger, eine Funktion oder ein Batch. Deine Multipart-Abfrage wird als Batch ausgeführt, dort hast Du die Möglichkeit SCOPE_IDENTY problemlos abzufragen. So funktioniert's z.B.:

public void RunTest()
{
 string insertSql = "INSERT INTO Kunden(Kundenname) VALUES(?); " +
  "SELECT KundenID, Kundenname FROM Kunden " +
  "WHERE KundenID=SCOPE_IDENTITY()";

 OdbcCommand loccmd = new OdbcCommand(insertSql);
 loccmd.Parameters.AddWithValue("?", "TEST");

 InsertData(loccmd);
}


public void InsertData(OdbcCommand loccmd)
{
 using (OdbcConnection con = new OdbcConnection(ConnectionString()))
 {
  loccmd.Connection = con;
  loccmd.Connection.Open();

  using (OdbcDataReader reader = loccmd.ExecuteReader())
  {
   while (reader.Read())
   {
    Console.WriteLine("KundenID: {0}", reader.GetString(0));
    Console.WriteLine("Kundenname: {0}", reader.GetString(1));
   }
  }
 }
}
<br/>

Ebenfalls aus der Dokumentation: "SCOPE_IDENTITY gibt jedoch nur im aktuellen Gültigkeitsbereich eingefügte Werte zurück, @@IDENTITY ist nicht auf einen bestimmten Gültigkeitsbereich begrenzt."

SCOPE_IDENTITY (Transact-SQL):
http://msdn.microsoft.com/de-de/library/ms190315.aspx

Answer 10

Hallo,

ich frische mal wieder ein altes Thema von mir auf. Das oben stehende habe ich nun endlich verstanden. Ich muss die Scope_Identity in den Command einbauen. Das klappt bei den Inserts, die ich direkt per SQL mache ganz gut.

Nun habe ich aber ein Konstrukt, das ich sehr oft nutze und eigentlich beibehalten wollen würde. Ich hole mit eine DataTable und mit einem DataAdapter. Nach dem Bearbeiten der Datatable z.B. hinzufügen neuer Datensätze schreibe ich diese per DataAdapter.Update(Datatble) zurück.

Um mit Scope_Identity arbeiten zu können, müsste dieser Befehle ja in dem Update des DataAdapters mit übergeben werden. Geht sowas?
Im Moment sieht meineFunktion so aus, und da müsste der Scope_Identity in das Update mit rein.

        /// <summary>
        /// Diese Funktion speichert den mit GetDataTable geholte Datatable inkl. der Änderungen wieder ab
        /// </summary>
        public void UpdateData()
        {
            con = new OdbcConnection(ConnectionString());

            // Datenbank öffnen
            this.con.Open();

            // Daten per Datenadapter aktualisieren
            this.dataAdapter.Update(this.dtTable);
            
            // Datenbank schliessen
            this.con.Close();
        }

---

Vielen Dank und Gruß Martin

Answer 11

Hallo,

habe mich jetzt auch länger damit beschäftigt und diverse Foren gelesen, aber keine Lösung gefunden. Also Quelle lesen bis unten hin. Auch ich arbeite meistens über die fertigen Tools aus der Visualisierung, da bringen mir direkte Objekte nicht viel.

Dein Ansatz ist richtig, Du musst die Verbindung vorher aufmachen (dadurch bleibt die Verbindung auf, wird sonst geöffnet und sofort wieder geschlossen) und dann den Update Befehl ausführen. Danach kannst Du den "SELECT @@IDENTITY" ausführen und der Rückwert ist die ID des Autowerts. Diesen kann man dann für die weiteren Querverbindungen benutzen oder ihn in den DataSet zurückschreiben damit die damit verbundene DataGridView auch eine gültige ID in den Items hat anstatt die "-1".

Anbei zu Verdeutlichung ein kleines Beispiel, die Variablen sollten selbsterklärend sein:

        if (Neu)
        {
          MaschinenDataSet.Maschinen.Rows.Add(DatenSatz);
          maschinenTableAdapter.Connection.Open();
        }

        maschinenTableAdapter.Update(DatenSatz);

        if (Neu)
        {
          OleDbCommand Kommando = maschinenTableAdapter.Connection.CreateCommand();
          Kommando.CommandText = "SELECT @@IDENTITY";
          string result = Kommando.ExecuteScalar().ToString();
          maschinenTableAdapter.Connection.Close();
          DatenSatz["ID"] = result;
        }

Gruß Holger

Answer 12

Hallo Holger,

Danach kannst Du den "SELECT @@IDENTITY" ausführen und der Rückwert ist die ID des Autowerts.

bei Access ist das korrekt. Für SQL Server (um den es hier geht) ist @@IDENTITY aber nicht das richtige, hier sollte man eher SELECT SCOPE_IDENTITY() einsetzen. Schau dir dazu auch mal diesen Thread an:

  http://social.msdn.microsoft.com/Forums/de-DE/sqlserverde/thread/9a8fbc18-4fae-45c4-9094-97b643b351fe

---

Gruß, Stefan
Microsoft MVP - Visual Developer ASP/ASP.NET
http://www.asp-solutions.de/ - Consulting, Development
http://www.aspnetzone.de/ - ASP.NET Zone, die ASP.NET Community

Answer 13

Hallo Stefan,

Danke für die Info. Das ist sehr hilfreich für mich. "Spiele" noch mit kleineren Programmen und Datenbanken rum um die Vorgehensweise komplett zu verstehen bevor ich mir einen konzeptionellen Fehler einhandel. Dein Link gibt mir sogar Antworten auf noch offene Fragen: korrekte Rückgabe bei Multiuser, was passiert wenn viel gleichzeitig, usw. Vielen Dank.

Mein Beispiel spielte hauptsächlich aber auf die Reihenfolge der Befehle ab. Denn so habe ich die Fragestellung verstanden
(-> "Um mit Scope_Identity arbeiten zu können, müsste dieser Befehle ja in dem Update des DataAdapters mit übergeben werden.")   und hatte selber auch das Problem. Habe die Grundbausteine des VisualStudios benutzt, kam aber immer an Probleme da die DataRow bei AutoWert einen negativen Wert einträgt, nach dem Update in der Datenbank aber schon die korrekte ID geschrieben wurde, die DataRow aber nicht aktualisiert wird.

Nur, wie bekomme ich jetzt diese ID in meine DataRow um direkt abhängige Tabellen mit der korrekten ID zu bestücken? Diese Frage habe ich als Antwort nicht gefunden.

Hierzu ein Nachsatz zu meinem Beispiel-Code. Hier ist noch ein Fehler drin. In der DataGridView wird nun zwar die korrekte ID angezeigt und kann demnach in anderen Tabellen verwendet werden, aber durch die letzte Zuweisung ist die gerade eingefügte DataRow wieder im Status "Modified" und dann stürzt das Programm ab da es beim Update einen Datensatz mit ID = -1 sucht. Nach "DatenSatz["ID"] = result;" muss noch der Befehl "DatenSatz.AcceptChanges()" aufgerufen werden. Dann wird der Status auf "UnChanged" geändert und es kann (ohne Neustart Programm bzw. Fill() ) ohne Probleme der Datensatz nachbearbeitet und gespeichert (!) werden.

Gruß Holger