SQL SERVER – 2005 – Search Stored Procedure Code – Search Stored Procedure Text

SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%<search_word>%'

SQL SERVER – Find Stored Procedure Related to Table in Database – Search in All Stored Procedure

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

SQL SERVER – Find Column Used in Stored Procedure – Search Stored Procedure for Column Name

SELECT obj.Name SPName, sc.TEXT SPText
FROM sys.syscomments sc
INNER JOIN sys.objects obj ON sc.Id = obj.OBJECT_ID
WHERE sc.TEXT LIKE '%' + 'Name Your Column Here' + '%'
AND TYPE = 'P'

SQL Constraints

SQL Constraints

SQL constraints are used to specify rules for the data in a table.  If there is any violation between the constraint and the data action, the action is aborted by the constraint.

Syntax for SQL Constraints

SQL CREATE TABLE + CONSTRAINT Syntax

CREATE TABLE table_name
(
column_name1 data_type(size) constraint_name,
column_name2 data_type(size) constraint_name,
column_name3 data_type(size) constraint_name,
);

In SQL, we have the following constraints

NOT NULL - Indicates that a column cannot store NULL value UNIQUE - Ensures that each row for a column must have a unique value PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Ensures that a column (or combination of two or more columns) have an unique identity which helps to find a particular record in a table more easily and quickly FOREIGN KEY - Ensure the referential integrity of the data in one table to match values in another table CHECK - Ensures that the value in a column meets a specific condition DEFAULT - Specifies a default value when specified none for this column

Column Exists or Not in SQL Server

Column Exists or Not in SQL Server

CREATE FUNCTION Axfn_ColumnExists
(
 @TableName VARCHAR(100) ,
@ColumnName VARCHAR(100) )
 RETURNS VARCHAR(100)
AS
BEGIN
 DECLARE @Result VARCHAR(100);
 IF EXISTS
(
 SELECT 1 FROM INFORMATION_SCHEMA.Columns
             WHERE TABLE_NAME = @TableName AND COLUMN_NAME = @ColumnName ) BEGIN
 SET @Result = 'Already Exsits'
 END
 ELSE
 BEGIN SET @Result = 'Not Available, You can create now!'
 END
 RETURN (@Result)
 END
GO

SQL SERVER – Disable All the Foreign Key Constraint in Database – Enable All the Foreign Key Constraint in Database

-- Disable all the constraint in database

EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

-- Enable all the constraint in database

EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

Refer this link

SQL SERVER – Create Unique Constraint on Table Column on Existing Table

by using UNIQUE for Unique constraint.

Syntax:

   UNIQUE([col name])

Example:

USE tempdb
GO
-- Create Table
CREATE TABLE Table1 (ID INT, Col1 VARCHAR(100))
GO
-- Alter Table Create Constraint
ALTER TABLE Table1
ADD CONSTRAINT UX_Constraint UNIQUE (Col1)
GO
-- Clean up
DROP TABLE Table1
GO

Parts in CTE

Common Table Expression contains three core parts

The CTE name (this is what follows the WITH keyword) The column list (optional) The query (appears within parentheses after the AS keyword) The query using the CTE must be the first query appearing after the CTE.

Use CTE in SQL Server

Syntax of CTE:With Parameter

With T(<col Name>, <col name1>, <Col name2>)  --Column names for Temporary table
AS
(
SELECT A.<Col name>, E.<col name1>, E.<col name2> from <table_name> A
INNER JOIN <table_name> E ON E.<col name> = A.<col name>
)
SELECT * FROM T  --SELECT or USE CTE temporary Table
WHERE T.[col name]  > 50
ORDER BY T.<col name>

Use CTE in SQL Server

Syntax of CTE:Without Parameter

WITH MyCTE AS (SELECT c.[Col name] FROM [table_name] pc INNER JOIN [table_name] c ON c.[Col name] = pc.[Col name]) SELECT cte.[Col name], p.[Col name] FROM [table_name] p INNER JOIN [table_name].[Col name] ea ON ea.[Col name] = p.[Col name] INNER JOIN MyCTE cte ON cte.[Col name] = p.[Col name] INNER JOIN [table_name].PersonPhone ph ON ph.[Col name] = p.[Col name];

