SQL Server Stored Procedures

This tutorial gives an overview of how to use SQL Server Stored Procedures in a web application.

Stored Procedures are compiled SQL code stored in the database. Calling stored procedures as opposed to sending over query strings improves the performance of a web application. Not only is there less network traffic since only short commands are sent instead of long query stings, but the execution of the actually code itself also improves. The reason is because a stored procedure is already compiled ahead of time. Stored procedures are also cached by SQL Server when they are run to speed things up for subsequent calls.

Other than performance, stored procedures are also helpful because they provide another layer of abstraction for your web application. For instance, you can change a query in a stored procedure and get different results without having to recompile your objects. Using stored procedures also makes your objects cleaner, and SQL Server’s convenient backup tool makes it easy to back them up.

To create a stored procedure, we can use the SQL Query Analyzer or the SQL Server Enterprise Manager. I find it easier to use the query analyzer because it allows you to test your query before putting it in a procedure and then tests the procedure after it has been created. However, enterprise manager provides an easy interface for viewing all of the existing stored procedures and editing them.

Suppose we have the following query that retrieves messages from a given thread:

SELECT message_id,
       thread_id,
       user_id,
       first_names,
       last_name,
       email,
       subject,
       body,
       date_submitted,
       category_name,
       category_id,
       last_edited
FROM    message_view
WHERE thread_id = @iThreadID
ORDER BY date_submitted asc

To put this query in a stored procedure using the query analyzer, we simply have to give it a name (GetThreadMessages) and tell it what inputs (@iThreadID int) it requires. The name of the procedure goes in the create statement, then come the comma separated inputs, and finally the AS keyword followed by the procedure. The resulting statement would look like this:


Creating the GetThreadMessages procedure using the query analyzer

Then, to test out the procedure in the query analyzer, we just run it with comma separated inputs (there’s only one input in this case).


Executing GetThreadMessages procedure with thread_id 1

Finally, if we want to view it in the enterprise manager, we just go to the stored procedures section, right click on it, and choose properties. Here we can edit it and save our changes. The other options when you right click it allow you to rename it, delete it, and copy it much like you could with a file. Opening up the properties in enterprise manager looks like this.


GetThreadMessages properties under enterprise manager

Now that we’ve made our stored procedure, we just need to execute it from our object model. Executing it from our object model is almost the same as executing a normal query. The only differences are instead of providing the query, we provide the name of the stored procedure, and we also specify that it is a stored procedure. The c# code to do this would look like this (sorry, have to leave out the connection string):

SqlConnection myConnection = new SqlConnection(strConnection);
SqlCommand myCommand  = new SqlCommand("GetThreadMessages", myConnection);
myCommand.CommandType = CommandType.StoredProcedure;

Don’t forget to bind the input variables like you normally would:

SqlParameter ThreadID = new SqlParameter("@iThreadID", SqlDbType.Int, 4);
ThreadID.Value        = iThreadID;
myCommand.Parameters.Add(ThreadID);

Hope you find this somewhat useful.

.Net Interview Questions and answers

.NET Architecture

The programming model for the .NET platform.

The .NET Framework provides a managed execution environment, simplified development and deployment, and integration with a wide variety of programming languages.

The .NET Framework has two key parts:

1. The .NET Framework class library is a comprehensive, object-oriented collection of reusable types that you can use to develop applications. The .NET Framework class library includes ADO.NET, ASP.NET and Windows Forms.
2. The common language runtime (CLR) is the core runtime engine for executing applications in the .NET Framework. You can think of the CLR as a safe area - a "sandbox" - inside of which your .NET code runs. Code that runs in the CLR is called managed code.

ADO.NET

The data access component for the .NET Framework.

ADO.NET leverages the power of XML to provide disconnected access to data. ADO.NET is made of a set of classes that are used for connecting to a database, providing access to relational data, XML, application data and retrieving results.

ADO.NET is made of a set of classes that are used for connecting to a database, providing access to relational data, XML, application data, and retrieving results.

ASP.NET

The component of the Microsoft .NET Framework used for building, deploying, and running Web applications and distributed applications.

Assembly

A compiled representation of one or more classes.

Each assembly is self-contained, that is, the assembly includes the metadata about the assembly as a whole.

Assemblies can be private or shared:

* Private assemblies, which are used by a limited number of applications, are placed in the application folder or one of its subfolders. For example, even if the client has two different applications that call a private assembly named formulas, each client application loads the correct assembly.
* Shared assemblies, which are available to multiple client applications, are placed in the Global Assembly Cache (GAC). Each shared assembly is assigned a strong name to handle name and version conflicts.

Assembly Cache

A code cache used for side-by-side storage of assemblies.

The assembly cache is made of two parts:

* The global assembly cache contains assemblies that are explicitly installed to be shared among many applications on the computer.
* The download cache is a directory inside the assembly cache that stores code downloaded from the Internet or intranet site and isolated to the application that caused the download. This isolation prevents code downloaded on behalf of one application from affecting other applications.

Code Access Security (CAS)

The component of the Microsoft .NET Framework used for building, deploying and running Web applications and distributed applications.

Common Language Runtime (CLR)

The core runtime engine in the Microsoft .NET Framework. The CLR supplies services such as cross-language integration, code access security, object lifetime management and debugging support. Applications that run in the CLR are sometimes said to be running "in the sandbox."

Download Cache

The subdirectory in assembly cache that stores code downloaded from Internet or intranet sites, isolated to the application that caused the download. This isolation prevents code downloaded on behalf of one application from affecting other applications.

DTC (Distributed Transaction Coordinator)

