C# SQL Connection Tutorial: Dapper + SQL Server Database Explained

Project Setup & Cleanup

Tim begins by reviewing the solution structure and confirming the changes made in the previous lesson. In Program.cs, he explains that he added code to connect to both SQL Server and text file databases, and changed the startup form to the “Create Prize” form so he can test the database connection immediately.

He then cleans up the project structure by creating two folders:

• Models

• DataAccess

He notes that this doesn’t affect the source code functionality, but it helps with organization. When you return to a project later, clean structure and consistent naming makes it much easier to maintain and expand.

Why Dapper?

Tim explains that he will use Dapper as the ORM (Object-Relational Mapper) for SQL connections. Dapper sits between raw SQL commands and higher-level ORMs like Entity Framework.

Tim prefers Dapper because it is:

• Almost as fast as raw ADO.NET

• Simpler than Entity Framework

• Avoids unnecessary complexity and heavy boilerplate code

He mentions that Entity Framework can be slower and heavier, especially for small applications. For his project, Dapper provides the right balance between performance and simplicity.

Installing Dapper

Tim demonstrates installing Dapper through NuGet Package Manager in Visual Studio. After installation, Dapper appears in the project references, and the SQL connection code becomes clean and readable.

Adding the Connection String

To use Dapper, you need a connection string. Tim adds it to the app.config file of the UI project, not the library project, because the library is a DLL and doesn’t have its own configuration.

The connection string includes:

• Data Source (server name or machine domain name)

• Initial Catalog (database name)

• Integrated Security (Windows Authentication)

• Connection Timeout

• User ID and Password (if not using Windows Authentication)

In Tim’s example, he uses trusted connection, meaning Windows authentication:

Data Source=SQL2016;Initial Catalog=Tournaments;Integrated Security=True;

He emphasizes that this avoids storing SQL Server user credentials in the file.

GlobalConfig: Getting Connection String

To retrieve the connection string, Tim adds a method in GlobalConfig:

public static string GetConnectionString(string name)
{
    return ConfigurationManager.ConnectionStrings[name].ConnectionString;
}

This method pulls the connection string from the config file and returns it. Tim also adds the required System.Configuration reference.

Creating the SQL Connection

Tim removes the placeholder code and creates a real SqlConnection using:

using (IDbConnection connection = new SqlConnection(
    GlobalConfig.GetConnectionString("tournaments")))
{
    // SQL logic here
}

He uses an IDbConnection interface so he can swap database types (SQL or text file) without changing the core code.

The using block is important because it ensures the connection is automatically closed when the code exits the block. This avoids open connections that can exhaust the connection pool or cause errors on the database server.

Creating the Stored Procedure

Instead of writing raw SQL inside C#, Tim uses a stored procedure. This keeps SQL logic in the database and reduces risks like SQL injection.

He creates a stored procedure named:

dbo.SP_Prizes_Insert

This procedure takes parameters matching the prize table:

• @PlaceNumber

• @PlaceName

• @PrizeAmount

• @PrizePercentage

• @ID (output)

The output parameter returns the newly generated ID using:

SELECT @ID = SCOPE_IDENTITY()

This ensures the new record ID is returned to the C# app.

Dynamic Parameters in Dapper

Back in C#, Tim uses Dapper’s DynamicParameters to send parameters to the stored procedure:

var p = new DynamicParameters();
p.Add("@PlaceNumber", model.PlaceNumber);
p.Add("@PlaceName", model.PlaceName);
p.Add("@PrizeAmount", model.PrizeAmount);
p.Add("@PrizePercentage", model.PrizePercentage);
p.Add("@ID", 0, dbType: DbType.Int32, direction: ParameterDirection.Output);

The ID is marked as Output, and Dapper handles the parameter mapping.

Executing the Stored Procedure

He executes the stored procedure:

connection.Execute("dbo.SP_Prizes_Insert", p, commandType: CommandType.StoredProcedure);

Execute is used because it performs an insert and does not return rows like a SELECT statement.

After execution, the code retrieves the ID:

model.ID = p.Get<int>("@ID");
return model;

Testing & Results

Tim runs the application, fills the form, and clicks Create Prize. The data is inserted into the SQL Server database successfully. He tests both:

• Prize Amount

• Prize Percentage

Both work, proving the database connection and stored procedure function correctly.

Design Challenge: Multiple Data Connectors (1:01:50)

Tim highlights a design issue: using both SQL and text file connectors simultaneously causes inconsistent IDs.

So he changes the design so only one connector runs at a time. He adds an enum:

DatabaseType
{
    SQL,
    TextFile
}

This ensures the app always uses a consistent database type, and prevents mixing database data from different sources.

Conclusion

Tim Corey’s Lesson 10 shows a real-world approach to connecting a C# application to SQL Server using Dapper. He covers:

• Adding a connection string

• Using SqlConnection

• Safely opening and closing connections

• Using stored procedures

• Avoiding SQL injection

• Proper architecture for multiple data sources

If you want a clean, fast, and maintainable way to connect C# to a SQL Server database, this lesson is a perfect guide.