CTE in SQL Server

CTE in SQL Server

The common table expression (CTE) is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement.  You can also use a CTE in a CREATE VIEW statement, as part of the view’s SELECT query. In addition, as of SQL Server 2008, you can add a CTE to the new MERGE statement.

SQL DB Formatting

SQL DB Formatting

Use upper case for all SQL keywords  SELECT, INSERT, UPDATE, WHERE, AND, OR, LIKE, etc.  Indent code to improve readability  Comment code blocks that are not easily understandable  Use single-line comment markers(–)  Reserve multi-line comments (/*.. ..*/) for blocking out sections of code  Use single quote characters to delimit strings.  Nest single quotes to express a single quote or apostrophe within a string  For example, SET @sExample = ‘SQL”s Authority’  Use parentheses to increase readability WHERE (color=’red’ AND (size = 1 OR size = 2))  Use BEGIN..END blocks only when multiple statements are present within a conditional code segment. Use one blank line to separate code sections.  Use spaces so that expressions read like sentences.  Format JOIN operations using indents  Also, use ANSI Joins instead of old style joins   Place SET statements before any executing code in the procedure.

SQL DB Structure

SQL DB Structure

Each table must have a primary key.  In most cases it should be an IDENTITY column named ID.  Normalize data to third normal form.  Do not compromise on performance to reach third normal form. Sometimes, a little de-normalization results in better performance.  Do not use TEXT as a data type; use the maximum allowed characters of VARCHAR instead.  In VARCHAR data columns, do not default to NULL; use an empty string instead.  Columns with default values should not allow NULLs.  As much as possible, create stored procedures on the same database as the main tables they will be accessing.

SQL DB General Rules

SQL DB General Rules

Do not use spaces in the name of database objects.  Do not use SQL keywords as the name of database objects.  In cases where this is necessary, surround the object name with brackets, such as [Year].  Do not prefix stored procedures with ‘sp_’2.  Prefix table names with the owner name3.

SQL SERVER – Guidelines and Coding Standards

Use “Pascal” notation for SQL server Objects Like Tables, Views, Stored Procedures. Also tables and views should have ending “s”.
Example: UserDetails
If you have big subset of table group than it makes sense to give prefix for this table group. Prefix should be separated by _.
Example: Page_ UserDetails
Use following naming convention for Stored Procedure. sp<Application Name>_[<group name >_]<action type><table name or logical instance> Where action is: Get, Delete, Update, Write, Archive, Insert… i.e. verb
Example:spApplicationName_GetUserDetails
Use following Naming pattern for triggers: TR_<TableName>_<action><description>
Example:TR_UserDetails_UpdateUserName
Indexes : IX_<tablename>_<columns separated by_>
Example:IX_UserDetails_UserID
Primary Key : PK_<tablename>
Example:PK_UserDetails
Foreign Key : FK_<tablename_1>_<tablename_2>
Example:FK_UserDetails_Emails
Default: DF_<table name>_<column name>
Example:DF_ UserDetails_UserName
Normalize Database structure based on 3rd Normalization Form. Normalization is the process of designing a data model to efficiently store data in a database. (Read More Here)
Avoid use of SELECT * in SQL queries. Instead practice writing required column names after SELECT statement.
Example:SELECT Username, Password
FROM UserDetails
Use SET NOCOUNT ON at the beginning of SQL Batches, Stored Procedures and Triggers. This improves the performance of Stored Procedure. (Read More Here)
Properly format SQL queries using indents.
Example:SELECT Username, Password
FROM UserDetails ud
INNER JOIN Employee e ON e.EmpID = ud.UserID
Practice writing Upper Case for all SQL keywords.
Example:SELECT, UPDATE, INSERT, WHERE, INNER JOIN, AND, OR, LIKE.
It is common practice to use Primary Key as IDENTITY column but it is not necessary. PK of your table should be selected very carefully.
If “One Table” references “Another Table” than the column name used in reference should use the following rule :
Column of Another Table : <OneTableName> ID
Example:
If User table references Employee table than the column name used in reference should be UserID where User is table name and ID primary column of User table and UserID is reference column of Employee table.
Columns with Default value constraint should not allow NULLs.
Practice using PRIMARY key in WHERE condition of UPDATE or DELETE statements as this will avoid error possibilities.
Always create stored procedure in same database where its relevant table exists otherwise it will reduce network performance.
Avoid server-side Cursors as much as possible, instead use SELECT statement. If you need to use cursor then replace it next suggestion.
Instead of using LOOP to insert data from Table B to Table A, try to use SELECT statement with INSERT statement. (Read More Here)
INSERT INTO TABLE A (column1, column2)
SELECT column1, column2
FROM TABLE B
WHERE ....
Avoid using spaces within the name of database objects; this may create issues with front-end data access tools and applications. If you need spaces in your database object name then will accessing it surround the database object name with square brackets.
Example:[Order Details]
Do not use reserved words for naming database objects, as that can lead to some unpredictable situations. (Read More Here)
Practice writing comments in stored procedures, triggers and SQL batches, whenever something is not very obvious, as it won’t impact the performance.
Do not use wild card characters at the beginning of word while search using LIKE keyword as it results in Index scan.
Indent code for better readability. (Example)
While using JOINs in your SQL query always prefix column name with the table name. (Example). If additionally require then prefix Table name with ServerName, DatabaseName, DatabaseOwner. (Example)
Default constraint must be defined at the column level. All other constraints must be defined at the table level. (Read More Here)
Avoid using rules of database objects instead use constraints.
Do not use the RECOMPILE option for Stored Procedure unless there is specific requirements.
Practice to put the DECLARE statements at the starting of the code in the stored procedure for better readability (Example)
Put the SET statements in beginning (after DECLARE) before executing code in the stored procedure.

