Skip to main content

​
Overview

The Canchas Deportivas application uses SQL Server with integrated Windows authentication. All database connections are managed through the CD_conexion class in the data access layer.

​
Connection String

The application uses the following connection string configuration: 
"Data Source=JONATHANCASTILL\\SQLEXPRESS;Initial Catalog=DB_canchasdeportivas;Integrated Security=True;Encrypt=True;Trust Server Certificate=True"

​
Connection String Parameters

ParameterValueDescription
Data SourceJONATHANCASTILL\SQLEXPRESSSQL Server instance name
Initial CatalogDB_canchasdeportivasDatabase name
Integrated SecurityTrueUse Windows Authentication
EncryptTrueEncrypt the connection
Trust Server CertificateTrueTrust the server certificate

​
CD_conexion Class

The CD_conexion class manages database connectivity for all data access operations. Location: capa_dato/CD_conexion.cs 
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.Data.SqlClient;

namespace capa_dato
{
    public class CD_conexion
    {
        private readonly SqlConnection conexion = new SqlConnection(
            "Data Source=JONATHANCASTILL\\SQLEXPRESS;" +
            "Initial Catalog=DB_canchasdeportivas;" +
            "Integrated Security=True;" +
            "Encrypt=True;" +
            "Trust Server Certificate=True"
        );

        public SqlConnection abrir_conexion()
        {
            if (conexion.State == System.Data.ConnectionState.Closed)
            {
                conexion.Open();
            }
            return conexion;
        }

        public SqlConnection cerrar_conexion() 
        {
            if (conexion.State == System.Data.ConnectionState.Open)
            {
                conexion.Close();
            }
            return conexion;
        }
    }
}

​
Setup Instructions

​
1. Install SQL Server

Download and install SQL Server Express: 
# Download SQL Server 2022 Express
https://www.microsoft.com/en-us/sql-server/sql-server-downloads

​
2. Create the Database

Connect to your SQL Server instance and create the database: 
CREATE DATABASE DB_canchasdeportivas;
GO

USE DB_canchasdeportivas;
GO

​
3. Create Tables

Execute the following SQL scripts to create the required tables: 
-- Create Usuarios table
CREATE TABLE Usuarios (
    IdUsuario INT PRIMARY KEY IDENTITY(1,1),
    Nombre NVARCHAR(100) NOT NULL,
    Clave NVARCHAR(255) NOT NULL,
    Estado BIT NOT NULL DEFAULT 1
);

-- Create Clientes table
CREATE TABLE Clientes (
    IdCliente INT PRIMARY KEY IDENTITY(1,1),
    Nombre NVARCHAR(100) NOT NULL,
    Telefono NVARCHAR(20),
    Correo NVARCHAR(100),
    Estado BIT NOT NULL DEFAULT 1
);

-- Create Canchas table
CREATE TABLE Canchas (
    IdCancha INT PRIMARY KEY IDENTITY(1,1),
    Nombre NVARCHAR(100) NOT NULL,
    Tipo NVARCHAR(50) NOT NULL,
    PrecioPorHora DECIMAL(10,2) NOT NULL,
    Estado NVARCHAR(20) NOT NULL DEFAULT 'Disponible'
);

-- Create Reservas table
CREATE TABLE Reservas (
    IdReserva INT PRIMARY KEY IDENTITY(1,1),
    IdCancha INT NOT NULL,
    IdCliente INT NOT NULL,
    IdUsuario INT NOT NULL,
    FechaReserva DATETIME NOT NULL,
    HoraInicio TIME NOT NULL,
    HoraFin TIME NOT NULL,
    Comentario NVARCHAR(500),
    Estado BIT NOT NULL DEFAULT 1,
    FOREIGN KEY (IdCancha) REFERENCES Canchas(IdCancha),
    FOREIGN KEY (IdCliente) REFERENCES Clientes(IdCliente),
    FOREIGN KEY (IdUsuario) REFERENCES Usuarios(IdUsuario)
);

