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/

Index

• Intro
• Async and Await
• What is a database
• What is LINQ
• LINQ Demo

Intro

Let's talk today about data. Bring data is typically a high cost operation that's why we need to use always the perfect data storage system regarding our requirements. There are techniques like asynchronous communications which will help us on speeding our systems. How to retrieve that data is an important subject and decide which approach to use will be also discussed.

Async and Await

Async and await simplify asynchronous programming in .Net. Async and await allow asynchronous code to resemble the structure of synchronous code. Methods marked with async may return Task as the Task-based Asynchronous Pattern (TAP) defines. The async keyword instructs the compiler to allow await. The await keyword instructs the methods to return The await keyword instructs the compiler to resume execution within the same context after the operation is complete. 

In the following example we run an asynchronous operation in our call to DownloadStringAsync which is defined within the WebClient component in the .Net framework. This functions performs some operation in background, which means, the execution process in our app is never stopped. We don't know when this task will be completed, that's why we've published an event called "Completed" which a class can subscribed to and be notified when the completion occurs.

using System;
using System.Net;
using System.Threading.Tasks;

namespace ConsoleApplication1
{
    class AsynchronousExample
    {
        public event EventHandler Completed;

        void GetHtml(string url)
        {
            var client = new WebClient();
            client.DownloadStringCompleted += client_DownloadStringCompleted;
            client.DownloadStringAsync(new Uri(url));
        }

        private void client_DownloadStringCompleted(object sender, DownloadStringCompletedEventArgs e)
        {
            if (Completed != null)
                Completed(this, e);
        }
    }
}

In our next example we are using the reserved word "async" to decorate our method. See the type returned here is a Task. This means when we trigger probably a long running process which will return immediately a Task object. It won't stop to the completion of the asynchronous method. This task object will contain the result but it will be uncompleted until the DownloadStringTaskAsync finishes.

using System;
using System.Net;
using System.Threading.Tasks;

namespace ConsoleApplication1
{
    class AsynchronousExample
    {
        private async Task GetHtmlWithAsync(string url)
        {
            var client = new WebClient { };
            return await client.DownloadStringTaskAsync(new Uri(url));
        }
    }
}

What is a database

• A database is a data store
• Data stored in a database is structured
• Data stored in a database may be related (Referential integrity)
• A database provides a way to access / query data (Optimized by indexes)

SQL DB Types

• Windows Azure SQL Database
• Local Network SQL Database
• Local Machine SQL Server Express 
• Application SQL LocalDB
• Application SQL CE
• Other providers: Oracle, SqLite, MySql, DB2

Types of access to a database: how do we talk to the database

• Low-Level
• Manual queries
• DbDataReader
• Object Relationship Models (ORM): allow you to conceptualize the model of you db.
• Entity Framework
• nHibernate

What is LINQ

Language Integrated Query:

• is a general-purpose Query Language
• is an integrated part of the .Net languages
• is Type Safe and has Intellisense
• includes operators like Traversal, Filter and Projection
• can be optimized with compiler versions
• can be invoked using its Query Syntax
• can be invoked using its Method Syntax

LINQ Demo

See here enclosed some examples about how to use the LINQ library in a .Net environment. I'll try to show you how to get the even and odds number from a list. Then we'll move forward sorting a bunch of letters in a handy way

var data = Enumerable.Range(1, 50);

// even or odd
// method syntax
var method = // IEnumerable
    data.Where(x => x % 2 == 0)
        .Select(x => x.ToString()); // Projection: take data and transform it into text

// query syntax
var query = // IEnumerable
    from d in data
    where d % 2 == 0
    select d.ToString();

var projection =
    from d in data
    select new
    {
        Even = (d % 2 == 0),
        Odd = (d % 2 != 0),
        Value = d,
    };

I'm using different approaches in order to get odd's and even numbers. I'm presenting the result in different ways also. For the method and the query variable, I'm storing the values as strings. Within the projection variable, I'm actually creating small structures containing three values, the first two save a boolean true/false and the last one stores the current value.

var letters = new[] { "A", "C", "B", "E", "Q" };

// query syntax
var sortAsc =
    from d in data
    orderby d ascending
    select d;

// method syntax
var sortDesc =
    letters.OrderByDescending(x => x);


var values = new[] { "A", "C", "A", "C", "A", "D" };

var distinct = values.Distinct();   // remove duplicates
var first = values.First();         // error if "values" is empty
var firstOr = values.FirstOrDefault();  // default if "values" is empty
var last = values.Last();
var page = values.Skip(2).Take(2);

See how in the previous example I'm working with a couple of character lists. For the first list I sort the characters using linq query sintax and linq method syntax respectively. For the last list, I wanted to show you the potential of  this language which allows you to performs actions like, remove duplicates, get the first/last or even skip some values and take more than one.