SQL SERVER – Stored Procedure Optimization Tips – Best Practices

• Include SET NOCOUNT ON statement
• Use schema name with object name
• Do not use the prefix “sp_” in the stored procedure name
• Use IF EXISTS (SELECT 1) instead of (SELECT *)
• Use the sp_executesql stored procedure instead of the EXECUTE statement
• Try to avoid using SQL Server cursors whenever possible
• Keep the Transaction as short as possible
• Use TRY-Catch for error handling

Auto Increment in VB.Net

Today, We will discuss about "How to Auto Increment in Vb.Net?"

Sub CustomerId()
 Dim strID As String = ""
 Dim St As String = ""
 CON = ConOpen()
 CMD = New SqlCommand("pass your sp or directquery", CON) 
 CMD.CommandType = CommandType.StoredProcedure
 CON.Open() 
 DR = CMD.ExecuteReader 
If DR.Read() Then
 strID = DR(0).ToString
 End If
 DR.Close()
 CON.Close()
 Dim INNO As Integer
 Dim IndID As String = ""
 Dim Num As Integer = "0001"
 Dim CPCode1 As String()
 Dim CPComp As String
 If strID = "" Then
 txtCustomerId.Text = "CI0000" & Num
 Else
 CPCode1 = strID.Split(New Char() {"I"c})
 For Each CPComp In CPCode1
 Console.WriteLine(CPComp)
 IndID = CPComp
 Next
 INNO = IndID + Num
 If INNO < 10 Then
 txtCustomerId.Text = "CI0000" & INNO
 ElseIf INNO >= 10 And INNO <= 99 Then
 txtCustomerId.Text = "CI000" & INNO
 ElseIf INNO >= 100 And INNO <= 999 Then
 txtCustomerId.Text = "CI00" & INNO
 ElseIf INNO >= 1000 And INNO <= 9999 Then
 txtCustomerId.Text = "CI0" & INNO
 ElseIf INNO >= 10000 Then
 txtCustomerId.Text = "CI" & INNO
 End If
 End If
 End Sub

In this Code, I create customer Id like "CI00001"

Entity Framework 6.0 Namespace

Entity Framework 6.0

