Changing Dynamic SQL to Static SQL

I've been on this project for a couple of weeks now, changing store procedures that use dynamic sql to a static sql. The reason for this change is too speed up the store procedures.

The store procedure might have a dynamic sql statement like:

SET @tsql = 'SELECT * FROM TABLE1
WHERE TABLE1.Name = ' + @Name

IF @ID <> ''
@tsql = @tsql + ' AND TABLE1.ID = ' + @ID

exec sp_executeSql @tSqlQuery

This would be changed to:

SELECT * FROM TABLE1 WHERE TABLE1.Name = @Name
AND( (@ID <> '' AND TABLE1.ID = @ID) OR
(@ID = '' ))

Now came the problem if they dynamically set a column to be sorted:

IF LTRIM(RTRIM(@sortColumn)) <> ''
@tsql = @tsql + 'ORDER BY ' + @sortColumn

This unfortunately had to be solved by making a case statement for all possible columns that are returned. So in this case this table returns only two columns (name and id):

SELECT NAME, ID FROM TABLE1
WHERE TABLE1.Name = @Name AND ((@ID <> ''
AND TABLE1.ID = @ID) OR (@ID = '' ))
ORDER BY
CASE @sortColumn WHEN 'ID' THEN ID ELSE NULL END,
CASE @sortColumn WHEN 'NAME' THEN NAME ELSE NULL END

The reason for the seperate case statements in the example is because it can only return one data type. If NAME is of varchar and ID is of int, then they have to be separated.

Well, that's a very basic and simple run down of what I've been doing. I do run into larger more complex store procedures and other situations. For example, when a dynamic sql statement is using a table name as a variable.

SQL: The Curse and Blessings of Dynamic SQL

http://www.sommarskog.se/dynamic_sql.html

A good read on Dynamic SQL.

Rollbacks and Commits in Stored Procedures

So, I ran into this problem of my transaction counts being offset. The problem was that store procedure A has a "Begin Transaction" then calls store procedure B which also has it's transactions of begin, commit, and rollback.

Microsoft says:

If @@TRANCOUNT has a different value when a stored procedure finishes than it had when the procedure was executed, an informational error (266) occurs. This can happen in two ways:

A stored procedure is called with an @@TRANCOUNT of 1 or greater and the stored procedure executes a ROLLBACK TRANSACTION statement. @@TRANCOUNT decrements to 0 and causes an error 266 when the stored procedure completes.


A stored procedure is called with an @@TRANCOUNT of 1 or greater and the stored procedure executes a COMMIT TRANSACTION statement. @@TRANCOUNT decrements by 1 and causes an error 266 when the stored procedure completes. However, if BEGIN TRANSACTION is executed after the COMMIT TRANSACTION, the error does not occur.

-- http://msdn2.microsoft.com/en-us/library/ms187844.aspx

DECLARE @LocalTransActive Bit
IF @@TRANCOUNT = 0
BEGIN
BEGIN TRANSACTION Trans_Discharge
SET @LocalTransActive = 1
END

For the commit part:
IF @LocalTransActive = 1
BEGIN
COMMIT TRANSACTION Trans_Discharge
END

For the rollback part:
IF @@TRANCOUNT > 0
BEGIN
IF @LocalTransActive = 1
BEGIN
ROLLBACK TRANSACTION Trans_Discharge
END
END

VB.Net: An editable combo box

So, I had to make a editable combo box took me longer then I've hoped.

In a custom made datagrid combo box column, which inherits from
DataGridColumnStyle. I have 3 functions that were mainly used in this custom datagrid combo box column. (BTW I also have a custom comboBox control, but nothing in that control that was needed to make this work):


Public Sub New(ByVal colName As String, ByVal dataSource As DataTable, ByVal displayMember As String, ByVal valueMember As String, ByVal dataGrid As DataGrid, ByVal editable As Boolean)

...
_ComboBox = New ctlComboBox
_ComboBox.Visible = True
_hasTextBox = editable

