app.config / web.config
<configuration>
<connectionStrings>
<add name="MyConnectionString"
providerName="System.Data.SqlClient"
connectionString="Data Source=(localdb)\v11; InitialCatalog=MyDatabaseName" />
</connectionStrings>
<system.data>
<DbProviderFactories>
<add name="SqlClient Data Provider"
invariant="System.Data.SqlClient"
description=".NET Framework Data Provider for SQL Server"
type="System.Data.SqlClient.SqlClientFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
</DbProviderFactories>
</system.data>
</configuration>
Herstellen der Verbindung
var settings = ConfigurationManager.ConnectionStrings["MyConnectionString"];
var factory = DbProviderFactories.GetFactory(settings.ProviderName);
using (var connection = factory.CreateConnection())
{
connection.ConnectionString = settings.ConnectionString;
// prepare Queries
await connection.OpenAsync();
// execute Queries
connection.Close();
}
Transaction Scope
using(var transaction = new TransactionScope())
{
using (var connection = factory.CreateConnection())
{
// open connection
// execute multiple queries within scope
}
// when no exception was thrown, complete the transaction
transaction.Complete();
}
Create Table
CREATE TABLE dbo.People
{
[id] INT NOT NULL IDENTITY,
[FirstName] VARCHAR(30) NOT NULL,
[MiddleName] VARCHAR(30) NULL,
[LastName] VARCHAR(30) NOT NULL
}
Read
var command = connection.CreateCommand();
command.CommandText = "SELECT * FROM dbo.People";
command.CommandType = CommandType.Text;
var dataReader = await command.ExecuteReaderAsync();
while(await dataReader.ReadAsync())
{
Console.WriteLine("Id: '{0}', FirstName: '{1}', MiddleName: '{2}', LastName: '{3}'",
dataReader["id"], dataReader["firstname"], dataReader["middlename"], dataReader["lastname"]);
}
dataReader.Close();
Update
var command = connection.CreateCommand();
command.CommandText = "UPDATE dbo.People WHERE Id=5 SET FirstName='John'";
command.CommandType = CommandType.Text;
await connection.OpenAsync();
int numberOfUpdatedRows = await command.ExecuteNonQueryAsync();
var command = connection.CreateCommand();
command.CommandText = "INSERT INTO dbo.People([FirstName], [MiddleName], [LastName]) VALUES(@firstName, @middleName, @lastName)";
command.CommandType = CommandType.Text;
command.Parameters.AddWithValue("@firstName", "John");
command.Parameters.AddWithValue("@middleName", "Little");
command.Parameters.AddWithValue("@lastName", "Doe");
await connection.OpenAsync();
int numberOfInsertedRows = await command.ExecuteNonQueryAsync();
Query Types
| Befehl |
Einsatz
|
.ExecuteReader() |
Query mit Liste von Rückgabewerten
|
.ExecuteScalar() |
Query mit Skalarem Rückgabewert
|
.ExecuteNonQuery() |
Command
|
Internetquellen
|}