Microsoft.Data.Entity.Design  Microsoft.Data.Entity.Design.CodeGeneration  Microsoft.Data.Entity.Design.DatabaseGeneration Microsoft.Data.Entity.Design.DatabaseGeneration.Activities Microsoft.Data.Entity.Design.DatabaseGeneration.OutputGenerators Microsoft.Data.Entity.Design.Extensibility  Microsoft.Data.Entity.Design.Templates  Microsoft.Data.Entity.Design.UI.Views.Explorer  Microsoft.Data.Entity.Design.VisualStudio.ModelWizard Microsoft.Data.Entity.Design.VisualStudio.SingleFileGenerator Microsoft.Data.Entity.Design.VisualStudio.TextTemplating System.ComponentModel.DataAnnotations.Schema  System.Data.Entity  System.Data.Entity.Core  System.Data.Entity.Core.Common  System.Data.Entity.Core.Common.CommandTrees System.Data.Entity.Core.Common.CommandTrees.ExpressionBuilder System.Data.Entity.Core.Common.CommandTrees.ExpressionBuilder.Spatial System.Data.Entity.Core.Common.EntitySql  System.Data.Entity.Core.EntityClient  System.Data.Entity.Core.Mapping  System.Data.Entity.Core.Metadata.Edm  System.Data.Entity.Core.Objects  System.Data.Entity.Core.Objects.DataClasses  System.Data.Entity.Infrastructure  System.Data.Entity.Infrastructure.Annotations  System.Data.Entity.Infrastructure.DependencyResolution  System.Data.Entity.Infrastructure.MappingViews  System.Data.Entity.Infrastructure.Pluralization  System.Data.Entity.Migrations  System.Data.Entity.Migrations.Builders  System.Data.Entity.Migrations.Design  System.Data.Entity.Migrations.History  System.Data.Entity.Migrations.Infrastructure  System.Data.Entity.Migrations.Model  System.Data.Entity.Migrations.Sql  System.Data.Entity.Migrations.Utilities  System.Data.Entity.ModelConfiguration  System.Data.Entity.ModelConfiguration.Configuration System.Data.Entity.ModelConfiguration.Conventions  System.Data.Entity.Spatial  System.Data.Entity.SqlServer  System.Data.Entity.SqlServerCompact  System.Data.Entity.Validation  XamlGeneratedNamespace

Entity Framework 6.0 Features

Async Query and Save adds support for the task-based asynchronous patterns that were introduced in .NET 4.5.  Connection Resiliency enables automatic recovery from transient connection failures.  Code-Based Configuration gives you the option of performing configuration – that was traditionally performed in a config file – in code.  Dependency Resolution introduces support for the Service Locator pattern and we've factored out some pieces of functionality that can be replaced with custom implementations.  Interception/SQL logging provides low-level building blocks for interception of EF operations with simple SQL logging built on top.  Testability improvements make it easier to create test doubles for DbContext and DbSet when using a mocking framework or writing your own test doubles.  DbContext can now be created with a DbConnection that is already opened which enables scenarios where it would be helpful if the connection could be open when creating the context (such as sharing a connection between components where you can not guarantee the state of the connection).  Improved Transaction Support provides support for a transaction external to the framework as well as improved ways of creating a transaction within the Framework.  Enums, Spatial and Better Performance on .NET 4.0 - By moving the core components that used to be in the .NET Framework into the EF NuGet package we are now able to offer enum support, spatial data types and the performance improvements from EF5 on .NET 4.0.  Improved performance of Enumerable.Contains in LINQ queries. Improved warm up time (view generation), especially for large models.

Request Flow in MVC

Request Flow in MVC

Answer:Request -->Routing --> Handler -->Controller --> Action --> View --> Response

Entity Framework 5.0 Features

Entity Framework 5.0 Features

This release can be used in Visual Studio 2010 and Visual Studio 2012 to write applications that target .NET 4.0 and .NET 4.5. When targeting .NET 4.5 this release introduces some new features including enum support, table-valued functions, spatial data types and various performance improvements.  If you create a new model using the Entity Framework Designer in Visual Studio 2012, the EF5 NuGet package will be installed to your project and the generated code will make use of EF5. New ASP.NET projects created in Visual Studio 2012 (including MVC projects) also have the EF5 NuGet package installed by default.  The Entity Framework Designer in Visual Studio 2012 also introduces support for multiple-diagrams per model, coloring of shapes on the design surface and batch import of stored procedures.

