Know About Web Technologies: sql server

Showing posts with label sql server. Show all posts

Sunday, November 22, 2015

Gridview in asp.net

Grid View Control:

It is used for data binding in tabular format.

<asp:gridview ID="Gridview1" runat="server">

<asp:BoundField DataField="" HeaderText ="" />

<asp:TemplateField>

</asp:TemplateField>

</Columns>

</asp:gridview>

There are multiple section in Gridview in <Columns> section:

<asp:BoundField />

<asp:TemplateField />

<asp:ButtonField />

<asp:CheckBoxField />

<asp:HyperLinkField />

<asp:CommandField />

<asp:ImageField />

1. In BoundField, We provide column name in DataField. We set header display text in HeaderText.

Example:

<asp:BoundField DataField="EmpName" HeaderText="Employee Name"

FooterStyle-Font-Bold="true"

ItemStyle-HorizontalAlign="right"

HeaderStyle-HorizontalAlign="right"

HeaderStyle-Width="300"

FooterText ="Employee Name"/>

2. Template Field is used for display column in customised way. In TemplateField, We can set any numbers of controls. It has three main section-

a. <HeaderTemplate></HeaderTemplate>

In HeaderTemplate, we can do all custom setting for header.

For header style for a particular column we use <HeaderStyle />

b. <ItemTemplate></ItemTemplate>

In ItemTemplate, we can do all setting related to the data shown.

For item style for a particular column we use <ItemStyle />

c. <FooterTemplate></FooterTemplate>

In FooterTemplate, We can do all setting that will come in footer of the table (Gridview).

Example :

<asp:TemplateField>

<HeaderTemplate>Address</HeaderTemplate>

<asp:Label ID="lblAddress" runat="server" Text='<%# Bind("Address") %>'></asp:Label>

</ItemTemplate>

<FooterTemplate>Address</FooterTemplate>

</asp:TemplateField>

Note 1 : Footer will shown only if we have set ShowFooter="true" in Gridview properties.

Note 2 : AllowPaging="true" is used to show paging in gridview. Set PageSize="10" value for change default PageSize (10) in Gridview properties.

Note 3 : Set AutoGenerateColumns="false" , when we want to define gridview columns in customised way.

Note 4 : For databind in a control in design (.aspx) page we use following method Eval, Bind method. Note the "<%# ....... %>" it is used for databind in design page.

Example:

<asp:Label ID="lblAddress" runat="server" Text='<%# Eval("Address") %>' ></asp:Label>

<asp:Label ID="lblAddress" runat="server" Text='<%# Bind("Address") %>' ></asp:Label>

Sunday, January 18, 2015

paging in asp.net gridview

Paging in asp.net gridview:

Question: How to bind gridview in asp.net and apply paging.

Answer: Here is an example of how to bind gridview.

Create a website and add a page. Suppose there is Default.aspx and its code behind page is Default.aspx.cs

In Default.aspx page add a gridview control and suppose its id is “grdEmployeeList”.

See below code:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

</head>

<body>

<div>

<asp:GridView ID="grdEmployeeList" runat="server" BackColor="White" AutoGenerateColumns="False"

BorderColor="#999999" BorderStyle="None" BorderWidth="1px" CellPadding="3" AllowPaging="True" PageSize="5"

EnableModelValidation="True" GridLines="Vertical" PagerSettings-Position="Bottom"

onpageindexchanging="grdEmployeeList_PageIndexChanging">

<asp:BoundField DataField="EMP_ID" HeaderText="Employee ID" />

<asp:BoundField DataField="EMP_NAME" HeaderText="Employee Name" />

<asp:BoundField DataField="DEPARTMENT_NAME" HeaderText="Department Name" />

<asp:BoundField DataField="SALARY" HeaderText="Salary" />

<asp:BoundField DataField="JOINING_DATE" HeaderText="Joining Date" />

</Columns>

</asp:GridView>

</div>

</form>

</body>

</html>

Here paging will be shown by AllowPaging="True" and PageSize="5". Default pagesize is 10. You can change page size whatever you want. Onpageindexchanging event is used for applying paging the gridview.

In Default.aspx.cs page write following page.

using System;

using System.Data;

using System.Data.SqlClient;

using System.Web.UI;

using System.Web.UI.WebControls;

public partial class _Default : System.Web.UI.Page

