Types of SQL Commands

 These functions include building database objects, manipulating objects, populating database tables with data, updating existing data in tables, deleting data, performing database queries, controlling database access, and overall database administration.

The main categories are

• DDL (Data Definition Language)
• DML (Data Manipulation Language)
• DQL (Data Query Language)
• DCL (Data Control Language)
• Data administration commands
• Transactional control commands

Defining Database Structures

Data Definition Language, DDL, is the part of SQL that allows a database user to create and restructure database objects, such as the creation or the deletion of a table.

Some of the most fundamental DDL commands discussed during following hours include the following:

CREATE TABLE

ALTER TABLE

DROP TABLE

CREATE INDEX

ALTER INDEX

DROP INDEX

CREATE VIEW

DROP VIEW

These commands are discussed in detail during Hour 3, "Managing Database Objects," Hour 17, "Improving Database Performance," and Hour 20, "Creating and Using Views and Synonyms."

Manipulating Data

Data Manipulation Language, DML, is the part of SQL used to manipulate data within objects of a relational database.

There are three basic DML commands:

INSERT

UPDATE

DELETE

Selecting Data (Data Query Language)

Though comprised of only one command, Data Query Language (DQL) is the most concentrated focus of SQL for modern relational database users. The base command is as follows:

SELECT

This command, accompanied by many options and clauses, is used to compose queries against a relational database. Queries, from simple to complex, from vague to specific, can be easily created.

A query is an inquiry to the database for information. A query is usually issued to the database through an application interface or via a command line prompt.

Data Control Language

Data control commands in SQL allow you to control access to data within the database. These DCL commands are normally used to create objects related to user access and also control the distribution of privileges among users. Some data control commands are as follows:

ALTER PASSWORD

GRANT

REVOKE

CREATE SYNONYM

You will find that these commands are often grouped with other commands and may appear in a number of different lessons throughout this book.

Data Administration Commands

Data administration commands allow the user to perform audits and perform analyses on operations within the database. They can also be used to help analyze system performance. Two general data administration commands are as follows:

START AUDIT

STOP AUDIT

Do not get data administration confused with database administration. Database administration is the overall administration of a database, which envelops the use of all levels of commands. Database administration is much more specific to each SQL implementation than are those core commands of the SQL language.

Transactional Control Commands

In addition to the previously introduced categories of commands, there are commands that allow the user to manage database transactions.

• COMMIT Saves database transactions
• ROLLBACK Undoes database transactions
• SAVEPOINT Creates points within groups of transactions in which to ROLLBACK
• SET TRANSACTION Places a name on a transaction

Create Dynamic SQLs via Stored Procedure

If you’re a developer, irrespective of the platform, you would have to work with Databases. Creating SQL statements for tables is quite often a monotonous job and it gets hectic especially with dealing gigantic tables that have hundreds of columns.

Writing SQL statements manually every time becomes a tiresome process. But we have a solution. You could write a Stored Procedure to automatically generate the queries. We have attached the Stored Procedure code for MSSQL Server, though you can replicate to any database with minor changes.

CREATE proc [dbo].[USP_QuerycreationSupport]
(
@table_Name varchar(100)

)
as
begin
DECLARE @InserCols NVARCHAR(MAX)
DECLARE @Inserparam NVARCHAR(MAX)
DECLARE @Insertquery NVARCHAR(MAX)
DECLARE @Selectquery NVARCHAR(MAX)
DECLARE @Update NVARCHAR(MAX)
DECLARE @DeleteQuery NVARCHAR(MAX)

 -- sp param

 SELECT 
    '@'+c.name+ SPACE(1) + case cast(t.Name as nvarchar(40))   when 'nvarchar'    then t.Name+'('+cast(c.max_length as nvarchar(30))+')' 
                  when 'varchar'    then t.Name+'('+cast(c.max_length as nvarchar(30))+')'
                  when 'char'    then t.Name+'('+cast(c.max_length as nvarchar(30))+')'
                  when 'decimal' then t.Name+'(18,2)' else t.Name end +'=null,' as colss
   
   
FROM    
    sys.columns c
