OutOfMemory Exception

The below code is used to merge multipage tiff with single page tiffs.

 private void CreatThumbnail(Image thumbNailImg, int iPagesCount)
        {
            try
            {
                //TRK
                //PictureBox[] picBoxArray = new PictureBox[iPagesCount];
                picBoxArray = new PictureBox[iPagesCount];
                PictureBox picBox = null;

                pnlThumbnail.Controls.Clear();

                
                int iWidth = 300;
                int iHeight = 300;

               
                if (!rbShowPageByPage.Checked && !rbShowBoth.Checked)
                {
                    //TRK - 01 
                    iWidth = 308;
                    iHeight = 550;
                    pnlThumbnail.Size = new Size(966, 560);

                    //iWidth = 500;
                    //iHeight = 550;
                    //pnlThumbnail.Size = new Size(966, 560);
                }
                else
                {
                    pnlThumbnail.Location = new Point(16, 110);
                    pnlThumbnail.Size = new Size(966, 131);
                }

                int iThumbCurrPage = 0;
                
                for (; iThumbCurrPage < iPagesCount; iThumbCurrPage++)
                {
                    //TRK
                    picBox = new PictureBox();
                    thumbNailImg.SelectActiveFrame(System.Drawing.Imaging.FrameDimension.Page, iThumbCurrPage);

                    using (Image myBmp = new Bitmap(thumbNailImg, iWidth, iHeight))
                    {
                        MemoryStream memoryStream = new MemoryStream();
                        myBmp.Save(memoryStream, System.Drawing.Imaging.ImageFormat.Tiff);
                        picBox.Image = Image.FromStream(memoryStream); // showing the page in the pictureBox1

                        myBmp.Dispose();
                        if (memoryStream != null)
                        {
                            memoryStream.Close();
                            memoryStream.Dispose();
                        }
                        GC.Collect();
                    }

                    picBox.Size = picBox.Image.Size;
                    picBox.Location = new Point(1 + (iThumbCurrPage * (iWidth + 10)), 1);
                    picBoxArray[iThumbCurrPage] = picBox;
                    picBoxArray[iThumbCurrPage].Name = Convert.ToString(iThumbCurrPage);
                    toolTipForControls.SetToolTip(picBoxArray[iThumbCurrPage], MessagesAndToolTips.ThumNailClick);

                    picBoxArray[iThumbCurrPage].Click += new System.EventHandler(this.ThumbNailPictureBox_Click);
                    pnlThumbnail.Controls.Add(picBoxArray[iThumbCurrPage]);

                    if (iThumbCurrPage == 0)
                        objCommonDeclarations.ShowMessage("Loading Page " + Convert.ToString(iThumbCurrPage + 1) + " Of " + Convert.ToString(iPagesCount), "INFO", lblShowMessage);
                    else
                        objCommonDeclarations.ShowMessage("Loading Next Page " + Convert.ToString(iThumbCurrPage + 1) + " Of " + Convert.ToString(iPagesCount), "INFO", lblShowMessage);
                    Application.DoEvents();

                    GC.Collect();
                }

                objCommonDeclarations.ShowMessage("Loading Last " + Convert.ToString(iThumbCurrPage) + " Of " + Convert.ToString(iPagesCount), "INFO", lblShowMessage);
                Application.DoEvents();

                objCommonDeclarations.ShowMessage(string.Empty, "INFO", lblShowMessage);
                Application.DoEvents();

            }
            catch (Exception ex)
            {
                objCommonDeclarations.ShowMessage(ex.Message.ToString(), "ERROR", lblShowMessage);
                objCommonDeclarations.WriteLog(ex.Message.ToString(), false, false);
            }
        }

 private void SwapImages()
        {
            try
            {
                int swapPage = Convert.ToInt32(txtPageNumber.Text.Trim()) - 1;

                //Open file in read only mode 
                using (FileStream fs = new FileStream(@lblFile.Text.Trim(), FileMode.Open, FileAccess.Read))
                {

                    string _sDestinatinPath = Path.Combine(CommonDeclarations.sDestinationPath, Path.GetFileName(@lblFile.Text.Trim()));
                    if (swapPage == 0)
                    {
                        using (Image bmp1 = Image.FromStream(fs))//TRK
                        {
                            bmp1.Save(_sDestinatinPath, ImageFormat.Tiff);

                            fs.Dispose();
                            bmp1.Dispose();
                            GC.Collect();
                        }
                        return;

                        //background
                        //return string.Empty;
                    }

                    ImageCodecInfo imageCodecInfo = GetEncoderInfo("image/tiff");

                    //Image bmp = Image.FromStream(fs);
                    using (Image bmp = Image.FromStream(fs))//TRK
                    {
                        //Bitmap bmp = new Bitmap(fs);

                        int frameCount = bmp.GetFrameCount(FrameDimension.Page);
                        bmp.SelectActiveFrame(System.Drawing.Imaging.FrameDimension.Page, swapPage);

                        //Image newTiff = new Bitmap(bmp, bmp.Width, bmp.Height);
                        //Image newTiff = Converter.ConvertToBitonal(new Bitmap(bmp, bmp.Width, bmp.Height));
                        using (Image newTiff = Converter.ConvertToBitonal(new Bitmap(bmp, bmp.Width, bmp.Height)))
                        {

                            EncoderParameters SaveEncoderParameters = new EncoderParameters(2);
                            System.Drawing.Imaging.Encoder SaveEncoder = System.Drawing.Imaging.Encoder.SaveFlag;
                            EncoderParameter CompressEncodeParam = new EncoderParameter(SaveEncoder, (long)(EncoderValue.MultiFrame));
                            SaveEncoderParameters.Param[0] = CompressEncodeParam;
                            SaveEncoder = System.Drawing.Imaging.Encoder.Compression;
                            CompressEncodeParam = new EncoderParameter(SaveEncoder, (long)(EncoderValue.CompressionCCITT4));
                            SaveEncoderParameters.Param[1] = CompressEncodeParam;


                            System.Drawing.Imaging.Encoder AddEncoder = System.Drawing.Imaging.Encoder.SaveFlag;
                            EncoderParameter AddEncodeParam = new EncoderParameter(AddEncoder, (long)EncoderValue.FrameDimensionPage);
                            System.Drawing.Imaging.Encoder AddCompressionEncoder = System.Drawing.Imaging.Encoder.Compression;
                            EncoderParameter AddCompressionEncodeParam = new EncoderParameter(AddCompressionEncoder, (long)EncoderValue.CompressionCCITT4);
                            EncoderParameters AddEncoderParams = new EncoderParameters(2);

                            AddEncoderParams.Param[0] = AddEncodeParam;
                            AddEncoderParams.Param[1] = AddCompressionEncodeParam;

                            ArrayList swap = new ArrayList();
                            for (int i = 0; i < frameCount; i++)
                            {
                                swap.Add(i);
                            }

                            swap.Insert(0, swap[swapPage]);
                            swap.RemoveAt(swapPage + 1);

                            int pageCount = 0;
                            for (; pageCount < frameCount; pageCount++)
                            {
                                switch (pageCount)
                                {
                                    case 0:

                                        newTiff.Save(_sDestinatinPath, imageCodecInfo, SaveEncoderParameters);
                                        break;
                                    default:

                                        bmp.SelectActiveFrame(System.Drawing.Imaging.FrameDimension.Page, Convert.ToInt32(swap[pageCount]));

                                        try
                                        {
                                            // Convert image to bitonal for saving to file
                                            using (Bitmap newPage = Converter.ConvertToBitonal(new Bitmap(bmp, bmp.Width, bmp.Height)))//TRK
                                            {
                                                newTiff.SaveAdd(newPage, AddEncoderParams);
                                                newPage.Dispose();
                                                GC.Collect();
                                            }
                                        }
                                        catch (Exception ex)
                                        {
                                            throw ex;
                                        }
                                        finally
                                        {

                                            //GC.WaitForPendingFinalizers();
                                        }
                                        break;
                                }

                                ////Background
                                //objCommonDeclarations.ShowMessage("Reordering Images " + Convert.ToString(pageCount + 1) + " of " + Convert.ToString(frameCount), "INFO", lblShowMessage);
                                //Application.DoEvents();

                                GC.Collect();
                                //GC.WaitForPendingFinalizers();
                            }

                            //////Background
                            //objCommonDeclarations.ShowMessage("Reordering Images " + Convert.ToString(pageCount + 1) + " of " + Convert.ToString(frameCount), "INFO", lblShowMessage);
                            //Application.DoEvents();

                            //////Background
                            //objCommonDeclarations.ShowMessage(string.Empty, "INFO", lblShowMessage);
                            //Application.DoEvents();

                            AddEncoderParams.Param[0] = new EncoderParameter(AddEncoder, (long)EncoderValue.Flush);
                            newTiff.SaveAdd(AddEncoderParams);

                            newTiff.Dispose();
                            GC.Collect();
                        }

                        bmp.Dispose();
                        fs.Dispose();
                        GC.Collect();

                    }
                }
            }
            catch (Exception ex)
            {
                objCommonDeclarations.ShowMessage(ex.Message.ToString(), "ERROR", lblShowMessage);
                //Background
                //return ex.Message.ToString();
                objCommonDeclarations.WriteLog(ex.Message.ToString(), false, false);
            }

            //Background
            //return string.Empty;
        }


