Stored procedure:Out put Parameter..using @@rowcount

Please check the below example:

alter PROCEDURE UpdateLogin
@UserName nvarchar(15),
@UpdateCount int OUTPUT
AS
update login set username=@UserName where userid<4
select @UpdateCount = @@rowcount
RETURN @UpdateCount

DECLARE @TheCount INT
exec UpdateLogin @UserName = 'sa', @UpdateCount = @TheCount OUTPUT
SELECT TheCount = @TheCount

DataGrid--->Sample for Accessing no of row for a 'Single Click'

CODE PART..

//The links important..
http://www.codedigest.com/Articles/ASPNET/132_GridView_with_CheckBox_%E2%80%93_Select_All_and_Highlight_Selected_Row.aspx
http://ramanisandeep.wordpress.com/tag/checkuncheck-all-items-in-an-asp-net-checkbox-list-using-jquery/
http://fun2code.blogspot.com/


using System;
using System.Data;
using System.Configuration;
using System.Collections;
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 Connection;

public partial class FakeMemberMark : System.Web.UI.Page
{
ClsConnection objCon = new ClsConnection();
string sqlStr = string.Empty;
protected void Page_Load(object sender, EventArgs e)
{
try
{
//Session["adminloginname"] = "satheesh";
Label5.Visible = false;

if ((string)Session["adminloginname"] != null)
{
if (!IsPostBack)
{
lblcaption.Text = "Admin � Fake Member Marking";
Label2.Text = "[ Hi, " + Session["adminloginname"].ToString() + "| <a href=adminwelcome.aspx>Control panel</a> | <a href=adminlogout.aspx >Logout</a> ]";
loadGrid();

}
}
else
{
Response.Redirect("adminlogin.aspx");
}
}
catch
{
Response.Redirect("adminlogin.aspx");
}
}



protected void GridView1_RowCreated(object sender, GridViewRowEventArgs e)
{
//if (e.Row.RowType == DataControlRowType.DataRow && (e.Row.RowState ==DataControlRowState.Normal || e.Row.RowState == DataControlRowState.Alternate))
//{
// CheckBox chkBxSelect = (CheckBox)e.Row.Cells[1].FindControl("chkBxSelect");
// CheckBox chkBxHeader = (CheckBox)this.GridView1.HeaderRow.FindControl("chkAll");
// chkBxSelect.Attributes["onclick"] = string.Format("javascript:ChildClick(this,'{0}');",chkBxHeader.ClientID);
//}
}
protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
Label lblIsFake = (Label)e.Row.FindControl("lblIsFake");
string IsFake = lblIsFake.Text.Trim().ToString();
CheckBox chkBxSelect = (CheckBox)e.Row.FindControl("chkBxSelect");
if (IsFake == "True")
{
chkBxSelect.Checked = true;
}
else
{
chkBxSelect.Checked = false;
}
try
{
HyperLink Hyperphotourl = (HyperLink)e.Row.FindControl("Hyperphotourl");

string HomeUrl = "http://" + HttpContext.Current.Request.Url.Host.ToString();//
string photourl = HomeUrl + "/" + Hyperphotourl.ImageUrl.ToString();
Hyperphotourl.ImageUrl = photourl;

Label lblusername = (Label)e.Row.FindControl("lblusername");
Hyperphotourl.NavigateUrl = "../" + lblusername.Text.ToString() + "/profile";
}
catch
{
}

}
}
protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
{


try
{
Label5.Visible = true;
if (e.CommandName == "SET")
{
int IsFake = 0;
for (int i = 0; i < GridView1.Rows.Count; i++)
{

int userid = int.Parse(GridView1.Rows[i].Cells[0].Text.ToString());
CheckBox chbTemp = GridView1.Rows[i].FindControl("chkBxSelect") as CheckBox;
if (chbTemp.Checked)
{
// Response.Write(GridView1.Rows[i].Cells[0].Text + "<BR>");
IsFake = 1;
}
UpdateTheDetails(userid, IsFake);
}
Label5.Visible = true;
Label5.Text = "Success..Marked the Fake Members Successfully..";
}
}
catch { }
}
protected void btnSubmit_Click(object sender, EventArgs e)
{

foreach (GridViewRow row in GridView1.Rows) //GridView1.PageSize * GridView1.PageCount
{
int IsFake = 0;
//Label lfilName = (Label)row.FindControl("lblfilename");
int userid = int.Parse(row.Cells[0].Text.ToString());

CheckBox chbTemp = (CheckBox)row.FindControl("chkBxSelect");
if (chbTemp.Checked)
{

IsFake = 1;
}
UpdateTheDetails(userid, IsFake);
}
Label5.Visible = true;
Label5.Text = "Success..Marked the Fake Members Successfully..";
}
protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
Label5.Visible = false;
GridView1.PageIndex = e.NewPageIndex;
loadGrid();
}