Really powerful library which I'm sure will help you to short out your code and present your future projects in a really professional way.

References

http://www.microsoftvirtualacademy.com/training-courses/developer-training-with-programming-in-c
https://msdn.microsoft.com/en-us/library/hh873175.aspx
https://msdn.microsoft.com/es-es/library/bb397926.aspx

A lot of times, we have the need to execute a scheduled task to perform different tasks in our databases like backups, restore db, extract or import data. There are a lot of different tools which are really powerful but for the most of the small tasks we might use the SQL console.

A few days ago I had to perform some tasks over a database throw different servers and I decided to do it using a sqlcmd process in a batch file. These are the steps I wanted to execute:

• Copy the last backup created in server A to server B.
• Restore the backup overwriting the database in server B.
• Extract all the images from one table in an FTP server

Copy last backup

At this stage wee need to share the folder where the .bak files are generated in the production database (server A). That folder contains the las N bakups created each week but we just need to get the last one created.

In the following script lines we take a list with all the .bak files and copy the last one over the network. Until the copy process is not finished our scrip does not continue executing the following lines:

@echo off

:Variables
SET DatabaseBackupPath=\\DatabasePath\

echo %DATE% %TIME%: Starting scheduled task >> "log.txt"
echo %DATE% %TIME%: Copy prod database backup >> "log.txt"

FOR /F "delims=|" %%I IN ('DIR "%DatabaseBackupPath%\*.bak" /B /O:D') DO SET NewestFile=%%I
copy "%DatabaseBackupPath%\%NewestFile%" "E:\Production DB copy\" /z

Notice the "echo ... >> "log.txt" ", which is saving all the actions we take in a text file file to get a summary of the tasks performed. In every echo command we are using the %DATE%%TIME% which gets the current date and time from the server and print it in the log.txt file.

Restore backup

Now the backup is already in our test server (server B) and we are free to perform a restoration task using the database backup just copied. The following lines are consecutive to the previous example and they will replace the database with the bakup from production:

echo %DATE% %TIME%: Restore Sys Database >> "log.txt"

:sqlcmd -S DbServerName -U UserName -P Password -o "DBlog.txt" -d master -Q ^
:"RESTORE DATABASE DbName ^
:FROM DISK = N'E:\Production DB copy\%NewestFile%' WITH REPLACE"

Notice about the "WITH REPLACE" parameter which allow us to overwrite the database if there is already created.

Extract all the images

Finally to extract all the images (or any other column you need to extract). First, I used SQL Server Data Tools, which it's a really powerful tool provided from Microsoft to handle all the extra stuff you want to do with your databases. I followed the tutorial from Daniel Calbimonte which comes really good explained with images of all the steps:

http://www.mssqltips.com/sqlservertip/2693/export-images-from-a-sql-server-table-to-a-folder-with-ssis/

Once your package is properly created you just need to create a new job in your SQL Server Agent to execute it every time you need to update the images. I faced some problems when I did this step, because the password to connect to the database is saved inside of the package and you need a way to secure it. In the following tutorial you can see an explanation of how to solve it:

http://zarez.net/?p=155

Finally, you'll just need to create a call to sql to execute the job throw our great bash console application:

echo %DATE% %TIME%: Execute 'Export images delta' job >> "log.txt"
osql -S "SQL11CTMD" -U UserName -P UserPassword -d master -Q"exec msdb.dbo.sp_start_job 'Export images delta'"

--

References 

• Daniel Calbimonte / http://www.mssqltips.com
• Zarez / http://zarez.net/?p=155

Index

1. Introducing Relational Database Management Systems (RDMS)
2. Understanding Query Methods
3. Database Connections

1. Introducting RDMS

Database is a collection of related information, for instance, an mp3 player is a kind of database where you not only save just your favourint music tracks, you also save related information like the name of every song, the lenght, the artins. All this information is stored and is related to the same subject.

RDMS is a relational storage concept for data, where you can allocate the information in different tables and you can link those tables depending of how do you want to access the information. For instance if you want to create a new database to save all the music you have at home, first you can create a table to save all the songs, other table to save the info related with the artist, other table could store the different music styles you have...

Uses normalization, which tries to avoid fields repetition. For our music example, imagine you want to save the city of all the bands you have in your music collection. First you can probably think, ok! let's save the City in my Bands table directly. At first it has sense, but the problem comes when that table goes bigger and the amount of Cities is too big. Why we don't create another table just to save the cities with a number assigned to every city (Primary key) and link this table with our bands table. Using this process, you are not saving thousands of times the same city name. This way, we will just have a number in the band table (Foreign key) which means the city assigned to that band. You save it just one time in your cities table and link it with a number, which is less heavy than the text and very easy to link for an RDMS.

