Difference Between User Defined functions And Stored Procedure

1>Procedure can return zero or n values whereas function can return one value which is mandatory.

2>Procedures can have input,output parameters for it whereas functions can have only input parameters.

3>Procedure allow select as well as DML statement in it whereas function allow only select statement in it.

4>Functions can be called from procedure whereas procedures cannot be called from function.

5>Exception can be handled by try-catch block in a procedure whereas try-catch block cannot be used in a function.

6>We can go for transaction management in procedure whereas we can't go in function.

7>Procedures can not be utilized in a select statement whereas function can be embedded in a select statement.

DataList Custom Paging

.cs


using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.Sql;
using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page
{
PagedDataSource pds = new PagedDataSource();
DataTable dt = new DataTable();
static int lastPage = 0;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
//lastPage = 0;
ddlPageSize.Items.Add("5");
ddlPageSize.Items.Add("10");
ddlPageSize.Items.Add("15");
BindGrid();
}
}
private void BindGrid()
{
string connectionString = @"Data Source =CB-36A\SQLEXPRESS ;Initial Catalog=master;Integrated Security=True";
SqlConnection myConnection = new SqlConnection(connectionString);
SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Country", myConnection);
DataTable dt = new DataTable();
da.Fill(dt);
pds.DataSource = dt.DefaultView;
pds.AllowPaging = true;
pds.PageSize = Convert.ToInt32(ddlPageSize.SelectedValue);
pds.CurrentPageIndex = CurrentPage;
lnkbtnNext.Enabled = !pds.IsLastPage;
lnkbtnPrevious.Enabled = !pds.IsFirstPage;
lnkbtnFirst.Enabled = !pds.IsFirstPage;
lnkbtnLast.Enabled = !pds.IsLastPage;
Countrydlst.DataSource = pds;
Countrydlst.DataBind();
doPaging();
}
protected void ddlPageSize_SelectedIndexChanged(object sender, EventArgs e)
{
BindGrid();
doPaging();
}
public int CurrentPage
{
get
{
if (this.ViewState["CurrentPage"] == null)
return 0;
else
return Convert.ToInt16(this.ViewState["CurrentPage"].ToString());
}
set
{
this.ViewState["CurrentPage"] = value;
}

}
private void doPaging()
{
DataTable dt = new DataTable();
dt.Columns.Add("PageIndex");
dt.Columns.Add("PageText");
for (int i = 0; i < pds.PageCount; i++)
{
DataRow dr = dt.NewRow();
dr[0] = i;
dr[1] = i + 1;
dt.Rows.Add(dr);
}
lastPage = int.Parse(dt.Rows[dt.Rows.Count - 1][0].ToString());
Pagingdlst.DataSource = dt;
Pagingdlst.DataBind();
}
protected void Pagingdlst_ItemCommand(object source, DataListCommandEventArgs e)
{
if (e.CommandName.Equals("lnkbtnPaging"))
{
CurrentPage = Convert.ToInt16(e.CommandArgument.ToString());
BindGrid();
}
}
protected void lnkbtnPrevious_Click(object sender, EventArgs e)
{
CurrentPage -= 1;
BindGrid();
}
protected void lnkbtnNext_Click(object sender, EventArgs e)
{
CurrentPage += 1;
BindGrid();
}
protected void Pagingdlst_SelectedIndexChanged(object sender, EventArgs e)
{
CurrentPage = 0;
BindGrid();
}
protected void Pagingdlst_ItemDataBound(object sender, DataListItemEventArgs e)
{
LinkButton lnkbtnPage = (LinkButton)e.Item.FindControl("lnkbtnPaging");
if (lnkbtnPage.CommandArgument.ToString() == CurrentPage.ToString())
{
lnkbtnPage.Enabled = false;
lnkbtnPage.Font.Bold = true;
}
}
protected void lnkbtnFirst_Click(object sender, EventArgs e)
{
CurrentPage = 0;
BindGrid();
}
protected void lnkbtnLast_Click(object sender, EventArgs e)
{
CurrentPage = lastPage;
BindGrid();
}
}

