Microsoft Technologies blogs(.net, Sql Server)

Calculated Running total in sql query

clock May 1, 2014 09:39 by author Arvind

There are lots of time in financial calculation the requeirment is get calculate the running total for each row. So I am writing the complete query with visible example.

Declare @TempTable Table

(

SNo int identity,

Total int

)

 

 

 

 INSERT INTO @TempTable(Total) Values(50)

 INSERT INTO @TempTable(Total) Values(60)

 INSERT INTO @TempTable(Total) Values(60)

 INSERT INTO @TempTable(Total) Values(10)

 INSERT INTO @TempTable(Total) Values(40)

 

 SELECT * FROM @TempTable

 

Select SNo, Temp.Total ,(select sum(Total) From @TempTable Where SNo <= Temp.SNo) 'Running Total'

From @TempTable Temp

 



Disabled button after click while processing in asp.net using javascript

clock April 7, 2014 11:59 by author Arvind

There is lots of way to prevent double clicking while postback process but this is the simple way to do that using window’s onbeforeunload eg. 

On aspx page your button control

<asp:Button ID="btnUpdate" runat="server" Text="Update" OnClick="btnUpdate_Clicked" />

Javascript code for that

<script type = "text/javascript">

function DisableButton()

{

document.getElementById("btnUpdate").disabled = true;

 }

window.onbeforeunload = DisableButton;

</script>

 

Reference: http://msdn.microsoft.com/en-us/library/ie/ms536907(v=vs.85).aspx



Create database using SMO instead of DMO in Sql server in C#.net

clock December 31, 2013 10:04 by author Arvind

Microsoft introduces SMO as new feature in sql server 2005 onward version. This is the lots of advantages over old DMO for manage sql server using program. SQL Server Management Objects (SMO) is objects designed for programmatic management of Microsoft SQL Server. One of my old projects using the DMO to create database and update all script related to sp/view and master data records as well as jobs. Once running server upgraded the sql server 2000 to sql server2012 after that this feature is stop working because of DMO no longer supported in sql server 2012. So now I made change code using SMO instead of old DMO and I love new feature of SMO.

I create one web services for the creating database and run script/jobs etc. by simply calling that method into existing application.

First of all just add reference of SMO dll in to web service project and use these on top

 

using Microsoft.SqlServer.Management.Smo;

using Microsoft.SqlServer.Management.Common;

using Microsoft.SqlServer.Management.Smo.Agent;

 

After that wrtie web method to create database and runt the sql script file for all stored proc/view/table etc.

 

[WebMethod]

        public bool CreateDataBase(string strSQLServer, string strDBName, string SqlUserName, string Sqlpwd,string strSchemaScriptPath, string strDataScriptPath)

        {

            bool flag = false;

            //here first of all create connection object

            ServerConnection objSc = new ServerConnection(strSQLServer, SqlUserName, Sqlpwd);

            //get server object based on connection

            Server objSrv = new Server(objSc);

            //create database object to create desired database using code.

            Database objDB = new Database(objSrv, strDBName);

            try

            {

                objDB.Create();

                //After creation of database create schema script

                FileInfo fileSchema = new FileInfo(strSchemaScriptPath);

                string scriptSchema = fileSchema.OpenText().ReadToEnd();

                objDB.ExecuteNonQuery(scriptSchema);

                //After createion os schema need to inser datascript

                FileInfo fileData = new FileInfo(strDataScriptPath);

                string scriptData = fileData.OpenText().ReadToEnd();

                objDB.ExecuteNonQuery(scriptData);

                flag = true;

            }

            catch (Exception ex)

            {

                string str = ex.Message;

                flag = false;

            }

            finally

            {

                objDB = null;

            }

            return flag;

        }

 

In above web method only you need to pass the sql server instance name, database name, sql server uid/pwd and script for schema and data script if any.



Paging in T-Sql using OFFSET and FETCH NEXT in Sql server 2012

clock December 5, 2013 06:19 by author Arvind

Microsoft introduced lots of good feature in Sql Server 2012. One of them is pagination (paging with OFFSET and FETCH NEXT). Pagination in Sql server improves performance mainly when retrieving and displaying large set of records from Database.

The OFFSET and FETCH NEXT clause is basically an extension of existing ORDER BY clause in T-SQL. This feature is very helpful for developer to get only desired number of records which they would like to display in ASP.NET Data Grid or other control instead of getting the all records. So using this feature developer can implement the paging on page with faster and only get desired records. It can be used only with the ORDER BY clause.

Actually by using OFFSET the pointer set to that record to skip those and fetching the record after that using NEXT command.

 

Syntax and example:-

[ORDER BY { order_by_expression [ ASC | DESC ] } [ ,...n][<offset_fetch>] ]

AND

<offset_fetch> ::= {OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS }    [FETCH { FIRST | NEXT } {integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY]}

 

 

We can use this feature in two different cases

Case 1: Skip first 100 rows from the result set and return the remaining rows

SELECT FirstName + ' ' + LastName FROM Employees ORDER BY FirstName OFFSET 100 ROWS

 

