By using Single LINQ Query retrieve foriegn key table column

In this article, I will explain you, How to retrieve data from two joined table using single LINQ Query. Here we have two joined table in mentioned diagram. Both tables are joined from dept_no, so if you want to retrieve record from both table like:

Retrieve emp_id and emp_name who belongs to IT department.

employeeEntities empl = new employeeEntities();

            var query = from g in empl.emps
                        join m in empl.depts on g.dept_no equals m.dept_no
                        where m.dept_name == "IT"
                        select new
                        {
                            Emp_Name = g.emp_name,
                            Emp_ID = g.emp_id
                        };
               
            dataGridView1.DataSource = query.ToList();
In this code, empl is the context object through which we can access columns of both tables. emp is the foreign key table map with dept table using dept_no column. Emp_Name and Emp_ID is the reference name , which are displayed on table. Bind the query result with the dataGridView. 

Filter data in datagridview according to combobox in c#

As we have previously studied that datagridview is used for display rows and columns of data in a grid format in windows form. Mostly data are previously defined which are to be shown in datagridview. Sometimes we have to change the data on basis of some conditions.

In previous post we have learnt to bind a datagridview with search option and in else case all the data from database will bind to datagridview in C# language. In this post we will learn to bind datagridview with the selected index changed event of combobox in C#.

The selection changed event of combobox occurs when the value of selected index property changes. We are going to change our data of datagridview according to that changed value.

Let’s create a student class, which will be used to bind our datagridview, having some properties like below:

public class Student

{

public string Name { get; set; }

public int Age { get; set; }

public string Address { get; set; }

public string Branch { get; set; }

}

Create a new list of student that will be the data source of datagridview and insert some records in that list having branch CS or IT as I have inserted in my example using C# language. Set this list as a data source of datagridview.

dataGridView1.DataSource = studentList;

Now when we run this project all the records will be shown in datagridview. We want to change the data of datagridview according to selection changed event. So write the below code in selection changed event of combobox

if (comboBox1.SelectedItem != null)

{

string branch = comboBox1.Text;

var students = studentList.Where(a => a.Branch.Equals(branch));

dataGridView1.DataSource = students.ToList();

}

Run this project now and change the selection value of combobox to CS and we will show the data of datagridview have been changed as following image:

Change the selection value to IT and the data will be changed as following image:

Go for example

Find a control in windows forms

When we drag-n-drop controls in our designer file then we can easily access those controls in our code file directly by its name. But when we dynamically add some controls either in constructor or in load event of form then we can't access them directly in our code.

Let's have an example

• Add a textbox and a button using drag-n-drop operation on our form i.e. Form1.
• Leave the name of controls i.e. textBox1 and button1 as they are.
• Now in constructor add a new textbox dynamically having name "txtBox" with some properties like:
  
  
  TextBox txtBox = new TextBox();
  txtBox.Name = "txtBox";
  txtBox.Text = "dynamically added control";
  txtBox.Width = 250;
  txtBox.Location = new Point(10, 10);
  
  this.Controls.Add(txtBox);

In above code the Name property will be used to find this control. All the remaining properties are familiar to you as in our designer file. At the last we have added this textbox to the form, here the keyword this is pointing Form1.

This form will look like the following screenshot: 

In click event of button when we will try to access above controls then we notice that "textBox1" will be accessed by its name and "txtBox" will not accessed here.

To access these dynamically added controls we have to use a pre-defined method Find(). This method searches the controls by their name and builds an array as in below code.

Control[] controls = this.Controls.Find("txtBox", false);

foreach (var item in controls)

{

     TextBox txtBox = item as TextBox;

     if (txtBox != null)

        MessageBox.Show("control accessed");

}

In above set of code "controls" will hold the array of all the controls having name "txtBox". One by one we will access items of array and check the type of item. If type of item is TextBox then we found our textbox having name "txtBox".

As we know about naming standards of programming that two controls of the same name cannot exists. Keep in mind the above line, and we are sure that, there will only one control named “txtBox” of type TextBox.

Now we will access the above textbox in one line of code i.e.

