I was learning from the Microsoft Learn Module: [Develop and configure an ASP.NET application that queries an Azure SQL database] (https://docs.microsoft.com/en-us/learn/modules/develop-app-that-queries-azure-sql/3-exercise-create-tables-bulk-import-query-data) and it offers some powerful tools for data import and query.

bcp

bcp is a convenient tool to import bulk data from file to sql database.

1
bcp [MSA-DB].dbo.MyTable format nul -c -f mytable.fmt -t , -S msa-anran.database.windows.net -U <username> -P <password>

This generates a format file, mytable.fmt, that defines how data should be mapped to the columns of the table, [MSA-DB].dbo.MyTable.
Note that the square brackets are added because the database name, [MSA-DB], contains the character -, so that the database name could be properly handled.

1
bcp "$DATABASE_NAME.dbo.studyplans" in studyplans.csv -f studyplans.fmt -S "$DATABASE_SERVER.database.windows.net" -U $AZURE_USER -P $AZURE_PASSWORD -F 2

A command like this will import data from studyplans.csv to the table $DATABASE_NAME.dbo.studyplans, following the format defined in studyplans.fmt.

The sqlcmd utility and Cloud Shell

Through Cloud Shell, you could access your Azure resources from anywhere.
To use the sqlcmd utility, go to Cloud Shell and run the following command. Replace with the name of the database server that you created, with the name of your database, and and with your credentials.

1
sqlcmd -S <server>.database.windows.net -d <database> -U <username> -P <password>

If the sign-in command succeeds, you’ll see a 1> prompt. You can enter SQL commands on several lines and then type GO to run them:

1
2
1> SELECT * FROM StudyPlans;  
2> GO

psql command line tool for PostgreSQL

The command-line tool called psql is the PostgreSQL distributed interactive terminal for working with PostgreSQL servers and databases.

The System.Data.SqlClient .NET library

The System.Data.SqlClient library is a collection of types and methods that you can use to connect to a SQL Server database that’s running on-premises or in the cloud on SQL Database.

Azure CLI az webapp command

The Azure CLI az webapp command manages web apps.

In order to build the connection between the course database and the app, the following files are modified, as instructed by the microsoft learn unit: Connect an ASP.NET application to Azure SQL Database:

  • CoursesAndModules.cs
    • The model of the C# object.
    • has getter and constructor for the fields
  • DataAccessController.cs
    • Responsible for the data access logic
    • An SqlConnection object is created which, given the connection string, connects to the database.
    • A method GetAllCoursesAndModules is created, using the connection object to obtain a list of courseAndModules.
  • Index.cshtml.cs
    • This file contains code that the index.cshtml runs when it’s displayed.
    • It contains a class, CoursesAndModulesModel : PageModel. This class overrides onGet method to provide the model needed by the view.
    • It depends on DataAccessController.cs: creates a DataAccessController object, and uses it to obtain the list of courseAndModules within onGet method.

#