INSERT into SQL Server table using rows of a DataGridView - LINQ version in C#?

I want to do the same thing as the code below -- but using LINQ in C# ( LINQ to SQL ) to do the iterating and inserting.  Please provide exact C# code.  The broader question is how to do basic CRUD operations in LINQ.  I do have a context called "Model1":

I want just very simple statements ... nothing complex.  Just basic CRUD operations.

I managed to get a SELECT working .. but I am not sure how to do an INSERT or UPDATE.


 using (var db = new Model1())
 {
//do a select
var query = from b in db.Customers
                            orderby b.CustID
                            select b;

                //    //Console.WriteLine("All blogs in the database:");
                foreach (var item in query)
                {
                    Console.WriteLine(item.CustID);
                }
}

 using (var db = new Model1())
 {
// do the insert 
}

 using (var db = new Model1())
 {
// do the update
}

 using (var db = new Model1())
 {
// do the delete 
}

Open in new window


private void btnInsert_Click(object sender, EventArgs e)
{
    foreach (DataGridViewRow row in dataGridView1.Rows)
    {
        string constring = @"Data Source=.\SQL2008R2;Initial Catalog=AjaxSamples;Integrated Security=true";
        using (SqlConnection con = new SqlConnection(constring))
        {
            using (SqlCommand cmd = new SqlCommand("INSERT INTO Customers VALUES(@CustomerId, @Name, @Country)", con))
            {
                cmd.Parameters.AddWithValue("@CustomerId", row.Cells["Id"].Value);
                cmd.Parameters.AddWithValue("@Name", row.Cells["Name"].Value);
                cmd.Parameters.AddWithValue("@Country", row.Cells["Country"].Value);
                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();
            }
        }
    }
    MessageBox.Show("Records inserted.");
}

Open in new window

LVL 5
Tom KnowltonWeb developerAsked:
Who is Participating?
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.

Pawan KumarDatabase ExpertCommented:
What error are you getting.
0
Tom KnowltonWeb developerAuthor Commented:
My question is not because of an error I am getting.

My question is about "How To" do CRUD operations in LINQ.
0
Fernando SotoRetiredCommented:
Hi Tom;

You state in your post the following, "LINQ in C# ( LINQ to SQL )", In your last question you where working with Linq to Entity Framework. Which technology is this question for, Linq to SQL ro Linq to Entity Framework.

Also initially will the DataGridView be loaded from the database or are you creating all the records in the DataGridView in the application and then update the database?

Also the database AjaxSamples is that a sample database you got off the web? If yes can you post the link.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Download your Guide
Fernando SotoRetiredCommented:
Hi Tom;

The below code snippet loads the DataGridView from the Customer table using Linq to SQL which will return a IQueryable collection and assigning it to the BindingSource object. Connecting the data source in this way will allow the user to insert a new record by going to the last row in the DataGridView and entering the new data in the empty row. It will allow the user to update fields in any column by just changing its value. And by selecting a complete row and then pressing the delete key on the keyboard will delete that record. All the changes stated above will only take effect after executing the db.SubmitChanges() method. 
namespace WindowsFormsApp
{
    public partial class Form1 : Form
    {
        // Linq to SQL DataContext 
        NWDataClassesDataContext db = new NWDataClassesDataContext();
        // Binding source which manages the collection in DbContext
        BindingSource bsCustomers = new BindingSource();

        public Form1()
        {
            InitializeComponent();

            // Query the database for the records in the Customer table and return to application
            // and assign it to the binding source object
            bsCustomers.DataSource = db.Customers;
            // Assign the binding source to the DataSource of the DataGridView
            dataGridView1.DataSource = bsCustomers;
            // If the primary key of the Customer table is a Auto Increment field in db then uncomment last line in this method
            // and change the column index to the correct index in your table. This should be done because modifing the 
            // primary key on an existing record or adding a value to a record to be inserted will throw an exception.
            // dataGridView1.Columns[0].Visible = false;

        }

        private void btnUpdate_Click(object sender, EventArgs e)
        {
            db.SubmitChanges();
        }

    }
}

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
Tom KnowltonWeb developerAuthor Commented:
Thank you!

Tom
0
Fernando SotoRetiredCommented:
Not a problem Tom, glad to help.
0
Tom KnowltonWeb developerAuthor Commented:
; )
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
SQL

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.