Benutzer:MovGP0/.NET/SQL

MovGP0

Über mich

Hilfen

Artikel

Weblinks

Literatur

Zitate

Notizen

Programmierung

MSCert

Physik

C♯ C♯ Program Flow Multithreading and asynchronous processing • Multithread management • Program flow • Events and callbacks • Exception handling Types Type creation • Type consumentation • Encapsulation • Class hierarchies • Reflection • Object life cycle • String manipulation Debugging and Security Input validation • Encryption • Assembly Management • Debugging • Diagnostics Data access I/O operations • Data consumtion • LINQ • Data serialization • Collections Theory Functional • Lambda Calculus • Category Theory

ADO.NET

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

• SQL Server connection strings. ConnectionStrings.com, abgerufen am 30. Mai 2014 (englisch). 

|}