See in the following example how different tables are linked in a company database. You can see here below, table "Orders" used to save the different sales orders with a numeric primary key called "OrderID", this is linked with a third table called "LineItems" responsible of link orders and products saving the "OrderID's" and the "ProductID" numbers. This way save a lot of space and process time working with numbers instead of the Product text name.

RDMS consists of services and applications for managing data and it allows querying, updating, inserting and deleting of data. All these tools give you the ability to play with all the information you stored in your database in a very efficient and performed way.

You can find different providers for your database like: SQL Server, Oracle or MySql. All of them use their own language (TransactSQL, PL/SQL and ) to access the information throw different commands 

2. Understanding Query Methods

Queries, allow developers to get information from the database, insert, edit and delete either from a web application or a desktop application. With a few examples we will go throw all of these different queries, using the Microsoft AdventureWorks2012 (you can get it from here) database tables:

• Selecting data: you will need to use the "Select" command from SQL, in our first example we will select all the columns throw the reserved word: *, after the SELECT word. Also we need to specify from which table we want to get the information with the FROM reserved word, in our case we are using the "Person.Address" table. Follow the following patter to get information: SELECT <column name/s> FROM <table name> WHERE (optional) <conditions>

USE AdventureWorks2012;

-- Get information from the address table for people

SELECT *
FROM Person.Address;

-- Get the state or province associated with the ID 79

SELECT *
FROM Person.Address
WHERE AddressID = 79;

• Updating data: to update information in your tables you need to use the UPDATE statement first, followed by the table name, then you will need to specify the values that you want to save and optionally the rows where you want to apply those values. Follow the following patter: UPDATE <table name> SET <values to save> WHERE (optional) <conditions>. In the following example we are setting the middle name 'Angela' to someone called 'Gail Erickson'.

-- Update the middle name to an initial
UPDATE Person.Person
SET MiddleName = 'Angela'
WHERE FirstName = 'Gail' AND LastName = 'Erickson'

You will need to be very careful when you launch update queries to your database, because sometimes you think you are updating just the rows that you want to update but maybe there are more rows which fit with your query conditions and can apply those changes to them too. To avoid these kind of errors the best way to update fields is selecting them by using their primary key like the Id because these keys use to be unique in the tables instead of using text fields.

• Inserting data: for this example we need to use the INSERT statement with the following query pattern: INSERT INTO <table name> VALUE <different values>. First take a look at that table to see how is built and which data contains. The idea is to add a new language for the people from Canada in the cultures table.

-- Return all information from the culture table
SELECT *
FROM Production.Culture;

-- Insert a new value into the culture table
INSERT INTO Production.Culture
VALUES ('ca-fr', 'Canadian French', GETDATE());

As you can see, one of the fields is populated with a special command called GETDATE(), this command gets the current date and time when the update statement is triggered.

• Deleting data: you will need to be very careful with this command, rememberer you are applying changes directly in the database and there is no roll back. The following pattern explains how this command works: DELETE FROM <table name> WHERE (optional) <conditions>. See the WHERE optional parameter, if we don't specify this parameter in our delete query, we are actually saying to SQL Server that we want to delete all the rows within the table we have specified. In the following example we will delete our previous insert.

-- Delete an item from the culture table
DELETE FROM Production.Culture
WHERE CultureID = 'ca-fr';

I hope these examples help you to get a better understanding of how powerful is this language and all the things you are capable to do. For a better understanding you can get more info from W3.

3. Database Connections

In our future applications we'll need to specify how to connect to our database server, here is where we need to provide a database connection, which provides the different user credentials and server information that we need to be able to link our app with our RDMS. The connections can be pooled, which means, open a connection to a database and allow a different set of sockets that can be used by different clients to access the database. This is used because open a database connection takes a lot of time, so we just want to open it once by our connection handler and the user can ask him for a connection when it is available. See the image bellow to get a better understanding:

This way we have just one place to handle the connection with the database and we allow access to those we want.

Another important point is to open and close the connection. If a database connection is not closed could produce security issues in your applications specially if your applications is web.

Here below I include an example of a connection string with different parameters that are required to make our connection successful:

m_sConnStr = "Provider=SQLOLEDB;Data Source=MySqlServer;Initial Catalog=Northwind;Integrated Security=True".

Provider is the component responsible of the communication and information handling. DataSource is the name of the server where we want to connect. Initial catalog is the name of the database within that database server. Integrated security equals true means, if I am connected to windows with my domain user and password, use those credentials to login into the database server. This is just an example but you can find thousands of different connections strings to use in your applications.

That's all for now and I hope to see you in the next module!