get the serial no. for records of table in sqlserver

select empname, row_number() over(order by empid)as 's.no' from tblEmp

Get the list of columns in a table in sqlserver

select column_name from information_schema.columns where table_name='tblEmp'
or
sp_columns 'tblName'
or
select name from sys.columns where object_id=object_id('tblEmp')
or
select rows from sysindexes where id=object_id('tblEemp') and indid in (0,1)

get the list of tables that r available in a particular database in sqlserver

select * from information_schema.tables
or
select * from sys.tables
or
sp_tables

copy one column data into other column of a same table using query in sqlserver

update tblEmp set address1=address2

how to find out second highest salaried employee details of a table in sqlserver

with s1(id,names,sal) as (select 1 ,'a' ,1.00 union all select 2,'b',2.00 union all select 3,'c',3.00 union all select 4,'d',4.00 union all select 5,'f',5.00)

select * into sam from s1

select * from sam

--get 2nd highest sal.emp

• select top 1 * from sam where sal in (select top 2 sal from sam order by sal desc) order by sal


• select * from sam tbl1 where 1=(select count(distinct sal) from sam tbl2 where tbl1.sal<tbl2.sal)


• select top 1 * from ( select top 2 * from sam order by sal desc)s order by sal


• select * from (select *, rn=row_number() over(order by sal desc) from sam) s where rn=2


• select * from (select *, rn = dense_rank() over(order by sal desc) from sam) s where rn=2

--get 2nd lowest sal.emp

• select top 1 * from sam where sal in (select top 2 sal from sam order by sal) order by sal desc

 

Max possible columns per a table in sqlserver is 1024 but in oracle 1000
Max possible databases : 32,767

Operator OverLoading in console apps

class ClsComplex
{
int R, I;
public ClsComplex(int R, int I)
{
this.R = R;
this.I = I;
Console.WriteLine("{0} + {1}i", R, I);
}
public static ClsComplex operator +(ClsComplex Num1, ClsComplex Num2)
{
ClsComplex Num3 = new ClsComplex(Num1.R + Num2.R, Num1.I + Num2.I);
return Num3;
}
}
class ClsOpOverLoad
{
static void Main(string[] args)
{
Console.Write("C1= ");
ClsComplex C1 = new ClsComplex(4, 5);
Console.Write("C2= ");
ClsComplex C2 = new ClsComplex(8, 2);
Console.Write("C3= ");
ClsComplex C3 = C1 + C2;
Console.Read();
}
}

Method OverLoading in console apps

class mOverLoading
{
int x=10;
public void show()
{
Console.WriteLine("default constructor");
Console.WriteLine(x + this.x);
}
public void show(int x)
{
Console.WriteLine("Parameterized constructors");
Console.WriteLine(x+" "+this.x);
}
public void show(string s, int x)
{
Console.WriteLine("Parameterized constructor");
Console.WriteLine(s + " " + x);
}
public static void Main()
{
mOverLoading mol = new mOverLoading();
mol.show();
mol.show(123);
mol.show("satya", 456);
Console.ReadLine();
}
}

Constructor OverLoading in Console apps

class CACOverLoading
{
int x;
public CACOverLoading()
{
Console.WriteLine("Default constructor");
x = 123;
}
public CACOverLoading(int x)
{
Console.WriteLine("Parameterized constructor");
Console.WriteLine(x);
this.x = 789;
}
public void display()
{
Console.WriteLine(x);
}
public static void Main()
{
CACOverLoading col = new CACOverLoading();
col.display();
CACOverLoading col1 = new CACOverLoading(456);
col1.display();
Console.ReadLine();
}
}

Prime No. in console apps

class Prime
{
static void Main(string[] args)
{
int j = 0;
int count = 0;
Console.WriteLine("Plz enter a number: ");
int n =int.Parse( Console.ReadLine() );
Console.WriteLine("The Prime no's are:");
for (int i = 2; i <= n; i++)
{
for (j = 2; j <= i; j++)
{
if (i % j == 0)
{
break;
}
}
if (i == j)
{
Console.WriteLine(i);
count++;
}
}
Console.WriteLine();
Console.Write("Total Prime No's: "+count);
Console.ReadLine();
}

Virtual class overriding

class Virtual
{
public virtual void add(int a, int b)
{
Console.WriteLine(a + b);
}
}
class overridevirtual:Virtual
{
public override void add(int a, int b)
{
Console.WriteLine(a);
}
public static void Main()
{
Virtual vir = new Virtual();
vir.add(1,2);
Console.ReadLine();
}
}

Abstract overriding in console apps

abstract class Abstract {

public abstract void mul(int x);
public void add(int x, int y)
{
Console.WriteLine("add is: "+(x + y));
}
}
class overrideabstract : Abstract
{
public override void mul(int a)
{
Console.WriteLine("mul is: "+a*a);
}
public static void Main()
{
overrideabstract ovra = new overrideabstract();
ovra.add(4, 5);
ovra.mul(3);
Console.ReadLine();
}
}

Note: An abstract class can implement from another abstract class. and a class can implement only one abstract class at a time.

Reverse of a string or number in console apps

class Reverseofno
{
public static void Main()
{
Console.WriteLine("enter a no/string: ");
string a = Console.ReadLine();
for (int x = a.Length - 1; x >= 0; x--)
{
Console.Write(a[x]);
}
Console.WriteLine();
--------------------------------------------------
string bb = string.Empty;
Console.WriteLine("enter a no/string: ");
string b = Console.ReadLine();
for (int x = b.Length - 1; x >= 0; x--)
{
bb += b[x].ToString();
}
string[] bbb = new string[bb.Split(' ').Length];
for (int y = 0; y = 0; z--)
{
Console.Write(bbb[z]);
}
Console.WriteLine();
---------------------------------------------------
Console.WriteLine("enter a name: ");
string n= Console.ReadLine();
if(bbb.Contains(n+" "))
Console.WriteLine("found");
else
Console.WriteLine("not found");
Console.ReadLine();
}
}

how to know the current system name and ip address in asp.net

Response.Write(Request.ServerVariables["REMOTE_ADDR"].ToString()+"
");
Response.Write(HttpContext.Current.Server.MachineName);

sp for search

create proc sp_search(@p varchar(20))
as
begin
select lastname from tblemployee where lastname is not null and lastname like @p+'%'
end
--exec sa_tstname c

GridView data filter with StoredProcedure

SqlCommand cmd=newSqlCommand();
SqlConnection con = new SqlConnection("..your connection string....");
//Data Source=satya;Initial Catalog=MyDB;Persist Security Info=True;User ID=sa;Password=sa123
SqlDataAdapter da=newSqlDataAdapter();
DataSet ds=newDataSet();
cmd.Parameters.Clear();

cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sp_getall";
cmd.Connection = con;
cmd.Parameters.Add("@p", SqlDbType.VarChar, 20).Value = txtSearch.Text;

da = newSqlDataAdapter(cmd);
da.Fill(ds);
DataView dv = ds.Tables[0].DefaultView;

dv.RowFilter = "eName like '" + mr+"'";
gvsearch.DataSource = dv;
gvsearch.DataBind();

create proc sp_getall
as
begin
select*from tblemp
end

------------------------------------

cmd.CommandText = "select * from user_master";
cmd.CommandType = CommandType.Text;
cmd.Connection = con;
con.Open();
dr = cmd.ExecuteReader();
//table, ExecuteScalar - single value object type, ExecuteNonQuery - Count
gv1.DataSource = dr;
gv1.DataBind();
con.Close();