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.