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