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.

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. 


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"));
    }
}



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 


How to set automatically focus on ASP.NET controls when validation fails ?

 “SetFocusOnError” properties to automatically focus ,if validation fails. with this it will help the user to identify the control easily SetFocusOnError=”True”. By default it’s false. 

To set focus automatically you  have to set  


How to Alter the Table Design after saving the Table

This is  My Table Structure and I have saved my  table as Jobs, 

Now I want to Edit my Table. , Then I was unable to Edit my Table. The Reason is 

Error as 



To solve this

Go to Tools in Sql Server ->Options


Now Un Check the Check Box

Now You  will be able to Save the Table.

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

Kubernetes

Prerequisites We assume anyone who wants to understand Kubernetes should have an understating of how the Docker works, how the Docker images...