SQL EXISTS() Query

Person Table
——————————–
| PersonId   |  PersonName       |
——————————–
| 1             |  Abc                  |
——————————–
| 2             |  Pqr                   |
——————————–
| 3             |  Xyz                  |
——————————–


Language Table
——————————–
| LanguageId  | LanguageName  |
——————————–
| 7                |  C#                 |
——————————–
| 8                |  Java               |
——————————–
| 9                |  Python            |
——————————–


PersonSkill Table
—————————————
PersonId  |  LanguageId  | SkillLevel   |
—————————————
| 1            |  7               |  20           |
—————————————
| 1            |  8               |  56           |
—————————————
| 1            |  9               |  60           | 
—————————————


Now i want to query those persons who know C# > 65 and Java > 80.


SQL Statement

SELECT * FROM Person
WHERE 
EXISTS(SELECT * FROM PersonSkill WHERE PersonId = Person.PersonId AND LanguageId  = 7 AND SkillLevel>65)

AND 
EXISTS(SELECT * FROM PersonSkill WHERE PersonId = Person.PersonId AND LanguageId  = 8 AND SkillLevel>80)

DataGridView CRUD operation with lookup combobox column

using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Data.OleDb; using System.Drawing; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Windows.Forms; namespace AdminApp { public partial class frmConversationType : Form { public frmConversationType() { InitializeComponent(); } private String connectionString = null; private OleDbConnection connection = null; private OleDbDataAdapter da = null; private OleDbCommandBuilder commandBuilder = null; private DataTable dataTable = null; private BindingSource bindingSource = null; private BindingSource bsCategory = null; private String selectQueryString = null; private void OnFormLoading(object sender, EventArgs e) { connectionString = GlobalVariables.ConnectionString;// ConfigurationManager.AppSettings[“connectionString”]; connection = new OleDbConnection(connectionString); selectQueryString = “SELECT * FROM ConversationType”; connection.Open(); da = new OleDbDataAdapter(selectQueryString, connection); commandBuilder = new OleDbCommandBuilder(da); dataTable = new DataTable(); da.Fill(dataTable); bindingSource = new BindingSource(); bindingSource.DataSource = dataTable; //first, set datasource to lookup combo column bsCategory = new BindingSource(); bsCategory.DataSource = CustomObjects.ConversationCategory.GetCategoryList(GlobalVariables.ConnectionString); DataGridViewComboBoxColumn colUserTypeId = (DataGridViewComboBoxColumn)dg.Columns[“ConversationCategoryId”]; colUserTypeId.DisplayMember = “ConversationCategoryName”; colUserTypeId.ValueMember = “ConversationCategoryId”; colUserTypeId.DataSource = bsCategory; dg.AutoGenerateColumns = false; dg.DataSource = bindingSource; //now, set datasource to datagridview dg.Columns[“ConversationTypeId”].DataPropertyName = “ConversationTypeId”; dg.Columns[“ConversationTypeName”].DataPropertyName = “ConversationTypeName”; dg.Columns[“ConversationCategoryId”].DataPropertyName = “ConversationCategoryId”; dg.Columns[“InformEhan”].DataPropertyName = “InformEhan”; } //Add & Update private void OnSaving(object sender, EventArgs e) { try { da.Update(dataTable); } catch (Exception exceptionObj) { MessageBox.Show(exceptionObj.Message.ToString()); } } private void OnDeleting(object sender, EventArgs e) { try { dg.Rows.RemoveAt(dg.CurrentRow.Index); da.Update(dataTable); } catch (Exception exceptionObj) { MessageBox.Show(exceptionObj.Message.ToString()); } } } }

Cleaner Conditional HTML Attributes In Razor Web Pages

@{

}

<!DOCTYPE html>
<
html lang="en">
<
head>
<
meta charset="utf-8" />
<
title></title>

</
head>
<
body>


     //Example for unordered list

   <ul>
<
li><a href="~/Page1" class="@IsCurrentPage("Page1")">Page 1</a></li>
<
li><a href="~/Page2" class="@IsCurrentPage("Page2")">Page 2</a></li>
<
li><a href="~/Page3" class="@IsCurrentPage("Page3")">Page 3</a></li>
</
ul>
      //Example for select combo

     <select>

        @foreach(var item in ViewBag.ItemList){
<option value="@Item.Id" @IsSelected("ItemId", @item.Id)>@item.Name
           </option>
}
     </select>

   <input type="checkbox" @IsChecked("IsApartments") /> Apartments

</body> </html>

@functions {
    public static bool Selected(int a, int b){
        return a == b;
}
    public string Iselected(string column, String value)

    {
       //Assume, you have sent a DataTable variable from controller to ViewBag.
       // If value matched with a specific column value, return “selected”.
        return ViewBag.DataTable.Rows[0][column].ToString() == value ? “selected” : null;
    }

    public string IsChecked(string columnName)
    {
       //Same as above function
        return Convert.ToBoolean( ViewBag.Retailer.Rows[0][columnName]) == true ? “checked” : null;
    }

}


Source: http://www.mikesdotnetting.com/Article/201/Cleaner-Conditional-HTML-Attributes-In-Razor-Web-Pages