Design an Azure SQL database and connect with C# and ADO.NET

Design an Azure SQL database and connect with C# and ADO.NET

Contributors
Gene Milener  Carl Rabeler  Cam Soper  Kris Crider
Azure SQL Database is a relational database-as-a service (DBaaS) in the Microsoft Cloud (Azure). In this tutorial, you learn how to use the Azure portal and ADO.NET with Visual Studio to:

Create a database in the Azure portal
Set up a server-level firewall rule in the Azure portal
Connect to the database with ADO.NET and Visual Studio
Create tables with ADO.NET
Insert, update, and delete data with ADO.NET
Query data ADO.NET
If you don't have an Azure subscription, create a free account before you begin.

Prerequisites
An installation of Visual Studio Community 2017, Visual Studio Professional 2017, or Visual Studio Enterprise 2017.

Log in to the Azure portal
Log in to the Azure portal.

Create a blank SQL database
An Azure SQL database is created with a defined set of compute and storage resources. The database is created within an Azure resource group and in an Azure SQL Database logical server.

Follow these steps to create a blank SQL database.

Click Create a resource in the upper left-hand corner of the Azure portal.

Select Databases from the New page, and select Create under SQL Database on the New page.

create empty-database

Fill out the SQL Database form with the following information, as shown on the preceding image:

Setting       Suggested value Description 
Database name mySampleDatabase For valid database names, see Database Identifiers.
Subscription Your subscription For details about your subscriptions, see Subscriptions.
Resource group myResourceGroup For valid resource group names, see Naming rules and restrictions.
Select source Blank database Specifies that a blank database should be created.
Click Server to create and configure a new server for your new database. Fill out the New server form with the following information: AZURE ONLINE TRAINING 

Setting       Suggested value Description 
Server name Any globally unique name For valid server names, see Naming rules and restrictions.
Server admin login Any valid name For valid login names, see Database Identifiers.
Password Any valid password Your password must have at least 8 characters and must contain characters from three of the following categories: upper case characters, lower case characters, numbers, and non-alphanumeric characters.
Location Any valid location For information about regions, see Azure Regions.
create database-server

Click Select.

Click Pricing tier to specify the service tier, the number of DTUs, and the amount of storage. Explore the options for the amount of DTUs and storage that is available to you for each service tier.

For this tutorial, select the Standard service tier and then use the slider to select 100 DTUs (S3) and 400 GB of storage.

create database-s1

Accept the preview terms to use the Add-on Storage option.

Important

* Storage sizes greater than the amount of included storage are in preview and extra costs apply. For details, see SQL Database pricing.

* In the Premium tier, more than 1 TB of storage is currently available in the following regions: Canada Central, Canada East, France Central, Germany Central, Japan East, Korea Central, South Central US, South East Asia, US East2, West US, US Gov Virginia, and West Europe. See P11-P15 Current Limitations.

After selecting the server tier, the number of DTUs, and the amount of storage, click Apply.

Select a collation for the blank database (for this tutorial, use the default value). For more information about collations, see Collations

Click Create to provision the database. Provisioning takes about a minute and a half to complete.

On the toolbar, click Notifications to monitor the deployment process.

notification

Create a server-level firewall rule
The SQL Database service creates a firewall at the server-level that prevents external applications and tools from connecting to the server or any databases on the server unless a firewall rule is created to open the firewall for specific IP addresses. Follow these steps to create a SQL Database server-level firewall rule for your client's IP address and enable external connectivity through the SQL Database firewall for your IP address only.AZURE ONLINE TRAINING HYDERABAD

Note

SQL Database communicates over port 1433. If you are trying to connect from within a corporate network, outbound traffic over port 1433 may not be allowed by your network's firewall. If so, you cannot connect to your Azure SQL Database server unless your IT department opens port 1433.

After the deployment completes, click SQL databases from the left-hand menu and then click mySampleDatabase on the SQL databases page. The overview page for your database opens, showing you the fully qualified server name (such as mynewserver20170824.database.windows.net) and provides options for further configuration.

Copy this fully qualified server name for use to connect to your server and its databases in subsequent quick starts.

server name

Click Set server firewall on the toolbar. The Firewall settings page for the SQL Database server opens.

server firewall rule

Click Add client IP on the toolbar to add your current IP address to a new firewall rule. A firewall rule can open port 1433 for a single IP address or a range of IP addresses.

Click Save. A server-level firewall rule is created for your current IP address opening port 1433 on the logical server.

Click OK and then close the Firewall settings page.

You can now connect to the SQL Database server and its databases using SQL Server Management Studio or another tool of your choice from this IP address using the server admin account created previously.

Important

By default, access through the SQL Database firewall is enabled for all Azure services. Click OFF on this page to disable for all Azure services.

SQL server connection information
Get the fully qualified server name for your Azure SQL Database server in the Azure portal. You use the fully qualified server name to connect to your server using SQL Server Management Studio.

Log in to the Azure portal.
Select SQL Databases from the left-hand menu, and click your database on the SQL databases page.
In the Essentials pane in the Azure portal page for your database, locate and then copy the Server name.

