Using DbLinqDataSource Component

In This Topic

This article guides you through the process of creating simple application powered by LinqConnect and shows you how to use DbLinqDataSource component in the ASP.NET applications. In first part a short walkthrough on the simple data binding and filtering will be given, the second part will be devoted to data modifying, and in the third one we will take a look on some additional tips and tricks with the declarative markup.

DataBinding
Customizing Datasource

DataBinding

We will make an Web application that shows data from CRM_DEMO database using LinqConnect DataContext.

Note:
We are using the GridView component to display the data received from the LinqConnect context we've created.

Perform the following steps to create application:

Create a new ASP.NET Web application project.
Create a new LinqConnect model. You can read more about this step in the Using Entity Developer section.
Save the project and rebuild your solution to generate code for your model.
Go to Web Forms Designer and switch to Design View and then drag the DbLinqDataSource component from the LinqConnect toolbox to the form.
Choose Configure Data Source in the DbLinqDataSource component smart tag. Select created context in the Choose your context object box and click the Next button, then choose tables and fields for displaying.
Drag a GridView component onto the page, right-click it and then click Show Smart Tag. On the GridView Tasks menu that opened, choose DbLinqDataSource1 in the corresponding combobox and select the Enable Editing check box.
Select Choose Data Source in the GridView Tasks. Select database table and fields for displaying in the Configure Data Source wizard:
Run application to view all records from selected table:
To edit any row click the Edit link:

Click the Update link to confirm your changes.

We don't call SubmitChanges method explicitly in the given example, because it is encapsulated in the DbLinqDataSource component and all of the changes are saved to the database automatically.

Data Filtering

Now let's consider how we can make record filtering:

Rather than show all companies within the database, we can update our user interface to include a dropdownlist that allows the user to filter the companies by a country.

    
        <div class="country">
            Pick Country:            
            <asp:DropDownList ID=CountryList
                DataSourceId = CompanyDatasource
                DataTextField = Country
                DataValueField = Country
                AutoPostBack = True
                runat = server
            />
        </div>

After this page running filter dropdownlist is enabled

Select the Configure DataSource option in the GridView smart tag:
This will bring us back to the LinqDataSource control property editor that was used at the very beginning of this tutorial. Click the Where button to add a binding filter to the datasource control.

Data Ordering

You can use the same approach for the data ordering.

Select the Configure DataSource option in the GridView smart tag:
Click the OrderBy button to add an ordering filter to the datasource control.

To enable another data source options just use GridView smart tag:

Just select the check boxes you need and corresponding options will be implemented automatically.

Customizing Datasource

In cases when design time filtering and sorting capabilities are not enough for your purposes, take a look at the Selecting DbLinqDataSource event. The following approach can be used for this operation performing:

LINQ query:

protected void DbLinqDataSource1_Selecting(object sender, LinqDataSourceSelectEventArgs e)
{
   CrmDemoContext.CrmDemoDataContext db = new CrmDemoContext.CrmDemoDataContext();
   System.Text.StringBuilder sb = new System.Text.StringBuilder();
   db.Log = new System.IO.StringWriter(sb);
   List<string> primaryContactCountries = new List<string> { "UK", "Russia", "China" };

   var q = from p in db.Companies
           where primaryContactCountries.Contains(p.PersonContact.Country)
           select p;
   
   q.ToList();

   e.Result = q;    
}

Visual Basic


Protected Sub DbLinqDataSource1_Selecting(ByVal sender As Object, ByVal e As LinqDataSourceSelectEventArgs)
    Dim CS$0$0000 As ParameterExpression
    Dim db As New CrmDemoContext.CrmDemoDataContext
    Dim sb As New System.Text.StringBuilder
    db.Log = New StringWriter(sb)
    Dim <>g__initLocal0 As New List(Of String)
    <>g__initLocal0.Add("UK")
    <>g__initLocal0.Add("Russia")
    <>g__initLocal0.Add("China")
    Dim primaryContactCountries As List(Of String) = <>g__initLocal0
    Dim q As IQueryable(Of Company) = 
        Queryable.Where(Of Company)(db.Companies, Expression.Lambda(Of Func(Of Company, Boolean))(Expression.Call(Expression.Constant(primaryContactCountries), 
            DirectCast(GetMethod(List(Of String).Contains, List(Of String)), MethodInfo), New Expression() { 
                Expression.Constant("", GetType(String)) }), New ParameterExpression() { CS$0$0000 = Expression.Parameter(GetType(Company), "p") }))
    q.ToList(Of Company)()
    Me.Label1.Text = sb.ToString
    e.Result = q
