Adding Connection string in web.config

Connections String is a piece of code used to connect to the database from the main code.

It is placed in web.config file of the code
you have to add a reference on above of the code

using System.Web.Configuration; 

<connectionStrings >
    <add
         name="myConnectionString" 
         connectionString="Server=YourServerAddress;Database=Databasename;User ID=ServerloginId;Password=ServerPassword;Trusted_Connection=False;"
         providerName="System.Data.SqlClient"/>
</connectionStrings>

ORM

Entity Framework is an open source ORM framework from Microsoft

Now what is ORM...?

ORM stands for Object Relational Mapping.

ORM is a tool for storing data from domain objects to relational database like MS SQL Server, in an automated way, without much programming. O/RM includes three main parts: Domain class objects, Relational database objects and Mapping information on how domain objects map to relational database objects (tables, views & storedprocedures). ORM allows us to keep our database design separate from our domain class design. This makes the application maintainable and extendable. It also automates standard CRUD operation (Create, Read, Update & Delete) so that the developer doesn't need to write it manually.

ORM Model from wikipedia

ORM tool generates classes for the database interaction for your application

DDL, DML, DCL and TCL in SQL

DDL, DML, DCL, TCL Commands

DDL

Data Definition Language (DDL) statements are used to define the database structure or schema. 

Some examples:

• CREATE - to create objects in the database 
• ALTER - alters the structure of the database 
• DROP - delete objects from the database 
• TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed 
• COMMENT - add comments to the data dictionary 
• RENAME - rename an object

DML

Data Manipulation Language (DML) statements are used for managing data within schema objects. 

Some examples:

• SELECT - retrieve data from the a database 
• INSERT - insert data into a table 
• UPDATE - updates existing data within a table 
• DELETE - deletes all records from a table, the space for the records remain 
• MERGE - UPSERT operation (insert or update) 
• CALL - call a PL/SQL or Java subprogram 
• EXPLAIN PLAN - explain access path to data 
• LOCK TABLE - control concurrency

DCL

Data Control Language (DCL) statements.
Some examples:

• GRANT - gives user's access privileges to database 
• REVOKE - withdraw access privileges given with the GRANT command

TCL

Transaction Control (TCL) statements are used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions.

• COMMIT - save work done 
• SAVEPOINT - identify a point in a transaction to which you can later roll back 
• ROLLBACK - restore database to original since the last COMMIT 
• SET TRANSACTION - Change transaction options like isolation level and what rollback segment to use 

Create a Schema from existing Database

USE [DatabaseName]  -- Name of Database
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[usp_createclientschema]

@Loginname nvarchar(50),  -- Login name of new schema like 'sa' in case of dbo
@LoginPassword nvarchar(50), -- password for new schema
@User nvarchar(50), -- user for schema 'can be same as schema name'
@SchemaName nvarchar(50) -- schema name

AS
BEGIN
declare @queryLoginName nvarchar(max)
declare @queryUser nvarchar(max)
declare @querySchema nvarchar(max)
declare @queryRole1 nvarchar(max)
declare @queryCreateTable nvarchar(max)
declare @authSchema nvarchar(max)

-- This portion create authentication for new schema

select @querySchema = ('CREATE SCHEMA '+ @SchemaName+ ';' ) execute (@querySchema)
select @queryLoginName = ('CREATE LOGIN '+@Loginname+' WITH PASSWORD = '''+@LoginPassword+''';')  execute (@queryLoginName)
select @queryUser = ('CREATE USER '+@User+' FOR LOGIN '+@Loginname+' WITH DEFAULT_SCHEMA = ' + @SchemaName + ';') execute (@queryUser)
select @authSchema = ('ALTER AUTHORIZATION ON SCHEMA::' + @SchemaName + ' TO ' + @User + ';')execute (@authSchema)
select @queryRole1 = ('sp_addrolemember ''db_owner'','''+@User +'''') execute (@queryRole1)


--Below portion create table into new schema from dbo, here condition 1=1 will copy complete structure with data and 1=2 with create only structure into schema
select @queryCreateTable =
('
SELECT * INTO '+ @SchemaName+'.Table1 from Table1  where 1 = 1 ;
SELECT * INTO '+ @SchemaName+'.Table2 from Table2 where 1 = 2 ;
')
execute(@queryCreateTable)

-- This portion of code will create Types , for that make stored procedure of Types into dbo database and ----execute them here

execute sp_storedprocedure1_Type  @SchemaName
execute sp_storedprocedure2_Type  @SchemaName

-- Below portion is to create Clustered index into schema

declare @ClusIndex1 nvarchar(max)
select @ClusIndex1 =
('
ALTER TABLE ['+@SchemaName+'].[Tablename] ADD  CONSTRAINT [PK_Columnname] PRIMARY KEY CLUSTERED
(
[AutoId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
')
execute (@ClusIndex1)


-- Below portion is to create NonClustered index into schema
declare @NonClusIndex1 nvarchar(max)
select @NonClusIndex1 =
('
CREATE NONCLUSTERED INDEX [Country] ON ['+@SchemaName+'].[tablename]
(
[columnname1] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
')
execute (@NonClusIndex1)


-- Below portion is to create Stored procedure into schema from dbo

DECLARE @SQL1 nvarchar(max) SELECT @SQL1 = REPLACE(OBJECT_DEFINITION (object_id('usp_Storedprocedure1')),'dbo',''+ @SchemaName+'')exec sp_executesql  @SQL1
DECLARE @SQL2 nvarchar(max) SELECT @SQL2 = REPLACE(OBJECT_DEFINITION (object_id('usp_Storedprocedure2')),'dbo',''+ @SchemaName+'')exec sp_executesql  @SQL2

END