Looping with ADO.NET on DataTable filled from SQL

Private Sub IsAgencyFormCompleted()
        Dim application_id As Int32
        Dim sqlString As String
        Dim dtl_id As String = String.Empty
        Dim dt As New DataTable()
        Dim MyCtrl As ctrl_db_commands

        If Not Session("application_id") Is Nothing Then
            Try
                application_id = Int32.Parse(Session("application_id"))
                'SQL to check to see if the Agency Form is completed
                sqlString = "SELECT agcy_xxx_number, agcy_npx_number, agcy_number, agcy_primary_contact_title, agcy_executive_director, agcy_medical_director, agcy_entity_type_id, agcy_tax_status FROM [dbo].[TBL_101_APPLICATION] WHERE application_id = " & application_id
                Using conn As New SqlConnection(ConfigurationManager.ConnectionStrings("conn").ConnectionString)
                    conn.Open()
                    Using sda As New SqlDataAdapter(sqlString, conn)
                        sda.Fill(dt)
                    End Using
                End Using
            Catch ex As Exception
                Throw New ArgumentException("Exception Occured Contact Cardinal Innovations Solutions Delievery")
            End Try
        End If
        For Each dataRow As DataRow In dt.Rows
            For Each dataColumn As DataColumn In dt.Columns
                Dim fieldValue As String = dataRow(dataColumn).ToString()
                'The AgencyForm is not completed Exit For and display message
                If (fieldValue = "" Or fieldValue = "-1") Then
                    Me.Master.ShowSysMessage("Please complete the entire Section1-1 (Agency) before adding Facilities", "Error")
                    MyCtrl = DirectCast(Me.fv1.FindControl("ctrl_db_commands1"), ctrl_db_commands)
                    MyCtrl.Show_Add = False
                    MyCtrl.Show_Cancel = False
                    MyCtrl.Header_Text_HTML = "Please complete the entire Section1-1 (Agency) before adding Facilities"
                    Exit For
                End If
            Next
        Next
    End Sub

Programmer productivity -- Dr. Dobbs

Article on ALM products, but the first part is so true…  Worth a read 

Multitasking is good when it comes to computer programs, letting them do more with less. But when computer programmers start multitasking, productivity flies out the door. 

For one thing, when programmers have to shift tasks, it takes "a really, really, really long time," says Joel Spolsky, host of the Joel On Software Web site and co-founder of Fog Creek Software. Programmers have to keep a lot of things in their heads at once, Spolsky says, and the more they remember, the more productive they are. "A programmer coding at full throttle is keeping zillions of things in their head at once," he says, "everything from names of variables, data structures, important APIs, the names of utility functions that they call a lot, even the name of the subdirectory where they store their source code." 

On top of that, as applications have become more collaborative, complex, modular, and distributed, developers are having to track an increasing number of tasks and deal with more interruptions from the people with whom they're collaborating. As a result, they're multitasking more frequently and becoming less productive. 

How bad is the problem? Developers spend an average of 11 minutes on one task before being interrupted to deal with another, according to Gloria Mark of the University of California at Irvine's Department of Informatics, who has spent years examining developers' work environments. It then takes them 25 minutes to return to the original task. 

Keeping programmers productive in these fragmented work environments is a challenge for large software developers as well as for IT shops developing for end users. In both cases, application life-cycle management tools and processes can help. They automate steps -- such as change management, build processes, and testing -- in the development process, off-loading work from developers and cutting back on the number of interruptions they face. 

Rest of Article --- http://www.drdobbs.com/tools/programmer-productivity/220100341

ASP.NET Coding Thoughts

Sometimes you just have to bang your head until you get it.  I worked till 9:00 PM EST yesterday to figure out the Network App App.

It was very challenging and frustrating, but I did learn quite a few things.

Using TryCast() or DirectCast() methods don't work well if  you are not in the correct container that you are trying to cast, aka our famous formview container.

Hint Hint… the most of the user controls are all in the formview --- That got me.

I keep saying to myself, why the hell are you not picking up the usercontrol (Then it clicked)

 The past programmer did a great deed by hard coding values on properties when you pass the value to the properties…  No problem that was fixed for the  Show_Add() Property.   

Finally the call of the user control was overwriting the properties that I set because of the ASP.NET Page Load Cycle.  As a result the user control properties value sets are set in the Me.PreRenderComplete Event (GOTCHA)

So in all lessons learned.

My takeaways.

1. Use DirectCast when you know the value type and in this scenario were casting the user control  with ID = ctrl_db_commands1 

2. Find the control in the correct container, never trust old applications and always research where these controls exist

3. If your properties are not changing you better debug and find out why in this case the values where hard coded in the properties, which was updated to work correctly.

Wow, what a challenge, what next does work have for me, from this point forward I will not take any of this code for granted.

Clustered and Non-Clustered Indexes by Vijay Modi

What is cluster and Non-Cluster index and why we need it? Do you know? Just read this article and you will know all about it.

Most database administrators are familiar with the potential performance benefits they can gain through the judicious use of indexes on database tables. Indexes allow you to speed query performance on commonly used columns and improve the overall processing speed of your database.
Microsoft SQL Server supports two types of indexes:

-> Clustered indexes define the physical sorting of a database table’s rows in the storage media. For this reason, each database table may have only one clustered index. If a PRIMARY KEY constraint is created for a database table and no clustered index currently exists for that table, SQL Server automatically creates a clustered index on the primary key.
-> Non-clustered indexes are created outside of the database table and contain a sorted list of references to the table itself.
SQL Server 2000 supports a maximum of 249 non-clustered indexes per table. However, it’s important to keep in mind that non-clustered indexes slow down the data modification and insertion process, so indexes should be kept to a minimum

