Define candidate key, alternate key, composite key.

A candidate key is one that can identify each row of a table uniquely. Generally a candidate key becomes the primary key of the table. If the table has more than one candidate key, one of them will become the primary key, and the rest are called alternate keys. A key formed by combining at least two or more columns is called composite key.

//link for Composite key understand

http://weblogs.sqlteam.com/jeffs/archive/2007/08/23/composite_primary_keys.aspx

What’s the difference between a primary key and a unique key?

Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Another major difference is that, primary key doesn’t allow NULLs, but unique key allows one NULL only.

Access view state value in another page

//Find out the view state value in another page but you can find only Init and page load event and only redirect on another page Server.Transfer than you can get the value otherwise it will give error to you...

//first page
take one text box with name of textbox1.text and fill the value of text box.....

//second page init and load event
Page Poster = this.PreviousPage;
TextBox txtNewTest = (TextBox)Poster.FindControl("TextBox1");

string sDisplay = txtNewTest.Text;

Response.Write(sDisplay);

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;
}
}
}