Pobieranie wartości tożsamości lub autonumeruj
Klucz podstawowy w relacyjnej bazie danych to kolumna lub kombinacja kolumn, które zawsze zawierają unikatowe wartości. Znajomość wartości klucza podstawowego pozwala zlokalizować wiersz, który go zawiera. Aparaty relacyjnych baz danych, takie jak SQL Server, Oracle i Microsoft Access/Jet, obsługują tworzenie automatycznie zwiększanych kolumn, które można wyznaczyć jako klucze podstawowe. Te wartości są generowane przez serwer w miarę dodawania wierszy do tabeli. W programie SQL Server ustawiasz właściwość tożsamości kolumny, w programie Oracle tworzysz sekwencję, a w programie Microsoft Access tworzysz kolumnę Autonumerowanie.
Można DataColumn również użyć elementu do automatycznego generowania wartości przyrostowych, ustawiając AutoIncrement właściwość na true. Można jednak umieścić zduplikowane wartości w oddzielnych wystąpieniach klasy , jeśli wiele aplikacji klienckich DataTableniezależnie generuje automatycznie wartości przyrostowe. Po wygenerowaniu automatycznie przyrostowych wartości serwera eliminuje potencjalne konflikty, umożliwiając każdemu użytkownikowi pobranie wygenerowanej wartości dla każdego wstawionego wiersza.
Podczas wywołania Update
metody DataAdapter
bazy danych baza danych może wysyłać dane z powrotem do aplikacji ADO.NET jako parametry wyjściowe lub jako pierwszy zwrócony rekord zestawu wyników instrukcji SELECT wykonanej w tej samej partii co instrukcja INSERT. ADO.NET można pobrać te wartości i zaktualizować odpowiednie kolumny w aktualizowaniu DataRow .
Niektóre aparaty bazy danych, takie jak aparat bazy danych Microsoft Access Jet, nie obsługują parametrów wyjściowych i nie mogą przetwarzać wielu instrukcji w jednej partii. Podczas pracy z aparatem bazy danych Jet można pobrać nową wartość autonumerowania wygenerowaną dla wstawionego wiersza, wykonując oddzielne polecenie SELECT w procedurze obsługi zdarzeń dla RowUpdated
zdarzenia DataAdapter
.
Uwaga
Alternatywą dla użycia wartości automatycznego zwiększania jest użycie NewGuid metody Guid obiektu do wygenerowania identyfikatora GUID lub globalnie unikatowego identyfikatora na komputerze klienckim, który można skopiować na serwer podczas wstawiania każdego nowego wiersza. Metoda NewGuid
generuje 16-bajtową wartość binarną utworzoną przy użyciu algorytmu, który zapewnia wysokie prawdopodobieństwo, że żadna wartość nie zostanie zduplikowana. W bazie danych programu SQL Server identyfikator GUID jest przechowywany w uniqueidentifier
kolumnie, którą program SQL Server może automatycznie wygenerować przy użyciu funkcji Transact-SQL NEWID()
. Użycie identyfikatora GUID jako klucza podstawowego może niekorzystnie wpłynąć na wydajność. Program SQL Server zapewnia obsługę NEWSEQUENTIALID()
funkcji, która generuje sekwencyjny identyfikator GUID, który nie jest gwarantowany globalnie unikatowy, ale może być indeksowany wydajniej.
Pobieranie wartości kolumn tożsamości programu SQL Server
Podczas pracy z programem Microsoft SQL Server można utworzyć procedurę składowaną z parametrem wyjściowym, aby zwrócić wartość tożsamości dla wstawionego wiersza. W poniższej tabeli opisano trzy funkcje języka Transact-SQL w programie SQL Server, które mogą służyć do pobierania wartości kolumn tożsamości.
Function | opis |
---|---|
SCOPE_IDENTITY | Zwraca ostatnią wartość tożsamości w bieżącym zakresie wykonywania. SCOPE_IDENTITY jest zalecana w większości scenariuszy. |
@@IDENTITY | Zawiera ostatnią wartość tożsamości wygenerowaną w dowolnej tabeli w bieżącej sesji. @@IDENTITY mogą mieć wpływ wyzwalacze i mogą nie zwracać oczekiwanej wartości tożsamości. |
IDENT_CURRENT | Zwraca ostatnią wartość tożsamości wygenerowaną dla określonej tabeli w dowolnej sesji i dowolnym zakresie. |
Poniższa procedura składowana pokazuje, jak wstawić wiersz do tabeli Categories i użyć parametru wyjściowego, aby zwrócić nową wartość tożsamości wygenerowaną przez funkcję 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()
Następnie procedurę składowaną można określić jako źródło InsertCommand SqlDataAdapter obiektu. Właściwość CommandType InsertCommand musi być ustawiona na StoredProcedure. Dane wyjściowe tożsamości są pobierane przez utworzenie obiektu SqlParameter z wartością ParameterDirection Output. Po przetworzeniu InsertCommand
obiektu zwracana jest automatycznie zwiększana wartość tożsamości i umieszczana w kolumnie CategoryID bieżącego wiersza, jeśli ustawisz UpdatedRowSource właściwość polecenia insert na UpdateRowSource.OutputParameters
lub na UpdateRowSource.Both
.
Jeśli polecenie insert wykonuje partię zawierającą zarówno instrukcję INSERT, jak i instrukcję SELECT zwracającą nową wartość tożsamości, możesz pobrać nową wartość, ustawiając UpdatedRowSource
właściwość polecenia insert na UpdateRowSource.FirstReturnedRecord
wartość .
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
Scalanie nowych wartości tożsamości
Typowym scenariuszem jest wywołanie GetChanges
metody w DataTable
celu utworzenia kopii zawierającej tylko zmienione wiersze i użycie nowej kopii podczas wywoływania Update
metody .DataAdapter
Jest to szczególnie przydatne, gdy konieczne jest przeprowadzenie marshalingu zmienionych wierszy do oddzielnego składnika wykonującego aktualizację. Po aktualizacji kopia może zawierać nowe wartości tożsamości, które należy następnie scalić z powrotem do oryginalnego DataTable
elementu . Nowe wartości tożsamości mogą się różnić od oryginalnych wartości w pliku DataTable
. Aby można było wykonać scalanie, oryginalne wartości kolumn AutoInkrement w kopii muszą być zachowywane, aby można było zlokalizować i zaktualizować istniejące wiersze w oryginalnym DataTable
obiekcie, zamiast dołączać nowe wiersze zawierające nowe wartości tożsamości. Jednak domyślnie te oryginalne wartości są tracone po wywołaniu Update
metody , ponieważ AcceptChanges
jest niejawnie wywoływana dla każdego zaktualizowanego DataAdapter
DataRow
elementu .
Istnieją dwa sposoby zachowania oryginalnych DataColumn
wartości elementu w DataRow
aktualizacji DataAdapter
:
Pierwszą metodą zachowania oryginalnych wartości jest ustawienie
AcceptChangesDuringUpdate
właściwości naDataAdapter
false
wartość . Ma to wpływ na każdąDataRow
aktualizacjęDataTable
. Aby uzyskać więcej informacji i przykładowy kod, zobacz AcceptChangesDuringUpdate.Drugą metodą jest napisanie kodu w procedurze
RowUpdated
obsługi zdarzeń obiektu , aby ustawić wartość Status SkipCurrentRow.DataAdapter
ElementDataRow
jest aktualizowany, ale oryginalna wartość każdego z nichDataColumn
jest zachowywana. Ta metoda umożliwia zachowanie oryginalnych wartości dla niektórych wierszy, a nie dla innych. Na przykład kod może zachować oryginalne wartości dla dodanych wierszy, a nie dla wierszy edytowanych lub usuniętych, sprawdzając StatementType najpierw ustawienie , a następnie ustawiając Status SkipCurrentRow wartość tylko dla wierszy z wartościąStatementType
Insert
.
Jeśli którakolwiek z tych metod jest używana do zachowania oryginalnych wartości w DataRow
trakcie DataAdapter
aktualizacji, ADO.NET wykonuje serię akcji w celu ustawienia bieżących wartości DataRow
wartości zwracanych przez parametry wyjściowe lub przez pierwszy zwrócony wiersz zestawu wyników, zachowując jednocześnie oryginalną wartość w każdym DataColumn
obiekcie . Najpierw metoda DataRow
metody jest wywoływana w AcceptChanges
celu zachowania bieżących wartości jako oryginalnych wartości, a następnie przypisywane są nowe wartości. Po wykonaniu tych akcji właściwość DataRows
ma RowState ustawioną Added RowState
właściwość na Modifiedwartość , co może być nieoczekiwane.
Sposób stosowania wyników polecenia do każdej DataRow aktualizacji jest określany przez UpdatedRowSource właściwość każdego DbCommandelementu . Ta właściwość jest ustawiona na wartość z UpdateRowSource
wyliczenia.
W poniższej tabeli opisano, jak UpdateRowSource
wartości wyliczenia wpływają na RowState właściwość zaktualizowanych wierszy.
Nazwa elementu członkowskiego | opis |
---|---|
Both | AcceptChanges jest wywoływana, a wartości parametrów wyjściowych i/lub wartości w pierwszym wierszu dowolnego zwróconego zestawu wyników są umieszczane w DataRow aktualizowaniu. Jeśli nie ma żadnych wartości do zastosowania, RowState parametr będzie mieć Unchangedwartość . |
FirstReturnedRecord | Jeśli wiersz został zwrócony, jest wywoływany, AcceptChanges a wiersz jest mapowany na zmieniony wiersz w elemecie DataTable , ustawiając wartość na RowState Modified . Jeśli żaden wiersz nie jest zwracany, AcceptChanges nie jest wywoływany i pozostaje Added .RowState |
None | Wszystkie zwrócone parametry lub wiersze są ignorowane. Nie ma połączenia i AcceptChanges RowState pozostaje Added . |
OutputParameters | AcceptChanges jest wywoływana, a wszystkie parametry wyjściowe są mapowane na zmieniony wiersz w DataTable obiekcie , ustawiając wartość RowState na Modified . Jeśli nie ma parametrów wyjściowych, RowState będzie to Unchanged . |
Przykład
W tym przykładzie pokazano wyodrębnianie zmienionych wierszy z elementu DataTable
i przy użyciu elementu SqlDataAdapter , aby zaktualizować źródło danych i pobrać nową wartość kolumny tożsamości. Wykonuje InsertCommand dwie instrukcje języka Transact-SQL; pierwsza jest instrukcją INSERT, a druga jest instrukcją SELECT, która używa funkcji SCOPE_IDENTITY w celu pobrania wartości tożsamości.
INSERT INTO dbo.Shippers (CompanyName)
VALUES (@CompanyName);
SELECT ShipperID, CompanyName FROM dbo.Shippers
WHERE ShipperID = SCOPE_IDENTITY();
Właściwość UpdatedRowSource
polecenia insert jest ustawiona na UpdateRowSource.FirstReturnedRow
, a MissingSchemaAction właściwość DataAdapter
właściwości jest ustawiona na MissingSchemaAction.AddWithKey
. Element DataTable
jest wypełniony, a kod dodaje nowy wiersz do elementu DataTable
. Zmienione wiersze są następnie wyodrębniane do nowego DataTable
elementu , który jest przekazywany do DataAdapter
elementu , który następnie aktualizuje serwer.
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
Procedura OnRowUpdated
obsługi zdarzeń sprawdza element StatementType , SqlRowUpdatedEventArgs aby określić, czy wiersz jest wstawiany. Jeśli tak jest, właściwość jest ustawiona Status na SkipCurrentRow. Wiersz jest aktualizowany, ale oryginalne wartości w wierszu są zachowywane. W głównej treści procedury metoda jest wywoływana w Merge celu scalenia nowej wartości tożsamości z oryginalną DataTable
wartością , a na koniec AcceptChanges
jest wywoływana.
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
Pobieranie wartości autonumerowania programu Microsoft Access
Ta sekcja zawiera przykład pokazujący sposób pobierania Autonumber
wartości z bazy danych Jet 4.0. Aparat bazy danych Jet nie obsługuje wykonywania wielu instrukcji w partii ani użycia parametrów wyjściowych, więc nie można użyć żadnej z tych technik, aby zwrócić nową Autonumber
wartość przypisaną do wstawionego wiersza. Można jednak dodać kod do RowUpdated
procedury obsługi zdarzeń, która wykonuje oddzielną instrukcję SELECT @@IDENTITY w celu pobrania nowej Autonumber
wartości.
Przykład
Zamiast dodawać informacje o schemacie przy użyciu metody MissingSchemaAction.AddWithKey
, ten przykład konfiguruje DataTable
element z poprawnym schematem przed wywołaniem OleDbDataAdapter elementu w celu wypełnienia elementu DataTable
. W takim przypadku kolumna CategoryID jest skonfigurowana do dekrementacji wartości przypisanej do każdego wstawionego wiersza rozpoczynającego się od zera, ustawiając wartość AutoIncrement na true
, AutoIncrementSeed na 0 i AutoIncrementStep na -1. Następnie kod dodaje dwa nowe wiersze i używa ich GetChanges
do dodania zmienionych wierszy do nowej DataTable
, która jest przekazywana Update
do metody .
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
Program RowUpdated
obsługi zdarzeń używa tego samego pliku otwartego OleDbConnection co Update
instrukcja OleDbDataAdapter
. Sprawdza liczbę StatementType
OleDbRowUpdatedEventArgs wstawionych wierszy. Dla każdego wstawionego wiersza zostanie utworzony nowy OleDbCommand , aby wykonać instrukcję SELECT @@IDENTITY w połączeniu, zwracając nową Autonumber
wartość, która jest umieszczana w kolumnie CategoryID obiektu DataRow
. Właściwość jest następnie ustawiona Status
na wartość , aby UpdateStatus.SkipCurrentRow
pominąć ukryte wywołanie metody AcceptChanges
. W głównej treści procedury metoda jest wywoływana w Merge
celu scalenia dwóch DataTable
obiektów, a na koniec AcceptChanges
jest wywoływana.
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
Pobieranie wartości tożsamości
Często ustawiamy kolumnę jako tożsamość, gdy wartości w kolumnie muszą być unikatowe. Czasami potrzebujemy wartości tożsamości nowych danych. W tym przykładzie pokazano, jak pobrać wartości tożsamości:
Tworzy procedurę składowaną w celu wstawiania danych i zwracania wartości tożsamości.
Wykonuje polecenie , aby wstawić nowe dane i wyświetlić wynik.
Używa SqlDataAdapter metody do wstawiania nowych danych i wyświetlania wyniku.
Przed skompilowanie i uruchomieniem przykładowej bazy danych należy utworzyć przy użyciu następującego skryptu:
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
Lista kodu jest następująca:
Napiwek
Lista kodu odwołuje się do pliku bazy danych programu Access o nazwie MySchool.mdb. Możesz pobrać MySchool.mdb z archiwum galerii kodu MSDN.
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();
}
}
}