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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Fernando SotoRetiredCommented:
Very good.
0
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

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
Fernando SotoRetiredCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.