🔗

Connecting to SQL Server with Dapper

Oct 28, 2024

C# Data Access Series: Connecting to a SQL Server Database

Introduction

  • Presenter: Tim Corey
  • Purpose: Simplify the process of connecting to a SQL Server database.
  • Key Concept: Data access should become easy and not a concern in application development.

Options for Connecting to SQL Server

  • Direct ADO.NET

    • Direct connection using C#.
    • Foundational but complicated; involves multiple configuration pieces.
  • Entity Framework (EF)

    • ORM that simplifies database connection and manipulation.
    • Considered "black box" due to its complexity and potential for issues.
    • Tim Corey prefers to avoid EF due to experiences with debugging issues.
  • Dapper

    • A lightweight ORM developed by the Stack Overflow team.
    • Provides a good balance of simplicity and control.
    • Recommended by Tim due to its performance and ease of use.

Setting Up the Project

  1. Creating a New Project

    • Type: Windows Forms Application
    • Solution Name: SQL Data Access Demo
    • Form Name: Dashboard
  2. Database Structure

    • Database Name: Sample
    • Table: dbo.people
      • Columns: ID, First Name, Last Name, Email Address, Phone Number
    • Stored Procedures:
      • people_get_by_last_name
      • people_insert

Creating the Data Model

  • Create a class named Person to represent the data model.
  • Properties:
    • int Id
    • string FirstName
    • string LastName
    • string EmailAddress
    • string PhoneNumber

Connection String Helper

  • Purpose: To simplify retrieval of connection strings from app.config.
  • Implement a helper class with a static method to fetch connection strings.
  • Use System.Configuration namespace for connection management.

Setting Up the Connection String

  • Add connection string to app.config:
    • Example: Server=. Database=SampleDB; Trusted_Connection=True; ProviderName=System.Data.SqlClient;
  • Use ConnectionStrings.com for easy generation of connection strings.

Creating the UI Controls

  • Add controls:
    • ListBox for displaying results.
    • TextBox for inputting last name.
    • Button for search functionality.

Data Access Logic

  1. Data Access Class: Create DataAccess class with methods for data retrieval and insertion.
  2. Get People Method:
    • Fetch data from SQL Server using Dapper.
    • Use a stored procedure for security and efficiency.
    • Return results as a list of Person objects.

Implementing the Search Functionality

  • Populate the ListBox with retrieved data using data binding.
  • Handle data updates by refreshing the data source after queries.

Inserting Data into SQL Server

  1. UI Elements: Create input fields for First Name, Last Name, Email Address, and Phone Number.
  2. Insert Method: Implement an insert method in DataAccess to add new records.
    • Use a stored procedure to handle the insert logic.

Conclusion

  • Emphasize simplicity of accessing data with Dapper versus ADO.NET and Entity Framework.
  • Encourage viewers to avoid raw SQL due to security risks (SQL injection).
  • Promote best practices such as utilizing stored procedures.

Additional Resources

  • Tim Corey’s blog for source code and additional information: imtimcorey.com
  • Mention of a comprehensive SQL course available on the blog.

Call to Action

  • Encourage viewers to subscribe to the mailing list for updates, discounts, and personalized responses.