Stored Procedures Quick Reference [SQL Server 2005]

SCOPE_IDENTITY and @@IDENTITY return the last identity values that are generated in any table in the current session.


-Get Currtent Date-
GETDATE()

-GET UTC Date
GETUTCDATE()

-Get UTC DATE WITHOUT TIME
cast(floor(cast(GETDATE() as float)) as datetime)


When SET NOCOUNT is ON, the count is not returned. When SET NOCOUNT is OFF, the count is returned.


-Return a Parameter back-

@bnGroupId bigint OUT,

SET @bnGroupId = SCOPE_IDENTITY()


-SQL SURFACE to enable remote logging-

1) Select Start Menu>SQL Server 2005>Configuration Tools>Surface area Configuration
2) Click Serface area Connection
3) Select Using Both TCP/IP and named piped

Network Configuration
1)Select Start Menu>SQL Server 2005>Configuration Tools>Server Configuration Manager
2)Click Nenwork Configuration
3)Select Protocol for SQL Server
4)Enable TCP/IP

How to test Romtote Logging to SQL
SQLCMD -E -S CT-MCDDEVSQL\SQLEXPRESSCLIENT,2301


-The SQL EXIST Statement-

IF EXISTS ( SELECT * FROM [tblData]
WHERE MessageId]=@MessageId
AND [RecipId]=@vcRecipId
AND [TypeId]= @TypeId
AND @vcStatus = @vcPendingStatus
)
BEGIN
--query
END


-How to Catch Sql Errors-

DECLARE @vcErrorNumber varchar(10)-- hold the error number

BEGIN TRY
INSERT INTO [tblDestinationInfoClientInfo]/--------/

END TRY

BEGIN CATCH
SET @vcErrorNumber = ERROR_NUMBER()
-- primary key vialation error number
IF (@vcErrorNumber = '2627')
BEGIN
/--------------/
END
END CATCH


-Define Transactions-

BEGIN TRAN
COMMIT TRAN

ROLLBACK TRAN


-How to Retrieve All Specific Table Columns from SysTable-

SELECT [name] AS FieldName
FROM syscolumns
WHERE id = ( SELECT id
FROM sysobjects
WHERE type = 'U'
AND [NAME] = 'tblEmp')

--sysobjects table contains all tables information
SELECT id
FROM sysobjects
WHERE type = 'U'
AND [NAME] = 'tblEmp'
In SQL Server the is a unique identification number for every tables. So we retrieve specified User Defiened table ID.

--syscolumns table contains all tables column information
We can retrieve specific table Columns by passing specific table Id.

SELECT [name] AS FieldName
FROM syscolumns
WHERE id = /*Table Id*/


-Define a Cursor [Column Cursor]-

DECLARE @fieldname varchar(200)

DECLARE temptable_fields_cursor CURSOR FOR
SELECT [name] AS FieldName
FROM syscolumns
WHERE id = ( SELECT id
FROM sysobjects
WHERE type = 'U'
AND [NAME] = 'tblClientRelationships')

OPEN temptable_fields_cursor
FETCH NEXT FROM temptable_fields_cursor INTO @fieldname

WHILE @@FETCH_STATUS = 0
BEGIN
----implementation----------
--------------
FETCH NEXT FROM temptable_fields_cursor INTO @fieldname

END --End While--

CLOSE temptable_fields_cursor
DEALLOCATE temptable_fields_cursor


-Create & Execute a Dynamic Query-

SET @SqlQuery = 'Select * From Emp'
EXEC (@SqlQuery )


-Define SQL Paging for a .NET GridView.-

declare @nPageNum INT
declare @nPageSize INT

set @nPageNum=1
set @nPageSize=10;

Note:-
--Following Select statement result will be stored in to 'Search' variable and we should need to retrieve data from 'Search' in the next statement.
--We can query 'Search' only one time.

WITH Search AS
(
SELECT rownum, Records.[Name] as PersonName , Records.[Age] as PersonAge ,Records.[ID], Records.[Bdate]
FROM (SELECT ROW_NUMBER() OVER(ORDER BY ID,Bdate) AS rownum ,
dbo.Person.[Name] , dbo.Person.[Age] ,dbo.Person.[ID]
,dbo.Person.[Bdate] FROM dbo.Person)
AS Records
)

SELECT PersonName,PersonAge,Search.[ID],Search.[Bdate],(SELECT COUNT(*) FROM Search) AS RecordCount
FROM Search
WHERE rownum BETWEEN (@nPageNum-1)*@nPageSize+1 AND @nPageNum*@nPageSize
ORDER BY Search.[Bdate]


-SQL SERVER – 2005 Locking-

ROWLOCK
Use row-level locks when reading or modifying data.

PAGLOCK
Use page-level locks when reading or modifying data.

TABLOCK
Use a table lock when reading or modifying data.

DBLOCK
Use a database lock when reading or modifying data.

