Update Statement for converting male to female and female to male

Update tablename set gender=(case, when gender='Male' then 'Female' else 'Male' end)

Instead of Trigger for Update

//Instead of triger will fired when user ant to update in emp table

create trigger tri_update on Emp
for update
as
begin
if update(ID)
begin
print'not update here'
rollback transaction
end
end

Randam Number Concatenate two string with insert the data in table

Create a table it has two column one is int type and another is varchar


string randomnumber()
{
SqlDataAdapter adp = new SqlDataAdapter("Select max(empid) as empid from emp1 ", con);
DataSet ds = new DataSet();
adp.Fill(ds);
string i="";
if (ds.Tables[0].Rows.Count > 0)
{
i = ds.Tables[0].Rows[0]["empid"].ToString();
}
if (i == "")
{
i = "0";
}
//i=i.Remove(0, 3);
i = (int.Parse(i) + 1).ToString();
if (i.Length == 2)
{
i = "OCG0" + i;
}
else if (i.Length == 1)
{
i = "OCG00" + i;
}
else if (i.Length > 2)
{
i = "OCG" + i;
}
return i;
}


int randomnumber1()
{
SqlDataAdapter adp = new SqlDataAdapter("Select max(empid) as empid from emp1 ", con);
DataSet ds = new DataSet();
adp.Fill(ds);
int i = 0;
if (ds.Tables[0].Rows.Count > 0)
{
i = Convert.ToInt32( ds.Tables[0].Rows[0]["empid"].ToString());
}
if (i.ToString() == "")
{
i = 0;
}

i = i + 1;

return i;
}

protected void Page_Load(object sender, EventArgs e)
{

if (!IsPostBack)
{

}
}
protected void btns_Click(object sender, EventArgs e)
{
SqlCommand com = new SqlCommand();
ListBox1.Items.Add(randomnumber().ToString());
con.Open();
com.CommandText = "insert into emp1 (empid,EMPCODE) values ("+randomnumber1()+" ,'" + randomnumber().ToString() +"')";
com.Connection = con;
com.ExecuteNonQuery();
con.Close();

}

Store image in DataBase and Display in Gridview and picture box

//Cretate table in sql server "storeimage"
Create table storeimage(
name varchar(100),
pics image)

//code in .Net

byte[] ReadFile(string sPath)
{
byte[] data = null;
FileInfo fInfo = new FileInfo(sPath);
long numBytes = fInfo.Length;
FileStream fStream = new FileStream(sPath, FileMode.Open, FileAccess.Read);
BinaryReader br = new BinaryReader(fStream);
data = br.ReadBytes((int)numBytes);
return data;
}

SqlConnection con = new SqlConnection("Data source=.;Database=master;user id=;password=; integrated security=sspi;");
SqlCommand cmd;


private void Form1_Load(object sender, EventArgs e)
{
SqlDataAdapter da = new SqlDataAdapter("SElect * from storeimage", con);
DataSet ds = new DataSet();
da.Fill(ds);
dataGridView1.DataSource = ds.Tables[0];
}

private void btnSave_Click(object sender, EventArgs e)
{
con.Open();
byte[] imageData;
imageData = ReadFile(textBox1.Text);
cmd = new SqlCommand("Insert into storeimage(name,pics) values('s' ,@OriginalPath)", con);
cmd.Parameters.Add(new SqlParameter("@OriginalPath", (object)imageData));

cmd.ExecuteNonQuery();
con.Close();

}


private void btnbrowse_Click(object sender, EventArgs e)
{
openFileDialog1.ShowDialog();
textBox1.Text = openFileDialog1.FileName;


}


private void dataGridView1_CellClick(object sender, DataGridViewCellEventArgs e)
{
if (e.RowIndex >0)
{
object a = dataGridView1.Rows[e.RowIndex].Cells["Pics"].GetType();
if (a.ToString() != "System.DBNull")
{

byte[] imageData = (byte[])dataGridView1 .Rows[e.RowIndex].Cells["pics"].Value;

imageData = (byte[])dataGridView1.Rows[e.RowIndex].Cells["pics"].Value;
Image newImage;
using (MemoryStream ms = new MemoryStream(imageData, 0, imageData.Length))
{
ms.Write(imageData, 0, imageData.Length);

newImage = Image.FromStream(ms, true);
}
//set picture
pictureBox1.Image = newImage;
}
}
}

Types of Classes

We have two types of classes..

1) Factory 2) Singleton

1) Factory : You can create a multiple object of factory class.

2) Singleton : only one instance of the class is created ex..Mouse Pointer,window popup.

//singleton programme

using system;
public class singleton
{
private static singleton instance;

private singleton()
{
}
public static singleton instance
{
get
{
if(instance==null)
{
instance=new singleton();
}
return instance;
}
}
}

Difference Between function and procedure in sqlserver

1)Function returns a value, but a procedure may return or may not return a value.

2)Function can take only input argument, but procedure may take both input and output parameters.

3)Function can be called inside the select statement but not the procedure

4)Function return 1 value only. Procedure can return multiple value.

5)Function are compiled and executed at runtime.Stored Procedure are stored in parsed and compiled format in the DataBase.

6)Function cannot affect the state of the database which means it cannot perform insert,update,delete and create operation on the database.
Stored procedure can affect the state of the database by using Insert,delete,update and create operation.

Number of user visit to your Website

//Take global.asax for make application level variable

void application_start(object sender,Eventargs e)
{
int count=convert.int32(application["hit"]);
count=0;
}

void session_start(object sender,eventargs e)
{
int count=convert.toint32(application["hit"]);
count=count+1;
application["hit"]=count;
}

//pageload

label1.text=convert.toint32(application["hit"]).tostrng();