Category: Programming

Creating System StoredProcedures in SQL Server

What is System StoredProcedures

A system StoredProcedure is a general stored procedure which started with ‘sp_’. System StoredProcedures are stored into the master database in SQL Server.

Requirement of System StoredProcedure

There are instances when you are required to perform some operations on the different databases, like finding the dependency of the table (sp_depends), you use the system StoredProcedure. There are many System StoredProcedure already provided by the SQL Server. some of the most commonly used are:

  • sp_who
  • sp_depends
  • sp_help
  • sp_helptext

Each of the above provide some pre-defined functionality, but there are chances where you want your own functionality. In that case you can create your own System StoredProcedures. Just create a StoredProcedure with prefix ‘sp_’ in the master database and you are ready to go. Here I am showing one I have created for my ease.

Example

[code language=”SQL” light=”true”]
— ======================================
— Author:      Anant Anand Gupta
— Date:        2010-05-10
— Description: Return the number of
—              records in the Table
— ======================================</p>
<p>USE master
GO</p>
<p>IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_GetCount]’) AND type IN (N’P’, N’PC’))
DROP PROCEDURE [dbo].[sp_GetCount]
GO</p>
<p>CREATE PROCEDURE [dbo].[sp_GetCount] @TableName NVARCHAR(776)
AS
BEGIN
SET NOCOUNT ON
DECLARE @str NVARCHAR(1000)
SET @str = ‘Select Count(*) [Record Count] From ‘ + @TableName
EXEC (@str)
END
[/code]

Now you can execute the stored procedure in any database you want like this:

[code language=”SQL” light=”true” toolbar=”false”]
EXEC sp_GetCount ‘<TableName>’
[/code]

 

Extras

You can also create a keyboard shortcut for the above StoredProcedure to use quickly in between your SQL Code to find out the number of records in the selected table. Go to Tools -> Options -> Keyboard in SQL Server Management Studio. Select the text box in front of the predefined shortcuts which are not used. Enter the name of your StoredProcedure in the TextBox.

Click OK.

Open New Query Window (modifications are not registered with the windows already opened) for the required database. Enter the desired TableName and select the text and press your shortcut (Ctrl + 5 in my case). Here is your result

Enjoy and be more productive at your workplace !!!

How To: Provide Save File Dialog for an Image Request in ASP.Net

It is the default behavior of the web browsers, when you click on the image link, to open the image and display it over there. There are instances when you might want your users to remain on the page and simply download and save the image after clicking on the image link.

This functionality can be achieved in ASP.Net. Here I am going to show how to:

Create a blank ASP.Net Web Application. Now you have to create a folder to store the image to be downloaded and add and image to it.

Your solution should look similar to this:

Open the Default.aspx and create a HyperLink to the image like this:

<asp:HyperLink ID="hlDownloadImage" runat="server" NavigateUrl="~/Images/image001.jpg?Action=Download">Download Image</asp:HyperLink>

Note that the image URL is having additional parameter “Action” with value “Download”. We will see the utilization of the same in a moment.

now go and add a new file of Type ASP.Net Module ImageDownload.cs to the solution and modify the code as below:

public class ImageDownload : IHttpModule
{
    #region IHttpModule Members
    public void Dispose() { }

    public void Init(HttpApplication context)
    {
        context.BeginRequest += new EventHandler(context_BeginRequest);
    }
    #endregion

    public void context_BeginRequest(object sender, EventArgs e)
    {
        HttpApplication app = (HttpApplication)sender;
        HttpRequest req = app.Request;
        HttpResponse res = app.Response;
        if (req.Params["Action"] != null && req.Params["Action"].ToString() == "Download")
        {
            string path = req.AppRelativeCurrentExecutionFilePath;
            res.ContentType = "image/jpeg";
            res.AppendHeader("Content-Disposition", "attachment; filename=" + Path.GetFileName(path));
            res.TransmitFile(app.Server.MapPath(path));
            res.End();
        }
    }
}

