How to use LINQ to SQL queries in ASP.Net website

Last Reply on May 03, 2014 04:40 AM By Mudassar

Posted on May 03, 2014 04:34 AM

I am working on some LINQ queries on my website database I want to be able to form some queries but I am just working on setting up my website so I can do these queries and I am offering reference errors

It seems to not be able to find certain classes that I need to be able to complete my task

I am making some classes to help me with linq queries and I was thinking I would be able to add the using statements in my project with just typing out the classes I need in my program

When I try this the red squiggly lines stay under the class names and it trys to generate a class instead of referencing a class like I need it to

Here is the list I am having issues and errors with:

Table<Product>

DataContext

[Table]

[Column(IsPrimaryKey = true, DbType = "int")]

EntityRef<Product>

[Association(Storage = "_myProduct", OtherKey = "Prod_ID", ThisKey = "Prod_ID", IsForeignKey = true)]

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web.UI.WebControls;
using System.Data.Linq;

/// <summary>
/// Summary description for ObjectFactory
/// </summary>
public class ObjectFactory
{
    public IEnumerable<Offer> myOffers { get; set; }

    public Table<Product> myProducts { get; set; }

    public DataContext myDataContext = null;

    //make the connection
    public ObjectFactory(string connectionString)
    {
        string connString = ConfigurationManager.ConnectionStrings[connectionString].ConnectionString;
        myDataContext = new DataContext(connString);

        //get products table
        myProducts = myDataContext.GetTable<Product>();

        //get offers table
        myOffers = myDataContext.ExecuteQuery<Offer>("SELECT Supp_ID,Prod_ID,Price, Quantity FROM Tb_Offers");
    }

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.Linq;
using System.Data.Linq.Mapping;

/// <summary>
/// Summary description for Offer
/// </summary>
/// 
[Table]
public class Offer
{
    [Column(IsPrimaryKey = true, DbType = "int")]
    private int Supp_ID;
    [Column(IsPrimaryKey = true, DbType = "int")]
    private int Prod_ID;
    [Column(DbType = "Decimal", CanBeNull = true)]
    public decimal Price { get; set; }
    [Column(DbType = "Float", CanBeNull = true)]
    public float Quantity { get; set; }

    //EntityRef<Supplier> _mySupplier;
    //[Association(Storage = "_mySupplier", OtherKey = "Supp_ID", ThisKey = "Supp_ID", IsForeignKey = true)]
    //public Supplier theSupplier { get { return this._mySupplier.Entity; } }

    //[Association(OherKey = "Supp_ID", ThisKey = "Supp_ID", IsForeignKey = true)]
    //public EntityRef<Supplier> theSupplier;

    EntityRef<Product> _myProduct;
    [Association(Storage = "_myProduct", OtherKey = "Prod_ID", ThisKey = "Prod_ID", IsForeignKey = true)]
    public Product theProduct { get { return this._myProduct.Entity; } }

    //strangely enough this does not work...
    //[Association(OtherKey = "Supp_ID", ThisKey = "Supp_ID", IsForeignKey = true)]
    //public EntityRef<Supplier> theSupplier { get; set; }

    //[Association(OtherKey = "Prod_ID", ThisKey = "Prod_ID", IsForeignKey = true)]
    //public EntityRef<Product> theProduct { get; set; }

    //public String Supplier_Name { get { return this.theSupplier.Name; } }
    public String Product_Name { get { return this.theProduct.Name; } }
}