/// 

/// To Merge the Single Page Tifs in folder to a Multipage Tiff Image
/// 
/// This denotes Multipage Image Full Path/// This denotes Single Page Tif Image List/// After Merge where need to store the New Merged Tif Filesprivate bool MergeTifImages(String sMultiPageTif, List lstSingleTifs, String sDestinationPath)
        {
            String SinglePageTif = String.Empty;
            bool sflg = true;
            try
            {
                // To Check Destination Path is Exist or Not, if not create the path and folder structure
                if (!Directory.Exists(@sDestinationPath)) Directory.CreateDirectory(@sDestinationPath);
                //Open file in read only mode 
                using (FileStream fs = new FileStream(@sMultiPageTif, FileMode.Open, FileAccess.Read))
                {
                    //To Form the Destination Tiff File Name
                    String _sDestinatinPath = Path.Combine(@sDestinationPath, Path.GetFileName(@sMultiPageTif));
                    //To Create the Image Codec Info                   
                    ImageCodecInfo imageCodecInfo = GetEncoderInfo("image/tiff");

                    //To Create the New bmp Image from the existing Multipage Tif
                    //Image bmp = Image.FromStream(fs);
                    Image newTiff = null;
                    //To Select the Encoder and Compresstion formates for the Tif Images
                    EncoderParameters SaveEncoderParameters = new EncoderParameters(2);
                    System.Drawing.Imaging.Encoder SaveEncoder = System.Drawing.Imaging.Encoder.SaveFlag;
                    EncoderParameter CompressEncodeParam = new EncoderParameter(SaveEncoder, (long)(EncoderValue.MultiFrame));
                    SaveEncoderParameters.Param[0] = CompressEncodeParam;
                    SaveEncoder = System.Drawing.Imaging.Encoder.Compression;
                    CompressEncodeParam = new EncoderParameter(SaveEncoder, (long)(EncoderValue.CompressionCCITT4));
                    SaveEncoderParameters.Param[1] = CompressEncodeParam;

                    System.Drawing.Imaging.Encoder AddEncoder = System.Drawing.Imaging.Encoder.SaveFlag;
                    EncoderParameter AddEncodeParam = new EncoderParameter(AddEncoder, (long)EncoderValue.FrameDimensionPage);
                    System.Drawing.Imaging.Encoder AddCompressionEncoder = System.Drawing.Imaging.Encoder.Compression;
                    EncoderParameter AddCompressionEncodeParam = new EncoderParameter(AddCompressionEncoder, (long)EncoderValue.CompressionCCITT4);
                    EncoderParameters AddEncoderParams = new EncoderParameters(2);

                    AddEncoderParams.Param[0] = AddEncodeParam;
                    AddEncoderParams.Param[1] = AddCompressionEncodeParam;

                    using (Image bmp = Image.FromStream(fs))
                    {
                        //Get the Page Count, i.e Frame Count
                        int frameCount = bmp.GetFrameCount(FrameDimension.Page);

                        //To Create Temp New Tiff                 
                        newTiff = Converter.ConvertToBitonal(new Bitmap(bmp, bmp.Width, bmp.Height));

                        // To Save the Multipage Tiff
                        newTiff.Save(_sDestinatinPath, imageCodecInfo, SaveEncoderParameters);
                        Application.DoEvents();
                        //To Update frame by frame to the multipage
                        int pageCount = 0;
                        for (; pageCount < frameCount; pageCount++)
                        {
                            switch (pageCount)
                            {
                                case 0:

                                    newTiff.Save(_sDestinatinPath, imageCodecInfo, SaveEncoderParameters);
                                    break;
                                default:
                                    bmp.SelectActiveFrame(System.Drawing.Imaging.FrameDimension.Page, pageCount);
                                    // Convert image to bitonal for saving to file

                                    using (Bitmap newPage = Converter.ConvertToBitonal(new Bitmap(bmp, bmp.Width, bmp.Height)))
                                    {
                                        //Bitmap newPage = Converter.ConvertToBitonal(new Bitmap(bmp, bmp.Width, bmp.Height));
                                        newTiff.SaveAdd(newPage, AddEncoderParams);
                                    }

                                    break;
                            }
                            Application.DoEvents();
                        }
                    }

                    // To Merage the Single Page Tif Image to Multipage Tif Image form Single Tifs List
                    foreach (String SingleTif in lstSingleTifs)
                    {
                        try
                        {
                            CommonDeclarations.WriteLog("Single Page TIF: " + Path.GetFileName(SingleTif), false, false);
                            using (FileStream fs1 = new FileStream(@SingleTif, FileMode.Open, FileAccess.Read))
                            {
                                //To over come Out of memory.
                                //Image Singlebmp = Image.FromStream(fs1);
                                using (Image Singlebmp = Image.FromStream(fs1))
                                {
                                    Application.DoEvents();
                                    int frameCount = Singlebmp.GetFrameCount(FrameDimension.Page);
                                    //To Check the Given Single Page tif is having More than One Frame
                                    if (frameCount > 1)
                                    {
                                        CommonDeclarations.WriteLog("The Selected Single Page TIF Having More Then One Page. Page Count: " + frameCount, false, false);
                                        //This works if Multipage exist 
                                        int pageCount = 0;
                                        for (; pageCount < frameCount; pageCount++)
                                        {
                                            Singlebmp.SelectActiveFrame(System.Drawing.Imaging.FrameDimension.Page, pageCount);
                                            // Convert image to bitonal for saving to file
                                            Bitmap SinglePageTiff = Converter.ConvertToBitonal(new Bitmap(Singlebmp, Singlebmp.Width, Singlebmp.Height));
                                            newTiff.SaveAdd(SinglePageTiff, AddEncoderParams);
                                            //  break;
                                        }
                                    }
                                    else
                                    {
                                        //This will works while Single Frame of Single Tiff Image
                                        using (Bitmap tempBmp = new Bitmap(Singlebmp, Singlebmp.Width, Singlebmp.Height))
                                        {
                                            using (Bitmap SinglePageTifs = Converter.ConvertToBitonal(tempBmp))
                                            {
                                                //Bitmap SinglePageTifs = Converter.ConvertToBitonal(new Bitmap(Singlebmp, Singlebmp.Width, Singlebmp.Height));
                                                Application.DoEvents();
                                                newTiff.SaveAdd(SinglePageTifs, AddEncoderParams);

                                                SinglePageTifs.Dispose();
                                                GC.Collect();
                                            }
                                            tempBmp.Dispose();
                                            GC.Collect();
                                        }
                                    }

                                    Singlebmp.Dispose();
                                    GC.Collect();
                                }

                                fs1.Close();
                                fs1.Dispose();
                                GC.Collect();
                            }
                        }
                        catch (Exception ex)
                        {
                            CommonDeclarations.WriteLog(ex.Message.ToString(), false, false);
                            sflg = false;
                        }

                    }
                    //Save the New Tiff and Encode the Tiff  Images
                    AddEncoderParams.Param[0] = new EncoderParameter(AddEncoder, (long)EncoderValue.Flush);
                    newTiff.SaveAdd(AddEncoderParams);
                    newTiff.Dispose();
                    GC.Collect();

                }
            }
            catch (Exception ex)
            {
                //CommonDeclarations.ShowMessage(ex.Message.ToString(), "ERROR", lblShowMessage);
                CommonDeclarations.WriteLog("Some Error in MergeTifImages, Error: " + ex.Message.ToString(), false, false);
                sflg = false;
            }
            return sflg;
        }

        /// 

        /// To Get the ImageCodecInfo. For Example *.TIF,*.TIFF or *.JPG,*.BMP,*.GIF
        /// 
        /// This denotes MimeType. Kind of Imgage to Get the Image Files Type/// 
        private static ImageCodecInfo GetEncoderInfo(String mimeType)
        {
            int j;
            try
            {
                ImageCodecInfo[] encoders;
                encoders = ImageCodecInfo.GetImageEncoders();
                for (j = 0; j < encoders.Length; ++j)
                {
                    if (encoders[j].MimeType == mimeType)
                        return encoders[j];
                }
                return null;
            }
            catch (Exception ex)
            {
                CommonDeclarations.WriteLog("Some Error in ImageCodecInfo,Error:"+ ex.Message.ToString(), false, false);
                return null;
            }
        }

