Preparing to MS Exam 70-483 - Retrieve data from a database; update data in a database; consume JSON and XML data; retrieve data by using web services

Classes dedicated for work with Databases are situated in System.Data namespace which is called ADO.NET.

ADO.NET data:

• connected (explicit connection to DB)
• disconnected (DB structure in-memory of app)
• DataSets
• DataTables

DataProvider is used for establishing connection to almost any DB type:

• MS SQL
• MySQL
• Oracle

Connection to DB

There is an abstract class System.Data.Common.DbConnection which is base for other classes each of them represents connection to particular database.

using (SqlConnection connection = new SqlConnection(connectionString))
{
          connection.Open();  
} 

Connection string can be hardcoded, generated by any child class of an abstract DbConnectionStringBuilder class or declared config file (in app.config / web.config manually of using System.Configuration.ConfigurationManager.ConnectionStrings).

NOTE 

ADO.NET has connections pool which is enabled by default. Instead of creating new connection it checks if there is opened one and utilize them.

CRUD is for Create, Read, Update, Delete

NOTE

As an example I will use SqlClient classes. 

Reading

As soon as connection established we can use SqlCommand class to query DB for data. SqlCommand.ExecuteReader() returns SqlReader  object which track position in the result set of SqlCommand.

SqlCommand command = new SqlCommand(“SELECT * FROM CustomTable”, connection);
SqlDataReader dataReader = command.ExecuteReader();
while (dataReader.Read())
{
       Console.WriteLine(dataReader["fieldName"]);
}
dataReader.Close();

NOTE

There is no possibility to go backward using SqlReader, it is forward-only.

Create, update, delete 

All these operations can be done by using method ExecuteNonQuery of SqlCommand object, it will return number of affected rows:

string query = “UPDATE CustomTable SET fieldName=’TableField’”
SqlCommand command = new SqlCommand(query, connection);
int numberOfUpdatedRows = command.ExecuteNonQuery();
Console.WriteLine(“Updated {0} rows”, numberOfUpdatedRows);

To avoid SQL Injection use parameterized SQL statements:

string query = “INSERT INTO CustomTable ([ID], [TableField]) VALUES(@id, @tableField)”;
SqlCommand command = new SqlCommand(query,connection);
command.Parameters.AddWithValue(“@id”, id);
command.Parameters.AddWithValue(“@tableField”, “unnamed”);
int numberOfInsertedRows = command.ExecuteNonQuery();
Console.WriteLine(“Inserted rows: {0} ”, numberOfInsertedRows);

Transactions

Transactions enables us to group set of queries into one atomic operation called transaction, if one of operations fails other will fail too.

Transactions provides ACID:

• Atomicity
• Consistency
• Isolation
• Durability - result of transuction stored permanently

The easiest vay to use transactions is TransactionScope([enum TransactionScopeOption]):

using(TransactionScope transaction = new TransactionScope())
{
    // querying Database
    TransactionScope.Complete()
}

TransactionScopeOption:

• Required (default) - joins with ambient transaction, if exists
• RequireNew - starts new transaction
• Suppress - do not participate in any transaction

NOTE

If your transaction with nested connections or multiple databases is promoted to a distributed transaction and if it’s not necessary it could cause performance hit.

Entity Framework

Relational structure of databases does not match OO stucture and to prevent that mismatch Object Relational Mapping (ORM) is used. One of the ORM's is Entity Framework which provides 3 approaches:

• Code First - database generated according to your object model
• Database First - object model generated according to your database