DotNet: July 2012

Friday, 27 July 2012

Stored procedures DONOT increase performance(.NET and SQL training video)

Thursday, 26 July 2012

Editable Gridview with Textbox, CheckBox, Radio Button and DropDown List

using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

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

protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
showgrid();
}
}
public void showgrid()
{
using (SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["conn"].ConnectionString))
{
DataTable dt = new DataTable();
sqlcon.Open();
SqlDataAdapter sda = new SqlDataAdapter("select * from employee ", sqlcon);
sda.Fill(dt);
GridView1.DataSource = dt;
GridView1.DataBind();
}

}
protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
GridView1.EditIndex = -1;
showgrid();

}
protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
{

GridView1.EditIndex = e.NewEditIndex;
showgrid();
}
protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{

Label lb = (Label)GridView1.Rows[e.RowIndex].FindControl("Label6");
DropDownList ddl = (DropDownList)GridView1.Rows[e.RowIndex].FindControl("DropDownList1");
RadioButtonList rbl = (RadioButtonList)GridView1.Rows[e.RowIndex].FindControl("RadioButtonList1");
CheckBoxList chb = (CheckBoxList)GridView1.Rows[e.RowIndex].FindControl("CheckBoxList2");
TextBox tx1 = (TextBox)GridView1.Rows[e.RowIndex].FindControl("TextBox1");
TextBox tx2 = (TextBox)GridView1.Rows[e.RowIndex].FindControl("TextBox2");
TextBox tx3 = (TextBox)GridView1.Rows[e.RowIndex].FindControl("TextBox3");
using (SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["conn"].ConnectionString))
{
sqlcon.Open();
string sql = "update employee set emp_name='" + tx1.Text + "',emp_address='" + tx2.Text + "',salary='" +
tx3.Text + "',department='" + ddl.SelectedValue.ToString() + "',maritalstatus='" +
rbl.SelectedValue.ToString() + "',Active_status='" + chb.SelectedValue.ToString() + "' where emp_id='" +
lb.Text + "'";
using (SqlCommand cmd = new SqlCommand(sql))
{
cmd.CommandType = CommandType.Text;
cmd.Connection = sqlcon;
cmd.ExecuteNonQuery();
GridView1.EditIndex = -1;
showgrid();
}

}

}
public DataTable load_department()
{
using (SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["conn"].ConnectionString))
{
DataTable dt = new DataTable();
SqlDataAdapter sd = new SqlDataAdapter("select DeparmentName,DeparmentId from department", sqlcon);
sd.Fill(dt);
return dt;

}

}
protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
DataRowView drv = e.Row.DataItem as DataRowView;

if (e.Row.RowType == DataControlRowType.DataRow)
{
if ((e.Row.RowState & DataControlRowState.Edit) > 0)
{
DropDownList dp = (DropDownList)e.Row.FindControl("ddlOutlet");
DataTable dt = (DataTable)ViewState["OutletDrpDown"];
dp.DataSource = dt;
dp.DataTextField = "DeparmentName";
dp.DataValueField = " DeparmentId";
dp.DataBind();
}
RadioButtonList rbtnl = (RadioButtonList)e.Row.FindControl("RadioButtonList1");
rbtnl.SelectedValue = drv[5].ToString();
CheckBoxList chkb = (CheckBoxList)e.Row.FindControl("CheckBoxList2");
chkb.SelectedValue = drv[6].ToString();
}
}

}

}
}
}

Output Parameter in stored Procedure(MYSQL) using C# in asp.net

DROP PROCEDURE IF EXISTS SP_OUTLETCREATION;
CREATE PROCEDURE SP_OUTLETCREATION(
IN SP_OutletId smallint,
IN SP_OutletName varchar(20),
out SP_MSG varchar(50)
)
Begin
IF NOT EXISTS(select 1 from outletcreation where OutLetName=SP_OutletName) then

if SP_OutletId=0 then
INSERT INTO OUTLETCREATION (OutletName,CreatedDate,ModifiedDate) values(SP_OutletName,CURRENT_TIMESTAMP(),CURRENT_TIMESTAMP());
else
UPDATE OUTLETCREATION SET outletName=SP_OutletName,ModifiedDate=CURDATE() where OutletId=SP_OutletId;
end if;
else
set SP_MSG="OutletName is Already Exist!!!";

End if;
End;

C#:

protected void btnCreateOutlet_Click(object sender, EventArgs e)

{

try

{

using (MySqlConnection ObjCon = new MySqlConnection(new CommonClass().GetConnectionString()))

{

ObjCon.Open();

using (MySqlCommand ObjCmd = new MySqlCommand("SP_OUTLETCREATION", ObjCon))

{

ObjCmd.CommandType = CommandType.StoredProcedure;

ObjCmd.Parameters.Add("SP_OutletId", MySqlDbType.Int64).Value = 0;

ObjCmd.Parameters.Add("SP_OutletName", MySqlDbType.VarChar, 20).Value = txtOutletName.Text.Trim();

MySqlParameter parm2 = new MySqlParameter("SP_MSG", MySqlDbType.VarChar);

parm2.Size = 50;

parm2.Direction = ParameterDirection.Output;

ObjCmd.Parameters.Add(parm2);

ObjCmd.ExecuteNonQuery();

lblMsg.Text = ObjCmd.Parameters["SP_MSG"].Value.ToString();

}

Bind();

}

catch (Exception ex)

{

lblMsg.Text=ex.Message;

}

Monday, 23 July 2012

DropDownList(Bind) Inside The Gridview Using Templatefield in Asp.net

<asp:GridView ID="GVBeneficiary" runat="server" AutoGenerateColumns="False"
CellPadding="4" ForeColor="#333333"
onrowcancelingedit="GVBeneficiary_RowCancelingEdit"
onrowediting="GVBeneficiary_RowEditing"
onrowupdating="GVBeneficiary_RowUpdating"
onrowdatabound="GVBeneficiary_RowDataBound">
<AlternatingRowStyle BackColor="White" />
<Columns>
<asp:CommandField ShowEditButton="True" />
<asp:TemplateField HeaderText="ID">
<ItemTemplate>
<asp:Label runat="server" ID="lblID" Text='<%#Bind("id") %>' ></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="AccountNumber">
<ItemTemplate>
<asp:TextBox runat="server" ID="txtAccountNumber" Text='<%#Bind("AccountNo") %>' ></asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Name">
<ItemTemplate>
<asp:TextBox runat="server" ID="txtName" Text='<%#Bind("Name") %>' ></asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="PhoneNumber">
<ItemTemplate>
<asp:TextBox runat="server" ID="txtPhoneNumber" Text='<%#Bind("PhoneNumber") %>' ></asp:TextBox>
</ItemTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText="BankName">
<ItemTemplate>
<asp:DropDownList runat="server" ID="ddlBankNme">
<asp:ListItem Value="0">Select</asp:ListItem>
<asp:ListItem Value="1">State Bank of India</asp:ListItem>
<asp:ListItem Value="2">Indian Bank</asp:ListItem>
</asp:DropDownList>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="BranchNumber">
<ItemTemplate>
<asp:TextBox runat="server" ID="txtBranchNumber" Text='<%#Bind("BranchName") %>' ></asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField Visible="false" >
<ItemTemplate>
<asp:HiddenField runat="server" ID="txtBankName" Value='<%#Bind("BankName") %>' />

</ItemTemplate>
</asp:TemplateField>
</Columns>
<FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
<HeaderStyle BackColor="Red" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#FFCC66" ForeColor="#333333" HorizontalAlign="Center" />
<RowStyle BackColor="#FFFBD6" ForeColor="#333333" />
<SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" />
</asp:GridView>

C#:

protected void GVBeneficiary_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
HiddenField txthidden = ((HiddenField)e.Row.FindControl("txtBankName"));
DropDownList ddlhidden = ((DropDownList)e.Row.FindControl("ddlBankNme"));
ddlhidden.SelectedValue = txthidden.Value;

}
}

Example For Insert,Update in Stored Procedure in Mysql

try
{
string MemID = string.Empty;
using (MySqlConnection ObjCon = new MySqlConnection(new CommonClass().GetConnectionString()))
{

ObjCon.Open();
using (MySqlCommand ObjCmd = new MySqlCommand("SP_Beneficiary", ObjCon))
{
ObjCmd.CommandType = CommandType.StoredProcedure;
ObjCmd.Parameters.Add("SP_Id", MySqlDbType.Int64).Value = 0;//Pass Zero Here For Insert;
ObjCmd.Parameters.Add("SP_AccountNo", MySqlDbType.VarChar,15).Value = txtBeneAcc.Text.Trim();
ObjCmd.Parameters.Add("SP_Name", MySqlDbType.VarChar, 50).Value = txtBeneName.Text.Trim();
ObjCmd.Parameters.Add("SP_PhoneNumber", MySqlDbType.VarChar, 15).Value = txtBenePh.Text.Trim();
ObjCmd.Parameters.Add("SP_BankName", MySqlDbType.VarChar, 30).Value = ddlBeneBank.SelectedItem.Value;
ObjCmd.Parameters.Add("SP_BranchName", MySqlDbType.VarChar, 30).Value = txtBeneBranch.Text.Trim();
ObjCmd.Parameters.Add("SP_Cre_By", MySqlDbType.VarChar, 20).Value = "Raja";
ObjCmd.ExecuteNonQuery();

}

}
}

catch (Exception ex)
{

Response.Redirect(ex.Message);

}

DROP PROCEDURE IF EXISTS SP_Beneficiary;
CREATE PROCEDURE SP_Beneficiary(
IN SP_Id INT,
IN SP_AccountNo varchar(15) ,
IN SP_Name varchar(50),
IN SP_PhoneNumber varchar(15),
IN SP_BankName varchar(30),
IN SP_BranchName varchar(30),
IN SP_Cre_By varchar(20)
)
Begin
if SP_Id=0 then
INSERT INTO Beneficiary (AccountNo,Name,PhoneNumber,BankName,BranchName,Cre_Dt,Cre_By) VALUES (SP_AccountNo,SP_Name,SP_PhoneNumber,SP_BankName,SP_BranchName,CURDATE(),SP_Cre_By);
else
update Beneficiary set AccountNo=SP_AccountNo,Name=SP_Name,PhoneNumber=SP_PhoneNumber,BankName=SP_BankName,BranchName=SP_BranchName,Mod_Dt=CURDATE(),Cre_By=SP_Cre_By where id=SP_Id;
End if;
End;

Sunday, 22 July 2012

To Open Pop Window and Pass Value Using C# in .net

String javaScript = "window.open('Message.aspx?ID=" + lblRefNum.Text + "', null, 'height=150,width=700,status=yes,toolbar=no,menubar=no,location=center, scrollbars=yes');";

ScriptManager.RegisterStartupScript(this, typeof(string), "OPEN_WINDOW", javaScript, true);

Friday, 20 July 2012

How to write connection string in web config file

<?xml version="1.0"?>



<configuration>
<system.web>
<compilation debug="true" targetFramework="4.0" />
</system.web>
<appSettings>
<add key="ConnectionString" value="server=localhost;user id=sa;Password=surandai;database=IEB"/>
</appSettings>
</configuration>

Validate(value Required) Text Box Using Javascript in asp.net

<%@ 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">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<script type="text/javascript">
function Myfunc() {
if (check("<%=TextBox1.ClientID %>"))
alert("Fill TextBox1");
else if (check("<%=TextBox2.ClientID %>"))
alert("Fill TextBox2");
else if (check("<%=TextBox3.ClientID %>"))
alert("Fill TextBox3");
else if (check("<%=TextBox4.ClientID %>"))
alert("Fill TextBox4");
else if (check("<%=TextBox5.ClientID %>"))
alert("Fill TextBox5");
}
function check(Id) {
if (!document.getElementById(Id)) return false;
return (document.getElementById(Id).value.length == 0)
}
</script>
</head>
<body>
<form id="form1" runat="server">
<div>
</div>
<center>
<asp:Label runat="server" ID="Label1" Text="TextBox Validation using Javascript" ForeColor="Red"></asp:Label><br />
<asp:Label runat="server" ID="Lable1" Text="Lable1"></asp:Label>
<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
<br />
<asp:Label runat="server" ID="Label2" Text="Lable2"></asp:Label>
<asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
<br />
<asp:Label runat="server" ID="Label3" Text="Lable3"></asp:Label>
<asp:TextBox ID="TextBox3" runat="server"></asp:TextBox>
<br />
<asp:Label runat="server" ID="Label4" Text="Lable4"></asp:Label>
<asp:TextBox ID="TextBox4" runat="server"></asp:TextBox>
<br />
<asp:Label runat="server" ID="Label5" Text="Lable5"></asp:Label>
<asp:TextBox ID="TextBox5" runat="server"></asp:TextBox>
<br />
<asp:Button ID="Button1" runat="server" Text="Button" OnClientClick="Myfunc();" />
</center>
</form>
</body>
</html>