After running the application I looked how much memory the Proces use. Object.Dispose(); doesn't release the memory occuped by the object immediately. It shows constant memory increase when seeing. After using GC.Collect(); if frees the memory. Afterwares no OutOfMemory Exception thrown. Here we added each image to a panel(pnlThumbnail) pnlThumbnail.Controls.Add(picBoxArray[iThumbCurrPage]); This occupies more memory. We have to clear that in the following way.

//Clear the picture box stored in thumbnailArray
                if (picBoxArray != null)
                {
                    foreach (PictureBox tempPb in picBoxArray)
                    {
                        if (tempPb.Image != null)
                        {
                            tempPb.Image.Dispose();
                            tempPb.Image = null;

                            GC.Collect();
                        }
                    }
                    picBoxArray = null;
                }

                for (int clearPictureBox = 0; clearPictureBox < pnlThumbnail.Controls.Count; clearPictureBox++)
                {

                    PictureBox pb = pnlThumbnail.Controls[clearPictureBox] as PictureBox;

                    pnlThumbnail.Controls.Remove(pb);

                    if (pb.Image != null)
                    {
                        pb.Image.Dispose();
                        pb.Image = null;
                    }
                }
                
                if (pbShowTiffImage.Image != null)
                {
                    pbShowTiffImage.Image = null;
                    pbShowTiffImage.Invalidate();
                    GC.Collect();
                }
                                
                //The above one is better than this
                //foreach (IDisposable control in pnlThumbnail.Controls)
                //    control.Dispose(); 

                pnlThumbnail.Controls.Clear();
                GC.Collect();

