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

Tom Knowlton
Tom Knowlton used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
What error are you getting.
Tom KnowltonWeb developer

Author

Commented:
My question is not because of an error I am getting.

My question is about "How To" do CRUD operations in LINQ.
Fernando SotoRetired
Distinguished Expert 2017

Commented:
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.
Expert Spotlight: Joe Anderson (DatabaseMX)

We’ve posted a new Expert Spotlight!  Joe Anderson (DatabaseMX) has been on Experts Exchange since 2006. Learn more about this database architect, guitar aficionado, and Microsoft MVP.

Retired
Distinguished Expert 2017
Commented:
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

Tom KnowltonWeb developer

Author

Commented:
Thank you!

Tom
Fernando SotoRetired
Distinguished Expert 2017

Commented:
Not a problem Tom, glad to help.
Tom KnowltonWeb developer

Author

Commented:
; )

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial