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

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.

 

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

clear all cotrol values of a page

<script type="text/javascript">
    function allclear()
    {    //debugger
        var elements=document.getElementsByTagName("INPUT");
        for(i=0;i<elements.length;i++)
            if(elements[i].type=="radio")
                elements[i].checked=false;
        for(i=0;i<elements.length;i++)
            if(elements[i].type=="checkbox")
                elements[i].checked=false;
        for(i=0;i<elements.length;i++)
           if (elements[i].type=="text")
                elements[i].value="";
        var elements1=document.getElementsByTagName("SELECT");
        for(i=0;i<elements1.length;i++)
            if(elements1[i].id!="" && elements1[i].id!=null)
                elements1[i].selectedIndex=0;
     }
</script>
---------------------------------------------------------------------------------------
private void ClearControls(Control c)
    {
        if (c.GetType() == typeof(System.Web.UI.WebControls.TextBox))
            ((TextBox)c).Text = "";
        if (c.GetType() == typeof(System.Web.UI.WebControls.RadioButtonList))
        {
            RadioButtonList rbtl = (RadioButtonList)c;
            for (int j = 0; j < rbtl.Items.Count; j++)
                rbtl.Items[j].Selected = false;
        }
        if (c.GetType() == typeof(System.Web.UI.WebControls.DropDownList))
            ((DropDownList)c).SelectedIndex = 0;
        if (c.GetType() == typeof(System.Web.UI.WebControls.RadioButton))
            ((RadioButton)c).Checked = false;
        foreach (Control child in c.Controls)
            ClearControls(child);
}

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

  private void DisableControls(Control c)
    {
        if ((c is TextBox) || (c is LinkButton) || (c is Button) || (c is CheckBox) || (c is CheckBoxList) || (c is RadioButtonList) || (c is DropDownList) || (c is Panel) || (c is ImageButton))
        {
            ((WebControl)c).Enabled = false;
        }
        foreach (Control child in c.Controls)
            DisableControls(child);
    }

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>

make webpage title scrolling

<script type="text/javascript">
    msg = "MyWebPageTitle";
    msg = "..." + msg;pos = 0;
    function scrollMSG() {
    document.title = msg.substring(pos, msg.length) + msg.substring(0, pos);
    pos++;
    if (pos >  msg.length) pos = 0
    window.setTimeout("scrollMSG()",200);
    }
    scrollMSG();
</script>

javascript handlers

// Add handler using the getElementById method
$addHandler(Sys.UI.DomElement.getElementById("Button1"), "click", toggleCssClassMethod);
// Add handler using the shortcut to the getElementById method
$addHandler($get("Button2"), "click", removeCssClassMethod);

String formats

String strVal = "a(1), a1(2), a.3(3), a'4 (4), a.5:(5)";
Regex.Replace(strVal, @"[\w\s';.&,:]+\((\d+)\)", "$1") = 1,2,3,4,5
-----------------------------------------------------------------------
string dNum = "32.123456";
string.Format("{0:c}", double.Parse(dNum)) = $32.12
string.Format("{0:n}", double.Parse(dNum)) = 32.12
string.Format("{0:D}", DateTime.Now) = Thursday, June 28, 2012
string.Format("{0:T}", DateTime.Now) = 10:59:28 AM
DateTime.Now.ToString("dd/MM/yyyy hh:mm:ss") = 28/06/2012 10:59:28
---------------------------------------------------------------------------------
decimal dNum = decimal.Parse("32.12345");
Response.Write(dNum.ToString("0.00"));

image file path to byte[] format

private byte[] imgStream(string filePath)
{
MemoryStream stream = new MemoryStream();
tryagain:
try
{ Bitmap bmp = new Bitmap(filePath);
  bmp.Save(stream, System.Drawing.Imaging.ImageFormat.Jpeg);
}
catch (Exception ex) { goto tryagain; }
return stream.ToArray();
}

System.Drawing.ColorTranslator.FromHtml("#D8BFD8");