Accelerate .NET and SQL development with Amazon CodeWhisperer as your AI coding companion

Introduction

.NET and Structured Query Language (SQL) developers these days are expected to do more with less time, while still maintaining code quality and adhering to best practices. Amazon CodeWhisperer is an artificial intelligence (AI)-powered coding companion that can help developers accelerate and enhance their software development with code generation, reference tracking, security scans, and customization capabilities.

In this blog post, we will explore how you can use Amazon CodeWhisperer to accelerate your .NET and SQL development. CodeWhisperer provides the following benefits:

• Code Generation – Because CodeWhisperer is trained on billions of lines of code, it can generate code suggestions ranging from snippets to full functions in real time, based on your comments and existing code. CodeWhisperer fully supports C# and SQL, so you can work from your favorite integrated development environments (IDEs). (Note: At the time of this writing, CodeWhisperer for Visual Studio is in preview and does not support SQL). Refer to the language & IDE support user guide for a complete list of supported languages and IDEs.
• Reference Tracking – CodeWhisperer provides built-in reference tracking by flagging code suggestions that resemble open-source training data. Such suggestions are annotated with the open-source project’s repository URL, file reference, and license information, so that you can review before deciding whether to incorporate the suggested code.
• Customizability – CodeWhisperer can be customized to generate more precise suggestions by including your organization’s internal APIs, libraries, classes, methods, and best practices. This can save you time that would typically be spent examining previously written internal code and documentation to understand how to incorporate them into your projects. To learn more, visit the CodeWhisperer customization capability page or read the announcement blog post.

Generative AI and Amazon CodeWhisperer

Generative AI on AWS holds great promise for businesses seeking to unlock new opportunities and drive innovation. Amazon Web Services (AWS) offers a robust suite of tools and capabilities that can revolutionize software development, generate valuable insights, and deliver enhanced customer value. AWS is committed to simplifying generative AI for businesses through services like Amazon CodeWhisperer.

CodeWhisperer streamlines .NET developers’ use of AWS services by providing optimized code suggestions for AWS service SDKs such as Amazon Elastic Compute Cloud (Amazon EC2), AWS Lambda, and Amazon Simple Storage Service (Amazon S3).

Set up Amazon CodeWhisperer in Visual Studio Code

AWS Toolkit for Visual Studio Code is an open-source plug-in for Visual Studio Code (VS Code) that makes it easier for developers by providing an integrated experience to create, debug, and deploy applications on AWS. Getting started with CodeWhisperer in VS Code is simple.

Step 1: Install AWS Toolkit for VS Code, which is available as a free download from the VS Code Marketplace.

Step 2: Set up CodeWhisperer authentication. You can authenticate as an individual developer by using an AWS Builder ID (through your organization using AWS IAM Identity Center) or by using AWS Identity and Access Management (IAM).

Step 3: Familiarize yourself with user actions and shortcuts available to you as a user of CodeWhisperer.

CodeWhisperer is also now available for Microsoft Visual Studio in preview. To learn more, visit  Amazon CodeWhisperer new enhancements and Using CodeWhisperer with Visual Studio.

Amazon CodeWhisperer for .NET and SQL development

C# is a general-purpose, object-oriented programming language developed by Microsoft. It is widely used for building .NET applications for desktop, web, and mobile. CodeWhisperer, when producing code suggestions in C#, takes many factors into consideration for code generation, including function and variable names, comments, and the contents of the file. CodeWhisperer will understand your intent and provide suggestions based on the single or multiple line directional prompts and comments. Writing unit tests is an important part of creating software applications, and CodeWhisperer can help you more quickly develop these tests for your C# code.

SQL is a programming language used with relational databases. It allows you to perform operations on databases, such as creating tables, and inserting, querying, and modifying data. CodeWhisperer can generate ANSI SQL, Transact-SQL (T-SQL) and database-specific SQL Data Definition Language (DDL) and Data Manipulation Language (DML) statements. It can also help with providing recommendations on database connectivity and queries in any of the supported programming languages.

Note that in the following examples, the code recommendations provided by CodeWhisperer are generated using artificial intelligence, and therefore, are non-deterministic. The code generated for you may differ from what is shown in this post.

CodeWhisperer for C# examples

CodeWhisperer considers the context of the active file, its dependencies, and provided comments to generate the code suggestions. Including contextual information, such as the appropriate namespaces, will give you better results from CodeWhisperer.

Generating a utility method

Creating a utility, or helper, method is a common use case that developers run into on a daily basis. We will start with a simple example that demonstrates how CodeWhisperer can generate a string extension method for validating an email address using a regular expression.