Define ASP .NET MVC Routing

Define ASP .NET MVC Routing

The ASP.NET Routing module is responsible for mapping incoming browser requests to particular MVC controller actions.  ASP .NET Routing is setup in two places   Web.Config,Global.asax

Expression Blend

Welcome to Expression Blend:

Expression Blend 2.0

Introducing Microsoft Expression Blend 2, a full-featured professional design tool for creating engaging and sophisticated user interfaces for Windows Presentation Foundation (WPF) and Microsoft Silverlight applications. Expression Blend lets designers focus on creativity while letting developers focus on programming.            In Expression Blend, you design your application visually, drawing shapes, paths, and controls on the artboard, and then modifying their appearance and behavior. You can import images, video, and sound. In Windows-based applications, you can also import and change 3D objects.         Silverlight 2 is supported in Expression Blend 2 with Service Pack 1 installed. You can import graphics and Extensible Application Markup Language (XAML) resources that are generated by Microsoft Expression Design 2 into your Expression Blend 2 project. You can also import Silverlight media projects that were created in Microsoft Expression Encoder 2, to add new features or visual elements to the project, or to modify the media player template that can be reused in Expression Encoder 2.         In Microsoft Expression Web 2, you can import Silverlight 1.0 websites and compiled Silverlight 2 application files into an existing or new project, and then publish your work.          Microsoft Visual Studio 2008 works seamlessly with Expression Blend 2 to automatically update code-behind files in your project when you specify events to listen for. From the Project panel in Expression Blend 2, you can open individual code-behind files or your whole project. You can also use the deployment tools of Visual Studio 2008 to deploy your applications.         Expression Blend produces Windows Presentation Foundation (WPF) applications, Silverlight 1.0 websites, and Silverlight 2 user controls (.xap and supporting files). Your visual design is represented by XAML. Just as HTML is the markup language for web applications, XAML is the markup language for WPF.        Blend is mainly Focused on Creating High Quality UI. where as Visual Studio for WPF/Silverlight is Mainly Focused on EventHandlers / Coding.

Advantages of Expression Blend

DRAWING vector art, paths, etc  Data binding dialogs with properties and complex options  Re-templating

Entity Framework 5.0 Namespaces

Entity Framework 5.0 Namespaces

System.Data.Entity  System.Data.Entity.Infrastructure  System.Data.Entity.Migrations  System.Data.Entity.Migrations.Builders  System.Data.Entity.Migrations.Design  System.Data.Entity.Migrations.History  System.Data.Entity.Migrations.Infrastructure  System.Data.Entity.Migrations.Model  System.Data.Entity.Migrations.Sql  System.Data.Entity.Migrations.Utilities  System.Data.Entity.ModelConfiguration  System.Data.Entity.ModelConfiguration.Configuration System.Data.Entity.ModelConfiguration.Conventions  System.Data.Entity.Validation

ADO.NET Tips

ADO.NET

ADO.NET provides consistent access to data sources such as SQL Server and XML, and to data sources exposed through OLE DB and ODBC. Data-sharing consumer applications can use ADO.NET to connect to these data sources and retrieve, handle, and update the data that they contain.

ADO.NET 2.0 Features

Enhancements to the DataSet and Datatable classes.  Optimized DataSet Serialization.  Conversion of a DataReader to a DataSet or a DataTable and vice versa. Data Paging.  Batch Updates — Reduction in database roundtrips.  Asynchronous Data Access. Common Provider Model.

ADO.NET 3.5 Features

Expanded Entity Framework (EF 4.0 and 1.0) Support .  Connection Pooling Performance Enhancements .  Increased Breadth of Support.

C# coding Guide

C# coding Guide