Common Function For Connection String

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;

namespace IEBDataLayer
{
class Common
{
public string GetConnectionString()
{
return ConfigurationSettings.AppSettings["ConnectionString"].ToString();
}
}
}

Import Excel From Folder into C#

Tuesday, 10 July 2012

How to stored values and retrieve vaule for Checklist box in windows application

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using MySql.Data.MySqlClient;
using System.Configuration;

namespace MalaysiaQRA
{
public partial class Master_APW4 : Form
{
MySqlCommand cmd;
MySqlDataReader rd;
MySqlConnection con;
MySqlTransaction tn;
DataTable dt = new DataTable();
public Master_APW4()
{
string connection = ConfigurationManager.AppSettings["ConnectionString"].ToString();
con = new MySqlConnection(connection);
InitializeComponent();
}

private void Master_APW4_Load(object sender, EventArgs e)
{
DataSet ds = new DataSet();
MySqlDataAdapter ada = new MySqlDataAdapter("select cur_cod,des from tblcurrency",con);
ada.Fill(ds);
for (int i=0; i < ds.Tables[0].Rows.Count; i++)
{
checkedListBox1.Items.Add(ds.Tables[0].Rows[i][0].ToString() +"-->"+ ds.Tables[0].Rows[i][1].ToString());
}
}

private void btnSave_Click(object sender, EventArgs e)
{
try
{
con.Open();
cmd = new MySqlCommand("delete from master_apw",con);
cmd.ExecuteNonQuery();
for (int i = 0; i < checkedListBox1.Items.Count; i++)
{
if (checkedListBox1.GetItemChecked(i) == true)
{ int u=checkedListBox1.Items[i].ToString().Length;
cmd = new MySqlCommand("insert into master_apw (cur_cod,description) values ('" + checkedListBox1.Items[i].ToString().Substring(0, 3) + "','" + checkedListBox1.Items[i].ToString().Substring(6,u-6) + "')", con);
cmd.ExecuteNonQuery();

}
}

con.Close();
MessageBox.Show("Updated Successfully!!!");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
con.Close();
}
}

}
}

Monday, 9 July 2012

Break Out From The Frame Using Javascript

<html>
<head>
<script type="text/javascript">
function breakoutfromframe()
{
if (window.top!=window.self)
{
window.top.location="xxxx.html";
}
}
</script>
</head>
<body>

<input type="button" onclick=" breakoutfromframe ()" value=" Break out from frame !">

</body>
</html>

Masked Edit Text Box Using Java Script

<%@ 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">

function SetAccountNum(keycode, objCtrl) {

if ((keycode > 47 && keycode < 58) || (keycode > 95 && keycode < 106)) {

var Ctrlval = objCtrl.value.trim().replace(/ /g, "");

if (Ctrlval.length == 0) return;

//Split Characters

var arrvalue = Ctrlval.split("");

var j = 0, result = "";

for (var i = 0; i < arrvalue.length; i++) {

if (j == 3) {

result = result + " " + arrvalue[i];

j = 0;

}

else {

result = result + arrvalue[i];

}

j++;

}

window.document.getElementById("<%=txtAcc.ClientID %>").value = result;

//SetCursorToTextEnd(objCtrl);

}

function SetCursorToTextEnd(text) {

if (text != null && text.value.length > 0) {

if (text.createTextRange) {

var FieldRange = text.createTextRange();

FieldRange.moveStart('character', text.value.length);

FieldRange.collapse();

FieldRange.select();

}

function BlockChar(keycode) {

return ((keycode > 47 && keycode < 58) || (keycode > 95 && keycode < 106));

}

</script>

</head>

<body>

<div>

<asp:TextBox ID="txtAcc" runat="server" Height="38px" Width="417px" onkeyup="javascript:SetAccountNum(event.keyCode,this);" onkeydown="javascript:return BlockChar(event.keyCode);"></asp:TextBox>

</div>

</form>

</body>

</html>