SanjheeviBlog: 2011

Sunday, May 1, 2011

Using Stored Procedures with ASP.NET

Introduction
Stored procedures (sprocs) are generally an ordered series of Transact-SQL statements bundled into a single logical unit. They allow for variables and parameters, as well as selection and looping constructs. A key point is that sprocs are stored in the database rather than in a separate file.
Advantages over simply sending individual statements to the server include:

Referred to using short names rather than a long string of text; therefore, less network traffiic is required to run the code within the sproc.

Pre-optimized and precompiled, so they save an incremental amount of time with each sproc call/execution.

Encapsulate a process for added security or to simply hide the complexity of the database.

Can be called from other sprocs, making them reusable and reducing code size.

Parameterization

A stored procedure gives us some procedural capability, and also gives us a performance boost by using mainly two types of parameters:

Input parameters

Output parameters

From outside the sproc, parameters can be passed in either by position or reference.

Declaring Parameters

The name

The datatype

The default value

The direction

The syntax is :

@parameter_name [AS] datatype [= default|NULL] [VARYING] [OUTPUT|OUT]
Let's now create a stored procedure named "Submitrecord".

First open Microsoft SQL Server -> Enterprise Manager, then navigate to the database in which you want to create the stored procedure and select New Stored Procedure.

See the below Stored Procedure Properties for what to enter, then click OK.

Now create an application named Store Procedure in .net to use the above sprocs.

Stored Procedure.aspx page code

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server"><title>Store Procedure</title>
</head>
<body><form id="form1" runat="server"><div><asp:Label ID="Label1" runat="server" Text="ID"></asp:Label><asp:TextBox ID="TextBox1" runat="server"></asp:TextBox><br /><br /><asp:Label ID="Label2" runat="server" Text="Password"></asp:Label><asp:TextBox ID="TextBox2" runat="server"></asp:TextBox><br /><br /><asp:Label ID="Label3" runat="server" Text="Confirm Password"></asp:Label><asp:TextBox ID="TextBox3" runat="server"></asp:TextBox><br /><br /><asp:Label ID="Label4" runat="server" Text="Email ID"></asp:Label><asp:TextBox ID="TextBox4" runat="server"></asp:TextBox><br /><br /><br /><asp:Button ID="Button1" runat="server" Text="Submit Record" OnClick="Button1_Click" /></div></form>
</body>
</html>

Stored Procedure.aspx.cs page code
using System;using System.Data;using System.Configuration;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;using System.Data.SqlClient;public partial class _Default : System.Web.UI.Page
{
     DataSet ds = new DataSet();
     SqlConnection con;
     //Here we declare the parameter which we have to use in our application
     SqlCommand cmd = new SqlCommand();
     SqlParameter sp1 = new SqlParameter();
     SqlParameter sp2 = new SqlParameter();
     SqlParameter sp3 = new SqlParameter();
     SqlParameter sp4 = new SqlParameter();
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
     con = new SqlConnection("server=(local); database= gaurav;uid=sa;pwd=");
     cmd.Parameters.Add("@ID", SqlDbType.VarChar).Value = TextBox1.Text;
     cmd.Parameters.Add("@Password", SqlDbType.VarChar).Value = TextBox2.Text;
     cmd.Parameters.Add("@ConfirmPassword", SqlDbType.VarChar).Value = TextBox3.Text;
     cmd.Parameters.Add("@EmailID", SqlDbType.VarChar).Value = TextBox4.Text;
     cmd = new SqlCommand("submitrecord", con);
     cmd.CommandType = CommandType.StoredProcedure;
     con.Open();
     cmd.ExecuteNonQuery();
     con.Close();
}
}

When we run the application, the window will look like this:

After clicking the submit button the data is appended to the database as seen below in the SQL Server table record:

How to Create Stored Procedure in MSSqlServer2005

Creating Stored Procedures

Writing stored procedures has never been easy as Microsoft has almost integrated SQL Server with Visual Studio 2005. In the past most of the developers has wondered can’t we have a good editor for creating stored procedures. One of the main advantage of creating procedures in Visual Studio is it creates the basic stub for you and further more, it has inbuilt syntax checking which makes the job easier for us.
In order to create a stored procedure from Visual Studio, first you need to create a data connection from the Server Explorer and follow the below steps.
Step 1: Open Visual Studio 2005.

Step 2: Create a VB.NET / C# Windows / Web Application Project.