REferences Tracking down managed memory leaks (how to find a GC leak) IDisposable.Dispose Method Detecting .NET application memory leaks memory leak with delegates and workflow foundation http://msdn.microsoft.com/en-us/magazine/cc163491.aspx Memory Leak Detection in .NET

Stored Procedure Generator for SQL SERVER

Download the tool


1. Get Sql Server Name.
2. Get Sql Server IP Address.
3. Load Server from local machine.
4. Load Server from your network.
5. Press the button load to load the Server Name or IP Address.
6. Press the button load to load the DataBase name for the the Selected Server Name or IP Address..
7. Save to File save the Procudure as .sql file.
8. Append The Scripts In SQLFile puts all the Procedure in a single .sql file.
9. Create New File For Each SP creates new .sql file with the name of the procedure.
10. Execute Script To Server, executes your procedure directly to your SQL server.
11. Overwrite the SP If Already Exists. If any procedure already exists it drops the procedure and creates a new one.
12. The left list box contails all the tables in your selected database.
13. >> button to move from left listbox to right for creating procedure for the table.
14. << botton to move from the right listbox to left if you don't want to create procedure for the table.
15. Select All To Move Right to select all tables in the left listbox for moving right.
16. Select All To Move Left to select all tables in the right listbox for moving left.
17. Click Create Scripts button to generate procedure according to the conditions.


