Microsoft Visual Studio .NET 2008 Tips

1. Using Linq

1. Use Server Explorer to connect to a SQL Server
2. Drag the table to your dbml diagram
3. Open projectname.designer.cs under projectname.dbml and search for the table name
Linq has generated the methods for select, insert, update and delete operations which will be called in the consumer classes.

4. Add a new class e.g. ProductAttributes.cs
5. Add the following to the new class

Note: example using get and set methods.
The retrieve function could build a dynamic object list or set simple variables.


private string attribute1;
private string attribute2;
private string attribute3;

public string Attribute1
{
    get { return attribute1; }
    set { attribute1 = value; }
}

public string Attribute2
{
    get { return attribute2; }
    set { attribute2 = value; }
}

public string Attribute3
{
    get { return attribute3; }
    set { attribute3 = value; }
}

String sz = System.Configuration.ConfigurationManager.ConnectionStrings["Project1AppConnectionString"].ToString();
public void Retrieve(int id)
{
    Project1DataContext db = new Project1DataContext(sz);
    var attributesRecs = db.sproc_t_attributes_valuesSelect(id);

    foreach (var attribute in attributesRecs)
    {
        attribute1 = attribute.Attribute1_Value;
        attribute2 = attribute.Attribute2_Value;
        attribute3 = attribute.Attribute3_Value;
    }
}

public void Create()
{

}

public void Update()
{

}

public void Delete()
{

}

Update the Create, Update and Delete methods to use the correct functions in projectname.designer.cs under projectname.dbml in Solution Explorer.

Call from a test harness.
Add a project reference to the data access component.


protected void GetProductAttributes_Click(object sender, EventArgs e)
{
    ProductAttributes x = new ProductAttributes();
    x.Retrieve(1);
    Response.Write(x.Attribute1 + "<br />");
    Response.Write(x.Attribute2 + "<br />");
    Response.Write(x.Attribute3 + "<br />");
}

2. New namespaces

Data access provider.


using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Collections.Generic;
using System.ComponentModel;

In a test harness the following namespaces must be added for the example above.


using System.ComponentModel;
using Project1;

3. Accessing output parameters from stored procedure calls

Data access provider.


private int table1ID;

public int Create()
{
    Project1DataContext db = new Project1DataContext(sz);
    System.Data.Linq.ISingleResult<sproc_t_table1Insert> r = db.sproc_t_table1Insert(name, description, DateTime.Now);
    int nRet = (int)r.ReturnValue;
    table1ID = (int)r.ElementAt(0).table1ID;
    return nRet;
}

table1ID = (int)r.ElementAt(0).table1ID uses the Enumerable object to access the generated return output variables.

Back to index.