Stored Procedure in SQL Server

A stored procedure is nothing more than prepared SQL Query that you save so you can reuse the Query over and over again. So if you think about a query that you write over and over again, instead of having to write that query each the time you would save it as a stored procedure and then just call the stored procedure to execute the SQL code that you saved as part of the stored procedure. Stored procedures are a batch of SQL statements that can be executed in a couple of ways. Most major DBMS support stored procedures; however, not all do. You will need to verify with your particular DBMS help documentation for specifics

A stored procedure will accept input parameters so that a single procedure can be used over the network by several clients using different input data. A stored procedure will reduce network traffic and increase the performance

Advantages of stored procedures

1. A stored procedure allows modular programming.
2. A stored procedure allows for faster execution.
3. A stored procedure can reduce network traffic.
4. Stored procedures provide better security to your data

Example of the stored procedure :

Step 1-Create simple stored procedure in SQL server

create procedure proc_getAllData as begin select * from temptable end

Step 2-Execute stored procedure in SQL server

exec proc_getAllData

Note: If you want to delete the stored procedure then use drop command to drop the stored procedure

drop procedure proc_getAllData

Types of Stored Procedure

SQL Server we have different types of stored procedures:

1. System stored procedures
2. User-defined stored procedures
3. Extended stored Procedures

System stored procedures

Stored procedures are stored in the master database and these start with a sp_ prefix. These procedures can be used to perform a variety of tasks to support SQL Server functions.

Example: sp_helptext [StoredProcedure_Name]

(To get the text of stored procedure )

User-defined stored procedures

User-defined stored procedures are usually stored in a user database and are typically designed to complete the tasks in the user database.

Extended stored Procedures

Extended stored procedures are the procedures that call functions from DLL files. Nowadays, extended stored procedures are depreciated for the reason it would be better to avoid using extended stored procedures.

State Management in asp.net

State management means to preserve the state of a control, web page, object/data, and user in the application explicitly because all ASP.NET web applications are stateless, i.e., by default, for each page posted to the server, the state of controls is lost. So State Management can be defined as the technique or the way by which we can maintain/store the state of the page or application until the User's Session ends.

State Management Techniques

ASP.NET provides us with 2 ways to manage the state of an application. It is divided into 2 categories

1. Client-Side State Management

2. Server-Side State Management

Client-Side State Management.

It is a way in which the information which is being added by the user or the information about the interaction happened between the user and the server is stored on the client's machine or on the page itself. The server resources (e.g. server's memory) are not at all utilized during the process.

1. Hidden Field

2. View State

3. Cookies

4. Control State

5. Query Strings

Hidden fields:–

hidden fields store data in an HTML form without displaying it in the user's browser. The data is available only when the form is processed.

View State:-

Asp.Net uses View State to track the values in the Controls. You can add custom values to the view state. It is used by the Asp.net page framework to automatically save the values of the page and of each control just before rendering to the page. When the page is posted, one of the first tasks performed by page processing is to restore view state.

Control State:-

If you create a custom control that requires view state to work properly, you should use a control state to ensure other developers don’t break your control by disabling view state.

Cookies: –

 Cookies store a value in the user's browser that the browser sends with every page request to the same server. Cookies are the best way to store state data that must be available for multiple Web pages on a web site.

Query Strings: -

 Query strings store values in the URL that are visible to the user. Use query strings when you want a user to be able to e-mail or instant message state data with a URL.

Server-Side State Management

ASP.NET provides another way to store the user's specific information or the state of the application on the server machine. It completely makes use of server resources (the server's memory) to store information.

1. Session

2. Application

Application State:-

 Application State information is available to all pages, regardless of which user requests a page.

Session State:–

 Session State information is available to all pages opened by a user during a single visit.

Magic Tables Used by Triggers in SQL Server

The tables "INSERTED" and "DELETED" are called magic tables of the SQL Server. We can not see these tables in the database. But we can access these tables from the "TRIGGER" so inserted and deleted tables are called Magic Tables in the context of a trigger. These tables are automatically created and managed by SQL Server internally to hold recently inserted, deleted and updated values during DML operations (Insert, Update and Delete) on a database table.

