Efficient use of Iterators and Delegates

This is a good read. It shows how to effectually use iterators to make garbage collection efficient and to lower memory usage. It also shows how to use delegates to also improve on this concept.

http://msdn2.microsoft.com/en-us/vcsharp/bb264519.aspx

Take the initial uneffecient example:

// First attempt:
public List PeopleIKnowInNewYork()
{
IEnumerable newYorkNumbers = PhoneBook.FindListFor(“New York”);
List peopleIKnow = new List();

foreach ( PhoneBookEntry ph in newYorkNumbers)
{
string name = string.Format(“{0} {1}”, ph.FirstName,
ph.LastName);

if ( RecognizePerson( name ) )
peopleIKnow.Add(name);
}

return peopleIKnow;
}

// Fifth attempt:public

IEnumerable PeopleIKnowInNewYork()
{
IEnumerable newYorkNumbers =
PhoneBook.FindListFor(“New York”);
IEnumerable names = Transform(newYorkNumbers,
delegate(PhoneEntry entry)
{
return string.Format(“{0} {1}”, entry.FirstName, entry.LastName);
});
return Filter(names,
delegate(string name)
{
return RecognizePerson(name);
}
);
}

LINQ and AI?

Well, LINQ is something new to both VB and C# in Visual Studio 8 that allows you to query the data. The data can be XML, ADO.NET, DataSet, or objects. An example of the LINQ format:

var locals = (from c in customers
where c.ZipCode == 91822
select new { FullName = c.FirstName + “ “ +
c.LastName, HomeAddress = c.Address})
.Count();

What I find really interesting about the possibility of using LINQ, is the idea that the program can look at itself in an evaluation. If you add some artificial intelligence, this idea can be a great help in developing programs that can prove themselves correct in logic. I even think, that this concept can be used, with lots of time and planning, to develop a program that can write other programs given a set of parameters and acceptable outcomes.

The concept of using a database like language in a structure language is a very nice concept. If LINQ could be used to select all number of the type Int32 to represent integer numbers, would be something fun to play with. On this subject I might be a little too much in the dream world at the momement, but I do look forward to the possibilities.

Some sources on LINQ:

http://msdn.microsoft.com/msdnmag/issues/07/06/CSharp30/
http://blogs.msdn.com/charlie/archive/2007/07/26/visual-studio-2008-beta-2-released.aspx

Some info on Lambda Calculus:

http://en.wikipedia.org/wiki/Typed_lambda_calculus

Quick Reference: Raise Events in C#

Just wanted to put this down for a quick reference to remember how to raise an event in C#:

Declare:

private delegate void OnMouseOverDelegate(object
sender, System.EventArgs e);
private event OnMouseOverDelegate OnMouseOver;

After InitializeComponent(); :

this.OnMouseOver += new OnMouseOverDelegate(my_MouseHover);

Function:

private void
my_MouseHover(object sender, System.EventArgs e)
{
}

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