Category Archives: MSSQL

Stored Procedures

 

 

 

 

 

SQL Identity Key ,T-SQL

Microsoft SQL Server?s identity column generates sequential values for new records using a seed value. Seeding and reseeding an identity column is easy and relatively safe, if you do it correctly. Susan Sales Harkins shows you how it works.

 

LICT .NET Training | Kamal Protap Sir

DDL – Data definition Language(Which deals with database schemas and descriptions that how the data should reside in database) – create,alter,drop, truncate, comment
DML – Data manipulation language(Modify, Update, Delete data in database) – select, insert, update, delete
DCL – Data Control Language(Regarding the rights of the database)  grant,revoke

 

31-10-17 vids

SQL Query

SQL Aggregate functions, slides presentation: This presentation describes SQL Aggregate function covering SQL COUNT(), SUM(), MAX(), MIN(), AVG) functions with examples.

SubQuery:

A query within another query is called subquery or nested query . Inner Query, Outer query is in subquery.

Sub Query are two types they are, co-related sub query and non co-related subquery. They works for both DDL and DML statements
Co-related means relationship/dependent on
Non co-related means independent

There are two queries in sub query. You will see group by, order by is single type query but in sub query it needs multiple query

Non co related: They are two types,
Inner query: It will first execute. It will return some result. Independently it will execute.
Outer Query: Based on the inner query result now outer query will execute. The whole process is known as non co-related

 

= for comapring single value
IN for comparing multiple value

Co-related: It is dependent query. Inner query can’t execute independently. Inner query depends on outer query. Without returning row from the outer query inner can’t execute. In co-related sub query. All execute first.

02.11.17 Vids
Store Procedure:

MS SQL Server store procedure is used to save time write the same code again and again for storing in database and also get the rquired output by passing parameters.

Syntax:

Following the basic syntax of stored procedure creation.

Lots of parameters used in stored procedure:

IN paramater is a paramater whose values are passed inside of the procedure
OUT paramater is a paramater whose value is pass outside the parameter
OUT parameter return some value
IN does not return any value
create procedure

Executing the stored procedure:

 

Here are the store procedure example basic in my blog

http://zakilive.com/2017/12/14/stored-procedures/

Creating View:
View is something where other developers can’t find details. But they can make query with the name when they need it.

View helps to show the particular data for the

UPDATE with view

View don’t support order by in that case we  need top keyword in view:

05.11.2017 vids

ADO.NET is  a set of classes that allows you to connect and work with data sources like databases,excel file, access file, xml file,my sql, and notepad.

To connect your application with different data sources of database you need to know the right data provider. There are sevral data providers in ADO.NET that connects with different types of sources.

 

There are 4 core components of .NET data providers that is used to connect,access and retrieve data from the database.

  1. Connection- This component is used for connecting to the database. The base class is DbConnection
  2. Command: This component executes SQL query against the data source. The base class is DbCommand.
  3. DataReader-It reads data from data source. It accesses data read only and forward only. The base class is DbDataReader.
  4. DataAdapter-It invokes dataset and resolves updates with the data source. The base class is DbDataAdapater

Pass value with parameters: