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.

C# Leaking

I got this info from MSDN magazine Jan 2007 Vol 22 No 1 issue.

Leaking Unmanaged Heap Memory:

• Interoperating with unmanaged code



Problem: "Using C-style DLLS that use P/Invoke and COM objects through COM interop. The Garbage Collector is unaware of unmanaged memory".


Solution: "Step through the code and examine memory usage before and after the unmanaged call to verify that the memory is reclaimed."



• Aborted Finalizers



Problem: "An objects finalizer does not get called and it contains code to clean up unmanaged memory allocated by the object".


Solution: "In .Net 1.x the only solution was to tear down the process and start again. The .Net Framework 2.0 introduces critical finalizers, which indicate that a finalizer will be cleaning up unmanaged resources and must be given a chance to run during AppDomain teardown."



• Dynamic Code Generation Leaks



Problem: "Sometimes code needs to be generated dynamically... dynamic assemly must be regenerated. The old assembly will no longer be used, but there is no way to evict it from memory."
Solution: "Check if you are regenerat[ing] code". You could either "load the generated MSIL into a child AppDomain. The child AppDomain can be unloaded when the generated code changes and a new one spun up to host the updated MSIL." Or, in 2.0 you can use DynamicMethod.Invoke.


• XmlSerializer Leaks



Problem: "XMLSerializer caches the temporary assemblies on a per-type basis" and when changing the root name within the XML by overloading the XMLSerializer constructor which doesn't cache.


Solution: Use XMLRootAttribute instead.

Leaking Managed Heap Memory

• Large Obect Heap Fragmentation



Problem: An object is 85,000 bytes or larger and allocated on the large object heap. "Unlike the rest of the managed heap, the Large Obect Heap is not compacted due to the cost of moving the large object. So as large objects are allocated, freed and cleanded up, gaps will appear." Which result in more memory usage then needed.


Solution: Try examining "how the application uses memory and specifically the types of objects that are on the large object heap using tools like CLRProfiler". Try to reduce the reliance on the LOH.



• Unneeded Rooted References



Problem/Solution: Forgetting to Null out rooted references, which prevents the GC from freeing up memory.



• Midlife Crisis



Problem: "A midlife crisis [...] is the overuse of managed heap memory and excessive amounts of processor time spent in the GC". An object lives to Gen1 or Gen2 and dies shortly afterward.


Solution: Beware of using finalizers in managed code. Use finalizers if there is a reference to unmanaged code. If using IDisposable, "implement the Dispose pattern to allow users of the object to clean up the resources and avoid finalization". Also don't hold onto objects before making a query to the database or a webservice.

.Net Framework Clarification on Stacks and Heaps

I was reading through January issue of MSDN on the article "Identify And Prevent Memory Leaks In Managed Code", where (James Kovacs) started discussing stacks and heaps. I and a couple of other co-workers where wondering about more details on what they do and why they are needed.

Stacks: "The stack is where local variables, method parameters, return values, and other temporary values are stored during the execution of an application. A stack is allocated on a per-thread basis and serves as a scratch area for the thread to perform its work. The GC [Garbage Collector] is not responsible for cleaning up the stack because the space on the stack reserved for a method call is automatically cleaned up when a method returns. Note, however, that the GC is aware of references to objects stored on the stack. When an object is instantiated in a method, its reference (a 32-bit or 64-bit integer depending on the platform) is kept on the stack, but the object it-self is stored on the managed heap and is collected by the garbage collector once the variable has gone out of scope."

Unmanaged Heap: "The unmanaged heap is used for runtime data structures, method tables, Microsoft intermediate language (MSIL), JITed code and so forth. Unmanaged code will allocate objects on the unmanaged heap or stack depending on how the object is instantiated. Managed code can allocate unmanaged heap memory directly by calling into unmanaged Win32 APIs or by instantiating COM objects. The CLR itself uses the unmanaged heap extensively for its data structures and code."

Managed Heap: "The managed heap is where managed objects are allocated and it is the domain of the garbage collector. The CLR uses a generational, compacting GC. The GC is generational in that it ages objects as they survive garbage collections; this is a performance enhancement. All versions of the .NET Framework have used three generations, Gen0, Gen1, and Gen2 (from youngest to oldest). The GC is compacting in that it relocates objects on the managed heap to eliminate holes and keep free ememory contiguous. Moving large objects is expensive and therefore the GC allocates them on a separate Large Object Heap, which does not compact."