Tuesday, July 31, 2012
Monday, July 30, 2012
Data Defination Language(DDL )
Data Definition Language:
1 Create
2 Alter
3 Drop
4 Truncate
Create : To create the data definition.
Syntax
Create database < Database_Name >
Example
Create database Customers
Syntax for creating a Table:
-CREATE TABLE <table_name>(
column_name1 <dtype> [width],
column_name1 <dtype> [width],
………………….
column_namen <dtype> [width])
-Table names must be unique within the database.
-Column names must be unique within the table.
-Every table can have maximum of 1024 and minimum of 1 column.
The table name should start with alphabet.
Spaces and Special Symbols are not allowed.
The table name can contain max of 128 characters.
Alter:
It is used to modify the structure of the table
Using Modify Command to do the following things
1 Adding New Column
2 Drop Column
3 Modifying Column(increment, decrement field Size),(changing data type)
1 Adding New Column
Syntax:
Alter table <table name> Add <Col Name> datatype[size]
Example: Alter table students Add rollno int
2 Syntax to Drop the Column
Alter Table <Table Name> Drop column <Column Name>
Example: Alter table student Drop column studentid
3 Modifying Columns
Syntax:
Inserting the Width of the Column
Alter table <TableName> Alter Column ColName datatype
Example : Alter table <TableName> Alter Column ColName datatype
Changing the DataType of the Column
Alter table <TableName> Alter Column ColName datatype(size)
Example : Alter table Students Alter column sname varchar(20)
Drop Command: If we want to destroy the existing tables present in the database we use the Drop Command.
Syntax: DROP TABLE <TableName>
Example : Drop Table Customers
Truncate Command: Removes all rows from a table. Here the columns Remains the same so that you can add New Records in the table.
Note: We do not have Where Clause Here.
Syntax: TRUNCATE TABLE <TableName>
Example : TRUNCATE TABLE Customers
Truncate
· Truncate table is faster in execution.
· Truncate Releases Memory
· It will Reset Identity
· It Deletes all the records
Delete
· Delete table is slower in execution.
· Delete will not Releases Memory
· It will Not Reset Identity
· It deletes all the records or Particular Record.
1 Create
2 Alter
3 Drop
4 Truncate
Create : To create the data definition.
Syntax
Create database < Database_Name >
Example
Create database Customers
Syntax for creating a Table:
-CREATE TABLE <table_name>(
column_name1 <dtype> [width],
column_name1 <dtype> [width],
………………….
column_namen <dtype> [width])
-Table names must be unique within the database.
-Column names must be unique within the table.
-Every table can have maximum of 1024 and minimum of 1 column.
The table name should start with alphabet.
Spaces and Special Symbols are not allowed.
The table name can contain max of 128 characters.
Alter:
It is used to modify the structure of the table
Using Modify Command to do the following things
1 Adding New Column
2 Drop Column
3 Modifying Column(increment, decrement field Size),(changing data type)
1 Adding New Column
Syntax:
Alter table <table name> Add <Col Name> datatype[size]
Example: Alter table students Add rollno int
2 Syntax to Drop the Column
Alter Table <Table Name> Drop column <Column Name>
Example: Alter table student Drop column studentid
3 Modifying Columns
Syntax:
Inserting the Width of the Column
Alter table <TableName> Alter Column ColName datatype
Example : Alter table <TableName> Alter Column ColName datatype
Changing the DataType of the Column
Alter table <TableName> Alter Column ColName datatype(size)
Example : Alter table Students Alter column sname varchar(20)
Drop Command: If we want to destroy the existing tables present in the database we use the Drop Command.
Syntax: DROP TABLE <TableName>
Example : Drop Table Customers
Truncate Command: Removes all rows from a table. Here the columns Remains the same so that you can add New Records in the table.
Note: We do not have Where Clause Here.
Syntax: TRUNCATE TABLE <TableName>
Example : TRUNCATE TABLE Customers
Truncate
· Truncate table is faster in execution.
· Truncate Releases Memory
· It will Reset Identity
· It Deletes all the records
Delete
· Delete table is slower in execution.
· Delete will not Releases Memory
· It will Not Reset Identity
· It deletes all the records or Particular Record.
Difference Between Drop And Truncate
Drop Command: If we want to destroy the existing tables present in the database we use the Drop Command.
Syntax: DROP TABLE <TableName>
Example : Drop Table Customers
Truncate Command: Removes all rows from a table. Here the columns Remains the same so that you can add New Records in the table.
Note: We do not have Where Clause Here.
Syntax: TRUNCATE TABLE <TableName>
Example : TRUNCATE TABLE Customers
Truncate
Truncate table is faster in execution.
Truncate Releases Memory
It will Reset Identity
It Deletes all the records
Saturday, July 28, 2012
Beginners Dotnet Array vs ArrayList
Arrays are of fixed size.
Drawbacks:
When we add more number of values to an array than the original size of the array, arrays cannot resize themselves automatically.
ArrayList
Array List is similar to an array, as the number of values are added to the array List. We can add, insert and delete items into an Array List very easily.
And Array List stores any type of data inherited from System. Object. In a single Array list we can store any data type - integer, string and any type of object inherited from System. Object.
Briefly, an array contains one data type(int) whereas array list can contain many data types(int,float,char), the data in an array is accessed by indexes. Traversing the data in the amaryllis can be done by using for. Each loop or using enumerators or using indexers.
If you delete an item in an array, it keeps that position empty, whereas in an array list that position is occupied by the next element.
Drawbacks:
When we add more number of values to an array than the original size of the array, arrays cannot resize themselves automatically.
ArrayList
Array List is similar to an array, as the number of values are added to the array List. We can add, insert and delete items into an Array List very easily.
And Array List stores any type of data inherited from System. Object. In a single Array list we can store any data type - integer, string and any type of object inherited from System. Object.
Briefly, an array contains one data type(int) whereas array list can contain many data types(int,float,char), the data in an array is accessed by indexes. Traversing the data in the amaryllis can be done by using for. Each loop or using enumerators or using indexers.
If you delete an item in an array, it keeps that position empty, whereas in an array list that position is occupied by the next element.
Working with Drop Down Cascading Drop Down
I am using Three Drop Down here
Default.aspx
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:DropDownList ID="ddlcounty" runat="server" AutoPostBack="true"
onselectedindexchanged="ddlcounty_SelectedIndexChanged">
</asp:DropDownList>
<asp:DropDownList ID="ddlstate" runat="server"
onselectedindexchanged="ddlstate_SelectedIndexChanged" AutoPostBack="true">
</asp:DropDownList>
<asp:DropDownList ID="ddlcity" runat="server">
</asp:DropDownList>
</div>
</form>
</body>
</html>
Default.aspx.cs
using System.Data;
using System.Data.SqlClient;
public partial class dropdown : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
SqlConnection con = new SqlConnection("user id=sa;password=uday;database=country;data source=localhost");
SqlDataAdapter da = new SqlDataAdapter("select * from tblcountry", con);
DataSet ds = new DataSet();
da.Fill(ds);
ddlcounty.DataTextField = "countryname";
ddlcounty.DataValueField = "countryid";
ddlcounty.DataSource = ds;
ddlcounty.DataBind();
ddlcounty.Items.Insert(0, new ListItem("select", "0"));
ddlstate.Items.Insert(0, new ListItem("select", "0"));
ddlcity.Items.Insert(0, new ListItem("select", "0"));
}
}
protected void ddlcounty_SelectedIndexChanged(object sender, EventArgs e)
{
int countryid = Convert.ToInt16(ddlcounty.SelectedValue);
SqlConnection con = new SqlConnection("user id=sa;password=uday;database=country;data source=localhost");
SqlDataAdapter da = new SqlDataAdapter("select * from tblstate where countryid="+countryid, con);
DataSet ds = new DataSet();
da.Fill(ds);
ddlstate.DataTextField = "statename";
ddlstate.DataValueField = "stateid";
ddlstate.DataSource = ds;
ddlstate.DataBind();
ddlstate.Items.Insert(0, new ListItem("Select", "0"));
if (ddlstate.SelectedValue == "0")
{
ddlcity.Items.Clear();
ddlcity.Items.Insert(0, new ListItem("Select", "0"));
}
}
protected void ddlstate_SelectedIndexChanged(object sender, EventArgs e)
{
int stateid = Convert.ToInt16(ddlstate.SelectedValue);
SqlConnection con = new SqlConnection("user id=sa;password=uday;database=country;data source=localhost");
SqlDataAdapter da = new SqlDataAdapter("select * from tblcity where stateid="+stateid, con);
DataSet ds = new DataSet();
da.Fill(ds);
ddlcity.DataTextField = "cityname";
ddlcity.DataValueField = "cityid";
ddlcity.DataSource = ds;
ddlcity.DataBind();
ddlcity.Items.Insert(0, new ListItem("Select", "0"));
}
}
Default.aspx
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:DropDownList ID="ddlcounty" runat="server" AutoPostBack="true"
onselectedindexchanged="ddlcounty_SelectedIndexChanged">
</asp:DropDownList>
<asp:DropDownList ID="ddlstate" runat="server"
onselectedindexchanged="ddlstate_SelectedIndexChanged" AutoPostBack="true">
</asp:DropDownList>
<asp:DropDownList ID="ddlcity" runat="server">
</asp:DropDownList>
</div>
</form>
</body>
</html>
Default.aspx.cs
using System.Data;
using System.Data.SqlClient;
public partial class dropdown : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
SqlConnection con = new SqlConnection("user id=sa;password=uday;database=country;data source=localhost");
SqlDataAdapter da = new SqlDataAdapter("select * from tblcountry", con);
DataSet ds = new DataSet();
da.Fill(ds);
ddlcounty.DataTextField = "countryname";
ddlcounty.DataValueField = "countryid";
ddlcounty.DataSource = ds;
ddlcounty.DataBind();
ddlcounty.Items.Insert(0, new ListItem("select", "0"));
ddlstate.Items.Insert(0, new ListItem("select", "0"));
ddlcity.Items.Insert(0, new ListItem("select", "0"));
}
}
protected void ddlcounty_SelectedIndexChanged(object sender, EventArgs e)
{
int countryid = Convert.ToInt16(ddlcounty.SelectedValue);
SqlConnection con = new SqlConnection("user id=sa;password=uday;database=country;data source=localhost");
SqlDataAdapter da = new SqlDataAdapter("select * from tblstate where countryid="+countryid, con);
DataSet ds = new DataSet();
da.Fill(ds);
ddlstate.DataTextField = "statename";
ddlstate.DataValueField = "stateid";
ddlstate.DataSource = ds;
ddlstate.DataBind();
ddlstate.Items.Insert(0, new ListItem("Select", "0"));
if (ddlstate.SelectedValue == "0")
{
ddlcity.Items.Clear();
ddlcity.Items.Insert(0, new ListItem("Select", "0"));
}
}
protected void ddlstate_SelectedIndexChanged(object sender, EventArgs e)
{
int stateid = Convert.ToInt16(ddlstate.SelectedValue);
SqlConnection con = new SqlConnection("user id=sa;password=uday;database=country;data source=localhost");
SqlDataAdapter da = new SqlDataAdapter("select * from tblcity where stateid="+stateid, con);
DataSet ds = new DataSet();
da.Fill(ds);
ddlcity.DataTextField = "cityname";
ddlcity.DataValueField = "cityid";
ddlcity.DataSource = ds;
ddlcity.DataBind();
ddlcity.Items.Insert(0, new ListItem("Select", "0"));
}
}
Database design
tblcountry
tblstate
tblcity
output
How to write the DataBase Connection String in Web.ConFig and How to Use that
File-> New->Website
Select the location and Click on Ok.
Now in web.config
<connectionStrings>
<add name="dbtaskallocation" connectionString="Data Source=localhost;database=taskallocation;user id=sa;password=uday" providerName="System.Data.SqlClient"/>
</connectionStrings>
How to use this web.config.
page.cs
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
SqlConnection con=new SqlConnection(ConfigurationManager.ConnectionStrings["dbtaskallocation"].ToString());
SqlCommand cmd=new SqlCommand("select * from tblemployee",con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
con.Open();
DataSet ds=new DataSet();
da.Fill(ds);
GridView1.DataSource = ds;
GridView1.DataBind();
con.Close();
web.config
page.cs
Some important attributes for connection string
i Data Source: Specifies the data source name
ii. Server: The server name where the database resides
iii. Initial Catalog/Database: The database name if the data source supports more than one database
iv. User id/ uid: The user name for authentication
v. Password/ pwd: The corresponding password for authentication
vi. Pooling = true/false: Whether the connection should support pooling or not
vii. Min Connections: The minimum number of connections that should exist in the pool
viii. Max Connections: The maximum number of connections that can exist in the pool
Friday, July 27, 2012
How to Back Up the DataBase
Here I am using the table Task Allocation
Right Click on the Table and Then Select
Tasks--> Back Up
Now Click on Add Button and Select the location of ur Choice and Click on OK Button
Then Give the Data Base Name of your Choice
To Select the location Click on Add Button( Default location is been displayed there).
Finally the database back up is completed
What is Char, Varchar , NVarchar Data Types
Char :
Char datatype which is used to store fixed length of characters. Suppose if we declared char(50) it will allocates memory for 50 characters. Once we declare char(50) and insert only 10 characters of word then only 10 characters of memory will be used and other 40 characters of memory will be wasted.
Varchar :
Varchar means variable characters and it is used to store non-unicode characters. It will allocate the memory based on number characters inserted. Suppose if we declared varchar(50) it will allocates memory of 0 characters at the time of declaration. Once we declare varchar(50) and insert only 10 characters of word it will allocate memory for only 10 characters.
NVarchar
nvarchar datatype same as varchar datatype but only difference nvarchar is used to store Unicode characters and it allows you to store multiple languages in database. nvarchar datatype will take twice as much space to store extended set of characters as required by other languages.
So if we are not using other languages then it’s better to use varchar datatype instead of nvarchar
Char datatype which is used to store fixed length of characters. Suppose if we declared char(50) it will allocates memory for 50 characters. Once we declare char(50) and insert only 10 characters of word then only 10 characters of memory will be used and other 40 characters of memory will be wasted.
Varchar :
Varchar means variable characters and it is used to store non-unicode characters. It will allocate the memory based on number characters inserted. Suppose if we declared varchar(50) it will allocates memory of 0 characters at the time of declaration. Once we declare varchar(50) and insert only 10 characters of word it will allocate memory for only 10 characters.
NVarchar
nvarchar datatype same as varchar datatype but only difference nvarchar is used to store Unicode characters and it allows you to store multiple languages in database. nvarchar datatype will take twice as much space to store extended set of characters as required by other languages.
So if we are not using other languages then it’s better to use varchar datatype instead of nvarchar
Subscribe to:
Posts (Atom)
Kubernetes
Prerequisites We assume anyone who wants to understand Kubernetes should have an understating of how the Docker works, how the Docker images...
-
Super aggregates: Are used to calculate aggregates of aggregates There are two super aggregates functions Roll Up Cube When there is a singl...
-
SOLID principles allows object oriented programming implementation in an efficient better way into project development S-->Single R...
-
With this example you can create pdf and print pdf. If You are using ASP.NET MVC , PDF can be created by using Rotativa Rotativa is a ...