{

SqlConnection conn = new SqlConnection(@"Data Source=SHASHI-PC\SQLSERVER2008;Initial Catalog=TestDB;User ID=testdb_user;Password=testdb_pass");

//change connection string as per your choice like Data Source , Database name (Initial Catalog), User ID and password of database.

protected void Page_Load(object sender, EventArgs e)

{

if (!Page.IsPostBack)

{

BindEmplyeeList();//bind gridview on pageload for employee list

}

protected void grdEmployeeList_PageIndexChanging(object sender, GridViewPageEventArgs e)

{

if (e.NewPageIndex >= 0)

{

grdEmployeeList.PageIndex = e.NewPageIndex;//change pageindex of the grid

BindEmplyeeList();

}

//bind gridview from database

private void BindEmplyeeList()

{

SqlDataAdapter sda = new SqlDataAdapter("Select EMP_ID , EMP_NAME , DEPARTMENT_NAME, SALARY, JOINING_DATE From MST_EMPLOYEE_MASTER", conn);

DataTable dtEmp = new DataTable();

sda.Fill(dtEmp);

grdEmployeeList.DataSource = dtEmp;

grdEmployeeList.DataBind();

}

Result:

=================================================================

Hope you have enjoyed with this article. Please write your comments for it.

=================================================================

Saturday, January 3, 2015

Views in sql server

Views are virtual table that are compiled at runtime. We use that column in view that need to retrieve / query again and again. We can also make index, trigger on views. We can also write insert/ update query with a view like an actual table.

In the main reason of using view is for security purpose. Second reason is that the end user only knows a few columns. End users do not have knowledge about actual tables. By using view we can minimize writing long query again and again.

Syntax:

Syntax for create view is:

CREATE VIEW "VIEW NAME"

[Select statements]

Syntax for alter view is:

ALTER VIEW "VIEW NAME"

WITH SCHEMABINDING

[Select statements]

Syntax for drop view is:

DROP VIEW "VIEW NAME"

Note: We do not use BEGIN and END while creating/ altering view.

Example:

CREATE TABLE MST_EMPLOYEE_MASTER

(

EMP_ID NUMERIC(18,0)IDENTITY(1,1),

EMP_NAME VARCHAR(50) NOT NULL,

DEPARTMENT_NAME VARCHAR(25),

SALARY NUMERIC(18,2) DEFAULT 0,

JOINING_DATE DATETIME NOT NULL DEFAULT GETDATE()

);

INSERT INTO MST_EMPLOYEE_MASTER (EMP_NAME, DEPARTMENT_NAME, SALARY) VALUES('Shashi','Software Development',50000.00);

INSERT INTO MST_EMPLOYEE_MASTER (EMP_NAME, DEPARTMENT_NAME, SALARY) VALUES('Vikas','Software Development',100000.00);

INSERT INTO MST_EMPLOYEE_MASTER (EMP_NAME, DEPARTMENT_NAME, SALARY) VALUES('Prince','Software Testing',80000.00);

INSERT INTO MST_EMPLOYEE_MASTER (EMP_NAME, DEPARTMENT_NAME, SALARY) VALUES('Mathew','Software Testing',50000.00);

INSERT INTO MST_EMPLOYEE_MASTER (EMP_NAME, DEPARTMENT_NAME, SALARY) VALUES('Rahul','Software Testing',50000.00);

INSERT INTO MST_EMPLOYEE_MASTER (EMP_NAME, DEPARTMENT_NAME, SALARY) VALUES('Jonson','Human Resource',60000.00);

Example 1:

CREATE VIEW V_EMPLOYEE_MASTER

SELECT EMP_ID, EMP_NAME, DEPARTMENT_NAME FROM MST_EMPLOYEE_MASTER

And when select from view :

SELECT * FROM V_EMPLOYEE_MASTER;

Example 2:

ALTER VIEW V_EMPLOYEE_MASTER

SELECT EMP_ID, EMP_NAME, DEPARTMENT_NAME, SALARY FROM MST_EMPLOYEE_MASTER

And when select from view :

SELECT * FROM V_EMPLOYEE_MASTER;

Example 3:

DROP VIEW V_EMPLOYEE_MASTER

Some important points about View:

1. We cannot use order by in view without using TOP clause.

2. When we change table(s) schema from which view has been created, we have to refresh view for preventing unexpected result from view.

Syntax: EXECUTE SP_REFRESHVIEW 'View Name'

Example:EXECUTE SP_REFRESHVIEW 'V_EMPLOYEE_MASTER'

3. In a view we can use maximum 1024 column(s).

4. When we want to perform DML (INSERT/ UPDATE/ DELETE) operation on view then view should follow following condition:

i) Column(s) that we modify must be from only one table.

ii) Column(s) that we modify must not be created manually by other ways besides actual table column (actual column alias can be but not some operation performed and then created like column made from aggregate function like COUNT,MAX,AVG etc.)

iii) When we are doing INSERT operation all NOT NULL column(s) must be included except IDENTITY, DEFAULT column(s).

