Scope
In this short article we will see how to create a SQL database using the services provided by Microsoft Azure cloud platform, and then connect to it via C # code, to perform the normal data insertion and retrieval operations
Prerequisites
In order to replicate what will be shown in the article, you must have:
- An Azure subscription (a credit to test the service is available)
Create a database on Azure
First, we'll visit the site https://portal.azure.com/ . The page will execute a redirect to a page that will allow us to authenticate ourselves the service, via e-mail address and password with which we have signed up. We will see our Azure dashboard, the control panel through which to perform all the operations available on the portal.
You will notice, in the sidebar to the left, the "SQL Database" entry. Click it to access its configuration page. Obviously empty at the start, at the top of the same page is available the button "Add". Press it to start creating our database.
For the following example, we will create a database named TechNet_Test. As you may notice, you need to define some preliminary parameters. First of all, our database will obviously have to reside on a server, and - if we have not already created one - in this section we can initialize a new server, which - as seen in the image below - will be reached at <SERVER>.Database.windows.net, where <SERVER> is the name that we have chosen, with specified credentials. There are different tariff plans for databases, each for performance and appropriate resources. In our case, being this a simple test, we will select the Basic tariff plan, which lets database size up to 2GB. Once you finished specifying the options you want, simply press the "Create" button to proceed with the creation of the database, and its server. Keeping the flag "Add to Dashboard" will create a useful link to the database on the dashboard, which we'll see in the portal opening.
We therefore await the end of the creation of our database. A notice on the top bar of the portal will inform us upon database creation. That's an operation that can last few minutes.
At this point, clicking again on "SQL Database" we will see our TechNet_Test database in the list. Clicking on it will open its parameter window.
We are here particularly interested in the 'Show database connection strings" option, as this command will expose the connection strings to be used in our programs to help refer to the instance / database created earlier. Also note in the toolbar at the top, the button "Tools". In this view, it exposes the command "Open in Visual Studio," which allows to open the server instance in Visual Studio for routine maintenance, to be performed using SQL scripts, which will be upload later.
Edit database with Visual Studio
Using the command "Open in Visual Studio", a further window will open, which allows us to confirm the operation and configure the Azure firewall. This is particularly important for controlling access: if - in fact - the platform hasn't been configured to allow access to a particular address, the various calls to the instance won't be completed successfully.
Click "Configure the firewall", and let's define the rules that concern us. In this case, we define a simple rule called 'Home', which allows access to only IP that we're using right now. Once saved the rule, a few minutes may be required to actually being able of accessing the instance.
At this point, we can click "Open in Visual Studio." We will be prompted for credentials spcified upn the server creation, typing which we will finally have access to our database. At this point, we will see the TechNet_Test database on the left sidebar, and we can - by clicking the right button - perform the add operation of a new table ( "Add New Table"). Then we will notice that the IDE will be prepared to allow the indication of the constituent fields the database through a convenient grid, which will be changed also by changing the script that comes, positioned in the lower window.
As you will notice in the image, it was chosen to create a table of absolute simplicity, for demonstration purposes only. The table "Example" has only two columns, called Id and Nominativo, the first of which is the primary key of the table. Once satisfied with the table, the button "Update" allows sending the script to Azure, on which it will be run, going to actually create our table.
The pop-up above shows us what will run the server side. Pressing the button "Update Database" to begin communications between our client and Azure.
Edit database with Management Studio
For editing our database, it is not necessary nor obligatory to use Visual Studio. For example, you can use Management Studio, if you prefer. In this case, simply start the program, and enter the login information so that the server name is the address of our server on Azure, the user name is in the form <USER> @ <SERVER>, and the password as the one set during server creation on Azure.
Any changes here will be made using the T-SQL syntax from the appropriate menu "New Query"
Access database via C# code
Now let's see some simple code snippet to make a connection to our Azure database. We will use the System.Data.SqlClient namespace, which provides effective classes suitable for this purpose.
Make a connection
We create a new C # WinForms project (any C # project type will be apt as well). The snippet that creates a simple opening and subsequent closing of our database can be represented by the following:
using System;
using System.Data.SqlClient;
using System.Windows.Forms;
namespace WindowsFormsApplication1
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
SqlConnection connessione = new SqlConnection("Server=tcp:XXXXX.database.windows.net,1433;Database=TechNet_Test;User ID=XXXXXX;Password={your_password_here};Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;");
connessione.Open();
connessione.Close();
}
}
}
It's sufficient to declare an object of the SqlConnection class, initializing it with the connection string that Azure calls "ADO.NET" obviously taking care to enter the correct server name, user name, and password. By launching this program, a call to the platform, the opening of TechNet_Test database, and subsequent closure will be carried out.
Insert a record
To insert a record in our table, we will use the SqlCommand class, in conjunction with SqlConnection already highlighted. Given the structure of view table above, we can insert a record in the table Example using a construct like this:
SqlConnection connessione = new SqlConnection("Server=tcp:XXXXX.database.windows.net,1433;Database=TechNet_Test;User ID=XXXXXX;Password={your_password_here};Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;");
connessione.Open();
SqlCommand cmd = new SqlCommand(@"INSERT INTO Esempio (Id, Nominativo)
VALUES (@Id, @Nominativo)", connessione);
cmd.Parameters.Add(new SqlParameter("Id", 1));
cmd.Parameters.Add(new SqlParameter("Nominativo", "MARIO ROSSI"));
cmd.ExecuteNonQuery();
connessione.Close();
Is that the connection open, as usual, and then initialize the variable belonging to the SqlCommand class using a T-SQL statement fits. Note the use of the SqlParameter class to pass to the query in a protected area parameters in order to avoid problems of escaping or SQL injection. The ExecuteNonQuery () method requires that you run the following command without a refund of the return values.
Finally, we end the connection.
Reading the contents of table
We now want to check what has been written on our remote table. We write so that a snippet by opening the connection, perform a selection of table records without any discrimination, and each would bring together the column data into a single string to be displayed by the MessageBox.
We can achieve this functionality as follows:
SqlConnection connessione = new SqlConnection("Server=tcp:XXXXX.database.windows.net,1433;Database=TechNet_Test;User ID=XXXXXX;Password={your_password_here};Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;");
connessione.Open();
SqlCommand cmd = new SqlCommand(@"SELECT * FROM Esempio", connessione);
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
String retVal = dr.GetInt32(0).ToString() + " " + dr.GetString(1);
MessageBox.Show(retVal);
}
}
dr.Close();
connessione.Close();
Still, we open the connection, and declare a command, this time selection. In this case, the command execution certainly will return the values we want to handle. For this reason, we will use the ExecuteReader () method, which returns an object of the SqlDataReader class, useful for navigation of the returned data. First, we verify that the return of records, the structure actually has the lines displayed (property dr.HasRows, Boolean). If so, we execute the Read ()method, which is also Boolean, which returns the value True as long as there are lines to be read, and False when they end. Depending on the return value of this method, we baste a loop, inside which we will treat the amounts that each execution of the method Read () makes available to us.
Note the use of GetInt32 methods first, and then GetString, in composing the retVal string. These methods allow to extract a column value, depending on the ordinal number passed to the method itself. Since, in our example, the first field Id table is an integer, while the second - Nominativo - is a string, respectively, we will use the GetInt32 methods with index zero, and GetString with index one, to read the values correctly.
Composed the string, we issue the screen using a MessageBox. The result in execution will be the following:
Conclusion
In this article we have quickly seen the basics regarding the use of SQL Azure database, in connection with the tools provided by Microsoft as code developed ad hoc. The reader is invited to explore further the potential of the instruments listed to create the solutions they need, also taking advantage of the bibliography to follow for further details of the case.
Bibliography
Other languages
This article is also available in the following localizations:
