.Net , SQL & React

Sunday, 11 January 2015

Difference between @@IDENTITY(),SCOPE_IDENTITY() and IDENT_CURRENT(‘tablename’)

SELECT @@IDENTITY() returns the last IDENTITY value produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value.
@@IDENTITY will return the last identity value entered into a table in your current session, while it is not limited to the current scope. It will return the last identity value that is either explicitly created (creatd by any trigger or user defined function).

SELECT SCOPE_IDENTITY() returns the last IDENTITY value produced on a connection and within the same scope, regardless of the table that produced the value. SCOPE_IDENTITY(), like @@IDENTITY returns the last identity value created in the current session, but it will also limit it to your current scope as well. In other words, it will return the last identity value that you explicitly created (not includes the identity created by any trigger or user defined function).

SELECT IDENT_CURRENT(‘tablename’) returns the last IDENTITY value produced in a table. It does not depend on any session or scope, instead it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope.

Difference between ListView and GridView

ListView

GridView

It was introduced with Asp.Net 3.5.

It was introduced with Asp.Net 2.0.

Template driven.

Rendered as Table.

Built-in supports for Data grouping.

Need to write custom code.

Built-in supports for Insert operation.

Need to write custom code.

Provides flexible layout to your data.

Need to write custom code.

Performance is fast is compared to GridView.

Performance is slow as compared to ListView.

Difference between GridView and DataGrid

GridView

DataGrid

It was introduced with Asp.Net 2.0.

It was introduced with Asp.Net 1.0.

Built-in supports for Paging and Sorting.

For sorting you need to handle SortCommand event and rebind grid required and for paging you need to handle the PageIndexChanged event and rebind grid required.

Built-in supports for Update and Delete operations.

Need to write code for implementing Update and Delete operations.

Supports auto format or style features.

This features is not supported.

Performance is slow as compared to DataGrid

Performance is fast as compared to GridView.

Cross-Page Posting in asp.net using c#

Page - 1

<%@ 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></title>
</head>
<body>
<form id="form1" runat="server">
<div>
Fullname:
<asp:TextBox ID="txtFullname" runat="server"></asp:TextBox>

<asp:Button ID="Button1" runat="server" Text="Cross Page"
PostBackUrl="~/Default2.aspx"/>
</div>
</form>
</body>
</html>

Page - 2

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

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<%@ PreviousPageType VirtualPath="~/Default.aspx" %>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>
</div>
</form>
</body>

</html>

Page - 2 .CS

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

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