_dv = dataSource.DefaultView
_ComboBox.DataSource = _dv
_dataTable = dataSource
_dataTableAll = dataSource
_ComboBox.DisplayMember = displayMember
_displayMember = displayMember
_ComboBox.ValueMember = valueMember
_valueMember = valueMember

Me.Editable()

Dim _graphicsContext As Graphics = dataGrid.CreateGraphics
Dim _widest As Double = 1
Dim _stringSize As SizeF = New SizeF(0, 0)

Dim dr As DataRow
For Each dr In dataSource.Rows
_stringSize = _graphicsContext.MeasureString(dr(displayMember).ToString, dataGrid.Font)
If (_stringSize.Width > _widest) Then
_widest = _stringSize.Width
End If
Next
_ComboBox.DropDownWidth = CType(Math.Ceiling(_widest), Integer)
Me.Width = _ComboBox.DropDownWidth + 25
Me.MappingName = colName
Me.HeaderText = colName
dataGrid.Controls.Add(_ComboBox)
End Sub

--AND--

Private Sub _comboBox_MouseUp(ByVal sender As Object, ByVal e As System.EventArgs) Handles _ComboBox.SelectionChangeCommitted
If (_hasTextBox) Then
RaiseEvent ComboBox_Edit(Me._ComboBox.Text)
End If
End Sub

--AND--


Public Sub Editable()
Dim dropDownButtonWidth As Integer = 14
If _hasTextBox Then
_ComboBox.DrawMode = DrawMode.Normal
_ComboBox.DropDownStyle() = ComboBoxStyle.DropDown
_ComboBox.Show()
_ComboBox.Visible() = True
_ComboBox.Focus()
HideComboBox()
End If
End Sub


###
Okay, now in my form I have a function that handles the event I throw from the datagrid combo box:
###


Private Sub ComboBox_Edit(ByVal value As String) Handles colName.ComboBox_Edit
Try

Me.dvShifts.Table().Rows(Me.dgrShifts.CurrentRowIndex)("Name") = value 'Set the value selected

If (Me.colName.SelectedIndex > -1 AndAlso _
Not IsDBNull(dsShifts.Tables(1).Rows(Me.colName.SelectedIndex())("ID"))) Then

'Set the datagrid values based on the selected information if need to.

End If

'Make a new column to implement changes if edited
colName = New ctlDataGridComboBoxColumn("Name", dsShifts.Tables(1), "Name", "Name", Me.dgrShifts, True)
colName.MappingName = "Name"
colName.HeaderText = "Name"

Me.colName.SelectedValue() = value
Me.colName.DataSourceTable() = dsShifts.Tables(1)
colName.Width() = ResizeColumn("Name", "Name")
'Some other custom function calls
...

Catch ex As Exception
'adding new row to datagrid
If (Me.colName.SelectedIndex > -1 AndAlso _
Not IsDBNull(dsShifts.Tables(1).Rows(Me.colName.SelectedIndex())("Shift_ID"))) Then

Dim newDR As DataRow
newDR = Me.dsShifts.Tables(0).NewRow()
newDR.Item("Name") = value
... 'other custom stuff
End If

colName = New ctlDataGridComboBoxColumn("Name", dsShifts.Tables(1), "Name", "Name", Me.dgrShifts, True)
colName.MappingName = "Name"
colName.HeaderText = "Name"
Me.colName.SelectedValue() = value
Me.colName.DataSourceTable() = dsShifts.Tables(1)
Me.dgrShifts.Update()
colName.Width() = ResizeColumn("Name", "Name")
BindControl()
End Try
End Sub


That is the bare minimum without going into great detail of how to get an editable combo box in a datagrid column.

Setting a custom datagrid's row's height

I'm so glad to find this on the web. I needed to set the height of a double-clicked row to adjust the height. Normally the height is already adjusted in the default datagrid, but when you override it -- you lose this ability. Found this at http://vbcity.com/forums/faq.asp?fid=30&cat=Windows+Forms

