Visual Studio, which version?

What version of Visual Studio is the most current?

Depending on what you are using and which application you are modifing the following are the latest releases of Visual Studio:

  • Visual Studio 6 (Used for the Legacy VB/C++ apps).
    • Latest build is 9782 and this corresponds to Visual Studio 6.0 SP6.
  • Visual Studio 2003
    • Version 7.1.6030. This corresponds to VS2003 SP1 and is a MANDATORY install. If you don't have it install it now.
  • Visual Studio 2005
    • Version 8.0.50727,762 (SP). This corresponds to VS2005 SP1 and is a MANDATORY install. If you don't have it install it now. It can also take over an hour to install.
  • Visual Studio 2008
    • Version 9.0.21022.8 RTM. This corresponds to the RTM release so if you have VS2008 you have this build.
  • Bottlenecks and Monitoring

    I've come across this fab system monitoring tool, Spotlight for Windows

    Spotlight on Windows empowers you to quickly identify and eliminate bottlenecks in the Windows environment

    For a busy IT professional, it is nearly impossible to diagnose, troubleshoot, and resolve every component affecting Windows Operating System (OS) performance using manual methods. Without a clear view of I/O and system activity, you learn of performance issues only after problems erupt.

    With its unique graphical view of the Windows OS internals, Spotlight on Windows will empower the user to rapidly eliminate and identify bottlenecks in the Windows environment. Displaying the real-time flow of data within your Windows OS, Spotlight enables you to quickly identify and resolve performance problems.

    Limitations:

    Spotlight on Windows is unsupported freeware. The license key is provided in the download package and expires one year after installation. To renew, please revisit this website. An activation key will be made available prior to the expiration date of your current key.

    http://www.quest.com/spotlight-on-windows/

    Examples of using System.Data.ParameterDirection with Sql Server

    When working with the SqlCommand you will no doubt have to supply parameters to your command whether it be a Stored Procedure or Text command.  Although not as efficient as an actual Stored Procedure there are some powerful things you can achieve using the System.Data.ParameterDirection, or rather other than just Input.

    The enumeration holds the following values:

    • Input
    • InputOutput
    • Output
    • ReturnValue

    In this article I will give a working example of each, simple but you will see the idea.  At the end I will make a custom object which I will populate using some of these directions.

    For this example I am working with the AdventureWorks Database for Sql Server 2005.

      

    Input

    The most common direction.

                using (System.Data.SqlClient.SqlConnection sc1 =
                    new System.Data.SqlClient.SqlConnection(@"Data Source=REA_ANDREW-PC\SQLEXPRESS;Initial Catalog=AdventureWorks;"+
                        "Integrated Security=True"))
                {
                    sc1.Open();
                    using (System.Data.SqlClient.SqlCommand command1 = new System.Data.SqlClient.SqlCommand())
                    {
                        command1.CommandType = CommandType.Text;
                        command1.Connection = sc1;
                        // DIRECTION :: Input
                        command1.CommandText = "INSERT INTO [Purchasing].[ShipMethod] (Name,ShipBase,ShipRate)"+
                            "VALUES (@Name,@ShipBase,@ShipRate)";
                        //Method 1
                        command1.Parameters.AddWithValue("@Name", "MyShippingMethod");
                        //Method 2
                        System.Data.SqlClient.SqlParameter parameter2 =
                            new System.Data.SqlClient.SqlParameter("@ShipBase", 10.00M);
                        parameter2.Direction = ParameterDirection.Input;
                        command1.Parameters.Add(parameter2);
                        //Method 3 //Deprecated for AddWithValue
                        command1.Parameters.Add("@ShipRate", 10.00M);

                        command1.ExecuteNonQuery();
                    }
                }

     

    Here I have displayed three different syntactical ways of adding parameters to your SqlCommand.  The third and final way is deprectaed and you will see this if you try inside Visual Studio.  Because of this it will not appear inside intellisense.

      

    Output

    The output is going to look like the above example only I will not give it an intial value and simply assign the value inside the command text.

                using (System.Data.SqlClient.SqlConnection sc1 =
                    new System.Data.SqlClient.SqlConnection(@"Data Source=REA_ANDREW-PC\SQLEXPRESS;Initial Catalog=AdventureWorks;"+
                        "Integrated Security=True"))
                {
                    sc1.Open();
                    using (System.Data.SqlClient.SqlCommand command1 = new System.Data.SqlClient.SqlCommand())
                    {
                        command1.CommandType = CommandType.Text;
                        command1.Connection = sc1;
                        // DIRECTION :: Input
                        command1.CommandText = "select @MyParameter = Count(*) FROM [Purchasing].[ShipMethod]";
                        System.Data.SqlClient.SqlParameter paramter1 = command1.Parameters.Add("@MyParameter", SqlDbType.SmallInt);
                        paramter1.Direction = ParameterDirection.Output;
                        command1.ExecuteNonQuery();
                        //The following value is now 6, the number of records inside the table
                        int newValue = (int)paramter1.Value;
                    }
                }

      

    Return Value

    The return value is something which you will return at the end of the statement/s.  For this example I need to create a short stored procedure, again i will simply return the count of records from the table:

    ALTER PROCEDURE CountRows
    ASDeclare @CountR intSELECT @CountR = Count(*) FROM [Purchasing].[ShipMethod]
    RETURN @CountR

    And so the function which will now work with this, and using the ReturnValue direction is as follows:

                using (System.Data.SqlClient.SqlConnection sc1 =
                    new System.Data.SqlClient.SqlConnection(@"Data Source=REA_ANDREW-PC\SQLEXPRESS;Initial Catalog=AdventureWorks;"+
                        "Integrated Security=True"))
                {
                    sc1.Open();
                    using (System.Data.SqlClient.SqlCommand command1 = new System.Data.SqlClient.SqlCommand())
                    {
                        command1.CommandType = CommandType.StoredProcedure;
                        command1.Connection = sc1;
                        // DIRECTION :: Input
                        command1.CommandText = "CountRows";
                        System.Data.SqlClient.SqlParameter paramter1 = command1.Parameters.Add("@CountR", SqlDbType.SmallInt);
                        paramter1.Direction = ParameterDirection.ReturnValue;
                        command1.ExecuteNonQuery();
                        //The following value is now 6, the number of records inside the table
                        int newValue = (int)paramter1.Value;
                    }
                }

     

      

    The simple example object

    I now want to use these counting methods to construct a sample object which will contain simply.

    1. Total Record Count
    2. List<string> of Shipping Names which can be bound to

    This is just for an example, I realise lol that I could simply use the List<string> Count property BUT think of it like this.  What if you wanted to create a paged object so the list actual only contains say ten items because of a page size you set BUT you still have knowledge of the total number of records through the Total Record Count property, from which you could calculate the total number of pages.!! 🙂

     

    The object

    namespace WindowsForm_Examples_NET_2
    {    class SimpleObjectOne
        {
            private int _recordCount;
            private List<string> _shippingNames;

            public List<string> ShippingNames
            {
                get { return _shippingNames; }
                set { _shippingNames = value; }
            }

            public int RecordCount
            {
                get { return _recordCount; }
                set { _recordCount = value; }
            }
            public SimpleObjectOne()
            {
                _shippingNames = new List<string>();
            }
            [System.ComponentModel.DataObjectMethod(
                System.ComponentModel.DataObjectMethodType.Select)]
            public static SimpleObjectOne GetSimpleObjectOne()
            {
                SimpleObjectOne objectOne = new SimpleObjectOne();
                objectOne.Execute();
                return objectOne;
            }

            private void Execute()
            {
                using (System.Data.SqlClient.SqlConnection sc1 =
        new System.Data.SqlClient.SqlConnection(@"Data Source=REA_ANDREW-PC\SQLEXPRESS;Initial Catalog=AdventureWorks;" +
            "Integrated Security=True"))
                {
                    sc1.Open();
                    using (System.Data.SqlClient.SqlCommand command1 = new System.Data.SqlClient.SqlCommand())
                    {
                        command1.CommandType = System.Data.CommandType.Text;
                        command1.Connection = sc1;
                        // DIRECTION :: Input
                        command1.CommandText = "SET @CountRows = (SELECT Count(*) as COUNTR FROM [Purchasing].[ShipMethod]);" +
                            "SELECT Name FROM [Purchasing].[ShipMethod];";
                        System.Data.SqlClient.SqlParameter parameter1 =
                            command1.Parameters.Add("@CountRows", System.Data.SqlDbType.Int);
                        parameter1.Direction = System.Data.ParameterDirection.Output;
                        using (System.Data.SqlClient.SqlDataReader reader = command1.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                _shippingNames.Add(reader["Name"] as string);
                            }
                        }
                        RecordCount = (int)parameter1.Value;
                        //The following value is now 6, the number of records inside the table
                    }
                }
            }
        }
    }

    An important note to make here, is if you are using the ExecuteReader on a statement and pass in Output parameters, you must close the data reader before you can access these output parameter values.  In the above you will see that I encase the data reader in a using statement block.

    And finally to consume this example I use the following three lines:

                SimpleObjectOne newObject = SimpleObjectOne.GetSimpleObjectOne();
                int RecordCount = newObject.RecordCount;
                List<string> names = newObject.ShippingNames;
     

    I now have 6 as the record count and a list of shipping names.

    C# DateTime.MinValue is not the same as SqlDateTime.MinValue

    Working with non-nullable types in C# can be a bit of a pain. For instance when I have a date as a string and need to parse it into a DateTime what should the value be if the parse fails? I can’t use null because DateTime is not a nullable type.

    This is exactly the dilema I encountered today. No worries, I’ll use DateTime.MinValue that way it is constant and I don’t have to worry about being consistent if I had chosen an arbitrary value of my own.

    Well as it turns out I did have some worries. Sql Server 2000’s minimum DateTime value is not the same, in fact it is quite different. This kept causing errors.

    For your reference here are some values you should take note of.

    DateTime myDate = DateTime.MinValue; //=> 1/1/0001
    SqlDateTime mySqlDate = SqlDateTime.MinValue; //=> 1/1/1753
    //also note that SQL Server’s smalldatetime min value is 1/1/1900

    If you need to use the smalldatetime you will need to create your own property to do this

    So my problem was easily averted, after a quick Google search. I just had to use SqlDateTime.MinValue instead of DateTime.MinValue.

    How to debug stored procedures in Visual Studio .NET

    SUMMARY

    This step-by-step article explains two ways that you can debug SQL Server stored procedures and the necessary configuration settings and steps for each approach.

    A Visual Studio .NET developer can use the Server Explorer to debug SQL Server stored procedures independently of any Visual Studio project, or the developer can step into the code of the stored procedure directly from managed code in a Visual Basic, Visual C#, or Visual J# project.

    Debug a stored procedure in standalone mode

    1. Open Server Explorer.

    NOTE: It is not necessary to add a Data Connection to work with a SQL Server server because SQL Server servers are listed under the Servers node also. You will use the Servers node in the steps that follow; however, you can use a Data Connection to you SQL Server server in the same way.

    2. Under the Servers node in Server Explorer, expand the SQL Server machine name, expand the SQL Servers node, expand the SQL Server instance, expand the Northwind database node, and then expand the stored procedures node.

    3. Right-click the CustOrderHist stored procedure and then click Step Into Stored Procedure.

    4. The Run stored procedure dialog box opens, which lists the parameters of the stored procedure. Type ALFKI as the value for the @CustomerID input parameter and then click OK.

    5. In the Visual Studio design environment, a window opens that displays the text of the stored procedure. The first executable line of the stored procedure is highlighted. Press F11 to step through the stored procedure to completion.

    6. In the Output window, the following message is displayed, which indicates successful execution:
    The program 'SQL Debugger: T-SQL' has exited with code 0 (0x0). 

    Best Windows Backup Tool

    Carbonite

    Well I've been using Carbonite for over 2 years now and with almost 100Gbs backed up online it can't be bad.  Carbonite runs in the background all the time, so it is always backing up your data files.  It's a don't worry type of backup, just install it and leave it.

    If I find I need more processing speed or I here the hard drive working hard, then I can pause Carbonite for 24 hours, and it stops it's searching for changed files.

    It's so simple and restoring files are as easy as coping your files from one drive to another, as Carbonite creates virtual drive of the backed up files.

    Carbonite

    Structure to Stored Procedure

    Everywhere I go from client to client I find that when developers create Stored Procedures that just perform the task in hand, and I so often find that no exception handling, comments or transaction based handling has been put in place.  So with all this in mind I set to write a structured Stored Procedure to be used as a template for future Stored Procedures that I create.

    -- =============================================
    -- Author: Bryan Avery
    -- Create date: 15th July 2008
    -- Description: Import the Taxonomy in to WPPs
    -- =============================================
    ALTER PROCEDURE [dbo].[sp_WaSPs_TaxonomyImport] 
     @Debug bit = 0,
     @ErrorMessage varchar(2000) OUTPUT, 
     @return_code Int OUTPUT
    
    AS
    BEGIN
    -- ========================================================
    -- Declare SProc variables 
    -- ========================================================
    
    DECLARE @Error int
     DECLARE @RowCount int
    
    -- ========================================================
    -- Initialise SProc variables 
    -- ========================================================
     
     SET NOCOUNT ON;
    
    -- ========================================================
    -- SProc content 
    -- ========================================================
    
    IF ( IsNull(@Debug,0) = 1 ) -- (debug info) 
     
    BEGIN 
     
    DECLARE @Debug_ProcStartTime datetime 
    , @Debug_ProcEndTime datetime 
     
    SET @Debug_ProcStartTime = Getdate() 
     
    SET NOCOUNT OFF 
     
     PRINT '' 
     
     PRINT '-- ======================================================' 
     PRINT '-- = PROCEDURE sp_WaSPs_TaxonomyImport' 
     PRINT '-- = @Debug = ' + IsNull(Cast(@Debug as varchar),'<NULL>') 
     PRINT '-- ======================================================' 
     
     PRINT '' 
     
     
    END
    
    IF ( IsNull(@Debug,0) = 1 ) -- (debug info) 
     
    BEGIN 
     
     PRINT '-- ******************************' 
     PRINT '-- * BEGIN TRANSACTION' 
     PRINT '-- ******************************' 
     
    END
    
    -- ========================================================
    -- Uncomment the section below if you want to remove all Taxonomy information from the application
    -- ========================================================
    
    -- DELETE tbl_WaSPs_GroupArea;
    -- DELETE tbl_WaSPs_AreaUserName;
    -- DELETE tbl_WaSPs_AreaSite;
    -- DELETE tbl_WaSPs_Areas;
    -- 
    -- DELETE tbl_WaSPs_SiteSite;
    -- DELETE tbl_WaSPs_Sites;
    
    
    BEGIN TRANSACTION
    
    
    BEGIN TRY
    
    -- ========================================================
    -- Update the areas
    -- ========================================================
    
    IF ( IsNull(@Debug,0) = 1 ) -- (debug info) 
     
    BEGIN 
     
     PRINT '-- ******************************' 
     PRINT '-- * Update the Areas' 
     PRINT '-- ******************************' 
     
    END
    
    UPDATE tbl_WaSPs_Areas
     SET AreaName = et2.EntityType, EntityTypeCD = et2.EntityTypeCD, myView = 'False'
     FROM WaspFactory.dbo.tblEntityType et2, tbl_WaSPs_Areas a1
     WHERE et2.EntityTypeCD = a1.EntityTypeCD 
     
    -- ========================================================
    -- Insert the areas
    -- ========================================================
     
    IF ( IsNull(@Debug,0) = 1 ) -- (debug info) 
     
    BEGIN 
     
     PRINT '-- ******************************' 
     PRINT '-- * Insert the Areas' 
     PRINT '-- ******************************' 
     
    END
    
    INSERT INTO tbl_WaSPs_Areas(AreaName, EntityTypeCD, myView)
     SELECT et2.EntityType, et2.EntityTypeCD, 'False'
     FROM WaspFactory.dbo.tblEntityType et2
     WHERE et2.EntityTypeCD NOT IN (SELECT EntityTypeCD FROM tbl_WaSPs_Areas)
    
    -- ========================================================
    -- Update the sites
    -- ========================================================
     
    IF ( IsNull(@Debug,0) = 1 ) -- (debug info) 
     
    BEGIN 
     
     PRINT '-- ******************************' 
     PRINT '-- * Update the Sites' 
     PRINT '-- ******************************' 
     
    END
    
    UPDATE tbl_WaSPs_Sites
     SET title = e1.EntityName, siteid = e1.EntityIdent, entityid = e1.EntityID
     FROM WaspFactory.dbo.tblEntity e1, tbl_WaSPs_Sites s1
     WHERE e1.EntityName IS NOT Null AND
     e1.EntityId = s1.entityid
    
    -- ========================================================
    -- Insert the sites
    -- ========================================================
     
    IF ( IsNull(@Debug,0) = 1 ) -- (debug info) 
     
    BEGIN 
     
     PRINT '-- ******************************' 
     PRINT '-- * Insert the Sites' 
     PRINT '-- ******************************' 
     
    END
    
    INSERT INTO tbl_WaSPs_Sites(title, siteid, entityid)
     SELECT EntityName, EntityIdent, EntityID
     FROM WaspFactory.dbo.tblEntity
     WHERE EntityName IS NOT Null AND
     EntityId NOT IN(SELECT entityid FROM tbl_WaSPs_Sites)
    
    -- ========================================================
    -- Remove all the links first
    -- ========================================================
     
    IF ( IsNull(@Debug,0) = 1 ) -- (debug info) 
     
    BEGIN 
     
     PRINT '-- ******************************' 
     PRINT '-- * Remove all the links first' 
     PRINT '-- ******************************' 
     
    END
    
    DELETE tbl_wasps_AreaSite
     FROM tbl_WaSPs_Areas Areas, WaspFactory.dbo.tblEntityType EntityType,
     tbl_WaSPs_Sites Sites, WaspFactory.dbo.tblEntity Entity
     WHERE Areas.EntityTypeCD = EntityType.EntityTypeCD AND
     Sites.EntityId = Entity.EntityId AND
     Entity.EntityTypeCD = EntityType.EntityTypeCD
    
    -- ========================================================
    -- Link the sites to the areas
    -- ========================================================
     
    IF ( IsNull(@Debug,0) = 1 ) -- (debug info) 
     
    BEGIN 
     
     PRINT '-- ******************************' 
     PRINT '-- * Link the sites to the areas' 
     PRINT '-- ******************************' 
     
    END
    
    INSERT INTO tbl_wasps_AreaSite(AreaId, SiteId)
     SELECT AreaId, Id
     FROM tbl_WaSPs_Areas Areas, WaspFactory.dbo.tblEntityType EntityType,
     tbl_WaSPs_Sites Sites, WaspFactory.dbo.tblEntity Entity
     WHERE Areas.EntityTypeCD = EntityType.EntityTypeCD AND
     Sites.EntityId = Entity.EntityId AND
     Entity.EntityTypeCD = EntityType.EntityTypeCD AND
     (AreaId NOT IN (SELECT AreaId FROM tbl_wasps_AreaSite) AND
     Id NOT IN (SELECT SiteId FROM tbl_wasps_AreaSite))
     
    -- ========================================================
    -- Remove all the site to site links
    -- ========================================================
     
    IF ( IsNull(@Debug,0) = 1 ) -- (debug info) 
     
    BEGIN 
     
     PRINT '-- ******************************' 
     PRINT '-- * Remove all the site to site links' 
     PRINT '-- ******************************' 
     
    END
    
    DELETE tbl_WaSPs_SiteSite
     WHERE SiteId = fkSiteId
    
    -- ========================================================
    -- Link the site to themself
    -- ========================================================
    
    IF ( IsNull(@Debug,0) = 1 ) -- (debug info) 
     
    BEGIN 
     
     PRINT '-- ******************************' 
     PRINT '-- * Link the site to themself' 
     PRINT '-- ******************************' 
     
    END
    
    INSERT INTO tbl_WaSPs_SiteSite(SiteID, fkSiteId, WaterDirection)
     SELECT ID, ID, 'Neither'
     FROM tbl_WaSPs_Sites
    
    -- ========================================================
    -- Delete all the site to site links
    -- ========================================================
    
    IF ( IsNull(@Debug,0) = 1 ) -- (debug info) 
     
    BEGIN 
     
     PRINT '-- ******************************' 
     PRINT '-- * Delete all the site to site links' 
     PRINT '-- ******************************' 
     
    END
    
    DELETE tbl_WaSPs_SiteSite
     FROM tbl_WaSPs_Sites s1, WaspFactory.dbo.tblEntity e1,
     WaspFactory.dbo.tblEntityLink el1, 
     WaspFactory.dbo.tblEntity e2, 
     tbl_WaSPs_Sites s2
     WHERE e1.EntityId = s1.EntityId AND
     s1.EntityId = el1.EntityID1 AND
     el1.EntityID2 = e2.EntityId AND
     e2.Entityid = s2.EntityId
    
    -- ========================================================
    -- Link the site to other sites
    -- ========================================================
    
    IF ( IsNull(@Debug,0) = 1 ) -- (debug info) 
     
    BEGIN 
     
     PRINT '-- ******************************' 
     PRINT '-- * Link the site to other sites' 
     PRINT '-- ******************************' 
     
    END
    
    INSERT INTO tbl_WaSPs_SiteSite(SiteID, fkSiteId, WaterDirection)
     SELECT s1.ID, s2.ID, 'Neither'
     FROM tbl_WaSPs_Sites s1, WaspFactory.dbo.tblEntity e1,
     WaspFactory.dbo.tblEntityLink el1, 
     WaspFactory.dbo.tblEntity e2, 
     tbl_WaSPs_Sites s2
     WHERE e1.EntityId = s1.EntityId AND
     s1.EntityId = el1.EntityID1 AND
     el1.EntityID2 = e2.EntityId AND
     e2.Entityid = s2.EntityId
     
    END TRY 
     
    BEGIN CATCH 
     
     SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT ; IF (@Error <> 0 OR @RowCount = 0) GOTO QuitWithRollback -- (check for error) 
     
    END CATCH
    
    -- ---------------------------------------------------------------------------------------------------- 
    -- Commit Transaction 
    -- ---------------------------------------------------------------------------------------------------- 
     
    IF (@@TRANCOUNT > 0) 
     
    BEGIN 
     
     IF ( IsNull(@Debug,0) = 1 ) -- (debug info) 
     
     BEGIN 
     
     PRINT '-- ******************************' 
     PRINT '-- * COMMIT TRANSACTION' 
     PRINT '-- ******************************' 
     
     END 
     
     COMMIT TRANSACTION 
     
    END
    
    GOTO QuitWithSuccess
    
    -- ---------------------------------------------------------------------------------------------------- 
    -- Error Handling 
    -- ---------------------------------------------------------------------------------------------------- 
    QuitWithConcurrencyRollback: 
     
    IF (@@TRANCOUNT > 0) 
     
     BEGIN 
     
     IF ( IsNull(@Debug,0) = 1 ) -- (debug info) 
     
     BEGIN 
     
     PRINT '-- ******************************' 
     PRINT '-- * ROLLBACK TRANSACTION' 
     PRINT '-- ******************************' 
     
     END 
     
     ROLLBACK TRANSACTION 
     
     END 
     
    GOTO QuitWithConcurrencyError
    
    -------------------------------------------------- 
    QuitWithRollback: 
     
    IF (@@TRANCOUNT > 0) 
     
     BEGIN 
     
     IF ( IsNull(@Debug,0) = 1 ) -- (debug info) 
     
     BEGIN 
     
     PRINT '-- ******************************' 
     PRINT '-- * ROLLBACK TRANSACTION' 
     PRINT '-- ******************************' 
     
     END 
     
     ROLLBACK TRANSACTION 
     
    END 
     
    GOTO QuitWithError
    
    -------------------------------------------------- 
    QuitWithConcurrencyError: 
     
    IF ( IsNull(@Debug,0) = 1 ) -- (debug info) 
     
    BEGIN 
     
     SET @Debug_ProcEndTime = Getdate() 
     
     PRINT '' 
     PRINT '-- ======================================================' 
     PRINT '-- = sp_WaSPs_TaxonomyImport' 
     PRINT '-- = RETURN VALUE' 
     PRINT '-- = QuitWithConcurrencyError' 
     PRINT '-- = @ErrorMessage = ' + IsNull(Cast(@ErrorMessage as varchar),'<NULL>') 
     PRINT '-- = EXECUTION TIME' 
     PRINT '-- = Start = ' + IsNull(Convert(varchar(24), @Debug_ProcStartTime , 113),'<NULL>') 
     PRINT '-- = End = ' + IsNull(Convert(varchar(24), @Debug_ProcEndTime , 113),'<NULL>') 
     PRINT '-- = Ellapsed Time (ms) = ' + IsNull(Cast(Datediff(millisecond, @Debug_ProcStartTime, @Debug_ProcEndTime) as varchar),'<NULL>') 
     PRINT '-- ======================================================' 
     PRINT '' 
     
    END 
     
    SET @ErrorMessage = 'Concurrency Error' 
     
    SET @return_code = 3 
     
    RETURN(3)
    
    -------------------------------------------------- 
    QuitWithError: 
     
    IF ( IsNull(@Debug,0) = 1 ) -- (debug info) 
     
    BEGIN 
     
     SET @Debug_ProcEndTime = Getdate() 
     
     PRINT '' 
     PRINT '-- ======================================================' 
     PRINT '-- = sp_WaSPs_TaxonomyImport' 
     PRINT '-- = RETURN VALUE' 
     PRINT '-- = QuitWithError' 
     PRINT '-- = @Error = ' + IsNull(Cast(@Error as varchar),'<NULL>') 
     PRINT '-- = @return_code = ' + IsNull(Cast(@return_code as varchar),'<NULL>') 
     PRINT '-- = EXECUTION TIME' 
     PRINT '-- = Start = ' + IsNull(Convert(varchar(24), @Debug_ProcStartTime , 113),'<NULL>') 
     PRINT '-- = End = ' + IsNull(Convert(varchar(24), @Debug_ProcEndTime , 113),'<NULL>') 
     PRINT '-- = Ellapsed Time (ms) = ' + IsNull(Cast(Datediff(millisecond, @Debug_ProcStartTime, @Debug_ProcEndTime) as varchar),'<NULL>') 
     PRINT '-- ======================================================' 
     PRINT '' 
     
    END 
     
    SET @ErrorMessage = 'SQL Error' 
     
    SET @return_code = 2 
     
    RETURN(2)
    
    -------------------------------------------------- 
    QuitWithParameterError: 
     
    IF ( IsNull(@Debug,0) = 1 ) -- (debug info) 
     
    BEGIN 
     
     SET @Debug_ProcEndTime = Getdate() 
     PRINT '' 
     PRINT '-- ======================================================' 
     PRINT '-- = sp_WaSPs_TaxonomyImport' 
     PRINT '-- = RETURN VALUE' 
     PRINT '-- = QuitWithParameterError' 
     PRINT '-- = @ErrorMessage = ' + IsNull(Cast(@ErrorMessage as varchar),'<NULL>') 
     PRINT '-- = EXECUTION TIME' 
     PRINT '-- = Start = ' + IsNull(Convert(varchar(24), @Debug_ProcStartTime , 113),'<NULL>') 
     PRINT '-- = End = ' + IsNull(Convert(varchar(24), @Debug_ProcEndTime , 113),'<NULL>') 
     PRINT '-- = Ellapsed Time (ms) = ' + IsNull(Cast(Datediff(millisecond, @Debug_ProcStartTime, @Debug_ProcEndTime) as varchar),'<NULL>') 
     PRINT '-- ======================================================' 
     PRINT '' 
     
    END 
     
     
    SET @return_code = 1 
     
    RETURN(1) 
    ------------------------------------------------ 
    QuitWithSuccess: 
     
    IF ( IsNull(@Debug,0) = 1 ) -- (debug info) 
     
    BEGIN 
     
     SET @Debug_ProcEndTime = Getdate() 
     
     PRINT '' 
     PRINT '-- ======================================================' 
     PRINT '-- = sp_WaSPs_TaxonomyImport' 
     PRINT '-- = RETURN VALUE' 
     PRINT '-- = QuitWithSuccess' 
     PRINT '-- = EXECUTION TIME' 
     PRINT '-- = Start = ' + IsNull(Convert(varchar(24), @Debug_ProcStartTime , 113),'<NULL>') 
     PRINT '-- = End = ' + IsNull(Convert(varchar(24), @Debug_ProcEndTime , 113),'<NULL>') 
     PRINT '-- = Ellapsed Time (ms) = ' + IsNull(Cast(Datediff(millisecond, @Debug_ProcStartTime, @Debug_ProcEndTime) as varchar),'<NULL>') 
     PRINT '-- ======================================================' 
     PRINT '' 
     
    END 
     
    SET @return_code = 0 
     
    RETURN(0) 
    -- -------------------------------------------------- 
    END

    StoredProcedure

    The SqlCommand Object

    I found this article on csharp station, as a very good insight in to the SqlCommand object, enjoy: 

    This lesson describes the SqlCommand object and how you use it to interact with a data base.  Here are the objectives of this lesson:

    Know what a command object is.

    • Learn how to use the ExecuteReader method to query data.
    • Learn how to use the ExecuteNonQuery method to insert and delete data.
    • Learn how to use the ExecuteScalar method to return a single value.

    Introduction

    A SqlCommand object allows you to specify what type of interaction you want to perform with a data base.  For example, you can do select, insert, modify, and delete commands on rows of data in a data base table.  The SqlCommand object can be used to support disconnected data management scenarios, but in this lesson we will only use the SqlCommand object alone.  A later lesson on the SqlDataAdapter will explain how to implement an application that uses disconnected data.  This lesson will also show you how to retrieve a single value from a data base, such as the number of records in a table.

    Creating a SqlCommand Object

    Similar to other C# objects, you instantiate a SqlCommand object via the new instance declaration, as follows:

        SqlCommand cmd = new SqlCommand("select CategoryName from Categories", conn);

    The line above is typical for instantiating a SqlCommand object.  It takes a string parameter that holds the command you want to execute and a reference to a SqlConnection object.  SqlCommand has a few overloads, which you will see in the examples of this tutorial.

    Querying Data

    When using a SQL select command, you retrieve a data set for viewing.  To accomplish this with a SqlCommand object, you would use the ExecuteReader method, which returns a SqlDataReader object.  We'll discuss the SqlDataReader in a future lesson.  The example below shows how to use the SqlCommand object to obtain a SqlDataReader object:

    // 1. Instantiate a new command with a query and connection
    SqlCommand cmd = new SqlCommand("select CategoryName from Categories", conn);

    // 2. Call Execute reader to get query results
    SqlDataReader rdr = cmd.ExecuteReader();

    In the example above, we instantiate a SqlCommand object, passing the command string and connection object to the constructor.  Then we obtain a SqlDataReader object by calling the ExecuteReader method of the SqlCommand object, cmd. 

    This code is part of the ReadData method of Listing 1 in the Putting it All Together section later in this lesson.

    Inserting Data

    To insert data into a data base, use the ExecuteNonQuery method of the SqlCommand object.  The following code shows how to insert data into a data base table:

    // prepare command string
     string insertString = @"
         insert into Categories
         (CategoryName, Description)
         values ('Miscellaneous', 'Whatever doesn''t fit elsewhere')";
     
     // 1. Instantiate a new command with a query and connection
     SqlCommand cmd = new SqlCommand(insertString, conn);
     
     // 2. Call ExecuteNonQuery to send command
     cmd.ExecuteNonQuery();

    The SqlCommand instantiation is just a little different from what you've seen before, but it is basically the same.  Instead of a literal string as the first parameter of the SqlCommand constructor, we are using a variable, insertString.  The insertString variable is declared just above the SqlCommand declaration. 

    Notice the two apostrophes ('') in the insertString text for the word "doesn''t".  This is how you escape the apostrophe to get the string to populate column properly. 

    Another observation to make about the insert command is that we explicitly specified the columns CategoryName and Description.  The Categories table has a primary key field named CategoryID.  We left this out of the list because SQL Server will add this field itself.  trying to add a value to a primary key field, such as CategoryID, will generate an exception.

    To execute this command, we simply call the ExecuteNonQuery method on the SqlCommand instance, cmd.

    This code is part of the Insertdata method of Listing 1 in the Putting it All Together section later in this lesson.

    Updating Data

    The ExecuteNonQuery method is also used for updating data.  The following code shows how to update data:

    // prepare command string
     string updateString = @"
         update Categories
         set CategoryName = 'Other'
         where CategoryName = 'Miscellaneous'";
     
     // 1. Instantiate a new command with command text only
     SqlCommand cmd = new SqlCommand(updateString);
     
     // 2. Set the Connection property
     cmd.Connection = conn;
     
     // 3. Call ExecuteNonQuery to send command
     cmd.ExecuteNonQuery();

    Again, we put the SQL command into a string variable, but this time we used a different SqlCommand constructor that takes only the command.  In step 2, we assign the SqlConnection object, conn, to the Connection property of the SqlCommand object, cmd. 

    This could have been done with the same constructor used for the insert command, with two parameters.  It demonstrates that you can change the connection object assigned to a command at any time.

    The ExecuteNonQuery method performs the update command.

    This code is part of the UpdateData method of Listing 1 in the Putting it All Together section later in this lesson.

    Deleting Data

    You can also delete data using the ExecuteNonQuery method.  The following example shows how to delete a record from a data base with the ExecuteNonQuery method:

    // prepare command string
     string deleteString = @"
         delete from Categories
         where CategoryName = 'Other'";
     
     // 1. Instantiate a new command
     SqlCommand cmd = new SqlCommand();
     
     // 2. Set the CommandText property
     cmd.CommandText = deleteString;
     
     // 3. Set the Connection property
     cmd.Connection = conn;
     
     // 4. Call ExecuteNonQuery to send command
     cmd.ExecuteNonQuery();

    This example uses the SqlCommand constructor with no parameters.  Instead, it explicity sets the CommandText and Connection properties of the SqlCommand object, cmd. 

    We could have also used either of the two previous SqlCommand constructor overloads, used for the insert or update command, with the same result.  This demonstrates that you can change both the command text and the connection object at any time. 

    The ExecuteNonQuery method call sends the command to the data base.

    This code is part of the DeleteData method of Listing 1 in the Putting it All Together section later in this lesson.

    Getting Single values

    Sometimes all you need from a data base is a single value, which could be a count, sum, average, or other aggregated value from a data set.  Performing an ExecuteReader and calculating the result in your code is not the most efficient way to do this.  The best choice is to let the data base perform the work and return just the single value you need.  The following example shows how to do this with the ExecuteScalar method:

    // 1. Instantiate a new command
     SqlCommand cmd = new SqlCommand("select count(*) from Categories", conn);
     
     // 2. Call ExecuteNonQuery to send command
     int count = (int)cmd.ExecuteScalar();

    The query in the SqlCommand constructor obtains the count of all records from the Categories table.  This query will only return a single value.  The ExecuteScalar method in step 2 returns this value.  Since the return type of ExecuteScalar is type object, we use a cast operator to convert the value to int.

    This code is part of the GetNumberOfRecords method of Listing 1 in the Putting it All Together section later in this lesson.

    Putting it All Together

    For simplicity, we showed snippets of code in previous sections to demonstrate the applicable techniques .  It is also useful to have an entire code listing to see how this code is used in a working program.  Listing 1 shows all of the code used in this example, along with a driver in the Main method to produce formatted output.

    Listing 1.  SqlConnection Demo
     using System;
     using System.Data;
     using System.Data.SqlClient;
     
     /// <summary>
     /// Demonstrates how to work with SqlCommand objects
     /// </summary>
     class SqlCommandDemo
     {
         SqlConnection conn;
     
         public SqlCommandDemo()
         {
             // Instantiate the connection
             conn = new SqlConnection(
                "Data Source=(local);Initial Catalog=Northwind;Integrated Security=SSPI");
         }
     
         // call methods that demo SqlCommand capabilities
         static void Main()
         {
             SqlCommandDemo scd = new SqlCommandDemo();
     
             Console.WriteLine();
             Console.WriteLine("Categories Before Insert");
             Console.WriteLine("————————");
     
             // use ExecuteReader method
             scd.ReadData();
     
             // use ExecuteNonQuery method for Insert
             scd.Insertdata();
             Console.WriteLine();
             Console.WriteLine("Categories After Insert");
             Console.WriteLine("——————————");
     
            scd.ReadData();
     
             // use ExecuteNonQuery method for Update
             scd.UpdateData();
     
             Console.WriteLine();
             Console.WriteLine("Categories After Update");
             Console.WriteLine("——————————");
     
             scd.ReadData();
     
             // use ExecuteNonQuery method for Delete
             scd.DeleteData();
     
             Console.WriteLine();
             Console.WriteLine("Categories After Delete");
             Console.WriteLine("——————————");
     
             scd.ReadData();
     
             // use ExecuteScalar method
             int numberOfRecords = scd.GetNumberOfRecords();
     
             Console.WriteLine();
             Console.WriteLine("Number of Records: {0}", numberOfRecords);
         }
     
         /// <summary>
         /// use ExecuteReader method
         /// </summary>
         public void ReadData()
         {
            SqlDataReader rdr = null;
     
             try
             {
                 // Open the connection
                 conn.Open();
     
                 // 1. Instantiate a new command with a query and connection
                 SqlCommand cmd = new SqlCommand("select CategoryName from Categories", conn);
     
                 // 2. Call Execute reader to get query results
                 rdr = cmd.ExecuteReader();
     
                 // print the CategoryName of each record
                 while (rdr.Read())
                 {
                     Console.WriteLine(rdr[0]);
                 }
             }
             finally
             {
                 // close the reader
                 if (rdr != null)
                 {
                     rdr.Close();
                 }
     
                 // Close the connection
                 if (conn != null)
                 {
                     conn.Close();
                 }
             }
         }
     
         /// <summary>
         /// use ExecuteNonQuery method for Insert
         /// </summary>
         public void Insertdata()
         {
             try
             {
                 // Open the connection
                 conn.Open();
     
                 // prepare command string
                 string insertString = @"
                     insert into Categories
                     (CategoryName, Description)
                     values ('Miscellaneous', 'Whatever doesn''t fit elsewhere')";
     
                 // 1. Instantiate a new command with a query and connection
                 SqlCommand cmd = new SqlCommand(insertString, conn);
     
                 // 2. Call ExecuteNonQuery to send command
                 cmd.ExecuteNonQuery();
             }
             finally
             {
                 // Close the connection
                 if (conn != null)
                 {
                     conn.Close();
                 }
             }
         }
     
         /// <summary>
         /// use ExecuteNonQuery method for Update
         /// </summary>
         public void UpdateData()
         {
             try
             {
                 // Open the connection
                 conn.Open();
     
                 // prepare command string
                 string updateString = @"
                     update Categories
                     set CategoryName = 'Other'
                     where CategoryName = 'Miscellaneous'";
     
                 // 1. Instantiate a new command with command text only
                 SqlCommand cmd = new SqlCommand(updateString);
     
                 // 2. Set the Connection property
                 cmd.Connection = conn;
     
                 // 3. Call ExecuteNonQuery to send command
                 cmd.ExecuteNonQuery();
            }
             finally
             {
                 // Close the connection
                 if (conn != null)
                 {
                     conn.Close();
                 }
             }
         }
     
         /// <summary>
         /// use ExecuteNonQuery method for Delete
         /// </summary>
         public void DeleteData()
         {
             try
             {
                 // Open the connection
                 conn.Open();
     
                 // prepare command string
                 string deleteString = @"
                     delete from Categories
                     where CategoryName = 'Other'";
     
                 // 1. Instantiate a new command
                 SqlCommand cmd = new SqlCommand();
     
                 // 2. Set the CommandText property
                 cmd.CommandText = deleteString;
     
                 // 3. Set the Connection property
                 cmd.Connection = conn;
     
                 // 4. Call ExecuteNonQuery to send command
                 cmd.ExecuteNonQuery();
             }
             finally
             {
                 // Close the connection
                 if (conn != null)
                 {
                     conn.Close();
                 }
             }
         }
     
         /// <summary>
         /// use ExecuteScalar method
         /// </summary>
         /// <returns>number of records</returns>
         public int GetNumberOfRecords()
         {
             int count = -1;
     
             try
             {
                 // Open the connection
                 conn.Open();
     
                 // 1. Instantiate a new command
                 SqlCommand cmd = new SqlCommand("select count(*) from Categories", conn);
     
                 // 2. Call ExecuteNonQuery to send command
                 count = (int)cmd.ExecuteScalar();
             }
             finally
             {
                // Close the connection
                 if (conn != null)
                 {
                     conn.Close();
                 }
             }
             return count;
         }
     }

    In Listing 1, the SqlConnection object is instantiated in the SqlCommandDemo structure.  This is okay because the object itself will be cleaned up when the CLR garbage collector executes.  What is important is that we close the connection when we are done using it.  This program opens the connection in a try block and closes it in a finally block in each method.

    The ReadData method displays the contents of the CategoryName column of the Categories table.  We use it several times in the Main method to show the current status of the Categories table, which changes after each of the insert, update, and delete commands.  Because of this, it is convenient to reuse to show you the effects after each method call.

    Summary

    A SqlCommand object allows you to query and send commands to a data base.  It has methods that are specialized for different commands.  The ExecuteReader method returns a SqlDataReader object for viewing the results of a select query.  For insert, update, and delete SQL commands, you use the ExecuteNonQuery method.  If you only need a single aggregate value from a query, the ExecuteScalar is the best choice.

     

    Determine the Connection Speed of your client

    How many times have you written the perfect application, you spend hours getting it right, only to be told it is running slow.  You have performed all the Performance Tuning, and you still get the users saying it is running slowly.

    One issue is with the clients connection speed, they may be over GPRS or a dial up connection.  You have no control over the users connections speeds, so to help this out why not check their connection speed and if it is running slow then provide a warning message.

    The following code will work out your clients connection speed.  The only issue with such code is you have to wait until it is completed before you know the speed.  To over come this I use an iFrame on the web page, so to speed of connection is worked out in a separate window to the main application.  Meaning the client can continue to work while the calculation is taking place.

    What and how you tell the client, is really up to you, one way is to display a JavaScript alert message giving them a warning, but be careful not to display this information too many times as they may get a little annoyed being told they have a slow connection speed when they already know they have,

    int numKB = 512;

    DateTime start;

    DateTime end;

    TimeSpan duration;

    int length = 0;

    string checklen = "\n";

    private double CalculateSpeed()

    {

    Response.Flush();

    start = DateTime.Now;

    length = checklen.Length;

    for (int i = 0; i < numKB; i++)

    {

    hfSpeedTest.Value += "".PadRight(1024 – length, "/*\\*"[0]) + "\n";

    Response.Flush();

    }

    end = DateTime.Now;

    duration = (end – start);

    double timediff = numKB / duration.TotalSeconds;

    double speed = System.Math.Round(timediff, 3);hfSpeedTest.Value =

    ""; return speed;

    }