Linq to dataset question C# vb.net LINQ ASP.Net

I have a question about LINQ to dataset.

I a class in my application which is very database intensive. As the requirements for the application are evolving and have grown to the point where we need to tweak the speed of the algorithm.

I am trying to find a solution which would fit into the flow of the algorithm so that it could be easily integrated following the same logic. I believe Linq to dataset will meet my needs.


Here is my Question:

I have a dataset named dsNorthwind in my application, lets say i am totally disconnected from my datasource, i can't do Linq to sql because of the requirements.  Ok, i want to take dsNorthwind and run Linq query on it.

Could someone give me a way to translate the following sql query to Linq to dataset on dsNorthwind.
Select * From Customers where customercity = 'London';

Here is the one other issue, i need to declare another dataset name dsNorthwindFiltered, this dataset will store the results from the Linq query. So i guess i have to somehow cast it back to a dataset.

If someone could help me or lead me in the right direction i would really appreciate it.

Thanks




LVL 7
talker2004Asked:
Who is Participating?
 
Fernando SotoConnect With a Mentor RetiredCommented:
Hi talker2004;

I converted the code to VB .Net

Fernando
Imports System.Data.SqlClient
 
Public Class Form1
    Dim dsNorthwind As New DataSet("dsNorthwind")
 
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        ' Loading the dsNorthwind with a data table called dtNorthwind
        Dim cnn As New SqlConnection("Data Source=.\Sqlexpress;Initial Catalog=Northwind;Integrated Security=true")
        Dim cmd = cnn.CreateCommand()
        cmd.CommandText = "Select * From Customers"
        cnn.Open()
        Dim dtNorthwind As New DataTable("dtNorthwind")
        Dim dr As SqlDataReader = cmd.ExecuteReader()
        dtNorthwind.Load(dr)
        cnn.Close()
        dsNorthwind.Tables.Add(dtNorthwind)
        ' Display in DataGridView1
        DataGridView1.DataSource = dsNorthwind.Tables("dtNorthwind")
 
    End Sub
 
    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        ' Select all the customers in London
        Dim drCustLondon As IEnumerable(Of DataRow) = _
            From customer In dsNorthwind.Tables("dtNorthwind").AsEnumerable() _
            Where customer.Field(Of String)("City") = "London" _
            Select customer
 
        ' Copy the London customers to a new table
        Dim dtLondon As DataTable = drCustLondon.CopyToDataTable()
        ' Display the London customers in DataGridView2
        DataGridView2.DataSource = dtLondon
 
    End Sub
End Class

Open in new window

0
 
Fernando SotoRetiredCommented:
Hi talker2004;

Below is code snippet showing how it is done.

Fernando
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
 
namespace WindowsFormsApplication29
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
 
        DataSet dsNorthwind = new DataSet("dsNorthwind");
 
        private void button1_Click(object sender, EventArgs e)
        {
            // Loading the dsNorthwind with a data table called dtNorthwind
            SqlConnection cnn = new SqlConnection(@"Data Source=.\Sqlexpress;Initial Catalog=Northwind;Integrated Security=true");
            SqlCommand cmd = cnn.CreateCommand();
            cmd.CommandText = "Select * From Customers";
            cnn.Open();
            DataTable dtNorthwind = new DataTable("dtNorthwind");
            SqlDataReader dr = cmd.ExecuteReader();
            dtNorthwind.Load(dr);
            cnn.Close();
            dsNorthwind.Tables.Add(dtNorthwind);
            // Display in DataGridView1
            dataGridView1.DataSource = dsNorthwind.Tables["dtNorthwind"];
        }
 
        private void button2_Click(object sender, EventArgs e)
        {
            // Select all the customers in London
            IEnumerable<DataRow> drCustLondon = from customer in dsNorthwind.Tables["dtNorthwind"].AsEnumerable()
                                                where customer.Field<String>("City") == "London"
                                                select customer;
 
            // Copy the London customers to a new table
            DataTable dtLondon = drCustLondon.CopyToDataTable<DataRow>();
            // Display the London customers in DataGridView2
            dataGridView2.DataSource = dtLondon;
         
        }
    }
}

Open in new window

0
 
talker2004Author Commented:
Hey, this looks really great thanks for addressing both of my concerns on my issue. I will covert it over to vb and test it, I will let you know how it goes.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Fernando SotoRetiredCommented:
Very good.
0
 
talker2004Author Commented:
I am in the ide working on it now.
0
 
talker2004Author Commented:

I am having issue with converting this line here.


 DataTable dtLondon = drCustLondon.CopyToDataTable<DataRow>();
0
 
talker2004Author Commented:
what is the significance of <DataRow>();

0
 
talker2004Author Commented:
Private Sub button2_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button2.Click
        ' Select all the customers in London
        Dim drCustLondon As IEnumerable(Of DataRow) = From customer In dsNorthwind.Tables("dtNorthwind").AsEnumerable() _
            Where customer.Field(Of String)("City") & "London" _
            Select customer

        ' Copy the London customers to a new table

        Dim dtLondon As DataTable = drCustLondon.CopyToDataTable()
        ' Display the London customers in DataGridView2
        DataGridView1.DataSource = dtLondon

    End Sub
0
 
talker2004Author Commented:
I am having issues with the code. Please let me know if you see the problem.
0
 
talker2004Author Commented:
Worked the first time....

thanks
0
 
Fernando SotoRetiredCommented:
Not a problem, glad it worked out for you.  ;=)
0
 
talker2004Author Commented:
Thanks so much, it's a real good start.

I am going to have to work with some more complex queries  but i am sure it's going to work out good.

0
 
Fernando SotoRetiredCommented:
Very good. If you have any more question just post a new question on the Language Integrated Query - LINQ thread. Have a great day.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.