Type in (or copy and paste) the following comment in your C# source file opened in VS Code, Visual Studio or any of the supported IDEs and press enter:

/*Extension method to validate email addresses using regex*/

CodeWhisperer will then start providing code suggestions, as shown in Figure 1. You can accept the suggestions by pressing the tab key.

namespace CodeWhisperer.Demos.CSharp
{
    public static class UtilityMethods
    {
        /*Extension method to validate email addresses using  regex*/
        public static bool IsValidEmail(this string email)
        {
            return System.Text.RegularExpressions.Regex.IsMatch(email, @"\A(?:[a-z0-9!#$%&'*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&'*+/=?^_`{|}~-]+)*@(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+[a-z0-9](?:[a-z0-9-]*[a-z0-9])?)\Z", System.Text.RegularExpressions.RegexOptions.IgnoreCase);
        }
    }  
}

Figure 1: Extension method for validating regular expressions, generated by CodeWhisperer

Generating classes

CodeWhisperer can accelerate tasks like generating code for classes. You can generate full or partial C# classes for constructs like entities, models, or controllers, complete with constructors, properties and methods.  You just have to provide a comment describing the intent and behavior of the class.

Type in (or copy and paste) the following comment and press enter:

/* Product class to hold name, price and brand
** and a shopping cart class to hold items
** and calculate the total price of the items
*/

CodeWhisperer can provide multiple code suggestions. You can view the suggestions using the arrow keys and accept the one that closely matches your requirement. In this instance, CodeWhisperer auto-generates the Product and ShoppingCart with the properties that are mentioned in the user prompt:

/*Product class to hold name, price and brand
** and a shopping cart class to hold items
** and calculate the total price of the items
*/
public class Product
{
    public string Name { get; set; }
    public double Price { get; set; }
    public string Brand { get; set; }
}

public class ShoppingCart
{
    public List<Product> Items { get; set; } = new List<Product>();

    public double TotalPrice
    {
        get
        {
            double total = 0;
            foreach (var item in Items)
            {
                total += item.Price;
            }
            return total;
        }
    }
}

Generating unit tests

CodeWhisperer can generate unit tests for your code. In the following example, we generate unit tests for the Product and ShoppingCart class (from the previous example) by providing the following user prompt. We add the prompt to the same code file used in the previous Product example so that CodeWhisperer uses the context of the file to suggest relevant code.

Type in (or copy and paste) the following comment and press enter:

/*Unit test class to test the functionality of the Product and ShoppingCart classes*/

It generates the following code:

public class StudentTest
{
    [Fact]
    public void Test1()
    {
        var product = new Product();
        product.Name = "iPhone";
        product.Price = 1000;
        product.Brand = "Apple";

        var cart = new ShoppingCart();
        cart.Items.Add(product);

        Assert.Equal(1000, cart.TotalPrice);
    }
}

Generating ASP.NET Core middleware code

ASP.NET Core Middleware are software components for handling HTTP web requests and responses. ASP.NET ships with many built-in middleware components that can be plugged into the pipeline, depending on the use case. You can write custom middleware for custom scenarios not handled by the built-in middleware. Following is an example for a custom middleware component.

Type in (or copy and paste) the following comment and press enter:

/*Add middleware to the pipeline to check for a valid API key in the request header*/

For this prompt, CodeWhisperer generates middleware code that returns an HTTP 401 (unauthorized) response status code when an API key is not passed in a request header.

The following code is generated:

var builder = WebApplication.CreateBuilder(args);
builder.Services.AddControllers();
builder.Services.AddEndpointsApiExplorer();
var app = builder.Build();

/*Add middleware to the pipeline to check for a valid API key in the request header*/
app.Use(async (context, next) =>
{
    if (context.Request.Headers.TryGetValue("X-API-KEY", out var extractedApiKey))
    {
        if (extractedApiKey.ToString() == "my-api-key")
        {
            await next();
        }
        else
        {
            context.Response.StatusCode = 401;
            await context.Response.WriteAsync("Unauthorized");
        }
    }
    else
    {
        context.Response.StatusCode = 401;
        await context.Response.WriteAsync("Unauthorized");
    }
});

app.UseHttpsRedirection();
app.UseAuthorization();
app.MapControllers();
app.Run();

Calling AWS services

Next, we will illustrate an example using Amazon Comprehend, a natural-language processing (NLP) service that uses machine learning to uncover valuable insights and connections in text.

Providing additional contextual information helps CodeWhisperer generate even more relevant code. For this example, we import the related namespaces, Amazon.Comprehend and Amazon.Comprehend.Model, from the AWS SDK for .NET into the code file.

