• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 590
  • Last Modified:

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




0
talker2004
Asked:
talker2004
  • 8
  • 5
1 Solution
 
Fernando SotoCommented:
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 SotoCommented:
Very good.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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 SotoCommented:
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
 
talker2004Author Commented:
Worked the first time....

thanks
0
 
Fernando SotoCommented:
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 SotoCommented:
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 8
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now