INNER JOIN 
    sys.types t ON c.user_type_id = t.user_type_id
LEFT OUTER JOIN 
    sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN 
    sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE
    c.object_id = OBJECT_ID(@table_Name)




 select 'Insert query'
 SET @InserCols=  ( SELECT DISTINCT (SELECT   sc.NAME +',' FROM
           sys.tables st INNER JOIN sys.columns sc ON st.object_id = sc.object_id
                                   WHERE st.name = @table_Name
           FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)'))

 -- Return the result of the function
 SELECT @InserCols=LEFT(@InserCols,LEN(@InserCols)-1)
 --select @InserCols

 
 SET @Inserparam=  ( SELECT DISTINCT (SELECT   '@'+sc.NAME +',' FROM
           sys.tables st INNER JOIN sys.columns sc ON st.object_id = sc.object_id
                                   WHERE st.name = @table_Name
           FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)'))

 -- Return the result of the function
 SELECT @Inserparam=LEFT(@Inserparam,LEN(@Inserparam)-1)
 --select @Inserparam

 set @Insertquery='insert into '+@table_Name+'('+@InserCols+')'+'values'+'('+@Inserparam+')'
 select @Insertquery

 select 'Update Query'
 SET @Update=  ( SELECT DISTINCT (SELECT   sc.NAME +'=@'+sc.NAME+',' FROM
           sys.tables st INNER JOIN sys.columns sc ON st.object_id = sc.object_id
                                   WHERE st.name = @table_Name
           FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)'))

 -- Return the result of the function
 SELECT @Update=LEFT(@Update,LEN(@Update)-1)
 --select @Update
 SET @Update='UPdate  '+@table_Name+'  set '+@Update
 select @Update

 -- For select Query
 select 'Select Query'
  
 set @Selectquery='select '+@InserCols +' from '+ @table_Name
 select @Selectquery

 -- For Delete Query
 select 'Delete Query'
  
 set @DeleteQuery='delete from  '+ @table_Name
 select @DeleteQuery
end

 --   exec USP_QuerycreationSupport @table_Name='MST_ComboMain'
 

Step 1: Create the StoredProcedure. The attached file contains the code for creating a Stored Procedure that auto-generates SQL Queries.

Step 2: Execute the StoredProcedure, passing your required table name as a parameter.

execUSP_QuerycreationSupport@table_Name=’mstCustomer’

List Example with Keyvaluepair

KeyValuePair

var myList = new List<KeyValuePair<int, string>>();
myList.Add(new KeyValuePair<int, string>(1, "One");

foreach (var item in myList)
{
    int i = item.Key;
    string s = item.Value;
}
or if you are .NET Framework 4 you could use:

var myList = new List<Tuple<int, string>>();
myList.Add(Tuple.Create(1, "One"));

foreach (var item in myList)
{
    int i = item.Item1;
    string s = item.Item2;
}
If either the string or integer is going to be unique to the set, you could use:

Dictionary<int, string> or Dictionary<string, int>

Difference between a stack, a queue and a heap

A stack keeps track of what is executing and contains stored value types to be accessed and processed as Last In First Out, with elements both inserted and deleted from the top end.

A queue lists items on a First In First Out basis in terms of both insertion and deletion, with the developer inserting items from the rear end and deleting items from the front end of the queue.

A heap contains stored reference types and is responsible for keeping track of more precise objects.

Read-only variables VS Constants

1. Read-only variables can support reference-type variables. Constants can hold only value-type variables.

2. Developers evaluate read-only variables at the runtime. They evaluate constants at the compile time.

LINQ

LINQ stands for Language Integrated Query.

Language-Integrated Query (LINQ) is an innovation introduced in Visual Studio 2008 and .NET Framework version 3.5 that bridges the gap between the world of objects and the world of data.

You can use LINQ queries in new projects, or alongside non-LINQ queries in existing projects. The only requirement is that the project target .NET Framework 3.5 or later.

This is a Microsoft programming model and methodology that offers developers a way to manipulate data using a succinct yet expressive syntax. It does so by instilling Microsoft .NET-based programming languages with the ability to make formal queries

In Visual Studio you can write LINQ queries in Visual Basic or C# with SQL Server databases, XML documents, ADO.NET Datasets, and any collection of objects that supports IEnumerable or the generic IEnumerable<T> interface. LINQ support for the ADO.NET Entity Framework is also planned, and LINQ providers are being written by third parties for many Web services and other database implementations.

WCF

WCF stands for Windows Communication Foundation.It is a framework for building, configuring, and deploying network-distributed services

WCF was released for the first time in 2006 as a part of the .NET framework with Windows Vista, and then got updated several times. WCF 4.5 is the most recent version that is now widely used.

A WCF application consists of three components:

• WCF service,
• WCF service host, and
• WCF service client.

Fundamental Concepts of WCF

Message

This is a communication unit that comprises of several parts apart from the body. Message instances are sent as well as received for all types of communication between the client and the service.

Endpoint

It defines the address where a message is to be sent or received. It also specifies the communication mechanism to describe how the messages will be sent along with defining the set of messages. A structure of an endpoint comprises of the following parts:

• Address - Address specifies the exact location to receive the messages and is specified as a Uniform Resource Identifier (URI). It is expressed as scheme://domain[:port]/[path]. Take a look at the address mentioned below:
  net.tcp://localhost:9000/ServiceA
  Here, 'net.tcp' is the scheme for the TCP protocol. The domain is 'localhost' which can be the name of a machine or a web domain, and the path is 'ServiceA'.

• Binding - It defines the way an endpoint communicates. It comprises of some binding elements that make the infrastructure for communication. For example, a binding states the protocols used for transport like TCP, HTTP, etc., the format of message encoding, and the protocols related to security as well as reliability.
• Contracts - It is a collection of operations that specifies what functionality the endpoint exposes to the clinet. It generally consists of an interface name.

Hosting

Hosting from the viewpoint of WCF refers to the WCF service hosting which can be done through many available options like self-hosting, IIS hosting, and WAS hosting.

Metadata

This is a significant concept of WCF, as it facilitates easy interaction between a client application and a WCF service. Normally, metadata for a WCF service is generated automatically when enabled, and this is done by inspection of service and its endpoints.

WCF Client

A client application that gets created for exposing the service operations in the form of methods is known as a WCF client. This can be hosted by any application, even the one that does service hosting.

Channel

Channel is a medium through which a client communicates with a service. Different types of channels get stacked and are known as Channel Stacks.

SOAP

Although termed as ‘Simple Object Access Protocol’, SOAP is not a transport protocol; instead it is an XML document comprising of a header and body section.

Advantages of WCF

• It is interoperable with respect to other services. This is in sharp contrast to .NET Remoting in which both the client and the service must have .Net.
• WCF services offer enhanced reliability as well as security in comparison to ASMX (Active Server Methods) web services.
• Implementing the security model and binding change in WCF do not require a major change in coding. Just a few configuration changes is required to meet the constraints.
• WCF has built-in logging mechanism whereas in other technologies, it is essential to do the requisite coding.
• WCF has integrated AJAX and support for JSON (JavaScript object notation).
• It offers scalability and support for up-coming web service standards.
• It has a default security mechanism which is extremely robust.

difference between WCF and Web services

Features	Web Service	WCF
Hosting	It can be hosted in IIS	It can be hosted in IIS, windows activation service, Self-hosting, Windows service
Programming	[WebService] attribute has to be added to the class	[ServiceContraact] attribute has to be added to the class
Model	[WebMethod] attribute represents the method exposed to client	[OperationContract] attribute represents the method exposed to client
Operation	One-way, Request- Response are the different operations supported in web service	One-Way, Request-Response, Duplex are different type of operations supported in WCF
XML	System.Xml.serialization name space is used for serialization	System.Runtime.Serialization namespace is used for serialization
Encoding	XML 1.0, MTOM(Message Transmission Optimization Mechanism), DIME, Custom	XML 1.0, MTOM, Binary, Custom
Transports	Can be accessed through HTTP, TCP, Custom	Can be accessed through HTTP, TCP, Named pipes, MSMQ,P2P, Custom
Protocols	Security	Security, Reliable messaging, Transactions