Jean Paul's Blog

There are 2 types of People in the World, One who Likes SharePoint and..

  • Microsoft MVP

  • MindCracker MVP

  • CodeProject MVP

  • eBook on SharePoint 2010

  • eBook on Design Patterns

  • eBook on Windows Azure

  • NLayers Framework @ CodePlex

  • MSDN Forums

  • .Net vs. Java

    Due to Public Demand

Posts Tagged ‘database’

Windows Azure – Summary of Storages

Posted by JP on November 13, 2011

In this article I would like to summarize the storage methods we learnt in Windows Azure. The advanced features are also discussed here.

Types of Storage

Recollecting that we have 3 types of storage in Windows Azure:

  • Local Storage
  • Windows Azure Storage
  • SQL Azure Storage

clip_image002

Below are some of the advanced storage features.


Local Storage Features

This is a temporary storage and the application cannot see the underlying disk or drive on which the storage is done. But the application can request up to 1 GB of data on the local storage.

The local storage space is not accessible outside or to any other instances. Windows Azure on the runtime chooses the best drive to store the data.

Windows Storage Features

The Windows Azure operating system allows 4 kinds of storage which are Blob, Table, Queue and Disk storage.

These storage accounts are publicly exposed so we can access they from virtually anywhere. The storage account data is exposed using HPTTP, REST and OData protocols.

The Blob storage provides huge amount of data manipulation. The table storage provides structured data processing as entities and Queue storage is efficient in back end and front end communication.

In Windows Azure Storage there is one more type called Drive storage. Windows Azure Drive allows us to upload Virtual Hard Drives as Blob. This provides great flexibility to developers. More information can be found here.

Automatic Replication is a feature that makes sure the data stored is replicated 3 times. This is taken care by the Content Delivery Network (CDN) and improves the performance.

Geo-replication is a new feature that provides replication for Blob and Table data at no additional cost. This provides the reliability during a crisis situation.

Queue Insert Message Timeout is a new feature that makes the queue message invisible until the specified time expires.

Table Query Projection is a new feature that allows partial reading of entity’s properties. This will improve the performance.

REST and Managed APIs support working with storage services. We need to download the REST API SDK to achieve this.

Pricing of Windows Azure Storage is performed based on the average usage of blob, table, queue and drive storage.

The updated information can be found here.

SQL Azure Storage Features

SQL Azure represents Cloud version of SQL Server. It is a highly available and scalable database on the cloud premises.

SQL Azure provides ease of use and maintenance. No need of installation and applying patches as these activities will be taken care by the provider.

ADO.NET, ODBC and JDBC providers are supported on SQL Azure.

The design time structure and data management can be done through:

  1. Windows Azure Portal
  2. SQL Azure Management Portal
  3. MMC Tool
  4. SQL Server Management Studio

The SQL Azure provides automatic replication of data and automatic switching of the server in case of failures.

Some of the current drawback of SQL Azure is:

  • Not all data types in SQL Server are supported
  • SSAS (SQL Server Analysis Services) not supported
  • Service Broker feature not supported

The editions in SQL Azure are the following:

  • Web Edition
  • Business Edition

The web edition can support 1 GB or 5 GB in size and good for small Web applications. The business edition can support up to 50 GB of data and good for independent software vendors and enterprise applications.

The layers in SQL Azure would be the following.

  • Client Layer
  • Services Layer
  • Platform Layer
  • Infrastructure Layer

clip_image004

The Client layer is hosted in windows azure and communicates with our application. The Service layer acts as a gateway between the client layer and the platform layer. The Platform layer includes the physical servers which supports the service layer. The Infrastructure layer consists of the IT administration of the physical hardware and operating systems that supports the service layer. More information can be found here.

Summary

In this article we have summarized the types of storages in Windows Azure and the advanced features provided by them.

Advertisements

Posted in C# | Tagged: , , , , | Leave a Comment »

Windows Azure – SQL Azure – Using ADO.NET Entity Framework – Data Operations

Posted by JP on November 13, 2011

In this article we are going to explore data operations using ADO.NET Entity Framework over our SQL Azure database. The data model creation which is an essential part required was discussed in the previous article.

Pre-Requisites

For working on this application, you need to have the following pre-requisites.

  • Azure project with web role
  • Entity data model with Employee class

You may use the project from the previous article.

After having the above, place the following on the Default.aspx page

  • 4 buttons with labels Insert, Update, Delete, Refresh
  • A grid control

clip_image002

Insert Operation

For creating a new employee, use the following code in the Insert button click event.

mysqlazureEntities context = new mysqlazureEntities();