//the functions listed below...................
private bool UpdateTheDetails(int userid, int IsFake)
{
bool status = false;
try
{
sqlStr = "Update membersetting set IsFake=" + IsFake + " where userid=" + userid;
if (objCon.SqlExecuteQuery(sqlStr) == "Success")
{
status = true;
}
}
catch { }
return status;
}
private void loadGrid()
{
GridView1.Visible = false;
try
{
DataSet ds = GetUserDetails();
if (ds.Tables[0].Rows.Count > 0)
{
GridView1.DataSource = ds;
GridView1.DataBind();
GridView1.Visible = true;
}
else
{
Label5.Visible = true;
Label5.Text = "Sorry No User Details found";
}
}
catch
{
}
}
public DataSet GetUserDetails()
{
DataSet ds = new DataSet();
try
{

string sqlStr = "select l.userid,l.username,m.firstname,m.email,r.photourl,isnull(ms.IsFake,0) as IsFake from membermaildetails m,login l,registration r,membersetting ms where l.userid= m.loginid and r.loginid=m.loginid and ms.userid= m.loginid order by l.userid";
ds = objCon.SelectDataset(sqlStr, "0");
}
catch
{
}
return ds;
}


}

//*******************************************************************************

DESIGN PART..

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

<!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 >
<script type="text/javascript">

function HighlightRow(chkB)

{

var IsChecked = chkB.checked;

if(IsChecked)

{

chkB.parentElement.parentElement.style.backgroundColor='black';

chkB.parentElement.parentElement.style.color='white';

}else

{

chkB.parentElement.parentElement.style.backgroundColor='white';

chkB.parentElement.parentElement.style.color='black';

}

}
function SelectAllCheckboxesSpecific(spanChk)

{

var IsChecked = spanChk.checked;

var Chk = spanChk;

Parent = document.getElementById('GridView1');

var items = Parent.getElementsByTagName('input');

for(i=0;i<items.length;i++)

{

if(items[i].id != Chk && items[i].type=="checkbox")

{

if(items[i].checked!= IsChecked)

{

items[i].click();

}

}

}

}
//*******************************************new one not using*******************

var TotalChkBx;

var Counter;

window.onload = function()

{

//Get total no. of CheckBoxes in side the GridView.

TotalChkBx = parseInt('<%= this.GridView1.Rows.Count %>');

//Get total no. of checked CheckBoxes in side the GridView.

Counter = 0;

}

function HeaderClick(CheckBox)

{

//Get target base & child control.

var TargetBaseControl = document.getElementById('<%= this.GridView1.ClientID %>');

var TargetChildControl = "chkBxSelect";



//Get all the control of the type INPUT in the base control.

var Inputs = TargetBaseControl.getElementsByTagName("input");



//Checked/Unchecked all the checkBoxes in side the GridView.

for(var n = 0; n < Inputs.length; ++n)

if(Inputs[n].type == 'checkbox' && Inputs[n].id.indexOf(TargetChildControl,0) >= 0)

Inputs[n].checked = CheckBox.checked;

//Reset Counter

Counter = CheckBox.checked ? TotalChkBx : 0;

}

function ChildClick(CheckBox, HCheckBox)

{

//get target base & child control.

var HeaderCheckBox = document.getElementById(HCheckBox);



//Modifiy Counter;

if(CheckBox.checked && Counter < TotalChkBx)

Counter++;

else if(Counter > 0)

Counter--;



//Change state of the header CheckBox.

if(Counter < TotalChkBx)

HeaderCheckBox.checked = false;

else if(Counter == TotalChkBx)

HeaderCheckBox.checked = true;

}


</script>
<title>Affiliser Admin � Fake Member Marking</title>
<link rel="stylesheet" type="text/css" href="StyleSheet.css" />
</head>
<body>



<div class="FormHeadingDiv"> Affiliser Admin � Fake Member Marking</div>
<div style="text-align:center;background-color:#E7DBD5">
<form id="form1" runat="server">
<div class="xLabelUserInfo"><asp:Label ID="Label2" runat="server" Text=""></asp:Label></div>



<div >
<div>
&nbsp;
<asp:Label ID="lblcaption" runat="server" BackColor="Olive" Font-Bold="True" Font-Names="Arial" Font-Size="Medium" ForeColor="PaleGreen" Width="123px" ></asp:Label><br />
&nbsp;</div>
<asp:ScriptManager ID="ScriptManager1" runat="server">