One of the hardest tasks facing database administrators is the selection of appropriate columns for non-clustered indexes. You should consider creating non-clustered indexes on any columns that are frequently referenced in the WHERE clauses of SQL statements. Other good candidates are columns referenced by JOIN and GROUP BY operations.
You may wish to also consider creating non-clustered indexes that cover all of the columns used by certain frequently issued queries. These queries are referred to as “covered queries” and experience excellent performance gains.

SQL Server provides a wonderful facility known as the Index Tuning Wizard which greatly enhances the index selection process. To use this tool, first use SQL Profiler to capture a trace of the activity for which you wish to optimize performance. You may wish to run the trace for an extended period of time to capture a wide range of activity. Then, using Enterprise Manager, start the Index Tuning Wizard and instruct it to recommend indexes based upon the captured trace. It will not only suggest appropriate columns for queries but also provide you with an estimate of the performance increase you’ll experience after making those changes!

Calendar Mask and AJAX

<asp:TextBox ID="txt_prov_srvc_from_dt" runat="server" MaxLength="10" />
                    <asp:ImageButton ID="ibtnServFromDtCal" AlternateText="Calendar" ImageUrl="~/images/calendar_icon_blue.gif" runat="server" CausesValidation="false" />
                                    <ajaxtoolkit:MaskedEditExtender ID="maskExtenderServFromDtCal" TargetControlID="txt_prov_srvc_from_dt" MaskType="None" Mask="99/99/9999" ClearMaskOnLostFocus="False" runat="server" />
                                    <ajaxtoolkit:CalendarExtender ID="maskCalExtenderServFromDtCal" TargetControlID="txt_prov_srvc_from_dt" PopupButtonID="ibtnServFromDtCal" Format="MM/dd/yyyy" runat="server" />
                    <asp:CompareValidator ID="ProvSrvcFromDtValidator" runat="server" ErrorMessage="CompareValidator" Type="Date" ControlToValidate="txt_prov_srvc_from_dt" Operator="DataTypeCheck" Display="Dynamic" Font-Bold="True">?</asp:CompareValidator>
                    <asp:TextBox ID="txt_prov_srvc_to_dt" runat="server" MaxLength="10" />
                    <asp:ImageButton ID="ibtnServToDtCal" AlternateText="Calendar" ImageUrl="~/images/calendar_icon_blue.gif" runat="server" CausesValidation="false" />
                                    <ajaxtoolkit:MaskedEditExtender ID="maskExtenderServToDtCal" TargetControlID="txt_prov_srvc_to_dt" MaskType="None" Mask="99/99/9999" ClearMaskOnLostFocus="False" runat="server" />
                                    <ajaxtoolkit:CalendarExtender ID="maskCalExtenderServToDtCal" TargetControlID="txt_prov_srvc_to_dt" PopupButtonID="ibtnServToDtCal" Format="MM/dd/yyyy" runat="server" />
                    <asp:CompareValidator ID="ProvSrvcToDtValidator" runat="server" ErrorMessage="CompareValidator" Type="Date" ControlToValidate="txt_prov_srvc_to_dt" Operator="DataTypeCheck" Display="Dynamic" Font-Bold="True">?</asp:CompareValidator>

ADO.NET 2012 - GET DATA from SQL with Using Statements

VB.NET
Private Function GetData() As String

        Dim intDataID As Integer
        Dim strFinalizedDate As String = "Not Completed"

        Try
            intDataID = Int32.Parse(lblDataID.Text)

            Using conn As New SqlConnection(ConfigurationManager.ConnectionStrings("conn_string").ConnectionString)
                conn.Open()
                Using cmd As New SqlCommand("SELECT update_dt FROM table WHERE data_id = " & intDataID, conn)
                    cmd.CommandType = CommandType.Text
                    Using reader As SqlDataReader = cmd.ExecuteReader()
                        If reader.HasRows Then
                           While reader.Read()
                                strFinalizedDate = reader("update_dt").ToString()
                            End While
                        End If
                    End Using
                End Using
            End Using

            Return strFinalizedDate

        Catch ex As Exception
            Throw

        End Try

    End Function

C#
try {
intDataID = Int32.Parse(lblDataID.Text);

using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings("conn_string").ConnectionString)) {
conn.Open();
using (SqlCommand cmd = new SqlCommand("SELECT update_dt FROM table WHERE data_id = " + intDataID, conn)) {
cmd.CommandType = CommandType.Text;
using (SqlDataReader reader = cmd.ExecuteReader()) {
if (reader.HasRows) {
while (reader.Read()) {
strFinalizedDate = reader("update_dt").ToString();
}
}
}
}
}

return strFinalizedDate;

} catch (Exception ex) {
throw;

}

To find out who is connected to database in SQL Server

There are three ways to find who is connected to a database in sqlserver.
First one:

• Use the SQL SERVER Management Studio -- Management -- Activity Monitor
• This gives a list of users, database names, status, command, transactions, application with which they are using the database, CPU, IO and Memory usage, Login time etc.

Second One:

• Use the built in stored procedure called sp_who2
• Run the command exec sp_who2
• This gives a list of users, database names, status, command, program with which they are using the database, CPU time, Login time etc.

Happy New Year

Goals for New Year

- .NET Entity Framework, know backwards and forwards
- JQuery
- Keep up with SharePoint

- Most important, GET IN SHAPE