End Sub

LINQ query is the most popular approach for implementing the dynamic filtering, ordering and so on.

Stored Procedure:

For the purposes of this sample you can add a function like the following:

CREATE OR REPLACE FUNCTION GetCompaniesByCountry(country varchar2) RETURN sys_refcursor AS 
cur sys_refcursor;
BEGIN 
OPEN cur FOR SELECT t1."CompanyID", t1."CompanyName", t1."PrimaryContact", 
t1."Web", t1."Email", t1."AddressTitle", t1."Address", t1."City", t1."Region", 
t1."PostalCode", t1."Country", t1."Phone", t1."Fax" FROM CRM_DEMO."Company" t1 
LEFT OUTER JOIN CRM_DEMO."Person Contact" t2 ON t1."PrimaryContact" = t2."ContactID" WHERE t2."Country" = country;
return cur;
END;

To use the convenience of the strong typing provided by LINQ, the return type of the function should be changed from the one autogenerated by Entity Developer to the CrmDemoContext.Company:

SQL Query:

Unlike the previous two cases, in which we have used the approaches that are possible with Microsoft code, this one will differ a bit. The reason is the fact that Devart.Data.Linq.DataContext class has a Query<TEntity> method.

This method, like the ExecuteQuery, takes a native SQL query as an input parameter, but returns an IQueryable<TEntity> instead of IEnumerable<TEntity>. This gives to user the opportunity to add additional filtering and sorting to the entities that were obtained using a native SQL query.

protected void DbLinqDataSource1_Selecting(object sender, LinqDataSourceSelectEventArgs e)
{
   CrmDemoContext.CrmDemoDataContext db = new CrmDemoContext.CrmDemoDataContext();
   System.Text.StringBuilder sb = new System.Text.StringBuilder();
   db.Log = new System.IO.StringWriter(sb);
 
   var q = db.Query<CrmDemoContext.Company>("SELECT t1.\"CompanyID\", 
        t1.\"CompanyName\", t1.\"PrimaryContact\", t1.\"Web\", t1.\"Email\", t1.\"AddressTitle\", 
        t1.\"Address\", t1.\"City\", t1.\"Region\", t1.\"PostalCode\", t1.\"Country\", 
        t1.\"Phone\", t1.\"Fax\" FROM CRM_DEMO.\"Company\" t1 LEFT OUTER JOIN
        CRM_DEMO.\"Person Contact\" t2 ON t1.\"PrimaryContact\" = t2.\"ContactID\" WHERE t2.\"Country\" 
        IN ('Russia', 'China', 'Germany')").ToList();
   
   e.Result = q;
}

Visual Basic


Protected Sub DbLinqDataSource1_Selecting(ByVal sender As Object, ByVal e As LinqDataSourceSelectEventArgs)
    Dim db As New CrmDemoDataContext
    Dim sb As New StringBuilder
    db.Log = New StringWriter(sb)

    Dim q As IQueryable(Of Company) = db.Query(Of Company)("SELECT t1.\"CompanyID\", 
        t1.\"CompanyName\", t1.\"PrimaryContact\", t1.\"Web\", t1.\"Email\", t1.\"AddressTitle\", 
        t1.\"Address\", t1.\"City\", t1.\"Region\", t1.\"PostalCode\", t1.\"Country\", 
        t1.\"Phone\", t1.\"Fax\" FROM CRM_DEMO.\"Company\" t1 LEFT OUTER JOIN
        CRM_DEMO.\"Person Contact\" t2 ON t1.\"PrimaryContact\" = t2.\"ContactID\" WHERE t2.\"Country\" 
        IN ('Russia', 'China', 'Germany')").ToList()
   
    e.Result = q
End Sub

[email protected]