How to Export the File in to Pdf, Doc and xls Using Crystal Report

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using CrystalDecisions.Shared;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.ReportSource;
using System.IO;
public partial class _Default : System.Web.UI.Page
{
//protected CrystalDecisions.Web.CrystalReportViewer CrystalReportViewer1;
//protected System.Web.UI.WebControls.Button Button1;
ReportDocument rDoc = new ReportDocument();
protected void Page_Load(object sender, EventArgs e)
{
string FilePath = Server.MapPath("CrystalReport.rpt");
rDoc.Load(FilePath);
DataSet ds = new DataSet();
CrystalReportViewer1.ReportSource = rDoc;
CrystalReportViewer1.Visible = true;
}
protected void Button1_Click(object sender, EventArgs e)
{
MemoryStream oStream= new MemoryStream (); // using System.IO
switch (DropDownList1.SelectedItem.Text )
{
case "Pdf":
oStream = (MemoryStream)rDoc.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat);
Response.Clear();
Response.Buffer = true;
Response.ContentType = "application/pdf";
Response.AddHeader("Content-Disposition", "attachment;filename=SearchResult.pdf");
Response.BinaryWrite(oStream.ToArray());
Response.End();
break ;
case "Doc":
oStream = (MemoryStream)rDoc.ExportToStream(CrystalDecisions.Shared.ExportFormatType.WordForWindows);
Response.Clear();
Response.Buffer = true;
Response.ContentType = "application/doc";
Response.AddHeader("Content-Disposition", "attachment;filename=SearchResult.doc");
Response.BinaryWrite(oStream.ToArray());
Response.End();
break;
case "Excel":
oStream = (MemoryStream)rDoc.ExportToStream(CrystalDecisions.Shared.ExportFormatType.Excel);
Response.Clear();
Response.Buffer = true;
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", "attachment;filename=SearchResult.xls");
Response.BinaryWrite(oStream.ToArray());
Response.End();
break;
}
}
}

Insert, Update, Delete with Gridview....simple way using Stored Procedure

Introduction
This is a simple way to display, Update, Delete and Insert through a single page.

Using the code
Many of us may be encountered with Update and Delete operations with GridView. I’m explaining this with more functionality that is Inserting record through the GridView. There could me more way to achieve this functionality but I think it’s a very simple way to achieve this. I’m taking a very simple table named “quest_categories”

CREATE TABLE [dbo].[quest_categories](
[cat_id] [int] IDENTITY(1,1) NOT NULL,
[cat_name] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_quest_categories] PRIMARY KEY CLUSTERED
(
[cat_id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Here is the Code for “Add Category”

protected void btnAdd_Click(object sender, EventArgs e)

{

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings\["exam_moduleConnectionString"].ConnectionString);

SqlDataAdapter da = new SqlDataAdapter("SELECT cat_id, cat_nameFROMquest_categories",con);

DataTable dt = new DataTable();

da.Fill(dt);
// Here we'll add a blank row to the returned DataTableDataRow dr = dt.NewRow();dt.Rows.InsertAt(dr, 0);//Creating the first row of GridView to be EditableGridView1.EditIndex = 0;

GridView1.DataSource = dt;

GridView1.DataBind();//Changing the Text for Inserting a New Record((LinkButton)GridView1.Rows[0].Cells[0].Controls[0]).Text = "Insert";
}

And according to Text Diplays we’ll do further processing as “Update” or “Insert” record.
Here is the code for “RowUpdating” event.

protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
if (((LinkButton)GridView1.Rows[0].Cells[0].Controls[0]).Text == "Insert")
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["exam_moduleConnectionString"].ConnectionString);
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "INSERT INTO quest_categories(cat_name) VALUES(@cat_name)";
cmd.Parameters.Add("@cat_name", SqlDbType.VarChar).Value = ((TextBox)GridView1.Rows[0].Cells[2].Controls[0]).Text;
cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();
con.Close();

Response.Redirect("quest_categories.aspx");
}
else
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["exam_moduleConnectionString"].ConnectionString);
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "UPDATE quest_categories SET cat_name=@cat_name WHERE cat_id=@cat_id";
cmd.Parameters.Add("@cat_name", SqlDbType.VarChar).Value = ((TextBox)GridView1.Rows[e.RowIndex].Cells[2].Controls[0]).Text;
cmd.Parameters.Add("@cat_id", SqlDbType.Int).Value = Convert.ToInt32(GridView1.Rows[e.RowIndex].Cells[1].Text);
cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
GridView1.EditIndex = -1;
BindData();
}
Rest operations are same as you might have done before so I’m not explaining it further.