Archive for February 2016
Examples of SQL Commands - DDL, DML
Example for SQL commands – DDL,
DML
--Create database Db_Product and check it in Databases Folder
create database
Db_Product
--using product database (Selected in databases dropdown)
use Db_Product
--create table Tbl_Items with 3 cols - Id int, ItemName
vc(30), ItemPrice money (Refresh tables folder)
create table
Tbl_Items(Id int,ItemName varchar(30),ItemPrice money)
--view data present in table
select * from Tbl_Items
--insert rows of data for Tbl_Items
insert into
Tbl_Items values(1,'Biryani',150.00)
insert into
Tbl_Items(Id,ItemName) values(1,'Noodles')
insert into
Tbl_Items(Id,ItemName,ItemPrice) values(1,'Coke',20)
--view data present in table
select * from Tbl_Items
--view data of two cols in table
select Id,
ItemName from Tbl_Items
--adding a new column Availablity (varchar(10))to the table
Alter table
Tbl_Items
Add Availability varchar(10)
--view data present in table
select * from Tbl_Items
--modify the datatype of availablity column to char
Alter table
Tbl_Items
Alter column
Availability char
--update Id as 2 for an item where ItemName is Noodles
update Tbl_Items set
Id=2 where
ItemName='Noodles'
--update Id as 3 for an item where ItemName is Coke
update Tbl_Items set
Id=3 where
ItemName='Coke'
--update Availability of all the items to A
update Tbl_Items set
Availability='A'
--view data present in table
select * from Tbl_Items
--delete an item data where id is 2
delete from
Tbl_Items where Id=2
--view data present in table
select * from Tbl_Items
--delete column ItemPrice
alter table
Tbl_Items
drop column
ItemPrice
--view data present in table
select * from Tbl_Items
--change column name to Status
sp_rename 'Tbl_Items.Availability','Status'
--view data present in table
select * from Tbl_Items
--changing Tablename to Tbl_ProductItems
sp_rename 'Tbl_Items','Tbl_ProductItems'
--to view data after changing table name
select * from Tbl_ProductItems
--clear the entire table data
truncate table
Tbl_ProductItems
--to view data present in table
select * from Tbl_ProductItems
--delete table
drop table
Tbl_ProductItems
--refresh the tables folder and you cant see the table there
--get data from deleted table
select * from Tbl_ProductItems --Invalid
object name
--delete database(can't delete if you are using it)
drop database
Db_Product
--refresh the database folder and see the database will be
missing
--You can't use this database any more
TCL commands
B – Begin Transaction (To start recording transactions)
C – Commit (To save the transaction permanently)
R – Rollback (To revert the database to the state where recording started)
C – Commit (To save the transaction permanently)
R – Rollback (To revert the database to the state where recording started)
DML commands
SUDI
S – Select (To retrieve data)U – Update (to update data)
D – Delete(to delete rows of data)
I – Insert (to insert new row of data)
DDL commands
DRCAT
D – Drop (To delete a database or table)
R – Rename (To change the column name)
C – Create (To create a database or table)
A – Alter (To work on columns)
T – Truncate(To clear entire table data)
Alter contains three commands:
Alter Add (To add column)
Alter alter (To modify data type of column)
Alter drop (To delete a column)
Introduction to SQL
SQL is a language used to interact with the database.
SQL stands for Structured Query Language
SQL lets you access and manipulate databases
SQL is an ANSI (American National Standards Institute) standard.
SQL is not case sensitive.
RDBMS is the basis for SQL, and for all modern database systems such as MS SQL Server, IBM DB2, Oracle, MySQL and Microsoft Access.
In RDMS data is stored in the form of tales which contains rows and columns.
SQL is divided into 4 Sub languages as follows:
DDL (Data Definition Language)
DML (Data Manipulation Language)
TCL (Transaction Control Language)
DCL (Data Control Language)
SQL stands for Structured Query Language
SQL lets you access and manipulate databases
SQL is an ANSI (American National Standards Institute) standard.
SQL is not case sensitive.
RDBMS
RDBMS stands for Relational Database Management System.RDBMS is the basis for SQL, and for all modern database systems such as MS SQL Server, IBM DB2, Oracle, MySQL and Microsoft Access.
In RDMS data is stored in the form of tales which contains rows and columns.
SQL is divided into 4 Sub languages as follows:
DDL (Data Definition Language)
DML (Data Manipulation Language)
TCL (Transaction Control Language)
DCL (Data Control Language)
Windows Forms User Controls
User Controls are controls that are created by the users with customized requirements. We can create a new control with so that we can reuse it in our project.
Implementing User Controls
Select project template as Windows Form control Library
Click on OK
Drag and drop exiting controls in toolbox and edit it based on your requirements
Build the project to get the dll for usercontrol in project location
Example: Projects\MyUserControl\MyUserControl\bin\Debug
Goto toolbox and create a new tab with a right click on exiting tab of toolbox and select Add new tab
Edit the caption of tab
Right click on the new tab and click on choose items
Select the browse button and select the user control dll
New control will be added to the toolbox which can be used everywhere in the project by simple drag and drop option to the design of the form.
Implementing User Controls
Steps to create user control
File – New Project – Select language as C#Select project template as Windows Form control Library
Click on OK
Drag and drop exiting controls in toolbox and edit it based on your requirements
Build the project to get the dll for usercontrol in project location
Example: Projects\MyUserControl\MyUserControl\bin\Debug
Using User Controls
Create a new windows applicationGoto toolbox and create a new tab with a right click on exiting tab of toolbox and select Add new tab
Edit the caption of tab
Right click on the new tab and click on choose items
Select the browse button and select the user control dll
New control will be added to the toolbox which can be used everywhere in the project by simple drag and drop option to the design of the form.
StringBuilder and string
StringBuilder is mutable which means we can append, insert or replace text in string without creating new memory for it everytime. Once a StringBuilder is created text can be added to the existing text without creating new memory everytime whenever we are adding content to it. String Builder is under System.Text namespace. Append method is used to add text to the existing StringBuilder. Performance of StringBuilder is higher than string.
Output:
Hello world Second Line
Hello India Second Line for string
String
String is immutable which means once we create string object we cannot modify. Every time when you change the text using insert, replace or append methods new instance is created to hold the new value by discarding the old value. Performance of string is less than StringBuilder. String is under System namespace.
Example
program to use StringBuilder and string
using System;
using System.Text;
namespace Overriding
{
class Program
{
static void Main(string[] args)
{
// Create StringBuilder.
StringBuilder stringbuilder = new StringBuilder();
//Appending first line
stringbuilder.Append("Hello world ");
//Appending second line
stringbuilder.Append("Second Line");
//Printing text in StringBuilder
Console.WriteLine(stringbuilder);
//create string
string s;
//assign text to string
s = "Hello India ";
//append text to string
s +=
"Second Line
for string";
Console.WriteLine(s);
Console.ReadKey();
}
}
}
Output:
Hello world Second Line
Hello India Second Line for string
Dialogs
Dialogs will allow the user give their inputs.
ColorDialog
Allows the user to select a color from the system colors.
FolderBrowserDialog
Allows the user to select a folder his system.
FontDialog
Allows the user to select the font style available in the system.
OpenFileDialog
Allows the user to select a file from the system.
SaveFileDialog
Allows the user to select a location where he can save a file.
Example code to use all the dialogs
Drag and drop five button to the form and also all the five dialog controls to the form which are displayed on the bottom panel.
Change the text of the buttons to Color, Folder, Font, Open File and Save File and Names to BtnColor, BtnFolder, BtnFont, BtnOpenFile, BtnSaveFile.
Double click on each button to create the events and write the below code in them
ColorDialog
Allows the user to select a color from the system colors.
FolderBrowserDialog
Allows the user to select a folder his system.
FontDialog
Allows the user to select the font style available in the system.
OpenFileDialog
Allows the user to select a file from the system.
SaveFileDialog
Allows the user to select a location where he can save a file.
Example code to use all the dialogs
Drag and drop five button to the form and also all the five dialog controls to the form which are displayed on the bottom panel.
Change the text of the buttons to Color, Folder, Font, Open File and Save File and Names to BtnColor, BtnFolder, BtnFont, BtnOpenFile, BtnSaveFile.
Double click on each button to create the events and write the below code in them
private void BtnColor_Click(object sender, EventArgs e)
{
colorDialog1.ShowDialog();
BtnColor.BackColor = colorDialog1.Color;
}
private void BtnFolder_Click(object sender, EventArgs e)
{
folderBrowserDialog1.ShowDialog();
BtnFolder.Text = folderBrowserDialog1.SelectedPath;
}
private void BtnFont_Click(object sender, EventArgs e)
{
fontDialog1.ShowDialog();
BtnFont.Font = fontDialog1.Font;
}
private void BtnOpenFile_Click(object sender, EventArgs e)
{
openFileDialog1.ShowDialog();
BtnOpenFile.Text = openFileDialog1.FileName;
}
private void BtnSaveFile_Click(object sender, EventArgs e)
{
saveFileDialog1.ShowDialog();
}
StatusStrip
StatusStrip is a collection of controls which are displayed at the bottom of the form which is generally used to display the status of the window form.
Default Event: Click which is fired when user click on the item.
Steps to create StatusStrip
Drag and drop a StatusStrip control to the form which is displayed on the bottom panel of the form but also shows up the dropdown with list of controls at the bottom of the form.
You can use the dropdown available at the bottom of the form to select and add controls in the StatusStrip which is present at the bottom of the form.
Double click on the StatusStrip item to see the generated event handler of that specific item and you can create event handler for each item to write some code that has to be executed when you click on that item in the runtime.
Default Event: Click which is fired when user click on the item.
Steps to create StatusStrip
Drag and drop a StatusStrip control to the form which is displayed on the bottom panel of the form but also shows up the dropdown with list of controls at the bottom of the form.
You can use the dropdown available at the bottom of the form to select and add controls in the StatusStrip which is present at the bottom of the form.
Double click on the StatusStrip item to see the generated event handler of that specific item and you can create event handler for each item to write some code that has to be executed when you click on that item in the runtime.
Example
code to display number of characters present in a textbox in status label
Double click on the textbox and write the
below code.
private void textBox2_TextChanged(object sender, EventArgs e)
{
toolStripStatusLabel1.Text = textBox2.Text.Length.ToString();
}
ContextMenuStrip
Context menus are appeared when user right-click. They looks like a floating menu. Different context menus can be displayed in a form based on the location where user has clicked. Multiple context menus can be created and they has to be assigned to specific controls in the form.
Default Event: Click which is fired when user click on the item in the contextmenustrip.
Property: For every control including form a propery called contextMenuStrip will be present which is used to assign a context menu strip to it which is displayed when user right click on that control in the run time.
Steps to create context menu strip
Drag and drop a contextmenustrip control to the form which is displayed at the bottom panel of the form but also shows up auto generated textboxes at the top of the form to add items.
You can create the context menu strip items just by adding text in the automatically generated textboxes which are displayed at the top of the form.
Double click on the each item to see the generated event handler of that specific item and you can create event handler for each item to write some code that has to be executed when you click on that item in the runtime.
For every control including Form will have a property called contextMenuStrip where you can add context menu strip name which has to displayed when user right click on that control.
Default Event: Click which is fired when user click on the item in the contextmenustrip.
Property: For every control including form a propery called contextMenuStrip will be present which is used to assign a context menu strip to it which is displayed when user right click on that control in the run time.
Steps to create context menu strip
Drag and drop a contextmenustrip control to the form which is displayed at the bottom panel of the form but also shows up auto generated textboxes at the top of the form to add items.
You can create the context menu strip items just by adding text in the automatically generated textboxes which are displayed at the top of the form.
Double click on the each item to see the generated event handler of that specific item and you can create event handler for each item to write some code that has to be executed when you click on that item in the runtime.
For every control including Form will have a property called contextMenuStrip where you can add context menu strip name which has to displayed when user right click on that control.
Example
code to copy and paste content of a text box by using context menu present on
form
Create
a context menu with 3 items – Copy, Paste and clear
Create a textbox and assign context menu to the form.
string s = string.Empty;
Create a textbox and assign context menu to the form.
string s = string.Empty;
private void copyToolStripMenuItem_Click(object sender, EventArgs e)
{
s =
textBox1.Text;
}
private void pasteToolStripMenuItem_Click(object sender, EventArgs e)
{
textBox1.Text = s;
}
private void clearToolStripMenuItem_Click(object sender, EventArgs e)
{
textBox1.Text = "";
}
ToolStrip
It is a menu present at the top of the form which is capable of hosting Button, Label, SplitButton, DropDownButton, Separator, ComboBox, TextBox and ProgressBar controls.
Default Event: Click which is fired when user click on the controls in toolbox.
Steps to create ToolStrip
Drag and drop a ToolStrip control to the form which is displayed on the bottom panel of the form but also shows up the dropdown with list of controls at the top of the form.
You can use the dropdown available at the top of the form to select and add controls in the ToolStrip which is present at the top of the form.
Double click on the ToolStrip item to see the generated event handler of that specific item and you can create event handler for each item to write some code that has to be executed when you click on that item in the runtime.
Default Event: Click which is fired when user click on the controls in toolbox.
Steps to create ToolStrip
Drag and drop a ToolStrip control to the form which is displayed on the bottom panel of the form but also shows up the dropdown with list of controls at the top of the form.
You can use the dropdown available at the top of the form to select and add controls in the ToolStrip which is present at the top of the form.
Double click on the ToolStrip item to see the generated event handler of that specific item and you can create event handler for each item to write some code that has to be executed when you click on that item in the runtime.
Example
to display message box when you click on ToolStrip item
private void toolStripComboBox1_Click(object sender, EventArgs e)
{
MessageBox.Show("You selected combo box in
toolstrip");
}
private void toolStripLabel1_Click(object
sender, EventArgs e)
{
MessageBox.Show("You selected label in
toolstrip");
}
MenuStrip
MenuStrip adds a menu bar at the top of the windows form.
Default Event: Click which is fired when you click on that specific menuitem. Each item will have its own click event.
Steps to create a MenuStrip
Drag and drop a menustrip control to the form which is displayed on the bottom panel of the form but also shows up auto generated textboxes at the top of the form to add items.
You can create the menu items just by adding text in the automatically generated textboxes which are displayed at the top of the form.
Double click on the menu item to see the generated event handler of that specific item and you can create event handler for each item to write some code that has to be executed when you click on that item in the runtime.
Default Event: Click which is fired when you click on that specific menuitem. Each item will have its own click event.
Steps to create a MenuStrip
Drag and drop a menustrip control to the form which is displayed on the bottom panel of the form but also shows up auto generated textboxes at the top of the form to add items.
You can create the menu items just by adding text in the automatically generated textboxes which are displayed at the top of the form.
Double click on the menu item to see the generated event handler of that specific item and you can create event handler for each item to write some code that has to be executed when you click on that item in the runtime.
Example
code to show a message box with the text on the menu item
private void newToolStripMenuItem_Click(object sender, EventArgs e)
{
MessageBox.Show(newToolStripMenuItem.Text);
}
private void projectToolStripMenuItem_Click(object sender, EventArgs e)
{
MessageBox.Show(projectToolStripMenuItem.Text);
}
TabControl
Controls are displayed under different tabs.
Different controls can be displayed in the same location and with the selection of tab controls inside that tab are displayed in the runtime.
Different controls can be displayed in the same location and with the selection of tab controls inside that tab are displayed in the runtime.
SplitContainer
Divides a container’s display area into two resizable panels to which you can add controls.
You can drag and drop controls into one of the two container and in the runtime the panels can be resized.
You can drag and drop controls into one of the two container and in the runtime the panels can be resized.
GroupBox
Displays a frame around the controls with an optional caption. The frame of the groupbox will be visible in the output.
Properties:
Text: The text that is displayed as the caption on groupbox control in output.
Steps to use a panel
Drag and drop a group box
Add controls inside it.
Arrange them relatively inside the group box.
Now you can arrange the group box in a form which brings all the controls inside it without any change in their relative distances of controls inside it.
Default Event: Enter which is fired when groupbox become active by using control inside it.
Properties:
Text: The text that is displayed as the caption on groupbox control in output.
Steps to use a panel
Drag and drop a group box
Add controls inside it.
Arrange them relatively inside the group box.
Now you can arrange the group box in a form which brings all the controls inside it without any change in their relative distances of controls inside it.
Default Event: Enter which is fired when groupbox become active by using control inside it.
Code to
change text of groupbox when user enters selects a textbox inside it
private void
groupBox1_Enter(object sender, EventArgs e)
{
//Place a textbox inside Groupbox
and click inside it
groupBox1.Text = "GroupBox
Text";
}
FlowLayoutPanel
It is capable of holding multiple controls inside it and it will arrange them in automatically an order.
Properties:
FlowDirection: Specifies the direction in which controls are laid out.
Example: LeftToRight, RightToLeft, TopDown, BottomUp
WrapContents: Set to wrap controls inside it or false to make controls go outside the border of the panel.
Steps to use FlowLayoutPanel
Drag and drop a FlowLayoutPanel.
Then drag and drop controls into FlowLayoutPanel and you can see that they are automatically arrange in an order.
Default Event: Paint which is fired when it is created
Code to change the FlowDirection when it is created
Properties:
FlowDirection: Specifies the direction in which controls are laid out.
Example: LeftToRight, RightToLeft, TopDown, BottomUp
WrapContents: Set to wrap controls inside it or false to make controls go outside the border of the panel.
Steps to use FlowLayoutPanel
Drag and drop a FlowLayoutPanel.
Then drag and drop controls into FlowLayoutPanel and you can see that they are automatically arrange in an order.
Default Event: Paint which is fired when it is created
Code to change the FlowDirection when it is created
private void
flowLayoutPanel1_Paint(object sender, PaintEventArgs e)
{
flowLayoutPanel1.FlowDirection = FlowDirection.RightToLeft;
}
Panel
Used to group collection of controls.
Panel is not visible in the output but helps in design time to group collection of controls.
All the controls inside panel can be moved at once and they can be relatively arranged inside a panel.
Steps to use a panel
Drag and drop a panel
Add controls inside it.
Arrange them relatively inside the panel.
Now you can arrange the panel in a form which brings all the controls inside it without any change in their relative distances of controls inside it.
Default event: Paint which fired when the panel is drawn/created on the form. This event is fired after form load event.
Panel is not visible in the output but helps in design time to group collection of controls.
All the controls inside panel can be moved at once and they can be relatively arranged inside a panel.
Steps to use a panel
Drag and drop a panel
Add controls inside it.
Arrange them relatively inside the panel.
Now you can arrange the panel in a form which brings all the controls inside it without any change in their relative distances of controls inside it.
Default event: Paint which fired when the panel is drawn/created on the form. This event is fired after form load event.
Code to
make change the back color of panel when it is painted or created
private void
panel1_Paint(object sender, PaintEventArgs e)
{
panel1.BackColor = Color.Black;
}
private void Form1_Load(object sender, EventArgs e)
{
panel1.BackColor = Color.Yellow;
}
First panel back color will be yellow and then it is
changed to black when it is created.
Code to
change visibility of panel on button click
private void
button1_Click(object sender, EventArgs e)
{
if (panel1.Visible == true)
{
panel1.Visible = false;
}
else { panel1.Visible = true; }
}
All the controls inside the panel will be visible and
invisible to the user based on the visibility of the panel in which they are present.
Containers
Containers are controls which can hold other controls
inside them.
Example: Panel, FlowLayoutPanel, GroupBox etc.
Example: Panel, FlowLayoutPanel, GroupBox etc.
They are used to hold multiple controls and helps in
easy organization of controls inside the form.
WebBrowser
It is used to browse web pages inside the windows form.
Property:
Specifies the URL that the web browser control has to display.
Example: http://www.google.com
This will work only when internet is available.
Property:
Specifies the URL that the web browser control has to display.
Example: http://www.google.com
This will work only when internet is available.
TreeView
It is used to display items in hierarchy in parent and child nodes format.
Adding items to treeview:
Click on the smart tag – Edit nodes.
Add root to add root items under which child items can be added with a click on Add child option by selecting the parent item.
Every node will have Text and Name property. Name is used to access that node in code and Text is used to display text on that node.
Adding items to treeview:
Click on the smart tag – Edit nodes.
Add root to add root items under which child items can be added with a click on Add child option by selecting the parent item.
Every node will have Text and Name property. Name is used to access that node in code and Text is used to display text on that node.
ToolTip
Displays information when the user moves the cursor over an associated control.
Steps to use tooltip:
Drag and drop a tooltip control to the design window. It is displayed on the bottom panel.
Properties:
TootlTipTitle: Determines the title of the tooltip.
Now you can use the tooltip created after setting the above property. One tooltip control can be used to more than one control in that windows form. And all the controls will display tooltip message under this tooltip title which is optional to set.
Then drag and drop two buttons to which you want to add tooltip messages. Then for each button set ToolTip on toolTip1 property with some text which will be displayed on runtime as tooltip text for those buttons.
Steps to use tooltip:
Drag and drop a tooltip control to the design window. It is displayed on the bottom panel.
Properties:
TootlTipTitle: Determines the title of the tooltip.
Now you can use the tooltip created after setting the above property. One tooltip control can be used to more than one control in that windows form. And all the controls will display tooltip message under this tooltip title which is optional to set.
Then drag and drop two buttons to which you want to add tooltip messages. Then for each button set ToolTip on toolTip1 property with some text which will be displayed on runtime as tooltip text for those buttons.
TextBox
It is used to take input from user.
Default Event: TextChanged which is fired when text in textbox changed.
Example code to take input from user and print immediately in a label control
Default Event: TextChanged which is fired when text in textbox changed.
Example code to take input from user and print immediately in a label control
private void textBox1_TextChanged(object sender, EventArgs e)
{
label1.Text = textBox1.Text;
}
RichTextBox
It is used to take input from user like textbox and it is capable of holding text styles.
Default Event: TextChanged which is fired when text in RichTextBox changed in runtime.
Example code to print text in RichTextBox in label with a change in text
Default Event: TextChanged which is fired when text in RichTextBox changed in runtime.
Example code to print text in RichTextBox in label with a change in text
private void richTextBox1_TextChanged(object sender, EventArgs e)
{
label1.Text = richTextBox1.Text;
}
Example code to print colourful text with styles in RichTextBox
private void Form1_Load(object sender, EventArgs e)
{
richTextBox1.Font = new Font("Consolas", 18f, FontStyle.Bold);
richTextBox1.BackColor = Color.AliceBlue;
string[] words =
{
"Hello", "World"
};
Color[] colors =
{
Color.Aqua,
Color.CadetBlue
};
for (int i = 0; i <
words.Length; i++)
{
string word = words[i];
Color color = colors[i];
{
richTextBox1.SelectionBackColor = color;
richTextBox1.AppendText(word);
richTextBox1.SelectionBackColor = Color.AliceBlue;
richTextBox1.AppendText(" ");
}
}
}