Working With dotConnect for SQL Server in ASP.NET Core

dotConnect for SQL Server is a fast ORM for SQL Server from Devart that is built on top of ADO.NET and provides you an opportunity to connect to SQL Server databases from .NET or .NET Core applications. dotConnect for SqlServer, earlier known as SQLDirect.NET, is a fast, scalable data access framework that can be used in WinForms, ASP.NET, etc. This article talks about the features and benefits of dotConnect for Sql Server and how we can work with it in ASP.NET Core.

Prerequisites

To be able to work with the code examples demonstrated in this article, you should have the following installed in your system:

• Visual Studio 2019 Community Edition
• SQL Server 2019 Developer Edition
• Entity Developer

You can download .NET Core from here.

You can download Visual Studio 2019 from here.

You can download SQL Server 2019 Developer Edition from here.

Create a new ASP.NET Core Web API Project in Visual Studio 2019

Once you’ve installed the necessary software and/or tools needed to work with dotConnect for SqlServer, follow the steps given below to create a new ASP.NET Core Web API project.

1. First off, open the Visual Studio 2019 IDE
2. Next, click "Create a new project" once the IDE has loaded
3. Click "Create a new project"
4. Next, select "ASP.NET Core Web Application"
5. Click the "Next" button
6. Specify the project name and location - where it should be stored in your system
7. Optionally, click the "Place solution and project in the same directory" checkbox.
8. Next, click the "Create" button
9. In the "Create a new ASP.NET Core Web Application" dialog window that is shown next, select "API" as the project template.
10. Select ASP.NET Core 3.1 or later as the version.
11. You should disable the "Configure for HTTPS" and "Enable Docker Support" options by disabling the respective checkboxes.
12. Since we'll not be using authentication in this example, specify authentication as "No Authentication".
13. Finally, click on the "Create" button to finish the process.

dotConnect for SQL Server: Features and Benefits

Some of the key features of dotConnect for SQL Server include the following:

• High performance
• Easy deployment
• Ability to connect to and work with SQL Server from within the managed environment
• Support for both connected and disconnected modes
• Support for the latest versions of SQL Server
• Support for all SQL Server data types
• Excellent data binding capabilities

The advantages of dotConnect for SQL Server are given below:

Optimized Code - The primary goal of dotConnect for SQL Server is to provide a framework which can be used for writing efficient code and build flexible data access applications.

Developer tools and extensions - Developers using dotConnect for SQL Server can take advantage of the rich set of design time tools such as design-time component editors, components specific to SQL Server, etc.

Comprehensive Support for SQL Server Features - Developers can take advantage of dotConnect for SQL Server to leverage the full capabilities of SQL Server.

Comprehensive Support for ADO.NET Features - dotConnect for SQL Server provides comprehensive support for several ADO.NET features. These features include provider model, connection string builder, metadata schemas, asynchronous commands, pooling enhancements, batch updates, database change notification and many more. It also provides support for features which are part of the newer releases of ADO.NET.

• Some of the new features of dotConnect for SQL Server include the following:
• Support for Visual Studio 2019
• Support for Visual Studio 2019 Preview (version 16.8 and beyond)
• Improved rendering of screens

Create the Database

To demonstrate how we can work with dotConnect for SqlServer, we’ll create a new database with a database table named Customers. Note that for the sake of simplicity we’ll use a database with just one tables with simple design in this example.

Launch the SQL Server Management Studio and create a new database called dotConnectDemo. Next, use the following script to create the Customers table inside the dotConnectDemo database.

xxxxxxxxxx

