As you may know already, Boolean values are represented in Transact-SQL with the BIT data type, which is actually a small integer. On the other hand, the .NET Framework check box holds a property named Checked that can be TRUE or FALSE. Based on this, when a user has clicked a check box, you can specify the SQL's bit value as 1, otherwise it would be false. Here is an example:
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 WindowsControls1
{
public partial class Exercise : Form
{
public Exercise()
{
InitializeComponent();
}
private void btnCreateDatabase_Click(object sender, EventArgs e)
{
string strConnection = "Data Source=(local);Integrated Security=yes";
using (SqlConnection connection = new SqlConnection(strConnection))
{
SqlCommand command =
new SqlCommand("CREATE DATABASE MotorVehicleAdministration1;",
connection);
connection.Open();
command.ExecuteNonQuery();
MessageBox.Show("A database named \"MotorVehicleAdministration\" has been created.",
"Motor Vehicle Administration",
MessageBoxButtons.OK, MessageBoxIcon.Information);
}
using (SqlConnection connection =
new SqlConnection("Data Source=(local);" +
"Database='MotorVehicleAdministration1';" +
"Integrated Security=yes;"))
{
SqlCommand command =
new SqlCommand("CREATE TABLE Drivers" +
"(" +
" DriverNumber nvarchar(25), " +
" FullName nvarchar(50)," +
" OrganDonor bit" +
");",
connection);
connection.Open();
command.ExecuteNonQuery();
MessageBox.Show("A new table named \"Drivers\" has been created.",
"Motor Vehicle Administration",
MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
private void btnSubmit_Click(object sender, EventArgs e)
{
byte isOrganDonor = 0;
using (SqlConnection connection =
new SqlConnection("Data Source=(local);" +
"Database='MotorVehicleAdministration1';" +
"Integrated Security=yes;"))
{
if (chkIsOrganDonor.Checked == true)
isOrganDonor = 1;
SqlCommand command =
new SqlCommand("INSERT INTO Drivers " +
"VALUES(N'" + txtDriverNumber.Text +
"', N'" + txtFullName.Text +
"', " + isOrganDonor + ");",
connection);
connection.Open();
command.ExecuteNonQuery();
MessageBox.Show("A new record has been created.",
"Motor Vehicle Administration",
MessageBoxButtons.OK, MessageBoxIcon.Information);
txtDriverNumber.Text = "";
txtFullName.Text = "";
chkIsOrganDonor.Checked = false;
}
}
private void btnClose_Click(object sender, EventArgs e)
{
Close();
}
}
}
A Boolean column must have a value of true or false. In Transact-SQL, such a field uses the bit data type. In the .NET Framework, the value can be provided by a check box whose Checked property holds a Boolean value. The value must be passed with single-quotes. Here is an example: using System;
using System.Data;
using System.Drawing;
using System.Windows.Forms;
using System.Data.SqlClient;
public class Exercise : System.Windows.Forms.Form
{
Label lblEmployeeName;
TextBox txtEmployeeName;
Label lblMaritalStatus;
DomainUpDown dudMaritalStatus;
CheckBox chkHasChildren;
Button btnCreateDatabase;
Button btnAddEmployee;
public Exercise()
{
InitializeComponent();
}
void InitializeComponent()
{
btnCreateDatabase = new Button();
btnCreateDatabase.Text = "Create Database";
btnCreateDatabase.Location = new Point(12, 12);
btnCreateDatabase.Width = 120;
btnCreateDatabase.Click += new EventHandler(btnCreateDatabaseClick);
lblEmployeeName = new Label();
lblEmployeeName.AutoSize = true;
lblEmployeeName.Text = "Employee Name:";
lblEmployeeName.Location = new Point(12, 44);
txtEmployeeName = new TextBox();
txtEmployeeName.Location = new Point(110, 44);
lblMaritalStatus = new Label();
lblMaritalStatus.AutoSize = true;
lblMaritalStatus.Text = "Marital Status:";
lblMaritalStatus.Location = new Point(12, 66);
dudMaritalStatus = new DomainUpDown();
dudMaritalStatus.Items.Add("Single");
dudMaritalStatus.Items.Add("Married");
dudMaritalStatus.Items.Add("Separated");
dudMaritalStatus.Items.Add("Divorced");
dudMaritalStatus.Items.Add("Widow");
dudMaritalStatus.Location = new Point(110, 66);
chkHasChildren = new CheckBox();
chkHasChildren.Text = "Has Children?";
chkHasChildren.Location = new Point(12, 86);
chkHasChildren.CheckAlign = ContentAlignment.MiddleRight;
btnAddEmployee = new Button();
btnAddEmployee.Text = "Add Employee";
btnAddEmployee.Location = new Point(12, 110);
btnAddEmployee.Width = btnCreateDatabase.Width;
btnAddEmployee.Click += new EventHandler(btnAddEmployeeClick);
Text = "Database Exercise";
Controls.Add(lblEmployeeName);
Controls.Add(txtEmployeeName);
Controls.Add(lblMaritalStatus);
Controls.Add(dudMaritalStatus);
Controls.Add(btnAddEmployee);
Controls.Add(btnCreateDatabase);
Controls.Add(chkHasChildren);
StartPosition = FormStartPosition.CenterScreen;
}
void btnCreateDatabaseClick(object sender, EventArgs e)
{
using (SqlConnection cntExercise =
new SqlConnection("Data Source='EXPRESSION';" +
"Integrated Security='SSPI';"))
{
SqlCommand cmdExercise =
new SqlCommand("IF EXISTS (SELECT name " +
"FROM sys.databases WHERE name = N'Exercise1' " +
") " +
"DROP DATABASE Exercise1; " +
"CREATE DATABASE Exercise1;", cntExercise);
cntExercise.Open();
cmdExercise.ExecuteNonQuery();
}
using (SqlConnection cntExercise =
new SqlConnection("Data Source='EXPRESSION'; " +
"Database='Exercise1'; " +
"Integrated Security='SSPI';"))
{
SqlCommand cmdExercise =
new SqlCommand("CREATE SCHEMA Personnel;",
cntExercise);
cntExercise.Open();
cmdExercise.ExecuteNonQuery();
}
using (SqlConnection cntExercise =
new SqlConnection("Data Source='EXPRESSION';" +
"Database='Exercise1'; " +
"Integrated Security='SSPI';"))
{
SqlCommand cmdExercise =
new SqlCommand("CREATE TABLE Personnel.Employees" +
"(" +
" EmployeeName nvarchar(50)," +
" MaritalStatus int," +
" HasChildren bit" +
");",
cntExercise);
cntExercise.Open();
cmdExercise.ExecuteNonQuery();
}
}
private void btnAddEmployeeClick(object sender, EventArgs e)
{
using (SqlConnection cntExercise =
new SqlConnection("Data Source='EXPRESSION';" +
"Database='Exercise1';" +
"Integrated Security=SSPI;"))
{
SqlCommand cmdExercise =
new SqlCommand("INSERT INTO Personnel.Employees " +
"VALUES('" + txtEmployeeName.Text + "', " +
dudMaritalStatus.SelectedIndex + ", '" +
chkHasChildren.Checked + "');",
cntExercise);
cntExercise.Open();
cmdExercise.ExecuteNonQuery();
}
}
}
public class Program
{
[STAThread]
static int Main()
{
System.Windows.Forms.Application.Run(new Exercise());
return 0;
}
}
A check box is used to display a value that can be considered true or false. In Transact-SQL, when creating a column for such a value, you can use the bit data type. When specifying the value of that column, you can assign 1 for true and 0 for false. In the same way, when getting a value for the column, you can check whether it holds 1 or 0. If you are using a data reader to get the value of a column that has Boolean values, parse that value before applying it to the check box. Here is an example: using System;
using System.Data;
using System.Drawing;
using System.Windows.Forms;
using System.Data.SqlClient;
public class Exercise : System.Windows.Forms.Form
{
Label lblEmployeeName;
CheckBox chkIsFullTime;
Button btnBinder;
Button btnCreateDatabase;
public Exercise()
{
InitializeComponent();
}
void InitializeComponent()
{
btnCreateDatabase = new Button();
btnCreateDatabase.Text = "Create Database";
btnCreateDatabase.Location = new Point(12, 12);
btnCreateDatabase.Width = 120;
btnCreateDatabase.Click += new EventHandler(btnCreateDatabaseClick);
btnBinder = new Button();
btnBinder.Text = "Bind";
btnBinder.Location = new Point(140, 12);
btnBinder.Click += new EventHandler(btnBinderClick);
lblEmployeeName = new Label();
lblEmployeeName.AutoSize = true;
lblEmployeeName.Text = "Customer Name";
lblEmployeeName.Location = new Point(12, 44);
chkIsFullTime = new CheckBox();
chkIsFullTime.Text = "Full Time?";
chkIsFullTime.CheckAlign = ContentAlignment.MiddleRight;
chkIsFullTime.Location = new Point(12, 70);
Text = "Database Exercise";
Controls.Add(lblEmployeeName);
Controls.Add(btnBinder);
Controls.Add(btnCreateDatabase);
Controls.Add(chkIsFullTime);
StartPosition = FormStartPosition.CenterScreen;
}
void btnCreateDatabaseClick(object sender, EventArgs e)
{
using (SqlConnection cntExercise =
new SqlConnection("Data Source=(local); " +
"Integrated Security='SSPI';"))
{
SqlCommand cmdExercise =
new SqlCommand("IF EXISTS (SELECT name " +
"FROM sys.databases WHERE name = N'Exercise1' " +
") " +
"DROP DATABASE Exercise1; " +
"CREATE DATABASE Exercise1;", cntExercise);
cntExercise.Open();
cmdExercise.ExecuteNonQuery();
}
using (SqlConnection cntExercise =
new SqlConnection("Data Source=(local); Database='Exercise1'; " +
"Integrated Security='SSPI';"))
{
SqlCommand cmdExercise =
new SqlCommand("CREATE SCHEMA Personnel;", cntExercise);
cntExercise.Open();
cmdExercise.ExecuteNonQuery();
}
using (SqlConnection cntExercise =
new SqlConnection("Data Source=(local); Database='Exercise1'; " +
"Integrated Security='SSPI';"))
{
SqlCommand cmdExercise =
new SqlCommand("CREATE TABLE Personnel.Employees(EmployeeNumber nvarchar(8), " +
"FirstName nvarchar(24), LastName nvarchar(24), " +
"EmployeeName AS LastName + N', ' + FirstName," +
"Title nvarchar(50), IsFullTime bit);", cntExercise);
cntExercise.Open();
cmdExercise.ExecuteNonQuery();
}
using (SqlConnection cntExercise =
new SqlConnection("Data Source=(local); Database='Exercise1'; " +
"Integrated Security='SSPI';"))
{
SqlCommand cmdExercise =
new SqlCommand("INSERT INTO Personnel.Employees(EmployeeNumber, " +
"FirstName, LastName, Title, IsFullTime) " +
"VALUES(N'927049', N'Aaron', N'Swanson', N'General Owner', 1)," +
" (N'297113', N'Jane', N'Simms', 'Account Associate', 1)," +
" (N'804070', N'Justine', N'Aronson', 'Accountant', 0)," +
" (N'284825', N'Paul', N'DaCosta', 'Webmaster', 0)," +
" (N'380408', N'Desmond', N'Perez', 'Account Associate', 1);",
cntExercise);
cntExercise.Open();
cmdExercise.ExecuteNonQuery();
}
}
private void btnBinderClick(object sender, EventArgs e)
{
using (SqlConnection cntExercise =
new SqlConnection("Data Source=(local);" +
"Database='Exercise1';" +
"Integrated Security=SSPI;"))
{
SqlCommand cmdExercise = new SqlCommand("SELECT ALL * FROM Personnel.Employees;",
cntExercise);
cntExercise.Open();
SqlDataReader rdrEmployees = cmdExercise.ExecuteReader();
while(rdrEmployees.Read())
{
lblEmployeeName.Text = rdrEmployees[3].ToString();
chkIsFullTime.Checked = bool.Parse(rdrEmployees[5].ToString());
}
}
}
}
public class Program
{
[STAThread]
static int Main()
{
System.Windows.Forms.Application.Run(new Exercise());
return 0;
}
}
If you are using the Binding class, when applying to the check box, pass the first argument of the Binding constructor as Checked. Here is an example: private void btnBinderClick(object sender, EventArgs e)
{
using (SqlConnection cntExercise =
new SqlConnection("Data Source=(local);" +
"Database='Exercise1';" +
"Integrated Security=SSPI;"))
{
SqlCommand cmdExercise = new SqlCommand("SELECT ALL * FROM Personnel.Employees;",
cntExercise);
SqlDataAdapter sdaExercise = new SqlDataAdapter(cmdExercise);
DataSet dsEmployees = new DataSet("EmployeesSet");
cntExercise.Open();
sdaExercise.Fill(dsEmployees);
lblEmployeeName.DataBindings.Add(new Binding("Text", dsEmployees.Tables[0], "EmployeeName"));
chkIsFullTime.DataBindings.Add(new Binding("Checked", dsEmployees.Tables[0], "IsFullTime"));
}
}
|
|
|||||||||
|
|