none
Doing LINQ to SQL (To Put Products in Northwind to DataViewGrid) in Visual C# 2008 Express: How can I Define the LINQ query, before I use AsDataView method? RRS feed

  • Question

  • Hi all,

    I am using Visual C# 2008 Express LINQ to SQL in Windows Forms application for the first time. I have the following code:
    // -- wfPopulateDataSet13_1.cs --//
    using System;
    
    
    
    using System.Collections.Generic;
    
    
    
    using System.ComponentModel;
    
    
    
    using System.Data;
    
    
    
    using System.Data.SqlClient;
    
    
    
    using System.Drawing;
    
    
    
    using System.Linq;
    
    
    
    using System.Text;
    
    
    
    using System.Windows.Forms;
    
    
    
    
    
    
    
    namespace wfPopulateDataSets13_1
    
    
    
    {
    
    
    
        public partial class Form1 : Form
    
    
    
        {
    
    
    
            public Form1()
    
    
    
            {
    
    
    
                InitializeComponent();
    
    
    
            }
    
    
    
            
    
    
    
    
    
    
    
            private void Form1_Load(object sender, EventArgs e)
    
    
    
            {
    
    
    
                SqlConnection conn;
    
    
    
                SqlCommand comm;
    
    
    
                SqlDataAdapter adapter;
    
    
    
                DataSet ds = new DataSet();
    
    
    
    
    
    
    
                // --Connect to the Northwind database in SQL Server 2008 Express
    
    
    
                conn = new SqlConnection(@"Data Source = NAB-WK-02554356\SQLSCOTT;" +
    
    
    
                        "Initial Catalog=Northwind; Integrated Security=True");
    
    
    
                comm = new SqlCommand("SELECT productname, unitprice FROM products WHERE unitprice <20", conn);
    
    
    
                adapter = new SqlDataAdapter(comm);
    
    
    
                adapter.Fill(ds);
    
    
    
    
    
    
    
                    // --query for the productname and unitprice<20
    
    
    
                     var products = from product in ds.Tables[0].AsEnumerable()
    
    
    
                                       where product.Field<Decimal>("unitprice") < 20
    
    
    
                                       select product;
    
    
    
            }
    
    
    
    
    
    
    
            private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
    
    
    
            {
    
    
    
                // --bind to a datagridview control
    
    
    
              DataGridView1.DataSource = products.AsDataView();
    
    
    
    
    
    
    
            }
    
    
    
    
    
    
    
                   
    
    
    
    
    
    
    
        }
    
    
    
    }<br/>===============================================<br/>

    I added 2 columns (Column1 and Column2) in DataViewGrid Task.
    I do not know how to define the LINQ query, before I use the AsDataView method.
    I executed the code anyway and I got the following 2 errors: <br/>
    Error 1 The name 'DataGridView1' does not exist in the current context 
    C:\Documents and Settings\e1enxshc\My Documents\Visual Studio 2008\Projects\wfPopulateDataSets13_1\
    wfPopulateDataSets13_1\Form1.cs 44 11 wfPopulateDataSets13_1<br/>
    Error 2 The name 'products' does not exist in the current context 
    C:\Documents and Settings\e1enxshc\My Documents\Visual Studio 2008\Projects\wfPopulateDataSets13_1\wfPopulateDataSets13_1\Form1.cs 44 38 wfPopulateDataSets13_1<br/>
    --------------------------------------------------<br/>
    Please help and advise me what I should do in defining the LINQ query to get the "product" and "unitprice"
    out of the Products in the Northwind database that is in SQL Server 2008 Express.<br/>
    Thanks,
    Scott Chang
    • Edited by S.Chang Monday, August 17, 2009 7:35 PM
    Monday, August 17, 2009 7:30 PM

Answers

  • Hi Scott Chang,

    The reason for your first error is that the default object name of the DataGridView class is dataGridView1. Pat attention to the capitalized letter. In fact you could detect this error if the object doesn't exist when programming, with the help of IntelliSense. If this function has been turned off in your Visual Studio, the following steps help you configure the environment:

        1. On the Tools menu, click Options.
        2. Select the Text Editor folder.
        3. Select the folder of the C# language.
        4. Select a property page for the language and check to see if you have selected related options.

    The reason for the second error is that you performed the Linq query in Form1_load method while referring to it in the dataGridView1_CellContentClick method. To solve this problem, you need to combine these two parts together. In other words, you can simply move the code in dataGridView1_CellContentClick method into the Form1_load method to make your program work, and vice versa.

    For your last request, there’s no field in the Products table called 'Product', but 'ProductName' or 'productID'. If you mean you want to select 'productName' and 'UnitPrice' in linq, instead of in the T-SQL expression in the constructor of SqlCommand, you'd better build an Entity Class. This link helps you create an Entity Class in your program:

    http://msdn.microsoft.com/en-us/library/bb425822.aspx

    After you set up the entity class, you can use the following code to achieve your purpose:

                //dc is instantiated by the entity class you create
                var product = from p in dc.Products
                              select
                              {
                                  p.ProductName,
                                  p.UnitPrice
                              };

                dataGridView1.DataSource = product.ToList();

    If you have any further question, feel free to ask.

     


    Best regards,
    Charlie Lee

    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    • Edited by Zhipeng Lee Thursday, August 20, 2009 9:07 AM
    • Marked as answer by Zhipeng Lee Monday, August 24, 2009 9:36 AM
    Thursday, August 20, 2009 8:10 AM

All replies

  • Hi Scott Chang,

    The reason for your first error is that the default object name of the DataGridView class is dataGridView1. Pat attention to the capitalized letter. In fact you could detect this error if the object doesn't exist when programming, with the help of IntelliSense. If this function has been turned off in your Visual Studio, the following steps help you configure the environment:

        1. On the Tools menu, click Options.
        2. Select the Text Editor folder.
        3. Select the folder of the C# language.
        4. Select a property page for the language and check to see if you have selected related options.

    The reason for the second error is that you performed the Linq query in Form1_load method while referring to it in the dataGridView1_CellContentClick method. To solve this problem, you need to combine these two parts together. In other words, you can simply move the code in dataGridView1_CellContentClick method into the Form1_load method to make your program work, and vice versa.

    For your last request, there’s no field in the Products table called 'Product', but 'ProductName' or 'productID'. If you mean you want to select 'productName' and 'UnitPrice' in linq, instead of in the T-SQL expression in the constructor of SqlCommand, you'd better build an Entity Class. This link helps you create an Entity Class in your program:

    http://msdn.microsoft.com/en-us/library/bb425822.aspx

    After you set up the entity class, you can use the following code to achieve your purpose:

                //dc is instantiated by the entity class you create
                var product = from p in dc.Products
                              select
                              {
                                  p.ProductName,
                                  p.UnitPrice
                              };

                dataGridView1.DataSource = product.ToList();

    If you have any further question, feel free to ask.

     


    Best regards,
    Charlie Lee

    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    • Edited by Zhipeng Lee Thursday, August 20, 2009 9:07 AM
    • Marked as answer by Zhipeng Lee Monday, August 24, 2009 9:36 AM
    Thursday, August 20, 2009 8:10 AM
  • Hello Charlie Lee,  Thanks for your response.

    #1 for solving Error1: I tried to follow your Instruction Steps 1, 2, 3 and 4.  I reached Step 4 and saw the following:
                           C#
                              --- General
                              --- Tabs
                              --- Advanced
                            |-|  Formatting
                                  --- General
                                  --- Indentation
                                  --- New Lines
                                  --- Spacing
                                  --- Wrapping
                              IntelliSence

    I do not know what to do next! (i.e. how to complete your Step 4).  Please help and advise me more in this matter.

    #2 for solving Error2: I had the following code statements:
                                    
    // --query for the productname and unitprice<20
                     var products = from product in ds.Tables[0].AsEnumerable()
                                       where product.Field<Decimal>("unitprice") < 20
                                       select product;
     Are these code statements OK to stay in my project, if I need to add the code statements you suggested ?

    I am new in the LINQ-to-SQL programming.  Please help and give me more detailed instructions.

    Thanks again,
    Scott Chang     
    Thursday, August 20, 2009 3:02 PM
  • Hi Scott Chang,

     

    When you march to the step 4, select the 'Advanced' sub-folder.  Check to see if you have selected the 'Underline errors in the editor' option in 'Editor help'. In fact, you'd better select all options in the 'Advanced' sub-folder. Next time when 'dataGridView1' mistyped as 'DataGridView1', it would be underlined with a red wavy line. Have a test.

     

    The code you provide works well if you combine the two parts together. My suggestion just offers an alternative to do the same thing. It's easier and more concise. If you use the code I provide, you need to replace the Linq Statement you mentioned. In other words, you can either or them, not both, as your way is actually Linq to dataset, while setting up Entity Class belongs to Linq to SQL.

     

    If you have any further questions, feel free to ask.

     


    Best regards,
    Charlie Lee

    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Friday, August 21, 2009 2:54 AM
  • Hi Charie Lee,  Thanks for your response.

    I am new in doing LINQ, rusty in T-SQL and not able to do either my way (combined T-SQL and LINQ) or your way (pure LINQ with Entity Class).  Could you please give me the detailed instructions for doing your way, especially establishing the Entity Class part(i.e. getting DataClass.dbml thing)?  Please reply.

    Thanks,
    Scott Chang
    SHC
    Monday, August 24, 2009 7:41 PM
  • Hi Charlie Lee,

    I spent last several days to study the Entity Claa, DataContext, and DataClass.dbml.  I added Northwind.sdf to Database Explorer and created the following code myself:

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data.Linq;
    using System.Data.SqlClient;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    
    namespace wfPopulateDataSets13_1
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }
            
    
            private void Form1_Load(object sender, EventArgs e)
            {
             
                
    
            //dc is instantiated by the entity class created by me
    
            string connString = @"
                  server = NAB-WK-02554356\SQLSCOTT;
                  intergrated security = true;
                  database = northwind;
                  ";
            
            DataContext db = new DataContext(connString);
    
                List<Customers> customers = dc.GetList<Customers>();
    
    
              var product = from p in dc.Products
                            select
                            {
                             p.ProductName,
                             p.UnitPrice
                             };
               dataGridView1.DataSource = product.ToList();
            }
       }
    }
    I thought I was in the right truck, but I got the following 16 errors:

    Error 1 The type or namespace name 'Customers' could not be found (are you missing a using directive or an assembly reference?) C:\Documents and Settings\e1enxshc\My Documents\Visual Studio 2008\Projects\wfPopulateDataSets13_1\wfPopulateDataSets13_1\Form1.cs 36 18 wfPopulateDataSets13_1
    Error 2 The name 'dc' does not exist in the current context C:\Documents and Settings\e1enxshc\My Documents\Visual Studio 2008\Projects\wfPopulateDataSets13_1\wfPopulateDataSets13_1\Form1.cs 36 41 wfPopulateDataSets13_1
    Error 3 The type or namespace name 'Customers' could not be found (are you missing a using directive or an assembly reference?) C:\Documents and Settings\e1enxshc\My Documents\Visual Studio 2008\Projects\wfPopulateDataSets13_1\wfPopulateDataSets13_1\Form1.cs 36 52 wfPopulateDataSets13_1
    Error 4 The name 'dc' does not exist in the current context C:\Documents and Settings\e1enxshc\My Documents\Visual Studio 2008\Projects\wfPopulateDataSets13_1\wfPopulateDataSets13_1\Form1.cs 39 35 wfPopulateDataSets13_1
    Error 5 Only assignment, call, increment, decrement, and new object expressions can be used as a statement C:\Documents and Settings\e1enxshc\My Documents\Visual Studio 2008\Projects\wfPopulateDataSets13_1\wfPopulateDataSets13_1\Form1.cs 42 26 wfPopulateDataSets13_1
    Error 6 The name 'p' does not exist in the current context C:\Documents and Settings\e1enxshc\My Documents\Visual Studio 2008\Projects\wfPopulateDataSets13_1\wfPopulateDataSets13_1\Form1.cs 42 26 wfPopulateDataSets13_1
    Error 7 Only assignment, call, increment, decrement, and new object expressions can be used as a statement C:\Documents and Settings\e1enxshc\My Documents\Visual Studio 2008\Projects\wfPopulateDataSets13_1\wfPopulateDataSets13_1\Form1.cs 42 28 wfPopulateDataSets13_1
    Error 8 Only assignment, call, increment, decrement, and new object expressions can be used as a statement C:\Documents and Settings\e1enxshc\My Documents\Visual Studio 2008\Projects\wfPopulateDataSets13_1\wfPopulateDataSets13_1\Form1.cs 43 26 wfPopulateDataSets13_1
    Error 9 The name 'p' does not exist in the current context C:\Documents and Settings\e1enxshc\My Documents\Visual Studio 2008\Projects\wfPopulateDataSets13_1\wfPopulateDataSets13_1\Form1.cs 43 26 wfPopulateDataSets13_1
    Error 10 Invalid expression term '{' C:\Documents and Settings\e1enxshc\My Documents\Visual Studio 2008\Projects\wfPopulateDataSets13_1\wfPopulateDataSets13_1\Form1.cs 41 25 wfPopulateDataSets13_1
    Error 11 ; expected C:\Documents and Settings\e1enxshc\My Documents\Visual Studio 2008\Projects\wfPopulateDataSets13_1\wfPopulateDataSets13_1\Form1.cs 41 26 wfPopulateDataSets13_1
    Error 12 Invalid expression term ',' C:\Documents and Settings\e1enxshc\My Documents\Visual Studio 2008\Projects\wfPopulateDataSets13_1\wfPopulateDataSets13_1\Form1.cs 42 39 wfPopulateDataSets13_1
    Error 13 ; expected C:\Documents and Settings\e1enxshc\My Documents\Visual Studio 2008\Projects\wfPopulateDataSets13_1\wfPopulateDataSets13_1\Form1.cs 42 39 wfPopulateDataSets13_1
    Error 14 ; expected C:\Documents and Settings\e1enxshc\My Documents\Visual Studio 2008\Projects\wfPopulateDataSets13_1\wfPopulateDataSets13_1\Form1.cs 42 40 wfPopulateDataSets13_1
    Error 15 { expected C:\Documents and Settings\e1enxshc\My Documents\Visual Studio 2008\Projects\wfPopulateDataSets13_1\wfPopulateDataSets13_1\Form1.cs 43 37 wfPopulateDataSets13_1
    Error 16 ; expected C:\Documents and Settings\e1enxshc\My Documents\Visual Studio 2008\Projects\wfPopulateDataSets13_1\wfPopulateDataSets13_1\Form1.cs 43 37 wfPopulateDataSets13_1

    I am completely lost in this project.  Please help and advise kindly.

    Thanks,
    Scott Chang 
    SHC
    Wednesday, August 26, 2009 6:06 PM