Employee employe = new Employee();

employe.Name = “New Employee from EF”;

context.AddToEmployees(employe);

context.SaveChanges();

Update Operation

For creating a new employee, use the following code in the Update button click event.

mysqlazureEntities context = new mysqlazureEntities();

Employee employee = context.Employees.Where(emp => emp.Name == “New Employee from EF”).FirstOrDefault();

if (employee != null)

employee.Name = “Updated Name”;

context.SaveChanges();

Delete Operation

For creating a new employee, use the following code in the Delete button click event.

mysqlazureEntities context = new mysqlazureEntities();

Employee employee = context.Employees.Where(emp => (emp.Name == “New Employee from EF” || emp.Name==”Updated Name”)).FirstOrDefault();

if (employee != null)

context.DeleteObject(employee);

context.SaveChanges();

Select Operation

For creating a new employee, use the following code in the Refresh button click event.

GridView grid = GridView1;

grid.Columns.Clear();

grid.Columns.Add(new BoundField() { HeaderText = “Id”, DataField = “Id” });

grid.Columns.Add(new BoundField() { HeaderText = “Name”, DataField = “Name” });

mysqlazureEntities context = new mysqlazureEntities();

grid.DataSource = context.Employees.ToList();

grid.DataBind();

On executing the application and playing with the buttons you can view the following results.

clip_image004

Summary

In this article we have explored the data operations using ADO.NET Entity Framework over our SQL Azure database. You need to change the database name, user name and password in the configuration file.

Posted in C# | Tagged: , , , , | Leave a Comment »

Windows Azure – SQL Azure – Using ADO.NET Entity Framework – Creating DataModel

Posted by JP on November 13, 2011

Another good remarkable point regarding SQL Azure is the reuse of our existing skills with ADO.NET Entity Framework. As the SQL Azure connectivity using ADO.NET is possible, it opens the door for Entity Framework as well.

Abut ADO.NET Entity Framework

ADO.NET Entity Framework is an ORM (Object Relational Mapping) framework from Microsoft. It provides the flexibilities like tables to entities, relational mapping to properties, Data operations to encapsulated methods etc.

clip_image002

Surely it gives the advantages of:

  • Wrapping of database in Object Oriented Manner
  • More Manageability
  • More Flexibility
  • Less Development Time

But everything comes at the cost of learning. You can find more information here.

Creating Schema

Create a new Windows Azure project and add a new web role into it. After that right click on the project and use Add > New Item. In the appearing dialog choose the Data item from the left pane and ADO.NET Entity Data Model from the right pane.

clip_image004

Enter the names as MyModel as shown above. Click the Add button to continue to see the following screen.

clip_image006

In the above dialog choose the default option (Generate from database) and click the Next button to continue.

clip_image008

In the appearing dialog choose your existing connection from the drop down list and click Next to continue..

clip_image010

You will be prompted with the login dialog above. Enter your user name and password to continue. (The Save my password option saves the password to the connection string in config)

In the next dialog, select the tables as shown below and click Finish button.

clip_image012

This will generate the entity data model files in your project. You can see that the Employee table from previous example is populated in the tables and it generates the Employee class as shown below.

clip_image014

Note: If your database does not contain the above table, you may need to create the table and repeat the operations.

Summary

In this article we have explored the scope of using ADO.NET Entity Framework on SQL Azure. The possibilities give us more flexibility, control and reuse of our existing ORM skills. The source code attached contains the Entity Data Model code. You need to change the database name, user name and password in the configuration file.

Posted in C# | Tagged: , , , , | Leave a Comment »

Windows Azure – SQL Azure Management Portal – Create Database and Table

Posted by JP on November 13, 2011

In the previous article we have seen how to connect and create database using the SQL Server Management Portal application. In this article we can explore how to create create table, views, run sql queries on it etc.

Login to SQL Azure Management Portal