connection information

Get more information on IOS from OnlineITGuru AZURE ONLINE COURSE


C# program example
The next sections of this article present a C# program that uses ADO.NET to send Transact-SQL statements to the SQL database. The C# program performs the following actions:

Connects to our SQL database using ADO.NET.
Creates tables.
Populates the tables with data, by issuing T-SQL INSERT statements.
Updates data by use of a join.
Deletes data by use of a join.
Selects data rows by use of a join.
Closes the connection (which drops any temporary tables from tempdb).
The C# program contains:

C# code to connect to the database.
Methods that return the T-SQL source code.
Two methods that submit the T-SQL to the database.
To compile and run
This C# program is logically one .cs file. But here the program is physically divided into several code blocks, to make each block easier to see and understand. To compile and run this program, do the following:

Create a C# project in Visual Studio.
The project type should be a console application, from something like the following hierarchy: Templates > Visual C# > Windows Classic Desktop > Console App (.NET Framework).
In the file Program.cs, erase the small starter lines of code.
Into Program.cs, copy and paste each of the following blocks, in the same sequence they are presented here.
In Program.cs, edit the following values in the Main method:

cb.DataSource
cd.UserID
cb.Password
InitialCatalog
Verify that the assembly System.Data.dll is referenced. To verify, expand the References node in the Solution Explorer pane.

To build the program in Visual Studio, click the Build menu.
To run the program from Visual Studio, click the Start button. The report output is displayed in a cmd.exe window.
Note

You have the option of editing the T-SQL to add a leading # to the table names, which creates them as temporary tables in tempdb. This can be useful for demonstration purposes, when no test database is available. Temporary tables are deleted automatically when the connection closes. Any REFERENCES for foreign keys are not enforced for temporary tables.


C# block 1: Connect by using ADO.NET
Next
C#

Copy
using System;
using System.Data.SqlClient;   // System.Data.dll
//using System.Data;           // For:  SqlDbType , ParameterDirection

