split last string in sqlserver

username

emp/satya
md/sai
emp/develop/suresh
clerk/surya
comp/sam


select reverse(substring(reverse(username),0,charindex('/',reverse(username)))) from tblemp

creating new row for a gridview dynamically

if (!IsPostBack)
{
getdata();
}
----------------------------
void getdata()
{
dalLogin dal = new dalLogin();
DataSet ds = new DataSet();
ds= dal.getdata();
ViewState["table"] = ds.Tables[0];
bindgrid();
}
void bindgrid()
{
GridView1.DataSource = (DataTable)ViewState["table"];
GridView1.DataBind();
}
-------------------------------
protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.Header)
{
e.Row.Cells[5].Attributes.Add("style", "display:none");
}
if (e.Row.RowType == DataControlRowType.DataRow)
{
for (int i = 0; i < GridView1.Rows.Count; i++)
GridView1.Rows[i].Cells[5].Attributes.Add("style", "display:none");
}
}
-----------------------------------
protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
{
if (e.CommandName == "newrow")
{
DataTable dt = (DataTable)ViewState["table"];
dt.Rows.Add(dt.NewRow());
ViewState["table"] = dt;
GridView1.EditIndex = GridView1.Rows.Count;
bindgrid();
Button btn = (Button)GridView1.Rows[GridView1.Rows.Count - 1].FindControl("Button1");
btn.Text = "save";
btn.CommandName = "sav";
}
else if (e.CommandName == "sav")
{
TextBox t1 = (TextBox)GridView1.Rows[GridView1.Rows.Count - 1].Cells[0].Controls[0];
TextBox t2 = (TextBox)GridView1.Rows[GridView1.Rows.Count - 1].Cells[1].Controls[0];
TextBox t3 = (TextBox)GridView1.Rows[GridView1.Rows.Count - 1].Cells[2].Controls[0];
TextBox t4 = (TextBox)GridView1.Rows[GridView1.Rows.Count - 1].Cells[3].Controls[0];
TextBox t5 = (TextBox)GridView1.Rows[GridView1.Rows.Count - 1].Cells[4].Controls[0];
DataTable dt = (DataTable)ViewState["table"];
dt.Rows.RemoveAt(GridView1.Rows.Count - 1);
DataRow dr = dt.NewRow();
dr[0] = t1.Text; dr[1] = t2.Text; dr[2] = t3.Text; dr[3] = t4.Text; dr[4] = t5.Text;
dt.Rows.Add(dr);
ViewState["table"] = dt;
GridView1.EditIndex = -1;
bindgrid();
Button btn = (Button)GridView1.Rows[GridView1.Rows.Count - 1].FindControl("Button1");
btn.Text = "new";
btn.CommandName = "newrow";
}
}

checking regular expression for different date formats

string spl = @"([/|\-|.|\s])?";
string d = "(0?[1-9]|[12][0-9]|3[01])";
string m = "(0?[1-9]|1[012])";
string mmm = "([J|j]an(uary)?|[F|f]eb(ruary)?|([M|m]a(r(ch)?|y))|[A|a]pr(il)?|[J|j]u(n(e)?|l(y)?)|[A|a]ug(ust)?|[O|o]ct(ober)?|([S|s]ep(t)?|[N|n]ov|[D|d]ec)(ember)?)";
string y = "((19|20)?[0-9]{2})";

string timspl = @"([:|.|\s])?";
string hh = @"(0?[0-9]|1[012])";
string min = @"([0?|1|2|3|4|5][0-9]|60)";
string ss = @"([0?|1|2|3|4|5][0-9]|60)";
string tt = @"([A|a|p|P]m|[A|P]M)?";
string time = @"(" + hh + timspl + min + timspl + ss + ")?"+spl+tt;

string zzz = @"(\+[0-9]{2}(\:)?[0-9]{2})?"+spl+tt;

string date = @"("+d+spl+"("+m+"|"+mmm+")|("+m+"|"+mmm+"+)+"+spl+d+")"+spl+y+"";

string exp = "^(" + date + spl + time + spl + zzz + ")|(" + time + spl + date + spl + zzz + ")|(" + zzz + spl + time + spl + date + ")$";
if (Regex.IsMatch(txtreg.Text.ToLower().Trim(), exp))
lblreg.Text = "available";
else
lblreg.Text = "not available";

date regular expressions in .net

@"(([0-9]{1,31})[/|-|(\s)]([0-9]{1,12})[/|-|(\s)](\d{4}|\d{2}))"
//DD/MM/YY(YY)
@"(([0-9]{1,12})[/|-|(\s)]([0-9]{1,31})[/|-|(\s)](\d{4}|\d{2}))"
//MM/DD/YY(YY)
@"((\d{4}|\d{2})[/|-|(\s)]([0-9]{1,31})[/|-|(\s)]([0-9]{1,12}))"
//YY(YY)/DD/MM
@"((\d{4}|\d{2})[/|-|(\s)]([0-9]{1,12})[/|-|(\s)]([0-9]{1,31}))"
//YY(YY)/MM/DD
@"(([0-9]{1,12})[/|-|(\s)](\d{4}|\d{2})[/|-|(\s)]([0-9]{1,31}))"
//MM/YY(YY)/DD
@"(([0-9]{1,31})[/|-|(\s)](\d{4}|\d{2})[/|-|(\s)]([0-9]{1,12}))"
//DD/YY(YY)/MM
@"^(([0-9]{1,2})(\s)?(Jan(uary)?|Feb(ruary)?|(Ma(r(ch)?|y))|Apr(il)?|Ju(n(e)?|l(y)?)|Aug|Oct(ober)?|(Sep(t)?|Nov|Dec)(ember)?)(\s)?(\d{2}|\d{4}))$"
//DD()MMM()YYYY

how to find out rank of employees based on their salaries

select empname,dense_rank()over(order by salary)as rank from tblEmp

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();