TextBox txtBox1 = this.Controls.Find("txtBox", false).First() as TextBox;
if (txtBox1 != null)
 MessageBox.Show("control accessed");

When we run our project and click on button then our form look like this.

How to change password in windows forms

In context of computer system, once another person acquire your password he/she can use your computer. They can even use your computer to attack another machines. If he/she do this, it will look like you do anything.

The same can be applied to our applications, if one acquire application’s password then he/she can modify or remove something, to harm you. That’s why we should change the password periodically in order to ensure the security of computer/application.

We have to follow some steps to change the password:

Step 1

After creating login control add another form i.e. Change password, and pass the username from login form to this form using constructor.

Step 2

Add three textboxes into new form. One for old password, second for new password and third will be used for comparing new password. Add a button and generate click event of that button.

Step 3

Write following code in click event of button

In above code, check the user is correct and also entered the equal values in confirm password and new password textbox. If both conditions are satisfied then, assign the user a new password that is in new password textbox. And finally save the changes in database.

Download source.

How to make login control in windows forms

Sometimes anonymous user can harm our application and can change the data stored in our application. That’s why, it is often necessary to permit only authorized users to use our application. There is no predefined method to do this operation in windows forms, so we have to implement it in our way.

If we have username and password in our database then we can implement a better authentication system. So we will create a table in our database named User which will have two columns (Username and Password). Insert some records in that table to check our login control.

In click event of login button write following code:

Here in this code will show a message if user leaves the textboxes empty. First check the username in our database having exactly the same name user entered in textbox. If there is an entry then compare the password.

We can show a message box at each wrong step of user.

Download example.

How to bind TabControl with database in windows forms

TabControl manages and displays a related collection of tabs that can contain controls and components. We can add multiple pages in a single form and each page can contain its individual set of controls that can displayed when that tab is selected.

When we add a TabControl in our form then it will add three Tab Pages as default pages and tabPage1 will be selected tab. We can remove any tabPage from that control or can add a new tabPage.

Create a tab page

To add a tab page we can use Add method of TabPages property. Following line of code will add three tab pages without any control

tabControl1.TabPages.Add("Page1");

tabControl1.TabPages.Add("Page2");

tabControl1.TabPages.Add("Page3");

Inserting a tabPage with a label control:

Label label = new Label();    //New labal control

label.Text = "This is the control which will added to TabPage";    //set text property of label

TabPage tabPage = new TabPage();    // create a new tabpage

tabPage.Text = "Tab Page 1";    // set text property of tabpage

tabPage.Controls.Add(label);    //add label control to above tabpage

tabControl1.TabPages.Add(tabPage);    //finally add tabpage to our tabcontrol

Remove a tab page 

To remove a tab page we can use Remove method of TabPages property. Using following line of code we can remove selected tab from our tabControl:

tabControl1.TabPages.Remove(tabControl1.SelectedTab);

Load tabpages from Database 

In the following example we will add tabPage according to our departments. Each tab contains a list of employees working in that department. For showing list of employees we used a DataGridView in a UserControl. When we add a tabPage then we will load userControl in that tabPage.

Step 1:

Create two tables i.e. Department and Employee (described in previous article) in our database.

Step 2:

Insert some records in both tables (described in previous article).

Step 3:

Add a UserControl to our project and then add a DataGridView that will contain list of employees. Write following code in constructor of UserControl:

  

public EmployeeUserControl(Department dept)

{

       InitializeComponent();

       DataContext dc = new DataContext();

       var employees = from temp in dc.Employee

                                where temp.Department.Name == dept.Name

                                select new

                                {

                                       temp.Name,

                                       temp.Email,

                                       temp.Mobile

                                 };

       empDataGridView.DataSource = employees.ToList();

 }

As in above code datagridview will bind to the list of employees according to department.

Step 4:

Now finally in our form’s constructor write below line of codes to add pages to tabControl

foreach (var item in dc.Department)

{

        TabPage newTab = new TabPage(item.Name);

         newTab.Controls.Add(new EmployeeUserControl(item));

         tabControl1.TabPages.Add(newTab);

}

Download Source

How to bind CheckedListBox using database In windows forms

