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.
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.
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
}
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.");
}
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
My question is not because of an error I am getting.
My question is about "How To" do CRUD operations in LINQ.
My question is about "How To" do CRUD operations in LINQ.
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.
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.
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.
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();
}
}
}
Premium Content
You need an Expert Office subscription to comment.Start Free Trial