In Microsoft Windows NT, Windows 2000, Windows XP and the Windows Server 2003 family, the DTC is a system service that is part of COM+ services.

COM+ components that use DTC can enlist .NET connections in distributed transactions. This makes it possible to scale transactions from one to many computers without adding special code.

Expose

To host and make available a Web service so that it can be used by other applications or services.

Garbage Collection

A process in the CLR that automatically frees allocated objects when there are no longer any outstanding references to them. The developer does not need to explicitly free memory assigned to an object.

Global Assembly Cache (GAC)

The part of the assembly cache that stores assemblies specifically installed to be shared by many applications on the computer. Applications deployed in the global assembly cache must have a strong name to handle name and version conflicts.

Isolation Level

An isolation level represents a particular locking strategy employed in the database system to improve data consistency. The higher the isolation level, the more complex the locking strategy behind it.

The isolation level provided by the database determines whether a transaction will encounter defined behaviors in data consistency.

The American National Standards Institute (ANSI) defines four isolation levels:

1. Read uncommitted (0)
2. Read committed (1)
3. Repeatable read (2)
4. Serializable (3)

JIT Compiler

The "just-in-time" compilation that converts Microsoft intermediate language (MSIL) into machine code at the point when the code is required at run time.

Locking Level

Locking is a database operation that restricts a user from accessing a table or record. Locking is used in situations when more than one user might try to use the same table at the same time. By locking the table or record, only one user at a time can affect the data.

Managed Code

Code executed and managed by the .NET Framework, specifically by the CLR. Managed code must supply the information necessary for the CLR to provide services such as memory management and code access security.

Microsoft .NET Framework

The Microsoft .NET Framework has two key parts:

1. The .NET Framework class library is a comprehensive, object-oriented collection of reusable types that you can use to develop applications. The .NET Framework class library includes ADO.NET, ASP.NET and Windows Forms.
2. The common language runtime (CLR) is the core runtime engine for executing applications in the .NET Framework. You can think of the CLR as a safe area - a "sandbox" - inside of which your .NET code runs. Code that runs in the CLR is called managed code.

Microsoft Intermediate Language (MSIL)

A CPU-independent set of instructions that can be converted to native code. MSIL includes instructions for loading, storing, initializing and calling methods on objects, as well as instructions for arithmetic and logical operations, control flow, direct memory access, exception handling and other operations.

Namespace

A logical naming scheme for grouping related types.

The .NET Framework uses a hierarchical naming scheme for grouping types into logical categories of related functionality, such as the ASP.NET technology or remoting functionality. Design tools can use namespaces to make it easier for developers to browse and reference types in their code.

A single assembly can contain types whose hierarchical names have different namespace roots, and a logical namespace root can span multiple assemblies.

In the .NET Framework, a namespace is a logical design-time naming convenience, whereas an assembly establishes the name scope for types at run time.

No-touch Deployment

A feature of the .NET Framework, similar to browser-based application deployment, that lets clients download the assemblies they need from a remote web server.

The first time an assembly is referenced, it is downloaded to the download cache on the client and executed. After that, when the client accesses the application, the application checks the server to find out whether any assemblies have been updated. Any new assemblies are downloaded to the download cache on the client, refreshing the application without any interaction with the end user.

Side-by-side Execution

The ability to install and use multiple versions of the same assembly in isolation at the same time. Allowing different versions of assemblies to coexist and to execute simultaneously on the same computer enables robust versioning.

Simple Object Access Protocol (SOAP)

A simple, XML-based protocol for exchanging structured data and type information over the Internet. SOAP is currently the de facto standard for XML messaging.

SOAP consists of:

* An envelope that defines a framework for describing message structure.
* A set of encoding rules for expressing instances of application-defined data types.
* A convention for using SOAP with HTTP.

Strong Name

A name that consists of an assembly's text name, version number and culture information (if provided), with a public key and a digital signature generated over the assembly.

Assemblies with the same strong name should be identical. Strong names provide a strong integrity check, because the .NET Framework security checks to be sure that the contents of the assembly have not been changed since it was built.

Universal Description, Discover, and Integration (UDDI)

A platform-independent framework that provides a way to locate and register Web services on the Internet.

The UDDI specification calls for three elements, similar to a telephone book:

1. White pages; which provide business contact information
2. Yellow pages; which organize Web services into categories (for example, credit card authorization services)
3. Green pages; which provide detailed technical information about individual services.

The UDDI specification also contains an operational registry.

Unicode

A standard that software can use to support multi-lingual character sets.

The .NET Framework uses UTF-16 Unicode encoding to represent characters. .NET applications use encoding and decoding to map character representations between Unicode and non-Unicode formats. The .NET Framework also provides UTF-8, ASCII, and ANSI/ISO encodings.

Unmanaged Code

Code that is executed directly by the operating system, outside of the CLR.

Unmanaged code includes all code written before the .NET Framework was introduced. This includes code written to use COM, native Win32 and Visual Basic 6. Because it does not run inside the .NET environment, unmanaged code cannot make use of any .NET managed facilities.

Web Forms

An ASP.NET feature that can be used to create the user interface for Web applications.

The Web Forms page works as a container for the static text and controls you want to display. The programming logic for the Web Forms page resides in a separate file from the user interface file. This file is referred to as the "code-behind" file and has an ".aspx.vb" or ".aspx.cs" extension, depending on whether the code-behind file was written in Visual Basic or Visual C#.

Web Services

A set of modular applications or "services" that can be accessed within a network (e.g., the Internet, an intranet, or extranet) through a standard interface, typically XML.