using linq to sql to add and delete data from sql database

In this article, we will see how to use LINQ to SQL to retrieve, add and delete data from the sql server database. To do this we need to create a LINQ to SQL Class. This class represents the Sql server database. We will create a windows application and accomplish the following tasks.

Retrieve data from the SQL server database and bind the data to the Listbox control in the form_Load method of windows form.

Delete data from the SQL server database
Add new data to the SQL server database

We will assume that we have already created a database SalesOrderManagement and a Customer table in the database SalesOrderManagement. Now that we have the database, we need to create the Class.

After the above step, we get two classes, one which represents database - DataContext class (in this example SalesDBDataContext ) and the other is the Customers class which represents the table in the database. The DataContext class is used to interact with the database.

The next step is to create a windows form and add the functionality to add, edit and delete data from the customers table. Create an interface as shown below.



Let us now add the functionality to add new records to the database. Double-click on the button Add creates the event handler for the click event.

Data is accessed using the DataContext object. As we do in every program, we will first create an instance of the DataContext class. The below code initializes the data context using the connection string in the app.config file.

< connectionStrings>
< add name="WindowsFormsApplication1.Properties.Settings.SalesOrderManagementConnectionString" 
connectionString="Data Source=SYS2;Initial Catalog=SalesOrderManagement;
Integrated Security=True" providerName="System.Data.SqlClient"/>
< /connectionStrings>

The following code is for the Form_Load event. In this event we retrieve customer id and name from the customers table and bind the data to the listbox control.

private void Form1_Load(object sender, EventArgs e)
{
 SalesDBDataContext dataContext = new SalesDBDataContext();
 var custQuery =
  from c in dataContext.Customers
  select new { CustomerID = c.CustomerId, CustomerName = c.Name };
//Bind the ListBox
listBox1.DataSource = custQuery.ToList();
listBox1.DisplayMember = "CustomerName";
listBox1.ValueMember = "CustomerID";
}

To add the functionality to the add button, double click on the add button and write the following code.

        private void add_button_Click(object sender, EventArgs e)
        {
            SalesDBDataContext dataContext = new SalesDBDataContext();
            Customer cust = new Customer();
            cust.Name = textBox1.Text;
            cust.Address = textBox1.Text;
            dataContext.Customers.InsertOnSubmit(cust);
            dataContext.SubmitChanges();
            textBox1.Text = "";
            textBox2.Text = "";
            textBox1.Focus();
       }

In the above code we have used InsertOnSubmit() method to add data. And we have used SubmitChanges() method of the dataContext class to commit changes back to the database. The newly added data is committed to the database only after we call the SubmitChanges() method.

To delete a selected record in a listbox control from the database, write the following code. As shown in Fig:1, the listbox control displays all the customers in a customer table. To delete a record, a user needs to select the record in the list box control and then press the delete button. Double click on the delete button to add the functionality.

private void delete_button_Click(object sender, EventArgs e)
{
	SalesDBDataContext dataContext = new SalesDBDataContext();
	MessageBox.Show(listBox1.SelectedValue.ToString());
	var selectedID = Convert.ToInt16(listBox1.SelectedValue);
	Customer toDelete = 
    dataContext.Customers.Single(p => p.CustomerId == selectedID);
	dataContext.Customers.DeleteOnSubmit(toDelete);
	dataContext.SubmitChanges();
}

In the above code, we have written a query to retrieve the row to be deleted. The DeleteOnSubmit method marks the row for deletion and SubmitChanges() method submits the changes to the database.