In previous article, we have seen how to create a blank Single Database in Azure SQL. We also have created a schema using SSMS.
In this article, we will see how to perform basic operations using ADO.NET. We will use a console application to demonstrate, but you can use any .NET application (Windows Forms, or WPF or Web Application) and use ADO.NET for connecting with the Azure SQL database.
Create Console Application
If you have already had experience of working with SQL Server and ADO.NET, working with Azure SQL is almost the same. The only part which will change is connection string.
Please follow my previous article to create the database and student management schema.
You can create a .NET Framework console application using Visual Studio. There are two important using statements which you need.
using System;
using System.Data.SqlClient;
using System.Data;
Under main method, you can create instance of Connection String builder as in below code.
- Data Source is the <azure-db-server>.database.windows.net
- UserId is the database user want to use to connect with database
- Password is the password of the user
- Initial Catalog is the name of Azure SQL database
var cb = new SqlConnectionStringBuilder();
cb.DataSource = "your_server.database.windows.net";
cb.UserID = "your_user";
cb.Password = "your_password";
cb.InitialCatalog = "your_database";
Then you can create the SqlConnection object as shown in below code snippet.
var cb = new SqlConnectionStringBuilder();
cb.DataSource = "your_server.database.windows.net";
cb.UserID = "your_user";
cb.Password = "your_password";
cb.InitialCatalog = "your_database";
using (var connection = new SqlConnection(cb.ConnectionString))
{
connection.Open();
Insert();
Update();
Select();
}
Insert
As shown in below snippet, you can create the insert statement and then call ExecuteNonQuery method on command to insert the data.
public static void Insert(SqlConnection connection)
{
var insertStatement = @"INSERT INTO [dbo].[Person] (
[FirstName],[MiddelInitial]
,[LastName],[DateOfBirth]) VALUES
('firstname', 'middlename', 'lastname', GETDATE())";
using (var command = new SqlCommand(insertStatement, connection))
{
int rowsAffected = command.ExecuteNonQuery();
Console.WriteLine(rowsAffected + " = rows affected.");
}
}
Update
As shown in below code, you can form an update statement. If you want you may add the parameters, but then you will have to set parameter values on the command.
public static void Update(SqlConnection connection)
{
var updateStatement = @"UPDATE [dbo].[Person]
SET [FirstName] = 'first'
,[MiddelInitial] = 'middle'
,[LastName] = 'last'
,[DateOfBirth] = GETDATE()
WHERE PersonId = 1;"
using (var command = new SqlCommand(updateStatement, connection))
{
int rowsAffected = command.ExecuteNonQuery();
Console.WriteLine(rowsAffected + " = rows affected.");
}
}
Select
Below code snippet shows example of a select query. Then using SqlDataReader, you can read the data from the returned result set.
public static void Select(SqlConnection connection)
{
var selectStatement = @"SELECT [PersonId]
,[FirstName],[MiddelInitial]
,[LastName],[DateOfBirth]
FROM [dbo].[Person]"
using (var command = new SqlCommand(selectStatement, connection))
{
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
Console.WriteLine("{0} , {1} , {2} , {3} , {4}",
reader.GetInt32(0),
reader.GetString(1),
reader.GetString(2),
reader.GetString(3),
reader.GetDateTime(4));
}
}
}
}
Similarly, you can also use stored procedures to interact with Azure SQL.
I hope you enjoyed this article. Let me know your thoughts.
