Getting Data from SQL DB for ASP.NET Web Forms API

Question

I followed this tutorial (http://www.asp.net/web-api/overview/getting-started-with-aspnet-web-api/tutorial-your-first-web-api) for setting up a Web API in my ASP.NET Web Application. It doesnt, however, talk about how to retrieve records from a database. It instead hard coded dummy data into the controller, like this:

Product[] products = new Product[] 
    { 
        new Product { Id = 1, Name = "Tomato Soup", Category = "Groceries", Price = 1 }, 
        new Product { Id = 2, Name = "Yo-yo", Category = "Toys", Price = 3.75M }, 
        new Product { Id = 3, Name = "Hammer", Category = "Hardware", Price = 16.99M } 
    };

Let's say I have a table in my SQL DB called 'Products', how would I retrieve the product data from there instead? If anyone could point me in the right direction, I would appreciate it.

I did try using a function but it didnt work.

 DataTable dbProducts = new DataTable();
 dbProducts = Wrapper.getProducts();

First you do need to have a database with a table and with records in it if you rally want to get data from a db. Secondly, you will need to add a data access layer so you can query your database and get the records you need so they can be used by the Web API. Follow the steps in this tutorial as it shows how to use the entity framework with the Web API: asp.net/web-api/overview/creating-web-apis/… — Ricardo Sanchez
– Ricardo Sanchez, Commented Oct 10, 2014 at 16:09

BillHaggerty · Accepted Answer · 2018-03-15 17:54:59Z

1

If the connection doesnt work check out this website http://www.sqlstrings.com/ and modify the connection string to your desired db type. If this is something more then a learning experience I recommend you use a linq to sql or entity framwork.(update 2018: Dapper is my prefered way of doing this)

Copy Pasta Code:

        // Create a connection to the database        
    SqlConnection conn = new SqlConnection("Data Source=MyDBServer;Initial Catalog=MyDB;Integrated Security=True");
    // Create a command to extract the required data and assign it the connection string
    SqlCommand cmd = new SqlCommand("SELECT * FROM Product", conn);
    cmd.CommandType = CommandType.Text;
    // Create a DataAdapter to run the command and fill the DataTable
    SqlDataAdapter da = new SqlDataAdapter();
    da.SelectCommand = cmd;
    DataTable dt = new DataTable();
    da.Fill(dt);
List<Product> products = new List<Product>();
foreach(DataRow row in dt.Rows)
{
    products.add(New Product{ row["Id"], row["Name"], row["Category"], row["Price"]});
}

edited Mar 15, 2018 at 17:54

answered Oct 10, 2014 at 16:14

BillHaggerty

6,69710 gold badges42 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

aberhamm Over a year ago

So after I do this, how would I add the data to the products array?

BillHaggerty Over a year ago

You could iterate through each row of the table and populate the array.

BillHaggerty Over a year ago

Added List population instead of array, mostly because I like list more.

hanz · Accepted Answer · 2014-10-10 16:37:31Z

You can use entity framework + Repository to quickly get the data from the database.

If you already have a table called Product in your SQl database, make your EF is configured and you have all the EF reference available in your project.

I haven't seen that tutorial, but just to give you a quick and rough idea...

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

public AppContext : DbContext
{
public DbSet<Product> Products {get;set;}
}


    public class IProductRepository
    {
        public IQuerable<Product> Products{get;}
    }

    public class ProductRepository : IProductRepository
    {
     private  AppContext context = new DBContext();

    public IQuerable<Product> Products
    {
        get
    {
    context.Products;
    }
    }

    }



    Now in your Web Api method & controller.... 


    public class ProductController 
    {
    private IProductRepository repo;

        public ProductController()
    {
         repo = new ProductRepository(); // or use DI
    }

    public List<Product> Get()
    {
     return repo.Products.ToList();
    }

    }

Collectives™ on Stack Overflow

Getting Data from SQL DB for ASP.NET Web Forms API

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related