{

protected void Page_Load(object sender, EventArgs e)

{

if (Page.PreviousPage != null)

{

TextBox txt = (TextBox)PreviousPage.FindControl("txtFullname");

Label1.Text = txt.Text;

}

Cross page posting with Master page - ASP.NET

if (Page.PreviousPage != null)

{

var cp = PreviousPage.Master.FindControl("ContentPlaceHolder1") as ContentPlaceHolder;

TextBox txt1 = cp.FindControl("txtaddresse") as TextBox;

//TextBox txt = (TextBox)PreviousPage.FindControl("ContentPlaceHolder1_txtaddresse");

if (txt1.Text != null)

{

string s = txt1.Text;

}

Sunday, 14 December 2014

Insert ,Update,Edit,Delete In Gridview

Page.aspx

<asp:GridView ID="GridView1" runat="server" style="height:100px; overflow:auto" AutoGenerateColumns="False"

DataSourceID="sqldsEmpDetails" onrowcancelingedit="GridView1_RowCancelingEdit"

onrowediting="GridView1_RowEditing" onrowupdating="GridView1_RowUpdating"

DataKeyNames="id" onrowdeleting="GridView1_RowDeleting"

onrowcommand="GridView1_RowCommand" onrowcreated="GridView1_RowCreated"

ShowHeader="False">

<asp:TemplateField HeaderText="Edit">

<asp:LinkButton ID="lkbtnUpdate" runat="server" CommandName="Update"

CommandArgument='<%# Eval("Id") %>'>Update</asp:LinkButton>

<asp:LinkButton ID="lkbtnCancal" runat="server" CommandName="Cancel">Cancal</asp:LinkButton>

</EditItemTemplate>

<asp:LinkButton ID="lkbtninsert" runat="server" CommandName="AddNew"

ValidationGroup="Insert">Insert</asp:LinkButton>

<asp:LinkButton ID="lkbtncancel" runat="server" onclick="lkbtncancel_Click">Cancel</asp:LinkButton>

</FooterTemplate>

<asp:LinkButton ID="lkbtnEdit" runat="server" CommandName="Edit">Edit</asp:LinkButton>

<asp:LinkButton ID="lkbtnDelete" runat="server"

CommandArgument='<%# Eval("id") %>' CommandName="Delete"

onclientclick="javascript:return DeleteItem();">Delete</asp:LinkButton>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Name">

<asp:TextBox ID="txtname" runat="server" Text='<%# Eval("Name") %>'></asp:TextBox>

</EditItemTemplate>

<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>

<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server"

ControlToValidate="TextBox1" ErrorMessage="*" ForeColor="Red"

ValidationGroup="Insert"></asp:RequiredFieldValidator>

</FooterTemplate>

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

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Addresse">

<asp:TextBox ID="txtAddresse" runat="server" Text='<%# Eval("Addresse") %>'></asp:TextBox>

</EditItemTemplate>

<asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>

<asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server"

ControlToValidate="TextBox2" ErrorMessage="*" ForeColor="Red"

ValidationGroup="Insert"></asp:RequiredFieldValidator>

</FooterTemplate>

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

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Cell">

<asp:TextBox ID="txtcell" runat="server" Text='<%# Eval("Cell") %>'></asp:TextBox>

</EditItemTemplate>

<asp:TextBox ID="TextBox3" runat="server"></asp:TextBox>

<asp:RequiredFieldValidator ID="RequiredFieldValidator3" runat="server"

ControlToValidate="TextBox3" ErrorMessage="*" ForeColor="Red"

ValidationGroup="Insert"></asp:RequiredFieldValidator>

</FooterTemplate>

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

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Id" Visible="False">

<asp:TextBox ID="txtid" runat="server" Text='<%# Eval("ID") %>'></asp:TextBox>

</EditItemTemplate>

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

</ItemTemplate>

</asp:TemplateField>

</Columns>

</asp:GridView>

<asp:Button ID="btnnewrow" runat="server" onclick="btnnewrow_Click"

Text="Add New" />

//conformation box for Delete

function DeleteItem() {

if (confirm("Are you sure you want to delete ...?")) {

return true;

}

return false;

}

</script>

Page.CS

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Data.Common;

using System.Data;

using MySql.Data.MySqlClient;

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

{

MySqlConnection conn = new MySqlConnection();

MySqlCommand cmd = new MySqlCommand();

MySqlDataAdapter adp = new MySqlDataAdapter();

sendmail objmail = new sendmail();

protected void Page_Load(object sender, EventArgs e)

{

Label1.Text = string.Empty;

conn = new MySqlConnection("Server=localhost ;Database=emp ;Uid=root;Pwd=send;");

conn.Open();

Bind();

}

protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)

{

GridView1.EditIndex = e.NewEditIndex;

GridView1.DataBind();

}

protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)

{

LinkButton lk = (LinkButton)GridView1.Rows[e.RowIndex].FindControl("lkbtnUpdate");

string id = lk.CommandArgument.ToString();

// string iidd = Gvempdetails.DataKeys[e.RowIndex].Value["id"];

// int id = Convert.ToInt32(Gvempdetails.DataKeys[e.RowIndex].Item["youfield"].tostring());

// TextBox id = (TextBox)GridView1.Rows[e.RowIndex].FindControl("txtid");

TextBox name = (TextBox)GridView1.Rows[e.RowIndex].FindControl("txtname");

TextBox Address = (TextBox)GridView1.Rows[e.RowIndex].FindControl("txtAddresse");

TextBox cell = (TextBox)GridView1.Rows[e.RowIndex].FindControl("txtcell");

string SaveQuery = "UPDATE empdetails SET NAME='" + name.Text + "',addresse='" + Address.Text + "',Cell=" + cell.Text + " WHERE id=" + id + "";

//cmd = new MySqlCommand(SaveQuery, conn);

//cmd.ExecuteNonQuery();

sqldsEmpDetails.UpdateCommand = SaveQuery;

sqldsEmpDetails.Update();

GridView1.EditIndex = -1;

GridView1.DataBind();

}

protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)

{

GridView1.EditIndex = -1;

GridView1.DataBind();

}

protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)