CREATE TABLE [dbo].[Customers](

[CustomerId] [bigint] IDENTITY(1,1) NOT NULL,

[CustomerName] [nvarchar](max) NOT NULL,

[CustomerEmail] [nvarchar](max) NOT NULL,

[CustomerPhone] [nvarchar](max) NOT NULL,

CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED

(

[CustomerId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

Programming dotConnect for SQL Server

In this section, we’ll examine how we can perform CRUD operations with dotConnect for SQL Server using C# as the programming language.

Creating a New Connection

When working with dotConnect for SQL Server you can create a new SQL Connection instance at design time or at run time.

To create a new SQL Connection at design time, open the toolbox, and then locate the SqlConnection component in the dotConnect for SQL Server category. Specify the values for the properties DataSource, database, userId, and Password. The default name of the SqlConnection object created using the designer will be sqlConnection1. You can rename this to specify a name as you desire.

To create a new SQL Connection object at runtime, you should first add references to the Devart.Data.SqlServer.dll and Devart.Data.dll assemblies.

The following code snippet illustrates how you can create SqlConnection at runtime.

xxxxxxxxxx

using Devart.Data.SqlServer;

using Devart.Data;

SqlConnection connection = new SqlConnection();

connection.DataSource = "LAPTOP-DEMO\MSSQLSERVER";

connection.Database = "Test";

connection.UserId = "some user name";

connection.Password = "some password";

connection.MaxPoolSize = 150;

connection.ConnectionTimeout = 30;

Alternatively, you can specify all of the above properties in a single statement as shown below:

SqlConnection connection = new SqlConnection();

connection.ConnectionString = "User Id=sa;Password=some password;

DataSource=LAPTOP-DEMO\MSSQLSERVER";

Reading Data in a Connected Mode

Similar to ADO.NET, you can retrieve data in a connected or disconnected mode using dotConnect for SQL Server. To retrieve data in a connected mode you would need to use a data reader. The following code snippet illustrates how this can be achieved.

xxxxxxxxxx

try

{

Devart.Data.SqlServer.SqlConnection connection = new

Devart.Data.SqlServer.SqlConnection

("UserId=sa;Password=somestrongpwd;DataSource=LAPTOP-

JQ5\MSSQLSERVER;Database=Test");

Devart.Data.SqlServer.SqlCommand command = new

Devart.Data.SqlServer.SqlCommand();

command.CommandText = "Select * From Customers";

command.Connection = connection;

connection.Open();

using (Devart.Data.SqlServer.SqlDataReader reader = command.ExecuteReader())

{

while (reader.Read())

{

for (int i = 0; i < reader.FieldCount; i++)

Console.Write(reader.GetValue(i).ToString() + "\t");

Console.WriteLine();

}

}

}

catch

{

throw;

}

Reading Data in a Disconnected Mode

To retrieve data from the database in a disconnected mode we use DataAdapters, DataSets and DataTables in ADO.NET. When working with dotConnect for SQL Server, you need to write almost the same code and most of the type names are the same. The following code snippet illustrates how you can retrieve data in the disconnected mode.

xxxxxxxxxx

string connectionString = "UserId = sa; Password = somestrongpwd; DataSource = LAPTOP-JQ5\\MSSQLSERVER; Database = Test";

SqlDataTable dataTable = new SqlDataTable("Select * FROM Customers", connectionString);

try

{

dataTable.FetchAll = true;

dataTable.Active = true;

foreach (DataRow row in dataTable.Rows)

{

foreach (DataColumn col in dataTable.Columns)

{

Console.Write(row[col] + "\t");

}

Console.WriteLine();

}

}

finally

{

 dataTable.Active = false;

}

Updating Data

The following code snippet shows how you can modify a record in the connected mode using dotConnect for SQL Server.

xxxxxxxxxx

try

{

string connectionString = "UserId = sa; Password = somestrongpwd;

DataSource = LAPTOP-JQ5\\MSSQLSERVER; Database = Test";

Devart.Data.SqlServer.SqlConnection connection = new Devart.Data.SqlServer.SqlConnection(connectionString);

Devart.Data.SqlServer.SqlCommand command = connection.CreateCommand();

command.CommandText = "Update Customers Set CustomerPhone ='1010101010' Where CustomerId = 1";

return command.ExecuteNonQuery();

}

catch

{

return -1;

}

To modify a record in the disconnected mode, you can take advantage of the following code snippet.

xxxxxxxxxx

string connectionString = "UserId = sa; Password = somestrongpwd; DataSource = LAPTOP-JQ5\\MSSQLSERVER; Database = Test";

SqlDataTable dataTable = new SqlDataTable("Select * FROM Customers", connectionString);

try

{

dataTable.FetchAll = true;

dataTable.Active = true;

dataTable.Rows[1]["CustomerPhone"] = "0101010101";

return dataTable.Update();

}

finally

{

dataTable.Active = false;

}

Inserting Data Into Database

Performing CRUD operations using dotConnect is simple. You can use almost the same syntax you use when working with ADO.NET. To insert data into the database using dotConnect for SQL Server, you need to take advantage of the types available in the Devart.Data.SqlServer namespace.

The following code snippet illustrates how you can insert data to the Customers table of the Test database.

xxxxxxxxxx

Devart.Data.SqlServer.SqlConnection connection = new Devart.Data.SqlServer.SqlConnection("User Id=sa;Password=somestrongpwd;DataSource=LAPTOP-JQ5\MSSQLSERVER;Database=Test");

SqlCommand command = new SqlCommand();

command.CommandText = "INSERT INTO Customers (CustomerName, CustomerEmail, CustomerPhone) VALUES ('Mike','[email protected]','1234567890')";

command.Connection = connection;

connection.Open();

try

{

return command.ExecuteNonQuery();

}

catch

{

return -1;

}

Refer to the preceding code snippet. Note the usage of the Devart.Data.SqlServer.SqlConnection class to create a connection instance. Once the connection instance is created, a SqlCommand instance is created and the CommandText and Connection properties assigned values as appropriate. The ExecuteNonQuery method is then called on the SqlCommand instance. The try – catch blocks are used for handling runtime exceptions.

Summary

dotConnect for SQL Server is available in two editions, i.e., Standard and Professional. While the former represents a full-featured ADO.NET data provider, the latter provides more classes, tools, and integration capabilities.