Case 2: Skip first 100 rows from the resultset and return next 50 rows.

SELECT FirstName + ' ' + LastName FROM Employees ORDER BY FirstName OFFSET 100 ROWS FETCH NEXT 50 ROWS ONLY

 

 

Limitations of OFFSET-FETCH clause:-

1. ORDER BY is mandatory to use OFFSET and FETCH clause.

2. OFFSET clause is mandatory with FETCH. You can never use, ORDER BY … FETCH.

3. TOP cannot be combined with OFFSET and FETCH in the same query expression.

4. The OFFSET/FETCH rowcount expression can be any arithmetic, constant, or parameter expression that will return an integer value. The rowcount expression does not support scalar sub-queries

 

I love this feature and hope you all enjoy this too………….

 

I also sharing one good query regarding the interview get nth highest salary of the employee with the help of OFFSET FETCH clause.

Eg- Suppose you have to write query for getting 51the highest salary of the employee you can write this simple query as follows

Select Salary From EmpSalDetails Order By Salary Desc OFFSET 50 ROWS FETCH NEXT 1 ROWS ONLY

 

It will return the 51st highest salary of the employee.

Ref:-http://technet.microsoft.com/en-us/library/gg699618.aspx



Create data script in sql server 2008/2012

clock December 2, 2013 12:48 by author Arvind

In new version of Sql server having good feature to generate data script automatically by selecting the option, this feature is very helpful when you are migrating data or updating production from test server. This is the simple step to generate data script of a table or whole database.

 

Step 1: Right click on database name and select “Tarsks” option in menu as in figure.

Step2: After selecting “Tasks” menu there are submenu comes up, in that submenu you can select “Generate script…” option.

Step3: Open “Generate and Publish Scripts” dialog box in that box click “Next” button to proceed.


Step4: After clicking “Next” button you can select the script option either one table or all table in that database or other database object eg views, stored procedures etc and click “Next” button.

Step 5: After selecting table you can click “Advanced” button to choose data script option.

Step 6: In “Advanced Scripting Options” you can select the “Type of data to script” either “Data only”, “Schema and data” and “Schema only” option as shown in figured.

Step 7 : If you selected “Data only” and click “Ok” and than “Next” and “Finish” button you will get all data script of the selected table.



Password for E-Aadhar card

clock November 13, 2013 04:54 by author Arvind

InIndianow a day everyone appling for Unique Identification Authority of India (UIDAI) and provide one option to get E-Aadhar card by downloading from there official website. But there are one problem facing lots of user after download the E-version of Aadhar Card that are not opening due to password protected. So I am writing this post to avoid the difficulty facing in opening the pdf e-aadhar card file.

 

Just open the E-Aadhar card pdf and the asking for password, your password for that pdf will be your postal address pin code/zip code(Which you given at the time of filling Aadhar card form).

Enjoy.......



Get two Database tables record within one stored procedure

clock August 6, 2013 11:44 by author Arvind

Sometime we need to get record from two different database tables record. So in such type of requirement if both databases are on same sql server than we can easily get two tables from different database within a stored procedure, that stored procedure may put in any one database where you want.

eg. Let consider two database db1 (table1) and db2 (table2) are on same sql server having two different table table1 and table2 respectively.

Now in db1 we write one stored procedure that need to fetch records from both tables as follows.

In db1, we can get data from table2 of db2 by using (double ..) as follows

Select * from db2..table2

If you want to get record from both tables using join condition than you store procedure should be looks like

Create Proc getDataFromTwoTable

AS

Begin

      Select * from db2..table2 T1 Inner Join table1 T2 ON T1.Id = T2.Id

 

End



How can toggle the word wrap in visual studio.net

clock August 2, 2013 06:29 by author Arvind

Once developer working in any environment there are some simple shortcut are more helpful.

Here just remind the shortcut or option for toggle the line in visual studio.net

Visual studio.net 2003 and 2005:-

Menu option: - Edit->Advanced->Word Wrap

Shortcut option:- ctrl+R+R Or ctrl+ R and ctrl+R (Two times)

Visual studio.net 2008, 2010 & 2012:- 

Menu option:- Tools -> Options -> Text Editor -> All Languages -> Word Wrap

 

Shortcut option:- ctrl+E+W



Asp.net Page life cycle

clock July 12, 2013 09:53 by author Arvind

There are one common question in interview explain the Page life cycle in asp.net. Once a page is requested in browser than there are lots of thing happens behind the load that page within the browser. In which some of related to page life cycle and some are not related to asp.net page directly. Eg. There is something happen like application life cycle and life cycle on the IIS before actual page life cycle started. So here I am not discussing the application and IIS related life cycle. My intention is aps.net page life cycle.

            There are also two different thing conflict each other and lots of confusion the page life cycle and page life cycle event. Both are different thing. In general page goes in different stages at the time of load in browser that know as page life cycle and the event fired in different page life stages are know as page life cycle event.

            First I am discussing the page life cycle stage.

1.Page Request-The page request the first stage of page life cycle or you can say just before the page life cycle begins at the time of is requested by a user, ASP.NET determines whether the page needs to be parsed and compiled or whether a cached version of the page can be sent in response without running the page.