{

string test = GridView1.DataKeys[e.RowIndex].Value.ToString();

LinkButton lk = (LinkButton)GridView1.Rows[e.RowIndex].FindControl("lkbtnDelete");

string id = lk.CommandArgument.ToString();

sqldsEmpDetails.DeleteCommand = "Delete From empdetails where id="+id+"";

sqldsEmpDetails.DataBind();

GridView1.DataBind();

}

protected void btnnewrow_Click(object sender, EventArgs e)

{

GridView1.ShowFooter = true;

}

protected void lkbtncancel_Click(object sender, EventArgs e)

{

GridView1.ShowFooter = false;

}

public void Bind()

{

DataSet ds= new DataSet();

adp = new MySqlDataAdapter("SELECT id,NAME,Addresse,Cell FROM empdetails ORDER BY NAME ASC", conn);

adp.Fill(ds, "c");

GridView1.DataSource = ds.Tables[0];

GridView1.DataBind();

}

Saturday, 6 December 2014

SQL Server ranking functions

In SQL Server there is 4 ranking functions:

ROW_NUMBER	Returns the rank of rows within the partition of a result set, without any gaps in the ranking. The rank of a row is one plus the number of distinct ranks that come before the row in question.
RANK	Returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of ranks that come before the row in question.
DENSE_RANK	Returns the rank of rows within the partition of a result set, without any gaps in the ranking. The rank of a row is one plus the number of distinct ranks that come before the row in question.
NTILE	Distributes the rows in an ordered partition into a specified number of groups. The groups are numbered, starting at one. For each row, NTILE returns the number of the group to which the row belongs.

below is the syntax:

ROW_NUMBER ( ) OVER ( [ <partition_by_clause> ] <order_by_clause> )

RANK ( ) OVER ( [ < partition_by_clause > ] < order_by_clause > )

DENSE_RANK ( ) OVER ( [ < partition_by_clause > ] < order_by_clause > )

NTILE (integer_expression) OVER ( [ <partition_by_clause> ] < order_by_clause > )

Saturday, 29 November 2014

Three Tire

Page.aspx

Page.aspx.cs

protected void btnsubmit_Click(object sender, EventArgs e)

{

BLClass _b = new BLClass();

Info _I = new Info();

_I._p_Fullname = txtFullname.Text;

_b.InsertUserInformation(_I);

}

BLClass

public class BLClass

{

public BLClass()

{

}

//SQL Connection string

string ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["empConnectionString"].ToString();

#region Insert User Details

public string InsertUserInformation(Info _I)

{

SqlConnection con = new SqlConnection(ConnectionString);

con.Open();

SqlCommand cmd = new SqlCommand("spname", con);

cmd.CommandType = CommandType.StoredProcedure;

try

{

cmd.Parameters.AddWithValue("@Fullname", _I._p_Fullname);

cmd.Parameters.AddWithValue("@f", "I");

cmd.ExecuteNonQuery();

con.Close();

string i = "0";

return i;

}

catch (Exception ex)

{

throw ex;

}

finally

{

cmd.Dispose();

con.Close();

con.Dispose();

}

#endregion

}

Info

public class Info

{

private string _Fullname;

public string _p_Fullname

{

get

{return _Fullname;}

set{_Fullname=value;}

}

public Info()

{

}