</asp:ScriptManager>
<asp:UpdateProgress ID="UpdateProgress1" runat="server" AssociatedUpdatePanelID="UpdatePanel1">
<progresstemplate>
<strong>Please wait..Processing....</strong><br />
<img src="http://affiliser.com//images/site/ajax-loader.gif" />
</progresstemplate>
</asp:UpdateProgress>

<asp:UpdatePanel ID="UpdatePanel1" runat="server">
<contenttemplate>
<div> <asp:Label ID="Label5" runat="server" ></asp:Label><br />
&nbsp;</div>
<div>

<asp:GridView ID="GridView1" CssClass="xGridViewPro" runat="server" AutoGenerateColumns="False" BackColor="White" BorderColor="#010101" BorderStyle="Groove" BorderWidth="1px" CellPadding="4" AllowPaging="True" ShowFooter="True" OnRowCommand="GridView1_RowCommand" OnRowCreated="GridView1_RowCreated" OnRowDataBound="GridView1_RowDataBound" OnPageIndexChanging="GridView1_PageIndexChanging">
<Columns>
<asp:BoundField DataField="userid" HeaderText="userid" Visible="true"/>
<asp:TemplateField HeaderText="userid" Visible="false">
<ItemTemplate>
<asp:Label ID="lbluserid" runat="server" Text='<%# Bind("userid") %>' ></asp:Label>
</ItemTemplate>

</asp:TemplateField>


<asp:TemplateField HeaderText="username">

<ItemTemplate>
<asp:Label ID="lblusername" runat="server" Text='<%# Bind("username") %>'></asp:Label>
</ItemTemplate>


</asp:TemplateField>

<asp:TemplateField HeaderText="firstname">

<ItemTemplate>
<asp:Label ID="lblfirstname" runat="server" Text='<%# Bind("firstname") %>'></asp:Label>
</ItemTemplate>


</asp:TemplateField>

<asp:TemplateField HeaderText="lastname">
<ItemTemplate>
<asp:Label ID="lblastname" runat="server" Text='<%# Bind("email") %>' ></asp:Label>
</ItemTemplate>

</asp:TemplateField>
<asp:TemplateField HeaderText="Photo">
<ItemTemplate>
<asp:HyperLink ID="Hyperphotourl" runat="server" ImageUrl='<%#Bind("photourl")%>' NavigateUrl='<%#Bind("photourl")%>'>Photo</asp:HyperLink>
</ItemTemplate>

</asp:TemplateField>



<asp:TemplateField ControlStyle-BackColor="AliceBlue" FooterStyle-BackColor="graytext" >

<HeaderTemplate>

<asp:CheckBox ID="chkAll" onclick="javascript:SelectAllCheckboxesSpecific(this);" runat="server" Text="Select" />

</HeaderTemplate>

<ItemTemplate>

<asp:CheckBox onclick="javascript:HighlightRow(this);" ID="chkBxSelect" runat="server" />

</ItemTemplate>

<FooterTemplate>
<asp:Button ID="BtnSET" runat="server" Text="SET" CommandName="SET" Height="33px" Width="99px" />
</FooterTemplate>


</asp:TemplateField>

<asp:TemplateField HeaderText="IsFake" Visible="false">
<ItemTemplate>
<asp:Label ID="lblIsFake" runat="server" Text='<%# Bind("IsFake") %>'></asp:Label>
</ItemTemplate>

</asp:TemplateField>

</Columns>


<FooterStyle BackColor="White" ForeColor="#330099" />
<RowStyle BackColor="White" ForeColor="#330099" />
<HeaderStyle BackColor="#F06300" Font-Bold="True" ForeColor="#FFFFCC" />
<PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
<SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
<AlternatingRowStyle BackColor="White" ForeColor="#284775" />



</asp:GridView>
</div>
</contenttemplate>
</asp:UpdatePanel>
<br />
<asp:Button ID="btnSubmit" runat="server" Height="35px" OnClick="btnSubmit_Click" Text="Submit"
Width="99px" /><br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />


</div>




</form>
</div>







</body>
</html>

Some tips image url

bnrimg.InnerHtml = "<a href=" + ds.Tables[0].Rows[0][1].ToString() + " target=_blank><img width=468 height=60 border=0 src=" + ds.Tables[0].Rows[0].ItemArray[0].ToString() + " /></a>";

//Thumbnail

System.Web.UI.HtmlControls.HtmlGenericControl dynDiv = (System.Web.UI.HtmlControls.HtmlGenericControl)Parent.Page.FindControl("profImg");
dynDiv.InnerHtml = "";