1. When we insert the record into the table, the magic table "INSERTED" will be created In that table the current inserted row will be available. We can access this Record in the "TRIGGER".

2. When we update a record on the table where trigger is created, the magic tables "INSERTED" and "DELETED" both will be created, the Old data of the updating record will be available in "DELETED" table and, the new data will be available in "INSERTED" table, while accessing them inside the trigger.

3. When we delete the record from the table, the magic table "DELETED" will be created In that table the current deleted row will be available. We can access this Record in the "TRIGGER".

So Magic tables are nothing but inserted and deleted which are temporary objects created by the server internally to hold recently inserted values in the case of the insert and to hold recently deleted values in the case of delete, to hold before updating values or after updating values in the case of update

These two tables, inserted and deleted, are called magic tables.

Example of Magic tables :

Step 1: Create ‘tblCustomer’ table and insert some records

CREATE TABLE [dbo].[tblCustomer](

           [empId] [int] NOT NULL,

           [emp_Name] [varchar](15) NULL,

           [emp_sal] [int] NULL

) ON [PRIMARY]

GO

insert into tblCustomer values (1, ‘Nitin’,10000)

insert into tblCustomer values (2,’ Rahul’,11000)

insert into tblCustomer values (3,’ prashant’,12000)

Step 2- Create a trigger on the tblCustomer table for the insetting new record

Step 3: Now insert one new record into the tblCustomer table to see the data within the Inserted and Deleted magic table.

Deleted magic table example :

The Deleted table holds the recently deleted or updated values.so the old updated and deleted records are inserted into the Deleted table.

MongoDB - Sort Records

To sort documents in MongoDB, you need to use sort() method. The method accepts a document containing a list of fields along with their sorting order. To specify sorting order 1 and -1 are used. 1 is used for ascending order while -1 is used for descending order.

Syntax:

>db.COLLECTION_NAME.find().sort({KEY:1})

The $sort stage has the following prototype form:

{ $sort: { <field1>: <sort order>, <field2>: <sort order> ... } }

$sort takes a document that specifies the field(s) to sort by and the respective sort order. <sort order> can have one of the following values:

1 to specify ascending order.

-1 to specify descending order.

{ $meta: "textScore" } to sort by the computed textScore metadata in descending order.

MongoDB Database Introduction

MongoDB is an open-source document database and a leading NoSQL database. MongoDB is written in C++.MongoDB is a cross-platform, document-oriented database that provides, high performance, high availability, and easy scalability. MongoDB works on the concept of database, collection, and document.

Database, so MongoDB is a NoSQL database that stores the data in the form of key-value pairs. It is an Open Source, Document Database which provides high performance and scalability along with data modeling and data management of huge sets of data in an enterprise application.MongoDB also provides the feature of Auto-Scaling. Since, MongoDB is a cross-platform database and can be installed across different platforms like Windows, Linux, etc.

History of MongoDB

MongoDB was developed by Eliot Horowitz and Dwight Merriman in the year 2007, when they experienced some scalability issues with the relational database while developing enterprise web applications at their company DoubleClick. According to Dwight Merriman, one of the developers of MongoDB, this name of the database was derived from the word humongous to support the idea of processing a large amounts of data.

The database is a physical container for collections. Each database gets its own set of files on the file system. A single MongoDB server typically has multiple databases.

Example of Data Storage in MongoDB :

Document-based storage: A Document is nothing but a data structure with name-value pairs like in JSON. It is very easy to map any custom Object of any programming language with a MongoDB Document.

example: Employee object has attributes name, EmpID, Designation, salary.

Document for Employee in MongoDB will be like :

{

            name : "Nitin Kumar",

            EmpID : 10001,

            Salary : XXXXX

}

jQuery before() method

The before() method inserts specified content in front of (before) the selected elements. To insert content after selected elements, use the after() method.

Syntax:

$(selector).before(content,function(index))

Example :

<!DOCTYPE html> <html> <head>   <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>   <script> $(document).ready(function(){     $("button").click(function(){         $("p").before("<p>Hello world!</p>");     }); });   </script> </head> <body>   <button>click here to Insert content before each p element</button>   <p>This is a paragraph....</p>   <p>This is a paragraph......</p> </body> </html>