​
4. Create Stored Procedures

Refer to the Stored Procedures documentation for the complete list of stored procedures to create.

​
5. Update Connection String

Modify the connection string in CD_conexion.cs to match your environment: 
private readonly SqlConnection conexion = new SqlConnection(
    "Data Source=YOUR_SERVER_NAME\\SQLEXPRESS;" +
    "Initial Catalog=DB_canchasdeportivas;" +
    "Integrated Security=True;" +
    "Encrypt=True;" +
    "Trust Server Certificate=True"
);
Replace YOUR_SERVER_NAME with your actual SQL Server instance name.

​
NuGet Packages

The application requires the following NuGet package for SQL Server connectivity: 
<PackageReference Include="Microsoft.Data.SqlClient" Version="5.x.x" />
Install using Package Manager Console: 
Install-Package Microsoft.Data.SqlClient
Or using .NET CLI: 
dotnet add package Microsoft.Data.SqlClient

​
Connection Management

​
Opening a Connection

All data access classes inherit from CD_conexion and use the abrir_conexion() method: 
CD_conexion conexion = new CD_conexion();

using (var conexionAbierta = conexion.abrir_conexion())
{
    // Perform database operations
    using (var comando = new SqlCommand("SP_Name", conexionAbierta))
    {
        comando.CommandType = CommandType.StoredProcedure;
        comando.ExecuteNonQuery();
    }
}

​
Closing a Connection

Connections are automatically closed when using the using statement, but can also be explicitly closed: 
conexion.cerrar_conexion();

​
Environment-Specific Configuration

​
Development Environment

For local development, use the default connection string with your local SQL Server Express instance.

​
Production Environment

For production, consider:
  1. Use Configuration Files: Store connection strings in appsettings.json or web.config
  2. Use SQL Authentication: Instead of Integrated Security, use SQL Server authentication
  3. Use Connection Pooling: Enable connection pooling for better performance
  4. Encrypt Sensitive Data: Store connection strings securely using encryption or Azure Key Vault

​
Example appsettings.json

{
  "ConnectionStrings": {
    "DefaultConnection": "Data Source=prod-server;Initial Catalog=DB_canchasdeportivas;User ID=sa;Password=YourPassword;Encrypt=True;Trust Server Certificate=True"
  }
}

​
Reading from Configuration

using Microsoft.Extensions.Configuration;

public class CD_conexion
{
    private readonly string connectionString;
    private readonly SqlConnection conexion;

    public CD_conexion(IConfiguration configuration)
    {
        connectionString = configuration.GetConnectionString("DefaultConnection");
        conexion = new SqlConnection(connectionString);
    }
}

​
Troubleshooting

​
Connection Failed

If you receive a connection error:
  1. Verify SQL Server is running: 
    Get-Service | Where-Object {$_.Name -like '*SQL*'}
  2. Check if TCP/IP is enabled in SQL Server Configuration Manager
  3. Verify the server name: 
    SELECT @@SERVERNAME;

​
Authentication Failed

For Windows Authentication issues:
  1. Ensure your Windows user has access to SQL Server
  2. Check SQL Server Authentication mode (should allow Windows Authentication)
  3. Grant appropriate permissions to your Windows user account

​
Database Not Found

If the database doesn’t exist: 
-- Check existing databases
SELECT name FROM sys.databases;

-- Create if missing
CREATE DATABASE DB_canchasdeportivas;

​
Security Best Practices

  1. Never hardcode credentials - Use configuration files or environment variables
  2. Use least privilege - Grant only necessary permissions to database users
  3. Enable encryption - Always use Encrypt=True in production
  4. Use parameterized queries - Prevent SQL injection (handled by stored procedures)
  5. Regular backups - Implement automated database backup strategies
  6. Monitor connections - Track open connections and potential leaks

Build docs developers (and LLMs) love

Get started for freeTalk to us