System.Web.UI.WebControls.Label LabelParentfullname = (System.Web.UI.WebControls.Label)Parent.Page.FindControl("lblfullname");
LabelParentfullname.Text = fullNameN;

File Upload in Asp.net

private string IMAGEurl()
{
string saveLocation = "";
string imageurl = "/images/thumbnail/UnknownSuper.gif";
try
{
string imageName = File1.PostedFile.FileName.Substring(File1.PostedFile.FileName.LastIndexOf("\\") + 1);
string extention = File1.PostedFile.FileName.Substring(File1.PostedFile.FileName.LastIndexOf("."));

saveLocation = string.Empty;
saveLocation = Server.MapPath("/images/user");

imageName = txtUsername.Text.ToString() + extention;

saveLocation = saveLocation + "/" + imageName;
// jerry 01-07-2009
Bitmap photo = objvalid.ResizeImage(File1.PostedFile.InputStream, 300, 300);// width height
photo.Save(saveLocation);
//
// File1.PostedFile.SaveAs(saveLocation);

saveLocation = string.Empty;
saveLocation = Server.MapPath("/images/thumbnail");
saveLocation = saveLocation + "/" + imageName;
Bitmap photoFile = objvalid.ResizeImage(File1.PostedFile.InputStream, 64, 64);// width height
photoFile.Save(saveLocation);
imageurl = "/images/thumbnail/" + imageName;
}
catch
{ }
return imageurl;
}

DataSet Creation Dynamically

DataSet Ds = new DataSet();
DataTable Dt = new DataTable();
Ds.Tables.Add(Dt);
Dt.Columns.Add("F_Name");
Dt.Columns.Add("S_Name");
DataRow Dr = Dt.NewRow();
Dt.Rows.Add(Dr);
Dr["F_Name"]="satheesh";
Dr["S_Name"]="mnair";

DataGrid--Sample Code

//THE HTML TAGS OF A DATAGRID


<div>
<asp:GridView ID="GridView1" CssClass="xGridViewPro" runat="server" AutoGenerateColumns="False" CellPadding="4" ForeColor="#333333" GridLines="None" Width="300px" OnRowEditing="GridView1_RowEditing" OnRowCancelingEdit="GridView1_RowCancelingEdit" OnRowUpdating="GridView1_RowUpdating" AllowPaging="True" ShowFooter="True" OnRowCommand="GridView1_RowCommand" OnPageIndexChanging="GridView1_PageIndexChanging" >
<Columns>

<asp:TemplateField HeaderText="Name">
<ItemTemplate>
<asp:Label ID="lblname" runat="server" Text='<%# Bind("name") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtnameEdit" runat="server" Text='<%# Bind("name") %>'></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtfootName" runat="server"></asp:TextBox>
</FooterTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText="Value">

<ItemTemplate>
<asp:Label ID="lblvalue" runat="server" Text='<%# Bind("value") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtValueEdit" runat="server" Text='<%# Bind("value") %>'></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtfootValue" runat="server"></asp:TextBox>
</FooterTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText="Comment">

<ItemTemplate>
<asp:Label ID="lblcomment" runat="server" Text='<%# Bind("comment") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtcommentEdit" runat="server" Text='<%# Bind("comment") %>'> </asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtfootComment" runat="server"></asp:TextBox>

</FooterTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText="id">
<ItemTemplate>
<asp:Label ID="lblid" runat="server" Text='<%# Bind("id") %>' ></asp:Label>
</ItemTemplate>
<FooterTemplate>
<asp:Button ID="BtnADD" runat="server" Text="Add" CommandName="addrow" />
</FooterTemplate>
</asp:TemplateField>
<asp:ButtonField CommandName="rowdelete" Text="Delete" />
<asp:CommandField ShowEditButton="True" />

</Columns>

<RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
<FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
<SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
<HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<EditRowStyle BackColor="#999999" />
<AlternatingRowStyle BackColor="White" ForeColor="#284775" />
</asp:GridView>
</div>


*******************************************************************************

IN CODE BEHIND-->CODE SAMPLE



protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
{
Label5.Visible = false;
GridView1.EditIndex = e.NewEditIndex;
loadGrid();

}
protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
try
{
int index = GridView1.EditIndex;
int a = e.RowIndex;
GridViewRow row = GridView1.Rows[index];


Label Codelblid = (Label)GridView1.Rows[e.RowIndex].FindControl("lblid");
TextBox CodetxtnameEdit = (TextBox)GridView1.Rows[e.RowIndex].FindControl("txtnameEdit");
TextBox CodetxtValueEdit = (TextBox)GridView1.Rows[e.RowIndex].FindControl("txtValueEdit");
TextBox CodetxtcommentEdit = (TextBox)GridView1.Rows[e.RowIndex].FindControl("txtcommentEdit");

string id = Codelblid.Text;
string name = CodetxtnameEdit.Text;
string value = CodetxtValueEdit.Text;
string comment = CodetxtcommentEdit.Text;

Label5.Visible = true;
if (UpdateTheDetails(id, name, value, comment))
{
try
{
GlobalValues objGlob = GlobalValues.SET();
}
catch
{
}
GridView1.EditIndex = -1;
loadGrid();
Label5.Text = "Global value Updated Successfully";


}
else
{
Label5.Text = "Sorry..Error ocuured..Global values not updated..";
Label5.BackColor = Color.Red;
}
}
catch { }

}
protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
Label5.Visible = false;
GridView1.EditIndex = -1;
loadGrid();
}

protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
{
try
{
Label5.Visible = true;

if (e.CommandName == "addrow")
{

// int index = Convert.ToInt32(e.CommandArgument);

TextBox CodetxtnameEdit = (TextBox)GridView1.FooterRow.FindControl("txtfootName");
TextBox CodetxtValueEdit = (TextBox)GridView1.FooterRow.FindControl("txtfootValue");
TextBox CodetxtcommentEdit = (TextBox)GridView1.FooterRow.FindControl("txtfootComment");
string name = CodetxtnameEdit.Text;
string value = CodetxtValueEdit.Text;
string comment = CodetxtcommentEdit.Text;
string validSTR=Validate(name, value, comment);
if (validSTR == "true")
{
if (INSERTTheDetails(name, value, comment))
{
loadGrid();

}
}
else
{

Label5.BackColor = Color.Red;

}

}
else if (e.CommandName == "rowdelete")
{
int index = Convert.ToInt32(e.CommandArgument);

Label Codelblid = (Label)GridView1.Rows[index].FindControl("lblid");
string id = Codelblid.Text;
if (DELETETheDetails(id))
{
loadGrid();

}

}
}
catch { }
}
protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
Label5.Visible = false;
GridView1.PageIndex = e.NewPageIndex;
loadGrid();
}

SQL Server Constraints Alexander Chigrik

/*1)Tablename-C_ActiorType
fields--ActiorID--->Pk
--Name --->UK
2)Tablename-C_CreditsMatrix
fields--ActorID---->Fk of ActiorID in C_ActiorType
*/

/* settng the primary key constraint...Unique and not allow null*/
ALTER TABLE C_ActiorType
ADD CONSTRAINT pk_ActiorID PRIMARY KEY (ActiorID)
GO

/*setting the Unique key constraint.....unique&can allow only one null*/
ALTER TABLE C_ActiorType ADD CONSTRAINT IX_Name UNIQUE(Name)
GO

/*drop the constraint*/
ALTER TABLE C_ActiorType drop CONSTRAINT IX_ActorID
GO
/*creating the foriegn key for another primary key*/
ALTER TABLE C_CreditsMatrix
ADD CONSTRAINT fk_ActorID
FOREIGN KEY (ActorID)
REFERENCES C_ActiorType (ActiorID) ON DELETE CASCADE
GO

//Please Refer this links

http://blog.sqlauthority.com/2007/02/05/sql-server-primary-key-constraints-and-unique-key-constraints/

http://mssqlserver.wordpress.com/2006/11/22/difference-between-unique-constraint-and-primary-key/

http://www.mssqlcity.com/Articles/General/using_constraints.htm

State Manaement-Good Article

Please Refer this link

http://www.dotnetjohn.com/articles.aspx?articleid=32

Cache-Asp.net simple example with concept

Please Refer this urls:

http://www.codeproject.com/KB/web-cache/cachingaspnet.aspx
http://aspalliance.com/795_Caching_in_ASPNET

Impersonation in ASP.NET

Please redfer the urls:

http://www.4guysfromrolla.com/articles/031204-1.aspx
http://www.dotnet-guide.com/impersonation.html

Connection Class and WebConfig(Sample using for a application)

//***********a)CONNECTION CLASS(in C#)***********
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.SqlClient;