Bracing            Open braces should always be at the beginning of the line after the statement that begins the block. Contents of the brace should be indented by 4 spaces. For example: “case” statements should be indented from the switch statement like this: Braces should never be considered optional. Even for single statement blocks, you should always use braces. This increases code readability and maintainability. Single line statements Single line statements can have braces that begin and end on the same line. It is suggested that all control structures (if, while, for, etc.) use braces, but it is not required. Spacing          Spaces improve readability by decreasing code density. Here are some guidelines for the use of space characters within code: Do use a single space after a comma between function arguments.  Do not use a space after the parenthesis and function arguments  Do not use spaces between a function name and parenthesis.  Do not use spaces inside brackets.  Do use a single space before flow control statements  Do use a single space before and after comparison operators  Naming         Follow all .NET Framework Design Guidelines for both internal and external members. Highlights of these include: Do not use Hungarian notation  Do not use a prefix for member variables (_, m_, s_, etc.). If you want to distinguish between local and member variables you should use “this.” in C# and “Me.” in VB.NET. Do use camelCasing for member variables  Do use camelCasing for parameters  Do use camelCasing for local variables  Do use PascalCasing for function, property, event, and class names  Do prefix interfaces names with “I” Do not prefix enums, classes, or delegates with any letter  Interop Classes            Classes that are there for interop wrappers (DllImport statements) should follow the naming convention below: NativeMethods            No suppress unmanaged code attribute, these are methods that can be used anywhere because a stack walk will be performed. UnsafeNativeMethods             Has suppress unmanaged code attribute. These methods are potentially dangerous and any caller of these methods must do a full security review to ensure that the usage is safe and protected as no stack walk will be performed. SafeNativeMethods             Has suppress unmanaged code attribute. These methods are safe and can be used fairly safely and the caller isn’t needed to do full security reviews even though no stack walk will be performed. All interop classes must be private, and all methods must be internal. In addition a private constructor should be provided to prevent instantiation. File Organization         Source files should contain only one public type, although multiple internal classes are allowed         Source files should be given the name of the public class in the file Directory names should follow the namespace for the class For example, I would expect to find the public class “System.Windows.Forms.Control” in “System\Windows\Forms\Control.cs" Classes member should be alphabetized, and grouped into sections (Fields, Constructors, Properties, Events, Methods, Private interface implementations, Nested types) Using statements should be inside the namespace declaration.

Mutable and Immutable string in C#

Mutable and Immutable string in C#

Mutable -->means -->change in value Immutable -->means -->No change in value

14 Principles on Total Quality Management

Professional Development – Dr W. Edwards Deming’s 14 Principles on Total Quality Management Dr. Demings’s 14 principles

Create a constant purpose toward improvement Adopt the new philosophy Cease dependence on mass inspection Use a single supplier for any one item Improve every process Create training on the job Adopt and institute leadership aimed at helping people do a better job Drive out fear Break down barriers between departments Get rid of unclear slogans Eliminate arbitrary numerical targets Permit pride of workmanship Implement education and self-improvement Make transformation everyone’s job

Event In C#

Event

Event is a kind of Notification(Information) giving one by one object to perform task. Event handling is not possible without delegate because it is delegate who will be responsible to call Event handler which even is fired.

Partial Class In C#

Partial Class

we can break a large class code into small pieces by specifying more than one class with same but with partial keyword. In that case those same name classes will not be treated as different classes so when we will made the object of the class, we will get all the method and member for all same name class.

Method Hiding in C#

Method Hiding

We can predefined the method of parent class in child class by implementing the concept of method hiding. In method hiding the base(parent) class method is predefined in child class by specify new keyword.

Types of delegates in c#

Types of delegates

Single cast delegate  Double cast delegate  Single cast delegate    One delegate object can call only one method, is known as single cast delegate. Double cast delegate          In c# One delegate object can call more than one method in sequence ,is known as multicast delegate.

Method Parameters in C#

Method of Parameters

Value Parameters. Reference Parameters. Output Parameters. Parameter arrays.   Value Parameters in C#      The Value Parameters are used to pass for passing parameters into method by value. Reference Parameters in C#         Reference parameters are used to pass parameters into Method by reference. Output Parameters in C#          The Output parameters are used to pass results back to the calling Method. Use of Parameter arrays in C#            A Method that can handle variable number of arguments,is known as parameter arrays.Parameter arrays are declared using the keyword param. Method Overloading in C#          To create More than one Method with same class name but with the different parameter lists and different definitions,is known as Method Overloading.