Happy Coding.

SQL SERVER Parameter Directions

There are 4 types of parameter direction in SQL SERVER.

1. Input
2. Output or Out
3. InputOutput
4. Return

But we can specify only OUT or OUTPUT as parameter direction for the Procedure or Function.

RETURN at the last statement of the Procedure or Function and not in the parameter declaration of the Procedure or Function.

IN

DECLARE @ParentID int
SET @ParentID =6
exec [dbo].[GetTestTable] @ParentID


ALTER PROCEDURE [dbo].[GetTestTable](
@ParentID INT)
AS
BEGIN
SET @ParentID =10
SELECT @ParentID
END

We can’t able to specify in any where during the execution or in the SP parameter direction.

DECLARE @ParentID int
SET @ParentID =6
exec [dbo].[GetTestTable] @ParentID IN –Not allowed

ALTER PROCEDURE [dbo].[GetTestTable](
@ParentID INT IN) -–Not allowed AS
BEGIN
SELECT @ParentID
END

We will get this error.
Incorrect syntax near the keyword 'in'.

OUTPUT
Output is not only for sending value back to the caller, it also accepts input from the caller. Since by default OUT implies both IN and OUT.

E.g. 1

ALTER PROCEDURE [dbo].[GetTestTable](
@ParentID INT OUTPUT)
AS
BEGIN
SET @ParentID = @ParentID + 5
END


