FAQ

1.architecture of .net
2.What is IL and CLR
3.Collections -Arrary, array list , hash array
4.How can we handile exception in .Net and SQL server--
5.Tell me simple steps to bind a grid using sql connection
6.State management-
7.Diff b/w Session and application memory
8.How to swap 2 values with out using temp
9.Overriding and Overloading
10. Dataset and reader...
11. Can we run all versions of .Net applications in same server.
12.One array size is 100, i have entered 99 records(Numbers from 1 to 100) in that array, how can i find missed value.
13.I have 2 arrays with diff size, i want to dispaly matched records and unmatched recores.
14..Net is plat form independent.
15. Tell me what are the namespaces we use to work with ajax. (Ans: System.Web.Extensions, System.Web.Extensions.Design)
16. Can my application can have more that 1 .config files...YES
17.Can i have more that one DLL with same name in my system. Ans)Yes in GAC it is possible
18.Diff b/w EXE and DLL
19.How can we improve asp.net application erformance.
22. architecture of u r current application. how can u support u r architecture is sutable for u r aplication.
23.how can we call webservices.

Tell u have a knowledge on workflows.....which u have..
1.what is workflow.
2.what is the use of workflows

Diff b/w function and SP in SQL
New features in sql 2005 that 2000
what is index in sql and advantages, diss adva?


How to get inserted row identity value from Database in SP?
Ans)
If you are using SQL Server 7.0, simply change the line in the stored procedure from ...

SELECT NEWID = SCOPE_IDENTITY()

... to ...

SELECT NEWID = @@IDENTITY

SQL

ALTER TABLE Table_1 ALTER COLUMN test1 nchar(20)
------------------------------
Declare @NewInt
intexec DebugA 5,@NewInt OUTPUT
Select @NewInt
-------------------
CREATE TABLE tt (EntryID uniqueidentifier DEFAULT NewID())
select EntryID from ttt
CREATE TABLE ttt (EntryID uniqueidentifier DEFAULT NewSequentialID())
INSERT INTO tt DEFAULT VALUES
-------------------------------------------------
--Table structure

CREATE TABLE dbo.GUIDTable2
(
GUIDCol uniqueidentifier NOT NULL PRIMARY KEY DEFAULT (NEWID()),
OtherColumn varchar(25) NOT NULL
)
GO
INSERT INTO dbo.GUIDTable2 (OtherColumn) VALUES ('Performance tuning')
select * from GUIDTable2
------------------------------------------------
Auto Increment
-----------------
Create TABLE tbl_Processes
(
ProcessID int IDENTITY(1,1),
ProcessName nvarchar (100),
Active bit,
CONSTRAINT pk_PersonID PRIMARY KEY (ProcessID)
)
-----------------------------------------------------

Create TABLE new_employees
(
id_num int IDENTITY(1,1),
fname varchar (20)
)
--------------------------------
CREATE TABLE Persons
(
P_Id int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
----------------------------
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
)
----------------------------------------------
CREATE TABLE Customer
(SID integer PRIMARY KEY,
Last_Name varchar(30),
First_Name varchar(30));

-----------------------------------
CREATE TABLE ORDERS
(Order_ID integer primary key,
Order_Date datetime,
Customer_SID integer references CUSTOMER(SID),
Amount double);

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

SELECT *
FROM TableName E1
WHERE (N =
(SELECT COUNT(DISTINCT (E2.sal))
FROM TableName E2
WHERE E2.sal >= E1.sal))


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

sp_helptext GetUniqueAppID-------We can view the sp
---------------------------------
SQL Server Installation
-----------------------
http://www.functionx.com/sqlserver/Lesson01.htm
http://www.exforsys.com/tutorials/sql-server-2005/getting-started-with-sql-server-2005/1.html



http://www.ss64.com/sql/

Copying a Database from SQL Server 2005 to SQL Server 2008 using the Copy Database Wizard

http://www.packtpub.com/article/copying-database-sql-2008-copy-database-wizard

http://blog.sqlauthority.com/

Reporting Services:
--------------------
http://msdn.microsoft.com/en-us/magazine/cc188691.aspx