2.Start-In the page start stage main the Request, Response, IsPostBack and UICulture property are set. It is mainly decide the page request is a post back or new request.

3.Initialization- In this stage the controls on the page are available and set the id property of controls. So in this stage we can get control of the page.

4.Load- This is most useful stage of page life in which controls loaded information from viewstate and control states.

5.Validation- IsValid propery can be check within this stage for validating the controls on the page.

6.Postback event handling:-In this stage determine if the request is a postback than control event handlers are called for server controls.

7.Rendering-In this stage view state is saved for the page and all controls.

8.Unload-In this stage page properties such as Response and Request are unloaded and cleanup is performed and finally sent to a client.

 

Second thing discussing the page life cycle event, there are all page life cycle have some events. These following are most common page life cycle event.

1.PreInt:- It comes in Start life cycle. This event fired after the start stage is complete and before the initialization stage begins. In this event set the IsPostBack, IsCallback and IsCrossPagePostBack and also load master page theme.
2.Init – It comes in Initialization stage and fired after all controls initialized. First fired all individual controls Init event than Page Init.

3.InitComplete:- It comes in Initialization stage.In this event view state tracking is turned on.

4.PreLoad:- It comes in Initialization stage.PreLoad occurs after the loads view state for itself and all controls and before the post back data is loaded in the controls.

5.Load:- It comes in Load stage. In this all the page controls information loaded and use for establish database connection and other data manipulation works.

6.Control Event:- It comes in Validation stage. In this event handle the control’s event eg button’s click.In a postback request, if the page contains validator controls, check the IsValid property of the Page and of individual validation controls before performing any processing.

7.LoadComplete:-It comes in Postback event handling stage. In this event all controls on the page loaded.

8.PreRender:- It comes in Postback event handling stage.Use the event to make final changes to the contents of the page.

9.PreRenderComplete:- It comes in Postback event handling stage.Raised after each data bound control whose DataSourceID property is set calls its DataBind method.

10.SaveStateComplete:- It comes in Postback event handling stage.ViewState has been saved.

11.Render:-It comes in Rendering stage.This is not an event actually it is stage of processing. In which render methods and wire up controls.

11.Unload:- It comes in Unload stage. It is final event where all closing open database connection and clean up work and sent to client. If you attempt to call a method such as the Response.Write method, the page will throw an exception.

  

Reference link- http://msdn.microsoft.com/en-us/library/ms178472(v=vs.100).aspx



Write T-Sql Query to fetch the Employee Name with his boss name

clock June 11, 2013 17:26 by author Arvind

There are very common interview question regarding T-Sql query that is write the select statement to fetch the employee name with his boss name. I am trying to explain this query with using sample with two different scenarios.

--Scenario 1, In which employee id and boss id stored in different table

Create table #Emptbl

(

       EmpId int,

       EmpName varchar(100)

)

 

Create table #Bosstbl

(

       EmpId int ,

       BossId int   

)

--Insert some record here of employee

Insert into #Emptbl Values(1,'Arvind kumar')

Insert into #Emptbl Values(2,'John Smith')

Insert into #Emptbl Values(3,'Ranjan Singh')

Insert into #Emptbl Values(4,'Soni Gupta')

Insert into #Emptbl Values(5,'Priya R')

Insert into #Emptbl Values(6,'S asthna')

Insert into #Emptbl Values(7,'Simona')

--here insert boss id of employee

Insert into #bosstbl values(1, null)

Insert into #bosstbl values(2,1)

Insert into #bosstbl values(3,1)

Insert into #bosstbl values(4,2)

Insert into #bosstbl values(5,3)

Insert into #bosstbl values(6,2)

Insert into #bosstbl values(7,5)

 

--Get Employee name with his boss usin join between two table

Select a.EmpName As EmployeeName, c.EmpName As BossName From #Emptbl a Inner join #bosstbl b on a.EmpId = b.EmpId

                                  Inner join #Emptbl c on b.bossid = c.empid

 

drop table #Emptbl

drop table #Bosstbl

 

 

Output of Scenario 1 is as follow

--Scenario 2, In which employeeid and bossid stored in same table

Create table #Emptbl

(

       EmpId int,

       EmpName varchar(100),

       BossId int   

)

 

--Insert record first here

Insert into #Emptbl Values(1,'Arvind kumar',null)

Insert into #Emptbl Values(2,'John Smith',1)

Insert into #Emptbl Values(3,'Ranjan Singh',1)

Insert into #Emptbl Values(4,'Soni Gupta',2)

Insert into #Emptbl Values(5,'Priya R',3)

Insert into #Emptbl Values(6,'S asthna',2)

Insert into #Emptbl Values(7,'Simona',5)

 

Select a.empid, a.EmpName As EmployeeName, b.EmpName As Bossname From #Emptbl a Left join #Emptbl b on a.BossId = b.EmpId

 

 

Drop table #Emptbl

 

 

Output of Scenario 2 is as follows



RecentComments

Comment RSS

Sign In

Akumar