<Description("Sets the height of one row.")> _

Public Sub SetRowHeight(ByVal Row As Integer, ByVal height As Integer)

Try

Dim d As New DataGrid()

Dim p As PropertyInfo = d.GetType.GetProperty("DataGridRows", BindingFlags.FlattenHierarchy Or BindingFlags.IgnoreCase Or BindingFlags.Instance Or BindingFlags.NonPublic Or BindingFlags.Public Or BindingFlags.Instance Or BindingFlags.Static)

Dim r As Object() = p.GetValue(Me, BindingFlags.Instance Or BindingFlags.Static Or BindingFlags.GetProperty Or BindingFlags.Public Or BindingFlags.SuppressChangeType, Nothing, Nothing, Nothing)

If Row < r.Length Then

r(Row).Height = height

Me.Invalidate()

Else

Throw New Exception("Row index outside of boundaries.")

End If

Catch

Throw New Exception("Error while reflecting. Framework version might be wrong.")

End Try

End Sub

VB.Net: Freeze a column in a data grid.

Ok, there is additional code that can be added to make this and feel more effiecient, but this gets the job done.

In your customize datagrid control override the horizontal scrollbar events with a class like this:

Protected Overrides Sub GridHScrolled(ByVal sender As Object, ByVal se As System.Windows.Forms.ScrollEventArgs)

Select Case se.Type

Case ScrollEventType.SmallIncrement, ScrollEventType.LargeIncrement
Dim SumWidth = 0
For i As Integer = _ColumnNumber + 1 To MyBase.TableStyles(0).GridColumnStyles.Count - 1
If MyBase.TableStyles(0).GridColumnStyles(i).Width > 0 Then
MyBase.TableStyles(0).GridColumnStyles(i).Width = 0
Exit For
End If
SumWidth += MyBase.TableStyles(0).GridColumnStyles(i).Width
Next
MyBase.TableStyles(0).GridColumnStyles(MyBase.TableStyles(0).GridColumnStyles.Count - 1).Width() += MyBase.TableStyles(0).DataGrid().Width - SumWidth

Case ScrollEventType.SmallDecrement, ScrollEventType.LargeDecrement
For i As Integer = MyBase.TableStyles(0).GridColumnStyles.Count - 1 To 1 Step -1
If MyBase.TableStyles(0).GridColumnStyles(i).Width = 0 Then
MyBase.TableStyles(0).GridColumnStyles(i).Width = 100
Exit For
End If
Next

Case ScrollEventType.EndScroll, ScrollEventType.First, ScrollEventType.Last, ScrollEventType.ThumbPosition, ScrollEventType.ThumbTrack
MyBase.GridHScrolled(sender, se)
End Select
Else
MyBase.GridHScrolled(sender, se)
End If
End Sub

Auto-Resize Columns in a data grid

So the data grids I've been working with are inherited from System.Windows.Forms.DataGrid. The problem I had was when a user double clicks between a column on the header to auto-fit the width of the column based on the data. Inside the inherited control you need something like:

Protected Overloads Overrides Sub OnMouseDown(ByVal e As MouseEventArgs)
If e.Button = MouseButtons.Left And e.Clicks > 1 And (Me.HitTest(e.X, e.Y).Type = DataGrid.HitTestType.ColumnResize) Then
Return 'Prevent sorting on the column if it's a resize
End If
End Sub



Then in the form's double click event I put something like:

Dim CursorPosition As System.Drawing.Point = dgr.PointToClient(Cursor.Position)
Dim DataGridHitTestInfo As DataGrid.HitTestInfo = dgr.HitTest(CursorPosition)

If DataGridHitTestInfo.Type = DataGrid.HitTestType.ColumnResize Or DataGridHitTestInfo.Type = DataGrid.HitTestType.ColumnHeader Then
Application.DoEvents()