We need to sign in to the SQL Azure Management Portal before continuing with the topics. Use the database url for signing in. (eg: https://server.database.windows.net)

Creating Table

Now we can explore how to create a table using the management portal. After selecting the database, click on the New Table button to get the table creation screen.

clip_image002

Within a few seconds you will get the following screen for table creation. Enter the table details. I have named the table as Car and entered 3 columns.

clip_image004

Click on the Save button to save the table.

clip_image006

The left side tree view provides access to all tables, views and stored procedures in the current database.

clip_image008

For accessing the table, click on the Tables item as shown above.

clip_image010

Click on the Data button from the Car table row to view the data inside it.

clip_image012

We can use the Add Row button to create new rows in the table. After entering the records, use the Save button to commit the changes.

clip_image014

Note: Without using the Save button the records will not be saved in the database. This is an important point in dealing with data.

Creating Views, Stored Procedures

Similar to the way of table creation, we can create Views and Stored Procedures using the ribbon toolbar buttons.

For creating views, use the New View button.

clip_image016

For creating stored procedures, use the New Stored Procedure button.

clip_image018

Query Execution

You can write and execute SQL query on the database using the New Query button from the ribbon toolbar.

clip_image020

On clicking the button, the following query window appears.

clip_image022

Enter the query “SELECT * FROM Car” and click the execute button. You will see the results as shown below. (If you cannot see the results, make sure you clicked the Save button in the record creation screen)

clip_image024

Summary

In this article we have explored table creation, running sql query on the SQL Azure Management Studio web application.

Posted in C# | Tagged: , , , , | Leave a Comment »

Windows Azure – SQL Azure Management Portal Introduction

Posted by JP on November 13, 2011

From the previous articles we have seen:

  • Creating database using Management Portal web application
  • Creating table through code
  • Using Management Studio desktop application

In this article we are going to discuss how to connect and create database using the online Management Portal application.

Advantage

One of the advantages of using it would be an extra tool that will be available on machines without SQL Server Management Studio installed.

SQL Azure Management Portal

For invoking the tool there are 2 ways.

Sign in to your management portal and click on the Manage button from the database view.

clip_image002

Or

Paste the url of your database in a browser address box. You can get the fully qualified url from the database server list as shown below.

clip_image004

In the above case the url would be http://abcd.database.windows.net

SQL Azure Management Portal Application

This is another silverlight application. The application will be prompting with the username and password. You need to enter your SQL Server username and password.

clip_image006

Enter your username and password and click the Log on button. (The database name is optional and we can choose later)


The Main Window

After login, you will be getting the following screen.

clip_image008

Creating new Database

We can create a new database using the Create button from the main window.

clip_image010

On clicking the button, within a short time you will be prompted with the following screen. You can enter the database details in it.

clip_image012

Use the Submit button and you will be ready with the database as shown below.

clip_image014

Summary

In this article we have seen how to connect and create database using the SQL Azure Management Portal. This tool provides an alternative way to the SQL Server Management Studio desktop application.

Posted in C# | Tagged: , , , , | Leave a Comment »

Windows Azure – Data Definition and Manipulation on SQL Azure

Posted by JP on November 13, 2011

In the previous article we have explored how to connect to the SQL Azure database through code. In this article we can work on the data definition and manipulation on the SQL Azure database. The idea of the article is to get familiar with the SQL Azure data access code.

The activities include the following:

clip_image002

We can start with the activities one by one:

Create Table

Add the following method for creating the table.

private void CreateTable()

{

SqlCommand command = new SqlCommand();

command.Connection = _connection;

command.CommandText = “CREATE TABLE Employee (Id int primary key, Name

varchar(50))”;

command.ExecuteNonQuery();

}

Insert Rows

Use the following method to insert rows into the table.

private void InsertRows()

{

SqlCommand command = new SqlCommand();

command.Connection = _connection;

for (int i = 1; i <= 10; i++)

{

command.CommandText = string.Format(“INSERT INTO Employee (Id, Name) VALUES({0}, ‘Name{1}’)”, i.ToString(), i.ToString());

command.ExecuteNonQuery();

}

}

Update Rows

Use the following method to update certain rows in the table.

private void UpdateRows()

{

SqlCommand command = new SqlCommand();

command.Connection = _connection;

command.CommandText = “UPDATE Employee SET Name=’NewName’ WHERE Id=5”;

command.ExecuteNonQuery();

}

Delete Rows

Use the following method to delete certain rows into the table.

private void DeleteRows()

{

SqlCommand command = new SqlCommand();

command.Connection = _connection;

command.CommandText = “DELETE FROM Employee WHERE Id > 5”;

command.ExecuteNonQuery();

}

View Rows

Place a DataGrid control on the Default.aspx page and name it as grid. Now add the following method to view the rows.

private void ViewRows()

{

grid.AutoGenerateColumns = false;

grid.Columns.Add(new BoundColumn() { HeaderText = “Id”, DataField = “Id” });

grid.Columns.Add(new BoundColumn() { HeaderText = “Name”, DataField = “Name” });

SqlCommand command = new SqlCommand();

command.Connection = _connection;

command.CommandText = “SELECT * FROM Employee”;

SqlDataReader reader = command.ExecuteReader();

grid.DataSource = reader;

grid.DataBind();

}

On execution of the application with the above methods invoked in the page load event in given order, we can see the following output.

clip_image004

Note: The open connection operation was demonstrated in the previous article so not included here. For the complete working application you can use the source code attached.

Summary

In this article we have explored the table creation, insert, update, delete database operations and selecting of rows on SQL Azure using ADO.NET classes.

Posted in C# | Tagged: , , , , | 1 Comment »

Windows Azure – Connect to SQL Azure

Posted by JP on November 13, 2011

We are now are ready with our SQL Azure database server and database. So we can proceed with connecting with the database. We can use our ADO.NET Connection class to connect to the database.

The steps involved here are:

  • Get the Database Server Name
  • Connect using SqlConnection class

The steps are following.

Get the Database Server Name

Sign in to the windows azure portal and click on the Database item from the left pane.

clip_image002

Then expand the subscriptions item and select the database server name. From the right hand side properties window, you can get the Fully qualified DNS Name of the server as shown in the figure below.

clip_image004

Copy your server name. Now open Visual Studio and create a new Azure project and add a web role into it.

Add a SqlDataSource component into it from the toolbox. In the properties window of the control, click on the ConnectionString property. From the drop down list select New Connection. You will be prompted with the following dialog.

clip_image006

Select Microsoft SQL Server from the list above and click continue. Now the Add Connection dialog will appear as shown below.

clip_image008

Enter the following

  • SQL Azure Server Name
  • User Name
  • Password

(Remember to check the Use SQL Server Authentication check box)

Now select the database we created and press Test Connection button. If you get the succeeded message box, you have a valid SQL Azure connection. Good Job!

clip_image010

Now press OK on the Add Connection dialog and from the properties window, you can collect the connection string as shown below.

clip_image012

Connect using SqlConnection class

Now we have the valid connection string, we can try connecting through code.

Open the code view of Default.aspx from the web role project. Add the following code into it. (Resolve the namespaces)

protected void Page_Load(object sender, EventArgs e)

{

string connectionString = “your connection string here”;

SqlConnection connection = new SqlConnection(connectionString);

connection.Open();

if (connection.State == System.Data.ConnectionState.Open)

Label1.Text = “Connection Succeeded!”;

else

Label1.Text = “Connection Failure!”;

}

Open the design view of Default.aspx and add a label control into it. You may remove the data source control from there.

Now execute the application and you can see the result as shown below.

clip_image014

If the message is Connection Succeeded you are having a valid connection established with SQL Azure.

Collecting Connection String from Windows Azure Portal

We can also get the connection string from the azure portal by selecting the database and clicking the View connection string button.

clip_image016

Then the following dialog box shows the connection string. The first one ADO.NET represents our needed connection string.

Summary

In this article we have seen how to get the database server name, get the connection string and connect it using c# code.

Posted in Azure | Tagged: , , , , , | Leave a Comment »

Windows Azure – Create new SQL Azure Server and Database

Posted by JP on November 13, 2011

Now we can proceed with creating the database in the azure account. The steps involved in this are following.

  • Create new subscription
  • Create new server
  • Create new database

New subscription is already created and we can reuse it. So we can proceed with the next 2 steps.

Create new server

Sign in to the windows azure portal and click the Database category from the left pane as shown below.

clip_image002

Wait for a few seconds and you will be getting the main pane ready as below:

clip_image004

Click on the Create a new SQL Azure Server item from the above screen. The following screen appears.

clip_image006

In the above dialog select the Subscription and click next to continue.

clip_image008

In the above screen select the Region and click Next to get the next screen.

clip_image010

In the above screen enter the administrative login and password. (The usual names like sa, admin etc. are reserved and entering is not allowed)

Note: On entering a valid login name and passwords only, the Next button will get enabled.

On clicking next, we get the following screen.

clip_image012

Use the Add button to add the IP address range. We need to enter the range for getting client access to the server. For demonstration I have entered 0.0.0.0 to 255. 255. 255. 255 to open all IP get access to it.

Now click the Finish button.

You can open the subscription item from the left top pane to see the new server and default databases inside it. You will be provided with a master database with read only access to it.

clip_image014

Create a new Database

Now we can proceed with creating our database. To achieve this click on the Create button from the top ribbon as shown below.

clip_image016

The following dialog box appears:

clip_image018

Enter the database name you prefer and select the default options for other entries, click Ok button.

You are now ready with the new database.

clip_image020

Now we are ready with the new database. In the upcoming articles we can see how to use the database using ADO.NET classes.

Summary

In this article we have seen how to create a database server and database in SQL Azure. You can find more information on SQL Azure here.

Posted in C# | Tagged: , , , , | Leave a Comment »

Windows Azure – SQL Azure Introduction

Posted by JP on November 13, 2011

Remembering from the storage type article, the 3 types of storage were:

  • Local Storage
  • Windows Azure Storage
  • SQL Azure Storage

In this article we are emphasizing on SQL Azure Storage. The important features are listed and we will be creating an database in the online storage account.

The features are mentioned below:

  • SQL Server equivalent in Cloud
  • Built on SQL Server Technologies
  • No installation needed
  • Transact SQL Support
  • ADO.NET and ODBC drivers supported
  • Available anywhere
  • Existing .Net Database skills are reusable here
  • Easier Scalability
  • Highly Fault Tolerant
  • Pricing involved

clip_image002

Note: The Sql Azure can be accessible from Azure applications as well as other applications like ASP.NET, Console Applications. Also the complete SQL Features are not available in SQL Azure. You can find more info here.

Features Explained

We can relate everything in our windows/asp.net world to azure world. The Sql Azure is the Sql Server equivalent in the cloud. There will be a server, database and connectivity using ADO.NET which we are familiar with. (Creation of server and database are discussed in the upcoming article.)

Connecting using ADO.NET

We can use the existing ADO.NET SQL Server classes to connect and manipulate the SQL Azure database.

Operation

Class

Connection

SqlConnection

Command

SqlCommand

Read

SqlDataReaders

Advantages

The SQL Azure database will be replicated on multiple servers and is more fault tolerant than the SQL Server database.

The automatic switching of server in case of server fault makes the SQL Azure have increased availability.

SQL Azure helps the database management easier by freeing up from:

  • Server Operating System Setup Overheads
  • SQL Server Setup Overheads
  • Scalability Setup Overheads

In the pre SQL Azure world, we need to setup the database server with the appropriate operating system, install the sql server, configure the server etc. But in SQL Azure we are free from all these overheads. This makes concentrating in the core aspects of the application.

In the SQL Azure, scalability is an easier task otherwise in Sql Server we have to deal with replication server configuration, principal and mirror server configuration etc. We can scale up and down based on the requirements of the application.

More information can be found here
Types of Database

In the SQL Azure there are two types of databases:

  • Web Edition
  • Business Edition

The Web Edition is the basic edition and right choice for a typical web application. This Database provides up to 5 GB of T-SQL based relational database. This edition is best suited for Web application, and Departmental custom apps.

The Business Edition SQL Azure DB provides up to 50 GB of T-SQL based relational database. This edition is best suited for Software as a Service applications, custom applications etc.

clip_image004

The billing also varies based on the edition. In our examples we will be using the Web Edition only.

Posted in C# | Tagged: , , , , , | Leave a Comment »

Windows Azure Platform Management Tool (MMC)

Posted by JP on November 13, 2011

In this article, I would like to introduce a tool useful while developing Windows Azure applications.

The Platform Management Tool is a desktop application and it allows the developers to manage:

  • Hosted Services
  • Perform Diagnostics
  • Certificates
  • Storage Services
  • Blob, Table, Queue
  • Command Based Operations

Download

The latest tool can be downloaded from codeplex.com

Source Code

The source code of the tool is available here.

Using the MMC

Download the tool and execute the setup.

clip_image002

The files will be extracted to the specified folder. Now execute the StartHere.cmd file from location C:\WindowsAzure\MMC. The following wizard will appear.

clip_image004

Click Next to continue and it will do a scan of the system.

clip_image006

If all the required software found the status will be green. Click Next to continue and the installation will be started. A command window will be prompted like below:

clip_image008

Now press any key to continue and within few seconds the installation will be completed. The following screen will be visible.

clip_image010

The MMC tool will be automatically started as below. (You can also launch it from the desktop icon)

clip_image012

Expand the Windows Azure Management item and click on the Service Management item.

clip_image014

Enter your Subscription ID in the appearing dialog. (Subscription ID can be retrieved from the Windows Azure Portal)

Then Click on the API Certificate button. Your certificate will be automatically populated in the appearing dialog as shown below: (If no certificates are listed, then you need to create and add certificate into your azure account. This is explained the previous article)

clip_image016

Select the certificate and click Ok.

Now click Connect from the right pane as shown below:

clip_image018

Wait for a while and you will be connected to the service. Now you can explore the hosted services, storage services by clicking the respective item from the left pane.

The figure below shows the content of Blob Containers.

clip_image020

Summary

In this article the MMC tool was introduced. It should be noted that the Storage functionalities of the tool can be achieved with the Server Explorer of Visual Studio.

Posted in C# | Tagged: , , , , | Leave a Comment »