LINQ To SQL
Today in this article I will teach you the most interesting and new features of the C# programming language, LINQ-To-SQL.
LINQ To SQL
First of all I will introduce you people to LINQ, then I will tell you how to work with LINQ-To-SQL.
Introduction
Language Integrated Query (LINQ) is a new feature introduced by Microsoft in .NET Framework 3.5. It used for querying databases and local collections and brings static type safety to database queries. It's very simple and well organized. A universal querying language that can work across SQL, XML, local collections and third-party APIs such as SharePoint.
Basically, what LINQ provides is lightweight programmatic data integration. This is such a big offer because currently data is everything and in the future I will write a complete article on Big Data.
Now I will tell you how to work with LINQ-To-SQL.
Step 1:
- Open your Visual Studio and make a new console project with any name.
- Then open Server Explorer then make a new database with some name and make a new table add some columns in the table.
- Then open your project and go into Solution Explorer and right-click on the project and then click "Add" -> "New item...".
- There search LINQ-To-SQL and add this file and press the "OK" button.
- Then you will see a blank file; on that file you will drag your table in the SQL Server database file onto the LINQ-To-SQL .dbml file extension.
Step 2: Here I will declare some class data members in the class.
class Program
{ // this is my program class
private int id;
private string name;
private string fname;
private int age;
private string sem;
}
Step 3: Now I will tell you how to insert data into the database using a LINQ-To-SQL query.
INSERT Data
Here is the insert data function in which we will insert our data into the database.
public void insert()
{
// these are the class data members through we will send our objects data in the database
Console.WriteLine("Enter id");
id = Convert.ToInt32(Console.ReadLine());
Console.WriteLine("Enter name");
name = Console.ReadLine();
Console.WriteLine("Enter father name");
fname = Console.ReadLine();
Console.WriteLine("Enter age");
age = Convert.ToInt32(Console.ReadLine());
Console.WriteLine("Enter semester");
sem = Console.ReadLine();
// this is the data context class the main class which handles all the functionality in this will pass the connection string of our database file.
LTSDataContext LTS = new LTSDataContext(@"Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\Users\Ehtesham Mehmood\Documents\Visual Studio 2012\Projects\ConsoleApplication5\ConsoleApplication5\SDatabase.mdf;Integrated Security=True;Connect Timeout=30");
// this is the table class which we drag on our linq to sql file
Student objStudentTable = new Student();
objStudentTable.Id = id;
objStudentTable.Name = name;
objStudentTable.Father_Name = fname;
objStudentTable.Age = age;
objStudentTable.Semester = sem;
LTS.Students.InsertOnSubmit(objStudentTable); // this is built in function.
LTS.SubmitChanges();// here is the final query will run in the data context class.
}
Step 4: Display Data
void Display()
{
LTSDataContext LTS = new LTSDataContext(@"Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\Users\Ehtesham Mehmood\Documents\Visual Studio 2012\Projects\ConsoleApplication5\ConsoleApplication5\SDatabase.mdf;Integrated Security=True;Connect Timeout=30");
var selectQuery = from s in LTS.Students
select s;
foreach (Student s in selectQuery)
{
Console.WriteLine(s.Id + "\t" + s.Name + "\t" + s.Father_Name + "\t" + s.Age + "\t" + s.Semester);
}
}
Step 5: Delete Data
void Delete()
{
int iid = 0;
Console.WriteLine("Enter the Id of the student u want to delete?");
iid = Convert.ToInt32(Console.ReadLine());
LTSDataContext LTS = new LTSDataContext(@"Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\Users\Ehtesham Mehmood\Documents\Visual Studio 2012\Projects\ConsoleApplication5\ConsoleApplication5\SDatabase.mdf;Integrated Security=True;Connect Timeout=30");
var delete = from p in LTS.Students
where p.Id == iid
select p;
LTS.Students.DeleteAllOnSubmit(delete);
LTS.SubmitChanges();
Student objStudentTable = LTS.Students.Single(c=> c.Id == iid);
}
Step 6: Update Data
void update()
{
LTSDataContext LTS = new LTSDataContext(@"Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\Users\Ehtesham Mehmood\Documents\Visual Studio 2012\Projects\ConsoleApplication5\ConsoleApplication5\SDatabase.mdf;Integrated Security=True;Connect Timeout=30");
Student objStudentTable = new Student();
int iid = 0;
Console.WriteLine("Enter the Id of the student u want to update ?");
iid = Convert.ToInt32(Console.ReadLine());
Console.WriteLine("Enter the new name of the student u want to update?");
string up = (Console.ReadLine());
var update = from s1 in LTS.Students
where s1.Id == iid
select s1;
foreach (var v in update)
v.Name = up;
LTS.SubmitChanges();
}
Main function
static void Main(string[] arg)
{
Program p1 = new Program(); // creates object
p1.insert();
p1.Display();
p1.Delete();
p1.update();
Console.ReadKey();
}
I will also attach the entire code file so you can download it.