C# tutorial - linq to manipulate SQL Server database


Using linq to read, add, edit, and delete data in database

In the previous post, you learned how to read, add, edit, and delete elements of the xml file by using Linq. In this tutorial, you will learn to connect to SQL Server database, read data in its table, and manipulate data in that table.

Here is the orginal table in SQL Server 2008 database called test. The name of the example table is TblTest. The table has three columns: ID, Name, and Sex.

TblTest table in SQL Server 2008

Read data from TblTest table

Before you can read or manipulate a table of SQL Server database, you need to create a class to map the table. This is the Student class used to map to the TblTest table. The Student class has three properties: id, name, and sex. The id, name, and sex properties are mapped to the ID, Name, and Sex columns respectively.

[Table(Name = "TblTest")]
class Student
{

[Column(IsPrimaryKey = true)]
public string id{get;set;}
[Column(Name = "Name")]
public string name{get;set;}
[Column(Name = "Sex")]
public string sex{get;set;}


}

This is the C# code to connect to and read data from the TblTest table:

//create connection string to local sql server database
string constr="Server=(local); Database=test; User=dara; Password=dara;";
//Select the database
DataContext db = new DataContext(constr);
//create query to read the data in the table
var testtbl = from test in db.GetTable<Student>()
select test;
//display the data on the screen
foreach (var row in testtbl)
{
Console.WriteLine(row.id+"\t"+row.name+"\t"+row.sex);

}

result got from TblTest database by Linq

Add a new record/row to the TblTest

To add a new record or row of data to the TblTest table, you need to create a Student object that stores id, name, and sex of a student. Then you will use the InsertOnSubmit method to add the new object to the database table.

Here is the example to add the Student object stnew to the TblTest table.

//Create Student object

Student stnew = new Student();
stnew.id = "2230";
stnew.name = "ChanD";
stnew.sex = "M";

//Add the object to the table

db.GetTable<Student>().InsertOnSubmit(stnew);

//Save change to the database

db.SubmitChanges();

 

Update a record of the TblTest table

Sometimes, you might want to update data in a table. Linq also allows you to do this task easily. The code below will change the student's sex that has id 333 from F to M.

//select student that has id 333

var testtbl = from test in db.GetTable<Student>()
where test.id=="333"
select test;

//update sex to F

foreach (var row in testtbl)
{

row.sex = "F";

}

db.SubmitChanges();

Delete a record/row from the TblTest table

To delete a record or row from the TblTest table, first you need to create a Student object corresponding to the record in the table. Then supply this object to the DeleteOnSubmit method.

//create object to represent the row to delete

Student sttodelete = new Student();
stor.id = "102";
stor.name = "SOK";
stor.sex = "M";

//delete the row and update the database

db.GetTable<Student>().DeleteOnSubmit(sttodelete);

//save change to the database

db.SubmitChanges();


Comments

CAPTCHA image




This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages:
C, C++, C#, Java, VB.NET, Python, VBA,PHP & Mysql, SQL, JSP, ASP.NET,HTML, CSS, JQuery, JavaScript and other applications such as MS Excel, MS Access, and MS Word. However, we don't guarantee all things of the web are accurate. If you find any error, please report it then we will take actions to correct it as soon as possible.