Type in (or copy and paste) the following prompt and press enter:

using System;
using System.Threading.Tasks;
using Amazon.Comprehend;
using Amazon.Comprehend.Model;
/* Create a class ComprehendService that generates a client and has a async method 
** DetectSentiment. The method should accept a text as input and 
** returns the sentiment
*/

The following code is generated:

using System;
using System.Threading.Tasks;
using Amazon.Comprehend;
using Amazon.Comprehend.Model;

/* Create a class ComprehendService that generates a client and has a async method 
** DetectSentiment. The method should accept a text as input and 
** returns the sentiment
*/
namespace ComprehendService
{
    public class ComprehendService
    {
        private readonly AmazonComprehendClient _client;
        public ComprehendService()
        {
            _client = new AmazonComprehendClient();
        }

        public async Task<DetectSentimentResponse> DetectSentiment(string text)
        {
            var request = new DetectSentimentRequest
            {
                Text = text,
                LanguageCode = "en"
            };
            return await _client.DetectSentimentAsync(request);
        }
    }
}

Generating code for AWS CDK .NET projects

CodeWhisperer can help accelerate the development of AWS Code Development Kit (AWS CDK) for .NET projects. You can generate C# code to model infrastructure by providing simple descriptions as comments. Here is an example of generating code to create an Amazon Simple Queue Service (Amazon SQS) queue, an Amazon Simple Notification Service (Amazon SNS) topic, and a subscription of the queue to the topic.

Type in (or copy and paste) the following user prompt (indicated by the comment block):

using Amazon.CDK;
using Amazon.CDK.AWS.SQS;
using Amazon.CDK.AWS.SNS;
using Amazon.CDK.AWS.SNS.Subscriptions;
using Constructs;
namespace CdkQueue
{
    public class CdkQueueStack : Stack
    {
        internal CdkQueueStack(Construct scope, string id, IStackProps props = null) : base(scope, id, props)
        {
            /* Create a SQS Queue, and a SNS Topic and subscribe the queue 
            ** to the topic */
        }
    }
}

The following code is generated:

using Amazon.CDK;
using Amazon.CDK.AWS.SQS;
using Amazon.CDK.AWS.SNS;
using Amazon.CDK.AWS.SNS.Subscriptions;
using Constructs;
namespace CdkQueue
{
    public class CdkQueueStack : Stack
    {
        internal CdkQueueStack(Construct scope, string id, IStackProps props = null) : base(scope, id, props)
        {
            /* Create a SQS Queue, and a SNS Topic and subscribe the queue 
            ** to the topic */
            var queue = new Queue(this, "CdkQueue", new QueueProps
            {
                VisibilityTimeout = Duration.Seconds(300)
            });
            var topic = new Topic(this, "CdkTopic");
            topic.AddSubscription(new SqsSubscription(queue));
        }
    }
}

Calling 3^rd-party libraries

CodeWhisperer can help integrate 3^rd-party libraries and packages to your existing code base without spending hours reading the official documentation for the library.

You can use CodeWhisperer to integrate Polly, a popular open-source library for handling HTTP web request failures, into your .NET code. Polly provides an elegant way to handle web request transient errors by performing retries, and defining policies to prevent system degradation during error scenarios. For better code recommendations, the “Polly” namespace can be imported into the code file to give more context.

Type in (or copy and paste) the following prompt and press enter:

/* Implement the Polly library to handle retries  
** The policy should retry up to 3 times with a backoff of 1 second between 
** each retry. The policy should catch any exceptions and return the result
** of the last attempt.
*/

The following code is generated:

using System;
using System.Net.Http;
using System.Threading.Tasks;
using Polly; //.NET library to handle transient errors, retries, and circuit breakers. 

namespace mainclass
{  
    public class CW
    {
        /* Implement the Polly library to handle retries  
        ** The policy should retry up to 3 times with a backoff of 1 second between 
        ** each retry. The policy should catch any exceptions and return the result
        ** of the last attempt.
        */
        public static async Task<string> GetCWAsync(string url)
        {
            var client = new HttpClient();
            var policy = Policy.Handle<Exception>().WaitAndRetryAsync(3, retryAttempt => TimeSpan.FromSeconds(1));
            var result = await policy.ExecuteAsync(async () => await client.GetStringAsync(url));
            return result;
        }
    }
}

CodeWhisperer for SQL examples

CodeWhisperer supports code generation for ANSI SQL. We will review a few examples to demonstrate how you can build SQL scripts and queries using CodeWhisperer.

Generating Data Definition Language (DDL) commands

CodeWhisperer can accelerate SQL development by generating commands to create, modify, or delete your database objects, including tables, indexes, stored procedures, views, cursors, and database users.

