Using LINQ to write records in a SQL Database
You are here > Home > Blog >
Using LINQ to write records in a SQL Database
There is a lot on the web regarding LINQ and how simple it is to
use and create complex queries in code behind. However for a new
user LINQ can be rather confusing and a bit daunting until you get
used to the syntax.
Why use LINQ in the first place? What's the matter with the
other methods of accessing data? Well the answer is easy. I have
found that LINQ is so much easier to code and flexible to apply
queries using one line of code to check stock etc instead of
lengthy T SQL statements or Objects.
So today I am going to do a tutorial showing you how to write a
new record in a database. I am assuming you are using Visual Studio
2005 or better and have access to a SQL Server instance. I am
writing in VB.net but the syntax is similar in C#
First off you need to create the classes that allow you to talk
to the database tables etc. These classes store the connection
strings and properties that you will interact with later. Visual
Studio has a template that does the work for you.
Right Click Solution Explorer and Select Add New Item
Select LINQ to SQL Classes and give it a Name. A file called
venues.dbml is created in the directory App_Code. In this instance
I created this DataContext called venues. This brings up a white
screen with two boxes. A large box on the left and a smaller one on
the right.
Go to the Server Explorer Tab and navigate to your tables through
the hierarchy.
Next drag the tables that you would want to query and update
into the left large box. A box appears showing the table and all
the fields. If you have supporting tables drag those across too. If
there are relations between the tables these will be shown with
arrows.
It is at this point you could just save the table and go to the
next step but it's also worth noting what the right hand side box
does. You can use this to add your SQL stored procedures to the
DataContent. In the same way drag your stored procedures into this
box and data classes will be created for them too. We won't
be using these for now so save your object and close the
screen.
So you now have your data classes created and you are ready to
create a web form to add a new record.
We now need to create a web form with a bunch of textboxs and a
button that when posted back writes the data in the textboxes to
the
database fields.
Create a Web Form and some textboxes to capture the data to be
inserted into the database. In this case I have a series of
TextBoxes and an Editor control from the AJAX Control Toolkit. The
textboxes are not bound to anything at the moment so add a button
control and set the text property to SAVE.
Double click the button to bring up the event handler in code
behind.
First off import System.Linq into your class.
Imports System.Linq
Next in the event handler for the Save_Click event you
need to create a reference to the DataContext you just created.
This is done but creating a variable:
Dim db As New venuesDataContext
Note that filename you created called venues is appended
with DataContext. Next create a variable to hold the
new record.
Dim NewVenue As New Store
Next assign the values from your textboxes to the table
fields. Intellisense is your friend in this instance.
NewVenue.venue = venuename_txtbx.text
Continue assigning your textbox values. Now
we need to save the record. We do this by calling the
InsertOnSubmit method.
db (assigned to the venuesDataContext)
Stores (the table to which you want to insert a
record)
InsertOnSubmit (the method that does the magic)
NewVenue (the variable that is holding the data to
upload.
The syntax is:
db.stores.InsertOnSubmit(NewVenue)
Next submit the changes to the database and write the new
record.
Db.SubmitChanges()
That's it. I hope this has been of help.
Last Updated: Wednesday, May 25, 2011