SQL Database

• The SQL database is composed of 2 resources

• SQL server: the machine that hosts the DBs. Has an unique hostname

• SQL database: db itself

Connectivity

• Connection:
• The db server name must be unique
• The public endpoint must be enabled in order to connect to the db from outside
• The client IP addresses must be added to allow firewall access
• For dotnet application, the nuget package System.Data.SqlClient is used

CREATE TABLE Course
(
  CourseID int,
  CourseName varchar(1000),
  Rating numeric(2,1)
)

INSERT INTO Course(CourseID,CourseName,Rating) VALUES(1, 'AZ-204 Developing Azure solutions', 4.5)
INSERT INTO Course(CourseID,CourseName,Rating) VALUES(1, 'AZ-303 Architecting Azure solutions', 4.6)
INSERT INTO Course(CourseID,CourseName,Rating) VALUES(1, 'DP-203 Azure Data Engineer', 4.7)

SELECT * FROM Course

// GET records
List<Course> _lst = new List<Course>();
string _connection_string = "Server=tcp:hvitoi.database.windows.net,1433;Initial Catalog=appdb;Persist Security Info=False;User ID=demousr;Password={your-pass};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;";
string _statement = "SELECT CourseID,CourseName,rating from Course";

SqlConnection _connection = new SqlConnection(_connection_string);
_connection.Open();
SqlCommand _sqlcommand = new SqlCommand(_statement, _connection);

using (SqlDataReader _reader = _sqlcommand.ExecuteReader())
{
  while (_reader.Read())
  {
    Course _course = new Course()
    {
      CourseID = _reader.GetInt32(0),
      CourseName = _reader.GetString(1),
      Rating = _reader.GetDecimal(2)
    };

    _lst.Add(_course)
  }
  _connection.Close();
  return _lst;
}

// POST records to a table
string _connection_string = "Server=tcp:hvitoi.database.windows.net,1433;Initial Catalog=appdb;Persist Security Info=False;User ID=demousr;Password={your-pass};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;";
string _statement = "INSERT INTO Course(CourseID,CourseName,rating) VALUES(@param1,@param2,@param3)";

SqlConnection _connection = new SqlConnection(_connection_string);
_connection.Open();

using(SqlCommand _command = new SqlCommand(_statement, _connection))
{
  _command.Parameters.Add("@param1", SqlDbType.Int).Value = data.CourseID;
  _command.Parameters.Add("@param2", SqlDbType.VarChar, 1000).Value = data.CourseName;
  _command.Parameters.Add("@param3", SqlDbType.Decimal).Value = data.Rating;
  _command.CommandType = CommandType.Text;
  _command.ExecuteNonQuery();
}

return "Course added";

Connection strings

• Add the connection strings to environment variables
• The connection string is taken from the DB portal -> Settings / Connection strings
• In dotnet, add to appsettings.json
• To add connections strings to the environment variables, add it to the application settings

{
  "ConnectionStrings": {
    "SQLConnection": "Server=tcp:hvitoi.database.windows.net,1433;Initial Catalog=appdb;Persist Security Info=False;User ID=hvitoi;Password={your_password};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"
  }
}

• To get environment variables in dotnet projects:

string _connection_string = Environment.GetEnvironmentVariable("SQLAZURECONNSTR_SQLConnectionString");