Type in (or copy and paste) the following prompt in a sql file to create a database with several tables.

/* Create a Company database with two tables. The table Employee with auto-incrementing 
** ID column and the following columns: first name, last name, address, city, state, zip, phone. 
** Create a table Department with name, location and an id which is auto incrementing */

This generates the following code:

CREATE DATABASE Company;
USE Company;
CREATE TABLE Employee(
    id INT NOT NULL AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    address VARCHAR(50),
    city VARCHAR(50),
    state VARCHAR(50),
    zip INT,
    phone INT,
    PRIMARY KEY(id)
    );
CREATE TABLE Department(
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(50),
    location VARCHAR(50),
    PRIMARY KEY(id)
    );

Figure 2: SQL code generated by CodeWhisperer defining a database and several tables

Altering table schemas and adding constraints

In the same IDE window that generated the previous example, type in (or copy and paste) the following user prompt to add a new column to the Employee table and create a foreign key reference to the Department table:

/*Add departmentid column to Employee table and add foreign key reference to Department table.*/

This generates the following code:

/*Add departmentid column to Employee table.
**and add foreign key reference to Department table.*/
ALTER TABLE Employee
ADD departmentid INT;
ALTER TABLE Employee
ADD FOREIGN KEY (departmentid) REFERENCES Department(id);

Creating stored procedures

Enter the following prompt in the same ID window to generate a stored procedure that uses the already created structures:

/*Stored Procedure that gets all employees that directly or indirectly report 
** to a given employee using recursive CTE*/

This generates the following code:

/* Stored Procedure that gets all employees that directly or 
** indirectly report to a given employee using recursive CTE*/
DELIMITER $$
CREATE PROCEDURE GetEmployee(IN employeeID INT)
BEGIN
    WITH RECURSIVE cte AS (
        SELECT id, first_name, last_name, DepartmentID
        FROM Employee
        WHERE id = employeeID
        UNION ALL
        SELECT e.id, e.first_name, e.last_name, e.DepartmentID
        FROM Employee e
        JOIN cte ON cte.DepartmentID = e.DepartmentID
    )
    SELECT * FROM cte;
    END$$
DELIMITER ;

Generating Data Manipulation Language (DML) commands

You can use CodeWhisperer to generate complex queries for retrieving, manipulating, and persisting data. We will review several examples with user prompts and the corresponding generated SQL.

Type in (or copy and paste) the following user prompt and press enter.

/*Query to get the list of all employees belonging to a department having less than 2 employees*/

The following SQL is generated in response to the prompt. It includes a correlated subquery, aggregate functions, and several joins.

/*Query to get the list of all employees belonging to a department having less than 2 employees*/
SELECT T1.first_name,T1.last_name,T2.name
FROM Employee AS T1
JOIN Department AS T2
ON T1.department_id = T2.id
WHERE T2.id IN
(SELECT T2.id
FROM Employee AS T1
JOIN Department AS T2
ON T1.department_id = T2.id
GROUP BY T2.id
HAVING COUNT(T1.id) < 2);

To create a temporary table with data, type in (or copy and paste) the following prompt:

/*Create a temp table and insert all employees belonging to department named IT.*/

It generates code using a temporary table to store intermediate data for later use:

/*Create a temp table and insert all employees belonging to department named IT.*/
CREATE TEMPORARY TABLE IT_Employees AS
SELECT * FROM Employee
INNER JOIN Department
ON Employee.departmentid = Department.id
WHERE Department.name = 'IT';

Cleanup

If you installed the AWS Toolkit for VS Code for these examples, you can remove it if you no longer need it. Navigate to the extensions item in VS Code and enter “AWS toolkit” in the search box. Right-click on “AWS Toolkit” and choose uninstall.

Conclusion

In this blog post, you have learned about using Amazon CodeWhisperer, a generative AI service on AWS. You have set up CodeWhisperer on VS Code and practiced several ways to generate C# and SQL code. By inferring natural language comments and contextual information, CodeWhisperer provides code snippets to accelerate your development. With CodeWhisperer, you can use keyboard shortcuts to get a new recommendation if the first one doesn’t meet your needs.

Try out Amazon CodeWhisperer on your coding projects today.

AWS has significantly more services, and more features within those services, than any other cloud provider, making it faster, easier, and more cost effective to move your existing applications to the cloud and build nearly anything you can imagine. Give your Microsoft applications the infrastructure they need to drive the business outcomes you want. Visit our .NET on AWS and AWS Database blogs for additional guidance and options for your Microsoft workloads. Contact us to start your migration and modernization journey today.