Thursday, September 10, 2009

.Net-Insert Excel Data into Database

// Connection String to Excel Workbook
string excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Empinfo.xls;Extended Properties=""Excel 8.0;HDR=YES;""";
// Create Connection to Excel Workbook
using (OleDbConnection connection = new OleDbConnection(excelConnectionString))
{
OleDbCommand command = new OleDbCommand("Select * FROM [Sheet1$]", connection);
connection.Open();
// Create DbDataReader to Data Worksheet
using (OleDbDataReader dr = command.ExecuteReader())
{
// SQL Server Connection String
string sqlConnectionString = "Data Source=SCSHYD-067\\SQLEXPRESS;Initial Catalog=test;Integrated Security=True";
// Bulk Copy to SQL Server
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
{
bulkCopy.DestinationTableName = "INA_EIS_EMOF_EMEM_OFFL";
bulkCopy.WriteToServer(dr);
}
}
}

SQl-Insert Excel Data into Database

/****** Object: StoredProcedure [dbo].[fs_prog_rt_InsertTransaction] Script Date: 09/10/2009 19:47:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[fs_prog_rt_InsertTransaction]
@xmlData XML,
@ErrorMessage varchar(500) OUTPUT
AS
BEGIN
BEGIN TRY
SET NOCOUNT ON;

DECLARE @SubId VARCHAR(20)
DECLARE @EntryType VARCHAR(50)
DECLARE @ContainerCode VARCHAR(20)
DECLARE @EquipmentId VARCHAR(20)
DECLARE @ProcessCode VARCHAR(20)
DECLARE @TestTypeCode VARCHAR(20)
DECLARE @CreatedByUserId VARCHAR(50)
DECLARE @CreatedTime VARCHAR(50)
DECLARE @idoc INT
DECLARE @LogId INT
DECLARE @return_Value INT
DECLARE @ValidationErrorMessage VARCHAR(500)

EXEC sp_xml_preparedocument @idoc OUTPUT, @xmlData

SELECT * into #temp_table FROM OPENXML (@idoc,'/NewDataSet/RT_Transaction',0)
WITH (LogId int 'LogId',
SubId varchar(50) 'SubId',
EntryType Varchar(50) 'EntryType',
ContainerCode varchar(50) 'ContainerCode',
CreatedByUserId varchar(10) 'CreatedByUserId',
ProcessCode varchar(100) 'ProcessCode',
EquipmentId varchar(50) 'EquipmentId',
TestTypeCode varchar(50) 'TestTypeCode',
CreatedTime varchar(100) 'CreatedTime')

EXEC sp_xml_removedocument @idoc

CREATE TABLE #temp_Errortable (
LogId int,
SubId varchar(50),EntryType Varchar(50),ContainerCode varchar(50),CreatedByUserId varchar(10),ProcessCode varchar(100),EquipmentId varchar(50),TestTypeCode varchar(50),CreatedTime varchar(100),ErrorMessage Varchar(250))




SELECT TOP 1 @LogId = [LOGID] FROM #temp_table

WHILE (ISNULL(@LogId,0) <> 0)
BEGIN
SELECT
@SubId = SubID,
@EntryType = EntryType,
@ContainerCode = ContainerCode,
@EquipmentId = EquipmentId,
@ProcessCode = ProcessCode,
@TestTypeCode = TestTypeCode,
@CreatedByUserId = CreatedByUserId,
@CreatedTime = CreatedTime
FROM #temp_table WHERE [LOGID] = @LogId

IF (@EntryType = 'Cart/Box')
BEGIN
EXEC @return_Value = dbo.RT_ValidateAndInsertCart @SubId,@EntryType,@ContainerCode,@CreatedByUserId,@ValidationErrorMessage OUTPUT,@ErrorMessage OUTPUT
END
ELSE
BEGIN
EXEC @return_Value = dbo.RT_ValidateAndInsertEquipment @SubId,@EntryType,@EquipmentId,@TestTypeCode,@ProcessCode,@CreatedByUserId,@ValidationErrorMessage OUTPUT,@ErrorMessage OUTPUT
END
IF (@ValidationErrorMessage <> '')
BEGIN
-- INSERT INTO #temp_Errortable SELECT LogId,SubId,EntryType,ContainerCode,CreatedByUserId,ProcessCode,EquipmentId,TestTypeCode,CreatedTime,@ValidationErrorMessage AS ErrorMessage FROM #temp_table Where LogId=@LogId
INSERT INTO #temp_Errortable Values(@LogId,@SubId,@EntryType,@ContainerCode,@CreatedByUserId,@ProcessCode,@EquipmentId,@TestTypeCode,@CreatedTime,@ValidationErrorMessage)
END

DELETE #temp_table WHERE [LOGID] = @LogId
set @LogId = 0
SELECT TOP 1 @LogId=[LOGID] FROM #temp_table
END

DROP TABLE #temp_table

Select * From #temp_Errortable
DROP TABLE #temp_Errortable
SET NOCOUNT OFF
END TRY
BEGIN CATCH
BEGIN
SELECT @ErrorMessage=ERROR_MESSAGE()
END
END CATCH
END

Monday, September 7, 2009

String Array

strEmployeeName="Tester(Test)";
///
/// It will return Full name and FQN
///

///
///
public string[] SplitEmployeeNameFQN(string strEmployeeName)
{
string[] strInputValue = strEmployeeName.Split(';');
string[] arrReturn = new string[2];
//string employeeName = string.Empty;
string strFQN = string.Empty;
for (int i = 0; i < strInputValue.Length; i++)
{
if (!(strInputValue[i] == ""))
{
int startIndex = strInputValue[i].IndexOf("(");
strFQN = strInputValue[i].Substring(startIndex + 1);
strFQN = strFQN.Replace(")", "");
arrReturn[0] += strFQN.Trim() + ";";
arrReturn[1] += strInputValue[i].Remove(startIndex).Trim() + ";";
}
}

return arrReturn;
}

Monday, August 24, 2009

SQL Search

CREATE PROCEDURE [dbo].[GetSparePartsECRETS]
(
@pnEcretsNum INT = NULL
,@pnvcEcretsTitle NVARCHAR(100) = NULL
,@pnvcOriginatorID NVARCHAR(50) = NULL
)
AS

BEGIN

/*SET NOCOUNT TO ON AND NO LONGER DISPLAY THE COUNT MESSAGE*/
SET NOCOUNT ON

/*START THE TRY BLOCK*/
BEGIN TRY
IF @pnEcretsNum = '' SET @pnEcretsNum = NULL
IF @pnvcEcretsTitle = '' SET @pnvcEcretsTitle = NULL
IF @pnvcOriginatorID = '' SET @pnvcOriginatorID = NULL

SELECT
TBLE.ECRETSID,
TBLE.[Title],
[TBEI].[FirstName] +' '+COALESCE([TBEI].[LastName],'') AS Originator
FROM dbo.tblECRETS TBLE,
dbo.TblEmployeeInfo TBEI
WHERE (TBLE.ECRETSID = @pnEcretsNum OR @pnEcretsNum IS NULL)
AND (TBLE.Title = @pnvcEcretsTitle OR @pnvcEcretsTitle IS NULL)
AND (TBLE.OriginatorID = @pnvcOriginatorID OR @pnvcOriginatorID IS NULL)
AND TBLE.OriginatorID = TBEI.FQN

END TRY
BEGIN CATCH
IF @@ERROR != 0
BEGIN
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_MESSAGE() AS ErrorMessage
END
END CATCH

/*Reset SET NOCOUNT to OFF*/
SET NOCOUNT OFF
END

Thursday, July 23, 2009

Web Services

1. Which attribute is used in order that the method can be used as WebService ?
WebMethod attribute has to be specified in order that the method and property can be treated as WebService.
2. Do webservice have state ?
Twist :- How can we maintain State in Webservices ?
Webservices as such do not have any mechanism by which they can maintain state.
3. How to Authentication for Web Services
using SOAP headers
4. HOW TO: Pass Current Credentials to an ASP.NET Web Service
myProxy.Credentials = System.Net.CredentialCache.DefaultCredentials
OR
localhost.MyWebService myService = new localhost.MyWebService();
System.Net.CredentialCache myCredentials = new System.Net.CredentialCache();
NetworkCredential netCred = new NetworkCredential("UserName", "Password");
myCredentials.Add(new Uri(myService.Url), "Basic", netCred);
myService.Credentials = myCredentials;
---------
DataSet dsPartDetails = new DataSet();
Item[] arrPartDetails = null;
arrPartDetails = objSrchPartsCommonMethods.GetPartService(vendorPartNum, manPartNum);
dsPartDetails = objSrchPartsCommonMethods.ConvertArrayToDataSet(arrPartDetails);
----------------------------------


public FirstSolarMasterDataServices.Item[] GetPartService(string partNumber, string manPartNum)
{
try
{
FirstSolarMasterDataServices.MasterData objFirstSolarERPWebService = new FirstSolarMasterDataServices.MasterData();
FirstSolarMasterDataServices.Item[] arrParts;

objFirstSolarERPWebService = ImpersonateDevUser();

//Calling Web services menthods
arrParts = objFirstSolarERPWebService.GetItems(ConfigurationManager.AppSettings["CompanyId"].ToString(), "", partNumber, "","",manPartNum);
return arrParts;
}
catch
{
throw;
}
//code used at Onsite
//comment this code at ODC

//finally
//{
// if (m_Ctx != null)
// {
// m_Ctx.Undo();
// }
//}
}

private FirstSolarMasterDataServices.MasterData ImpersonateDevUser()
{
FirstSolarMasterDataServices.MasterData objFirstSolarErpModifyWebService = new FirstSolarMasterDataServices.MasterData();

try
{
//code used at ODC
//comment this code at Onsite
//*************************************

NetworkCredential nwCredentials = new NetworkCredential();

nwCredentials.UserName = ConfigurationManager.AppSettings["NWUserName"].ToString();
nwCredentials.Password = ConfigurationManager.AppSettings["NWPassword"].ToString();
nwCredentials.Domain = ConfigurationManager.AppSettings["NWDomain"].ToString();

//Assignign credentials to web services
objFirstSolarErpModifyWebService.Credentials = new NetworkCredential(nwCredentials.UserName, nwCredentials.Password, nwCredentials.Domain);

//*************************************


//code used at Onsite
//comment this code at ODC
//*************************************

//m_Ctx = WindowsIdentity.GetCurrent().Impersonate();
//RevertToSelf();

//objFirstSolarERPModifyWebService.Url = ModifyWebService;

// //We have to authenticate ourselves with the web service.
//objFirstSolarERPModifyWebService.PreAuthenticate = true;
//objFirstSolarERPModifyWebService.Credentials = System.Net.CredentialCache.DefaultCredentials;

//*************************************
}
catch
{
throw;
}

return objFirstSolarErpModifyWebService;
}
------------------
Covert Arrat to Dataset
---
public DataSet ConvertArrayToDataSet(Object[] objArray)
{
XmlTextReader reader;
DataSet arrayDataSet = new DataSet();
MemoryStream memStream = new MemoryStream();
XmlTextWriter writer = new XmlTextWriter(memStream, System.Text.Encoding.UTF8);
XmlSerializer serializer = new XmlSerializer(objArray.GetType());
try
{
serializer.Serialize(writer, objArray);
memStream.Position = 0;
reader = new XmlTextReader(memStream);
arrayDataSet.ReadXml(reader);

return arrayDataSet;
}
catch (Exception ex)
{
throw;
}
}

Tuesday, July 21, 2009

Use Data View

protected void BindEmployee()
{
ConnectDB objConnectDB = null;
SqlConnection objSqlConnection = null;
SqlCommand objSqlCommand = null;
SqlDataAdapter objSqlDataAdapter = null;
DataSet objDataSet = null;
try
{
objConnectDB = new ConnectDB();
objSqlConnection = objConnectDB.getConnection();
objDataSet = new DataSet();
//string strSQL = "SELECT GUID,FirstName+' '+coalesce(LastName,'') as FullName from TblEmployeeInfo where active=1 order by FirstName+' '+coalesce(LastName,'')";
string strSQL = "SELECT GUID, FirstName+' '+coalesce(LastName,'') as FullName, REPLACE(FirstName+' '+coalesce(LastName,''),'''','\\''') as FullNameRead from TblEmployeeInfo where active=1 order by FirstName+' '+coalesce(LastName,'')";

objSqlCommand = new SqlCommand(strSQL, objSqlConnection);
objSqlDataAdapter = new SqlDataAdapter(objSqlCommand);

objSqlDataAdapter.Fill(objDataSet);

if (txtFilter.Text == "")
{
grdvwEmployee.DataSource = objDataSet.Tables[0];
}
else
{
//DataView dv = new DataView(objDataSet.Tables[0], "FullName like '%" + txtFilter.Text.Trim() + "%'", "", DataViewRowState.CurrentRows);
DataView dv = new DataView(objDataSet.Tables[0], "FullName like '%" + txtFilter.Text.Trim().Replace("'", "") + "%'", "", DataViewRowState.CurrentRows);
grdvwEmployee.DataSource = dv;
}


grdvwEmployee.DataBind();


}
catch (Exception ex)
{
lblMsg.Text = "Error: " + ex.Message;
}
finally
{
objSqlCommand.Dispose();
objSqlDataAdapter.Dispose();
objDataSet.Dispose();
}
}

Monday, July 20, 2009

Get Selected result with Comma separate

CREATE TABLE dbo.tblItems
(
ItemID int NOT NULL IDENTITY (1, 1),
ItemName nvarchar(50) NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.tblItems ADD CONSTRAINT
PK_Table_1 PRIMARY KEY CLUSTERED
( ItemID )
GO



Insert into dbo.tblItems (ItemName) Values ('Item1')
Insert into dbo.tblItems (ItemName) Values ('Item2')
Insert into dbo.tblItems (ItemName) Values ('Item3')
Insert into dbo.tblItems (ItemName) Values ('Item4')
Go


Declare @m_ItemsList nvarchar(2000)
Select @m_ItemsList=''
Select @m_ItemsList=@m_ItemsList+ItemName+',' from tblItems
Select Left(@m_ItemsList,Len(@m_ItemsList)-1)

Tuesday, July 14, 2009

Monday, July 13, 2009

Javascript with DIV for tool tip

Javascript with DIV for tool tip












style="cursor: hand; ime-mode: active; color: Red">[?]


*Part Type:

*Preventative Maintenance
Part
:
onmouseover="Div3.style.visibility='visible'" />

Sunday, July 12, 2009

Dot Net FAQs

ExecuteNonQuery-returns number of rows effected
ExecuteReader-It will return SQLDatareader(Set of records)
ExecuteScalar-It will return last inserted row value.

GAC- C:\WINDOWS\assembly
ILDASM.EXE- C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin
Q)IS there any way to restrict the Developer to delete a assembly? How can i do? pros and cons?
Ans.Yes,By placing the assembly in to GAC.

System.GC.AddMemoryPressure(200024);
System.GC.RemoveMemoryPressure(200024);
This statements will be usefull while uploading large files from .Net to server
System.GC.Collect()- forces garbage collector to run

1) What are situations when you register .NET assembly in GAC ?

2) What’s the difference between Cache object and application object ?

The main difference between the Cache and Application objects is that the Cache object
provides cache-specific features, such as dependencies and expiration policies.

3) When we use Cache object and when we use application object?

4) How can get access to cache object ?

You can get a reference
to the Cache object by using the Cache property of the HttpContext class

5) Where do you specify session state mode in ASP.NET ?

6)What are benefits and limitations of using Cookies?

7)How u define the cookie in ASP.Net?
Request.Cookies.Add(New HttpCookie(“name”, “user1”))

8)How cross page posting can be achieved in ASP.Net?
PostBackUrl="~/nextpage.aspx" Text="Post to nextpage" />

9)How delegates used for event handling?

10)Can you prevent a class from overriding ?

11)What is the significance of Finalize method in .NET?

12)When we required System.StringBuilder classes?

13)How can we skip some set of code from the Page PostBack?

14)Where is ViewState information stored ?
In HTML Hidden Fields.


15)How can you enable automatic paging in DataGrid ?


16)What is the method to customize columns in DataGrid?
Use the template column.

17)What are major events in GLOBAL.ASAX file ?What order they are triggered ?

18)where do we enable tracing ?
<%@ Page Trace="true" %>


19) How to kill a session in asp.net?

Session.abandon

20) How can I track event in checkbox which is one of the columns of a datagrid ?

21) What is difference between Constant and readonly properties?

get accessor in property implemantation.

22) Is structure can be inherited?

23) what is difference between static variable and private variable?

24) What is use of virtual method in a base class?

25) When we require interface? when we require abstract?

26) what is use of out parameter in c#?

27) what is the scope of extern variable?

28) what is use of static constructor?

29) What is the difference between is and as?
The AS operator is used to perform certain type of conversion between compatible reference types.
IS checks if object is compatiable with a given type.

30) What is scope of access modifers like internal,protected?

31) what is the use of partial classes?

32) What is the use of “OverRides” and “Overridable” keywords ?
Overridable is used in parent class to indicate that a method can be overridden. Overrides
is used in the child class to indicate that you are overriding a method


33)In what instances you will declare a constructor to be private?
When we create a private constructor, we can not create object of the class directly from
a client. So you will use private constructors when you do not want instances of the class
to be created by any external client. Example UTILITY functions in project will have no
instance and be used with out creating instance, as creating instances of the class would
be waste of memory.

34)Can two catch blocks be executed?
No, once the proper catch section is executed the control goes finally to block. So there
will not be any scenarios in which multiple catch blocks will be executed.

35)What is impersonation in ASP.NET ?
By default, ASP.NET executes in the security context of a restricted user account on the
local machine. Sometimes you need to access network resources such as a file on a shared
drive, which requires additional permissions. One way to overcome this restriction is to
use impersonation. With impersonation, ASP.NET can execute the request using the
identity of the client who is making the request, or ASP.NET can impersonate a specific
account you specify in web.config.


36)Can you explain in brief how the ASP.NET authentication process works?
ASP.NET does not run by itself, it runs inside the process of IIS. So there are two
authentication layers which exist in ASP.NET system. First authentication happens at
the IIS level and then at the ASP.NET level depending on the WEB.CONFIG file.
Below is how the whole process works:-
v IIS first checks to make sure the incoming request comes from an IP address
that is allowed access to the domain. If not it denies the request.
v Next IIS performs its own user authentication if it is configured to do so. By
default IIS allows anonymous access, so requests are automatically
authenticated, but you can change this default on a per – application basis
with in IIS.
v If the request is passed to ASP.net with an authenticated user, ASP.net checks
to see whether impersonation is enabled. If impersonation is enabled, ASP.net
acts as though it were the authenticated user. If not ASP.net acts with its own
configured account.
v Finally the identity from step 3 is used to request resources from the operating
system. If ASP.net authentication can obtain all the necessary resources it
grants the users request otherwise it is denied. Resources can include much
more than just the ASP.net page itself you can also use .Net’s code access
security features to extend this authorization step to disk files, Registry keys
and other resources.

37)How can we check if all the validation control are valid
and proper ?
Using the Page.IsValid() property you can check whether all the validation are done


38)What are the two fundamental objects in ADO.NET ?
Datareader and Dataset are the two fundamental objects in ADO.NET.

39)What is the use of command objects ?
They are used to connect connection object to Datareader or dataset. Following are the
methods provided by command object :-
v ExecuteNonQuery :- Executes the command defined in the CommandText
property against the connection defined in the Connection property for a query
that does not return any row (an UPDATE, DELETE or INSERT). Returns
an Integer indicating the number of rows affected by the query.
v ExecuteReader :- Executes the command defined in the CommandText property
against the connection defined in the Connection property. Returns a "reader"
object that is connected to the resulting rowset within the database, allowing
the rows to be retrieved.
v ExecuteScalar :- Executes the command defined in the CommandText property
against the connection defined in the Connection property. Returns only
single value (effectively the first column of the first row of the resulting rowset)
any other returned columns and rows are discarded. It is fast and efficient
when only a "singleton" value is required


What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?
You can use Having Clause with the GROUP BY function in a query and WHERE
Clause is applied to each row before they are part of the GROUP BY function in a query.

(B) What is the difference between UNION and UNION ALL SQL syntax ?
UNION SQL syntax is used to select information from two tables. But it selects only
distinct records from both the table, while UNION ALL selects all records from both the
tables.



When listing product information you might want to draw the user's eye to products that
are out of stock. one way to accomplish this would be to change the back ground colour of those
rows with unit in stock value of 0.

The gridview offers a onRowDataBound event that fires once for each row after the row has been creted
and bound to corresponding record of data from the data source control.

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

Thursday, May 28, 2009

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.
Loading...