Step 3: Open the Server Explorer by Selecting View -> Server Explorer.

Step 4: Create a Data Connection to your server you can do this by Right Clicking on the Data Connection Tree and Selecting “Add New Connection”.

Step 5: It will Prompt for the Provider Type you can select .NET SQL Server Provider as it gives more performance.

Step 6: After giving all the credentials once the connection is active expand the database that you are having.

Step 7: Expand the Stored Procedure Tree.

Step 8: To Create a New Procedure Right Click and Select “Add New Procedure”.

Step 9: The IDE will give you a Stub where you can replace the Name of the Procedure and Arguments.

Those who are familiar with Visual Studio IDE would love to create procedures here rather then doing it in Query Analyzer or in SQL Enterprise Manager, though it doesn’t provide any fancy auto complete drop downs its still the best I believe to create stored procedures.

TIP: The Maximum number of parameters in a stored procedure is 2100.

Calling Stored Procedure

Hope everyone have used SQLCommand / OLEDB Command objects in .NET. Here we can call stored procedures in two different forms, one without using parameter objects which is not recommended for conventional development environments, the other one is the familiar model of using Parameters.
In the first method you can call the procedure using Exec command followed by the procedure name and the list of parameters, which doesn’t need any parameters.

Example:

Dim SQLCon As New SqlClient.SqlConnection
SQLCon.ConnectionString = "Data Source=Server;User ID=User;Password=Password;"
SQLCon.Open()

Calling Stored Procedures with Exec command

SQLCmd.CommandText = "Exec SelectRecords 'Test', 'Test', 'Test'"
SQLCmd.Connection = SQLCon 'Active Connection

The second most conventional method of calling stored procedures is to use the parameter objects and get the return values using them. In this method we need to set the “SQLCommandType” to “StoredProcedure” remember you need to set this explicitly as the the default type for SQLCommand is SQLQuery”.
Here is an example to call a simple stored procedure.

Example - I (A Stored Procedure Returns Single Value)

In order to get XML Results from the Stored Procedure you need to first ensure that your stored procedure is returning a valid XML. This can be achieved using FOR XML [AUTO | RAW | EXPLICIT] clause in the select statements. You can format XML using EXPLICIT Keyword, you need to alter your Query accordingly

'Set up Connection object and Connection String for a SQL Client
Dim SQLCon As New SqlClient.SqlConnection
SQLCon.ConnectionString = "Data Source=Server;User ID=User;Password=Password;"
SQLCon.Open()

SQLCmd.CommandText = "SelectRecords" ' Stored Procedure to Call
SQLCmd.CommandType = CommandType.StoredProcedure 'Setup Command Type
SQLCmd.Connection = SQLCon 'Active Connection

The procedure can be called by adding Parameters in at least two different methods, the simplest way to add parameters and respective values is using

SQLCmd.Parameters.AddWithValue("S_Mobile", "Test")
SQLCmd.Parameters.AddWithValue("S_Mesg", "Test")
SQLCmd.Parameters.AddWithValue("LastMsgID", "")

In this above method, you doesn’t necessarily know the actually data type that you had in your procedure and all parameters are validated according to the type declared in your procedure but only thing is all the validations will occur in SQL and not in your client code.
We still need to declare the last parameter as Output and we need to do that explicitly as the default type is Input. So here we are going to declare the last parameter as Output by
SQLCmd.Parameters("LastMsgID").Direction = ParameterDirection.Outputfs

If you want to declare parameters properly then you need to use the below method to add all the parameters with its data type, direction. Also if you are using stored procedures to update all the rows in a dataset then you need to declare parameters in the below fashion and give SouceColumn value as the Column name in the DataTable.

SQLCmd.Parameters.Add(New SqlClient.SqlParameter("S_Mobile", SqlDbType.VarChar, 10, ParameterDirection.Input, False, 30, 0, "", DataRowVersion.Current, "91000000000"))

SQLCmd.Parameters.Add(New SqlClient.SqlParameter("S_Mesg", SqlDbType.VarChar, 160, ParameterDirection.Input, False, 30, 0, "", DataRowVersion.Current, "Calling Stored Procedures from VB.NET"))

SQLCmd.Parameters.Add(New SqlClient.SqlParameter("LastMsgID", SqlDbType.BigInt, 5, ParameterDirection.Output, False, 5, 0, "", DataRowVersion.Current, 0))

' The Above Procedure has two input parameters and one output parameter you can notice the same in the “Parameter Direction”
SQLCmd.ExecuteNonQuery() 'We are executing the procedure here by calling Execute Non Query.

MsgBox(SQLCmd.Parameters("LastMsgID").Value) 'You can have the returned value from the stored procedure from this statement. Its all similar to ASP / VB as the only difference is the program structure.

Example - II (Stored Procedure to get Table Result Set)

In order to get the result sets from the stored procedure, the best way is to use a DataReader to get the results. In this example we are getting the results from the Stored Procedure and filling the same in a DataTable.

Here we need to additionally declare a SQLDataReader and DataTable

Dim SQLDBDataReader As SqlClient.SqlDataReader
Dim SQLDataTable As New DataTable

SQLCmd.CommandText = "GetAuthors"
SQLCmd.CommandType = CommandType.StoredProcedure
SQLCmd.Connection = SQLCon
SQLCmd.Parameters.Add(New SqlClient.SqlParameter("AuthorName", SqlDbType.VarChar, 100, ParameterDirection.Input, False, 30, 0, "", DataRowVersion.Current, "Y%")) SQLDBDataReader = SQLCmd.ExecuteReader() SQLDataTable.Columns.Add("AuthorName", GetType(Int32), "") SQLDataTable.Columns.Add("AuthorLocation", GetType(String), "")

Dim FieldValues(1) As Object 'A Temporary Variable to retrieve all columns in a row and fill them in Object array

While (SQLDBDataReader.Read)
SQLDBDataReader.GetValues(FieldValues)
SQLDataTable.Rows.Add(FieldValues)

End While

Example - III (Calling Simple Stored Procedure to get XML Result Set)

CREATE PROCEDURE GetRecordsXML (@AuthorName varchar(100))
AS

Select Author_ID, Author_Name, Author_Location Where Author_Name LIKE @AuthorName from Authors FOR XML AUTO

RETURN

When you use the above procedure you can get XML Results with TableName as Element and Fields as Attributes

Dim SQLXMLReader As Xml.XmlReader

SQLCmd.CommandText = "GetAuthorsXML"
SQLCmd.CommandType = CommandType.StoredProcedure
SQLCmd.Connection = SQLCon
SQLCmd.Parameters.Add(New SqlClient.SqlParameter("AuthorName", SqlDbType.VarChar, 100, ParameterDirection.Input, False, 30, 0, "", DataRowVersion.Current, "Y%"))
SQLDBDataReader = SQLCmd.ExecuteReader()

SQLXMLReader = SQLCmd.ExecuteXmlReader()
While (SQLXMLReader.Read)
MsgBox(SQLXMLReader.ReadOuterXml)
End While

You can further process this XML or write XSL to display results in a formatted manner. But in order to get formatted XML Results, we need to use EXPLICIT case which we can see in our next article on SQL Queries & XML.

Monday, April 11, 2011

C# .NET interview questions

Are private class-level variables inherited? - Yes, but they are not accessible, so looking at it you can honestly say that they are not inherited. But they are.
Why does DllImport not work for me? - All methods marked with the DllImport attribute must be marked as public static extern.

Why does my Windows application pop up a console window every time I run it? - Make sure that the target type set in the project properties setting is set to Windows Application, and not Console Application. If you’re using the command line, compile with /target:winexe, not /target:exe.
Why do I get an error (CS1006) when trying to declare a method without specifying a return type? - If you leave off the return type on a method declaration, the compiler thinks you are trying to declare a constructor. So if you are trying to declare a method that returns nothing, use void. The following is an example: // This results in a CS1006 error public static staticMethod (mainStatic obj) // This will work as wanted public static void staticMethod (mainStatic obj)
Why do I get a syntax error when trying to declare a variable called checked? - The word checked is a keyword in C#.
Why do I get a security exception when I try to run my C# app? - Some security exceptions are thrown if you are working on a network share. There are some parts of the frameworks that will not run if being run off a share (roaming profile, mapped drives, etc.). To see if this is what’s happening, just move the executable over to your local drive and see if it runs without the exceptions. One of the common exceptions thrown under these conditions is System.Security.SecurityException. To get around this, you can change your security policy for the intranet zone, code group 1.2, (the zone that running off shared folders falls into) by using the caspol.exe tool.
Why do I get a CS5001: does not have an entry point defined error when compiling? - The most common problem is that you used a lowercase ‘m’ when defining the Main method. The correct way to implement the entry point is as follows: class test { static void Main(string[] args) {} }
What optimizations does the C# compiler perform when you use the /optimize+ compiler option? - The following is a response from a developer on the C# compiler team: We get rid of unused locals (i.e., locals that are never read, even if assigned). We get rid of unreachable code. We get rid of try-catch with an empty try. We get rid of try-finally with an empty try. We get rid of try-finally with an empty finally. We optimize branches over branches: gotoif A, lab1 goto lab2: lab1: turns into: gotoif !A, lab2 lab1: We optimize branches to ret, branches to next instruction, and branches to branches.
What is the syntax for calling an overloaded constructor within a constructor (this() and constructorname() does not compile)? - The syntax for calling another constructor is as follows: class B { B(int i) { } } class C : B { C() : base(5) // call base constructor B(5) { } C(int i) : this() // call C() { } public static void Main() {} }
What is the equivalent to regsvr32 and regsvr32 /u a file in .NET development? - Try using RegAsm.exe. Search MSDN on Assembly Registration Tool.
What is the difference between a struct and a class in C#? - From language spec: The list of similarities between classes and structs is as follows. Longstructs can implement interfaces and can have the same kinds of members as classes. Structs differ from classes in several important ways; however, structs are value types rather than reference types, and inheritance is not supported for structs. Struct values are stored on the stack or in-line. Careful programmers can sometimes enhance performance through judicious use of structs. For example, the use of a struct rather than a class for a Point can make a large difference in the number of memory allocations performed at runtime. The program below creates and initializes an array of 100 points. With Point implemented as a class, 101 separate objects are instantiated-one for the array and one each for the 100 elements.

My switch statement works differently than in C++! Why? - C# does not support an explicit fall through for case blocks. The following code is not legal and will not compile in C#:

switch(x)
{
 case 0: // do something
 case 1: // do something as continuation of case 0
 default: // do something in common with
  //0, 1 and everything else
 break;
}

To achieve the same effect in C#, the code must be modified as shown below (notice how the control flows are explicit):

class Test
{
 public static void Main() {
  int x = 3;
  switch(x)
  {
   case 0: // do something
   goto case 1;
   case 1: // do something in common with 0
   goto default;
   default: // do something in common with 0, 1, and anything else
   break;
  }
 }
}

Is there regular expression (regex) support available to C# developers? - Yes. The .NET class libraries provide support for regular expressions. Look at the System.Text.RegularExpressions namespace.

Is there any sample C# code for simple threading? - Yes:

using System;
using System.Threading;
class ThreadTest
{
 public void runme()
 {
  Console.WriteLine("Runme Called");
 }
 public static void Main(String[] args)
 {
  ThreadTest b = new ThreadTest();
  Thread t = new Thread(new ThreadStart(b.runme));
  t.Start();
 }
}

Is there an equivalent of exit() for quitting a C# .NET application? - Yes, you can use System.Environment.Exit(int exitCode) to exit the application or Application.Exit() if it’s a Windows Forms app.
Is there a way to force garbage collection? - Yes. Set all references to null and then call System.GC.Collect(). If you need to have some objects destructed, and System.GC.Collect() doesn’t seem to be doing it for you, you can force finalizers to be run by setting all the references to the object to null and then calling System.GC.RunFinalizers().

Is there a way of specifying which block or loop to break out of when working with nested loops? - The easiest way is to use goto:

using System;
class BreakExample
{
 public static void Main(String[] args) {
  for(int i=0; i<3; i++)
  {
   Console.WriteLine("Pass {0}: ", i);
   for( int j=0 ; j<100 ; j++ )
   {
    if ( j == 10)
     goto done;
    Console.WriteLine("{0} ", j);
   }
   Console.WriteLine("This will not print");
  }
  done:
   Console.WriteLine("Loops complete.");
 }
}

Is it possible to restrict the scope of a field/method of a class to the classes in the same namespace? - There is no way to restrict to a namespace. Namespaces are never units of protection. But if you’re using assemblies, you can use the ‘internal’ access modifier to restrict access to only within the assembly.

SanjheeviBlog

Sunday, May 1, 2011

Using Stored Procedures with ASP.NET

How to Create Stored Procedure in MSSqlServer2005

Monday, April 11, 2011

C# .NET interview questions

Saturday, April 9, 2011

Top 50 Interview Questions and their answers for Freshers