namespace Connection
{

public class ClsConnection
{


private SqlConnection conSQL;
// private string conString = "server=SYS9;uid=root; password=integral; database=dbsuperlistexplode;";
// private string conString = ConfigurationSettings.AppSettings["constringLogin"].ToString();
private string conString = ConfigurationManager.ConnectionStrings["conStrSuperListExplode"].ToString();


//private MySqlConnection consql=new MySqlConnection(ConfigurationSettings.AppSettings(conStrTutorinn);



//-----------------------------------------------------------------------------------
string _SuccessMsg;
public string SuccessMsg //success message - cannot always be returned, so we have a read only access method..
{
get { return _SuccessMsg; }
set { _SuccessMsg = value; }
}
//-----------------------------------------------------------------------------------------
public ClsConnection()//Constructor
{

//conSQL = new MySqlConnection(ConnectionStringSettings["constringLogin"]);
conSQL = new SqlConnection(conString);
}

public string SqlExecuteQuery(string str)
{
try
{
conSQL.Open();
SqlCommand cmd = new SqlCommand(str, conSQL);
cmd.ExecuteNonQuery();
conSQL.Close();
SuccessMsg = "Success";
return SuccessMsg;
}
catch (Exception e)
{
conSQL.Close();
SuccessMsg = e.Message;
return SuccessMsg;
}

}
//-----------------------------------------------------------------------------------------
public bool SQLReturnBool(string sqlstr)
{

bool retval;
try
{
conSQL.Close();
conSQL.Open();
SqlCommand cmd = new SqlCommand(sqlstr, conSQL);
int retvalbool = int.Parse(cmd.ExecuteScalar().ToString());
if (retvalbool == 1) retval = true;
else retval = false;
conSQL.Close();
}
catch (Exception ex)
{
conSQL.Close();
SuccessMsg = ex.Message;
retval = false;
}
return retval;
}

//-----------------------------------------------------------------------------------------
public int SQLReturnInteger(string sqlstr)
{

int retval;
try
{
conSQL.Close();
conSQL.Open();
SqlCommand cmd = new SqlCommand(sqlstr, conSQL);
retval = int.Parse(cmd.ExecuteScalar().ToString());
conSQL.Close();
}
catch (Exception ex)
{
conSQL.Close();
SuccessMsg = ex.Message;
retval = 0;
}
return retval;
}

//-----------------------------------------------------------------------------------------
public float SQLReturnFloat(string sqlstr)
{
float retval;
try
{
conSQL.Close();
conSQL.Open();
SqlCommand cmd = new SqlCommand(sqlstr, conSQL);
retval = float.Parse(cmd.ExecuteScalar().ToString());
conSQL.Close();
}
catch (Exception ex)
{
conSQL.Close();
SuccessMsg = ex.Message;
retval = 0;
}
return retval;
}
//-----------------------------------------------------------------------------------------
public string SQLReturnString(string sqlstr)
{
string retval;
try
{
conSQL.Close();
conSQL.Open();
SqlCommand cmd = new SqlCommand(sqlstr, conSQL);
retval = cmd.ExecuteScalar().ToString();
SuccessMsg = "Success";
conSQL.Close();
}
catch (Exception ex)
{
conSQL.Close();
SuccessMsg = ex.Message;
retval = "";
}
return retval;
}

//-----------------------------------------------------------------------------------------



public DataSet SelectDataset(string sqlstr, string sqltbl)
{
DataSet dssql = new DataSet();
try
{
if (conSQL.State == ConnectionState.Open)
{
conSQL.Close();
conSQL.Open();
}
else
{
conSQL.Open();
}

SqlDataAdapter dasql = new SqlDataAdapter(sqlstr, conSQL);

dasql.Fill(dssql, sqltbl);

}
catch (Exception ex)
{
SuccessMsg = ex.Message;
conSQL.Close();
}
conSQL.Close();
return dssql;
}

public byte[] SQLReturnByte(string sqlstr)
{
byte[] Rblob = null;
// try
// {
conSQL.Close();
conSQL.Open();
SqlCommand cmd = new SqlCommand(sqlstr, conSQL);
SqlDataReader MyReader = cmd.ExecuteReader();
if (MyReader.Read())
{
//byte[] m_MyImage = (byte[])MyReader["pimage"];
Rblob = (byte[])MyReader[0];
//Response.BinaryWrite(retval);

}
//retval[]= Convert.ToBase64CharArray(cmd.ExecuteScalar().ToString());
SuccessMsg = "Success";
conSQL.Close();

// }
// catch (Exception ex)
// {
// conSQL.Close();
// SuccessMsg=ex.Message;
// //retval= "";
//
// }
return Rblob;

}


}
}

//-------------------------------------------------------------------------------

// **********b)WEBCONFIG****************


