Hi,
wenn du einen DataAdapter nutzt, kannst du dich an RowApdated hängen und dann den zuletzt vergebenen Autowert zurücklesen und in das Datenobjekt eintragen. Als Beispiel habe ich auf die Schelle einen VB.NET Codeschnipsel gefunden:
Public Sub SaveData()
Try
Using da = Adapter
Dim cb As New MySqlCommandBuilder(da)
AddHandler da.RowUpdated, AddressOf da_updated
da.Update(dt)
End Using
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
Private Sub da_updated(sender As Object, e As MySqlRowUpdatedEventArgs)
Try
Dim idCMD As New MySqlCommand("SELECT LAST_INSERT_ID()", e.Command.Connection)
If e.StatementType = StatementType.Insert Then
Dim newID = CType(idCMD.ExecuteScalar(), Integer)
For Each col As DataColumn In e.Row.Table.Columns
If col.AutoIncrement = True Then
e.Row(col.ColumnName) = newID
Exit Sub
End If
Next
End If
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
In C#.NET kann eine Datenklasse dafür so aussehen:
using MySql.Data.MySqlClient;
using System;
using System.Data;
using System.Data.Odbc;
using System.Transactions;
using System.Windows.Forms;
namespace ConsoleApp1
{
public class Datenklasse
{
DataTable dt = new DataTable();
public BindingSource GetData()
{
try
{
using (MySqlDataAdapter da = Adapter)
{
da.Fill(dt);
dt.Columns["id"].AutoIncrement = true;
dt.Columns["id"].AutoIncrementSeed = -1;
dt.Columns["id"].AutoIncrementStep = -1;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
return new BindingSource() { DataSource = dt };
}
public void SaveData()
{
try
{
using (MySqlDataAdapter da = Adapter)
{
MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
da.RowUpdated += da_updated;
da.Update(dt);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void da_updated(object sender, MySqlRowUpdatedEventArgs e)
{
try
{
MySqlCommand idCMD = new MySqlCommand("SELECT LAST_INSERT_ID()", e.Command.Connection);
if (e.StatementType == StatementType.Insert)
{
int newID = (int)(idCMD.ExecuteScalar());
foreach (DataColumn col in e.Row.Table.Columns)
if (col.AutoIncrement == true)
{
e.Row[col.ColumnName] = newID;
return;
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public MySqlDataAdapter Adapter { get => new MySqlDataAdapter("SELECT * FROM Tab1", "Server=localhost;Database=test;Uid=dev1;Pwd=demo;"); }
}
}
--
Best Regards / Viele Grüße
Peter Fleischer (former MVP for Developer Technologies)
Homepage, Tipps, Tricks
