DotNet By Satya

20 August 2012

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

15 August 2012

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'

14 August 2012

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'

13 August 2012

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

22 July 2012

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

16 July 2012

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

15 July 2012

General Page Life-Cycle Event Stages

The events occur in the following sequence.

Its best to turn on tracing(<% @Page Trace=”true”%>) and track the flow of events :

PageRequest – The page request occurs before the page life cycle begins. When the page is requested by a user, ASP.NET determines whether the page needs to be parsed and compiled(therefore beginning the life of a page), or whether a cached version of the page can be sent in response without running the page.

Start – In the start stage, page properties such as Request and Response are set. At this stage, the page also determines whether the request is a postback or a new request and sets the IsPostBack property. The page also sets the UICulture property.

PreInit– This event represents the entry point of the page life cycle. If you need to change the Master page or theme programmatically, then this would be the event to do so. Dynamic controls are created in this event.

protected void Page_PreInit(object sender, EventArgs e)

Init – Each control in the control collection is initialized. i.e., During page Intialization, controls on the page are available and each control’s UniqueID property is set. A master page and themes are also applied to the page if applicable. If the current request is a postback, the postback data has not yet been loaded and control property values have not been restored to the values from viewstate.

protected void Page_Init(object sender, EventArgs e)

Init Complete* - Page is initialized and the process is completed.

protected void Page_InitComplete(object sender, EventArgs e)

PreLoad* - This event is called before the loading of the page is completed.

protected void Page_PreLoad(object sender, EventArgs e)

Load– This event is raised for the Page and then all child controls. The controls properties and view state and control state can be accessed at this stage. This event indicates that the controls have been fully loaded.

protected void Page_Load(object sender, EventArgs e)

LoadComplete* - This event signals indicates that the page has been loaded in the memory. It also marks the beginning of the rendering stage.

protected void Page_LoadComplete(object sender, EventArgs e)

PreRender – If you need to make any final updates to the contents of the controls or the page, then use this event. It first fires for the page and then for all the controls.

protected void Page_PreRender(object sender, EventArgs e)

Savestate

Rendering – Before rendering, viewstate is saved for the page and all controls. At this stage, the page calls the Render method for each control, providing a text writer that writes its output to the OutputStream object of the page’s Response property.

PreRenderComplete* - Is called to explicitly state that the PreRender phase is completed.

protected void Page_PreRenderComplete(object sender, EventArgs e)

SaveStateComplete* - In this event, the current state of the control is completely saved to the ViewState.

protected void Page_SaveStateComplete(object sender, EventArgs e)

Unload – This event is typically used for closing files and database connections. At times, it is also used for logging some wrap-up tasks.

protected void Page_Unload(object sender, EventArgs e)

The events marked with * have been introduced in ASP.NET 2.0.

page life cycle -- click to download the document

Comparison with Init and Load events in Master and content pages:

Page Init Event	Page Load event
1. Execution sequence starts from Init event of Main master page--> Sub Master Page --> ……. -->Requested ASPX page init event.	1. Execution sequence starts from Load event of Requested ASPX page --> Sub Master Page --> ……. --> Main master page load event.

09 July 2012

convertion differences between Convert.ToInt32 and int.Parse

int.Parse("12") --> 12
int.Parse("12.12") --> Format Ex
int.Parse(null) --> Null Ex
int.Parse("889708888888899888888098980909089808990890890890900") --> overflow Ex

convert.ToInt32("12") --> 12
convert.ToInt32("12.12") --> Format Ex
convert.ToInt32(null) --> 0
convert.ToInt32("889708888888899888888098980909089808990890890890900") --> overflow Ex

int re = convert.ToInt32(Session["val"]); --> 0
int re = int.Parse(Session["val"]); --> ArgumentNULLException

int re = 0, res;
if (int.TryParse(re.ToString(), out res))
{ string fds = res.ToString(); }
else
{ string dfsa = res.ToString(); }

re = Convert.ToInt32(ViewState["fdsa"]); --> 0
re = (int)ViewState["fdsa"]; --> null reference exception, Object reference not set to instance of an object // it is fastest than all
re = int.Parse(ViewState["fdsaf"].ToString()); --> null reference exception, Object reference not set to instance of an object
--
Convert.ToString() handles the NULL
.ToString() throws the NULL reference exception

28 June 2012

clear all cotrol values of a page

make listbox items colorful

protected void Page_PreRender(object sender, EventArgs e)
{
    bool flag=false;
    foreach (ListItem li in ListBox1.Items)
    {
        if (flag)
        {
            li.Attributes.Add("class", "optred");
            flag = false;
        }
        else
        {
            li.Attributes.Add("class", "optblue");
            flag = true;
        }
    }
}
<style type="text/css">
.optred{background-color:red;}
.optblue{background-color:blue;}
</style>