<?xml version="1.0"?>
<!--
Note: As an alternative to hand editing this file you can use the
web admin tool to configure settings for your application. Use
the Website->Asp.Net Configuration option in Visual Studio.
A full list of settings and comments can be found in
machine.config.comments usually located in
\Windows\Microsoft.Net\Framework\v2.x\Config
-->
<configuration>
<configSections>
<sectionGroup name="system.web.extensions" type="System.Web.Configuration.SystemWebExtensionsSectionGroup, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35">
<sectionGroup name="scripting" type="System.Web.Configuration.ScriptingSectionGroup, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35">
<section name="scriptResourceHandler" type="System.Web.Configuration.ScriptingScriptResourceHandlerSection, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" requirePermission="false" allowDefinition="MachineToApplication"/>
<sectionGroup name="webServices" type="System.Web.Configuration.ScriptingWebServicesSectionGroup, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35">
<section name="jsonSerialization" type="System.Web.Configuration.ScriptingJsonSerializationSection, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" requirePermission="false" allowDefinition="Everywhere"/>
<section name="profileService" type="System.Web.Configuration.ScriptingProfileServiceSection, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" requirePermission="false" allowDefinition="MachineToApplication"/>
<section name="authenticationService" type="System.Web.Configuration.ScriptingAuthenticationServiceSection, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" requirePermission="false" allowDefinition="MachineToApplication"/>
</sectionGroup>
</sectionGroup>
</sectionGroup>
</configSections>

<appSettings>
<add key="ChooseMode" value="TEST"/>
<!--
Set ChooseMode as TEST for test server.
Set ChooseMode as LIVE for live server.
-->
<add key="mailFrmPrimestreams.SendMail" value="http://primestreams.net/SendMail.asmx"/>
</appSettings>
<connectionStrings>
<add name="conStrSuperListExplode" connectionString="Data Source=SYS26\SQLEXPRESS;Initial Catalog=dbAffiliser;User ID=sa;Password=integraloffice"/>
<add name="dbAffiliserConnectionString" connectionString="Data Source=SYS26\SQLEXPRESS;Initial Catalog=dbAffiliser;User ID=sa;Password=integraloffice" providerName="System.Data.SqlClient"/>
</connectionStrings>
<system.web>
<pages>
<controls>
<add tagPrefix="asp" namespace="System.Web.UI" assembly="System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"/>
</controls>
</pages>
<!--
Set compilation debug="true" to insert debugging
symbols into the compiled page. Because this
affects performance, set this value to true only
during development.
-->
<sessionState mode="InProc" cookieless="false" timeout="30"></sessionState>
<identity impersonate="true" />
<httpHandlers>
<add verb="GET" path="FtbWebResource.axd" type="FreeTextBoxControls.AssemblyResourceHandler, FreeTextBox" />
<add verb="GET" path="CaptchaImage.aspx" type="WebControlCaptcha.CaptchaImageHandler, WebControlCaptcha" />
<remove verb="*" path="*.asmx"/>
<add verb="*" path="*.asmx" validate="false" type="System.Web.Script.Services.ScriptHandlerFactory, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"/>
<add verb="*" path="*_AppService.axd" validate="false" type="System.Web.Script.Services.ScriptHandlerFactory, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"/>
<add verb="GET,HEAD" path="ScriptResource.axd" type="System.Web.Handlers.ScriptResourceHandler, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" validate="false"/>

</httpHandlers>
<!--
Set compilation debug="true" to insert debugging
symbols into the compiled page. Because this
affects performance, set this value to true only
during development.
-->
<compilation debug="true">
<assemblies>
<add assembly="System.Design, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/>
<!--jerry-->
<add assembly="System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"/>
<add assembly="System.Management, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/>

</assemblies>
</compilation>
<!--
The <authentication> section enables configuration
of the security authentication mode used by
ASP.NET to identify an incoming user.
-->
<authentication mode="Windows"/>
<!--
The <customErrors> section enables configuration
of what to do if/when an unhandled error occurs
during the execution of a request. Specifically,
it enables developers to configure html error pages
to be displayed in place of a error stack trace.

<customErrors mode="RemoteOnly" defaultRedirect="GenericErrorPage.htm">
<error statusCode="403" redirect="NoAccess.htm" />
<error statusCode="404" redirect="FileNotFound.htm" />
</customErrors>
-->

<customErrors mode="Off" />
<httpModules>
<add name="ScriptModule" type="System.Web.Handlers.ScriptModule, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"/>
</httpModules>

