split string in c# with regular expression instead of forloop

string s = "er32(s),re4'wr(a),f2.0(t)A(y),(a)";
        s = Regex.Replace(s, @"[\w\s';.&,:]+\(([\w\d.]+)\)", "$1");
        Response.Write(s);

Convert column to row in SQL Server

select (select convert(varchar(max),id)+',' from #tbl1 for xml path('')) colTOrow

Get the html control to asp without using runat='server' property

string strValue = Page.Request.Form["name of the control"].ToString();

How to get the current row in the grid view rowcommand event

GridViewRow row = ((LinkButton)e.CommandSource).NamingContainer as GridViewRow;

In RowDataBound for datakeys

GridView1.DataKeys[e.Row.RowIndex].Value.ToString()

((DataRowView)e.Row.DataItem)["mainid"].ToString()

persistedselection property for a gridview control in asp

You can specify how row selection is persisted when the GridView control is in paging mode. By default, row selection is persisted by row index. For example, if the second row in a page is selected, the second row in all pages will be selected.

Alternatively, you can specify that row selection is persisted by data-row key. In that case, when a row is selected, rows in other pages are not selected. To enable this functionality, set the EnablePersistedSelection property to true as shown in the following example:

<asp:GridView id="GridView1" runat="server" PersistedSelection="true">
</asp:GridView>

datarow to dataset in asp

DataTable dt = (DataTable)ViewState["tabData"];
DataRow[] dr = dt.Select("test_cd='899'");

DataSet dsnew = new DataSet();
DataTable dtnew = dt.Clone();
dtnew.Constraints.Clear();
DataRow drnew = dtnew.NewRow();

drnew.ItemArray = dr[0].ItemArray;
dtnew.Rows.Add(drnew);
dsnew.Tables.Add(dtnew);

Query to attaching of database using only .mdf file without associated log file

CREATE DATABASE ... FOR ATTACH_REBUILD_LOG

with log file: CREATE DATABASE ... FOR ATTACH

Order of the sqlquery that logically processed

What is the order of execution in the following list?

• select


• from


• where


• group by


• order by


• having

A: from,where,group by,having,select,order by

ref:  http://msdn.microsoft.com/en-us/library/ms174149.aspx

Rename/Alter the column for a table in sqlserver

To add new column: Alter Table TableName add ColName dataType

To alter a column: Alter Table TableName Alter Column ColumnName DataType NULL/NOT NULL

To rename a column:

EXEC sp_rename
@objname = ' TableName. OldColumnName’,
@newname = 'New ColumnName',
@objtype = 'COLUMN'

note: computed columns(col3=col1+col2) doesnt hav a chance to rename, the only way is it has to be dropped and added back

Ex: Alter Table tblAddress Alter Column Address VARCHAR(512) NULL

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

To rename the table name:

sp_RENAME 'TestTable', 'NewTable'

can u set innerjoin for update command?

Update Employee set State = s.StateID  
from Employee e  
inner join State s on e.State = s.StateName

Decide whether i/p parameter contains integers or not in sqlserver

IF PATINDEX('%[0-9]%',@val) > 0  
print 'i/p value contain integer'
else
print 'Not contain integer'

Insert records (multiple) into a table using single query in sqlserver

create table tblEmp (eid int, ename varchar(20), estatus bit)

sqlserver2005

basic: insert into tblEmp values (0,'emp', 'True')

insert into tblEmp select 1,'emp1','True'

union all select 2,'emp2','True'

union all select 3,'emp3','True'

sqlserver2008

insert into tblEmp values (1,'emp1','True'),(2,'emp2','True'),(3,'emp3','True')

If table not exist in database then:

select * into tblEmp from (select 1 eid,'emp1' ename,'True' estatus) tbltemp

SQL Command Types

DDL is Data Definition Language statements. Some examples:

• CREATE - to create objects in the database


• ALTER - alters the structure of the database


• DROP - delete objects from the database


• TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed


• RENAME - raname column for a table


• COMMENT - add comments to the data dictionary

DML is Data Manipulation Language statements. Some examples:

• SELECT - retrieve data from the a database


• INSERT - insert data into a table


• UPDATE - updates existing data within a table


• DELETE - deletes all records from a table, the space for the records remain


• CALL - call a PL/SQL or Java subprogram


• EXPLAIN PLAN - explain access path to data


• LOCK TABLE - control concurrency

TCL is Transaction Control Language statements. Some examples:

• COMMIT - save work done


• SAVEPOINT - identify a point in a transaction to which you can later roll back


• ROLLBACK - restore database to original since the last COMMIT


• SET TRANSACTION - Change transaction options like what rollback segment to use

DQL/DRL is Data Query/Retrieve Language statements. Some examples:

• SELECT - retrieve data from the a database

DCL is Data Control Language statements. Some examples:

• GRANT - gives user's access privileges to database


• REVOKE - withdraw access privileges given with the GRANT command


• DENY - remove user's access privileges to database

select distinct of rows from a table of multiple duplicate columns

create table emp(empid int,empname varchar(50),sal money,status bit)

insert into emp select 1,'satya',20000,1 union all
select 1,'satya',20000,2 union all
select 1,'satya',20000,1000 union all
select 2,'sant',21000,-3 union all
select 3,'chclt',42000,0

alter table emp add cno int identity(1,1)

select empid, empname, sal from emp where cno in (select min(cno) from emp group by empid,empname,sal,comm,status)

output

empid empname sal
1           satya          20000
2           sant            21000
3           chclt           42000

alter table emp drop column cno

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

with x as(select *,row_number() over(partition by empid,empname,sal,comm,status order by empid) as count from emp)
delete from x where count>1

array conversions

using System;
using System.Collections.Generic;
---------------------------
arraylist to array
> string[] a = (string[])arraylist.toarray(typeof(string));
> arraylist.copyto(array,0);
list to array
> string[] a = list.toarray();
---------------------------
arraylist to arraylist
> arraylist.addrange(arraylist);
---------------------------
array to arraylist
> arraylist.addrange(array);
> arraylist a = new arraylist(array);
array to list
> list<string> l = new list<string>(array)
> list<string> l = array.tolist();
---------------------------
array to array
> string[]  a = (string[])array.clone();// Clone method creates a shallow copy of an array. A shallow copy of an Array copies only the elements of the Array
---------------------------
array declaration and intilization methods
int[] a;
a = new int[3]{3,5,6};
int[] a = new int[]{3,4,5};
int[] a = new int[3]{3.4,5};
int[] a = new int[3];
a[0] = 2; a[1] = 3; a[2] = 4;
---------------------------
search arraylist
arraylist.sort();
arraylist.binaryseach(43);
arraylist.indexof(searchstr , 0);
---------------------------
string to bytearray
> byte[] = (new UnicodeEncoding()).GetBytes(stringToConvert);
---------------------------
bytearray to string
>char[] cs = new char[bs.Length];
for (int i = 0; i < cs.Length; ++i)
{
cs[i] = Convert.ToChar(bs[i]);
}
---------------------------

char array to string
string str = new string(chararray);

string to char array
string s = "hello";
char[] c = s.ToCharArray();
---------------------------

array to datatable

>DataTable dataTable = new DataTable();dataTable.LoadDataRow(array, true);//Pass array object to LoadDataRow methodreturn dataTable;
---------------------------
get array size
array.getlength(0).tostring();
array.getlowerbound(0).tostring();
---------------------------
better to use List instead of ArrayList
better to use Dictionary instead of HashTable