Connecting to a database in ASP.NET Web API is a common requirement for applications that need to store, retrieve, and manipulate data. This guide will explain how to connect to a database using Entity Framework, a popular Object-Relational Mapping (ORM) framework, along with sample code to demonstrate the process.

Step 1: Install Entity Framework

To get started, you need to install Entity Framework in your ASP.NET Web API project. You can do this using the NuGet Package Manager. Open the Package Manager Console and run the following command:

        
        Install-Package EntityFramework
            

Step 2: Create a Database Context

The database context is a class that derives from DbContext and represents a session with the database. It is responsible for querying and saving data. Below is an example of a simple database context for a product management application:

        
        using System.Data.Entity;

        public class ProductContext : DbContext
        {
            public ProductContext() : base("name=ProductDb") // Connection string name
            {
            }

            public DbSet<Product> Products { get; set; } // DbSet for products
        }

        public class Product
        {
            public int Id { get; set; }
            public string Name { get; set; }
            public decimal Price { get; set; }
        }
            

Step 3: Configure the Connection String

You need to define a connection string in the Web.config file of your ASP.NET Web API project. This connection string specifies how to connect to your database. Below is an example of a connection string for a SQL Server database:

        
        <connectionStrings>
            <add name="ProductDb" 
                 connectionString="Server=YOUR_SERVER_NAME;Database=YOUR_DATABASE_NAME;Trusted_Connection=True;" 
                 providerName="System.Data.SqlClient" />
        </connectionStrings>
            

Replace YOUR_SERVER_NAME and YOUR_DATABASE_NAME with the actual server and database names.

Step 4: Using the Database Context in a Controller

Now that you have set up the database context, you can use it in your Web API controllers to perform CRUD operations. Below is an example of a ProductsController that uses the ProductContext to manage products:

        
        using System.Collections.Generic;
        using System.Linq;
        using System.Web.Http;

        public class ProductsController : ApiController
        {
            private ProductContext db = new ProductContext(); // Database context

            // GET api/products
            public IEnumerable<Product> Get()
            {
                return db.Products.ToList(); // Retrieve all products
            }

            // GET api/products/1
            public IHttpActionResult Get(int id)
            {
                var product = db.Products.Find(id);
                if (product == null)
                {
                    return NotFound(); // Return 404 if not found
                }
                return Ok(product); // Return the product
            }

            // POST api/products
            public IHttpActionResult Post([FromBody] Product product)
            {
                if (!ModelState.IsValid)
                {
                    return BadRequest(ModelState); // Return validation errors
                }
                db.Products.Add(product); // Add the new product
                db.SaveChanges(); // Save changes to the database
                return CreatedAtRoute("DefaultApi", new { id = product.Id }, product); // Return 201
            }

            // PUT api/products/1
            public IHttpActionResult Put(int id, [FromBody] Product product)
            {
                if (!ModelState.IsValid || product.Id != id)
                {
                    return BadRequest(ModelState); // Return validation errors
                }
                db.Entry(product).State = EntityState.Modified; // Update the product
                db.SaveChanges(); // Save changes to the database
                return StatusCode(HttpStatusCode.NoContent); // Return 204
            }

            // DELETE api/products/1
            public IHttpActionResult Delete(int id)
            {
                var product = db.Products.Find(id);
                if (product == null)
                {
                    return NotFound(); }
                db.Products.Remove(product); // Remove the product
                db.SaveChanges(); // Save changes to the database
                return Ok(product); // Return the deleted product
            }

            protected override void Dispose(bool disposing)
            {
                if (disposing)
                {
                    db.Dispose(); // Dispose the database context
                }
                base.Dispose(disposing);
            }
        }
            

Conclusion

Connecting to a database in ASP.NET Web API using Entity Framework is straightforward. By following the steps outlined above, you can set up a database context, configure your connection string, and perform CRUD operations in your API controllers. This allows you to build robust web applications that can interact with a database effectively.