Select Case DataGridHitTestInfo.Column
Case 2
Me.col2.Width() = ResizeColumn(Me.col2.MappingName, Me.col2.HeaderText)
Case 3
Me.col3.Width() = ResizeColumn(Me.col3.MappingName, Me.col3.HeaderText)
Case 4
Me.col4.Width() = ResizeColumn(Me.col4.MappingName, Me.col4.HeaderText)
Case 5
Me.col5.Width() = ResizeColumn(Me.col5.MappingName, Me.col5.HeaderText)
Case 6
Me.col6.Width() = ResizeColumn(Me.col6.MappingName, Me.col6.HeaderText)
End Select

Column_WidthChanged(sender, e)
End If



And added a function like this:


Private Function ResizeColumn(ByVal columnName As String, ByVal header As String) As System.String
Dim i As Integer
Dim length As Single = 0
Dim g As System.Drawing.Graphics = Me.dgr.CreateGraphics
Dim str As System.String
Dim strLength As Single
length = System.Math.Max(g.MeasureString(header, Me.Font).Width(), length)
For i = 0 To Me.dsPCDCodes.Tables(0).Rows.Count - 1
If Not IsDBNull(Me.ds.Tables(0).Rows(i).Item(columnName)) Then
str = CType(Me.ds.Tables(0).Rows(i).Item(columnName), System.String)
strLength = g.MeasureString(str, Me.Font).Width()
length = System.Math.Max(strLength, length)
End If
Next
Return length
End Function

Auto width in a data grid to fit data

Intersting way to auto fit the column width in a data grid to fit the width of the data. You basically use the graphics for the data grid and use a function called MeasureString. Send in the string and the font that will be used, and get the width from this.

Dim i As Integer
Dim length As Integer = 0
Dim g As System.Drawing.Graphics = Me.dgr.CreateGraphics
Dim str As System.String
Dim strLength As Single

For i = 0 To ds.Tables(0).Rows.Count - 1
str = CType(ds.Tables(0).Rows(i).Item("Name"), System.String)
strLength = g.MeasureString(str, Me.Font).Width()
length = System.Math.Max(strLength, length)
Next

'Set Length
Me.col.Width = length

Tip on finding the connection string.

One quick way of find a connection string is to create a new text document and rename the extension to udl. Go through the wizard for connecting to a database like SQL Server. When done, open the file in a text editor and the data would look something like:

[oledb]
; Everything after this line is an OLE DB initstring
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=ClientTracker;Data Source=ComputersName

Edit this by removing the "Provider=SQLOLEDB.1"

thats it.

Database Error 17: SQL Server does not exist or access denied.

Okay, I had to install VS.Net on top of VS 2005 and while I did that I figured I would update the security knowledge base from microsoft. Apparently it must of disabled the TCP/IP ports to prevent potential worms and such. So when I started one of our apps, I would now end up getting:

Database Error 17: SQL Server does not exist or access denied.

So here is the solution I found to work thanks to http://www.aspfaq.com/sql2005/show.asp?id=3

Step 1)
Make sure that SQL Server 2005 is functioning properly:

Start / Run... / type "CMD" without the quotes and hit OK
Type "SQLCMD" without the quotes and hit Enter
Verify that you have a "1>" prompt
Type "Exit" without the quotes and hit Enter

Step 2)
Start the SQL Browser service:
Start / Run... / type "NET START SQLBROWSER" without the quotes and hit OK

Step 3)
Make sure that named pipes and TCP/IP protocols are enabled:

Start / Programs / SQL Server 2005 / Configuration Tools / SQL Server Configuration Manager
Open "SQL Server 2005 Network Configuration"
Highlight "Client Protocols"
Right-click the Tcp node and make sure it is enabled (click "Enable" if it is available)
Repeat for the Named Pipes node


Step 4)
Restart SQL Server 2005 if you made any changes above:

Start / Run... / type "NET STOP MSSQL" without the quotes and hit OK
Start / Run... / type "NET START MSSQL" without the quotes and hit OK

Step 5)
If SQL Server 2000 is installed on the same machine, make sure that SP4 is installed prior to installing SQL Server 2005.