C# linq to SQL

[Siten0308]

Hello,

This may be a stupid question but i want to ask anyways plus if all works well, i am going to turn this into a tutorial for all to show how easy it is to use LINQ to SQL compared to my other example which i will also post using ADO.Net by using dataadaptors to datasets. But anyways on with the question,

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();
        }
    }
}

[ArekBulski]

Hmm, maybe add an Insert statment to it, and then you find out?

[Siten0308]

Also another question i wanted to ask was do i need to close a connetion or anything from my program to the SQL database when using LINQ?

[ArekBulski]

My refactored SQL program (from part 3) opens a SqlConnection but never closes it. And it works just fine. I think it's not needed.

[Siten0308]

Hello,

I am uploading my project with database so everyone can check it out, i did make some progress and found this example below from MSDN:

// 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();
}

however i dont know what class or object Order ord = new order{

};

i dont know what that is?? i just made one myself and now i am trying to figure out how to connect each column with the textbox, such as the example above: OrderID = 2000, shipCity = "Seattle" etc? any ideas?

[Siten0308]

whooops sorry here is the code attached

[ArekBulski]

I believe Order is a generated LINQ to SQL class. I found a pretty long (too long) tutorial about it, too. LINQ to SQL (Part 2 - Defining our Data Model Classes) - ScottGu's Blog

[Siten0308]

YAY got it working, i will create a thorough tutorial and step by step on how to use LINQ to SQL, but i have read some blogs and articles that concerned me saying that using LINQ to SQL is not a great thing, some even go as far as telling me that .net is abandoning it, but thats just posts and stuff, however linq to sql is still a very good and should be learnred anyways, they say to stay with using datasets and dataadaptors since its soo much easier to manipulate data to sql database etc. which if anyone cares i will write a nice tutorial on that as well, but i will have to give credit to a certain website for teaching me since it wasnt my own learning. thank you anyways for trying to help all, but again i will make sure i post for everyones info.

Thanks again

[Siten0308]

Hello,

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;
                    }
                }

[Siten0308]

just an update:

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?

Also a 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