Identiteits- of autonummeringswaarden ophalen
Een primaire sleutel in een relationele database is een kolom of combinatie van kolommen die altijd unieke waarden bevatten. Als u de waarde van de primaire sleutel kent, kunt u de rij zoeken die deze bevat. Relationele database-engines, zoals SQL Server, Oracle en Microsoft Access/Jet ondersteunen het maken van automatisch incrementele kolommen die kunnen worden aangewezen als primaire sleutels. Deze waarden worden door de server gegenereerd wanneer rijen worden toegevoegd aan een tabel. In SQL Server stelt u de identiteitseigenschap van een kolom in, in Oracle maakt u een reeks en maakt u in Microsoft Access een AutoNummering-kolom.
Een DataColumn kan ook worden gebruikt om automatisch incrementele waarden te genereren door de AutoIncrement eigenschap in te stellen op true. Het is echter mogelijk dat u dubbele waarden opgeeft in afzonderlijke exemplaren van een DataTable, als meerdere clienttoepassingen onafhankelijk automatisch incrementele waarden genereren. Als de server automatisch incrementele waarden genereert, worden potentiële conflicten geëlimineerd door elke gebruiker de gegenereerde waarde voor elke ingevoegde rij op te halen.
Tijdens een aanroep naar de Update methode van een DataAdapter, kan de database gegevens terugsturen naar uw ADO.NET-toepassing als uitvoerparameters of als de eerste geretourneerde record van de resultatenset van een SELECT-instructie die wordt uitgevoerd in dezelfde batch als de INSERT-instructie. ADO.NET kunt deze waarden ophalen en de bijbehorende kolommen in de DataRow bijgewerkte kolom bijwerken.
Sommige database-engines, zoals de Microsoft Access Jet-database-engine, bieden geen ondersteuning voor uitvoerparameters en kunnen niet meerdere instructies in één batch verwerken. Wanneer u met de Jet-database-engine werkt, kunt u de nieuwe AutoNummering-waarde ophalen die is gegenereerd voor een ingevoegde rij door een afzonderlijke SELECT-opdracht uit te voeren in een gebeurtenis-handler voor de gebeurtenis van de RowUpdated DataAdaptergebeurtenis.
Notitie
Een alternatief voor het gebruik van een waarde voor automatisch verhogen is het gebruik van de NewGuid methode van een Guid object om een GUID of globaal unieke id te genereren op de clientcomputer die naar de server kan worden gekopieerd wanneer elke nieuwe rij wordt ingevoegd. De NewGuid methode genereert een binaire waarde van 16 bytes die wordt gemaakt met behulp van een algoritme dat een hoge kans biedt dat er geen waarde wordt gedupliceerd. In een SQL Server-database wordt een GUID opgeslagen in een uniqueidentifier kolom die SQL Server automatisch kan genereren met behulp van de Transact-SQL-functie NEWID() . Het gebruik van een GUID als primaire sleutel kan de prestaties nadelig beïnvloeden. SQL Server biedt ondersteuning voor de NEWSEQUENTIALID() functie, waarmee een sequentiële GUID wordt gegenereerd die niet gegarandeerd wereldwijd uniek is, maar die efficiënter kan worden geïndexeerd.
Waarden voor SQL Server-identiteitskolommen ophalen
Wanneer u met Microsoft SQL Server werkt, kunt u een opgeslagen procedure maken met een uitvoerparameter om de identiteitswaarde voor een ingevoegde rij te retourneren. In de volgende tabel worden de drie Transact-SQL-functies in SQL Server beschreven die kunnen worden gebruikt om waarden voor identiteitskolommen op te halen.
| Functie | Beschrijving |
|---|---|
| SCOPE_IDENTITY | Retourneert de laatste identiteitswaarde binnen het huidige uitvoeringsbereik. SCOPE_IDENTITY wordt aanbevolen voor de meeste scenario's. |
| @@IDENTITY | Bevat de laatste identiteitswaarde die is gegenereerd in een tabel in de huidige sessie. @@IDENTITY kan worden beïnvloed door triggers en retourneert mogelijk niet de identiteitswaarde die u verwacht. |
| IDENT_CURRENT | Retourneert de laatste identiteitswaarde die is gegenereerd voor een specifieke tabel in een sessie en elk bereik. |
De volgende opgeslagen procedure laat zien hoe u een rij invoegt in de tabel Categorieën en een uitvoerparameter gebruikt om de nieuwe identiteitswaarde te retourneren die is gegenereerd door de functie Transact-SQL SCOPE_IDENTITY().
CREATE PROCEDURE dbo.InsertCategory
@CategoryName nvarchar(15),
@Identity int OUT
AS
INSERT INTO Categories (CategoryName) VALUES(@CategoryName)
SET @Identity = SCOPE_IDENTITY()
De opgeslagen procedure kan vervolgens worden opgegeven als de bron van het InsertCommand SqlDataAdapter object. De CommandType eigenschap van de InsertCommand moet worden ingesteld op StoredProcedure. De identiteitsuitvoer wordt opgehaald door een SqlParameter van te maken met een ParameterDirection van Output. Wanneer de InsertCommand identiteit wordt verwerkt, wordt de waarde voor automatisch verhogende identiteit geretourneerd en in de kolom CategoryID van de huidige rij geplaatst als u de UpdatedRowSource eigenschap van de invoegopdracht UpdateRowSource.OutputParameters instelt op of op UpdateRowSource.Both.
Als de invoegopdracht een batch uitvoert die zowel een INSERT-instructie als een SELECT-instructie bevat die de nieuwe identiteitswaarde retourneert, kunt u de nieuwe waarde ophalen door de UpdatedRowSource eigenschap van de opdracht Invoegen in te stellen op UpdateRowSource.FirstReturnedRecord.
static void RetrieveIdentity(string connectionString)
{
using (SqlConnection connection =
new(connectionString))
{
// Create a SqlDataAdapter based on a SELECT query.
SqlDataAdapter adapter =
new(
"SELECT CategoryID, CategoryName FROM dbo.Categories",
connection)
{
//Create the SqlCommand to execute the stored procedure.
InsertCommand = new SqlCommand("dbo.InsertCategory",
connection)
{
CommandType = CommandType.StoredProcedure
}
};
// Add the parameter for the CategoryName. Specifying the
// ParameterDirection for an input parameter is not required.
adapter.InsertCommand.Parameters.Add(
new SqlParameter("@CategoryName", SqlDbType.NVarChar, 15,
"CategoryName"));
// Add the SqlParameter to retrieve the new identity value.
// Specify the ParameterDirection as Output.
SqlParameter parameter =
adapter.InsertCommand.Parameters.Add(
"@Identity", SqlDbType.Int, 0, "CategoryID");
parameter.Direction = ParameterDirection.Output;
// Create a DataTable and fill it.
DataTable categories = new();
adapter.Fill(categories);
// Add a new row.
DataRow newRow = categories.NewRow();
newRow["CategoryName"] = "New Category";
categories.Rows.Add(newRow);
adapter.Update(categories);
Console.WriteLine("List All Rows:");
foreach (DataRow row in categories.Rows)
{
{
Console.WriteLine("{0}: {1}", row[0], row[1]);
}
}
}
}
Private Sub RetrieveIdentity(ByVal connectionString As String)
Using connection As SqlConnection = New SqlConnection( _
connectionString)
' Create a SqlDataAdapter based on a SELECT query.
Dim adapter As SqlDataAdapter = New SqlDataAdapter( _
"SELECT CategoryID, CategoryName FROM dbo.Categories", _
connection)
' Create the SqlCommand to execute the stored procedure.
adapter.InsertCommand = New SqlCommand("dbo.InsertCategory", _
connection)
adapter.InsertCommand.CommandType = CommandType.StoredProcedure
' Add the parameter for the CategoryName. Specifying the
' ParameterDirection for an input parameter is not required.
adapter.InsertCommand.Parameters.Add( _
"@CategoryName", SqlDbType.NVarChar, 15, "CategoryName")
' Add the SqlParameter to retrieve the new identity value.
' Specify the ParameterDirection as Output.
Dim parameter As SqlParameter = _
adapter.InsertCommand.Parameters.Add( _
"@Identity", SqlDbType.Int, 0, "CategoryID")
parameter.Direction = ParameterDirection.Output
' Create a DataTable and fill it.
Dim categories As DataTable = New DataTable
adapter.Fill(categories)
' Add a new row.
Dim newRow As DataRow = categories.NewRow()
newRow("CategoryName") = "New Category"
categories.Rows.Add(newRow)
' Update the database.
adapter.Update(categories)
Console.WriteLine("List All Rows:")
Dim row As DataRow
For Each row In categories.Rows
Console.WriteLine("{0}: {1}", row(0), row(1))
Next
End Using
End Sub
Nieuwe identiteitswaarden samenvoegen
Een veelvoorkomend scenario is het aanroepen van de GetChanges methode van een DataTable om een kopie te maken die alleen gewijzigde rijen bevat en om de nieuwe kopie te gebruiken bij het aanroepen van de Update methode van een DataAdapter. Dit is met name handig wanneer u de gewijzigde rijen moet marshalen naar een afzonderlijk onderdeel waarmee de update wordt uitgevoerd. Na de update kan de kopie nieuwe identiteitswaarden bevatten die vervolgens weer moeten worden samengevoegd in het origineel DataTable. De nieuwe identiteitswaarden verschillen waarschijnlijk van de oorspronkelijke waarden in de DataTable. Als u de samenvoegbewerking wilt uitvoeren, moeten de oorspronkelijke waarden van de autoaanmaakkolommen in de kopie behouden blijven om bestaande rijen in het origineel DataTablete kunnen zoeken en bij te werken in plaats van nieuwe rijen met de nieuwe identiteitswaarden toe te voegen. Deze oorspronkelijke waarden gaan echter standaard verloren na een aanroep van de Update methode van een DataAdapter, omdat AcceptChanges impliciet wordt aangeroepen voor elke bijgewerkte DataRowwaarde.
Er zijn twee manieren om de oorspronkelijke waarden van een in een DataColumn DataRow DataAdapter update te behouden:
De eerste methode voor het behouden van de oorspronkelijke waarden is het instellen van de
AcceptChangesDuringUpdateeigenschap van deDataAdapterwaardefalse. Dit is van invloed op elkeDataRowupdateDataTable. Zie AcceptChangesDuringUpdatevoor meer informatie en een codevoorbeeld.De tweede methode is het schrijven van code in de
RowUpdatedgebeurtenis-handler van deDataAdapterom het Status in te stellen op SkipCurrentRow. DeDataRowwaarde wordt bijgewerkt, maar de oorspronkelijke waarde van elkDataColumnbestand blijft behouden. Met deze methode kunt u de oorspronkelijke waarden voor sommige rijen en niet voor anderen behouden. Uw code kan bijvoorbeeld de oorspronkelijke waarden voor toegevoegde rijen behouden en niet voor bewerkte of verwijderde rijen door eerst de StatementType en vervolgens in te stellen Status op SkipCurrentRow alleen voor rijen met eenStatementTypevanInsert.
Wanneer een van deze methoden wordt gebruikt om oorspronkelijke waarden in een DataRow DataAdapter update te behouden, ADO.NET een reeks acties uitvoert om de huidige waarden van de DataRow huidige waarden in te stellen op nieuwe waarden die worden geretourneerd door uitvoerparameters of door de eerste geretourneerde rij van een resultatenset, terwijl de oorspronkelijke waarde in elk van deze DataColumnmethoden behouden blijft. Eerst wordt de AcceptChanges methode van de methode DataRow aangeroepen om de huidige waarden als oorspronkelijke waarden te behouden en vervolgens worden de nieuwe waarden toegewezen. Als u deze acties uitvoert, DataRows is RowState de RowState eigenschap ingesteld op Added de eigenschap waarop de eigenschap is ingesteldModified. Dit kan onverwacht zijn.
Hoe de opdrachtresultaten worden toegepast op elke DataRow die wordt bijgewerkt, wordt bepaald door de UpdatedRowSource eigenschap van elke DbCommand. Deze eigenschap is ingesteld op een waarde uit de UpdateRowSource opsomming.
In de volgende tabel wordt beschreven hoe de UpdateRowSource opsommingswaarden van invloed zijn op de RowState eigenschap van bijgewerkte rijen.
| De naam van lid | Beschrijving |
|---|---|
| Both | AcceptChanges wordt aangeroepen en zowel uitvoerparameterwaarden als/of de waarden in de eerste rij van een geretourneerde resultatenset worden geplaatst in de DataRow update. Als er geen waarden zijn die moeten worden toegepast, is de RowState waarde Unchanged. |
| FirstReturnedRecord | Als er een rij is geretourneerd, AcceptChanges wordt de rij aangeroepen en wordt de rij toegewezen aan de gewijzigde rij in de DataTable, waarbij de RowState Modifiedwaarde wordt ingesteld op . Als er geen rij wordt geretourneerd, wordt deze AcceptChanges niet aangeroepen en blijft de RowState rij Addedbehouden. |
| None | Geretourneerde parameters of rijen worden genegeerd. Er is geen oproep naar AcceptChanges en de RowState overblijfselen Added. |
| OutputParameters | AcceptChanges wordt aangeroepen en alle uitvoerparameters worden toegewezen aan de gewijzigde rij in de DataTable, waarbij de instelling op RowState Modified. Als er geen uitvoerparameters zijn, is Unchangeddit RowState . |
Opmerking
In dit voorbeeld ziet u hoe gewijzigde rijen worden geëxtraheerd uit een DataTable en een SqlDataAdapter om de gegevensbron bij te werken en een nieuwe waarde voor de identiteitskolom op te halen. Hiermee InsertCommand worden twee Transact-SQL-instructies uitgevoerd. De eerste is de INSERT-instructie en de tweede is een SELECT-instructie die gebruikmaakt van de SCOPE_IDENTITY-functie om de identiteitswaarde op te halen.
INSERT INTO dbo.Shippers (CompanyName)
VALUES (@CompanyName);
SELECT ShipperID, CompanyName FROM dbo.Shippers
WHERE ShipperID = SCOPE_IDENTITY();
De UpdatedRowSource eigenschap van de opdracht Invoegen is ingesteld op UpdateRowSource.FirstReturnedRow en de MissingSchemaAction eigenschap van de DataAdapter opdracht is ingesteld op MissingSchemaAction.AddWithKey. De DataTable is gevuld en de code voegt een nieuwe rij toe aan de DataTable. De gewijzigde rijen worden vervolgens geëxtraheerd in een nieuwe DataTable, die wordt doorgegeven aan de DataAdapter, die vervolgens de server bijwerken.
static void MergeIdentityColumns(string connectionString)
{
using (SqlConnection connection =
new(connectionString))
{
// Create the DataAdapter
SqlDataAdapter adapter =
new(
"SELECT ShipperID, CompanyName FROM dbo.Shippers",
connection)
{
//Add the InsertCommand to retrieve new identity value.
InsertCommand = new SqlCommand(
"INSERT INTO dbo.Shippers (CompanyName) " +
"VALUES (@CompanyName); " +
"SELECT ShipperID, CompanyName FROM dbo.Shippers " +
"WHERE ShipperID = SCOPE_IDENTITY();", connection)
};
// Add the parameter for the inserted value.
adapter.InsertCommand.Parameters.Add(
new SqlParameter("@CompanyName", SqlDbType.NVarChar, 40,
"CompanyName"));
adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.Both;
// MissingSchemaAction adds any missing schema to
// the DataTable, including identity columns
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
// Fill the DataTable.
DataTable shipper = new();
adapter.Fill(shipper);
// Add a new shipper.
DataRow newRow = shipper.NewRow();
newRow["CompanyName"] = "New Shipper";
shipper.Rows.Add(newRow);
// Add changed rows to a new DataTable. This
// DataTable will be used by the DataAdapter.
DataTable dataChanges = shipper.GetChanges()!;
// Add the event handler.
adapter.RowUpdated +=
OnRowUpdated;
adapter.Update(dataChanges);
connection.Close();
// Merge the updates.
shipper.Merge(dataChanges);
// Commit the changes.
shipper.AcceptChanges();
Console.WriteLine("Rows after merge.");
foreach (DataRow row in shipper.Rows)
{
{
Console.WriteLine("{0}: {1}", row[0], row[1]);
}
}
}
}
Private Sub MergeIdentityColumns(ByVal connectionString As String)
Using connection As SqlConnection = New SqlConnection( _
connectionString)
' Create the DataAdapter
Dim adapter As SqlDataAdapter = New SqlDataAdapter( _
"SELECT ShipperID, CompanyName FROM dbo.Shippers", connection)
' Add the InsertCommand to retrieve new identity value.
adapter.InsertCommand = New SqlCommand( _
"INSERT INTO dbo.Shippers (CompanyName) " & _
"VALUES (@CompanyName); " & _
"SELECT ShipperID, CompanyName FROM dbo.Shippers " & _
"WHERE ShipperID = SCOPE_IDENTITY();", _
connection)
' Add the parameter for the inserted value.
adapter.InsertCommand.Parameters.Add( _
New SqlParameter("@CompanyName", SqlDbType.NVarChar, 40, _
"CompanyName"))
adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.Both
' MissingSchemaAction adds any missing schema to
' the DataTable, including identity columns
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
' Fill the DataTable.
Dim shipper As New DataTable
adapter.Fill(shipper)
' Add a new shipper.
Dim newRow As DataRow = shipper.NewRow()
newRow("CompanyName") = "New Shipper"
shipper.Rows.Add(newRow)
' Add changed rows to a new DataTable. This
' DataTable will be used by the DataAdapter.
Dim dataChanges As DataTable = shipper.GetChanges()
' Add the event handler.
AddHandler adapter.RowUpdated, New _
SqlRowUpdatedEventHandler(AddressOf OnRowUpdated)
' Update the datasource with the modified records.
adapter.Update(dataChanges)
' Merge the two DataTables.
shipper.Merge(dataChanges)
' Commit the changes.
shipper.AcceptChanges()
Console.WriteLine("Rows after merge.")
Dim row As DataRow
For Each row In shipper.Rows
Console.WriteLine("{0}: {1}", row(0), row(1))
Next
End Using
End Sub
De OnRowUpdated gebeurtenis-handler controleert de SqlRowUpdatedEventArgs StatementType om te bepalen of de rij een invoegpositie is. Als dat het is, wordt de Status eigenschap ingesteld op SkipCurrentRow. De rij wordt bijgewerkt, maar de oorspronkelijke waarden in de rij blijven behouden. In de hoofdtekst van de procedure wordt de Merge methode aangeroepen om de nieuwe identiteitswaarde samen te voegen in het oorspronkelijke DataTable, en uiteindelijk AcceptChanges wordt aangeroepen.
protected static void OnRowUpdated(
object sender, SqlRowUpdatedEventArgs e)
{
// If this is an insert, then skip this row.
if (e.StatementType == StatementType.Insert)
{
e.Status = UpdateStatus.SkipCurrentRow;
}
}
Private Sub OnRowUpdated( _
ByVal sender As Object, ByVal e As SqlRowUpdatedEventArgs)
' If this is an insert, then skip this row.
If e.StatementType = StatementType.Insert Then
e.Status = UpdateStatus.SkipCurrentRow
End If
End Sub
Autonummeringswaarden van Microsoft Access ophalen
Deze sectie bevat een voorbeeld waarin wordt getoond hoe u waarden ophaalt Autonumber uit een Jet 4.0-database. De Jet-database-engine biedt geen ondersteuning voor het uitvoeren van meerdere instructies in een batch of het gebruik van uitvoerparameters, dus het is niet mogelijk om een van deze technieken te gebruiken om de nieuwe Autonumber waarde te retourneren die is toegewezen aan een ingevoegde rij. U kunt echter code toevoegen aan de RowUpdated gebeurtenishandler waarmee een afzonderlijke SELECT-@@IDENTITY-instructie wordt uitgevoerd om de nieuwe Autonumber waarde op te halen.
Opmerking
In plaats van schemagegevens toe te voegen met behulp MissingSchemaAction.AddWithKeyvan dit voorbeeld, wordt in dit voorbeeld een DataTable met het juiste schema geconfigureerd voordat u de schemagegevens aanroept om de OleDbDataAdapter DataTablegegevens in te vullen. In dit geval wordt de kolom CategoryID geconfigureerd om de waarde te verlagen die aan elke ingevoegde rij begint vanaf nul, door in te truestellen AutoIncrement op , AutoIncrementSeed op 0 en AutoIncrementStep op -1. De code voegt vervolgens twee nieuwe rijen toe en gebruikt GetChanges om de gewijzigde rijen toe te voegen aan een nieuwe DataTable die wordt doorgegeven aan de Update methode.
static OleDbConnection s_connection = default!;
static void MergeIdentityColumns(OleDbConnection connection)
{
using (connection)
{
// Create a DataAdapter based on a SELECT query.
OleDbDataAdapter adapter = new(
"SELECT CategoryID, CategoryName FROM Categories",
connection)
{
// Create the INSERT command for the new category.
InsertCommand = new OleDbCommand(
"INSERT INTO Categories (CategoryName) Values(?)", connection)
{
CommandType = CommandType.Text
}
};
// Add the parameter for the CategoryName.
adapter.InsertCommand.Parameters.Add(
"@CategoryName", OleDbType.VarWChar, 15, "CategoryName");
adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.Both;
// Create a DataTable
DataTable categories = new();
// Create the CategoryID column and set its auto
// incrementing properties to decrement from zero.
DataColumn catId = new()
{
DataType = Type.GetType("System.Int32"),
ColumnName = "CategoryID",
AutoIncrement = true,
AutoIncrementSeed = 0,
AutoIncrementStep = -1
};
categories.Columns.Add(catId);
// Create the CategoryName column.
categories.Columns.Add(new DataColumn
{
DataType = Type.GetType("System.String"),
ColumnName = "CategoryName"
});
// Set the primary key on CategoryID.
var pKey = new DataColumn[] { catId };
categories.PrimaryKey = pKey;
// Fetch the data and fill the DataTable
adapter.Fill(categories);
// Add a new row.
DataRow newRow = categories.NewRow();
newRow["CategoryName"] = "New Category";
categories.Rows.Add(newRow);
// Add another new row.
DataRow newRow2 = categories.NewRow();
newRow2["CategoryName"] = "Another New Category";
categories.Rows.Add(newRow2);
// Add changed rows to a new DataTable that will be
// used to post the inserts to the database.
DataTable dataChanges = categories.GetChanges()!;
// Include an event to fill in the Autonumber value.
adapter.RowUpdated +=
OnRowUpdated;
// Update the database, inserting the new rows.
adapter.Update(dataChanges);
Console.WriteLine("Rows before merge:");
foreach (DataRow row in categories.Rows)
{
{
Console.WriteLine(" {0}: {1}", row[0], row[1]);
}
}
// Merge the two DataTables.
categories.Merge(dataChanges);
// Commit the changes.
categories.AcceptChanges();
Console.WriteLine("Rows after merge:");
foreach (DataRow row in categories.Rows)
{
{
Console.WriteLine(" {0}: {1}", row[0], row[1]);
}
}
}
}
Shared connection As OleDbConnection = Nothing
Private Shared Sub MergeIdentityColumns(ByVal connection As OleDbConnection)
Using connection
' Create a DataAdapter based on a SELECT query.
Dim adapter As OleDbDataAdapter = New OleDbDataAdapter(
"SELECT CategoryID, CategoryName FROM Categories",
connection)
' Create the INSERT command for the new category.
adapter.InsertCommand = New OleDbCommand(
"INSERT INTO Categories (CategoryName) Values(?)", connection)
adapter.InsertCommand.CommandType = CommandType.Text
' Add the parameter for the CategoryName.
adapter.InsertCommand.Parameters.Add(
"@CategoryName", OleDbType.VarWChar, 15, "CategoryName")
adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.Both
' Create a DataTable.
Dim categories As DataTable = New DataTable
' Create the CategoryID column and set its auto
' incrementing properties to decrement from zero.
Dim column As New DataColumn()
column.DataType = System.Type.GetType("System.Int32")
column.ColumnName = "CategoryID"
column.AutoIncrement = True
column.AutoIncrementSeed = 0
column.AutoIncrementStep = -1
categories.Columns.Add(column)
' Create the CategoryName column.
column = New DataColumn()
column.DataType = System.Type.GetType("System.String")
column.ColumnName = "CategoryName"
categories.Columns.Add(column)
' Set the primary key on CategoryID.
Dim pKey(0) As DataColumn
pKey(0) = categories.Columns("CategoryID")
categories.PrimaryKey = pKey
' Fetch the data and fill the DataTable.
adapter.Fill(categories)
' Add a new row.
Dim newRow As DataRow = categories.NewRow()
newRow("CategoryName") = "New Category"
categories.Rows.Add(newRow)
' Add another new row.
Dim newRow2 As DataRow = categories.NewRow()
newRow2("CategoryName") = "Another New Category"
categories.Rows.Add(newRow2)
' Add changed rows to a new DataTable that will be
' used to post the inserts to the database.
Dim dataChanges As DataTable = categories.GetChanges()
' Include an event to fill in the Autonumber value.
AddHandler adapter.RowUpdated,
New OleDbRowUpdatedEventHandler(AddressOf OnRowUpdated)
' Update the database, inserting the new rows.
adapter.Update(dataChanges)
Console.WriteLine("Rows before merge:")
Dim row1 As DataRow
For Each row1 In categories.Rows
Console.WriteLine(" {0}: {1}", row1(0), row1(1))
Next
' Merge the two DataTables.
categories.Merge(dataChanges)
' Commit the changes.
categories.AcceptChanges()
Console.WriteLine("Rows after merge:")
Dim row As DataRow
For Each row In categories.Rows
Console.WriteLine(" {0}: {1}", row(0), row(1))
Next
End Using
End Sub
De RowUpdated gebeurtenis-handler gebruikt hetzelfde open OleDbConnection als de Update instructie van de OleDbDataAdapter. Hiermee wordt de StatementType status van de OleDbRowUpdatedEventArgs ingevoegde rijen gecontroleerd. Voor elke ingevoegde rij wordt er een nieuwe OleDbCommand gemaakt om de INSTRUCTIE SELECT @@IDENTITY uit te voeren voor de verbinding, waarbij de nieuwe Autonumber waarde wordt geretourneerd, die in de kolom CategoryID van de DataRow. De Status eigenschap wordt vervolgens ingesteld om UpdateStatus.SkipCurrentRow de verborgen aanroep naar AcceptChangeste onderdrukken. In de hoofdtekst van de procedure wordt de Merge methode aangeroepen om de twee DataTable objecten samen te voegen en tot slot AcceptChanges wordt deze aangeroepen.
static void OnRowUpdated(
object sender, OleDbRowUpdatedEventArgs e)
{
// Conditionally execute this code block on inserts only.
if (e.StatementType == StatementType.Insert)
{
OleDbCommand cmdNewID = new("SELECT @@IDENTITY",
s_connection);
// Retrieve the Autonumber and store it in the CategoryID column.
e.Row["CategoryID"] = (int)cmdNewID.ExecuteScalar()!;
e.Status = UpdateStatus.SkipCurrentRow;
}
}
Private Shared Sub OnRowUpdated(
ByVal sender As Object, ByVal e As OleDbRowUpdatedEventArgs)
' Conditionally execute this code block on inserts only.
If e.StatementType = StatementType.Insert Then
' Retrieve the Autonumber and store it in the CategoryID column.
Dim cmdNewID As New OleDbCommand("SELECT @@IDENTITY",
connection)
e.Row("CategoryID") = CInt(cmdNewID.ExecuteScalar)
e.Status = UpdateStatus.SkipCurrentRow
End If
End Sub
Identiteitswaarden ophalen
We stellen de kolom vaak in als identiteit wanneer de waarden in de kolom uniek moeten zijn. En soms hebben we de identiteitswaarde van nieuwe gegevens nodig. In dit voorbeeld ziet u hoe u identiteitswaarden ophaalt:
Hiermee maakt u een opgeslagen procedure om gegevens in te voegen en een identiteitswaarde te retourneren.
Hiermee voert u een opdracht uit om de nieuwe gegevens in te voegen en het resultaat weer te geven.
Hiermee SqlDataAdapter voegt u nieuwe gegevens in en geeft u het resultaat weer.
Voordat u het voorbeeld compileert en uitvoert, moet u de voorbeelddatabase maken met behulp van het volgende script:
USE [master]
GO
CREATE DATABASE [MySchool]
GO
USE [MySchool]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[CourseExtInfo] @CourseId int
as
select c.CourseID,c.Title,c.Credits,d.Name as DepartmentName
from Course as c left outer join Department as d on c.DepartmentID=d.DepartmentID
where c.CourseID=@CourseId
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[DepartmentInfo] @DepartmentId int,@CourseCount int output
as
select @CourseCount=Count(c.CourseID)
from course as c
where c.DepartmentID=@DepartmentId
select d.DepartmentID,d.Name,d.Budget,d.StartDate,d.Administrator
from Department as d
where d.DepartmentID=@DepartmentId
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[GetDepartmentsOfSpecifiedYear]
@Year int,@BudgetSum money output
AS
BEGIN
SELECT @BudgetSum=SUM([Budget])
FROM [MySchool].[dbo].[Department]
Where YEAR([StartDate])=@Year
SELECT [DepartmentID]
,[Name]
,[Budget]
,[StartDate]
,[Administrator]
FROM [MySchool].[dbo].[Department]
Where YEAR([StartDate])=@Year
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GradeOfStudent]
-- Add the parameters for the stored procedure here
@CourseTitle nvarchar(100),@FirstName nvarchar(50),
@LastName nvarchar(50),@Grade decimal(3,2) output
AS
BEGIN
select @Grade=Max(Grade)
from [dbo].[StudentGrade] as s join [dbo].[Course] as c on
s.CourseID=c.CourseID join [dbo].[Person] as p on s.StudentID=p.PersonID
where c.Title=@CourseTitle and p.FirstName=@FirstName
and p.LastName= @LastName
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[InsertPerson]
-- Add the parameters for the stored procedure here
@FirstName nvarchar(50),@LastName nvarchar(50),
@PersonID int output
AS
BEGIN
insert [dbo].[Person](LastName,FirstName) Values(@LastName,@FirstName)
set @PersonID=SCOPE_IDENTITY()
END
Go
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Course]([CourseID] [nvarchar](10) NOT NULL,
[Year] [smallint] NOT NULL,
[Title] [nvarchar](100) NOT NULL,
[Credits] [int] NOT NULL,
[DepartmentID] [int] NOT NULL,
CONSTRAINT [PK_Course] PRIMARY KEY CLUSTERED
(
[CourseID] ASC,
[Year] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Department]([DepartmentID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[Budget] [money] NOT NULL,
[StartDate] [datetime] NOT NULL,
[Administrator] [int] NULL,
CONSTRAINT [PK_Department] PRIMARY KEY CLUSTERED
(
[DepartmentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Person]([PersonID] [int] IDENTITY(1,1) NOT NULL,
[LastName] [nvarchar](50) NOT NULL,
[FirstName] [nvarchar](50) NOT NULL,
[HireDate] [datetime] NULL,
[EnrollmentDate] [datetime] NULL,
[Picture] [varbinary](max) NULL,
CONSTRAINT [PK_School.Student] PRIMARY KEY CLUSTERED
(
[PersonID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[StudentGrade]([EnrollmentID] [int] IDENTITY(1,1) NOT NULL,
[CourseID] [nvarchar](10) NOT NULL,
[StudentID] [int] NOT NULL,
[Grade] [decimal](3, 2) NOT NULL,
CONSTRAINT [PK_StudentGrade] PRIMARY KEY CLUSTERED
(
[EnrollmentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create view [dbo].[EnglishCourse]
as
select c.CourseID,c.Title,c.Credits,c.DepartmentID
from Course as c join Department as d on c.DepartmentID=d.DepartmentID
where d.Name=N'English'
GO
INSERT [dbo].[Course] ([CourseID], [Year], [Title], [Credits], [DepartmentID]) VALUES (N'C1045', 2012, N'Calculus', 4, 7)
INSERT [dbo].[Course] ([CourseID], [Year], [Title], [Credits], [DepartmentID]) VALUES (N'C1061', 2012, N'Physics', 4, 1)
INSERT [dbo].[Course] ([CourseID], [Year], [Title], [Credits], [DepartmentID]) VALUES (N'C2021', 2012, N'Composition', 3, 2)
INSERT [dbo].[Course] ([CourseID], [Year], [Title], [Credits], [DepartmentID]) VALUES (N'C2042', 2012, N'Literature', 4, 2)
SET IDENTITY_INSERT [dbo].[Department] ON
INSERT [dbo].[Department] ([DepartmentID], [Name], [Budget], [StartDate], [Administrator]) VALUES (1, N'Engineering', 350000.0000, CAST(0x0000999C00000000 AS DateTime), 2)
INSERT [dbo].[Department] ([DepartmentID], [Name], [Budget], [StartDate], [Administrator]) VALUES (2, N'English', 120000.0000, CAST(0x0000999C00000000 AS DateTime), 6)
INSERT [dbo].[Department] ([DepartmentID], [Name], [Budget], [StartDate], [Administrator]) VALUES (4, N'Economics', 200000.0000, CAST(0x0000999C00000000 AS DateTime), 4)
INSERT [dbo].[Department] ([DepartmentID], [Name], [Budget], [StartDate], [Administrator]) VALUES (7, N'Mathematics', 250024.0000, CAST(0x0000999C00000000 AS DateTime), 3)
SET IDENTITY_INSERT [dbo].[Department] OFF
SET IDENTITY_INSERT [dbo].[Person] ON
INSERT [dbo].[Person] ([PersonID], [LastName], [FirstName], [HireDate], [EnrollmentDate]) VALUES (1, N'Hu', N'Nan', NULL, CAST(0x0000A0BF00000000 AS DateTime))
INSERT [dbo].[Person] ([PersonID], [LastName], [FirstName], [HireDate], [EnrollmentDate]) VALUES (2, N'Norman', N'Laura', NULL, CAST(0x0000A0BF00000000 AS DateTime))
INSERT [dbo].[Person] ([PersonID], [LastName], [FirstName], [HireDate], [EnrollmentDate]) VALUES (3, N'Olivotto', N'Nino', NULL, CAST(0x0000A0BF00000000 AS DateTime))
INSERT [dbo].[Person] ([PersonID], [LastName], [FirstName], [HireDate], [EnrollmentDate]) VALUES (4, N'Anand', N'Arturo', NULL, CAST(0x0000A0BF00000000 AS DateTime))
INSERT [dbo].[Person] ([PersonID], [LastName], [FirstName], [HireDate], [EnrollmentDate]) VALUES (5, N'Jai', N'Damien', NULL, CAST(0x0000A0BF00000000 AS DateTime))
INSERT [dbo].[Person] ([PersonID], [LastName], [FirstName], [HireDate], [EnrollmentDate]) VALUES (6, N'Holt', N'Roger', CAST(0x000097F100000000 AS DateTime), NULL)
INSERT [dbo].[Person] ([PersonID], [LastName], [FirstName], [HireDate], [EnrollmentDate]) VALUES (7, N'Martin', N'Randall', CAST(0x00008B1A00000000 AS DateTime), NULL)
SET IDENTITY_INSERT [dbo].[Person] OFF
SET IDENTITY_INSERT [dbo].[StudentGrade] ON
INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (1, N'C1045', 1, CAST(3.50 AS Decimal(3, 2)))
INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (2, N'C1045', 2, CAST(3.00 AS Decimal(3, 2)))
INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (3, N'C1045', 3, CAST(2.50 AS Decimal(3, 2)))
INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (4, N'C1045', 4, CAST(4.00 AS Decimal(3, 2)))
INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (5, N'C1045', 5, CAST(3.50 AS Decimal(3, 2)))
INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (6, N'C1061', 1, CAST(4.00 AS Decimal(3, 2)))
INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (7, N'C1061', 3, CAST(3.50 AS Decimal(3, 2)))
INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (8, N'C1061', 4, CAST(2.50 AS Decimal(3, 2)))
INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (9, N'C1061', 5, CAST(1.50 AS Decimal(3, 2)))
INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (10, N'C2021', 1, CAST(2.50 AS Decimal(3, 2)))
INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (11, N'C2021', 2, CAST(3.50 AS Decimal(3, 2)))
INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (12, N'C2021', 4, CAST(3.00 AS Decimal(3, 2)))
INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (13, N'C2021', 5, CAST(3.00 AS Decimal(3, 2)))
INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (14, N'C2042', 1, CAST(2.00 AS Decimal(3, 2)))
INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (15, N'C2042', 2, CAST(3.50 AS Decimal(3, 2)))
INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (16, N'C2042', 3, CAST(4.00 AS Decimal(3, 2)))
INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (17, N'C2042', 5, CAST(3.00 AS Decimal(3, 2)))
SET IDENTITY_INSERT [dbo].[StudentGrade] OFF
ALTER TABLE [dbo].[Course] WITH CHECK ADD CONSTRAINT [FK_Course_Department] FOREIGN KEY([DepartmentID])
REFERENCES [dbo].[Department] ([DepartmentID])
GO
ALTER TABLE [dbo].[Course] CHECK CONSTRAINT [FK_Course_Department]
GO
ALTER TABLE [dbo].[StudentGrade] WITH CHECK ADD CONSTRAINT [FK_StudentGrade_Student] FOREIGN KEY([StudentID])
REFERENCES [dbo].[Person] ([PersonID])
GO
ALTER TABLE [dbo].[StudentGrade] CHECK CONSTRAINT [FK_StudentGrade_Student]
GO
De codevermelding volgt:
Tip
De codevermelding verwijst naar een Access-databasebestand met de naam MySchool.mdb. U kunt MySchool.mdb downloaden uit het archief van de MSDN-codegalerie.
using System;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
class Program {
static void Main(string[] args) {
String SqlDbConnectionString = "...";
InsertPerson(SqlDbConnectionString, "Janice", "Galvin");
Console.WriteLine();
InsertPersonInAdapter(SqlDbConnectionString, "Peter", "Krebs");
Console.WriteLine();
String oledbConnectionString = "...";
InsertPersonInJet4Database(oledbConnectionString, "Janice", "Galvin");
Console.WriteLine();
Console.WriteLine("Please press any key to exit.....");
Console.ReadKey();
}
// Using stored procedure to insert a new row and retrieve the identity value
static void InsertPerson(String connectionString, String firstName, String lastName) {
String commandText = "dbo.InsertPerson";
using (SqlConnection conn = new SqlConnection(connectionString)) {
using (SqlCommand cmd = new SqlCommand(commandText, conn)) {
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@FirstName", firstName));
cmd.Parameters.Add(new SqlParameter("@LastName", lastName));
SqlParameter personId = new SqlParameter("@PersonID", SqlDbType.Int);
personId.Direction = ParameterDirection.Output;
cmd.Parameters.Add(personId);
conn.Open();
cmd.ExecuteNonQuery();
Console.WriteLine("Person Id of new person:{0}", personId.Value);
}
}
}
// Using stored procedure in adapter to insert new rows and update the identity value.
static void InsertPersonInAdapter(String connectionString, String firstName, String lastName) {
String commandText = "dbo.InsertPerson";
using (SqlConnection conn = new SqlConnection(connectionString)) {
SqlDataAdapter mySchool = new SqlDataAdapter("Select PersonID,FirstName,LastName from [dbo].[Person]", conn);
mySchool.InsertCommand = new SqlCommand(commandText, conn);
mySchool.InsertCommand.CommandType = CommandType.StoredProcedure;
mySchool.InsertCommand.Parameters.Add(
new SqlParameter("@FirstName", SqlDbType.NVarChar, 50, "FirstName"));
mySchool.InsertCommand.Parameters.Add(
new SqlParameter("@LastName", SqlDbType.NVarChar, 50, "LastName"));
SqlParameter personId = mySchool.InsertCommand.Parameters.Add(new SqlParameter("@PersonID", SqlDbType.Int, 0, "PersonID"));
personId.Direction = ParameterDirection.Output;
DataTable persons = new DataTable();
mySchool.Fill(persons);
DataRow newPerson = persons.NewRow();
newPerson["FirstName"] = firstName;
newPerson["LastName"] = lastName;
persons.Rows.Add(newPerson);
mySchool.Update(persons);
Console.WriteLine("Show all persons:");
ShowDataTable(persons, 14);
}
}
/// For a Jet 4.0 database, we need use the single statement and event handler to insert new rows and retrieve the identity value.
static void InsertPersonInJet4Database(String connectionString, String firstName, String lastName) {
String commandText = "Insert into Person(FirstName,LastName) Values(?,?)";
using (OleDbConnection conn = new OleDbConnection(connectionString)) {
OleDbDataAdapter mySchool = new OleDbDataAdapter("Select PersonID,FirstName,LastName from Person", conn);
// Create Insert Command
mySchool.InsertCommand = new OleDbCommand(commandText, conn);
mySchool.InsertCommand.CommandType = CommandType.Text;
mySchool.InsertCommand.Parameters.Add(new OleDbParameter("@FirstName", OleDbType.VarChar, 50, "FirstName"));
mySchool.InsertCommand.Parameters.Add(new OleDbParameter("@LastName", OleDbType.VarChar, 50, "LastName"));
mySchool.InsertCommand.UpdatedRowSource = UpdateRowSource.Both;
DataTable persons = CreatePersonsTable();
mySchool.Fill(persons);
DataRow newPerson = persons.NewRow();
newPerson["FirstName"] = firstName;
newPerson["LastName"] = lastName;
persons.Rows.Add(newPerson);
DataTable dataChanges = persons.GetChanges();
mySchool.RowUpdated += OnRowUpdated;
mySchool.Update(dataChanges);
Console.WriteLine("Data before merging:");
ShowDataTable(persons, 14);
Console.WriteLine();
persons.Merge(dataChanges);
persons.AcceptChanges();
Console.WriteLine("Data after merging");
ShowDataTable(persons, 14);
}
}
static void OnRowUpdated(object sender, OleDbRowUpdatedEventArgs e) {
if (e.StatementType == StatementType.Insert) {
// Retrieve the identity value
OleDbCommand cmdNewId = new OleDbCommand("Select @@IDENTITY", e.Command.Connection);
e.Row["PersonID"] = (Int32)cmdNewId.ExecuteScalar();
// After the status is changed, the original values in the row are preserved. And the
// Merge method will be called to merge the new identity value into the original DataTable.
e.Status = UpdateStatus.SkipCurrentRow;
}
}
// Create the Persons table before filling.
private static DataTable CreatePersonsTable() {
DataTable persons = new DataTable();
DataColumn personId = new DataColumn();
personId.DataType = Type.GetType("System.Int32");
personId.ColumnName = "PersonID";
personId.AutoIncrement = true;
personId.AutoIncrementSeed = 0;
personId.AutoIncrementStep = -1;
persons.Columns.Add(personId);
DataColumn firstName = new DataColumn();
firstName.DataType = Type.GetType("System.String");
firstName.ColumnName = "FirstName";
persons.Columns.Add(firstName);
DataColumn lastName = new DataColumn();
lastName.DataType = Type.GetType("System.String");
lastName.ColumnName = "LastName";
persons.Columns.Add(lastName);
DataColumn[] pkey = { personId };
persons.PrimaryKey = pkey;
return persons;
}
private static void ShowDataTable(DataTable table, Int32 length) {
foreach (DataColumn col in table.Columns) {
Console.Write("{0,-" + length + "}", col.ColumnName);
}
Console.WriteLine();
foreach (DataRow row in table.Rows) {
foreach (DataColumn col in table.Columns) {
if (col.DataType.Equals(typeof(DateTime)))
Console.Write("{0,-" + length + ":d}", row[col]);
else if (col.DataType.Equals(typeof(Decimal)))
Console.Write("{0,-" + length + ":C}", row[col]);
else
Console.Write("{0,-" + length + "}", row[col]);
}
Console.WriteLine();
}
}
}