Modify the web.config file to include following in <httpModules> section:

<add name="ImageDownload" type="WebUploadManager.ImageDownload"/>

You are done. Run the application and click on the Link for the image and you will see the Run/Save dialog. Try removing the Action parameter from the NavigationURL of the hyperlink; image will be displayed on screen as the default behavior of the browser.

Design Patterns

What is a Design Pattern?

If you are here reading this post you might be looking a knowledge on Design Patterns and the first question which comes on anyone’s mind is “What is a Design Pattern?”. Lets answer this question in your own way.

You are person who know the syntax of a programming language and you are able to successfully convert any requirement into the code. One fine morning you reach office and your senior tells you about a new project requirement. You understood the requirement and have decided whats need to be done and what are the object of classes required to achieve this. But internally, whole the time from beginning to the end of the development you always know that there can be a better way to achieve this, and of course you search for other solutions to. Whatever you decide to implement you will finish off the task, but was that the best solution for the requirement? When this question comes in to you mind, the answer can be only given in terms of Design Patterns.

A design Pattern is nothing but a conceptual way to represent a reusable solution for a typical problem.

Here is a list of all known Design Patterns:

  1. Strategy Design Pattern
  2. Decorator Design Pattern
  3. Factory Design Pattern
  4. Observer Design Pattern
  5. Chain of Responsibility Design Pattern
  6. Singleton Design Pattern
  7. Flyweight Design Pattern
  8. Adapter Design Pattern
  9. Facade Design Pattern
  10. Template Design Pattern
  11. Builder Design Pattern
  12. Iterator Design Pattern
  13. Composite Design Pattern
  14. State Design Pattern
  15. Proxy Design Pattern
  16. Command Design Pattern
  17. Mediator Design Pattern
  18. Abstract Factory Design Pattern
  19. Prototype Design Pattern
  20. Bridge Design Pattern
  21. Interpreter Design Pattern
  22. Memento Design Pattern
  23. Visitor Design Pattern
  24. Circular Design Pattern
  25. Double Buffer Design Pattern
  26. Recycle Bin Design Pattern
  27. Model-View-Controller Design Pattern
  28. Model-View-View-Model Design Pattern

I will updating the details of each kind of design patterns as soon as they are ready to be posted.

Visual Studio 2005 SP1: didn’t pass the digital signature policy error

To solve this follow the work around:

Work Arround 1:

  1. Start Menu, click Run, type > control admintools and then click OK.
  2. Double-click Local Security Policy.
  3. Click Software Restriction Policies.
    Note: If no software restrictions are listed, right-click Software Restriction Policies, and then click Create New Policy.
  4. Under Object Type, double-click Enforcement.
  5. Click All users except Local Administrators, and then click OK.
  6. Restart the computer.

Install SP1 with no errors.

Work Arround 2:

There is a fix from Microsoft to resolve the issue. Please visit the following knowledge base article:

http://support.microsoft.com/kb/925336 or Download the Update for Windows Server 2003 (KB925336).

Note: Revert the settings after the installation is over.

Server.MapPath

In general whenever we need to get physical location of the file in ASP.Net Application, we use Server.MapPath. This is the most commonly adopted method. If you want the file to be located with reference to the path of the current WebPage, then the implementation holds good, but, in case you have to always refer the file from the application root, this method gives you different results. Take the following scenario, where the application directory structure is as follows:

  • Root
    • Data
      • Data.XML
    • ClassA.cs (uses Server.MapPath(“\Data\Data.XML”))
    • ClassB.cs (uses ClassA to get the XML file contents)
    • SubDir
      • ClassC.cs (uses ClassA to get the XML file contents)

In the above scenario the ClassC will fail to retrive the contents in case of the WebApplication is hosted in a virtual directory. The application will work fine if it is a website. So the implementation will not show any errors when we run the application from the Visual Studio. to make it more generic we can replace the Server.MapPath with

System.Web.Hosting.HostingEnvironment.ApplicationPhysicalPath + @”Data\Data.XML”.