August 2009

Send Email

You must have to use Gmail ID as From Email ID.

USE: using System.Net.Mail; // Namespace

OnSendMail Button Click Event :

try
{
SmtpClient obj = new SmtpClient(“smtp.gmail.com”);
obj.Credentials = new System.Net.NetworkCredential(“your@gmail.com”, “password”);
obj.Port = 587;
obj.Host = “smtp.gmail.com”;
obj.EnableSsl = true;

MailMessage message = new MailMessage(“your@gmail.com”,”to@yahoo.com”);
message.Subject = txtSUB.Text; // subject textbox’s text
message.Body = txtMSG.Text; // Message textbox’s text
obj.Send(message);
Response.Write(“Mail Successfully send”);
}
catch (SmtpException ex)
{
Response.Write(ex.Message.ToString());
}

File Extension Javascript Validation

<script language=”javascript” type=”text/javascript”>
function chkFile()
{
var fextension = document.getElementById(“<%=FileUpload1.ClientID %>”).value;

var exten = fextension.split(“.”);
var len=exten.length;
//alert(exten.length);

if(exten[len-1]==”doc” || exten[len-1]==”docx”)
{
alert(“File upload successfully”);
return true;
}
else
{
alert(“Please select doc file”);
return false;
}
}
</script>

Create Folder/Directory Check Existing

Create Folder/Directory and Check Existing or not

Here Session[“user”] is a name which will be created…. ‘upload’ is a floder/Director that already created.

if(Directory.Exists(Server.MapPath(“upload”) + “/” + Session[“user”].ToString()))
{
}
else
{
System.IO.Directory.CreateDirectory(Server.MapPath(“upload”) + “/” + Session[“user”].ToString());
}

DataTable : (Use For Make Shopping Cart)

First Time Page Load Gridview Shown as below

first_time_gridview

.aspx Page Coding

<div>
<asp:GridView ID=”GridView1″ runat=”server” AutoGenerateColumns=”False” ShowFooter=”True” >
<Columns>
<asp:TemplateField HeaderText=”Rollno”>
<ItemTemplate>
<asp:Label ID=”lblRno” runat=”Server” Text=””></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText=”Name”>
<ItemTemplate>
<asp:Label ID=”lblName” runat=”server” Text='<%# Eval(“name”) %>’></asp:Label>
</ItemTemplate>
<FooterTemplate>
<asp:TextBox ID=”txtName” runat=”server” Text=””></asp:TextBox>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText=”City”>
<ItemTemplate>
<asp:Label ID=”lblCity” runat=”server” Text='<%# Eval(“city”) %>’></asp:Label>
</ItemTemplate>
<FooterTemplate>
<asp:TextBox ID=”txtCity” runat=”server” Text=””></asp:TextBox>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField>
<HeaderTemplate>
<asp:Button ID=”btnAdd” runat=”server” Text=”ADD” OnClick=”btnAdd_Click” />
</HeaderTemplate>
</asp:TemplateField>
</Columns>
<EmptyDataTemplate>
No Records
</EmptyDataTemplate>
</asp:GridView>
<asp:Button ID=”Button1″ runat=”server” OnClick=”Button1_Click” Style=”z-index: 100;
left: 16px; position: absolute; top: 192px” Text=”Insert Total Records from Grdiview” />
</div>

.aspx.cs Coding

DataTable dt = new DataTable();
DataColumn dc;
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
Session[“cart”] = null;
MakeCart();
BindGrid();
}
}

//Define columns of DataTable
public void MakeCart()
{
dc = new DataColumn();
dc.ColumnName = “rollno”;
dc.DataType = typeof(int);
dt.Columns.Add(dc);

dc = new DataColumn();
dc.ColumnName = “name”;
dc.DataType = typeof(string);
dt.Columns.Add(dc);

dc = new DataColumn();
dc.ColumnName = “city”;
dc.DataType = typeof(string);
dt.Columns.Add(dc);

}

//Bind Records into Gridview
public void BindGrid()
{
if (dt.Rows.Count == 0)
{
DataRow dr = dt.NewRow();

dr[“name”] = “No Record”;
dr[“city”] = “No Record”;
dt.Rows.Add(dr);
}
GridView1.DataSource = dt;
GridView1.DataBind();
}
protected void btnAdd_Click(object sender, EventArgs e)
{
if (Session[“cart”] == null)
{
MakeCart();
DataRow dr = dt.NewRow();
dr[“name”] = ((TextBox)GridView1.FooterRow.FindControl(“txtName”)).Text;
dr[“city”] = ((TextBox)GridView1.FooterRow.FindControl(“txtCity”)).Text;
dt.Rows.Add(dr);
Session[“cart”] = dt;
BindGrid();
}
else
{
dt = (DataTable)Session[“cart”];
DataRow dr = dt.NewRow();
dr[“name”] = ((TextBox)GridView1.FooterRow.FindControl(“txtName”)).Text;
dr[“city”] = ((TextBox)GridView1.FooterRow.FindControl(“txtCity”)).Text;
dt.Rows.Add(dr);
Session[“cart”] = dt;
BindGrid();
}
}

//Insert All Record from Gridview to DataSource  Sqlserver table.
protected void Button1_Click(object sender, EventArgs e)
{
dt = (DataTable)Session[“cart”];
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings[“TESTConnectionString”].ToString());
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = “insTy”;
for (int i=0; i <dt.Rows.Count; i++)
{
cmd.Parameters.AddWithValue(“@nm”, dt.Rows[i][“name”]);
cmd.Parameters.AddWithValue(“@ct”, dt.Rows[i][“city”]);
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
}

Checkbox Gridview Javascript Multiple Delete Records

–JavaScript–

<script language=”javascript” type=”text/javascript”>