DECLARE @ParentID int
SET @ParentID =6
exec [dbo].[GetTestTable] @ParentID OUT
PRINT @ParentID 

Result: 11

There is no need of RETURN or SELECT statement for the OUT direction parameter. Just assing the value, we will get the result.

If we didn’t assing a new value, we will get only the value we sent during execution of the Procedure or Function.(i.e. 6 for the above condion.)

If we didn’t sent any value during execution of the Procedure or Function we will get only nothing for the below condion.

DECLARE @ParentID int
exec [dbo].[GetTestTable] @ParentID OUT
PRINT @ParentID

E.g. 2

ALTER PROCEDURE [dbo].[GetTestTable](
@ParentID INT OUTPUT)
AS
BEGIN
SET @ParentID =10
SELECT @ParentID
END

No need to pass value. Just specify the keyword OUT near the parameter.

DECLARE @ParentID INT
EXEC [dbo].[GetTestTable] @ParentID OUT
PRINT @ParentID

Result: 10

For the above Procedure or Function we are assigning value to the parameter and also we are using SELECT statement. Here we will get a result-set and aslo the value assigned to our argument.

RETURN

There should be a RETURN statement at the end of the Procedure or Function.

ALTER PROCEDURE [dbo].[GetTestTable](
@ParentID INT OUTPUT)
AS
BEGIN
SET @ParentID =10
RETURN @ParentID
END

DECLARE @ParentID    INT
DECLARE @ReturnValue INT
SET @ParentID     =6
EXEC @ReturnValue = [dbo].[GetTestTable] @ParentID
PRINT @ReturnValue

If there is no RETURN statemnt at the end of the Procedure or Function, we will get only the default value of the RETURN argument datatype.

For example there is no RETURN statemnt for the above Procedure the result will be 0.

We can’t able to return a table variable from a RETURN statement.


Note:

1. By default OUT implies both IN and OUT.
2. Since our parameter is OUT we can pass value, since OUT implies both IN and OUT.
3. The default parameter direction is IN.
4. If the last statement is just RETURN without any value or a variable we will get
   nothing (if we didn’t pass value to the parameter of assign) or default value (if
   we assign value when passing). This is default for all the parameter direction.

These all are wrong.
@ReturnValue EXEC [dbo].[GetTestTable] @ParentID

Line 4: Incorrect syntax near '@ReturnValue'.

@ReturnValue = EXEC [dbo].[GetTestTable] @ParentID

Line 4: Incorrect syntax near '@ReturnValue'.

EXEC [dbo].[GetTestTable] @ParentID, @ReturnValue RETURN

Procedure or Function GetTestTable has too many arguments specified.

Three tire code Generator for C#.NET

Download the toolOr

