This sample shows how to enumerate and select a specific tray to use for printing.
The code works bij intercepting the oReport.PrintingSystem.StartPrint event, by adding a handler and sending it to a routine which enumerates the trays of the printer.
DevExpress
' --- Apply currency format
GridView7.Columns("price").DisplayFormat.FormatString = "c"
GridView7.Columns("price").DisplayFormat.FormatType = DevExpress.Utils.FormatType.Custom
' //
' --- Right align a column
GridViewArtikelInslag.Columns("datum").AppearanceCell.TextOptions.HAlignment = DevExpress.Utils.HorzAlignment.Far
' //
' --- Auto fit sizes to content
GridView7.OptionsView.ColumnAutoWidth = False
GridView7.BestFitColumns()
' //
Default .NET Control
' --- Activate auto sizing
DataGridViewA.AutoSizeColumnsMode = DataGridViewAutoSizeColumnMode.Fill
' //
'--- Apply currency format
DataGridViewA.Columns("price").DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
' //
' --- Set fill weight
DataGridViewA.Columns("price").FillWeight = 300
' //
By default the DevExpress grid control will force all columsn to fit in the default view. These property’s can be used to change that behaviour and have teh columns size to the content of the cells and a scrollbar to appear.
' --- A blank grid control is present on the form, which will supply the
' gridview1 object by default.
' --- By default, ALL columns will be forced into the avaiable width
GridView1.OptionsView.ColumnAutoWidth = False
' --- Size the columns to the content
GridView1.BestFitColumns()
The following code snippet applies the technique to a form which contains a gridcontrol with no bindinge made at design time. At runtime, a qeury is run against an ODBC database, and the result of that query is visualized in the grid.
Code to run the query and bind the results to the grid:
Private Sub RunQuery()
Using oCon As New OdbcConnection(My.Settings.ODBCLink)
oCon.Open()
Dim sQuery As String = TextEdit1.Text
Using oDa As New OdbcDataAdapter(sQuery, oCon)
Using oDs As New DataSet
oDa.Fill(oDs, "resultset")
GridControl1.DataSource = oDs
GridControl1.DataMember = "resultset"
End Using
End Using
End Using
End Sub
And apply the formatting so we can see all resulting data
GridView1.OptionsView.ColumnAutoWidth = False
GridView1.BestFitColumns()
These samples show handling the clipboard in the most basic way. First a test is done to see if there currently is text on the clipboard. If a text is found, the programm proceeds to read and display the text, and then replace it with a new text.
By modifieing the parameters you can use the same technique to handle other formats such as images or audio and video fragments.
Imports System.Windows.forms
Module Module1
Sub Main()
If Clipboard.ContainsText = True Then
' --- Get the current text from the clipboard
Dim sClipboard As String = Clipboard.GetText
Console.WriteLine("The text on the clipboard was: " & sClipboard)
' //
' --- Set a new text on the clipboard
Dim sNewValue As String = "This is now on the clipboard"
Clipboard.SetText(sClipboard)
Console.WriteLine("The new value is : " & Clipboard.GetText)
' //
Else
Console.WriteLine("Sorry, the clipboard does not contain a text, the demo failed")
End If
End Sub
End Module
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms; // dont forget to add this one
namespace ConsoleApplication2
{
class Program
{
static void Main(string[] args)
{
if (Clipboard.ContainsText() == true)
{
// Get and display the current clipboard text
string sCurrent = Clipboard.GetText();
Console.WriteLine("The text on the clipboard was: " + sCurrent);
// Set a new value onto the clipboard, and display it to the user
string sNew = "This is now on the clipboard";
Clipboard.SetText(sNew);
Console.WriteLine("The new value on the clipboard is: " + Clipboard.GetText());
}
else
{
Console.WriteLine("Sorry, the clipboard does not contain a text, the demo failed");
}
}
}
}
This code sample demonstrates the most basic way to read in a generic XML document, and loop through it extracting data and sub data from various nodes.
Module Module1
Sub Main()
' --- Attach XML bestand
Dim oDebitorsfile As XDocument = XDocument.Load("g:\debiteuren124119.xml")
' --- Loop all entrys within the main debitor tag
For Each oDebitor As XElement In oDebitorsfile...<DEBITEUR>
' --- Output the debitors number (or any field from the main entry)
Console.WriteLine(oDebitor.Element("NAW_NUMMER").Value)
' --- Get a value from a single subnode
Dim oMainaddress As XElement = oDebitor.Element("NAW_VESTIGINGADRES")
Console.WriteLine(oMainaddress.Element("ADR_NAAM1").Value)
' --- Get values from a list of subnodes
' Loop delivery addresses within this single debitor
For Each oVerzend As XElement In oDebitor...<NAW_VERZENDADRES>
Console.WriteLine(oVerzend.Element("ADR_NAAM1").Value)
Next
' //
Next
Console.Write("end-run")
Console.ReadLine()
End Sub
End Module
many documents will not contain sub elemenst for all elements, because the export will only craete them when a value is present. To check if certain elemenst exist in a document, or as sub-elements you can use the functions below:
''' <summary>
''' Check for the presence of a value within an xml element
''' </summary>
''' <param name="oElement"></param>
''' <param name="sName"></param>
''' <returns></returns>
''' <remarks></remarks>
Public Shared Function CheckElement(ByVal oElement As XElement, ByVal sName As XName) As Boolean
Return (oElement.Descendants(sName).Any())
End Function
''' <summary>
''' Check for the presence of a value within an xml document
''' </summary>
''' <param name="oDocument"></param>
''' <param name="sName"></param>
''' <returns></returns>
''' <remarks></remarks>
Public Shared Function CheckDocument(ByVal oDocument As XDocument, ByVal sName As XName) As Boolean
Return (oDocument.Descendants(sName).Any())
End Function
The file below is the input file used, of course – the technique works with any xml document als long as you use the correct
<?xml version="1.0" encoding="ISO-8859-1" ?>
<!-- Generated by King Export-->
<KING_DEBITEUREN>
<DEBITEUREN>
<DEBITEUR>
<NAW_NUMMER>124119</NAW_NUMMER>
<NAW_ZOEKCODE>searchcode</NAW_ZOEKCODE>
<NAW_BETALINGSCONDITIE>1</NAW_BETALINGSCONDITIE>
<NAW_VALUTACODE>EUR</NAW_VALUTACODE>
<NAW_BTWCODE>2</NAW_BTWCODE>
<NAW_TAALCODE>N</NAW_TAALCODE>
<NAW_AANMANINGSTYPE>AANMANING</NAW_AANMANINGSTYPE>
<NAW_APARTEFACTUREN>NEE</NAW_APARTEFACTUREN>
<NAW_FACTURERENINEXBTW>EXCLBTW</NAW_FACTURERENINEXBTW>
<NAW_FACTUURADRESSOORT>COR</NAW_FACTUURADRESSOORT>
<NAW_VERZENDADRESSOORT>COR</NAW_VERZENDADRESSOORT>
<NAW_EINDBESTEMMINGSOORT>NVT</NAW_EINDBESTEMMINGSOORT>
<NAW_AANTALKOPIEFACTUREN>0</NAW_AANTALKOPIEFACTUREN>
<NAW_ORDERKORTINGSOORT>DEB</NAW_ORDERKORTINGSOORT>
<NAW_ORDERKORTING>0</NAW_ORDERKORTING>
<NAW_BLOKKEERORDERINVOER>false</NAW_BLOKKEERORDERINVOER>
<NAW_WEBKLANT>false</NAW_WEBKLANT>
<NAW_KVKNUMMER>14087629</NAW_KVKNUMMER>
<NAW_INKOOPCOMBINATIE>0</NAW_INKOOPCOMBINATIE>
<NAW_VESTIGINGADRES>
<ADR_NAAM1>Jan Aap</ADR_NAAM1>
<ADR_STRAAT>Kooi</ADR_STRAAT>
<ADR_HUISNUMMER>13</ADR_HUISNUMMER>
<ADR_POSTCODE>1000AA</ADR_POSTCODE>
<ADR_WOONPLAATS>Artis</ADR_WOONPLAATS>
<ADR_LAND>NL</ADR_LAND>
<ADR_EMAIL>ape@zoo.com</ADR_EMAIL>
<ADR_TELEFOON>5552509120</ADR_TELEFOON>
</NAW_VESTIGINGADRES>
<NAW_VERZENDADRESSEN>
<NAW_VERZENDADRES>
<ADR_NAAM1>Ape verzend 1</ADR_NAAM1>
<ADR_STRAAT>Kooi</ADR_STRAAT>
<ADR_HUISNUMMER>13</ADR_HUISNUMMER>
<ADR_POSTCODE>1000AA</ADR_POSTCODE>
<ADR_WOONPLAATS>AMSTERDAM</ADR_WOONPLAATS>
<ADR_LAND>NL</ADR_LAND>
<ADR_TELEFOON>(020)</ADR_TELEFOON>
<ADR_TELEFOON2>(020)</ADR_TELEFOON2>
<ADR_TELEFAX>(020)</ADR_TELEFAX>
<ADR_NUMMER>1</ADR_NUMMER>
<ADR_OMSCHRIJVING>Ape&amp;apos;s eerste verzendadres</ADR_OMSCHRIJVING>
</NAW_VERZENDADRES>
<NAW_VERZENDADRES>
<ADR_NAAM1>Ape verzend 2</ADR_NAAM1>
<ADR_STRAAT>Kooi</ADR_STRAAT>
<ADR_HUISNUMMER>13</ADR_HUISNUMMER>
<ADR_POSTCODE>1000AA</ADR_POSTCODE>
<ADR_WOONPLAATS>AMSTERDAM</ADR_WOONPLAATS>
<ADR_LAND>NL</ADR_LAND>
<ADR_TELEFOON>(020)</ADR_TELEFOON>
<ADR_TELEFOON2>(020)</ADR_TELEFOON2>
<ADR_TELEFAX>(020)</ADR_TELEFAX>
<ADR_NUMMER>2</ADR_NUMMER>
<ADR_OMSCHRIJVING>Ape&amp;apos;s Tweede verzend adres</ADR_OMSCHRIJVING>
</NAW_VERZENDADRES>
</NAW_VERZENDADRESSEN>
<NAW_SELECTIES>
<NAW_SELECTIE>
<NAW_SELECTIECODE>B02</NAW_SELECTIECODE>
</NAW_SELECTIE>
<NAW_SELECTIE>
<NAW_SELECTIECODE>Cat</NAW_SELECTIECODE>
</NAW_SELECTIE>
<NAW_SELECTIE>
<NAW_SELECTIECODE>H07</NAW_SELECTIECODE>
</NAW_SELECTIE>
<NAW_SELECTIE>
<NAW_SELECTIECODE>M01</NAW_SELECTIECODE>
</NAW_SELECTIE>
<NAW_SELECTIE>
<NAW_SELECTIECODE>SeN</NAW_SELECTIECODE>
</NAW_SELECTIE>
</NAW_SELECTIES>
<NAW_BANKREKENINGEN>
<NAW_BANKREKENING>
<BANK_REKENINGNUMMER>56266765655</BANK_REKENINGNUMMER>
<BANK_REKENINGHOUDER>Ape Zoo</BANK_REKENINGHOUDER>
<BANK_ADRES>Zoobank</BANK_ADRES>
<BANK_POSTCODE>1000 AA</BANK_POSTCODE>
<BANK_WOONPLAATS>ARTIS</BANK_WOONPLAATS>
<BANK_LANDCODE>NL</BANK_LANDCODE>
<BANK_ISDEFAULTREKENING>true</BANK_ISDEFAULTREKENING>
<BANK_ISINCASSOREKENING>false</BANK_ISINCASSOREKENING>
<BANK_ISZUIVEREREKENING>false</BANK_ISZUIVEREREKENING>
<BANK_ISBINNENLAND>true</BANK_ISBINNENLAND>
</NAW_BANKREKENING>
</NAW_BANKREKENINGEN>
<NAW_DOCUMENTEN>
<NAW_DOCUMENT_INSTELLING>
<DOC_DOCUMENTSOORT>ORDERBEV</DOC_DOCUMENTSOORT>
<DOC_REGELS>
<DOC_REGEL>
<DOC_REGELSOORT>AFDRUK</DOC_REGELSOORT>
<DOC_AFDRUKVOORWAARDE>ALTIJD</DOC_AFDRUKVOORWAARDE>
</DOC_REGEL>
</DOC_REGELS>
</NAW_DOCUMENT_INSTELLING>
<NAW_DOCUMENT_INSTELLING>
<DOC_DOCUMENTSOORT>PAKBON</DOC_DOCUMENTSOORT>
<DOC_REGELS>
<DOC_REGEL>
<DOC_REGELSOORT>AFDRUK</DOC_REGELSOORT>
<DOC_AFDRUKVOORWAARDE>ALTIJD</DOC_AFDRUKVOORWAARDE>
</DOC_REGEL>
</DOC_REGELS>
</NAW_DOCUMENT_INSTELLING>
<NAW_DOCUMENT_INSTELLING>
<DOC_DOCUMENTSOORT>PROFORMA</DOC_DOCUMENTSOORT>
<DOC_REGELS>
<DOC_REGEL>
<DOC_REGELSOORT>AFDRUK</DOC_REGELSOORT>
<DOC_AFDRUKVOORWAARDE>ALTIJD</DOC_AFDRUKVOORWAARDE>
</DOC_REGEL>
</DOC_REGELS>
</NAW_DOCUMENT_INSTELLING>
<NAW_DOCUMENT_INSTELLING>
<DOC_DOCUMENTSOORT>FACTBATCH</DOC_DOCUMENTSOORT>
<DOC_REGELS>
<DOC_REGEL>
<DOC_REGELSOORT>AFDRUK</DOC_REGELSOORT>
<DOC_AFDRUKVOORWAARDE>ALTIJD</DOC_AFDRUKVOORWAARDE>
</DOC_REGEL>
</DOC_REGELS>
</NAW_DOCUMENT_INSTELLING>
<NAW_DOCUMENT_INSTELLING>
<DOC_DOCUMENTSOORT>FACTDIRECT</DOC_DOCUMENTSOORT>
<DOC_REGELS>
<DOC_REGEL>
<DOC_REGELSOORT>AFDRUK</DOC_REGELSOORT>
<DOC_AFDRUKVOORWAARDE>ALTIJD</DOC_AFDRUKVOORWAARDE>
</DOC_REGEL>
</DOC_REGELS>
</NAW_DOCUMENT_INSTELLING>
<NAW_DOCUMENT_INSTELLING>
<DOC_DOCUMENTSOORT>FACTPROJECT</DOC_DOCUMENTSOORT>
<DOC_REGELS>
<DOC_REGEL>
<DOC_REGELSOORT>AFDRUK</DOC_REGELSOORT>
<DOC_AFDRUKVOORWAARDE>ALTIJD</DOC_AFDRUKVOORWAARDE>
</DOC_REGEL>
</DOC_REGELS>
</NAW_DOCUMENT_INSTELLING>
<NAW_DOCUMENT_INSTELLING>
<DOC_DOCUMENTSOORT>REKOVERZICHT</DOC_DOCUMENTSOORT>
<DOC_REGELS>
<DOC_REGEL>
<DOC_REGELSOORT>AFDRUK</DOC_REGELSOORT>
<DOC_AFDRUKVOORWAARDE>ALTIJD</DOC_AFDRUKVOORWAARDE>
</DOC_REGEL>
<DOC_REGEL>
<DOC_REGELSOORT>AFDRUKFORMAAT</DOC_REGELSOORT>
<DOC_AFDRUKFORMAATNUMMER>10</DOC_AFDRUKFORMAATNUMMER>
<DOC_AFDRUKVOORWAARDE>ALTIJD</DOC_AFDRUKVOORWAARDE>
</DOC_REGEL>
</DOC_REGELS>
</NAW_DOCUMENT_INSTELLING>
<NAW_DOCUMENT_INSTELLING>
<DOC_DOCUMENTSOORT>INCASSOMISLUKT</DOC_DOCUMENTSOORT>
<DOC_REGELS>
<DOC_REGEL>
<DOC_REGELSOORT>AFDRUK</DOC_REGELSOORT>
<DOC_AFDRUKVOORWAARDE>ALTIJD</DOC_AFDRUKVOORWAARDE>
</DOC_REGEL>
<DOC_REGEL>
<DOC_REGELSOORT>AFDRUKFORMAAT</DOC_REGELSOORT>
<DOC_AFDRUKFORMAATNUMMER>10</DOC_AFDRUKFORMAATNUMMER>
<DOC_AFDRUKVOORWAARDE>ALTIJD</DOC_AFDRUKVOORWAARDE>
</DOC_REGEL>
</DOC_REGELS>
</NAW_DOCUMENT_INSTELLING>
<NAW_DOCUMENT_INSTELLING>
<DOC_DOCUMENTSOORT>AANMANING1</DOC_DOCUMENTSOORT>
<DOC_REGELS>
<DOC_REGEL>
<DOC_REGELSOORT>AFDRUK</DOC_REGELSOORT>
<DOC_AFDRUKVOORWAARDE>ALTIJD</DOC_AFDRUKVOORWAARDE>
</DOC_REGEL>
<DOC_REGEL>
<DOC_REGELSOORT>AFDRUKFORMAAT</DOC_REGELSOORT>
<DOC_AFDRUKFORMAATNUMMER>10</DOC_AFDRUKFORMAATNUMMER>
<DOC_AFDRUKVOORWAARDE>ALTIJD</DOC_AFDRUKVOORWAARDE>
</DOC_REGEL>
</DOC_REGELS>
</NAW_DOCUMENT_INSTELLING>
<NAW_DOCUMENT_INSTELLING>
<DOC_DOCUMENTSOORT>AANMANING2</DOC_DOCUMENTSOORT>
<DOC_REGELS>
<DOC_REGEL>
<DOC_REGELSOORT>AFDRUK</DOC_REGELSOORT>
<DOC_AFDRUKVOORWAARDE>ALTIJD</DOC_AFDRUKVOORWAARDE>
</DOC_REGEL>
<DOC_REGEL>
<DOC_REGELSOORT>AFDRUKFORMAAT</DOC_REGELSOORT>
<DOC_AFDRUKFORMAATNUMMER>10</DOC_AFDRUKFORMAATNUMMER>
<DOC_AFDRUKVOORWAARDE>ALTIJD</DOC_AFDRUKVOORWAARDE>
</DOC_REGEL>
</DOC_REGELS>
</NAW_DOCUMENT_INSTELLING>
<NAW_DOCUMENT_INSTELLING>
<DOC_DOCUMENTSOORT>AANMANING3</DOC_DOCUMENTSOORT>
<DOC_REGELS>
<DOC_REGEL>
<DOC_REGELSOORT>AFDRUK</DOC_REGELSOORT>
<DOC_AFDRUKVOORWAARDE>ALTIJD</DOC_AFDRUKVOORWAARDE>
</DOC_REGEL>
<DOC_REGEL>
<DOC_REGELSOORT>AFDRUKFORMAAT</DOC_REGELSOORT>
<DOC_AFDRUKFORMAATNUMMER>10</DOC_AFDRUKFORMAATNUMMER>
<DOC_AFDRUKVOORWAARDE>ALTIJD</DOC_AFDRUKVOORWAARDE>
</DOC_REGEL>
</DOC_REGELS>
</NAW_DOCUMENT_INSTELLING>
<NAW_DOCUMENT_INSTELLING>
<DOC_DOCUMENTSOORT>AANMANING4</DOC_DOCUMENTSOORT>
<DOC_REGELS>
<DOC_REGEL>
<DOC_REGELSOORT>AFDRUK</DOC_REGELSOORT>
<DOC_AFDRUKVOORWAARDE>ALTIJD</DOC_AFDRUKVOORWAARDE>
</DOC_REGEL>
<DOC_REGEL>
<DOC_REGELSOORT>AFDRUKFORMAAT</DOC_REGELSOORT>
<DOC_AFDRUKFORMAATNUMMER>10</DOC_AFDRUKFORMAATNUMMER>
<DOC_AFDRUKVOORWAARDE>ALTIJD</DOC_AFDRUKVOORWAARDE>
</DOC_REGEL>
</DOC_REGELS>
</NAW_DOCUMENT_INSTELLING>
</NAW_DOCUMENTEN>
<NAW_VRIJERUBRIEKEN>
<NAW_VRIJERUBRIEK>
<NAW_VRIJERUBRIEK_NAAM>Beroep</NAW_VRIJERUBRIEK_NAAM>
<NAW_VRIJERUBRIEK_WAARDE>Tandarts</NAW_VRIJERUBRIEK_WAARDE>
</NAW_VRIJERUBRIEK>
<NAW_VRIJERUBRIEK>
<NAW_VRIJERUBRIEK_NAAM>Catalogus sinds</NAW_VRIJERUBRIEK_NAAM>
<NAW_VRIJERUBRIEK_WAARDE>1909-05-06</NAW_VRIJERUBRIEK_WAARDE>
</NAW_VRIJERUBRIEK>
<NAW_VRIJERUBRIEK>
<NAW_VRIJERUBRIEK_NAAM>Geboortedatum</NAW_VRIJERUBRIEK_NAAM>
<NAW_VRIJERUBRIEK_WAARDE>1968-03-24</NAW_VRIJERUBRIEK_WAARDE>
</NAW_VRIJERUBRIEK>
<NAW_VRIJERUBRIEK>
<NAW_VRIJERUBRIEK_NAAM>GSM</NAW_VRIJERUBRIEK_NAAM>
<NAW_VRIJERUBRIEK_WAARDE>5550397483</NAW_VRIJERUBRIEK_WAARDE>
</NAW_VRIJERUBRIEK>
<NAW_VRIJERUBRIEK>
<NAW_VRIJERUBRIEK_NAAM>Testset sinds</NAW_VRIJERUBRIEK_NAAM>
<NAW_VRIJERUBRIEK_WAARDE>2009-05-06</NAW_VRIJERUBRIEK_WAARDE>
</NAW_VRIJERUBRIEK>
<NAW_VRIJERUBRIEK>
<NAW_VRIJERUBRIEK_NAAM>Website</NAW_VRIJERUBRIEK_NAAM>
<NAW_VRIJERUBRIEK_WAARDE>www.dePoPo.net</NAW_VRIJERUBRIEK_WAARDE>
</NAW_VRIJERUBRIEK>
</NAW_VRIJERUBRIEKEN>
</DEBITEUR>
</DEBITEUREN>
</KING_DEBITEUREN>
This code example demonstrates how to convert a List(Of… into an in memory data table object, and write the content out to an XML file.
The second block does the reverse, it reads in an XML file into an in memory table, and then breaks the content of the table down into the original List(Of…) objects.
''' <summary>
''' Convert a List(Of ... to XML format, and write the target XML file pointed to
''' </summary>
''' <param name="oItemList">Data to be written</param>
''' <param name="sTargetFile">Target file to write to</param>
''' <remarks></remarks>
Public Shared sub ToXML(Byval oItemList as List(Of class_ServerTasks), Byval sTargetFile as string)
using oTable as new Datatable
oTable.Tablename = "class_ServerTasks"
oTable.columns.add("id", Type.GetType("System.Int32"))
oTable.columns.add("task_caller_station", Type.GetType("System.String"))
oTable.columns.add("task_caller_user", Type.GetType("System.String"))
oTable.columns.add("task_caller_stamp", Type.GetType("System.DateTime"))
oTable.columns.add("task_caller_company", Type.GetType("System.String"))
oTable.columns.add("task_caller_server", Type.GetType("System.String"))
oTable.columns.add("task_caller_order", Type.GetType("System.String"))
oTable.columns.add("task_caller_parameters", Type.GetType("System.String"))
oTable.columns.add("task_caller_wantsfeedback", Type.GetType("System.Boolean"))
oTable.columns.add("task_caller_feedback", Type.GetType("System.String"))
oTable.columns.add("task_disp_stamp", Type.GetType("System.DateTime"))
oTable.columns.add("task_mod_completed_stamp", Type.GetType("System.DateTime"))
oTable.columns.add("task_mod_completed_ok", Type.GetType("System.Boolean"))
oTable.columns.add("task_mod_completion_msg", Type.GetType("System.String"))
oTable.columns.add("testfield", Type.GetType("System.Byte[]"))
oTable.columns.add("testfieldint", Type.GetType("System.Int32"))
oTable.columns.add("testfieldstring", Type.GetType("System.String"))
oTable.Columns("ID").Unique=True
For each oItem as class_ServerTasks in oItemList
dim oRow as DataRow = oTable.NewRow
oRow("id") = oItem.id
oRow("task_caller_station") = oItem.task_caller_station
oRow("task_caller_user") = oItem.task_caller_user
oRow("task_caller_stamp") = oItem.task_caller_stamp
oRow("task_caller_company") = oItem.task_caller_company
oRow("task_caller_server") = oItem.task_caller_server
oRow("task_caller_order") = oItem.task_caller_order
oRow("task_caller_parameters") = oItem.task_caller_parameters
oRow("task_caller_wantsfeedback") = oItem.task_caller_wantsfeedback
oRow("task_caller_feedback") = oItem.task_caller_feedback
oRow("task_disp_stamp") = oItem.task_disp_stamp
oRow("task_mod_completed_stamp") = oItem.task_mod_completed_stamp
oRow("task_mod_completed_ok") = oItem.task_mod_completed_ok
oRow("task_mod_completion_msg") = oItem.task_mod_completion_msg
oRow("testfield") = oItem.testfield
oRow("testfieldint") = oItem.testfieldint
oRow("testfieldstring") = oItem.testfieldstring
oTable.Rows.Add(oRow)
Next
oTable.WriteXML(sTargetFile)
end using
end sub
''' <summary>
''' Reads the records in the Sourcefile, and returns a lList(of ... with the contents
''' </summary>
''' <param name="sSourceFile">XML file with class records</param>
''' <param name="sConString">Connection String of the target database</param>
''' <returns>List(of .. with class records, connected to the chosen database</returns>
''' <remarks></remarks>
Public Shared Function FromXML(byval sSourceFile as string, byval sConString as String) as List(Of class_ServerTasks)
dim oReturn as new List(of class_ServerTasks)
using oTable as new Datatable
oTable.Tablename = "class_ServerTasks"
oTable.columns.add("id", Type.GetType("System.Int32"))
oTable.columns.add("task_caller_station", Type.GetType("System.String"))
oTable.columns.add("task_caller_user", Type.GetType("System.String"))
oTable.columns.add("task_caller_stamp", Type.GetType("System.DateTime"))
oTable.columns.add("task_caller_company", Type.GetType("System.String"))
oTable.columns.add("task_caller_server", Type.GetType("System.String"))
oTable.columns.add("task_caller_order", Type.GetType("System.String"))
oTable.columns.add("task_caller_parameters", Type.GetType("System.String"))
oTable.columns.add("task_caller_wantsfeedback", Type.GetType("System.Boolean"))
oTable.columns.add("task_caller_feedback", Type.GetType("System.String"))
oTable.columns.add("task_disp_stamp", Type.GetType("System.DateTime"))
oTable.columns.add("task_mod_completed_stamp", Type.GetType("System.DateTime"))
oTable.columns.add("task_mod_completed_ok", Type.GetType("System.Boolean"))
oTable.columns.add("task_mod_completion_msg", Type.GetType("System.String"))
oTable.columns.add("testfield", Type.GetType("System.Byte[]"))
oTable.columns.add("testfieldint", Type.GetType("System.Int32"))
oTable.columns.add("testfieldstring", Type.GetType("System.String"))
oTable.Columns("ID").Unique=True
oTable.ReadXML(sSourcefile)
for each oRow as DataRow in oTable.Rows
dim oItem as new class_ServerTasks(sConString)
if oRow("id") IsNot DBNull.Value then oItem.id = oRow("id")
if oRow("task_caller_station") IsNot DBNull.Value then oItem.task_caller_station = oRow("task_caller_station")
if oRow("task_caller_user") IsNot DBNull.Value then oItem.task_caller_user = oRow("task_caller_user")
if oRow("task_caller_stamp") IsNot DBNull.Value then oItem.task_caller_stamp = oRow("task_caller_stamp")
if oRow("task_caller_company") IsNot DBNull.Value then oItem.task_caller_company = oRow("task_caller_company")
if oRow("task_caller_server") IsNot DBNull.Value then oItem.task_caller_server = oRow("task_caller_server")
if oRow("task_caller_order") IsNot DBNull.Value then oItem.task_caller_order = oRow("task_caller_order")
if oRow("task_caller_parameters") IsNot DBNull.Value then oItem.task_caller_parameters = oRow("task_caller_parameters")
if oRow("task_caller_wantsfeedback") IsNot DBNull.Value then oItem.task_caller_wantsfeedback = oRow("task_caller_wantsfeedback")
if oRow("task_caller_feedback") IsNot DBNull.Value then oItem.task_caller_feedback = oRow("task_caller_feedback")
if oRow("task_disp_stamp") IsNot DBNull.Value then oItem.task_disp_stamp = oRow("task_disp_stamp")
if oRow("task_mod_completed_stamp") IsNot DBNull.Value then oItem.task_mod_completed_stamp = oRow("task_mod_completed_stamp")
if oRow("task_mod_completed_ok") IsNot DBNull.Value then oItem.task_mod_completed_ok = oRow("task_mod_completed_ok")
if oRow("task_mod_completion_msg") IsNot DBNull.Value then oItem.task_mod_completion_msg = oRow("task_mod_completion_msg")
if oRow("testfield") IsNot DBNull.Value then oItem.testfield = oRow("testfield")
if oRow("testfieldint") IsNot DBNull.Value then oItem.testfieldint = oRow("testfieldint")
if oRow("testfieldstring") IsNot DBNull.Value then oItem.testfieldstring = oRow("testfieldstring")
oReturn.Add(oItem)
next
end using
return oReturn
end function
These code samples show how to recursively scan a directory tree
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim s_result As New List(Of String)
s_result = TreeList("c:\", s_result)
Dim outfile As New System.IO.StreamWriter(My.Computer.FileSystem.SpecialDirectories.Desktop & "\output.txt")
For Each result As String In s_result
outfile.WriteLine(result)
Next
outfile.Close()
Process.Start(My.Computer.FileSystem.SpecialDirectories.Desktop & "\output.txt")
End Sub
Function TreeList(ByVal dir As String, ByVal resultlist As List(Of String)) As List(Of String)
With My.Computer.FileSystem
' --- Scan all file in the requested directory, and add then to the list
Try
For Each file As String In .GetFiles(dir)
resultlist.Add(file)
Next file
Catch ex As Exception
' --- no permissions for this path, symlink, or other mischief
End Try
' --- Call the mapper itsself for each directory in the current directory
Try
For Each folder As Object In .GetDirectories(dir)
resultlist = TreeList(folder, resultlist)
Next folder
Catch ex As Exception
' --- no permissions for this path, symlink, or other mischief
End Try
End With
Return resultlist
End Function
End Class
Imports System.IO
Module Module1
Private Sub ListDirectory(ByVal di As DirectoryInfo)
Try
Dim oDi() As DirectoryInfo = di.GetDirectories
Dim oFi() As FileInfo = di.GetFiles
' --- List all files in the current directory
For Each oFile As FileInfo In oFi
Console.WriteLine(oFile.FullName)
Next
' //
' --- Call listdirectory itsself for all subdirectory's
For Each oDirectory As DirectoryInfo In oDi
ListDirectory(oDirectory)
Next
' //
Catch ex As Exception
Console.WriteLine("Error accessing {0}, ENTER to continue", di.FullName)
Console.ReadLine()
End Try
End Sub
Sub Main()
Dim oDi As New DirectoryInfo("c:\")
ListDirectory(oDi)
Console.ReadLine()
End Sub
End Module
This code sample reads the pixel of the screen that the mouse points to, and changes the form’s background color to match that pixel’s color. Note that this code attaches to the main screen, and not to the window of a specific application. If you want to attach to a specific application’s window and read inside that, the sample lower on the page applies.
Public Class Form1
Public Declare Function GetPixel Lib "gdi32.dll" (ByVal hdc As IntPtr, _
ByVal nXPos As Int32, _
ByVal nYPos As Int32 _
) As Int32
Private Declare Function GetDC Lib "user32.dll" (ByVal hWnd As IntPtr) As IntPtr
Private Declare Function ReleaseDC Lib "user32.dll" (ByVal hWnd As IntPtr, _
ByVal hdc As IntPtr _
) As Int32
Private mainhdc = New IntPtr()
Private Sub Form1_Shown(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Shown
mainhdc = GetDC(IntPtr.Zero)
While True ' --- endless loop
BackColor = ColorTranslator.FromWin32(GetPixel(mainhdc, Cursor.Position.X, Cursor.Position.Y))
Application.DoEvents()
End While
End Sub
End Class
For the second sample, start notepad and type some text on the top line. The programm will retrieve a handle to the notepad window, and copy the upper left part of that window to the form.
Public Class Form1
Private Declare Function GetDC Lib "user32.dll" (ByVal hWnd As IntPtr) As IntPtr
Private Sub Form1_Shown(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Shown
' --- GetProcessByName attaches to the friendly name of a process, so without path and the .exe
Dim oProc As Process() = Process.GetProcessesByName("notepad")
If oProc.Length = 0 Then
MsgBox("Unable to find the notepad.exe process")
End
End If
' --- Get a handle to
Dim ptrWindowhandle As IntPtr = oProc(0).MainWindowHandle
Dim ptrNotepadhdc As IntPtr = GetDC(ptrWindowhandle)
' --- Create a graphics object on the current form1
Dim oGraphics As Graphics = Me.CreateGraphics
' --- Loop some notepad pixels and draw them on the form
' If you typed something in the notepad window it will appear on the Form
' If you left notepad blank just a white block will appear
For x = 1 To 50
For y = 1 To 50
' --- Get the pixel from the notepad window handle
Dim oPixelcolor As Color = ColorTranslator.FromWin32(GetPixel(ptrNotepadhdc, x, y))
' --- we cant set a pixel directly on a gdi+ surface, so we need to resort
' to creating a single pixel bitmap in the correct color, and placing that
Dim bm As Bitmap = New Bitmap(1, 1)
bm.SetPixel(0, 0, oPixelcolor)
oGraphics.DrawImageUnscaled(bm, x, y)
Next
Next
End Sub
End Class
as minitech pointed out here there is a shorter and more “.net way” method to read pixels from the screen if you are not interested in attaching to a specific process.
Private Function GetScreenColor(ByVal location As Point)
Dim bmp As New Bitmap(1, 1)
Using g As Graphics = Graphics.FromImage(bmp)
g.CopyFromScreen(location, Point.Empty, New Size(1, 1))
End Using
Return bmp.GetPixel(0, 0)
End Function
' --- Offer a directory browser dialog to the user, and store the selected path Dim sSelectedpath As String Using oBrowser As New FolderBrowserDialog() If oBrowser.ShowDialog() = Windows.Forms.DialogResult.OK Then sSelectedpath = oBrowser.SelectedPath Else sSelectedpath = String.Empty End If End Using ' //
Dim s_filename As String = ""
Dim myopener As New OpenFileDialog
myopener.Filter = "XML files (*.xml)|*.xml|All files (*.*)|*.*"
If myopener.ShowDialog = Windows.Forms.DialogResult.OK Then
s_filename = myopener.FileName
Else
MsgBox("No file selected")
End If
Dim mysavedialog As New SaveFileDialog mysavedialog.Filter = "XML files (*.xml)|*.xml|All files (*.*)|*.*" mysavedialog.InitialDirectory = My.Computer.FileSystem.SpecialDirectories.MyDocuments If mysavedialog.ShowDialog() = System.Windows.Forms.DialogResult.Cancel Then Exit Sub Dim s_exportfile As String = mysavedialog.FileName
The following snippet demonstrates the use of the OpenFileDialog and FolderBrowserDialog in a small C# programm that splits a source PDF into multiple pages. Button1 Allows the user to select the source PDF. Button2 allows the user to select a target folder to place the seperate pages in. Finaly, button3 is unrelated to the subject, but does the actual splitting based on a modified sample from the PDFSharp lib.
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using PdfSharp;
using PdfSharp.Drawing;
using PdfSharp.Pdf;
using PdfSharp.Pdf.IO;
namespace PDF_Splitter
{
public partial class Form1 : Form
{
private string sSourcefile;
private string sTargetFolder;
public Form1()
{
InitializeComponent();
}
private void button3_Click(object sender, EventArgs e)
{
Cursor.Current = Cursors.WaitCursor;
PdfDocument inputDocument = PdfReader.Open(sSourcefile, PdfDocumentOpenMode.Import);
string name = Path.GetFileNameWithoutExtension(sSourcefile);
for (int idx = 0; idx < inputDocument.PageCount; idx++)
{
// Create new document
PdfDocument outputDocument = new PdfDocument();
outputDocument.Version = inputDocument.Version;
outputDocument.Info.Title = String.Format("Page {0} of {1}", idx + 1, inputDocument.Info.Title);
outputDocument.Info.Creator = inputDocument.Info.Creator;
// Add the page and save it
outputDocument.AddPage(inputDocument.Pages[idx]);
outputDocument.Save(String.Format("{2}\\{0} - Page {1}.pdf", name, idx + 1, sTargetFolder));
}
Cursor.Current = Cursors.Default;
MessageBox.Show("Klaar!");
}
// Source picker
private void button1_Click(object sender, EventArgs e)
{
using (OpenFileDialog oPicker = new OpenFileDialog())
{
if (oPicker.ShowDialog() == DialogResult.OK)
{
sSourcefile = oPicker.FileName;
labelSource.Text = sSourcefile;
button2.Enabled = true;
}
}
}
// Target folder
private void button2_Click(object sender, EventArgs e)
{
using (var oBrowser = new FolderBrowserDialog())
{
if (oBrowser.ShowDialog() == DialogResult.OK)
{
sTargetFolder = oBrowser.SelectedPath;
labelTarget.Text = sTargetFolder;
button3.Enabled = true;
}
}
}
private void Form1_Load(object sender, EventArgs e)
{
}
}
}
This code sample sets up a handler to monitor the c:\ disk for changes in any file’s size, and report those changes to the console.
Imports System.IO
Module Module1
''' <summary>
''' Display the change events detected by the file system watcher on the console
''' </summary>
''' <param name="sender"></param>
''' <param name="e"></param>
''' <remarks></remarks>
Private Sub DisplayChangeEvent(ByVal sender As Object, ByVal e As FileSystemEventArgs)
Console.WriteLine("{0}{1} {2} ", e.FullPath, e.Name, e.ChangeType)
End Sub
''' <summary>
''' Monitor the file system for changes, and report any changes in file size
''' until a random key is pressed.
''' </summary>
''' <remarks></remarks>
Sub Main()
' --- Enlarge the console window for better reading
Console.WindowHeight = 43
Console.WindowWidth = 132
' //
' --- Setup the file system watcher
Dim oWatcher As New FileSystemWatcher("c:\")
oWatcher.IncludeSubdirectories = True
' oWatcher.Filter = "*.ini"
oWatcher.NotifyFilter = NotifyFilters.Size
AddHandler oWatcher.Changed, New FileSystemEventHandler(AddressOf DisplayChangeEvent)
oWatcher.EnableRaisingEvents = True
' //
' --- Just idle in the loop handling events untill the user hits a key
While Console.KeyAvailable = False
System.Threading.Thread.Sleep(1)
End While
' //
End Sub
End Module
To save changes made to a datagrid do the following;
- call endedit on the grid
- call update on the tableadapter, with the datasource as parameter.
In the example below the tablename in the database is named __Global_Settings, which is part of the Webgeneratordataset
' --- End editing on the grid, and save changes
' to the attached dataset
DataGridView1.EndEdit()
__Global_SettingsTableAdapter.Update(WebGeneratorDataSet)
MsgBox("Saved")
To save changes made to a data bound form, you can do almost the same:
- Call validate on the form
- call endedit on the bindingdource
- call update on the tableadapter
This code sample demonstrates how to access data in an msaccess database from vb.net code, without having to make a “hard” connection through business objects.
The basic system is the same as could be used when querying an sql server, but using the oledb stack instead of the sqlclient.
Updated 4/2011; C# code sample
/// <summary>Reads a table from an msaccess file and returns a data table object with the content</summary>
/// <param name="sFileHandle">Complete path and filename handle to the msaccess file to read</param>
/// <param name="sTablename">Name of the table in the msaccess database to return</param>
/// <returns>Datatable with the content of the file.</returns>
public static DataTable GetDataTableFromDiskFile(string sFileHandle, string sTablename)
{
// Check for the correct platform
if (NHC.Base.SysInfo.CpuPlatform() == 64)
{ throw new ApplicationException("The call LibMsAccess:GetDataTable depends on Microsoft.Jet.OLEDB.4.0, which is not available in 64 bit mode"); }
string sConString = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}", sFileHandle);
using (var oCon = new OleDbConnection(sConString))
{
oCon.Open();
using (var oCmd = new OleDbCommand("select * from " + sTablename, oCon))
{
using (var oDa = new OleDbDataAdapter(oCmd))
{
using (var oDs = new DataSet())
{
oDa.Fill(oDs);
return oDs.Tables[0].Copy();
}
}
}
}
}
Imports System.Data.OleDb
Dim s_filename as String = "c:\myaccessdatabase.mdb"
Dim mydatareader As OleDbDataReader
Dim myconnection As OleDbConnection
Dim mycommand As OleDbCommand
myconnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" &amp; "Data Source=" &amp; s_filename &amp; ";")
myconnection.Open()
mycommand = New OleDbCommand("SELECT * FROM .........", myconnection)
mydatareader = mycommand.ExecuteReader
While mydatareader.Read()
' handle data
End While
mydatareader.Close()
myconnection.Close()
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
' --- Build a list of all active forms, and display it to the user
Dim sActiveforms As String = String.Empty
For Each oItem As Form In Application.OpenForms
sActiveforms = String.Format("{0}{1}{2}", sActiveforms, oItem.Name, vbCrLf)
Next
MsgBox(String.Format("The names of the active forms are:{0}", sActiveforms))
' //
End Sub
While there are a lot of build in property’s on datagridview controls to stop users from adding and deleting data, there is no default proprty to stop the entire grid from sorting.
There is however, a sorting lock possible through the columns, so if you want to control sorting on a datagridview programmaticaly and stop the user from sorting the results, here is how to do it:
' --- Disable sorting by the user on the datagridview
' While retaining to sort from code
For Each oColumn As DataGridViewColumn In DataGridView1.Columns
oColumn.SortMode = DataGridViewColumnSortMode.Programmatic
Next
' //
Module Module1
' MCTS70-536-1-1
' VB.NET
' Demonstration of a structure with
' an operator (+)
' an Override (toString)
'
Public Structure DemoStruct
Private _waarde As Integer
' --- Read and write initial & current value
Public Property Value() As Integer
Get
Return _waarde
End Get
Set(ByVal value As Integer)
_waarde = value
End Set
End Property
' --- Override de + OPERATOR, to SUBSTRACT the value in arg2 from the struct defined in arg1
Public Shared Operator +(ByVal Arg1 As DemoStruct, ByVal arg2 As Integer) As DemoStruct
Arg1.Value -= arg2
Return Arg1
End Operator
' --- Override the toString METHOD, to display * characters around the output
Public Overrides Function ToString() As String
Return String.Format("*{0}*", Value)
End Function
End Structure
Sub Main()
Dim x As New DemoStruct
x.Value = 10
For f As Integer = 1 To 10
Console.WriteLine(x.Value & " " & x.Value.ToString & " override: " & x.ToString)
x += 2 ' --- This will actualy substract 2, since we did an override of the + operator
x = x + 1 ' --- Same ! + is overriden and now acts as -
Next
' --- End run
Console.WriteLine(">>")
Console.ReadLine()
End Sub
End Module
Module Module1
' MCTS70-536-1-2
' VB.NET
' Demonstration of a structure and an override of the ToString method to display
' all parts of our person
Public Structure Person
' --- Example of an enumaration, see the actual Main() Method for use
Enum Genders
Male
Female
End Enum
Private _firstname As String
Private _lastname As String
Private _age As Integer
Private _gender As Genders
Public Property Gender() As Genders
Get
Return _gender
End Get
Set(ByVal value As Genders)
_gender = value
End Set
End Property
Public Property firstName() As String
Get
Return _firstname
End Get
Set(ByVal value As String)
_firstname = value
End Set
End Property
Public Property lastName() As String
Get
Return _lastname
End Get
Set(ByVal value As String)
_lastname = value
End Set
End Property
Public Property age() As Integer
Get
Return _age
End Get
Set(ByVal value As Integer)
_age = value
End Set
End Property
Public Sub New(ByVal _firstname As String, ByVal _lastname As String, ByVal _age As Integer, ByVal _gender As Genders)
firstName = _firstname
lastName = _lastname
age = _age
Gender = _gender
End Sub
Public Overrides Function ToString() As String
Return firstName & " " & lastName & ", " & age.ToString & " " & Gender.ToString
End Function
End Structure
Sub Main()
Dim x As New Person("Jan", "Aap", 42, Person.Genders.Male)
Console.WriteLine(x.ToString)
' --- Change gender of our struct
x.Gender = Person.Genders.Female
Console.WriteLine(x.ToString)
Console.Write(">>")
Console.ReadLine()
End Sub
End Module
Module Module1
' MCTS70-536/1/1/3
' Extending an existing class
''' <summary>
''' A simple base class
''' </summary>
''' <remarks></remarks>
Public Class Customer
Private _naam As String
Private _adres As String
Private _woonplaats As String
Public Property Naam() As String
Get
Return _naam
End Get
Set(ByVal value As String)
_naam = value
End Set
End Property
Public Property Adres() As String
Get
Return _adres
End Get
Set(ByVal value As String)
_adres = value
End Set
End Property
Public Property Woonplaats() As String
Get
Return _woonplaats
End Get
Set(ByVal value As String)
_woonplaats = value
End Set
End Property
End Class
''' <summary>
''' The XCustomer INHERITS and extends the Base Customer Class
''' </summary>
''' <remarks></remarks>
Public Class XCustomer
Inherits Customer
Private _huisnummer As Integer
Public Property Huisnummer() As Integer
Get
Return _huisnummer
End Get
Set(ByVal value As Integer)
_huisnummer = value
End Set
End Property
' --- Override the ToString method
Public Overrides Function ToString() As String
Return Me.Naam &amp;amp; ":" &amp;amp; Me.Adres &amp;amp; ":" &amp;amp; Me.Huisnummer &amp;amp; ":" &amp;amp; Me.Woonplaats
End Function
End Class
Sub Main()
' Our XCustomer object has ALL properties from the base class, and the extension
' we made. Additionaly, the .ToString method now returns all data embedded in the class
Dim x As New XCustomer
x.Naam = "piet"
x.Huisnummer = 10
Console.WriteLine(x.ToString)
Console.Write(" >>")
Console.ReadLine()
End Sub
End Module
Public Class Form1
' MCTS70-536-1-3
' Registering and handling Events
Private t As Timer
Private p As ProgressBar
Private Sub t_Tick(ByVal sender As Object, ByVal e As EventArgs)
p.Value += 1
If p.Value = 100 Then
p.Value = 1
End If
End Sub
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
' --- Create a progressbar and add it to the form
p = New ProgressBar
p.Location = New Point(20, 20)
Me.Controls.Add(p)
' //
' --- Programmaticaly create a timer
t = New Timer
t.Interval = 100
' --- Attach the tick event
AddHandler t.Tick, AddressOf t_Tick
' --- Start the timer
t.Start()
End Sub
End Class
This example shows how to create a stored procedure in an SQL Server database which can select data from various tables, combine the results as a jon query, and make that data available as a table object in memory for further processing. Additionaly a paramter is passed to the query from the code to influence the results.
The example uses the AdventureworksLT database.
The first code block is the stored procedure that query’s some data and builds a result set.
create procedure sp_demoquery(@ignorecust int)
as
-- Discard temp tables if they still exist in this session
IF Object_ID('tempdb..#selection1') IS NOT NULL DROP TABLE #selection1
IF Object_ID('tempdb..#selection2') IS NOT NULL DROP TABLE #selection2
-- Query 1 deliverig the first half of our data
create table #selection1 (salesorderid int, orderdate datetime, salesordernumber nvarchar(25))
insert into #selection1 (salesorderid, orderdate, salesordernumber)
select SalesOrderID, OrderDate,SalesOrderNumber from SalesLT.SalesOrderHeader
where CustomerID <> @ignorecust
-- Query 2 delivering the second half of our data
create table #selection2 (salesid int, billid int, shipid int)
insert into #selection2 (salesid, billid, shipid)
select SalesOrderID as salesid, BillToAddressID as billid, ShipToAddressID as shipid from SalesLT.SalesOrderHeader
where CustomerID <> @ignorecust
-- The resulting join dataset to be returned from the stored procedure
select * from #selection1 as s1
join #selection2 as s2 on s1.salesorderid = s2.salesid
order by salesorderid
The result of the procedure as shown in the sql server manager:
Finaly, the code to execute the stored procedure in the database, and construct the table object holding the resturned data. In it’s simples form we could construct and type a table manualy and then use a datareader to loop the resultsets and fill the table up. This would result in this code:
Imports System.Data.SqlClient
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
' --- Create a table to hold the result set
Dim oTable As New DataTable
oTable.Columns.Add("salesordid", Type.GetType("System.Int32"))
oTable.Columns.Add("orderdate", Type.GetType("System.Date"))
oTable.Columns.Add("salesordernumber", Type.GetType("System.String"))
oTable.Columns.Add("salesid", Type.GetType("System.Int32"))
oTable.Columns.Add("billid", Type.GetType("System.Int32"))
oTable.Columns.Add("shipid", Type.GetType("System.Int32"))
' //
' --- Run the stored procedure and retrieve the data
Using oCon As New SqlConnection(My.Settings.ConString)
oCon.Open()
Using oCmd As New SqlCommand("sp_demoquery")
oCmd.CommandType = CommandType.StoredProcedure
oCmd.Parameters.AddWithValue("@ignorecust", 31100)
Using oReader As SqlDataReader = oCmd.ExecuteReader
If oReader.HasRows = True Then
While oReader.Read
' --- Create a new row for our table and retrieve data from the query
Dim oRow As DataRow = oTable.NewRow
oRow("salesordid") = oReader("salesorderid").ToString
oRow("orderdate") = oReader("orderdate").ToString
oRow("salesordernumber") = oReader("salesordernumber").ToString
oRow("salesid") = oReader("salesid").ToString
oRow("billid") = oReader("billid").ToString
oRow("shipid") = oReader("shipid").ToString
oTable.Rows.Add(oRow)
End While
End If
End Using
End Using
End Using
' --- oTable now holds the results of the stored procedure
' Perform your processing here
' //
oTable.Dispose()
End Sub
End Class
Continuing along the same line it is also possible to retrieve the results as a generic datatable object which you can use for further processing. We can also pass parameters to the stored procedure if they are required. To retrieve the output of a strored procedure taking two parameters and return the results as a datatable the code would look as follows:
Using oCon As New SqlConnection(My.Settings.ConString)
oCon.Open()
Using oDa As New SqlDataAdapter
oDa.SelectCommand = New SqlCommand("sp_myprocedure", oCon)
oDa.SelectCommand.CommandType = CommandType.StoredProcedure
oDa.SelectCommand.Parameters.AddWithValue("@parm1", nSomeVal)
oDa.SelectCommand.Parameters.AddWithValue("@parm2", nSomeVal)
Using oDs As New DataSet
oDa.Fill(oDs, "results")
' --- Processing here
' MicroTools.ExcelWriter.Write(oDs)
End Using
End Using
End Using
Using oCon As New SqlConnection(My.Settings.modb3ConnectionString)
Using Oda As New SqlDataAdapter("select * from sometable", oCon)
Using oDs As New DataSet
Oda.Fill(oDs, "resultset")
GridControl1.DataSource = oDs
GridControl1.DataMember = "resultset"
End Using
End Using
End Using
In addition to the method below, you can also use the standard context menu provided by Visual Studio. The downside of the standard control is that it will not select the cell oyou are on when used with a datagrid. To get that behaviour you can trap the MouseDown event on the datagrid which is executed before the context menu control. So you could simply bind a standard context menu to the datagrid and this as handler:
Private Sub DataGridView1_CellMouseDown(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellMouseEventArgs) Handles DataGridView1.CellMouseDown
Try
DataGridView1.CurrentCell = DataGridView1.Rows(e.RowIndex).Cells(e.ColumnIndex)
Catch ex As ArgumentException
' --- The user clicked outside a valid cell, we cant select
Beep()
End Try
End Sub
If you are using a DevExpress gridcontrol, the code changes slightly, but still comes down to the same. After binding the standard context menu control to the gridcontrol, you can reference the currently selected row and retrive the value from a cell in it:
''' <summary>
''' this is the handler for the context menu's option
''' </summary>
''' <param name="sender"></param>
''' <param name="e"></param>
''' <remarks></remarks>
Private Sub BewerkenToolStripMenuItem_Click(sender As System.Object, e As System.EventArgs) Handles BewerkenToolStripMenuItem.Click
' --- Retrieve the value from the ID column from the currently selected row
Dim row As System.Data.DataRow = GridView1.GetDataRow(GridView1.FocusedRowHandle)
MsgBox(row.Item("id"))
End Sub
The method below is an alternative method which has the same behaviour, but does not use the context menu control supplied by Visual Studio.
Additionaly, the method below dows not show the menu as a response to rigth clicks when the mouse is over a blank are of the grid.
Private Sub DataGridView1_CellMouseUp(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellMouseEventArgs) Handles DataGridView1.CellMouseUp
If e.Button = Windows.Forms.MouseButtons.Right Then
If e.ColumnIndex = -1 = False And e.RowIndex = -1 = False Then
' Valid cell, Select it
Me.DataGridView1.ClearSelection()
Me.DataGridView1.CurrentCell = Me.DataGridView1.Item(e.ColumnIndex, e.RowIndex)
' Popup the context menu
Dim mygrid As DataGridView = sender
Dim mymenu As ContextMenuStrip = New ContextMenuStrip
mymenu.Items.Add("Option 1", Nothing, AddressOf Option1Call)
mymenu.Items.Add("Option 2", Nothing, AddressOf Option2Call)
Dim mypoint As Point = mygrid.PointToClient(Control.MousePosition)
mymenu.Show(DataGridView1, mypoint.X, mypoint.Y)
End If
End If
End Sub
Public Sub Option1Call()
Dim n_current_row As Integer
Dim n_clicked_id As Integer
Dim d_row As DataGridViewRow
' get the selected gridrow
n_current_row = Me.DataGridView1.CurrentCell.RowIndex
If n_current_row < 0 Then Exit Sub ' no data on this gridline
d_row = DataGridView1.Rows(n_current_row)
' get the id value of the database record (assuming column 0 contains the id)
n_clicked_id = d_row.Cells(0).Value
MsgBox("Clicked database id: " &amp;amp; n_clicked_id)
End Sub
Public Sub Option2Call()
MsgBox("Option 2 clicked")
End Sub
These samples whow how to compress and decompress files with the build in framework methods. For real world applications consider using something like .netzip from codeplex, since the compression quality from the build in methods is limited. Also, the build in methods will allow you to just compress and decompress a single file.
Imports System.IO
Imports System.IO.Compression
Module packtool
''' <summary>
''' Example on using a deflatestream to compress a file into a target file
''' </summary>
''' <param name="args">args() will automagicaly hold the command line args
''' passed to the application</param>
''' <remarks>Error checking on the source and target files is left out to
''' keep the example to it's core. In a live deployment you should always
''' check to see if the source file is present and accessible and the target
''' file writable</remarks>
Sub Main(ByVal args() As String)
If args.Count <> 2 Then
Console.WriteLine("format: pack <source> <target>")
Return
End If
Dim sSource As String = args(0)
Dim sTarget As String = args(1)
Console.WriteLine("packing {0} to {1}", sSource, sTarget)
Dim oSourcefile As FileStream = File.OpenRead(sSource)
Dim oDestfile As FileStream = File.Create(sTarget)
Using oCompressed As New System.IO.Compression.DeflateStream(oDestfile, CompressionMode.Compress)
Dim dBuffer(8192) As Byte ' --- 8 KB data buffer
Dim nProcessed As Int64 = 0
Dim nRead As Integer = 0
Do
nRead = oSourcefile.Read(dBuffer, 0, 8192)
nProcessed += nRead
oCompressed.Write(dBuffer, 0, nRead)
Console.Write("{0} Kb processed{1}", Int(nProcessed / 1024), vbCr)
Loop Until nRead = 0
Console.WriteLine()
End Using
oDestfile.Close()
oSourcefile.Close()
End Sub
End Module
Imports System.IO
Imports System.IO.Compression
Module unpacktool
''' <summary>
''' Example on uncompressing a compressed file with the standard .net methods
''' </summary>
''' <param name="args">Command line arguments passed by the user</param>
''' <remarks>Error checking left out on the source and target file</remarks>
Sub Main(ByVal args() As String)
If args.Count <> 2 Then
Console.WriteLine("format: unpack <source> <target>")
Return
End If
Dim sSource As String = args(0)
Dim sTarget As String = args(1)
Console.Write("packing {0} to {1}", sSource, sTarget)
Dim oSourcefile As FileStream = File.OpenRead(sSource)
Dim oDestfile As FileStream = File.Create(sTarget)
Using oCompressed As New System.IO.Compression.DeflateStream(oSourcefile, CompressionMode.Decompress)
' --- We could use a larger buffer here like in the compression example, but there
' is no real performance gain to be made since the framework handles the buffering
' quite efficiently.
Dim dByte As Integer = oCompressed.ReadByte
While dByte <> -1
oDestfile.WriteByte(CType(dByte, Byte))
dByte = oCompressed.ReadByte()
End While
End Using
oDestfile.Close()
oSourcefile.Close()
End Sub
End Module
Module Module1
Sub Main()
' --- Idle time only, the lowest priority. Use with care as your application could get very little cpu
' time allocated on a busy system
System.Diagnostics.Process.GetCurrentProcess().PriorityClass = System.Diagnostics.ProcessPriorityClass.Idle
' --- Below normal priority
System.Diagnostics.Process.GetCurrentProcess().PriorityClass = System.Diagnostics.ProcessPriorityClass.BelowNormal
' --- Default priority, equal to other programms
System.Diagnostics.Process.GetCurrentProcess().PriorityClass = System.Diagnostics.ProcessPriorityClass.Normal
' --- High priority, use for importan applications and processes that need to be responsive all the time.
System.Diagnostics.Process.GetCurrentProcess().PriorityClass = System.Diagnostics.ProcessPriorityClass.AboveNormal
' --- Use with care, as your application may eat up more resources then you like !
System.Diagnostics.Process.GetCurrentProcess().PriorityClass = System.Diagnostics.ProcessPriorityClass.RealTime
End Sub
End Module
Using oCon As New SqlConnection(My.Settings.ConString)
Using oDc As New SomeDataContext(oCon)
Dim oQuery = From pr In oDc.SomeTable _
Order By pr.field1, pr.field2 _
Select pr.field1, pr.field2, pr.field3, pr.field4
GridControl1.DataSource = oQuery.ToList
GridView1.OptionsBehavior.Editable = False
End Using
End Using
While in normal cases your database query will handle sorting efficiently, you can run into a situation where you gather data from multiple different sources into an in-memory table object, and need to sort that. This is quickly archieved with a DataView:
' --- Suppose the in memory table object is named oUpdates, and we want ' to sort on the string field somekey, AND on the date field lastlogon Dim oView As DataView = oUpdates.DefaultView oView.Sort = "somekey, lastlogon DESC" oUpdates = oView.ToTable
// Suppose the in memory table object is named oUpdates, and we want // to sort on the string field somekey, AND on the date field lastlogon DataView oView = oUpdates.DefaultView; oView.Sort = "somekey, lastlogon DESC"; oUpdates = oView.ToTable();