Linq to Sql for Data Navigation

As every one knows, LINQ stands for Language Integrated Query.

LINQ has its own set of namespaces and classes that provide us with various querying facilitites against the various data sources.

This article is about using LINQ to provide data navigation in Windows applications.

About this application

1. DataContext Class represents the entry point to the SQL Server database from the Windows form.

2. A Class file defining the class and properties mapping to the table and the columns of the database using the relevant attributes.

3. The user interface that will interact with the database using the LINQ classes

FORM -> LINQ -> Database

A reference to the System.Data.Linq.dll has to be added.

This is the snapshot of the exe




The procedure is as follows

1. Create one table in SQL Server database known as emp,

Create table emp
(eno int primary key,
ename varchar(10),
Salary int)
Insert some records

2. Open a Windows Application.

3. Add a class file and the reference to System.Data.Linq.dll

4. Write this coding in the class file

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Linq.Mapping;

namespace linqnavigation
{

[Table]
class emp
{
[Column]
public int eno
{
get;
set;

}
[Column]
public string ename
{
get;
set;

}
[Column]
public int salary
{
get;
set;

}

}
}

5. Create the User Interface and write this code in Form1.cs file

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.Linq;

namespace linqnavigation
{
public partial class Form1 : Form
{
//Create an object of DataContext class
DataContext d = new DataContext("server=.;uid=sa;pwd=1234;database=hh");


public Form1()
{
InitializeComponent();
}

private void Form1_Load(object sender, EventArgs e)
{
//display full data uaing LINQ query
gridbinding();




//call the textbox binding method

bindings();
curposition();




}

//get current record position

void curposition()
{
int a = bindingSource1.Position;
var q = d.GetTable<emp>().Count();
label3.Text = (a+1) + " "+"of" + " "+q+" "+"records";

}

//LINQ query
void gridbinding()
{

var q = from p in d.GetTable<emp>() select p;
bindingSource1.DataSource = q;
dataGridView1.DataSource = bindingSource1;
}

//displays the data in textboxes

void bindings()
{
textBox1.DataBindings.Add("Text", bindingSource1, "eno");
textBox2.DataBindings.Add("Text", bindingSource1, "ename");
textBox3.DataBindings.Add("Text", bindingSource1, "salary");
}



private void button1_Click(object sender, EventArgs e)
{
//search specific record
try
{
if (textBox4.Text == "")
{
MessageBox.Show("please enter the record number");
textBox4.Focus();

}
else
{
int a = Convert.ToInt32(textBox4.Text);
if (a > d.GetTable<emp>().Count())
{
MessageBox.Show("row number exceeded");
textBox4.Text = "";

}
else
{
var p = d.GetTable<emp>().Skip(a - 1).Take(1);
dataGridView1.DataSource = p;
foreach (var z in p)
{
textBox1.Text = z.eno.ToString();
textBox2.Text = z.ename.ToString();
textBox3.Text = z.salary.ToString();
}



}
textBox5.Text = "";
textBox6.Text = "";
button4.Enabled = false;
button5.Enabled = false;
button6.Enabled = false;
button7.Enabled = false;
label3.Text = "";
label3.Enabled = false;
}
}

catch (Exception ex)
{
MessageBox.Show(ex.Message);
textBox4.Text = "";


}



}

private void button3_Click(object sender, EventArgs e)
{
gridbinding();
textBox4.Text = "";
button4.Enabled = true;
button5.Enabled = true;
button6.Enabled = true;
button7.Enabled = true;
label3.Enabled = true;
textBox4.Text = "";

textBox5.Text = "";
textBox6.Text = "";
curposition();



}

private void button2_Click(object sender, EventArgs e)
{
//range of records
try
{
if (textBox5.Text == "" | textBox6.Text == "")
{
MessageBox.Show("please enter the range");
}
else
{
int s = Convert.ToInt32(textBox5.Text);
int p = Convert.ToInt32(textBox6.Text);
int r = p - s;

var q = d.GetTable<emp>().Skip(s - 1).Take(r + 1);


dataGridView1.DataSource = q;

textBox1.Text = "";
textBox2.Text = "";
textBox3.Text = "";
textBox4.Text = "";
button4.Enabled = false;
button5.Enabled = false;
button6.Enabled = false;
button7.Enabled = false;
label3.Text = "";
label3.Enabled = false;

}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
textBox5.Text = "";
textBox6.Text = "";




}

}

private void button4_Click(object sender, EventArgs e)
{
bindingSource1.MoveFirst();

textBox4.Text = "";
curposition();
}

private void button5_Click(object sender, EventArgs e)
{
bindingSource1.MoveNext();

textBox4.Text = "";
curposition();

}

private void button6_Click(object sender, EventArgs e)
{
bindingSource1.MovePrevious();

textBox4.Text = "";
curposition();
}

private void button7_Click(object sender, EventArgs e)
{
bindingSource1.MoveLast();

textBox4.Text = "";
curposition();
}
}
}

6. Execute the application

Navigation features

1. First,Next,Previous,Last records buttons
2. Search for specific record
3. Search for some range of records (example: data from record number 2 to record number 5)

Note

First,next,previous,last record buttons will be disabled when you click specific record and range of record buttons.

To enable them, you have to click the Full Table button.

All the best.