5. Use WITH SCHEMABINDING when we want to prevent table dropped and view remain orphaned.

Syntax:

CREATE VIEW "VIEW NAME"

WITH SCHEMABINDING

[Select statements]

Example:

CREATE VIEW V_EMPLOYEE_MASTER

WITH SCHEMABINDING

SELECT EMP_ID, EMP_NAME, DEPARTMENT_NAME, SALARY FROM DBO.MST_EMPLOYEE_MASTER

Thursday, January 1, 2015

select into create table sql server

Select into table in Sql Server can be used to create a new table from an existing table and insert data from existing table simultaneously.

By using select into table statement we can insert combined data of multiple table into a single table.

Columns created by select into table has same schema of the column into select statement.

Syntax:

Select Column(s) into table2 from table1

Exmple:

CREATE TABLE MST_EMPLOYEE_MASTER

(

EMP_ID NUMERIC(18,0)IDENTITY(1,1),

EMP_NAME VARCHAR(50),

DEPARTMENT_NAME VARCHAR(25),

SALARY NUMERIC(18,2),

JOINING_DATE DATETIME

);

SELECT * INTO MST_EMPLOYEE_BACKUP FROM MST_EMPLOYEE_MASTER WHERE JOINING_DATE>='01-JAN-2014';

Or,

SELECT EMP_ID, EMP_NAME, DEPARTMENT_NAME, SALARY, JOINING_DATE

INTO MST_EMPLOYEE_BACKUP FROM MST_EMPLOYEE_MASTER WHERE JOINING_DATE>='01-JAN-2014';

Insert Into Select sql server

We can insert into a table using Select Statement.

1. This can be used to copy data from one table to another table.

2. Copy Filtered Data of a Table into another Table in specified columns.

Syntax:

Insert into destination_table_name select * from source_table_name;

Insert into destination_table_name (columns_list) select columns_list from source_table_name;

EXAMPLE:

CREATE TABLE MST_EMPLOYEE_MASTER

(

EMP_ID NUMERIC(18,0)IDENTITY(1,1),

EMP_NAME VARCHAR(50),

DEPARTMENT_NAME VARCHAR(25),

SALARY NUMERIC(18,2),

JOINING_DATE DATETIME

);

CREATE TABLE MST_EMPLOYEE_BACKUP

(

EMP_ID NUMERIC(18,0),

EMP_NAME VARCHAR(50),

DEPARTMENT_NAME VARCHAR(25),

SALARY NUMERIC(18,2),

JOINING_DATE DATETIME

);

INSERT INTO MST_EMPLOYEE_BACKUP SELECT * FROM MST_EMPLOYEE_MASTER;

Or,

INSERT INTO MST_EMPLOYEE_BACKUP(EMP_ID, EMP_NAME, DEPARTMENT_NAME, SALARY, JOINING_DATE)SELECT EMP_ID, EMP_NAME, DEPARTMENT_NAME, SALARY, JOINING_DATE FROM MST_EMPLOYEE_MASTER;

Insert Into statement in sql

Insert Into statement in sql server:

We can use insert into statement in Sql Server in two ways:

1.Insert in all column of the a table

Syntax: Insert into values(value1, value2, value3,…..)

Example:

CREATE TABLE MST_EMPLOYEE_MASTER

(

EMP_ID NUMERIC(18,0)IDENTITY(1,1),

EMP_NAME VARCHAR(50),

DEPARTMENT_NAME VARCHAR(25),

SALARY NUMERIC(18,2)

);

INSERT INTO MST_EMPLOYEE_MASTER VALUES('Shashi','Software Development',50000.00);

(1 row(s) affected)

SELECT * FROM MST_EMPLOYEE_MASTER;

2.Insert in only specified column in a database

Syntax: Insert into (column1, column2, column3) values (value1, value2, value3);

Example:

INSERT INTO MST_EMPLOYEE_MASTER (EMP_NAME, DEPARTMENT_NAME, SALARY) VALUES('Vikas','Software Development',100000.00);

SELECT * FROM MST_EMPLOYEE_MASTER WHERE EMP_NAME='Vikas';