http://www.microsoft.com/technet/prodtechnol/sql/2005/2005ssrs.mspx

http://www.simple-talk.com/sql/learn-sql-server/beginning-sql-server-2005-reporting-services-part-1/

http://www.dotnetheaven.com/UploadFile/ursbgiri/106012007065515AM/1.aspx

Reporting
------------
http://www.codeproject.com/KB/database/MergeReplication.aspx
http://www.codeproject.com/KB/reporting-services/RecursiveData.aspx



GOOD:
=====
SELECT max( mark ) FROM `student`
--------------------
SELECT * FROM Product where Product_price = (
SELECT MAX(Product_price)FROM Product WHERE Product_price < (SELECT MAX(Product_price) FROM Product) ) -------------------- SELECT * FROM Product E1 WHERE(3 =(SELECT COUNT(DISTINCT (E2.Product_price))FROM Product E2 WHERE E2.Product_price >= E1.Product_price))
----------------------------------------------------------------------------------------------------

Renaming a Column
------------------
sp_rename 'TableName.ColumnName', 'NewColumnName', 'COLUMN'

Deleting a Column
-----------------
ALTER TABLE TableName
DROP COLUMN ColumnName

DATE TIME
---------
http://www.sqljunkies.ddj.com/Article/6676BEAE-1967-402D-9578-9A1C7FD826E5.scuk

Retrieve Current Date Time in SQL Server
----------------------------------------
SELECT {fn NOW()}
GO
SELECT GETDATE()
GO
SELECT CURRENT_TIMESTAMP
GO


SELECT CONVERT(VARCHAR(10),GETDATE(),111)
--------------------------------
DateTime dtDOB = Convert.ToDateTime(Request.Form["TextBox1"].ToString());
DateTime dtNow = DateTime.Now;
//TimeSpan k=dtNow.Subtract(dtDOB);

if ((dtNow.Subtract(dtDOB).TotalDays / 365) < 18)
{
Response.Write("Sorry");
}
else
{
Response.Write("Success");
}
-------------------------------------------------
CREATE TABLE MyTable
(
ID TINYINT NOT NULL IDENTITY (1, 1),
FirstCol TINYINT NOT NULL,
SecondCol TINYINT NOT NULL,
ThirdCol TINYINT NOT NULL,
ComputedCol AS (FirstCol+SecondCol)*ThirdCol
) ON [PRIMARY]
GO

INSERT INTO MyTable
([FirstCol],[SecondCol] ,[ThirdCol]) values (1,2,3)
GO

SELECT *
FROM MyTable
GO
----------------------------------------------------\\

http://publib.boulder.ibm.com/infocenter/rbhelp/v6r3/index.jsp?topic=/com.ibm.redbrick.doc6.3/sqlrg/sqlrg67.htm
-----------------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[VALIDATEUSER]
@UserName VARCHAR(100),
@Password VARCHAR(100),
@Result INT OUT
AS
BEGIN
DECLARE @Cnt AS INT

SELECT @Cnt = COUNT(*) FROM USERS WHERE USERNAME=@UserName AND PASSWORD=@Password
IF(@Cnt=0)
BEGIN

SELECT @Cnt = COUNT(*) FROM USERS WHERE USERNAME=@UserName
IF(@Cnt=0)
BEGIN
SET @Result = -1
END
ELSE
BEGIN
SET @Result = 0
END

END
ELSE
BEGIN
SELECT @Result=USERID FROM USERS WHERE USERNAME=@UserName AND PASSWORD=@Password
END
END

Upload Document To MOSS 2007

SPSite siteCollection = null;
SPWeb topLevelSite = null;
string strDocLibraryLink = System.Configuration.ConfigurationManager.AppSettings["SPDocumentLibrary"].ToString();
//"http://#/Corporate/DealDesk/Documents/";
siteCollection = new SPSite(strDocLibraryLink);
topLevelSite = siteCollection.AllWebs[ConfigurationManager.AppSettings["TopLevelSite"].ToString()];
// siteCollection.AllWebs["Corporate/DealDesk"];