1. Select Table Tab to get the tables for your ConnectionString.
2. Enter your connection string.
3. Press to get the tables for your ConnectionString.
4. After pressing the button Load Tables the tables are loaded. When selecting a table it focus on the Conditions tab and all the column and DataType are loaded automatically in 9, 10, 10.1.
5. Conditon tab to specify the property and field prefix.
6. Namespace for your class.
7. Name of your property class.
8. Name of your data access layer class.
9. Enter your table name. This helps to produce the sp name. For Select method it creates spname as GetTableName.
10. Table column name along with DataType.
11. 10.1. Get distinct of DataType from 10.
12. Clear all the controls.
13. For each datatype in 10.1 we are generating a textbox to enter the prefix for the DataType.
14. For each data-type you want to specify a separate prefix check it. For exampele for string specify str. Your field will be created like strUserID. The panel will be enabled and enter it.
15. By default the field names are generated with underscore (_). If you want to change give a new one.
16. Enter the prefix for the fields. This will be enabled by checking Is prefix required for fields.
17. For properties some of us specify pUserID. If you want to specify enter it by check the checkbox of is prefix required for properties.
18. To genereate the Data Tire Classes.
19. If your get procedure contains any parameter check the select.
20. If your Delete procedure contains any parameter check the select.
21. Select the column names. These are the parameters for procedure Insert and Update.
22. This is single select. This column will be your direction. For all the four procedures.
23. Select your ParameterDirection
24. Select your ParameterDirection.
25. Select your ParameterDirection.
26. Select your ParameterDirection.
27. If you are using SQLHelper class check it. The code generated accordingly.
28. If you want to save the calsses as file check it. It opens a folder dialogue to select a path where to store the class files.
29. After select you can view the selected path.
30. The tab Properties contains a class with properties.
31. The tab DAL contains a class with functions for calling Select, Insert, Delete Update procecures.
32. The tab SQLHelper contains a class with functions for calling Select, Insert, Delete Update procecures. This class uses SQLHelper.cs

Happy Coding.

ADO.NET Parameter Direction

