Friday, October 19, 2007

Add new document to Document Library in SharePoint using C#

          We are exploring SharePoint 2007 from last few days. Here is the code for adding new document to document library using C#.  I have created a page with a file upload control and a button. When user clicks on button the selected document will be uploaded to specific document library. You need to add reference to Microsoft.SharePoint DLL to make this code running.

if (FileUpload1.PostedFile != null)
            if(FileUpload1.PostedFile.ContentLength > 0)
                   System.IO.Stream strm =

                   byte[] byt = new byte[
Convert.ToInt32 (

                   strm.Read(byt, 0, Convert.ToInt32


                // Open site where document library is created.
                using (SPSite objSite = new SPSite
                    using (SPWeb objWeb = objSite.OpenWeb())
                        SPFolder mylibrary = objWeb.Folders["Name of
                                                     Document Library"

                        // Set AllowUnsafeUpdates = true to avoid
                        // security error
                        objWeb.AllowUnsafeUpdates = true;

                               (FileUpload1.PostedFile.FileName), byt);

     Above code first reads the uploaded file in Stream and than uploads it on document library.

Happy Programming !!!

Friday, October 12, 2007

Add aspx page to SharePoint 2007

            This post will show how to create aspx (web page) page to SharePoint 2007. You can find  a really good article to add custom page in SharePoint 2007 site. I have shown one approach describe in above article.

            Create a new Web Application Project from Visual Studio 2005. You need to download web application project extension for this. Open Visual Studio 2005 and select Web Application Project as shown below,


Fig - (1) Create new Web Application

            Name the project "FirstWebProject". Delete Default.aspx page and add new Web Page and name it to "FirstCustomePage.aspx".  You can design this page as you want. Once you are done with designing run the application once to make sure that there is no error.

            Now go to the file path where you have created the Web Application. Here in my case as you can see in figure - (1) file path is "c:\chirag\VortalProject". You have to copy the DLL file from web application's BIN folder to share point site's BIN folder. To Find BIN folder for your share point site, go to IIS and select your share point site. Right click on the site name and click on Open.


Fig - (2) Locate BIN Directory for SharePoint Site.

          This will open physical location where you site resides. Copy web application's DLL file (In my case "FirstWebProject.dll") in BIN directory of SharePoint site.

          Now create a new folder at same level where your SharePoint site's BIN directory resides. Name is "CustomePage". Add the "FirstCustomePage.aspx" page to "CustomPage" folder.

         Open Web.Config file of Share Point site and copy safe control tag shown below to Web.Config file.

<SafeControl Assembly="FirstWebProject" Namespace="FirstWebProject" TypeName="*" />

Fig - (3) Safe Control Tag.

         If you have chosen different namespace and assembly names than you have to use your name instead of this. You have completed 90% of the task.

          The last thing is, you have to add <trustLevel> tag in share point sites's web.Config file. You need to  set trust level to "WSS_Medium"

<trustLevel name="WSS_Medium" policyFile="C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\config\wss_mediumtrust.config" />

Fig - (4) Setting Trust Level.

          Here you have completed all the required configurations !!!! Now open your share point site in browser and check for newly added page. In our case the link will be http://ServerName:PortNumber/CustomePage/FirstCustomePage.aspx

         If you have done all the changes as mentioned above you can see your page running fine in browser.

Happy Programming !!! 

Page Ghosting & UnGhosting And effect of PageGhosting on Performance in SharePoint 2007

           The WSS team created a virtual path provider named SPVirtualPathProvider that is integrated into every Web application. The SPVirtualPathProvider class is integrated into the ASP.NET request handling infrastructure by the SPRequestModule. More specifically, the SPRequestModule component contains code to register the SPVirtualPathProvider class with the ASP.NET Framework as it does its work to initialize a Web application.


Fig - (1) Execution flow for SharePoint pages. 

Page Ghosting and UNGhosting

             When a page instance is initially provisioned from a page template, WSS doesn’t need to store a copy of it in the content database because WSS can load the page template from the file system of the Web server and use it to process any request for an uncustomized page instance. Therefore, you can say that page ghosting describes the act of processing a request for an uncustomized page instance by using a page template loaded into memory from the file system of the front-end Web server.

             Page ghosting is valuable because it eliminates the need to transfer the contents of a page definition file from the SQL Server computer with the content database to the front-end Web server computer. Page ghosting also makes it possible to process the home pages for thousands of different

sites by using a single page template that is compiled into an assembly DLL and loaded into memory in the IIS worker process just once per Web application. Both of these optimizations are key factors in the scalability of WSS in high-traffic environments running thousands or tens of thousands of sites.

            When you modify a page and save a customized version of it in the content database using SharePoint Designer, you eliminate the possibility of page ghosting. Instead, the provided SPVirtualPathProvider must retrieve the customized version of the page from the content database, as shown in Figure 2-6. For this reason, customized pages are sometimes referred to as unghosted pages.

            Now that you understand how WSS processes requests for both ghosted and unghosted pages, you should observe the important role that is played by the SPVirtualPathProvider. It is the SPVirtualPathProvider that determines whether the page being requested has been customized. The SPVirtualPathProvider makes the decision whether to process a page as a ghosted or an unghosted page.

             As a developer, your initial reaction to this might be to question why customized pages are processed in no-compile mode. Your instincts likely tell you that compiled pages run faster than no-compile pages. However, no-compile pages can be more efficient and more scalable in certain scenarios. This is especially true in a large WSS environment where the number of customized pages can reach into the thousands or tens of thousands.

              No-compile pages can be loaded into memory and then unloaded in a manner that is not possible for compiled pages because the .NET Framework doesn’t really support the concept of unloading an assembly DLL from memory. The closest equivalent would be to recycle the current Windows process or the current .NET AppDomain. However, this type of recycling involves unloading all assembly DLLs from memory, not just those assembly DLLs that haven’t been used recently. Furthermore, the .NET Framework places an upper limit on the number of assembly DLLs that can be loaded into a .NET AppDomain.

              No-compile pages provide higher levels of scalability because they do not require loading new assembly DLLs or managed classes into memory. Instead, the processing of no-compile pages involves loading control trees into memory. WSS can manage the memory usage for the control trees associated with customized pages more efficiently because they are not compiled into assembly DLLs. For example, once WSS has finished processing a customized page, it can unload the page’s control tree to free up memory for other purposes. Furthermore, no-compile pages eliminate the need to go through the compilation process, which actually provides faster response times for pages upon first access.

NOTE: Reference taken form Inside Microsoft Windows SharePoint Service 3.0

Thursday, September 27, 2007

Parameter Sniffing and SQL Server

        Today I came to know about an interesting point "Parameter Sniffing". I search on net and found some good material and facts about it. First, what is parameter sniffing? Below is the definition which I got from here.

"Parameter sniffing refers to a process whereby SQL Server's execution environment "sniffs" the current parameter values during compilation or recompilation, and passes it along to the query optimizer so that they can be used to generate potentially faster query execution plans. The word "current" refers to the parameter values present in the statement call that caused a compilation or a recompilation."

        When a stored procedure is created, a normalized query tree for the procedure is saved in the SYSPROCEDURES system table. The normalized query tree contains the referenced table/view and column information. The process of normalization parses the SQL statements into a more efficient format and resolves all referenced objects into their internal representations. During this process, table names, for example, are resolved into their object IDs and column names are resolved into column IDs. When the procedure is subsequently executed, the tree is retrieved from SYSPROCEDURES, and based on it, an optimized execution plan is created and stored in the procedure cache.

        For stored procedures that accept parameters that can be used in the WHERE clause, the execution plan in the procedure cache is the optimized path to the data (based on the parameters given during the first execution of the procedure). For such procedures, if the parameters can be significantly different for each execution of the procedure, it is probably worth exploring the use of the WITH RECOMPILE option during execution or even during the creation of the procedure. This does add the overhead of having to generate the execution plan for each execution of the procedure.
        The additional execution plans stay in cache until they are paged out, and could cause one execution to be very different from another. A user has no control or knowledge of which plan will be used. This can sometimes explain unexpectedly different execution times for the same procedure given the same data and parameters.

You can get more information on parameter sniffing at link shown below.




Happy Programming !!

HTTP could not register URL http://+:8000/. Your process does not have access rights to this namespace (see for details)

        You may find this error when you try to register WCF service host using HTTP binding in windows vista. I had the same problem and I found the perfect solution here.

Saturday, September 01, 2007

Value does not fall within the expected range exception while using ReportDataSource

           Here is my code which produce "Value does not fall within the expected range" exception. 

 ReportDataSource objReportDataSource 
              = new ReportDataSource("CategoryProduct");
 objReportDataSource.Name = "NewDataSet_CategoryProducts";
 // dsDataSet is DataSet object
 objReportDataSource.Value = dsData;

            After doing some try and error method I found that this exception is due to objReportDataSource.Value = dsData. You need to assign a DataTable to Value property instead of DataSet. Below is the code that is working fine.

 ReportDataSource objReportDataSource 
              = new ReportDataSource("CategoryProduct");
 objReportDataSource.Name = "NewDataSet_CategoryProducts";
 // dsDataSet is DataSet object
 objReportDataSource.Value = dsData.Tables[0];

Happy Programming !!!

Friday, August 31, 2007

Storing and Retrieving doc/pdf/xls files in SQL Server

         I have explained how to store and retrieve image file in SQL Server in my previous post. I received many comments mentioning that how can we store and retrieve doc or pdf or excel (or any type of file) in SQL Server. Few friends (developers) have also posted comment that they receive only 13 byte when they retrieve stored image or doc or xls or pdf or rtf file. So I thought let be write another blog for them. Sorry friends I am bit late in writing this article and mean while you also have solved your issues. However this may help some new friends.

         In this example I have used a table which has four fields. Below is the script for table,

         [ID] [int] IDENTITY(1,1) NOT NULL,
         [FileName] [nvarchar](15) NOT NULL,
         [Extension] [nvarchar](5) NOT NULL,
         [Content] [image] NULL

Fig - (1) Scrpit for Table

         In my demo project I have used one file Upload control (to upload the file), one Textbox (where user can enter ID for uploaded file to retrieve it) and 2 buttons (one for uploading file and other for retrieving).

         When user select the file and click on Upload button the code stores the selected file in database. Below is the code for that,

    using (SqlConnection cnn = new SqlConnection("Connection
            SqlCommand cmd =
                                new SqlCommand("InsertFile", cnn);
            cmd.CommandType =
            cmd.Parameters.Add(new SqlParameter
("@FileName", "Name of  Uploaded File"));
            cmd.Parameters.Add(new SqlParameter
("@Extension", "Extension of Uploaded File"));
            cmd.Parameters.Add(new SqlParameter
("@Content", "byte array
               (byte[]) of uploaded file"


   Fig - (2) Code for inserting selected file in database.    

          Now when user enter FileID for uploaded file in textbox and click on retrieve button we will get the Content and extension field from database for that file id. You can use FillDataSet method to retrieve the byte array. Below code shows how to send retrieved file to user depending on the extension.

       string strExtenstion = "extension of retrieved file";
       byte[] bytFile =  "Byte array retrieved from database";

       Response.Buffer = true;

       if (strExtenstion == ".doc" || strExtenstion == ".docx")
            Response.ContentType = "application/";
       else if (strExtenstion == ".xls" || strExtenstion == ".xlsx")
            Response.ContentType = "application/";
       else if (strExtenstion == ".pdf")
            Response.ContentType = "application/pdf";

       Response.Charset = "";

       // If you write,
// Response.Write(bytFile1);
// then you will get only 13 byte in bytFile.


Fig - (3) Code to retrieve the file from database.

Happy Programming !!!



Thursday, August 30, 2007

Great tool for LINQ Query

       Joseph Albahari has created a great tool for LINQ query expression. You can dwnload it from here.

Happy Programmig !!

Saturday, August 25, 2007

How to use transaction in LINQ using C#

I installed VS 2008 beta 2 before few days and started exploring it. I looked in to the LINQ and found very interesting. LINQ generates DataContext class which provides classes and methods which is used in OR-Mapping. You can also use your own stored procedures and views with LINQ. You may require to use transaction with your own SPs for Insert, Delete or Update operations.

System.Data.Common.DbTransaction class provides the Transaction object. I have used Northwind database in this example. Lets start with new project, you can select new project from Start -> All Programs -> Microsoft Visual Studio 2008 Beta 2 and click on Microsoft Visual Studio 2008 Beta 2. Create new website. Right click on website from solution explorer and select LINQ to SQL classes from Add New Item.

This will generate dbml file in App_Code folder. Select the tables, views, stored procedures and function from server explorer and drag it on dbml file. DataContext class generates methods for each SPs, functions and views.

I have used Category and Product tables in this example. I have created two SPs InsertCategory and InsertProduct for inserting records in appropriate tables. You can see your SPs when you create the object of DataContext class.

I will first insert the category and then insert product for newly created category. If you have used some parameters as OUT parameters in your SP, you need to pass these parameters as Ref in calling method. In my SPs I have used CategoryID and ProductID as OUT parameter in SP.

Now, lets look in to the Transaction. I want that either category and product both will be added in database or none of them will be inserted. Below is the code for that,

System.Data.Common.DbTransaction trans = null;
DataClassesDataContext objDataClass = new DataClassesDataContext

// Nullable data type as the methods generated for
//SP will use Nullable type
int? intCategoryID =0;
int? intProductID =0;

// Open the connection

// Begin the transaction
trans = objDataClass.Connection.BeginTransaction();

// Assign transaction to context class
// All the database operation perform by this object
//will now use transaction

objDataClass.Transaction = trans;

// Insert Category
// I have to use Ref keyword CategoryID of newly
//added category will be assign to this variable

ref intCategoryID,
txtName.Text.Trim().Replace("'", "''"),
txtDescription.Text.Trim().Replace("'", "''"),
new byte[0]

// Insert Product
// I have to use Ref keyword as ProductID of newly
// generated product will be assign to this variable

ref intProductID,
txtQuantityPerUnit.Text.Trim().Replace("'", "''"),
txtUnitPrice.Text.Trim().Replace("'", "''")

// Commit transaction

catch (Exception ex)

// Rollback transaction
if (trans != null)

// Close the connection
if (objDataClass.Connection.State ==

Fig - (3) Code for Transaction in LINQ using C#

Happy Programming !!

Thursday, August 23, 2007

Raise event from user control to main page / Event delegation from user control to aspx page in ASP.NET,C#

          "What is delegate?" we all have faced this question in one or more interview. :) and the most common answer is "Function pointer". Here I am showing a simple example of delegate. I have one user control and one aspx page. The user control contains one button. When user click on this button I will call a method on main page using delegate. Here is my user control,

<%@ Control Language="C#" AutoEventWireup="true" CodeFile="WebUserControl.ascx.cs" Inherits="Dalegate_WebUserControl" %>

asp:Button ID="btnTest" runat="server" Text="I am Inside User Control" OnClick="btnTest_Click" />

Fig - (1) WebUserControl.ascx

            On WebUserControl.ascx.cs I have written simple delegate and event handler as shown below,

public partial class Dalegate_WebUserControl : System.Web.UI.UserControl

    // Delegate declaration
    public delegate void OnButtonClick(string strValue);

    // Event declaration
    public event OnButtonClick btnHandler;
    // Page load
    protected void Page_Load(object sender, EventArgs e)

    protected void btnTest_Click(object sender, EventArgs e)
           // Check if event is null
           if (btnHandler != null)

           // Write some text to output
           Response.Write("User Control's Button Click <BR/>");

Fig - (2) WebUserControl.ascx.cs

          Above code first check whether btnHandler is not null and than raise the event by passing argument. You can pass any number of argument in event. You need to change public delegate void OnButtonClick(string strValue) and btnHandler(string.Empty) lines for changing number of arguments. Now take a look at aspx page,

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="Dalegate_Default" %>

Register Src="WebUserControl.ascx" TagName="WebUserControl" TagPrefix="uc1" %>

DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "">

html xmlns="" >
head runat="server">
    <title>Untitled Page</title>
    <form id="form1" runat="server">
        <asp:Label ID="lblText" Text="I am On Main Page : "
        <asp:DropDownList ID="ddlTemp" runat="server">
        <br />
        <br />
        <uc1:WebUserControl ID="WebUserControl1"
           runat="server" />    

Fig - (3) Default.aspx

          Default.aspx has one drop down list and a user control as shown in above code. Lets look at cs file,

public partial class Dalegate_Default : System.Web.UI.Page
      protected void Page_Load(object sender, EventArgs e)
             // Declare and Define Event of User Control. When User
             // Clicks on button (which is inside UserControl)
             // below event is raised as I have called raised that
             // event on Button Click
             WebUserControl1.btnHandler += new 

      void WebUserControl1_btnHandler(string strValue)
             Response.Write("Main Page Event<BR/>Selected
                  Value: "
+ ddlTemp.SelectedItem.Text + "<BR/>");

 Fig - (4) Default.aspx.cs

            Now when you run the application and clicks on button you can see that when user click on button the user control raise the click event and calls the WebUserControl1_btnHandler(string strValue) method on main page.

Happy Programming !!!

Tuesday, August 21, 2007

Application failed to initialize properly(0xcC000142). Press OK to terminate the application


            I am having a small issue. I am calling a console application from ASP.NET 2.0 website. Below is my code,

string strPassword = Convert.ToString("Password");

System.Security.SecureString ssPassword = new System.Security.SecureString();

foreach (char chr in strPassword)

System.Diagnostics.ProcessStartInfo info = new System.Diagnostics.ProcessStartInfo("Path of EXE");
info.UseShellExecute = false;
info.Arguments = "ARGUMENTS";
info.UserName = Convert.ToString("UserName");
info.Password = ssPassword;
info.Domain = Convert.ToString("Domain");


Fig - (1) code for calling exe from

            When I run (by pressing F5 from VS) it works fine and console application loads. However once I have deployed the application console application does not load and gives error "Application failed to initialize properly(0xcC000142). Press OK to terminate the application".
                   I have used proper credential(Machine's Admin User Name and Password on which web application is deployed. Console application is on same machine).

Early response is highly appreciated.   :)

Hide console window in console application

          As you all know when you execute an console application it will load command prompt and shows out put. There are amny situation in which you do not want this console window to be dislayed to user while your application runing. You can do that by calling some unmanaged functions. I have used following method to do that which is posted by Brendan Grant on MSDN.

using System.Runtime.InteropServices;

public static extern IntPtr
FindWindow(string lpClassName, string lpWindowName);

static extern bool ShowWindow(IntPtr hWnd, int nCmdShow);

IntPtr hWnd = FindWindow(null, title);

 if (hWnd != IntPtr.Zero)
       if (!visible)
          //Hide the window                    
          ShowWindow(hWnd, 0); // 0 = SW_HIDE               
          //Show window again                   
          ShowWindow(hWnd, 1); //1 = SW_SHOWNORMA          

Fig - (1) Code to hide console window in console application

Happy Programming !!!

Thursday, August 16, 2007

Viewpoint content is not displayed OR Mime type for Viewpoint content

        We are using Viewpoint 3-D content in our LMS (Learning Management System). The content is working fine in my local machine. However when I uploaded the LMS on deployment server the contect stopped working. The content loads perfectly. User can listen the sound and can change the step and steps, however 3-D content is not visible. After doing dome google search I found about Mime type settings in IIS. Viewpoint content requires Mime type added in IIS. Once I added required Mime type content it start working.

        Viewpoint require ".mts", ".mtx", ".mtz" and ".mzv" mime types. You have to add these mime types in IIS to make viewpoint content working. You can add Mime type by,

        1. Click on Start Menu
        2. Select Run.
        3. Type inetmgr and press enter.
        4. Select perticular Website or Virtual directory to add mime
            type for that website/Virtual directory. If you want to
            add for all the sites select Default Website and click on
        5. Click on HTTP Headers and select Mime type.
        6. In the Extension field, type .mts.
        7. In the Content Type field, type application/metastream, 
           and then click OK.
        8. Repeat for .mtx, .mtz, and .mzv files.

Happy Programming !!

Wednesday, August 08, 2007

Generate thumbnail image in dotnet

          If you have created any e-commerce site, you must have used thumbnail image to display product. You may have seen many sites which display thumbnail image and product description and when user click on image it shows large image of the product.  You can do this by uploading two different images for each product OR by uploading a single image and runtime generate thumbnail image from that. You can easily do that in dotnet using "GetThumbnailImage" method. Below is the code for that,

function GenerateThumbnailImage()

        System.Drawing.Image imgThumbImage;
        System.Drawing.Image imgOriginalImage = 
                 new Bitmap(Server.MapPath(".")
                       + "\\Images\\bike1.jpg");

        using (imgThumbImage = 
            imgOriginalImage.GetThumbnailImage(50, 50, 
            new System.Drawing.Image.GetThumbnailImageAbort 
            (Abort), IntPtr.Zero))

private bool Abort()
        return false;

Fig - (1)  Generate thumbnail image

Happy Programming !!

Get height and width of image / Generate image object from byte array

          We are storing image in database in our recent project. We do not store height and width for that image in database.  After few months of deployment the client wants to display the height and width of each image while we display image.  As we do not store height and width in database we need to find that runtime when we load the image from database. Below is the code that we used to solve our issue,

using (SqlConnection cnn = new SqlConnection(ConnectionString))
                SqlCommand cmd = new SqlCommand("select
                             [Image] from   tableName where ID = 1"
                cmd.Connection = cnn;
                Byte[] bytImage = (Byte[])cmd.ExecuteScalar();

               // Generate Image from Byte array
                System.Drawing.Image img =
                            (new System.IO.MemoryStream(bytImage));

                // If you are storing image in specific folder instead
                // of database then you can use following code to
                // generate image

                System.Drawing.Image img = 
                            (@"D:\ExampleWeb Projects\Learn2K5

                //  Display Height and Width
                Response.Write("Height : " + img.Height.ToString()
                             + "   Width : " + img.Width.ToString());

Fig - (1) Generate image object from byte array and get height and width

Happy Programming !!!

Wednesday, August 01, 2007

Using the inserted and deleted Tables in SQL Sever

          DML trigger statements use two special tables: the deleted table and the inserted tables. SQL Server 2005 automatically creates and manages these tables. You can use these temporary, memory-resident tables to test the effects of certain data modifications and to set conditions for DML trigger actions. You cannot directly modify the data in the tables or perform data definition language (DDL) operations on the tables, such as CREATE INDEX.

         In DML triggers, the inserted and deleted tables are primarily used to perform the following:

            1. Extend referential integrity between tables.
            2. Insert or update data in base tables underlying a view.
            3. Test for errors and take action based on the error.
            4. Find the difference between the state of a table
                before and after a data modification and 
                take actions based on that difference.

          The deleted table stores copies of the affected rows during DELETE and UPDATE statements. During the execution of a DELETE or UPDATE statement, rows are deleted from the trigger table and transferred to the deleted table. The deleted table and the trigger table ordinarily have no rows in common.

          The inserted table stores copies of the affected rows during INSERT and UPDATE statements. During an insert or update transaction, new rows are added to both the inserted table and the trigger table. The rows in the inserted table are copies of the new rows in the trigger table.

          An update transaction is similar to a delete operation followed by an insert operation; the old rows are copied to the deleted table first, and then the new rows are copied to the trigger table and to the inserted table.

          When you set trigger conditions, use the inserted and deleted tables appropriately for the action that fired the trigger. Although referencing the deleted table when testing an INSERT or the inserted table when testing a DELETE does not cause any errors, these trigger test tables do not contain any rows in these cases.

           SQL Server 2005 does not allow for text, ntext, or image column references in the inserted and deleted tables for AFTER triggers. However, these data types are included for backward compatibility purposes only. The preferred storage for large data is to use the varchar(max), nvarchar(max), and varbinary(max) data types. Both AFTER and INSTEAD OF triggers support varchar(max), nvarchar(max), and varbinary(max) data in the inserted and deleted tables. For more information, see CREATE TRIGGER (Transact-SQL).

An Example of Using the inserted Table in a Trigger to Enforce Business Rules

           Because CHECK constraints can reference only the columns on which the column-level or table-level constraint is defined, any cross-table constraints (in this case, business rules) must be defined as triggers.

           The following example creates a DML trigger. This trigger checks to make sure the credit rating for the vendor is good when an attempt is made to insert a new purchase order into the PurchaseOrderHeader table. To obtain the credit rating of the vendor corresponding to the purchase order that was just inserted, the Vendor table must be referenced and joined with the inserted table. If the credit rating is too low, a message is displayed and the insertion does not execute.

IF OBJECT_ID ('Purchasing.LowCredit','TR') IS NOT NULL
   DROP TRIGGER Purchasing.LowCredit
CREATE TRIGGER LowCredit ON Purchasing.PurchaseOrderHeader
DECLARE @creditrating tinyint,
   @vendorid int
SELECT @creditrating = v.CreditRating, @vendorid = p.VendorID
FROM Purchasing.PurchaseOrderHeader p INNER JOIN inserted i ON p.PurchaseOrderID =
   i.PurchaseOrderID JOIN Purchasing.Vendor v on v.VendorID = i.VendorID
IF @creditrating = 5
   RAISERROR ('This vendor''s credit rating is too low to accept new
      purchase orders.
', 16, 1)


Using the inserted and deleted Tables in INSTEAD OF Triggers

         The inserted and deleted tables passed to INSTEAD OF triggers defined on tables follow the same rules as the inserted and deleted tables passed to AFTER triggers. The format of the inserted and deleted tables is the same as the format of the table on which the INSTEAD OF trigger is defined. Each column in the inserted and deleted tables maps directly to a column in the base table.

         The following rules regarding when an INSERT or UPDATE statement referencing a table with an INSTEAD OF trigger must supply values for columns are the same as if the table did not have an INSTEAD OF trigger:

      1. Values cannot be specified for computed columns or
          columns with a timestamp data type.
      2. Values cannot be specified for columns with an IDENTITY
          property, unless IDENTITY_INSERT is ON for that table.
          When IDENTITY_INSERT is ON, INSERT statements must
          supply a value.
      3. INSERT statements must supply values for all NOT NULL
          columns that do not have DEFAULT constraints.
      4. For any columns except computed, identity, or
columns, values are optional for any
          column that allows nulls, or any NOT NULL column that 
          has a DEFAULT definition.

         When an INSERT, UPDATE, or DELETE statement references a view that has an INSTEAD OF trigger, the SQL Server 2005 Database Engine calls the trigger instead of taking any direct action against any table. The trigger must use the information presented in the inserted and deleted tables to build any statements required to implement the requested action in the base tables, even when the format of the information in the inserted and deleted tables built

         The format of the inserted and deleted tables passed to an INSTEAD OF trigger defined on a view matches the select list of the SELECT statement defined for the view. For example:


CREATE VIEW EmployeeNames (EmployeeID, LName, FName)
SELECT e.EmployeeID, c.LastName, c.FirstName
FROM AdventureWorks.HumanResources.Employee e
JOIN AdventureWorks.Person.Contact c
ON e.ContactID = c.ContactID

         The result set for this view has three columns: an int column and two nvarchar columns. The inserted and deleted tables passed to an INSTEAD OF trigger defined on the view also have an int column named EmployeeID, an nvarchar column named LName, and an nvarchar column named FName.

The select list of a view can also contain expressions that do not directly map to a single base-table column. Some view expressions, such as a constant or function invocation, may not reference any columns and can be ignored. Complex expressions can reference multiple columns, yet the inserted and deleted tables have only one value for each inserted row. The same issues apply to simple expressions in a view if they reference a computed column that has a complex expression. An INSTEAD OF trigger on the view must handle these types of expressions. For more information, see Expressions and Computed Columns in INSTEAD OF Triggers.


         This article is copied from MSDN for my personal learning.

Happy Learning !!!

Triggres in SQL Sever

           A trigger is single or multiple SQL statements that fires as an actionwhen event (like INSERT, DELETE,UPDATE, CREATE, ALTER, DROP) occures. You can devide tringgers in to two parts. DDL Triggers and DML Triggers.

DDL (Data Defenition Language)  Triggers

            DDL Triggers are a special kind of trigger that fire in response to Data Definition Language (DDL) statements. These events primarily correspond to Transact-SQL statements that start with the keywords CREATE, ALTER, and DROP. Certain system stored procedures that perform DDL-like operations can also fire DDL triggers. They can be used to perform administrative tasks in the database such as auditing and regulating database operations. Use DDL triggers when you want to do the following:

  • You want to prevent certain changes to your database schema.
  • You want something to occur in the database in response to a change in your database schema.
  • You want to record changes or events in the database schema.

            DDL triggers fire only after the DDL statements that trigger them are run. DDL triggers cannot be used as INSTEAD OF triggers.

            The following example shows how a DDL trigger can be used to prevent any table in a database from being modified or dropped.

PRINT 'You must disable Trigger "safety" to drop or alter tables!'
Fig - (1) Example of DDL Trigger

DML (Data Manipulation Language) Triggers

           DML triggers are invoked when a data manipulation language (DML) event takes place in the database. DML events include INSERT, UPDATE, or DELETE statements that modify data in a specified table or view. A DML trigger can query other tables and can include complex Transact-SQL statements. The trigger and the statement that fires it are treated as a single transaction, which can be rolled back from within the trigger. If a severe error is detected (for example, insufficient disk space), the entire transaction automatically rolls back.

DML triggers are useful in these ways:

  • They can cascade changes through related tables in the database; however, these changes can be executed more efficiently using cascading referential integrity constraints.
  • They can guard against malicious or incorrect INSERT, UPDATE, and DELETE operations and enforce other restrictions that are more complex than those defined with CHECK constraints.
    Unlike CHECK constraints, DML triggers can reference columns in other tables. For example, a trigger can use a SELECT from another table to compare to the inserted or updated data and to perform additional actions, such as modify the data or display a user-defined error message.
  • They can evaluate the state of a table before and after a data modification and take actions based on that difference.
  • Multiple DML triggers of the same type (INSERT, UPDATE, or DELETE) on a table allow multiple, different actions to take place in response to the same modification statement.

Types of DML Triggers

           You can program the following types of DML Triggers:

        After Trigger

                   AFTER triggers are executed after the action of the INSERT, UPDATE,
            or   DELETE statement is performed. Specifying AFTER is the same as
            specifying FOR, which is the only option available in earlier versions of 
            Microsoft SQL Server. AFTER triggers can be specified only on tables. 

        Instead Of Trigger

                    INSTEAD OF triggers are executed in place of the usual triggering
            action. INSTEAD OF triggers can also be defined on views with one or more
            base tables, where they can extend the types of updates a view can support.

For more information about AFTER and INSTEAD OF triggers, see DML Trigger Planning Guidelines

CLR Triggers

          A CLR Trigger can be either an AFTER or INSTEAD OF trigger. A CLR trigger can also be a DDL trigger. Instead of executing a Transact-SQL stored procedure, a CLR trigger executes one or more methods written in managed code that are members of an assembly created in the .NET Framework and uploaded in SQL Server. For more information, see Programming CLR Triggers.  

DML Trigger Execution

            AFTER triggers are never executed if a constraint violation occurs; therefore, these triggers cannot be used for any processing that might prevent constraint violations.

             INSTEAD OF triggers are executed instead of the triggering action. These triggers are executed after the inserted and deleted tables reflecting the changes to the base table are created, but before any other actions are taken. They are executed before any constraints, so can perform preprocessing that supplements the constraint actions.

             If an INSTEAD OF trigger defined on a table executes a statement against the table that would usually fire the INSTEAD OF trigger again, the trigger is not called recursively. Instead, the statement is processed as if the table had no INSTEAD OF trigger and starts the chain of constraint operations and AFTER trigger executions. For example, if a DML trigger is defined as an INSTEAD OF INSERT trigger for a table, and the trigger executes an INSERT statement on the same table, the INSERT statement executed by the INSTEAD OF trigger does not call the trigger again. The INSERT executed by the trigger starts the process of performing constraint actions and firing any AFTER INSERT triggers defined for the table.

             If an INSTEAD OF trigger defined on a view executes a statement against the view that would usually fire the INSTEAD OF trigger again, it is not called recursively. Instead, the statement is resolved as modifications against the base tables underlying the view. In this case, the view definition must meet all of the restrictions for an updatable view. For a definition of updatable views, see Modifying Data Through a View. For example, if a DML trigger is defined as an INSTEAD OF UPDATE trigger for a view, and the trigger executes an UPDATE statement referencing the same view, the UPDATE statement executed by the INSTEAD OF trigger does not call the trigger again. The UPDATE executed by the trigger is processed against the view as if the view did not have an INSTEAD OF trigger. The columns changed by the UPDATE must be resolved to a single base table. Each modification to an underlying base table starts the chain of applying constraints and firing AFTER triggers defined for the table.

             DML trigger performance overhead is usually low. The time involved in running a DML trigger is spent mostly in referencing other tables, which can be either in memory or on the database device. The deleted and inserted tables are always in memory. The location of other tables referenced by the trigger determines the amount of time the operation requires.

DML Trigger Execution and Partitioned Views

             In SQL Server 2005, the way UPDATE and DELETE triggers fire on tables that underlie a partitioned view has changed from the way they fire in SQL Server 2000. In SQL Server 2000, when an UPDATE or DELETE statement is issued against a partitioned view, either local or distributed, any UPDATE or DELETE triggers that are defined on the base tables of the view fire. These include triggers on tables that are not affected by the update or delete operation. In SQL Server 2005, an UPDATE or DELETE trigger fires only if the base table on which the trigger is defined is affected by the update or delete operation. This behavior is the same for both AFTER and INSTEAD OF triggers.

            For example, consider a partitioned view PV that consists of fields from tables T1 and T2. Additionally, both T1 and T2 have UPDATE triggers defined on them. In SQL Server 2000, an UPDATE statement issued against PV that only affects rows in T1 causes the UPDATE triggers on both T1 and T2 to fire. In SQL Server 2005, only the UPDATE trigger that is defined on T1 fires.

CREATE TRIGGER TriggerName ON TableName

Fig - (2) Syntax for DML Trigger

            There is no BEFORE trigger in SQL Server.


Understanding DDL Triggers vs. DML Triggers

DDL triggers and DML triggers are used for different purposes.

DML triggers operate on INSERT, UPDATE, and DELETE statements, and help to enforce business rules and extend data integrity when data is modified in tables or views.

              DDL triggers operate on CREATE, ALTER, DROP, and other DDL statements. They are used to perform administrative tasks and enforce business rules that affect databases. They apply to all commands of a single type across a database, or across a server.

               DML triggers and DDL triggers are created, modified, and dropped by using similar Transact-SQL syntax, and share other similar behavior.

               Like DML triggers, DDL triggers can run managed code packaged in an assembly that was created in the Microsoft .NET Framework and uploaded in SQL Server. For more information, see Programming CLR Triggers.

               Like DML triggers, more than one DDL trigger can be created on the same Transact-SQL statement. Also, a DDL trigger and the statement that fires it are run within the same transaction. This transaction can be rolled back from within the trigger. Serious errors can cause a whole transaction to be automatically rolled back. DDL triggers that are run from a batch and explicitly include the ROLLBACK TRANSACTION statement will cancel the whole batch. For more information, see Using DML Triggers That Include COMMIT or ROLLBACK TRANSACTION.

               Like DML triggers, DDL triggers can be nested. For more information, see Using Nested Triggers.

               When you are designing DDL triggers, consider how they differ from DML triggers in the following ways:

  • DDL triggers run only after a Transact-SQL statement is completed. DDL triggers cannot be used as INSTEAD OF triggers.
  • DDL triggers do not create the inserted and deleted tables. The information about an event that fires a DDL trigger, and the subsequent changes caused by the trigger, is captured by using the EVENTDATA function. For more information, see Using the EVENTDATA Function.


                     This article is copied from different MSDN links. I have just gethered all the information at single place.

Happy Learning !!!

Monday, July 30, 2007

Debugging JavaScript in MAC Safari / Tool to debug JavaScript in Safari

      Safari has inbuilt debug menu for java script. By default it is hidden. Safari's "Debug" menu allows you to turn on the logging of JavaScript errors. To display the debug menu in Mac OS X, open a Terminal window and type:

            defaults write IncludeDebugMenu 1

      To display the debug menu in Safari 3.0 for Windows, use a text editor to add the following to the Preferences.plist file located at C:\Documents and Settings\USERNAME\Application Data\Apple Computer\Safari\Preferences.plist :

            <key>IncludeDebugMenu</key> <true/>

        Safari 1.3 and above supports explicit logging of arbitrary information - similar to Objective-C NSLog() - function by using window.console.log() in your JavaScript. All messages are routed to the JavaScript Console window and show up nicely in a dark green, to easily differentiate themselves from JavaScript exceptions.

                window.console.log("I think therefore I code!"); 
                alert("I think therefore I code!");

        WebKit crew has released a JavaScript debugger tool Drosera to debug javascript in safari.

Happy Programming !!

Saturday, July 28, 2007

Session.SessionID is not unique

    I have seen many forums in which users are asking that Session.SessionID is not unique. You also have seen forums saying that "I am getting different value for SessionID on every page or in each post back." Yes they are correct !!!! "This is not possible. How can it be?", I know this is your reaction. We have read in all the books and seen practically that SessionID is unique, until user logs off or close the browser. This is also correct. Now you will say then why am I writing the story?

   Here is the actual fundamentals that I have observe practically. The session changes in each request (either post back or redirecting from one page to another page) until user has not insert any value in Session collection. This means server treats each request from new session if user has not entered any value in session. You can check this practically!!!

  Create a web application with two pages Default.aspx and Default2.aspx. Add one button and two lables on Default.aspx page. On page load of Default.aspx in if(!Page.IsPostBack) set any one lable's text to Session.SessionID. Now in click event of button set second lable's text to Session.SessionID. You can see that every time when post back occurs you have new value of SessionID. Amazing !!!!!!! You can check by redirecting to Default2.aspx page and print SessionID.

    Now, on Default.aspx page in if(!Page.IsPostBack) set Session["test"] = "1" or set any name value collection. Once you do this run the page. Click on button any number of time and you see that now SessionID is unique.

   Really Amazing!!!!


Happy Programming !!

Tuesday, July 24, 2007

Dispose and Finalize in Dot Net OR Maemory Management using Dispose and Finalize

       You can find article here

Happy Programming !!

Monday, July 23, 2007

Delete single row from duplicate rows in SQL Server 2005 and 2000

        Lets assume that you are using SQL Server 2005 for your current project. You found that you have few rows which have duplicate data in all the columns. Lets consider that you have table name "Example" which has two columns ID and Name.

CREATE TABLE [dbo].[Example]
[ID] [int] NOT NULL,
[Name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

Fig - (1) Create Statement for Table     

INSERT INTO [Example] ([ID],[Name]) VALUES (1,Chirag)
INSERT INTO [Example] ([ID],[Name]) VALUES (1,Chirag)
INSERT INTO [Example] ([ID],[Name]) VALUES (2,'Shailesh')
INSERT INTO [Example] ([ID],[Name]) VALUES (3,'Dipak')
INSERT INTO [Example] ([ID],[Name]) VALUES (4,'Mihir')
INSERT INTO [Example] ([ID],[Name]) VALUES (4,'Mihir')
INSERT INTO [Example] ([ID],[Name]) VALUES (4,'Piyush')

Fig - (2) Insert Script.

          You can see that first two and last three rows are duplicate rows. All the values in these rows are same.  Here is the insert script, if you want to do this practically in your local database.

       Now you want to delete duplicate rows in such a way that only one row will be exist after delete statement. First let me write the query which will give return all the duplicate rows from table.

        COUNT([ID]) > 1

Fig - (3) Query to identify duplicate rows in table.

       Here I have used COUNT([ID]) in select statement as ID is not null filed. You can use any column which is not NULL. If all the columns in your table allows NULL value than you can use COUNT(*). The Difference between COUNT(Column Name) and COUNT(*) is, if your column allows null value and in table you have 5 records with 2 null values in ColumnA. If you use COUNT(ColumnA) it will returns 3 and if you use COUNT(*) it will returns 5. So COUNT(Column Name) ignores NULL value. Lets get back to our query. I have used all the column in SELECT and GROUP BY clause. You also have to write all the columns of your table in SELECT and GROUP BY clause. This way you can identify all the duplicates row from table.

        Lets assume that you have to delete the row which has value (1, 'Chirag') so that only one row remains. Here is the query, (Note: This will work only in SQL Sever 2005)

      DELETE TOP(1) FROM [Example] WHERE [ID] = 1

Fig - (3) Delete single row from duplicate rows.

          Here I have used TOP(1) , If you have n rows which has all the values same than you have to use TOP(n-1) so that only 1 row will be remain after delete statement. To delete all the duplicate rows you need to write a cursor as shown below,




/* Loop through cursor for remaining ID */




Fig - (4) Cursor to delete all duplicate  records

         This is all about deleting duplicate rows in SQL Server 2005.

       Now to do the same in SQL server 2000.  There is function called ROWCOUNT in SQL.  I have used same [Example] table. You can do this by,

DELETE FROM [Example] WHERE [ID] = 1

Fig - (5) Delete duplicate row in SQL Server 2000

      ROWCOUNT function specify that how many rows will be affected by the statement which is immediately written below.  Here also you have to write  ROWCOUNT (n -1) to delete n duplicate rows such that only  1 row will remain in database.

Happy Programming !!