</system.web>
<system.web.extensions>
<scripting>
<webServices>
<!-- Uncomment this line to customize maxJsonLength and add a custom converter -->
<!--
<jsonSerialization maxJsonLength="500">
<converters>
<add name="ConvertMe" type="Acme.SubAcme.ConvertMeTypeConverter"/>
</converters>
</jsonSerialization>
-->
<!-- Uncomment this line to enable the authentication service. Include requireSSL="true" if appropriate. -->
<!--
<authenticationService enabled="true" requireSSL = "true|false"/>
-->
<!-- Uncomment these lines to enable the profile service. To allow profile properties to be retrieved
and modified in ASP.NET AJAX applications, you need to add each property name to the readAccessProperties and
writeAccessProperties attributes. -->
<!--
<profileService enabled="true"
readAccessProperties="propertyname1,propertyname2"
writeAccessProperties="propertyname1,propertyname2" />
-->
</webServices>
<!--
<scriptResourceHandler enableCompression="true" enableCaching="true" />
-->
</scripting>



</system.web.extensions>
<system.webServer>
<validation validateIntegratedModeConfiguration="false"/>
<modules>
<add name="ScriptModule" preCondition="integratedMode" type="System.Web.Handlers.ScriptModule, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"/>
</modules>
<handlers>
<remove name="WebServiceHandlerFactory-Integrated"/>
<add name="ScriptHandlerFactory" verb="*" path="*.asmx" preCondition="integratedMode" type="System.Web.Script.Services.ScriptHandlerFactory, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"/>
<add name="ScriptHandlerFactoryAppServices" verb="*" path="*_AppService.axd" preCondition="integratedMode" type="System.Web.Script.Services.ScriptHandlerFactory, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"/>
<add name="ScriptResource" preCondition="integratedMode" verb="GET,HEAD" path="ScriptResource.axd" type="System.Web.Handlers.ScriptResourceHandler, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"/>
</handlers>
</system.webServer>
</configuration>

SQL-The Language Specifications:

The Language Specifications::

http://treasure4developer.wordpress.com/2009/07/12/what-are-the-difference-between-ddl-dml-and-dcl-commands/
http://www.orafaq.com/faq/what_are_the_difference_between_ddl_dml_and_dcl_commands

Roll Back And TRY - CATCH to Rollback a Transaction - SQL Server

Defenition:-

a rollback is an operation which returns the database to some previous state. Rollbacks are important for database integrity,
because they mean that the database can be restored to a clean copy even after erroneous operations are performed.


. A transaction begins with the execution of a SQL-Data statement when there is no current transaction. All subsequent SQL-Data statements until COMMIT or ROLLBACK become part of the transaction. Execution of a COMMIT Statement or ROLLBACK Statement completes the current transaction.

COMMIT Statement
The COMMIT Statement terminates the current transaction and makes all changes under the transaction persistent. It commits the changes to the database. The COMMIT statement has the following general format:

COMMIT [WORK]

WORK is an optional keyword that does not change the semantics of COMMIT.

ROLLBACK Statement
The ROLLBACK Statement terminates the current transaction and rescinds all changes made under the transaction. It rolls back the changes to the database. The ROLLBACK statement has the following general format:

ROLLBACK [WORK]

The major link for refferring these is:

http://www.eggheadcafe.com/tutorials/aspnet/6a8ef7d5-840e-4629-b53a-1a40e7db601f/using-try--catch-to-roll.aspx

Click to View in Detail

Triggers: Defenition

It is a piece of SQL that is activated when a certain event happens or occurs.

The Major Links are,

http://www.datasprings.com/Resources/ArticlesInformation/CreatingEmailTriggersinSQLServer2005.aspx
http://www.sqlteam.com/article/using-ddl-triggers-in-sql-server-2005-to-capture-schema-changes
http://msdn.microsoft.com/en-us/library/ms189799.aspx

Triggers: Sample DDL trigger for Database

CREATE trigger [SQLInfoNewTrigger]
on database
for create_procedure, alter_procedure, drop_procedure, --events
create_table, alter_table, drop_table,
create_function, alter_function, drop_function
as
set nocount on
declare @data xml
set @data = EVENTDATA()

insert into databasename.dbo.SQLINFONEW(DatabaseName,EventType,ObjectName,ObjectType,LoginName,TSQLCommand,date)
values
(@data.value('(/EVENT_INSTANCE/DatabaseName)[1]','varchar(50)'),
@data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)'),
@data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(50)'),
@data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(50)'),
@data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(50)'),
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)'),

getdate()
)

Triggers: Sample DML trigger

CREATE trigger [dbo].[Triggername]
ON [dbo].[Table_exist]
FOR update
AS

declare @userid int
select @userid=userid from deleted d
insert into Tble (userid) values(@userid)