CheckedListBox displays a list of items with a checkbox on the left side of each item. The user can select one or more items by simply place a checkmark to one or more items. To access those checked items we can use either CheckedListBox.CheckedItemCollection or CheckedListBox.CheckedIndexCollection in our code behind file.

To add a list of items at run time there is a function i.e. AddRange() that need a parameter of type array. We can add individual items using simple Add() method.

When we run our project it will look like:

CheckedListBox provides two types of states i.e. Checked and Unchecked. There is one more state i.e. Intermediate that must be set through code If we want to use this.

Now to bind this control using database we must have some entries in our table and I have described this in my previous post. To bind CheckedListBox with our table we have to write following code in our Form’s constructor:

After bind this CheckedListBox  it’s time to get checked items. To get checked items there is a property i.e. CheckedItems type of CheckedItemCollection. So add a button in our form and in the click event of that button we will get checked item one by one and add it to a string variable and finally show this through a message box.

When we will run our project it will show a message box having items that are checked in our CheckedListBox.

Download Source code

How to bind TreeView with database table in c# Windows Form

TreeView displays a hierarchical collection of labelled items to the user that optionally contains an image. Microsoft is also using treeview in left side of windows explorer from the time of windows 98 till now.

It is very simple to add a root node with some child nodes to treeview as:

Now in this article I am going to bind this treeview through a database table having following properties:

Create a database having single table i.e. TreeNode1 as I have described in my previous post “How to create database using Entity Framework”. To bind treeview to table there should be some records in our table Treenode1, so let add some entries to database like:

Now to bind treeview with table TreeNode1 write following code in our Form1’s constructor

Here in the first statement I got all the records which have Parent key = 0 i.e. they will be our root node. Now one by one we will check if any node in database have this node as its parent node, if any, then create that new node to its child node.

At the last add that root node to treeview and that is it. I have a screenshot also as an example.

Download source code.

How to bind GridView with search results in c# Windows Forms

There are many cases in which user want to search some records in database that are satisfying a condition and bind them to gridview. So I am writing this post to bind a gridview with search option and in else case all the data from database will bind to gridview.

Now create a new windows form application in visual studio, make a database as I have described in my previous post How to create Database using Entity Framework and add a table i.e. Student having following properties:

Insert some records in Student table as I have entered following list of items:

In Form1 designer add two controls TextBox and DataGridView.

Now bind DataGridView to a list that have all the records saved in student table in database.

There is an event of textbox i.e. textchanged event that is raised when the value of text property is changed on control.

In this event of textbox write the following code:

When you run this project it will show default binding of datagridview as

and when you type some text in textbox then:

so this datagridview has been bind to search result of textbox.

Download Example.

How to Perform LINQ in C# Programming to Get Records using Entity Framework 5

LINQ stands for Language INtegrated Queries in the context of sql programming. These are like SQL queries used to manipulate collection of objects through entity framework. LINQ are queries that can be directly written in c# programming language and will be translated to SQL statements and executed against the database. It simplify the complex process of database developer to query in databases.

These type of queries are executed on collection of IEnumerable<T> or IQueryable<T> and as results are returned new collection of IEnumerable<T> objects. In this post I will explain some example that show how to use LINQ on collection of objects in c# programming.

Let’s suppose we have a c# class Student having two properties i.e. Name and Age and of course a primary key Id. Now to get all records of students from database we have to write:

var students = from student in dc.Student

               select student;

In above code it will extract all columns from Student table like Id, Name and Age also. If we want only name and Age column then:

var students = from student in dc.Student

               select new

               {

                   student.Name,

                   student.Age

               };

It will show only Name and Age columns of all the records.

It’s very simple to get some records having where condition like in sql programming. In below code if we want to get all records having age = "25" then the following code should be written:

var students = from student in dc.Student

                     where student.Age == 25

                     select student;

Now at the last if you want to get records having age = "25" and name = "Rahul" then following line of code has to be written:

var students = from student in dc.Student

               where student.Age == 25 && student.Name == "Rahul"

               select student;

So according to above examples, LINQ is very simple language to be queried with databases. Here dc is used for data context that is created through entity framework 5.