Day 16 Programming in C# 70-483

Index

• Introduction
• Databases
• XML
• JSON
• Web services
• References

Introduction

This post is part of a series of post to help you prepare the MCSD certification, particularly the certification exam 70-483, based on the book:

You will find all the code available in the following GitHub repository. Lets talk a little bit about threads and how they work using a .Net development environment.

Databases

Applications need a persist storage system to save data, this can be done in a database using ADO.NET or the Entity Framework. You can also use a web service and retrieve a response in JavaScript Object Notation (JSON) or Extensible Markup Language (XML). All the .Net functionalities to work with databases is stored within the System.Data namespace and it consists on two different approaches: 

• connected: by running queries by using Structured Query Language (SQL) to create, read, update, and delete data (known as CRUD operations)
• or disconnected data: you'll use DataSets and DataTables to mimic the database structures. Any change made can be sent back to the data store by using a DataReader.

Providers:

1. Microsoft
2. SQL
3. Oracle
4. MySQL

Connecting: we always need to define some connection (DbConnection class) details using a connectionString and providing the database type, the location and the credentials to log in. Connections are used within a using statement, IDisposable is implemented. Typically this strings are stored in a config file (app.config or web.config) but you can build your dynamically by using the DbConnectionStringBuilder class: OracleConnectionStringBuilder, SQLConnectionStringBuilder... Hard-code this is a bad practice, as mentioned before you can save them in a config file and retrieve them by calling the ConfigurationManager class like here:

Connecting is a time consuming operation and leave a connection open can prevent other users to access the storage, instead you can use the connection pool to save some resources and time.

Selection: when running a particular query against a database you can use the SqlCommand class which returns a SqlDataReader which keeps track of where you are in the result set. Asyn/Await is supported as well. SqlDataReader is a forward-only stream. You can't go back while you're reading, you can access the columns by index and by name by calling:

• GetInt32(int index)
• GetGuid(int index)
• GetString(int index)

You can even batch multiple operations together and in result the SqlDataReader will return multiple result sets. Then you can move over them by calling NextResult() or NextResultAsync().

Update: here when you change something in a database you don't get a set with the information affected by your query, instead you end up with an integer which represents the amount of elements that have been modified. Run a ExecuteNonQuery() or ExecuteNonQueryAsync() in a command.

Parameters: typically you use parameters in your queries when filtering your selections or when updating data. Never hook your user interface components with you queries as this is a potential SQL injection attack candidate. Instead, use parameterized SQL which produces a more generic query and is easier to precompile an execution plan producing a more secure and better performance result.

Transactions: (ACID) key properties:

1. Atomicity: if one fails, they all fail (rollback).
2. Consistency: from one valid state to another.
3. Isolation: Multiple concurrent transactions won't influence each other.
4. Durability: committed transactions result is always stored permanently.

If nothing goes wrong you call TransactionScope.Complete() within a using statement. Transactions can be created using three options:

1. Required: Join the ambient transaction or create a new one if it doesn't exist.
2. RequiresNew: Start a new transaction.
3. Suppress: Don't take part in any transaction.

The .Net framework manages transactions for you. If the transaction uses nested connections, multiple databases or multiple resources it will be promoted to a distributed transaction (avoid if possible).

ORM (Object Relational Mapper): you can manually write your SQL statements but if your app grows it end up being a nightmare to maintain or improve. Here is when ORMs like Entity Framework come handy generating for you all those queries. 

It provides three different approaches:

1. Database First
2. Model First: typically use a graphical tool
3. Code First: you need to define all in code, your entities, relations and so... inherit 
1. DbContext is used to create your own context, which is the interface between your code and the database. 
2. By adding entities to the context you'll end up creating new rows and to commit that calling the SaveChanges() method. 
3. Conventions are applied like the Id property is a primary key and more...

XML

It's a document formatted to be readable both by humans and computers. First line is an optional line which is called prolog and tells you are reading to an xml file and the enconding used, it typically looks like this:

<?xml version="1.0" encoding="UTF-8" ?>

Now we'll take a look at some .Net classes to help us work with this type of files:

• XmlReader: read xml files in hierarchical manner and only forward without cached
• Create(): static. Param XmlReaderSettings to configure how to read and skip some data.
• XmlWriter: write xml files only forward without cached.
• Create(): static. Param XmlWritterSettings.
• XmlDocument: navigate and edit for smaller documents as it's slower than XmlReader/Writer. After editing you can save. It uses XmlNode to move through your document and perform changes of attributes in nodes. A nifty way to navigate is by using XPath (query language for xml). XPathNvigator class offers an easy way to navigate through an XML document.
• XPathNavigator: navigating through an XML

See an example here of how XML looks like:

<employees>
    <employee>
        <firstName>John</firstName> <lastName>Doe</lastName>
    </employee>
    <employee>
        <firstName>Anna</firstName> <lastName>Smith</lastName>
    </employee>
    <employee>
        <firstName>Peter</firstName> <lastName>Jones</lastName>
    </employee>
</employees>

JSON

Lighter version of XML is JSON or JavaScript Object Notation, this contains less rules and therefor is why is light. The .Net library Newtonsoft.Json available at http://json.codeplex.com/ offers some functionalities to work with this format. Typically is used with asynchronous calls between web sites and servers or alson known as AJAX (Asynchronous JavaScript and XML, in reality XML was replaced by JSON so AJAJ is more accurate).

See here below the previous XML example this time using JSON

{"employees":[
    {"firstName":"John", "lastName":"Doe"},
    {"firstName":"Anna", "lastName":"Smith"},
    {"firstName":"Peter", "lastName":"Jones"}
]}

Web services

Web services and micro web services turned in loosely coupled solutions for those who want to integrate to splitted systems. .Net offers the Windows Communication Framework (WCF) to build you own services. You will build a class with all the logic you service offers and it will be decorated with the attribute: ServiceContract close to the class name. Bear in mind you have to add System.ServiceModel reference into your project. For each of the methods exposed you will use the OperationContract decorator. Example:



Every WCF service has the following ABC properties:

• Address: defines the endpoint. This is the URL you have to tackle to open the connection.
• Bindings: it configures the protocols and transports that can be used to call your service: HTTP, HTTPS, named-pipe connections...
• Contract: this defines which are the operations your web service exposes.

If the web service is already created, VS offers you a handy way to add a reference into your project which will help you to map all the methods exposed. Behind the scenes, the proxy uses the configuration file (app.config, web.config) to get the ABC settings.

References

https://github.com/tribet84/MCSD/tree/master/mcsd
http://www.connectionstrings.com/
http://www.w3schools.com/sql/sql_injection.asp
https://en.wikipedia.org/wiki/Entity_Framework
http://www.w3schools.com/json/