Input InputOutput Output ReturnValue Passing value to procedure   or function. Pass value to the procedure or function and get back the assigned value from the procedure or function. Get value from the procedure or function. Get the value of the return statement. ParameterDirection .Input; ParameterDirection .InputOutput ParameterDirection .Output ParameterDirection .ReturnValue (@Columndatetime   datetime)    By default it is IN. So   it is not possible to use the keyword IN. (@Columndatetime datetime OUT)    Since OUT implies both IN and OUT and there is no INOUT. (@Columndatetime datetime OUT) We are not passing the parameter in Procedure or function.           DECLARE @Columnnumeric numeric, @ReturnValue NUMERIC SET @Columnnumeric = 10 EXEC @ReturnValue = [dbo]. [GetTestTable] @Columnnumeric, 'BABU' PRINT @ReturnValue We can assign the value any where in the procedure or function.          We can assign value at declaration itself.          (@Columndatetime datetime = 10 OUT)    If we didn’t assign value in the procedure or function it took the sent value.    If we didn’t pass value and we didn’t assing value in the procedure or function but we assign default value it took the default value. We can assign the value any where in the procedure or function.          We can assign value at declaration itself.          (@Columndatetime datetime = 10 OUT)       If we didn’t assign value in the procedure or function it took the sent value.    If we didn’t pass value and we didn’t assing value in the procedure or function but we assign default value it took the default value.       DECLARE @Columnnumeric numeric, @ReturnValue NUMERIC SET @Columnnumeric = 10 EXEC [dbo].[GetTestTable] @Columnnumeric OUT, 'BABU' PRINT @Columnnumeric This should be the last statement of the procedure or function.          return 10         or like this.    DECLARE @ReturnValue numeric SET @ReturnValue = 10 RETURN 10       Get value from the procedure or function.    DECLARE @Columnnumeric numeric, @ReturnValue NUMERIC SET @Columnnumeric = 10 EXEC @ReturnValue = [dbo].[GetTestTable] @Columnnumeric PRINT @ReturnValue                                         objCmd. Parameters .Add(" @Columnnumeric" , SqlDbType .Decimal) .Value = 10.0d; objCmd.Parameters .Add("@ Columnnumeric", SqlDbType. Decimal) .Value = 10.0d; objCmd. Parameters["@ Columnnumeric"] .Direction = Parameter Direction . InputOutput; objCmd. Parameters .Add("@ Columnnumeric", SqlDbType. Decimal). Value = 10.0d; objCmd. Parameters["@ Columnnumeric"] .Direction = Parameter Direction . Output; objCmd. Parameters .Add(" @Columnnumeric", SqlDbType .Decimal). Value = 10.0d; objCmd. Parameters [" @Columnnumeric"] .Direction = Parameter Direction .ReturnValue;           Decimal Columnnumeric = (Decimal)objCmd. Parameters ["@ColumnnTest"] .Value; Decimal Columnnumeric   = (Decimal)objCmd. Parameters ["@ColumnnTest"] .Value; Decimal Columnnumeric = (Decimal)objCmd. Parameters ["@ColumnnTest"] .Value;                               If the ParameterDirection is ParameterDirection .ReturnValue we should not use the parameter in prarameter declaration of the procedure or function. !supportEmptyParas]> <![endif]> http://weblogs.asp.net/andrewrea/archive/2008/02/19/examples-of-using-system-data-parameterdirection-with-sql-server.aspx Rules to follow Rule 1 If our ParameterDirection is ParameterDirection.ReturnValue and we are using in the parameter of the Procedure or Function it expects the parameter. And we throw this error. Procedure 'GetTestTable' expects parameter '@Columnnumeric', which was not supplied. We should not use the parameter in the procedure if we set ParameterDirection as ParameterDirection.ReturnValue in ADO.net. E.g.   CREATE PROCEDURE [dbo].[GetTestTable] (@Columndatetime DATETIME,                                        @Columnnumeric  NUMERIC) AS BEGIN       RETURN 15 END objCmd.Parameters.Add("@Columndatetime", SqlDbType.DateTime).Value = objPropertiesClassName.Columndatetime; objCmd.Parameters.Add("@Columnnumeric", SqlDbType.Decimal).Value = objPropertiesClassName.Columnnumeric; objCmd.Parameters["@Columnnumeric"].Direction = ParameterDirection.ReturnValue; objCmd.ExecuteNonQuery(); int i = (int)objCmd.Parameters["@Columnnumeric"].Value; Rule 2 Consider that we have given the parameter order as given below. The parameter @ColumnnTest will contain the return value. We will think that we have specified the direction ReturnValue to @Columnnumeric before @ColumnnTest and @Columnnumeric will contain the value. But it is not like that. Since we added the parameter @ColumnnTest before @Columnnumeric. It took according to the order of parameter that we are adding to the command. objCmd.Parameters.Add("@ColumnnTest", SqlDbType.Decimal).Value = objPropertiesClassName.Columnnumeric; objCmd.Parameters.Add("@Columnnumeric", SqlDbType.Decimal).Value = objPropertiesClassName.Columnnumeric; objCmd.Parameters["@Columnnumeric"].Direction = ParameterDirection.ReturnValue; objCmd.Parameters["@ColumnnTest"].Direction = ParameterDirection.ReturnValue; int j = (int)objCmd.Parameters["@ColumnnTest"].Value; object i = (object)objCmd.Parameters["@Columnnumeric"].Value; Rule 3 objCmd.Parameters.Add("@ColumnnTest", SqlDbType.Decimal).Value = 20.0d; objCmd.Parameters.Add("@Columnnumeric", SqlDbType.Decimal).Value = 10.0d; objCmd.Parameters["@Columnnumeric"].Direction = ParameterDirection.ReturnValue; objCmd.Parameters["@ColumnnTest"].Direction = ParameterDirection.ReturnValue;   If the procedure or function doesn’t return any value, the first added parameter tooks the default value.   But the next parameter took the value that we asigned.   Here @ColumnnTest returns 0.0d, while ("@Columnnumeric returns the assigned value 10.0d.   We can ask instead of ReturnValue can we use ExecuteScalar(), since it also returns single value. The ExecuteScalar returns a single value that is in the form of a result set.  That means the value must be "SELECTed" in the SQL. I.e. if the result-set contains 3 columns and 2 rows, it took only the 1st row and 0th column value. The remaining are discarded.  Using the RETURN keyword is possible through the use of a ReturnValue parameter. References Configuring Parameters and Parameter Data Types (ADO.NET) Examples of using System.Data.ParameterDirection with Sql Server