none
C# linq to SQL simple example please RRS feed

  • Question

  • Hello,

    I am new to C# linq to SQL, i have used datasets and dataadaptors to connect to a sql database and manipulate it such as insert, update, delete etc. however now i want to learn using LINQ to SQL using C#, however i have found many examples on the web that uses web applications and not form applications, wondering, and looking at the web applications, they still look complicated even though it says "simple LINQ to SQL"... simple ya right... but anyways wondering if someone can please write or give an example of how to use linq to sql using C# with maybe only 3 columns to keep it simple, so i can understand how it works, and also with web applications as well but also in simple 3 column format, the examples they use is like 8 columns with so many objects, which for me its best to start learning small and then building it up, and on msdn, i look at it and i see its missing stuff like the most important thing... the ENTIRE CODE!! also can you do the example not in console application but in windows form? but i could be wrong but if again could someone give me an example that would bee sOOOOOO awesome.

    Thank you

    Also,

    hope i dont have to create another thread for this simple question, is linq to SQL good practice or is used frequently in programming C# careers etc. more or is datasets and dataadaptors or other methods used more? which is better performance etc?

    thanks again
    Wednesday, September 2, 2009 6:02 PM

Answers

  • Hello All,

    FOUND IT!! made it work, the problem or work around was instead of using user_id to reference back to the database however i decided to use something else, i used first and last name to refernce back, i know the bad news if using that may result in multiple deleting however if you just and another column to reference i am sure you wont have that problem, again relieved, and still have some hair to pull later on... lol but i sadly may post again for the update part of LINQ TO SQL.

    Thanks all for the assist
    • Marked as answer by StevenIB Monday, September 7, 2009 1:11 AM
    Monday, September 7, 2009 1:11 AM

All replies

  • Great news i THINK I performed the LINQ to sql, but please let me know, i made a simple database called users, and a windows form with textboxes (which first will come later) but now i go to add a LINQ to SQL class which then i name it MYUserS.dbml, then i add in a class with the name called db, as shown below in the code, my main question is, did i successfully utilize or do LINQ to SQL successfully, if not, can you please tell me how to really do it, so far it seems to pull all the information to the listview successfully, i guess the next step for me is to have the information show up in the textboxes, then have a button to navigate through the database from the starting row to ending, then delete, insert, update etc. anyone know how to do that? most importantly though, did i do LINQ TO SQL successfully?

    Thanks

    attached is my code and SQL database

    Thanks again

    Code:

    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;
    
    namespace LINQSQLExample
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }
    
            MyUsersDataContext db = new MyUsersDataContext();
    
            public void stringquery()
            {
                var queryResults = from c in db.tblUsers
                                   select new { FirstN = c.First_Name, LastN = c.Last_Name, JobT = c.Job_Title };
                foreach (var peeps in queryResults)
                {
                    ListViewItem lv = new ListViewItem();
                    lv.Text = peeps.FirstN;
                    lv.SubItems.Add(peeps.LastN);
                    lv.SubItems.Add(peeps.JobT);
                    listView1.Items.Add(lv);
                }
            }
    
            private void Form1_Load(object sender, EventArgs e)
            {
                
            }
    
            private void btn_Submit_Click(object sender, EventArgs e)
            {
                stringquery();
            }
    
            private void btn_Close_Click(object sender, EventArgs e)
            {
                this.Close();
            }
        }
    }
    EEEK sorry i cant attach my project and database, can someone please still try and let me know if i did it right or not??

    Thanks, also what do i do now, how do i insert since its now in list, how do i go from list to sql database using LINQ? same with delete and update, how?

    Thanks again
    Wednesday, September 2, 2009 9:07 PM
  • hello,

    I am making progress, trying to follow this example below:

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

    however there is an error in my program saying argument 1: cannt convert from LINQSQLEXAMPLE.users' to LINQSQLEXAMPLE.tblusers' can anyone tell me what i am doing wrong? here is the code below:

    public void insertquery()
            {
                var queryResults = from c in db.tblUsers
                                   select new { FirstN = c.First_Name, LastN = c.Last_Name, JobT = c.Job_Title };
                listView1.Clear();
                users userinfo = new users(txtbFirstN.Text, txtbLastN.Text, txtbJobT.Text);
    
                db.tblUsers.InsertOnSubmit(userinfo);
    
                foreach (var peeps in queryResults)
                {
                    ListViewItem lv = new ListViewItem();
                    lv.Text = peeps.FirstN;
                    lv.SubItems.Add(peeps.LastN);
                    lv.SubItems.Add(peeps.JobT);
                    listView1.Items.Add(lv);
                }
                
            }
    Thank you
    Wednesday, September 2, 2009 10:22 PM
  • So it looks like you are selecting columns then creating user objects. LINQ to SQL does this for you automatically, just change the code to:

     var queryResults = from c in db.tblUsers
                                   select c;

    You then don't need the new users bit.

    What are you trying to do with db.tblUsers.InsertOnSubmit ?  It looks like you're trying to put users back into the database they just came from?

    [)amien

    Thursday, September 3, 2009 5:25 AM
    Moderator
  • for the insertion i found the method I need, which is this below:
    InsertOnSubmit
     
    however look at the example below:
     
    // Create a new Order object.
    Order ord = new Order
    {
        OrderID = 12000,
        ShipCity = "Seattle",
        OrderDate = DateTime.Now
        // …
    };

    // Add the new object to the Orders collection.
    db.Orders.InsertOnSubmit(ord);

    // Submit the change to the database.
    try
    {
        db.SubmitChanges();
    }
    catch (Exception e)
    {
        Console.WriteLine(e);
        // Make some adjustments.
        // ...
        // Try again.
        db.SubmitChanges();
    }

     
    Simple right? i know thats what i thought, but the problem is, where it says:
     
    // Create a new Order object.
    Order ord = new Order
    {
        OrderID = 12000,
        ShipCity = "Seattle",
        OrderDate = DateTime.Now
        // …
    };

    what object is the Order or what class is that? is it custom made, if so how do i make it to where
    it will have {} instead of ()? thats the key right there that i am stuck at. let me know
     
    Thanks

    Thursday, September 3, 2009 4:15 PM
  • Hi Steven,

     

    Order is the class in dbml which is mapping to the table in database.                                                                               

    This class can be generated or created by your own.

     

    Order ord = new Order(1200,”Seattle”)

    It will call the constructor with two parameters to initialize the instance.

     

    Order ord = new Order
    {
        OrderID = 12000,
        ShipCity = "Seattle",
    };

    It will call the getter and setter of each property to intizlize the instance.

     

    There is tutorial of LINQ to SQL for your reference:

    http://it-box.blogturk.net/wp-content/themes/it-box/files/LINQToSql.pdf

    It will make your study more efficient.

     

     

    Best Regards

    Yichun Feng

    Friday, September 4, 2009 6:25 AM
  • Yup you are right thank you, but now i have another problem, ok so now i got it to populate the listview which displays all the sql rows etc. but the problem is now, or what i am trying to do is, have the user select the user he/she wants to delete, by left clicking the row, which highlights the entire row, press the delete button, and removes it from the database, which i know how, but the code below is not working for some strange reason, i have the code below as said and have also the reason of it just in case anyone wants to see the entire code on how i insert it which can be found on msdn linq to sql, anyways please let me know how to delete by having the user select by left mouse clicking it to highlight in blue, then press the delete button to delete the user from the database, hope this makes sense.

    Thank you
    public Form1()
            {
                InitializeComponent();
                stringquery();
            }
    
            MyUsersDataContext db = new MyUsersDataContext();
            
    
            public void stringquery()
            {
                var queryResults = from c in db.tblUsers
                                   select new { FirstN = c.First_Name, LastN = c.Last_Name, JobT = c.Job_Title };
                foreach (var peeps in queryResults)
                {
                    ListViewItem lv = new ListViewItem();
                    lv.Text = peeps.FirstN;
                    lv.SubItems.Add(peeps.LastN);
                    lv.SubItems.Add(peeps.JobT);
                    listView1.Items.Add(lv);
                }
            }
    public void insertquery()
            {
                // we have two was of doing this, either this way...
                tblUser tableusers = new tblUser
                {
                    First_Name = txtbFirstN.Text, 
                    Last_Name = txtbLastN.Text, 
                    Job_Title = txtbJobT.Text
                };
    
                //or we can do:
                //tableusers.First_Name = txtbFirstN.Text;
                //tableusers.Last_Name = txtbLastN.Text;
                //tableusers.Job_Title = txtbJobT.Text;
                //whatever suprise me!!
    
                db.tblUsers.InsertOnSubmit(tableusers);
    
                db.SubmitChanges();
                listView1.Items.Clear();
    
                stringquery();
            }
    
    private void btn_Delete_Click(object sender, EventArgs e)
            {
                if (listView1.SelectedItems.Count > 0)
                {
                    MessageBox.Show(listView1.SelectedIndices[0].ToString());
                    int iddelete = listView1.SelectedIndices[0];
                    tblUser userdelete = db.tblUsers.Single(p => p.User_ID == iddelete);
                    db.tblUsers.DeleteOnSubmit(userdelete);
                    db.SubmitChanges();
                    listView1.Items.Clear();
                    stringquery();
    
                }
                else
                    MessageBox.Show("Please select user to delete");
    
                // the code below is not working either, thought i try a different approuch
                // but still does not work, if anyone can let me know how to get it to work
                // for either the code above or code below
                // thank you
    
                    
                /*MessageBox.Show(listView1.SelectedIndices[0].ToString());
                    var deleteuser = from user in db.tblUsers
                                     where user.User_ID == listView1.SelectedIndices[0]
                                     select user;
                    foreach (var userbyebye in deleteuser)
                    {
                        if (listView1.SelectedItems.Count > 0)
                        {
                            db.tblUsers.DeleteOnSubmit(userbyebye);
                            listView1.Items.RemoveAt(listView1.SelectedIndices[0]);
                            break;
                        }
                    }
                    db.SubmitChanges();
                listView1.Items.Clear();
                stringquery();
                 */
            }
    Friday, September 4, 2009 7:50 PM
  • I found the problem which i dont understand or how to solve it, its regarding where it says:

    int iddelete = listView1.SelectedIndices[0];

    which there is a number there, but then when it comes to

    tblUser userdelete = db.tblUsers.Single(p => p.User_ID == iddelete);

    there is nothing in iddelete? why is that, and what could i do to get the row/variable in there?

    Thanks
    Friday, September 4, 2009 8:41 PM
  • also an FYI the runtime error i get at this line below is:

    tblUser userdelete = db.tblUsers.Single(p => p.User_ID == iddelete);

    which the error message says:

    sequence contains no element.


    but if you look at the code, it should...shouldnt?

    thanks let me know the problem because i cant figure it out :(
    Friday, September 4, 2009 8:45 PM
  • Ok here is the update: i thought i got it, but no, for some strange reason look at the code below which i will explain whats happening and what i would like to happen that could resolve this delete issue:

    private void btn_Delete_Click(object sender, EventArgs e)
            {
                if (listView1.SelectedItems.Count > 0)
                {
                    MessageBox.Show(listView1.SelectedIndices[0].ToString());
                    int number = listView1.SelectedIndices[0];
                    var queryresults = (from u in db.GetTable<tblUser>()
                                        where u.User_ID == number
                                        select u).SingleOrDefault();
    
                    MessageBox.Show(queryresults.User_ID.ToString());
                    db.tblUsers.DeleteOnSubmit(queryresults);
                    db.SubmitChanges();
                    listView1.Items.Clear();
                    stringquery();
                }
                else
                    MessageBox.Show("Please select user to delete");
    }            
    Ok so as you can see i tried a different approach, first to test the selectedindices i made a messagebox appear to tell you which number you have selected, which it does, then i made the selectedindices = int number, which looks fine, but right when you get to the queryresults part variable, thats where i set the number to = the user.id in the database, so whatever you click on will equal to the number in the user_ID so it will pull that row, and using the query it will select the entire row from the database, from there i wanted to show in the next messagebox to see if it had not lost the user_ID that you have selected which IT DOES!!! it disappears, becomes null!!!! then after that the error message comes at the messagebox.show(queryresults.user_ID.Tostring()); saying nothing there in user_ID... BUT IT WAS THERE IT WAS!! (Pulls his hair out), what could be the problem, why its not holding that variable and why wont it query right? someone please help :( hope this helps that can lead to the resolution of this post.

    Thank you
    Friday, September 4, 2009 10:18 PM
  • Hi Steven

     

    Your problem is probably caused by this:

    Original:

    DB               Combobox

    id  item         index   item

    0    A            0       A

    1    B            1       B

    2    C            2       C

    3    D            3       D

     

    After deleting the third record:

    DB               Combobox

    id  item         index   item

    0    A            0       A

    1    B            1       B

    3    D            2       D

     

    You can see that the index and id is not related after deleting once.

    To fix it, you can set combobox’s valuemeber as the id and the displaymember as itemname.

     

    Does this works for you?

     

    Best Regards

    Yichun Feng

    Saturday, September 5, 2009 3:06 AM
  • Hello and thanks Yichun for the reply, that makes sense, i guess i can try using this example below, or try making it work the way i would like, which is have the user select the user to delete in the listview then press the delete button, i saw this on MSDN and tried it out:

    C# Copy Code // Access the first element in the Orders collection.
    Order ord0 = existingCust.Orders[0];
    
    // Access the first element in the OrderDetails collection.
    OrderDetail detail0 = ord0.OrderDetails[0];
    
    // Display the order to be deleted.
    Console.WriteLine
        ("The Order Detail to be deleted is: OrderID = {0}, ProductID = {1}",
        detail0.OrderID, detail0.ProductID);
    
    // Mark the Order Detail row for deletion from the database.
    db.OrderDetails.DeleteOnSubmit(detail0);
    then i go ahead and try it out on my code:

    int number = listView1.SelectedIndices[0];
                    var queryresults = (from u in db.GetTable<tblUser>()
                                        where u.User_ID == number
                                        select u).SingleOrDefault();
    
    //error on this code below:
    
    tblUser userdelet = queryresults.User_ID[0];
    
    db.tblUsers.DeleteOnSubmit(queryresults);
    cant see why i get the error message where it says on the comment above saying:

    Cannot apply indexing with [] to an expression of type 'int'

    why is that it works fine or msdn puts that it will work with [] but when i try it it doesnt? any ideas that would be great. i like the idea that Yichun_Feng suggested but i want to go beyond and make this application a little more advanced, if all else i will try Yichun_feng advice, but Yichun if you dont mind can you post an example of how to set the combobox valuemembers as the id and the displaymemeber as itemname?

    thanks
    Sunday, September 6, 2009 4:58 PM
  • Hello,


    Sorry for all the posts however its just so i can let everyone know how much i am trying.... with no success.... i am starting to think the only way to delete from using LINQ to SQL is by Yichun_Feng's idea (also can you post your idea on how to do that Yichun please??) but i am also starting to think LINQ TO SQL sucks because it does not give you that expansion of ways to delete and update the database unlike using dataset to dataadaptors, but anyways here is the code on what i am using to try and accomplish however still comes up on this line:

    tblUser empremove = (from user in db.tblUsers
                                     where user.User_ID == int.Parse(usertodelete.ToString())
                                     select user).Single();
    and it says sequence contains no elements, please please is there anything or anyone that can solve my delima? again i am sadly starting to think LINQ TO SQL sucks and the only way to delete is using Yichun's idea (and again sorry to sound like a broken record, just frustrated) if Yichun can type out the example for all of us to see how to do it. but here is the entire code for everyone to see what i am doing.

    Thanks in advance

    private void btn_Delete_Click(object sender, EventArgs e)
            {
                string usertodelete = listView1.SelectedIndices[0].ToString();
                tblUser empremove = (from user in db.tblUsers
                                     where user.User_ID == int.Parse(usertodelete.ToString())
                                     select user).Single();
                db.tblUsers.DeleteOnSubmit(empremove);
                db.SubmitChanges();
                stringquery();
             }
    Sunday, September 6, 2009 9:37 PM
  • Hello All,

    FOUND IT!! made it work, the problem or work around was instead of using user_id to reference back to the database however i decided to use something else, i used first and last name to refernce back, i know the bad news if using that may result in multiple deleting however if you just and another column to reference i am sure you wont have that problem, again relieved, and still have some hair to pull later on... lol but i sadly may post again for the update part of LINQ TO SQL.

    Thanks all for the assist
    • Marked as answer by StevenIB Monday, September 7, 2009 1:11 AM
    Monday, September 7, 2009 1:11 AM