ADO.Net Connected model

Connection
Command
DataReader
Transactions

КОМПОНЕНТЫ CONNECTED MODEL

Connection
Command
DataReader

CONNECTION

СОЗДАНИЕ CONNECTION

using (var conn = new SqlConnection(
"Data Source=(local);Initial Catalog=AdventureWorksLT;Integrated Security=True"))
{

CONNECTION STRINGS

Data Source=(local);

Integrated Security=True

Initial Catalog=AdventureWorksLT;

Server

Data Base

Windows-authentication

Общая структура
param1=value; param2=value; …
Свои элементы

Connection Strings (ADO.NET)
https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/connection-strings

http://www.connectionstrings.com

CONNECTION STRING BUILDER ПРИМЕР

var connectionStringBuilder = new SqlConnectionStringBuilder
{
DataSource = "(local)",

COMMON CONNECTION PARAMETERS (SQLCLIENT)

Connection parameters
https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring(v=vs.110).aspx

CONNECTION STRING + APP.CONFIG + PROVIDER FACTORIES

CONNECTION POOLS

Connection Pooling

Application

con1.Open()

con2.Open()

con3.Open()

TestDB

Connection pools

Pool A

Pool B

Data Source=(local);Initial Catalog=SqlDemoDB;Integrated Security=True

Data Source=(local);Initial Catalog=TestDB;Integrated

Держите соединение с источником минимальное кол-во времени
Всегда закрывайте все созданные вами

COMMAND

СОЗДАНИЕ COMMAND

Command should be associated with Connection

using (IDbConnection connection = new

ОБЩИЕ СВОЙСТВА COMMAND

using (IDbConnection connection = new SqlConnection(ConnectionString))
{
connection.Open();
var command

COMMAND TYPES

COMMAND RESULTS

ПАРАМЕТРИЗОВАННЫЕ ЗАПРОСЫ. SQL ИНЪЕКЦИИ

string.Format( "select top 1 * from dbo.Users where Login = '{0}' and Password = '{1}'", login, password);

select top 1 * from dbo.Users 
where Login = ‘user' and Password = '123'

select top 1 * from dbo.Users 
where Login = '' OR 1 = 1

COMMAND PARAMETERS

command.CommandText = 
"SELECT count(*) FROM Northwind.Products 
WHERE UnitPrice >= @minPrice";

IDbCommand

var minPrice = command.CreateParameter(); minPrice.ParameterName = "@minPrice"; minPrice.DbType = DbType.Decimal; minPrice.Value = 50;
command.Parameters.Add(minPrice);

SqlCommand

command.Parameters.AddWithValue("@minPrice", 50m);

ВЫЗОВ STORED PROCEDURES

var command = connection.CreateCommand(); command.CommandText = "[Northwind].[CustOrdersStatistic]"; command.CommandType = CommandType.StoredProcedure; command.Parameters.AddWithValue("@CustomerID", "BONAP");
var all = command.Parameters.Add(     new SqlParameter()     {         ParameterName = "@All",         DbType = DbType.Int32,         Direction = ParameterDirection.Output     }); var shipped = command.Parameters.Add(     new SqlParameter()     {         ParameterName = "@Shipped",         DbType = DbType.Int32,         Direction = ParameterDirection.Output     }); command.ExecuteNonQuery(); Console.WriteLine("{0} {1}", all.Value, shipped.Value);

CREATE PROCEDURE [Northwind].[CustOrdersStatistic]     @CustomerID nchar(5),     @Shipped int OUTPUT,     @All int OUTPUT AS
…

DATAREADER

Side-by-side execution can only take place in different connections
Every readers should

DATAREADER МЕТОДЫ

DataReader
Navigation
Read()
NextResult()
HasRows
Get fields value
By field name
[“field_name”]
By field index
GetString(i)
GetDateTime(i)
GetBoolean(i)
…

READ MANY RESULT SETS

var command = connection.CreateCommand(); command.CommandText =     "SELECT * " +     "FROM Northwind.Orders " +     "where OrderID = @orderId;" +     "SELECT p.ProductName, ods.UnitPrice, ods.Quantity " +     "FROM Northwind.[Order Details] ods " +     "LEFT JOIN Northwind.Products p ON p.ProductID = ods.ProductID " +     "WHERE ods.OrderID = @orderId;";
command.Parameters.AddWithValue("@orderId", 10262); using (var reader = command.ExecuteReader()) {     reader.Read();     Console.WriteLine("{0} ({1})", reader["OrderID"], reader["OrderDate"]);
    reader.NextResult();     while (reader.Read())         Console.WriteLine("\t{0} - {1}", reader["ProductName"], reader["UnitPrice"]); }

TRANSACTIONS