Using LINQ to SQL to Add/Delete from SQL Database in C#
In this tutorial, we will be looking at one flavor of Microsoft’s new introduction to the .NET Framework, LINQ to SQL. LINQ (Language-Integrated Query) comes in a variety of forms, including LINQ to XML and LINQ to Objects. We will be creating a LINQ to SQL Class to represent our SQL database, and will be dealing with LINQ statements instead of working directly with SQL.
Before we begin coding, we will need to start a new project and create our database. Open Visual Studio, and then goto File > New Website. Then choose your location and give the website a name – LINQtoSQL will do. When you click Ok, Visual Studio will create the necessary files and we will eventually be provided with our ASPX page. To the right, we have our Solution Explorer. Right-click the App_Data folder and choose Add New Item, then choose SQL Server Database. Give it a name.
Now we have our database ready, we will add some sample data. Right-click the table in the Server Explorer and choose Show Table Data. Now let’s add some sample data – notice we don’t need to complete the id column as it is auto-generated:
This is where LINQ creates its class based on our input. We can add our database tables and stored procedures to this designer and modify the relationships between the data, and then Visual Studio will create the class or classes based upon the layout.
For this example, we only have one table to work with, so the design is going to be very simple. From Server Explorer, drag the table onto the main area of the designer. Our table should be graphically represented like so:
Now we can save the changes we made and close the designer. Let’s now drag a LinqDataSource control onto the ASPX page, and we can use the Smart Tag in design view to configure the data source. We want to use the DataClassesDataContext we just created:
Yes, it is possible to find a good web host. Sometimes it takes a while to find one you are comfortable with. After trying several, we went with Server Intellect and have been very happy thus far. They are by far the most professional, customer service friendly and technically knowledgeable host we’ve found so far.
<form id="form1" runat="server">
<asp:textbox id="txtName" runat="server" />
<asp:textbox id="txtCity" runat="server" />
<br /><asp:DataGrid ID="DataGrid1" runat="server" DataSourceID= "LinqDataSource1" Width="334px"><asp:button id="butAdd" runat="server" text="Add" />
<asp:linqdatasource id="LinqDataSource1" runat="server" contexttype name="DataClassesDataContext"
enabledelete="True" enableinsert="True" tablename="tblPeoples">
Server Intellect assists companies of all sizes with their hosting needs by offering fully configured server solutions coupled with proactive server management services. Server Intellect specializes in providing complete internet-ready server solutions backed by their expert 24/365 proactive support team.Now if we right-click our Default.aspx in the Solution Explorer and choose View in Browser, we will be presented with an input form and a neat table displaying the data from the database. This is similar to using the SqlDataSource and DataGrid or GridView controls, but LINQ has so much more to offer and makes it a lot easier for us to communicate with and manipulate data, as we will see in the rest of this article.
protected void butAdd_Click(object sender, EventArgs e)
string connectionString = ConfigurationManager.ConnectionStrings["D atabaseConnectionString"].ToString();
DataClassesDataContext dataContext = new DataClassesDataContext( connectionString);
tblPeople tblP = new tblPeople();
tblP.name = txtName.Text;
tblP.city = txtCity.Text;
We can now move onto deletions. We want to add the ability to delete any record in the database. We will do this by populating a DropDownList with the names in the database, and then use a delete button to delete the selected record. We can use the same DataSource for the DropDown, so the code would look something like this:
<asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True"
DataSourceID="LinqDataSource1" DataTextField="name" DataValueField="id">
<asp:Button ID="butDelete" runat="server" Text="Delete" onclick=" butDelete_Click" />
protected void butDelete_Click(object sender, EventArgs e)
DataClassesDataContext db = new DataClassesDataContext();
var selectedID = Convert.ToInt16(DropDownList1.SelectedValue);
tblPeople toDelete = db.tblPeoples.Single(p => p.id == selectedID);
db.tblPeoples.DeleteOnSubmit( toDelete );
Now we can run our web application and be able to view, add and delete records from the SQL database without writing any SQL statements. Congratulations! We have learned that we can easily use LINQ to interact with a SQL database, making it easy for us to add new records to the database as well as deleting records from it. For the full source file click “download source” below, have a good one!