//user for select all checkbox
function SelectAllCheckboxes(spanChk){

// Added as ASPX uses SPAN for checkbox

var oItem = spanChk.children;
var theBox= (spanChk.type==”checkbox”) ?
spanChk : spanChk.children.item[0];
xState=theBox.checked;
elm=theBox.form.elements;

for(i=0;i<elm.length;i++)
if(elm[i].type==”checkbox” &&
elm[i].id!=theBox.id)
{
//elm[i].click();

if(elm[i].checked!=xState)
elm[i].click();
//elm[i].checked=xState;

}
}

//User for check any checkbox is selected or not and delete confirm message
function checkDelete(spanChk)
{
var chks=document.getElementsByTagName(‘input’);
var hasChecked = false;
for (var i = 0; i < chks.length; i++)
{
if (chks[i].checked)
{
hasChecked = true;
break;
}
}
if (hasChecked == false)
{
alert(“Please select at least one checkbox..!”);
return false;
}
else
{
if(confirm(“r u sure delete records?”))
{
return true;
}
else
{
return false;
}
}
}
</script>

–GridView .aspx page–

<asp:GridView ID=”GridView1″ runat=”server” AutoGenerateColumns=”False” OnRowDeleting=”GridView1_RowDeleting” ShowFooter=”True”>
<Columns>
<asp:TemplateField HeaderText=”Select”>
<HeaderTemplate>
<input id=”chkAll” type=”checkbox” runat=”Server” onclick=”javascript:SelectAllCheckboxes(this);” />
</HeaderTemplate>
<ItemTemplate>
<asp:CheckBox ID=”checkbox1″ runat=”Server” />
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText=”Rollno”>
<ItemTemplate>
<asp:Label ID=”lblRollno” runat=”server” Text='<%# Eval(“rollno”) %>’></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText=”Name”>
<ItemTemplate>
<asp:Label ID=”lblName” runat=”server” Text='<%# Eval(“name”) %>’></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText=”City”>
<ItemTemplate>
<asp:Label ID=”lblCity” runat=”server” Text='<%# Eval(“city”) %>’></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField>
<ItemTemplate>
<asp:LinkButton ID=”lbnDelete” CommandName=”Delete” runat=”server” Text=”Delete”
OnClientClick=”javascript:return checkDelete(this);”></asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField>
<FooterTemplate>
<asp:Button ID=”btnSubmit” runat=”server” Text=”Delete Selected” OnClick=”btnSubmit_Click”
OnClientClick=”javascript:return checkDelete(this);”  />
</FooterTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>

—aspx.cs page—

Single Record Delete Coding From Gridview

protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
Label rno = (Label)GridView1.Rows[e.RowIndex].FindControl(“lblRollno”);
int no = Convert.ToInt32(rno.Text);

string str = “delete from tybca where rollno=” + no;
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings[“TESTConnectionString”].ToString());
conn.Open();
SqlCommand cmd = new SqlCommand(str, conn);
cmd.ExecuteNonQuery();
BindGrid();
}

Multiple Selected Checkbox Record Deleteing Coding

protected void btnSubmit_Click(object sender, EventArgs e)
{
ArrayList chkList = new ArrayList();
Label rollno;
int rno;

//Find Multiple checkbox was checked

for(int row=0; row<GridView1.Rows.Count;row++)
{
CheckBox c = (CheckBox)GridView1.Rows[row].Cells[0].FindControl(“CheckBox1”);
if (c.Checked != null)
{
if (c.Checked)
{
rollno = (Label) GridView1.Rows[row].Cells[1].FindControl(“lblRollno”);
rno = Convert.ToInt32(rollno.Text);
chkList.Add(rno);
}
}
}

//Delete Multiple Records
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings[“TESTConnectionString”].ToString());
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
string strSql;
foreach (int i in chkList)
{
//Response.Write(i);
strSql = “Delete from tybca where rollno=” + i;
cmd.CommandType = CommandType.Text;
cmd.CommandText = strSql;
cmd.ExecuteNonQuery();
}
BindGrid(); //this function bind gridview with records from datasource
}

Joins in SQL Server

Inner Joins

It match records together based on one or more common fields, as do most JOINS but inner joins return only records where there are matches for whatever fields you have said are to be used for join.

Join Two Tables

Table_1 : Product_Master

product_master

Table_2 : Sales_Order_Detaisl

sales_order_details

Query:

select a.product_no,a.decscription from product_master a
inner join sales_order_details b on a.product_no = b.product_no

Multiple Inner Joins

select a.product_no,a.decscription,d.name from product_master a
inner join sales_order_details b on a.product_no = b.product_no
inner join sales_order c on b.s_order_no = c.s_order_no
inner join client_master d on c.client_no = d.client_no
where d.name=’Ivan Bayross’

Outer Joins

Syntax: Select * from left_table_name <LEFT or RIGHT>  OUTER  JOIN right_table_name on <condition>

Example

select a.product_no from product_master a
left outer join sales_order_details b on
a.product_no = b.product_no

It will display all records of left, it does not important to find matches with right table. And it does not display right table records which are not match found

CROSS Join

A cross join is differ from other joins in that there is no ON operator and that it joins every record on one side of the JOIN with every record on the other side of the JOIN.

Syntax : Select * from left_table_name cross join right_table_name

It will display possibility of matching records in both tables. Suppose LEFT table have 3 records and right table 5 then Cross join display 3*5=15 records, match possibilities.

Find Tables List Structure Count Duplicate Records

use <database_name>

Select DataBase using above query

select * from information_schema.tables

You can easily fine tables list using above syntax.

sp_help <table_name>

See particular table structure/Design.

select s_order_no, count(s_order_no) As ‘SalesOrder’ from sales_order_details group by s_order_no having (count(s_order_no)>1)

Find Duplicate Records In Table