diffrence between web application and website

Web Application

1. If we create any class files / functions those will be placed anywhere in the applications folder structure and it is precomplied into one single DLL.

2. In web application we have chance of select only one programming language during creation of project either C# or VB.NET.

3. Whenever we create Web Application those will automatically create project files (.csproj or .vbproj).

4. We need to pre-compile the site before deployment.

5. If we want to deploy web application project we need to deploy only .aspx pages there is no need to deploy code behind files because the pre-compiled dll will contains these details.

6. If we make small change in one page we need to re-compile the entire sites.

WebSite

1. If we create any class files/functions those will be placed in ASP.NET folder (App_Code folder) and it's compiled into several DLLs (assemblies) at runtime.

2. In website we can create pages in multi programming languages that means we can create one page code in C# and another page code in vb.net.

3. Web Sites won’t create any .csproj/.vbproj files in project

4. No need to recompile the site before deployment.

5. We need to deploy both .aspx file and code behind file.

6. If we make any code changes those files only will upload there is no need to re-compile entire site 

bulk inset in sql server

Create CSV file in drive C: with name sweetest. text with the following content. The location of the file is C:\csvtest.txt
1,James,Smith,19750101
2,Meggie,Smith,19790122
3,Robert,Smith,20071101
4,Alex,Smith,20040202

Now run following script to load all the data from CSV to database table. If there is any error in any row it will be not inserted but other rows will be inserted.

BULK
INSERT CSVTest
FROM 'c:\csvtest.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO
--Check the content of the table.
SELECT *
FROM CSVTest
GO
--Drop the table to clean up database.
DROP TABLE CSVTest
GO

Find Stored Procedure name based on table name in sql server

Find Stored Procedure name based on table name in SQL SERVER


There are few query copy and paste in sql editor and change the name of table :


----Option 1 SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '%tablename%'

----Option 2
SELECT DISTINCT o.name, o.xtype
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
WHERE c.TEXT LIKE '%tablename%'

Sql Offset

SELECT *
  FROM [fortune].[dbo].[TblStates] order by StateID desc Offset
  2 Rows fetch Next 2 Rows only;

This is important when the data is large and we need few record on click .

Note : offset work only in sql server 2012 and above version on change of offset number the result will change .

WITH ShowMessage(STATEMENT, LENGTH)AS
(
SELECT STATEMENT = CAST('I Am ' AS VARCHAR(300)), LEN('I Am') UNION ALL SELECT
CAST(STATEMENT + 'Sandeep! ' AS VARCHAR(300)) , LEN(STATEMENT)+ 9 FROM ShowMessage WHERE LENGTH < 300
)
SELECT STATEMENT, LENGTH FROM ShowMessage

Above we are keeping the record in tempoarary table that is showmessage  and in the last query we are fetching record from temp table.

read excel file in sql server

Here the code to read excel file from sql server

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=D:\sheet(3-12-2013).xls','SELECT *  FROM [Sheet1$]')

Modify database path

but it gives while excuting query to overcome the problem Excute below syntex:

sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;

and then

sp_configure 'show advanced options', 1;
RECONFIGURE;

Now the above query will work..

Comma seprated value in multiple rows sql server

Above is my output after executing the query but i want to to separated  row means multiple row splitting the taskid column by ' , '.

After so many searches and some modification i able to create a query which is very helpful for me -


SELECT  
     Split.a.value('.', 'VARCHAR(100)') AS taskid,  A.invoiceid,A.Rate
 FROM  (SELECT 
         CAST ('<M>' + REPLACE([taskids], ',', '</M><M>') + '</M>' AS XML) AS String , [invoiceid],[Rate]
     FROM  SMOInvoiceHistory) AS A CROSS APPLY String.nodes ('/M') AS Split(a)

Happy Coding...........