UPDLOCK
UPDLOCK reads data without blocking other readers, and update it later with the assurance that the data has not changed since last read.

XLOCK
Use exclusive locks instead of shared locks while reading a table, and use hold locks until the end of the statement or transaction.

HOLDLOCK
Use a hold lock to hold a lock until completion of the transaction, instead of releasing the lock as soon as the required table, row, or data page is

no longer required.

NOLOCK
This does not lock any object. This is the default for SELECT operations. It does not apply to INSERT, UPDATE, and DELETE statements.

Examples:
SELECT OrderID
FROM Orders (WITH ROWLOCK)
WHERE OrderID BETWEEN 100
AND 2000

UPDATE Products (WITH NOLOCK)
SET ProductCat = 'Machine'
WHERE ProductSubCat = 'Mac'

http://msdn.microsoft.com/en-us/library/aa213026(SQL.80).aspx
http://blog.sqlauthority.com/2007/04/27/sql-server-2005-locking-hints-and-examples/




View SQL Server Connected Users:-
sp_who or sp_who2

Get SQL users that are connected and how many sessions they have
SELECT login_name, count(session_id) as session_count
FROM sys.dm_exec_sessions
GROUP BY login_name

ADO.NET Sample

SQL:-
CREATE PROCEDURE ShowSuppliers(
@txt varchar(50),
@Name varchar(50) output,
@Company varchar (50) output,
@Country varchar (50) output
)
AS
select @Name = ContactName, @Company = CompanyName,
 @Country = Country
from Suppliers
Where Country like "%"+ @txt +"%"
GO
--------------------------------------------------------------------------------------------------------
C#:-
// Define the command object with stored procedure name.
SqlCoommand objCommand = new Command("ShowSuppliers", objConnection);
objCommand.CommandType = CommandType.StoredProcedure;
 
// Define input Sql parameter with input value.
SqlParameter Param = objCommand.Parameters.Add("@txt", SqlDbType.Varchar, 50);
Param.Direction = ParameterDirection.Input;
Param.Value = "US";
 
// Define sql stored procedure output parameters
SqlParameter Param = objCommand.Parameters.Add("@Name", SqlDbType.Varchar, 50);
Param.Direction = ParameterDirection.Output;
SqlParameter Param = objCommand.Parameters.Add("@Company", SqlDbType.Varchar, 50);
Param.Direction = ParameterDirection.Output;
SqlParameter Param = objCommand.Parameters.Add("@Country", SqlDbType.Varchar, 50);
Param.Direction = ParameterDirection.Output;
 
// Execute the query
objCommand.ExecuteNonQuery();
 
// Retrieve Sql StoredProcedure output data.
Response.Write (objCommand.Parameters["@Name"].Value.ToString() + "<BR>");
Response.Write (objCommand.Parameters["@Company"].Value.ToString() + "<BR>");
Response.Write (objCommand.Parameters["@Country"].Value.ToString() + "<BR>");
 