topLevelSite.AllowUnsafeUpdates = true;
SPList list = topLevelSite.Lists["Documents"];
//foreach (SPListItem item in list.Items)
//{
// if (Convert.ToString(item["ID"]).Equals(strUARID))
// {
SPFolder objFolder = topLevelSite.GetFolder("documents");
byte[] bufDoc = null;
string strFileName = Path.GetFileName(filename.FileName);
strMossFileName = strFileName;
int nLen = filename.ContentLength;
bufDoc = new byte[nLen];
Stream oStream = filename.InputStream;
oStream.Read(bufDoc, 0, nLen);
// SPFile file = objFolder.Files.Add(strFileName, bufDoc, true);
System.GC.AddMemoryPressure(200024);
SPFile file = objFolder.Files.Add(strFileName, oStream, true);
Response.Write("File Name:" + file);
Response.Write("File ID:" + file.Item["ID"].ToString());
//strFileName = file;
file.Update();

Note: In K2 we should not upload document, tr .net only we have upload and then in k2 we have update Metadata only tr file name as a identity.

Bind Data with dataset

SqlConnection con=new SqlConnection("user id=sa;password=;database=northwind;data source=compaq12");
SqlDataAdapter da=new SqlDataAdapter("select * from orders order by "+ str,con);
DataSet ds=new DataSet();
da.Fill(ds,"orders");
DataGrid1.DataSource=ds.Tables["orders"];
DataGrid1.DataBind();

Bind data with datareader

void Page_Load(Object sender, EventArgs e)
{
SqlConnection myConnection;
SqlCommand myCommand;
SqlDataReader myDataReader;
myConnection = new SqlConnection(ConfigurationSettings.AppSettings["strConn"]); myConnection.Open();
//prepare sql statements
myCommand = new SqlCommand("SELECT TOP 10 * FROM EMPLOYEE", myConnection);
myDataReader = myCommand.ExecuteReader();
while (myDataReader.Read())
{
Response.Write(myDataReader["fname"]);
//Spacing
Response.Write(" ");
Response.Write(myDataReader["minit"]);
//Spacing
Response.Write(" ");
Response.Write(myDataReader["lname"]);
//New Line
Response.Write("
");
}
//cleanup objects
myDataReader.Close();
myConnection.Close();
}

DATABASE Tuning

1) Return Multiple Resultsets
2) Connection Pooling and Object Pooling
3) Use SqlDataReader Instead of Dataset wherever it is possible
4) Keep Your Datasets Lean
5) Avoid Inefficient queries
6) Unnecessary round trips
7) Too many open connections
8) Avoid Transaction misuse
9) Avoid Over Normalized tables
10) Reduce Serialization
11) Do Not Use CommandBuilder at Run Time
12) Use Stored Procedures Whenever Possible
13) Avoid Auto-Generated Commands
14) Use Sequential Access as Often as Possible

Asp.Net Web applications Performance tuning

1) Turn off Tracing unless until required
trace enabled="false"
2) Turn off Session State, if not required
<@%Page EnableSessionState="false"%>
3) Disable View State of a Page if possible
4) Set debug=false in web.config
5) Avoid Response.Redirect
5. A) To reduce CLR Exceptions count, Use Response.Redirect (".aspx", false) instead of response.redirect (".aspx").
6) Use the String builder to concatenate string
7) Avoid throwing exceptions
8) Use Finally Method to kill resources
9) Use Client Side Scripts for validations
10) Avoid unnecessary round trips to the server
11) Use Page.ISPostBack
12) Include Return Statements with in the Function/Method
13) Use Foreach loop instead of For loop for String Iteration
14) Avoid Unnecessary Indirection
15) Use "ArrayLists" in place of arrays
16) Always check Page.IsValid when using Validator Controls
17) Use Paging
18) Store your content by using caching
19) Use low cost authentication
20) Minimize the number of web server controls
21) Avoid using unmanaged code
22) Avoid making frequent calls across processes
23) Cleaning Up Style Sheets and Script Files
24) Design with ValueTypes
25) Minimize assemblies
26) Encode Using ASCII When You Don't Need UTF
27) Avoid Recursive Functions / Nested Loops
28) Minimize the Use of Format ()
29) Place StyleSheets into the Header
30) Put Scripts to the end of Document
31) Make JavaScript and CSS External