namespace csharp_db_test
{
   class Program
   {
      static void Main(string[] args)
      {
         try
         {
            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();

               Submit_Tsql_NonQuery(connection, "2 - Create-Tables",
                  Build_2_Tsql_CreateTables());

               Submit_Tsql_NonQuery(connection, "3 - Inserts",
                  Build_3_Tsql_Inserts());

               Submit_Tsql_NonQuery(connection, "4 - Update-Join",
                  Build_4_Tsql_UpdateJoin(),
                  "@csharpParmDepartmentName", "Accounting");

               Submit_Tsql_NonQuery(connection, "5 - Delete-Join",
                  Build_5_Tsql_DeleteJoin(),
                  "@csharpParmDepartmentName", "Legal");

               Submit_6_Tsql_SelectEmployees(connection);
            }
         }
         catch (SqlException e)
         {
            Console.WriteLine(e.ToString());
         }
         Console.WriteLine("View the report output here, then press any key to end the program...");
         Console.ReadKey();
      }

C# block 2: T-SQL to create tables
Previous   /   Next
C#

Copy
      static string Build_2_Tsql_CreateTables()
      {
         return @"
DROP TABLE IF EXISTS tabEmployee;
DROP TABLE IF EXISTS tabDepartment;  -- Drop parent table last.


CREATE TABLE tabDepartment
(
   DepartmentCode  nchar(4)          not null
      PRIMARY KEY,
   DepartmentName  nvarchar(128)     not null
);

CREATE TABLE tabEmployee
(
   EmployeeGuid    uniqueIdentifier  not null  default NewId()
      PRIMARY KEY,
   EmployeeName    nvarchar(128)     not null,
   EmployeeLevel   int               not null,
   DepartmentCode  nchar(4)              null
      REFERENCES tabDepartment (DepartmentCode)  -- (REFERENCES would be disallowed on temporary tables.)
);
";
      }
Entity Relationship Diagram (ERD)
The preceding CREATE TABLE statements involve the REFERENCES keyword to create a foreign key (FK) relationship between two tables. If you are using tempdb, comment out the --REFERENCES keyword using a pair of leading dashes.

Next is an ERD that displays the relationship between the two tables. The values in the #tabEmployee.DepartmentCode child column are limited to the values present in the #tabDepartment.Department parent column.

ERD showing foreign key


C# block 3: T-SQL to insert data
Previous   /   Next
C#

Copy
      static string Build_3_Tsql_Inserts()
      {
         return @"
-- The company has these departments.
INSERT INTO tabDepartment
   (DepartmentCode, DepartmentName)
      VALUES
   ('acct', 'Accounting'),
   ('hres', 'Human Resources'),
   ('legl', 'Legal');

-- The company has these employees, each in one department.
INSERT INTO tabEmployee
   (EmployeeName, EmployeeLevel, DepartmentCode)
      VALUES
   ('Alison'  , 19, 'acct'),
   ('Barbara' , 17, 'hres'),
   ('Carol'   , 21, 'acct'),
   ('Deborah' , 24, 'legl'),
   ('Elle'    , 15, null);
";
      }

C# block 4: T-SQL to update-join
Previous   /   Next
C#

Copy
      static string Build_4_Tsql_UpdateJoin()
      {
         return @"
DECLARE @DName1  nvarchar(128) = @csharpParmDepartmentName;  --'Accounting';


-- Promote everyone in one department (see @parm...).
UPDATE empl
   SET
      empl.EmployeeLevel += 1
   FROM
      tabEmployee   as empl
      INNER JOIN
      tabDepartment as dept ON dept.DepartmentCode = empl.DepartmentCode
   WHERE
      dept.DepartmentName = @DName1;
";
      }

C# block 5: T-SQL to delete-join
Previous   /   Next
C#

Copy
      static string Build_5_Tsql_DeleteJoin()
      {
         return @"
DECLARE @DName2  nvarchar(128);
SET @DName2 = @csharpParmDepartmentName;  --'Legal';


-- Right size the Legal department.
DELETE empl
   FROM
      tabEmployee   as empl
      INNER JOIN
      tabDepartment as dept ON dept.DepartmentCode = empl.DepartmentCode
   WHERE
      dept.DepartmentName = @DName2

-- Disband the Legal department.
DELETE tabDepartment
   WHERE DepartmentName = @DName2;
";
      }

C# block 6: T-SQL to select rows
Previous   /   Next
C#

Copy
      static string Build_6_Tsql_SelectEmployees()
      {
         return @"
-- Look at all the final Employees.
SELECT
      empl.EmployeeGuid,
      empl.EmployeeName,
      empl.EmployeeLevel,
      empl.DepartmentCode,
      dept.DepartmentName
   FROM
      tabEmployee   as empl
      LEFT OUTER JOIN
      tabDepartment as dept ON dept.DepartmentCode = empl.DepartmentCode
   ORDER BY
      EmployeeName;
";
      }

C# block 6b: ExecuteReader
Previous   /   Next
This method is designed to run the T-SQL SELECT statement that is built by the Build_6_Tsql_SelectEmployees method.

C#

Copy
      static void Submit_6_Tsql_SelectEmployees(SqlConnection connection)
      {
         Console.WriteLine();
         Console.WriteLine("=================================");
         Console.WriteLine("Now, SelectEmployees (6)...");

         string tsql = Build_6_Tsql_SelectEmployees();

         using (var command = new SqlCommand(tsql, connection))
         {
            using (SqlDataReader reader = command.ExecuteReader())
            {
               while (reader.Read())
               {
                  Console.WriteLine("{0} , {1} , {2} , {3} , {4}",
                     reader.GetGuid(0),
                     reader.GetString(1),
                     reader.GetInt32(2),
                     (reader.IsDBNull(3)) ? "NULL" : reader.GetString(3),
                     (reader.IsDBNull(4)) ? "NULL" : reader.GetString(4));
               }
            }
         }
      }

C# block 7: ExecuteNonQuery
Previous   /   Next
This method is called for operations that modify the data content of tables without returning any data rows.

C#

Copy
      static void Submit_Tsql_NonQuery(
         SqlConnection connection,
         string tsqlPurpose,
         string tsqlSourceCode,
         string parameterName = null,
         string parameterValue = null
         )
      {
         Console.WriteLine();
         Console.WriteLine("=================================");
         Console.WriteLine("T-SQL to {0}...", tsqlPurpose);

         using (var command = new SqlCommand(tsqlSourceCode, connection))
         {
            if (parameterName != null)
            {
               command.Parameters.AddWithValue(  // Or, use SqlParameter class.
                  parameterName,
                  parameterValue);
            }
            int rowsAffected = command.ExecuteNonQuery();
            Console.WriteLine(rowsAffected + " = rows affected.");
         }
      }
   } // EndOfClass
}

C# block 8: Actual test output to the console
Previous
This section captures the output that the program sent to the console. Only the guid values vary between test runs.

text

Copy
[C:\csharp_db_test\csharp_db_test\bin\Debug\]
>> csharp_db_test.exe

=================================
Now, CreateTables (10)...

=================================
Now, Inserts (20)...

=================================
Now, UpdateJoin (30)...
2 rows affected, by UpdateJoin.

=================================
Now, DeleteJoin (40)...

=================================
Now, SelectEmployees (50)...
0199be49-a2ed-4e35-94b7-e936acf1cd75 , Alison , 20 , acct , Accounting
f0d3d147-64cf-4420-b9f9-76e6e0a32567 , Barbara , 17 , hres , Human Resources
cf4caede-e237-42d2-b61d-72114c7e3afa , Carol , 22 , acct , Accounting
cdde7727-bcfd-4f72-a665-87199c415f8b , Elle , 15 , NULL , NULL

[C:\csharp_db_test\csharp_db_test\bin\Debug\]
>>
Next steps
In this tutorial, you learned basic database tasks such as create a database and tables, load and query data, and restore the database to a previous point in time. You learned how to:

Create a database
Set up a firewall rule
Connect to the database with Visual Studio and C#
Create tables
Insert, update, and delete data
Query data
Advance to the next tutorial to learn about migrating your data and azure online training India