view is one type of virtual table so view does not contain physical memory.
view is just some columns and rows combination which is selected from one or
more table.just it contain query.
view benefits :- view is provide abstract layer over database tables. it's provide security.
only some non sensitive columns select in query so confidential data can not
appear that particular user.
for example:-
create view view_name select name from tbladmin.
On web 1000 of records are in Database.We need only small amount
of data i.e 10,15 etc. default paging all records are load and
it take more time. Performance of our Application decreasing.
we create custom Optimize Paging.
In this paging we retrieve only our page size data only from database
for that we create store procedure.
Customize or Manual Paging Store procedure set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: <Author,,ASPdotNet-Example.blogspot.com> -- Create date: <Create Date,,May 20 2011> -- Description: <Description,,> -- =============================================
CREATE PROCEDURE [dbo].[SP_Paging]
@PageIndex int, @PageSize int,
@TotalRecord int output, AS BEGIN SET NOCOUNT ON; select @TotalRecord=count(id) from tblemp
select * from
(
select row_number() over (order by [id] asc )as RowNumber,
Name,depid,Salary from tblemp
)as a
where a.rownumber between (@PageIndex-1)*@PageSize+1 AND
(((@PageIndex-1)*@PageSize+1)+@PageSize)-1 END
There are many way Insert data in database table.
I described Five way here.
1. using sql command techniques insert data into database table
2.Using Bind parameters sql command techniques insert data into database table
3.using sql command with Parameters techniques insert data into database table
4.Using ExecuteScalar and SqlCommand Insert data into Database Table
5.using CommandBuilder insert datat in form datatable in datadase
5 is Best and Optimize way of insert data into database table because in with
using conncetionless (SqlDataAdpter) concepts and in insert in datatable form so
it is best in Performance view and it can use with asp.net where large amount of
users are working.
This is the best and optimize way of insert data in to database table
with ado.net
InsertingDataUsingCommandBuilder
using System; using System.Data; using System.Data.SqlClient; public class InsertingDataUsingCommandBuilder
{static void Main(string[] args)
{
string Cn="DataSource=(local);InitialCatalog=CaseManager;Integrated Security=true";
string Query="SELECT ID, Contact, Email FROM Test";
SqlConnection objConnection = new SqlConnection(Cn);
SqlDataAdapter objDataAdapter = new SqlDataAdapter(Query,objConnection);
SqlCommandBuilder objCmd = new SqlCommandBuilder(objDataAdapter);
DataSet objDataSet = new DataSet();
objDataAdapter.Fill(objDataSet);
DataRow objRow = objDataSet.Tables[0].NewRow();
objRow["ID"] = 520;
objRow["Contact"] = "Bill";
objRow["Email"] = "Ray";
objDataSet.Tables[0].Rows.Add(objRow);
objDataAdapter.Update(objDataSet);
}
}
using System;
using System.Data;
using System.Data.SqlClient;
public class InsertingDataUsingSQLStatementsExecuteScalar
{
static void Main(string[] args)
{string CN="Data Source=(local); Initial Catalog = DBNAME; Integrated Security=true; SqlConnection objConnection = new SqlConnection(CN); String Query ="INSERT INTO tblTest(ID, Contact, Email) VALUES(3, 'Bill', 'John')"; SqlCommand objCmd = new SqlCommand(Query, onConnection);objConnection.Open();objCmd.ExecuteScalar(); objConnection.Close(); }
}
using System;
using System.Data;
using System.Data.SqlClient;
class UsingParameterAndCommand
{
public static void Main()
{
string cn="server=localhost;database=DBTEST;uid=sa;pwd=sa";
SqlConnection Connection = new SqlConnection(cn);
SqlCommand objCmd = Connection.CreateCommand();
objCmd.CommandText =
"INSERT INTO ASPTable (" +
" CustomerID, CompanyName, ContactName" +
") VALUES (" +
" @CustomerID, @CompanyName, @ContactName" +
")";
objCmd.Parameters.Add("@CustomerID", "AB001");
objCmd.Parameters.Add("@CompanyName", "MicroSoft");
objCmd.Parameters.Add("@ContactName", "Bill");
Connection.Open();
objCmd.ExecuteNonQuery();
Console.WriteLine("Successfully added row to ASPTable ");
Connection.Close();
}
}
This techniques is better than
using sql command techniques insert data into database table
because SqlDataAdapter is used here but not optimize solution.
Bind Parameters techniques for Insert data
using System;
using System.Data;
using System.Data.SqlClient;
class bindParametersExample
{
static void Main()
{
string ConnectionString ="server=(local)\\SQLEXPRESS;database=DBTEST;Integrated Security=SSPI"; string Query = @"select * from aspdotnetTable"; string InsertQuery = @"insert into aspdotnetTable(firstname,lastname)
values(@firstname,@lastname)";
SqlConnection objConnection = new SqlConnection(ConnectionString );
try{
SqlDataAdapter objAd = new SqlDataAdapter();
da.SelectCommand = new SqlCommand(Query, objConnection );
DataSet objDs = new DataSet();
objAd.Fill(objDs , "aspdotnetTable");
DataTable objDataTable = objDs.Tables["aspdotnetTable"];
DataRow objRow = objDataTable.NewRow();
objRow["firstname"] = "Bill";
objRow["lastname"] = "Gates";
objDataTable.Rows.Add(objRow);
foreach (DataRow row in objDataTable.Rows){
Console.WriteLine(
"{0} {1}",
row["firstname"].ToString().PadRight(15),
row["lastname"].ToString().PadLeft(25));
}
// Insert data in to aspdotnetTable
SqlCommand objCmd = new SqlCommand(InsertQuery, objConnection);
objCmd.Parameters.Add("@firstname", SqlDbType.NVarChar, 10, "firstname");
objCmd.Parameters.Add("@lastname", SqlDbType.NVarChar, 20, "lastname");
objAd.InsertCommand = objCmd;
objAd.Update(objDs, "aspdotnetTable");
} catch(Exception e) {
Console.WriteLine("Error: " + e);
} finally {
objConnection.Close();
}
}
}we must add using System.Data.SqlClient; namespace. now following code you can use.This is Vary old Techniques. If we use techniques with Asp.net there is Pooling required because if there is large amount of users use this page than it is difficult to handle connnection. Insert into database using sqlcommand {
static void Main()
{string CN="server=(local)\\SQLEXPRESS;database=DBTEST;Integrated Security=SSPI";
SqlConnection objCn = new SqlConnection(CN);
SqlCommand objCmd= objCn.CreateCommand();
try
{objCn.Open();
objCmd.CommandText = "CREATE DATABASE NewDB";
Console.WriteLine(objCmd.CommandText);
objCmd.ExecuteNonQuery();
Console.WriteLine("Now is Database created and database");
objCn.ChangeDatabase("NewDB");
objCmd.CommandText = "CREATE TABLE aspdotnetTable(COL1 integer)";
Console.WriteLine(objCmd.CommandText);
Console.WriteLine("NO Rows Affected is: {0}", objCmd.ExecuteNonQuery());
objCmd.CommandText = "INSERT INTO aspdotnetTable VALUES (100)";
Console.WriteLine(objCmd.CommandText);
Console.WriteLine("NO Rows Affected is: {0}", objCmd.ExecuteNonQuery());
} catch (SqlException ex) {
Console.WriteLine(ex.ToString());
} finally {
objCn.Close();
Console.WriteLine("Connection Closed.");
}
}
}<%@ 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"> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <title>Untitled Page</title> </head> <body> <form id="form1" runat="server"> <div> <asp:DataList ID="dtlEmp" runat="server"> <HeaderTemplate> <table> <tr> <th> ID </th> <th> Name </th> <th> DepID </th> <th> Salary </th> </tr> </HeaderTemplate> <ItemTemplate> <tr> <td>
<asp:Label ID="lblID" runat="server"
Text='<%# Bind("id") %>'></asp:Label>
</td>
<td>
<asp:Label ID="lblName" runat="server" Text='<%# Bind("Name") %>'></asp:Label>
</td>
<td><asp:Label ID="lblDepID" runat="server" Text='<%# Bind("depid") %>'></asp:Label>
</td>
<td>
<asp:Label ID="lblSalary" runat="server" Text='<%# Bind("Salary") %>'></asp:Label>
</td>
</tr>
</ItemTemplate>
<FooterTemplate>
</table>
</FooterTemplate>
</asp:DataList>
<table>
<tr>
<td><a href="Default.aspx#BookMark" id="First" onserverclick="ShowFirstPage"
runat="server"> <img src="firstpage.gif" alt="" /> </a> </td> <td> <a href="Default.aspx#BookMark" id="Prev" onserverclick="ShowPrevPage"
runat="server"> <img src="prevpage.gif" alt="" /> </a> </td> <td> <a href="Default.aspx#BookMark" id="Next" onserverclick="ShowNextPage"
runat="server"> <img src="nextpage.gif" alt="" /> </a> </td> <td>
<a href="Default.aspx#BookMark" id="Last" onserverclick="ShowLastPage"
runat="server"> <img src="lastpage.gif" alt="" /> </a> </td> </tr> </table> <asp:Label ID="lblCurrentIndex" runat="server" Text="0"></asp:Label> <asp:Label ID="lblPagesize" runat="server" Text="5"></asp:Label> <asp:Label ID="lblRecordCount" runat="server" Text="Label"></asp:Label> </div> </form> </body> </html>
using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
public partial class _Default : System.Web.UI.Page
{
SqlConnection objcn;
DataSet objDs;
SqlDataAdapter objAd;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
BindData();
}
public void BindData()
{
string Query="select * from tblEmp";
objcn = new SqlConnection(ConfigurationManager.ConnectionStrings["conn"].ConnectionString);
objAd = new SqlDataAdapter(Query, objcn);
objDs = new DataSet();
if (!IsPostBack)
{
objAd.Fill(objDs);
lblRecordCount.Text=(objDs.Tables[0].Rows.Count).ToString();
//objDs=null;
//objDs=new DataSet();
}
objAd.Fill(objDs,Convert.ToInt32(lblCurrentIndex.Text),Convert.ToInt32(lblPagesize.Text),"tblEmp");
dtlEmp.DataSource = objDs.Tables["tblEmp"].DefaultView;
dtlEmp.DataBind();
}
public void ShowFirstPage(object sender, EventArgs e)
{
lblCurrentIndex.Text = "0";
BindData();
}
public void ShowPrevPage(object sender, EventArgs e)
{
lblCurrentIndex.Text=((Convert.ToInt32(lblCurrentIndex.Text))-Convert.ToInt32(lblPagesize.Text)).ToString();
if(Convert.ToInt32(lblCurrentIndex.Text)<0)
{
lblCurrentIndex.Text="0";
}
BindData();
}
public void ShowNextPage(object sender, EventArgs e)
{
if (Convert.ToInt32(lblCurrentIndex.Text) + Convert.ToInt32(lblPagesize.Text) < Convert.ToInt32(lblRecordCount.Text))
{
lblCurrentIndex.Text = (Convert.ToInt32(lblCurrentIndex.Text)+ Convert.ToInt32(lblPagesize.Text)).ToString();
}
BindData();
}
public void ShowLastPage(object sender, EventArgs e)
{
int Mod = Convert.ToInt32(lblRecordCount.Text) /Convert.ToInt32(lblPagesize.Text);
if (Mod > 0)
{
lblCurrentIndex.Text = (Convert.ToInt32(lblRecordCount.Text) -Mod).ToString();
}
else
{
lblCurrentIndex.Text=(Convert.ToInt32(lblRecordCount.Text)-Convert.ToInt32(lblPagesize.Text)).ToString(); } BindData(); } }