Dim sConnectionString As String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Book1.xls;Extended Properties=Excel 8.0" Dim objConn As New System.Data.OleDb.OleDbConnection(sConnectionString) objConn.Open() Dim da As New System.Data.OleDb.OleDbDataAdapter("Select * from [Sheet1]", objConn) Dim ds As New DataSet("Sheet1") da.Fill(ds, "Sheet1")In this sample we connect to excel file using jet.oledb driver. I have used this in production and it worked like a charm with extreme preformance. For creating excel files I use excel library. This is probably the best free solution.
Friday, December 4, 2009
Creating and using excel files
Saturday, November 28, 2009
Nullable datetime picker
I have for the first time used datetimepicker control in my project. Although it is a great control I found it useless because of two things that are related:
- Default value cannot be set to nothing (null in C#).
- Datetimepicker canot display null (nothing in VB) values.
When I checked the msdn there was a caution notice saying just that.
I checked the codeproject website to look for an alternative. I found this control that suited my needs I only changed it to recognize the DateTime.MinValue to be nothing and set default to nothing. What I liked about it that it was the extended control, so it kept all the nice things I liked about default control. Here it is:
Imports System Imports System.ComponentModel Imports System.Globalization Imports System.Runtime.InteropServices Imports System.Threading Imports System.Windows.Forms ' Copyright (c) 2005 Claudio Grazioli, http://www.grazioli.ch ' ' This code is free software; you can redistribute it and/or modify it. ' However, this header must remain intact and unchanged. Additional ' information may be appended after this header. Publications based on ' this code must also include an appropriate reference. ' ' This code is distributed in the hope that it will be useful, but ' WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY ' or FITNESS FOR A PARTICULAR PURPOSE. ' ''' <summary> ''' Represents a Windows date time picker control. It enhances the .NET standard <b>DateTimePicker</b> ''' control with the possibility to show empty values (null values). ''' </summary> <ComVisible(False)> _ Public Class DateTimePickerCtrl Inherits System.Windows.Forms.DateTimePicker #Region "Member variables" ' true, when no date shall be displayed (empty DateTimePicker) Private _isNull As Boolean ' If _isNull = true, this value is shown in the DTP Private _nullValue As String ' The format of the DateTimePicker control Private _format As DateTimePickerFormat = DateTimePickerFormat.[Long] ' The custom format of the DateTimePicker control Private _customFormat As String ' The format of the DateTimePicker control as string Private _formatAsString As String #End Region #Region "Constructor" ''' <summary> ''' Default Constructor ''' </summary> Public Sub New() MyBase.New() MyBase.Format = DateTimePickerFormat.[Custom] NullValue = " " Format = DateTimePickerFormat.[Long] End Sub #End Region #Region "Public properties" ''' <summary> ''' Gets or sets the date/time value assigned to the control. ''' </summary> ''' <value>The DateTime value assigned to the control ''' </value> ''' <remarks> ''' <p>If the <b>Value</b> property has not been changed in code or by the user, it is set ''' to the current date and time (<see cref="DateTime.Now"/>).</p> ''' <p>If <b>Value</b> is <b>null</b>, the DateTimePicker shows ''' <see cref="NullValue"/>.</p> ''' </remarks> <Bindable(True)> _ <Browsable(False)> _ Public Shadows Property Value() As [Object] Get If _isNull Then Return Nothing Else Return DateTime.MinValue End If End Get Set(ByVal value As [Object]) If value Is Nothing OrElse value Is DBNull.Value OrElse (value.GetType() Is GetType(DateTime) And CDate(value) = DateTime.MinValue) OrElse (CDate(value) = New Date(CType(0, Long))) Then SetToNullValue() Else SetToDateTimeValue() MyBase.Value = DirectCast(value, DateTime) End If End Set End Property ''' <summary> ''' Gets or sets the format of the date and time displayed in the control. ''' </summary> ''' <value>One of the <see cref="DateTimePickerFormat"/> values. The default is ''' <see cref="DateTimePickerFormat.Long"/>.</value> <Browsable(True)> _ <DefaultValue(DateTimePickerFormat.[Long]), TypeConverter(GetType([Enum]))> _ Public Shadows Property Format() As DateTimePickerFormat Get Return _format End Get Set(ByVal value As DateTimePickerFormat) _format = value If Not _isNull Then SetFormat() End If OnFormatChanged(EventArgs.Empty) End Set End Property ''' <summary> ''' Gets or sets the custom date/time format string. ''' <value>A string that represents the custom date/time format. The default is a null ''' reference (<b>Nothing</b> in Visual Basic).</value> ''' </summary> Public Shadows Property CustomFormat() As [String] Get Return _customFormat End Get Set(ByVal value As [String]) _customFormat = value End Set End Property ''' <summary> ''' Gets or sets the string value that is assigned to the control as null value. ''' </summary> ''' <value>The string value assigned to the control as null value.</value> ''' <remarks> ''' If the <see cref="Value"/> is <b>null</b>, <b>NullValue</b> is ''' shown in the <b>DateTimePicker</b> control. ''' </remarks> <Browsable(True)> _ <Category("Behavior")> _ <Description("The string used to display null values in the control")> _ <DefaultValue(" ")> _ Public Property NullValue() As [String] Get Return _nullValue End Get Set(ByVal value As [String]) _nullValue = value End Set End Property #End Region #Region "Private methods/properties" ''' <summary> ''' Stores the current format of the DateTimePicker as string. ''' </summary> Private Property FormatAsString() As String Get Return _formatAsString End Get Set(ByVal value As String) _formatAsString = value MyBase.CustomFormat = value End Set End Property ''' <summary> ''' Sets the format according to the current DateTimePickerFormat. ''' </summary> Private Sub SetFormat() Dim ci As CultureInfo = Thread.CurrentThread.CurrentCulture Dim dtf As DateTimeFormatInfo = ci.DateTimeFormat Select Case _format Case DateTimePickerFormat.[Long] FormatAsString = dtf.LongDatePattern Exit Select Case DateTimePickerFormat.[Short] FormatAsString = dtf.ShortDatePattern Exit Select Case DateTimePickerFormat.Time FormatAsString = dtf.ShortTimePattern Exit Select Case DateTimePickerFormat.[Custom] FormatAsString = Me.CustomFormat Exit Select End Select End Sub ''' <summary> ''' Sets the <b>DateTimePicker</b> to the value of the <see cref="NullValue"/> property. ''' </summary> Private Sub SetToNullValue() _isNull = True MyBase.CustomFormat = If((_nullValue Is Nothing OrElse _nullValue = [String].Empty), " ", "'" & _nullValue & "'") End Sub ''' <summary> ''' Sets the <b>DateTimePicker</b> back to a non null value. ''' </summary> Private Sub SetToDateTimeValue() If _isNull Then SetFormat() _isNull = False MyBase.OnValueChanged(New EventArgs()) End If End Sub #End Region #Region "Events" ''' <summary> ''' This member overrides <see cref="Control.WndProc"/>. ''' </summary> ''' <param name="m"></param> Protected Overloads Overrides Sub WndProc(ByRef m As Message) If _isNull Then If m.Msg = &H4E Then ' WM_NOTIFY Dim nm As NMHDR = DirectCast(m.GetLParam(GetType(NMHDR)), NMHDR) If nm.Code = -746 OrElse nm.Code = -722 Then ' DTN_CLOSEUP || DTN_? SetToDateTimeValue() End If End If End If MyBase.WndProc(m) End Sub <StructLayout(LayoutKind.Sequential)> _ Private Structure NMHDR Public HwndFrom As IntPtr Public IdFrom As Integer Public Code As Integer End Structure ''' <summary> ''' This member overrides <see cref="Control.OnKeyDown"/>. ''' </summary> ''' <param name="e"></param> Protected Overloads Overrides Sub OnKeyUp(ByVal e As KeyEventArgs) If e.KeyCode = Keys.Delete Then Me.Value = Nothing OnValueChanged(EventArgs.Empty) End If MyBase.OnKeyUp(e) End Sub Protected Overloads Overrides Sub OnValueChanged(ByVal eventargs As EventArgs) MyBase.OnValueChanged(eventargs) End Sub #End Region End Class
Wednesday, October 14, 2009
How to use reflection to look for property that implements IList (or any other) iterface
In this sample I have created a function that gets a class of type T and inspects properties to find IList implementation.
Public Function GetResponse(Of T)(ByVal instance As T) As IList Dim props As PropertyInfo() = instance.GetType.GetProperties(BindingFlags.Public Or BindingFlags.Instance) For Each p In props If GetType(IList).IsAssignableFrom(p.PropertyType) AndAlso p.PropertyType.IsGenericType Then Dim listItems As IList = DirectCast(p.GetValue(instance, Nothing), IList) If listItems IsNot Nothing Then Return listItems End If End If Next Return Nothing End Function
Thursday, October 8, 2009
How to convert dataset or dbreader to List(of T) or BindingList(Of T) using mapping
When we want to make our applications more open to other platforms than we cannot use datasets as Therefor we need a way to convert datasets to simple objects and back. In this implementation I used a custom attribute to provide mapping between database language and business language, because that was a requirement.
Here is the custom attribute class:
Imports System.Reflection <AttributeUsage(AttributeTargets.Field Or AttributeTargets.[Property], AllowMultiple:=False, Inherited:=True)> _ Public NotInheritable Class DataColumnAttribute Inherits Attribute Private Const Flags As BindingFlags = BindingFlags.[Public] Or BindingFlags.NonPublic Or BindingFlags.Instance Or BindingFlags.FlattenHierarchy Private _name As String = Nothing Public ReadOnly Property Name() As String Get Return _name End Get End Property Public Sub New() End Sub Public Sub New(ByVal name As String) _name = name End Sub Private Function GetName(ByVal member As MemberInfo) As String Return If(_name, member.Name) End Function Public Shared Sub Bind(ByVal row As DataRow, ByVal target As Object) Dim type As Type = target.[GetType]() For Each column As DataColumn In row.Table.Columns For Each field As FieldInfo In type.GetFields(Flags) For Each att As DataColumnAttribute In field.GetCustomAttributes(GetType(DataColumnAttribute), True) att.Bind(column.ColumnName, target, field, row(column)) Next Next For Each [property] As PropertyInfo In type.GetProperties(Flags) For Each att As DataColumnAttribute In [property].GetCustomAttributes(GetType(DataColumnAttribute), True) att.Bind(column.ColumnName, target, [property], row(column)) Next Next Next End Sub Public Shared Sub Bind(ByRef rd As DbDataReader, ByVal target As Object) Dim type As Type = target.[GetType]() For i = 0 To rd.FieldCount - 1 For Each field As FieldInfo In type.GetFields(Flags) For Each att As DataColumnAttribute In field.GetCustomAttributes(GetType(DataColumnAttribute), True) att.Bind(rd.GetName(i), target, field, rd.Item(i)) Next Next For Each [property] As PropertyInfo In type.GetProperties(Flags) For Each att As DataColumnAttribute In [property].GetCustomAttributes(GetType(DataColumnAttribute), True) att.Bind(rd.GetName(i), target, [property], rd.Item(i)) Next Next Next End Sub Private Sub Bind(ByVal columnName As String, ByVal target As Object, ByVal field As FieldInfo, ByVal value As Object) If GetName(field).ToUpper() = columnName.ToUpper() Then field.SetValue(target, value) End If End Sub Private Sub Bind(ByVal columnName As String, ByVal target As Object, ByVal [property] As PropertyInfo, ByVal value As Object) If value.GetType() IsNot GetType(System.DBNull) Then If GetName([property]).ToUpper = columnName.ToUpper() Then [property].SetValue(target, value, Nothing) End If End If End Sub End Class
Here is the converter class
Imports System.ComponentModel Public Class DataTableConverter(Of T) Public Function GetObjectsAsList(ByRef rd As DbDataReader) As List(Of T) Dim list As New List(Of T)() While rd.Read Dim target As T = Activator.CreateInstance(Of T)() DataColumnAttribute.Bind(rd, target) list.Add(target) End While Return list End Function Public Function GetObjectsAsBindingList(ByRef rd As DbDataReader) As BindingList(Of T) Dim list As New BindingList(Of T)() While rd.Read Dim target As T = Activator.CreateInstance(Of T)() DataColumnAttribute.Bind(rd, target) list.Add(target) End While Return list End Function Public Function GetObjectsAsBindingList(ByRef dt As DataTable) As BindingList(Of T) Dim list As New BindingList(Of T)() For Each row As DataRow In dt.Rows Dim target As T = Activator.CreateInstance(Of T)() DataColumnAttribute.Bind(row, target) list.Add(target) Next Return list End Function Public Function GetObjectsAsList(ByRef dt As DataTable) As List(Of T) Dim list As New List(Of T)() For Each row As DataRow In dt.Rows Dim target As T = Activator.CreateInstance(Of T)() DataColumnAttribute.Bind(row, target) list.Add(target) Next Return list End Function End Class
Here is the reverse converter class:
Imports System.Reflection Imports System.ComponentModel Public Class ListConverter Public Function GetDataSetFromList(Of T)(ByVal list As List(Of T)) As DataSet Dim _resultDataSet As New DataSet() Dim _resultDataTable As New DataTable("results") Dim _resultDataRow As DataRow = Nothing Dim _itemProperties() As PropertyInfo = Nothing ' ' Meta Data. ' _itemProperties = list.Item(0).GetType().GetProperties() For Each p As PropertyInfo In _itemProperties _resultDataTable.Columns.Add(p.Name, _ p.GetGetMethod.ReturnType()) Next ' ' Data ' For Each item As T In list ' ' Get the data from this item into a DataRow ' then add the DataRow to the DataTable. ' Eeach items property becomes a colunm. ' _itemProperties = item.GetType().GetProperties() _resultDataRow = _resultDataTable.NewRow() For Each p As PropertyInfo In _itemProperties _resultDataRow(p.Name) = p.GetValue(item, Nothing) Next _resultDataTable.Rows.Add(_resultDataRow) Next ' ' Add the DataTable to the DataSet, We are DONE! ' _resultDataSet.Tables.Add(_resultDataTable) Return _resultDataSet End Function Public Function GetDataSetFromBindingList(Of T)(ByVal list As Bindinglist(Of T)) As DataSet Dim _resultDataSet As New DataSet() Dim _resultDataTable As New DataTable("results") Dim _resultDataRow As DataRow = Nothing Dim _itemProperties() As PropertyInfo = Nothing ' ' Meta Data. ' _itemProperties = list.Item(0).GetType().GetProperties() For Each p As PropertyInfo In _itemProperties For Each att As DataColumnAttribute In p.GetCustomAttributes(GetType(DataColumnAttribute), True) _resultDataTable.Columns.Add(p.Name, p.GetGetMethod.ReturnType()) Next Next ' ' Data ' For Each item As T In list ' ' Get the data from this item into a DataRow ' then add the DataRow to the DataTable. ' Eeach items property becomes a colunm. ' _itemProperties = item.GetType().GetProperties() _resultDataRow = _resultDataTable.NewRow() For Each p As PropertyInfo In _itemProperties For Each att As DataColumnAttribute In p.GetCustomAttributes(GetType(DataColumnAttribute), True) _resultDataRow(p.Name) = p.GetValue(item, Nothing) Next Next _resultDataTable.Rows.Add(_resultDataRow) Next ' ' Add the DataTable to the DataSet, We are DONE! ' _resultDataSet.Tables.Add(_resultDataTable) Return _resultDataSet End Function End Class
How to validate XML string using XSD file
I have spended quite some time looking for a .net 3.5 compatible implementation of XML validation with XSD. Since i could not find one I wrote my own, bases on 2.0 implementation that I did find. Here it is:
Imports System.Text Imports System.Xml.Schema Imports System.Xml Public Class XMLValidator2 ' Validation Error Count Shared ErrorsCount As Integer = 0 ' Validation Error Message Shared ErrorMessage As String = "" ' Validation Error Count Shared XSDErrorsCount As Integer = 0 ' Validation Error Message Shared XSDErrorMessage As String = "" Public Sub Validate(ByVal strXMLDoc As String, ByVal xsdFilePath As String) Dim strXMLReader As New IO.StringReader(strXMLDoc) Dim schemaTextReader As IO.TextReader = New IO.StreamReader(xsdFilePath) Dim readerSettings As XmlReaderSettings = New XmlReaderSettings() 'NOTE: če bomo kdaj javno objavili shemo lahko za path damo url readerSettings.Schemas.Add(XmlSchema.Read(schemaTextReader, AddressOf XSDSchemaValidationHandler)) readerSettings.ValidationType = ValidationType.Schema AddHandler readerSettings.ValidationEventHandler, New ValidationEventHandler(AddressOf ValidationHandler) Dim books As XmlReader = XmlReader.Create(strXMLReader, readerSettings) While books.Read() End While ' Raise exception, if XML validation fails If ErrorsCount > 0 Then Throw New ApplicationException(ErrorMessage) End If ' XML Validation succeeded Console.WriteLine("XML validation succeeded." & vbCr & vbLf) End Sub Private Sub ValidationHandler(ByVal sender As Object, ByVal args As ValidationEventArgs) ErrorMessage = ErrorMessage + args.Message & vbCr & vbLf ErrorsCount += 1 End Sub Private Sub XSDSchemaValidationHandler(ByVal sender As Object, ByVal args As ValidationEventArgs) XSDErrorMessage = XSDErrorMessage + args.Message & vbCr & vbLf XSDErrorsCount += 1 End Sub End Class
Thursday, May 21, 2009
Pain, pain, pain configuring smartparts on sharepoint
Friday, May 15, 2009
How to serialize xml into a class
Imports System.Xml.Serialization Imports System.Runtime.Serialization Imports System.IO Public Class Serialization Public Shared Function Serialize(Of T As IDeserializationCallback)(ByVal instance As T) As String Dim sb As New System.Text.StringBuilder Dim xmw As System.Xml.XmlWriter = System.Xml.XmlWriter.Create(sb) Dim xmlserializer As New XmlSerializer(GetType(T)) xmlserializer.Serialize(xmw, instance) Return sb.ToString End Function Public Shared Function Deserialize(Of T As IDeserializationCallback)(ByVal strXml As String) As T Dim rdr As System.Xml.XmlReader = System.Xml.XmlReader.Create(New System.IO.StringReader(strXml)) Dim xmlserializer As New XmlSerializer(GetType(T)) Dim obj As T = CType(xmlserializer.Deserialize(rdr), T) obj.OnDeserialization(Nothing) Return obj End Function End ClassAll left to do now is call the function like:
Dim cl As ClassName cl = Deserialize(Of ClassName)(inXml)XML is of course an XML string that you want to deserialize.
Thursday, April 23, 2009
How to print contents of datagridview?
I was looking for an answer on a web since i didn't want to write PrintDocument form as scratch. I found this website to be useful, but when i implemented code there were minor bugs in the code. There was some problem with page numbers when using a print preview and code assumed that data source of a grid was dataset and that all columns were shown, witch was not true in my case. Fixed document class below:
using System; using System.Data; using System.Windows.Forms; using System.Drawing.Printing; using System.Drawing; namespace VA.Gui { public class PrintGridDocument : PrintDocument { //Data Members private DataGridView m_oDataGrid; private int m_nCurrPage; private int m_nCurrRow; private int m_nColumns; private int m_nRows; private bool m_bInitialized; private int m_nLinesPerPage; private int m_nTotalPages; private int[] m_nColBounds; //Properties public Font PrintFont; public string Title; public PrintGridDocument(DataGridView aGrid) : base() { //Default Values m_oDataGrid = aGrid; m_nCurrPage = 0; m_nCurrRow = 0; m_bInitialized = false; m_nColumns = m_oDataGrid.ColumnCount; m_nRows = m_oDataGrid.RowCount ; } //Override OnBeginPrint to set up the font we are going to use protected override void OnBeginPrint(PrintEventArgs ev) { base.OnBeginPrint(ev); //If client has not created a font, create a default font // Note: an exception could be raised here, but it is deliberately not // being caught because there is nothing we could do at this point! if (PrintFont == null) PrintFont = new Font("Arial", 9); } //Override the OnPrintPage to provide the printing logic for the document protected override void OnPrintPage(PrintPageEventArgs e) { m_nCurrPage = 0; //Call base method base.OnPrintPage(e); //Get the margins int nTextPosX = e.MarginBounds.Left; int nTextPosY = e.MarginBounds.Top; //Do first time initialization stuff if (!m_bInitialized) { // Calculate the number of lines per page. m_nLinesPerPage = (int)(e.MarginBounds.Height / PrintFont.GetHeight(e.Graphics)); m_nTotalPages = (int)Math.Ceiling((float)m_nRows / (float)m_nLinesPerPage); //Create bounding box for columns m_nColBounds = new int[m_nColumns]; //Calculate the correct spacing for the columns for (int nCol = 0; nCol < m_nColumns; nCol++) { //Measure the column headers first m_nColBounds[nCol] = (int)e.Graphics.MeasureString( m_oDataGrid.Columns[nCol].HeaderText , PrintFont).Width; for (int nRow = 0; nRow < m_nRows; nRow++) { //Compare data to current max if (e.Graphics.MeasureString(m_oDataGrid[nCol,nRow].Value.ToString(), PrintFont).Width > m_nColBounds[nCol]) m_nColBounds[nCol] = (int)e.Graphics.MeasureString(m_oDataGrid[nCol, nRow].Value.ToString(), PrintFont).Width; } //Just use max possible size if too large if (m_nColBounds[nCol] > e.MarginBounds.Width / m_nColumns) m_nColBounds[nCol] = e.MarginBounds.Width / m_nColumns; //Can't be less than column width if (m_nColBounds[nCol] < (int)Math.Round(e.Graphics.MeasureString(m_oDataGrid.Columns[nCol].HeaderText , PrintFont).Width)) m_nColBounds[nCol] = (int)Math.Round(e.Graphics.MeasureString(m_oDataGrid.Columns[nCol].HeaderText, PrintFont).Width); } //Move to correct starting page if (this.PrinterSettings.PrintRange == PrintRange.SomePages) { while (m_nCurrPage < this.PrinterSettings.FromPage - 1) { //Move to next page - advance data to next page as well m_nCurrRow += m_nLinesPerPage; m_nCurrPage++; if (m_nCurrRow > m_nRows) return; } if (m_nCurrPage > this.PrinterSettings.ToPage) { //Don't print anything more return; } } //Set flag m_bInitialized = true; } //Move to next page m_nCurrPage++; //Print Title if first page if (m_nCurrPage == 1) { Font TitleFont = new Font("Arial", 15); int nXPos = (int)(((e.PageBounds.Right - e.PageBounds.Left) / 2) - (e.Graphics.MeasureString(Title, TitleFont).Width / 2)); e.Graphics.DrawString(Title, TitleFont, Brushes.Black, nXPos, e.MarginBounds.Top - TitleFont.GetHeight(e.Graphics) - 10); } //Draw page number string strOutput = "Page " + m_nCurrPage + " of " + m_nTotalPages; e.Graphics.DrawString(strOutput, PrintFont, Brushes.Black, e.MarginBounds.Right - e.Graphics.MeasureString(strOutput, PrintFont).Width, e.MarginBounds.Bottom); //Utility rectangle - use for many drawing operations Rectangle aRect = new Rectangle(); //Loop through data for (int nRow = m_nCurrRow; nRow < m_nRows; nRow++) { //Draw the current row within a shaded/unshaded box aRect.X = e.MarginBounds.Left; aRect.Y = nTextPosY; aRect.Width = e.MarginBounds.Width; aRect.Height = (int)PrintFont.GetHeight(e.Graphics); //Draw the box if (nRow % 2 == 0) e.Graphics.FillRectangle(Brushes.LightGray, aRect); e.Graphics.DrawRectangle(Pens.Black, aRect); //Loop through each column for (int nCol = 0; nCol < m_nColumns; nCol++) { //Set the rectangle to the correct position aRect.X = nTextPosX; aRect.Y = nTextPosY; aRect.Width = m_nColBounds[nCol]; aRect.Height = (int)PrintFont.GetHeight(e.Graphics); //Print the data e.Graphics.DrawString(m_oDataGrid[nCol,nRow].Value.ToString(), PrintFont, Brushes.Black, aRect); //Advance the x Position counter nTextPosX += m_nColBounds[nCol]; } //Reassign the x position counter nTextPosX = e.MarginBounds.Left; //Move the y position counter down a line nTextPosY += (int)PrintFont.GetHeight(e.Graphics); //Check to see if we have reached the line limit - move to a new page if so if (nRow - ((m_nCurrPage - 1) * m_nLinesPerPage) == m_nLinesPerPage) { //Save the current row m_nCurrRow = ++nRow; e.HasMorePages = true; return; } } } } }
Usage is simple:
private void buttonCtl1_Click(object sender, EventArgs e) { //Setup the document to print PrintGridDocument aDoc = new PrintGridDocument(dataGridViewCtl1 ); aDoc.Title = "Employee Report"; printDialog1 .Document = aDoc; if (printDialog1.ShowDialog() == DialogResult.OK) { //Display the print preview dialog aDoc.DefaultPageSettings = new PageSettings(printDialog1.PrinterSettings); //comment next two lines if do not want to use print preview printPreviewDialog1.Document = aDoc; printPreviewDialog1.ShowDialog(); //uncomment lines below if not using print preview //try //{ // //Print the document // aDoc.Print(); //} //catch (Exception ex) //{ // //Display any errors // MessageBox.Show(ex.ToString()); //} }
Tuesday, April 21, 2009
How to execute (invoke) a function using reflection if we know it's name and class
Have you ever wondered how to do that. This can be useful if you build a custom control and want user to tell you what function to call, for some reason. Code shown bellow.
Public Shared Function CallFunctionByReflection(ByVal className As String, ByVal functionName As String, ByVal arguments As Object()) Dim theType As Type = Nothing Dim theObj As [Object] = Nothing Dim theMethodInfo As MethodInfo = Nothing theType = System.Type.GetType(className) theObj = Activator.CreateInstance(theType) Try theMethodInfo = theType.GetMethod(functionName) If arguments IsNot Nothing Then Return theMethodInfo.Invoke(theObj, arguments) Else Return theMethodInfo.Invoke(theObj, Nothing) End If Catch Ex As Exception Throw Ex Return Nothing Finally theType = Nothing theObj = Nothing theMethodInfo = Nothing End Try End Function
Tuesday, March 24, 2009
Design patterns
Tuesday, March 10, 2009
How to change the backgroud color of MDI windows
For Each ctrl As Control In Controls If ctrl.GetType() IsNot GetType(MdiClient) Then Continue For End If Dim colorName As String = ConfigurationManager.AppSettings("Dialog.BackColor") If colorName Is Nothing Then Exit For End If Dim color As Color = color.FromName(colorName) 'checks if color is transparent, mdiclient.backcolor doesn't support transparent colors If color.A <> 0 Then ctrl.BackColor = color.FromName(colorName) End If next
Wednesday, February 25, 2009
How to programaticaly detect .NET framework installed
Reading the registry:
C++ example:
http://astebner.sts.winisp.net/Tools/detectFX.cpp.txt
C# example:
http://www.codeproject.com/KB/dotnet/frameworkversiondetection.aspx
Browsing the file system folder %systemroot%\Microsoft.NET\Framework:
http://msdn.microsoft.com/en-us/kb/kb00318785.aspx
Tuesday, February 24, 2009
Browsing trough files programaticaly
To browse trough the filesystem .net provides in System.IO namespace two very useful functions: Directory.GetFiles and Directory.GetDirectories. What those functions do is quite obvious. Simple example below.
Public Function SearchForFile(ByVal targetDirectory As String, ByVal fileToFind As String, ByVal recursive As Boolean) As String Dim fileName As String = String.Empty 'ProcessFolder(targetDirectory) If fileToFind IsNot Nothing Then Dim fileEntries As String() = Directory.GetFiles(targetDirectory) ' Process the list of files found in the directory. For Each fileName In fileEntries If fileName.ToLower.Contains(fileToFind) Then Return fileName End If Next fileName If recursive Then Dim subdirectoryEntries As String() = Directory.GetDirectories(targetDirectory) ' Recurse into subdirectories of this directory. Dim subdirectory As String For Each subdirectory In subdirectoryEntries SearchForFile(subdirectory, fileToFind, recursive) Next subdirectory End If End If Return fileName End Function
Friday, February 20, 2009
How to set backgroud color of MDI
Since the property is not available at design time, all you need is a simple loop through controls (probably in load event) to set the background color of mdi window.
foreach (Control ctrl in Controls) { if (!(ctrl is MdiClient)) continue; string colorName = ConfigurationManager.AppSettings["Dialog.BackColor"]; if (colorName == null) continue; Color color = Color.FromName(colorName); //checks if color is transparent, mdiclient.backcolor doesn't support transparent colors if (color.A != 0) ctrl.BackColor = Color.FromName(colorName); }
Monday, February 16, 2009
How to make an simple SMTP client
Imports System Imports System.Net Imports System.Net.Mail Imports System.Net.Mime Imports System.Threading Imports System.ComponentModel Module SimpleAsynchronousExample Dim mailSent As Boolean = False Private Sub SendCompletedCallback(ByVal sender As Object, ByVal e As AsyncCompletedEventArgs) 'Get the unique identifier for this asynchronous operation. Dim token As String = CType(e.UserState, String) If (e.Cancelled) Then Console.WriteLine("[{0}] Send canceled.", token) If (e.Error IsNot Nothing) Then Console.WriteLine("[{0}] {1}", token, e.Error.ToString()) Else Console.WriteLine("Message sent.") End If mailSent = True End If End Sub Sub Main(ByVal args As String()) 'Command line argument must the the SMTP host. Dim client As SmtpClient = New SmtpClient("hostname") '' Specify the e-mail sender. '' Create a mailing address that includes a UTF8 character '' in the display name. Dim from As MailAddress = New MailAddress("someone@test.com", "Some" + " One", System.Text.Encoding.UTF8) ' Set destinations for the e-mail message. Dim toAddress As MailAddress = New MailAddress("someone@test.com") '' Specify the message content. Dim message As MailMessage = New MailMessage(from, toAddress) message.Body = "This is a test e-mail message sent by an application. " ''Include some non-ASCII characters in body and subject. message.Body += Environment.NewLine + "This is a mail" message.BodyEncoding = System.Text.Encoding.UTF8 message.Subject = "test message 1" + "test message" message.SubjectEncoding = System.Text.Encoding.UTF8 ' Set the method that is called back when the send operation ends. AddHandler client.SendCompleted, AddressOf SendCompletedCallback ' The userState can be any object that allows your callback ' method to identify this send operation. 'For this example, the userToken is a string constant. Dim userState As String = "test message1" client.SendAsync(message, "this is me") Console.WriteLine("Sending message... press c to cancel mail. Press any other key to exit.") Dim answer As String = Console.ReadLine() '' If the user canceled the send, and mail hasn't been sent yet, '' then cancel the pending operation. If (answer.StartsWith("c") AndAlso mailSent <> False) Then client.SendAsyncCancel() End If ''Clean up. message.Dispose() Console.WriteLine("Goodbye.") End Sub End ModuleIn order for example to work hostname of a SMTP server must be entered. Also make sure to have relay rules set. If you want no rule to apply set:
Friday, February 13, 2009
Pagging as concept part2
SELECT *
FROM (SELECT ROWNUM row_num, column1, column2
FROM (SELECT column1, column2
FROM table
)
WHERE ROWNUM <= :page_num * :rec_count)
WHERE row_num > (:page_num - 1) * :rec_count
Rec_count tells us how many records we want on a page, an page_num is the number of page we want.
I like this version more that maybe more used version:
SELECT *
FROM (SELECT ROWNUM, table_name.*
FROM (SELECT *
FROM table) table_name
WHERE ROWNUM <= 200)
WHERE ROWNUM <= 101
Whole point of doing sub select is to limit the number of results with inner select and then extract the rows required with outer select.
Simple implementation of SQL for paging modification would look something like:
Public Function GetSQL(ByVal sqlin As String, ByVal sort As String, ByVal table As String, ByVal page As Integer, ByVal rec_count As Integer) As String
Dim sql2 As String = " SELECT * FROM (SELECT ROWNUM as ROW_NUM, " & table & ".* FROM "
sql2 = sql2 & " ( " & sqlin & " " & sort & " ) " & table
sql2 = sql2 & " WHERE ROWNUM <= " & page & " * " & rec_count & ") WHERE ROW_NUM > (" & page & " - 1) *" & rec_count
Return sql2
End Function
Monday, February 9, 2009
Paging as a concept
Paging is yet another mysterious word used in by software developers. In principle it means that instead of downloading all the data returned by a query at once from database, we download subset (page)(first 100 rows) and when user browse through records, we fetch another page, and continue to do so until we have downloaded all the data. When applying this concept we presume that user will narrow its search when he cannot find what he was looking for in first n pages. If done correctly paging can have a significant impact on application performance.
Here is how we do it on all the databases that support rownum:
SELECT * FROM
( SELECT rownum as row_num,
column1,
column2,
column3,
FROM
datatable
)
WHERE ROWNUM <= 201 and ROW_NUM >= 300
Downside of paging is that it does not guaranty the coherence of data. If data in database is modified during the paging, duplicates or missing data my appear.
Thursday, February 5, 2009
Wednesday, February 4, 2009
How to easily produce CSV files
If dataset (datatable) is the source then:
Public Shared Sub ProduceCSV(ByVal dt As DataTable, ByVal file As System.IO.StreamWriter, ByVal WriteHeader As Boolean)
Dim i As Int32
Dim j As Int32
Dim myCIintl As System.Globalization.CultureInfo
myCIintl = System.Globalization.CultureInfo.CurrentCulture
If (WriteHeader) Then
Dim arr(dt.Columns.Count - 1) As String
For i = 0 To dt.Columns.Count - 1
arr(i) = dt.Columns(i).ColumnName
arr(i) = GetWriteableValue(arr(i))
Next
file.WriteLine(String.Join(myCIintl.TextInfo.ListSeparator, arr))
End If
For j = 0 To dt.Rows.Count - 1
Dim dataArr(dt.Columns.Count - 1) As String
For i = 0 To dt.Columns.Count - 1
Dim o As Object = dt.Rows(j)(i)
dataArr(i) = GetWriteableValue(o)
Next
file.WriteLine(String.Join(myCIintl.TextInfo.ListSeparator, dataArr))
Next
End Sub
Making csv files from reader:
Public Shared Sub ProduceCSV(ByVal reader As DbDataReader, ByVal file As System.IO.StreamWriter, ByVal WriteHeader As Boolean)
Dim i As Int32
Dim myCIintl As System.Globalization.CultureInfo
While reader.Read
myCIintl = System.Globalization.CultureInfo.CurrentCulture
If (WriteHeader) Then
Dim arr(reader.FieldCount) As String
For i = 0 To reader.FieldCount - 1
arr(i) = reader.GetName(i)
arr(i) = GetWriteableValue(arr(i))
Next
file.WriteLine(String.Join(myCIintl.TextInfo.ListSeparator, arr))
WriteHeader = False ''header zapisemo samo enkrat
End If
Dim dataArr(reader.FieldCount - 1) As String
For i = 0 To reader.FieldCount - 1
Dim val As String
'If reader.GetName(i).ToLower.Contains("stid") Then
' val = String.Format("""STID:{0}""", reader.Item(i))
'Else
val = GetWriteableValue(reader.Item(i))
'End If
dataArr(i) = val
Next
file.WriteLine(String.Join(myCIintl.TextInfo.ListSeparator, dataArr))
End While
End Sub
Since csv in my case contain millions of records I use reader as a source to limit memory usage and boost performance.
How to make binding to objects (classes) work
Public Class ListOfUnits
Private _ID As Integer
Public Property ID() As Integer
Get
Return _ID
End Get
Set(ByVal value As Integer)
_ID = value
End Set
End Property
Private _UNIT As String
Public Property UNIT() As String
Get
Return _UNIT
End Get
Set(ByVal value As String)
_UNIT= value
End Set
End Property
end class
then make another class, that inherits from bindinglist(of T), where T is your class name
Public Class SortableListOfUnits
Inherits BindingList(Of ListOfUnits)
Private mIsSorted As Boolean
Private m_SortDirection As ListSortDirection
Private m_SortProperty As PropertyDescriptor
Protected Overrides ReadOnly Property SortPropertyCore() As System.ComponentModel.PropertyDescriptor
Get
Return m_SortProperty
End Get
End Property
Protected Overrides ReadOnly Property SortDirectionCore() As System.ComponentModel.ListSortDirection
Get
Return m_SortDirection
End Get
End Property
Protected Overrides ReadOnly Property SupportsSortingCore() As Boolean
Get
Return True
End Get
End Property
Protected Overrides Sub ApplySortCore(ByVal prop As System.ComponentModel.PropertyDescriptor, ByVal direction As System.ComponentModel.ListSortDirection)
Dim items As System.Collections.Generic.List(Of ListOfUnits) = DirectCast(Me.Items, List(Of ListOfUnits))
If Not IsNothing(items) Then
m_SortDirection = direction
m_SortProperty = prop
items.Sort(New PropertyComparer(Of ListOfUnits)(prop.Name, direction))
mIsSorted = True
Else
mIsSorted = False
End If
Me.OnListChanged(New ListChangedEventArgs(ListChangedType.Reset, -1))
End Sub
Protected Overrides ReadOnly Property IsSortedCore() As Boolean
Get
Return mIsSorted
End Get
End Property
Protected Overrides Sub RemoveSortCore()
mIsSorted = False
End Sub
End Class
3) all you need now is a property comparer or in simple words a class that will implement a IComparer(Of T) interface if you want to make your own comparing logic. I have found a excelent implementation of icomparer online, that is generic, since i do not want to write comparer logic for every class.
Imports System.Reflection
Public Class PropertyComparer(Of T)
Implements IComparer(Of T)
Private FPropertyName As String = ""
Private FDirection As SortDirection
Public Sub New(ByVal propertyName As String)
FPropertyName = propertyName
FDirection = SortDirection.Ascending
End Sub
Public Sub New(ByVal propertyName As String, ByVal Direction As SortDirection)
FPropertyName = propertyName
FDirection = Direction
End Sub
' Try to sort based on type using CompareTo method
' Multiple by FDirection to alternate sort direction
Public Function Compare(ByVal x As T, ByVal y As T) _
As Integer Implements System.Collections.Generic. _
IComparer(Of T).Compare
Dim propertyX As PropertyInfo = x.GetType().GetProperty(FPropertyName)
Dim propertyY As PropertyInfo = y.GetType().GetProperty(FPropertyName)
Dim px As Object = propertyX.GetValue(x, Nothing)
Dim py As Object = propertyY.GetValue(y, Nothing)
If (TypeOf px Is Integer) Then
Return Compare(Of Integer)(CType(px, Integer), CType(py, Integer)) * FDirection
End If
If (TypeOf px Is Decimal) Then
Return Compare(Of Decimal)(CType(px, Decimal), CType(py, Decimal)) * FDirection
End If
If (TypeOf px Is DateTime) Then
Return Compare(Of DateTime)(CType(px, DateTime), CType(py, DateTime)) * FDirection
End If
If (TypeOf px Is Double) Then
Return Compare(Of Double)(CType(px, Double), CType(py, Double)) * FDirection
End If
If (TypeOf px Is String) Then
Return Compare(Of String)(CType(px, String), CType(py, String)) * FDirection
End If
If (TypeOf px Is Decimal) Then
Return Compare(Of Decimal)(CType(px, Decimal), CType(py, Decimal)) * FDirection
End If
Dim methodX As MethodInfo = propertyX.GetType().GetMethod("CompareTo")
If (methodX Is Nothing = False) Then
Return CType(methodX.Invoke(px, New Object() {py}), _
Integer) * FDirection
Else
Return 0
End If
End Function
Private Function Compare(Of K As IComparable)(ByVal x As K, _
ByVal y As K) As Integer
Return x.CompareTo(y)
End Function
Public Enum SortDirection
Descending = -1
Ascending = 1
End Enum
End Class