Microsoft SQL Server Topics: Introduction to Visual Data Selection

	Introduction to Visual Data Selection

Introduction

This is an introduction to selecting records from a table (or view) and displaying them to a user.

Practical Learning: Introducing Data Selection

Start Microsoft SQL Server and connect
Right-click the server name and click New Query

To create a new database, in the empty window, type the following:

USE master;
GO

CREATE DATABASE ApartmentsManagement2;
GO

USE ApartmentsManagement2;
GO

CREATE SCHEMA Listing;
GO
CREATE TABLE Listing.Apartments
(
    UnitNumber nvarchar(10),
    Bedrooms tinyint,
    Bathrooms float,
    MonthlyRate money,
    SecurityDeposit money,
    Availability nvarchar(25)
);
GO

INSERT INTO Listing.Apartments
VALUES(N'8241-0600', 1, 1,  895,  250, N'Other'),
      (N'2729-3971', 1, 1,  925,  275, N'Available'),
      (N'6200-8048', 2, 1, 1100,  500, N'Occupied'),
      (N'3840-0002', 3, 2, 1450,  600, N'Available'),
      (N'4927-5396', 3, 2, 1350,  425, N'Available'),
      (N'3006-6088', 1, 1, 1100,  600, N'Occupied'),
      (N'5927-5940', 0, 1,  865,  225, N'Available'),
      (N'1397-0492', 2, 1, 1050,  350, N'Available'),
      (N'3692-9404', 1, 1,  895,  250, N'Other'),
      (N'5020-8114', 1, 1,  895,  250, N'Other'),
      (N'6286-6408', 0, 1,  950,  450, N'Available'),
      (N'2927-1539', 0, 1,  825,  200, N'Available');
GO

To execute the SQL statement, press F5
Start Microsoft Visual Studio
Start a new project and name it ivds
Click OK
Add a list view and design the form as follows:

Double-click an empty area of the form and implement the event as follows:

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

namespace ivds
{
    public partial class LambdaSquare : Form
    {
        public LambdaSquare()
        {
            InitializeComponent();
        }

        private void LambdaSquare_Load(object sender, EventArgs e)
        {
            using (SqlConnection scApartments = new SqlConnection("Data Source=CENTRAL;" +
                                                                  "Database='ApartmentsManagement2';" +
                                                                  "Integrated Security=Yes"))
            {
                SqlCommand cmdApartments = new SqlCommand("SELECT * FROM Listing.Apartments;", scApartments);
                scApartments.Open();

                SqlDataAdapter sdaApartments = new SqlDataAdapter(cmdApartments);
                DataSet dsApartments = new DataSet("ApartmentsSet");
                sdaApartments.Fill(dsApartments);

                for (int i = 0; i < dsApartments.Tables[0].Rows.Count; i++)
                {
                    DataRow drApartment = dsApartments.Tables[0].Rows[i];

                    ListViewItem lviApartment = new ListViewItem(drApartment["UnitNumber"].ToString());
                    lviApartment.SubItems.Add(drApartment["Bedrooms"].ToString());
                    lviApartment.SubItems.Add(drApartment["Batrhrooms"].ToString());
                    lviApartment.SubItems.Add(drApartment["MonthlyRate"].ToString());
                    lviApartment.SubItems.Add(drApartment["SecurityDeposit"].ToString());
                    lviApartment.SubItems.Add(drApartment["Availability"].ToString());

                    lvwApartments.Items.Add(lviApartment);
                }
            }
        }
    }
}

Execute the application (by press Ctrl + F5)