MSDN Developer Network

Introduction to Database Integration

Understand the fundamental concepts of connecting your applications to databases. This tutorial covers different database types, connection methods, and the importance of data abstraction layers.

Key Topics:

• Relational vs. NoSQL Databases
• Connection Strings and Drivers
• Data Access Objects (DAOs)
• Object-Relational Mappers (ORMs)

Working with SQL Databases (e.g., SQL Server, PostgreSQL, MySQL)

Master the art of interacting with popular SQL databases. Learn best practices for writing efficient SQL queries, handling transactions, and ensuring data integrity.

Topics Covered:

• Establishing Connections
• Executing Queries (SELECT, INSERT, UPDATE, DELETE)
• Parameterized Queries to Prevent SQL Injection
• Transaction Management
• Schema Design Basics

Example: Connecting to SQL Server using C#

This example demonstrates how to connect to a SQL Server database and retrieve data using ADO.NET.

using System;
using System.Data;
using System.Data.SqlClient;

public class SqlServerExample
{
    private const string ConnectionString = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;";

    public static void GetCustomers()
    {
        using (SqlConnection connection = new SqlConnection(ConnectionString))
        {
            try
            {
                connection.Open();
                Console.WriteLine("Connection opened successfully.");

                string sql = "SELECT CustomerID, CompanyName FROM Customers WHERE City = @City";
                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    command.Parameters.AddWithValue("@City", "London");
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        Console.WriteLine("CustomerID | CompanyName");
                        Console.WriteLine("-----------|------------");
                        while (reader.Read())
                        {
                            Console.WriteLine($"{reader["CustomerID"]} | {reader["CompanyName"]}");
                        }
                    }
                }
            }
            catch (SqlException e)
            {
                Console.WriteLine($"An error occurred: {e.Message}");
            }
        }
    }

    public static void Main(string[] args)
    {
        GetCustomers();
    }
}
                

Integrating with NoSQL Databases (e.g., MongoDB, Cosmos DB)

Explore the world of NoSQL databases and how they differ from relational databases. Learn how to store, query, and manage semi-structured or unstructured data.

Key Concepts:

• Document Databases
• Key-Value Stores
• Working with JSON data
• Scalability and Flexibility

Example: Storing Data in MongoDB

A brief example showing how to insert a document into a MongoDB collection using the official driver.

const { MongoClient } = require('mongodb');

const uri = "mongodb://localhost:27017";
const client = new MongoClient(uri);

async function run() {
  try {
    await client.connect();
    const database = client.db("sampleDatabase");
    const collection = database.collection("users");

    const userDocument = {
      name: "Jane Doe",
      age: 30,
      email: "jane.doe@example.com",
      hobbies: ["reading", "hiking"]
    };

    const result = await collection.insertOne(userDocument);
    console.log(`A document was inserted with the _id: ${result.insertedId}`);
  } finally {
    await client.close();
  }
}
run().catch(console.dir);
                

Using Object-Relational Mappers (ORMs)

Simplify database interactions with ORMs like Entity Framework Core or Hibernate. Learn how to map database tables to application objects and perform operations using familiar programming language constructs.

Benefits of ORMs:

• Reduced boilerplate code
• Improved code readability
• Database independence
• Enhanced security

Example: Using Entity Framework Core

A snippet illustrating how to query data using Entity Framework Core.

// Assuming DbContext and Product entity are defined elsewhere

public class ProductRepository
{
    private readonly AppDbContext _context;

    public ProductRepository(AppDbContext context)
    {
        _context = context;
    }

    public IEnumerable<Product> GetProductsByCategory(string category)
    {
        return _context.Products
                       .Where(p => p.Category == category)
                       .OrderBy(p => p.Name)
                       .ToList();
    }
}