=================================================================================
SQL:-
CREATE PROCEDURE ShowSuppliers (
@txt varchar(50)
)
AS
Select CompanyName, City, Country
from Suppliers
Where  Country like "%" + @txt + "%"
--------------------------------------------------------------------------------------------------------
C#:-
try{
 SqlCommand objCommand = new SqlCommand("ShowSuppliers",objConnect);
 objCommand.CommandType = CommandType.StoredProcedure;

 SqlParameter Param = objCommand.Parameters.Add("@txt",SqlDbType.VarChar, 50);
 Param.Value = "US";
 
 SqlDataReader objDataReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection);

   while (objDataReader .Read())
     {
        // Get data from the DataReader
        Console.WriteLine("CompanyName:-", (objDataReader ["CompanyName"]);
        Console.WriteLine("City:-", (objDataReader ["City"]);
        Console.WriteLine("Country :-", (objDataReader ["Country "]);
     }
}
finally
{
     // close the reader in the face of exceptions
     if (objDataReader != null)
     {
        if (!objDataReader.IsClosed)
          objDataReader.Close();
      }
}
 
 

Database Normalization- Quick Reference

The Normalization is used to organize data in a DB by eliminating redundant data and
ensuring all table relationships make sense
 
First Normal Form (1NF)

First Normal Form ensures there is no repeating groups
    * Eliminate duplicative columns from the same table.
    * Create separate tables for each group of related data and identify each row with a unique column
 or set of columns (the primary key).
 
============================================================================
Second Normal Form (2NF)

Second normal form (2NF) further addresses the concept of eliminating Redundant Data
    * Meet all the requirements of the first normal form.
    * Remove subsets of data that apply to multiple rows of a table and place them in separate tables.
    * Create relationships between these new tables and their predecessors through the use of foreign keys.
 
============================================================================
Third Normal Form (3NF)

Third normal form (3NF) further addresses the concept of eliminating Columns which are Not Dependent On the Key
    * Meet all the requirements of the second normal form.
    * Remove columns that are not dependent upon the primary key.
 
============================================================================
Fourth Normal Form (4NF)

Finally, fourth normal form (4NF) has one additional requirement:
    * Meet all the requirements of the third normal form.
    * A relation is in 4NF if it has no multi-valued dependencies.
 

SQL Table Joins- Quick Reference

EQUI-JOIN
--------------
select *
from emp INNER JOIN dept
ON
emp.deptId = dept.id;

============================================================================
INNER JOIN
----------------
select e.empId, j.salary
from jobs j INNER JOIN emp e
ON
e.salary BETWEEN j.salary
---OR---------------------------
select e.empId, j.salary
from jobs j INNER JOIN
USING(Salary)

**Equi Join only use to retrieve data base on '='.
But Inner Join can use to retrieve data base on '=', '<', '>'
**NATURAL JOIN is same to the INNER JOIN but use NATURAL JOIN keyword and
which avoids duplicate columns.

=============================================================================
SELF JOIN
--------------
This also use INNER JOIN key word but used to retrive data from the same table.
SELECT e.first_name AS 'Employee FN', e.last_name
FROM employees AS e LEFT OUTER JOIN employees AS m
ON e.manager =m.id


LEFT OUTER JOIN

----------------------------
Select *
From emp LEFT OUTER JOIN dept
ON
emp.deptId = dept.Id

RIGHT OUTER JOIN
---------------------------
Select *
From emp RIGHT OUTER JOIN dept
ON
emp.deptId = dept.Id

FULL OUTER JOIN
---------------------------
select *
From emp FULL OUTER JOIN dept
ON
emp.eid = dept.id

**Outer joins will display all columns in both tables filled with null values for culumns which doesnt retrieve the through query.
More Info:- http://en.wikipedia.org/wiki/Join_(SQL)

.NET REFLECTION

CLR loading each assembly into the appropriate application domain and controlling memmory layout of it.Assembly contains modules, module contain type and type contain members.
Reflection provide object that encapsulate assemblies, modules and types.
 
**U can use reflection to :
-dynamically create an instances of a type,
-bind type to an existing object or
-get the type of from an existing object.

========================================================
Reflection is the feature in .Net, which enables us to get some information about object in runtime. That information contains data of the class. Also it can get the names of the methods that are inside the class and constructors of that object.
-----------------------------------------------------------------------------------------------------------------
    *  Use Assembly to define and load assemblies, load modules that are listed in the assembly manifest, and locate a type from this assembly and create an instance of it.

    * Use Module to discover information such as the assembly that contains the module and the classes in the module. You can also get all global methods or other specific, nonglobal methods defined on the module.
 
    * Use ConstructorInfo to discover information such as the name, parameters, access modifiers (such as public or private), and implementation details (such as abstract or virtual) of a constructor. Use the GetConstructors or GetConstructor method of a Type to invoke a specific constructor.
 
    * Use MethodInfo to discover information such as the name, return type, parameters, access modifiers (such as public or private), and implementation details (such as abstract or virtual) of a method. Use the GetMethods or GetMethod method of a Type to invoke a specific method.
    * Use FieldInfo to discover information such as the name, access modifiers (such as public or private) and implementation details (such as static) of a field, and to get or set field values.
    * Use EventInfo to discover information such as the name, event-handler data type, custom attributes, declaring type, and reflected type of an event, and to add or remove event handlers.
    * Use PropertyInfo to discover information such as the name, data type, declaring type, reflected type, and read-only or writable status of a property, and to get or set property values.
    * Use ParameterInfo to discover information such as a parameter's name, data type, whether a parameter is an input or output parameter, and the position of the parameter in a method signature.
===========================================================================
Ex:-
TestDataType testObject = new TestDataType(15);
Type objectType = testObject.GetType();
ConstructorInfo [] info = objectType.GetConstructors();
MethodInfo [] methods = objectType.GetMethods();

XML & Binary Serialization C#

The Binary serialization is fast but serialization and deserialization is language dependent while the XML serialization is platform/language independent.

FileStream fs = new FileStream("Ser.Data, FileMode.Create");
BinaryFormatter bf = new BinaryFormatter();
bf.Serialized(fs, "data");

FileStream fs = new FileStream("Ser.Data, FileMode.Open");
string data = (string) bf.Deserialized(fs);
fs.Close();

[Serialized]
[NonSerialized]
[OptionalFieled] -Use for newly added fields when try to deserialize an old instance of class

===================================================
FileStream fs = new FileStream("Ser.Data, FileMode.Create");
XmlSerializer xs = new XmlSerializer(typeOf(string));
xs.Serialize(fs, "string1");

FileStream fs = new FileStream("Ser.Data, FileMode.Open");
strig str = (string)xs.Deserialize(fs);
fs.Close();

[XmlRoot]
[XmlAttribute]
[XmlIgnore]