1 2 3 4 5  ... Previous Next 

Load CSV into DataTable


If you write a file called a Schema.ini in the same folder as the CSV file you can load it straight into a datatable with the datatypes and headers you want.  Here is the documentation on Schema.ini is here http://msdn.microsoft.com/en-us/library/ms709353(VS.85).aspx

Here is some sample code to do it:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim strCSVFile = "c:\temp\data.csv"
Dim dtReturnData As New DataTable
If CreateSchema(strCSVFile) Then
            CSV2DataTable(strCSVFile, dtReturnData, "SELECT *", "", "", True)
End If
End Sub
Public Function CSV2DataTable(ByVal strFilename As String, ByRef dtCSVData As DataTable, ByVal strSelectList As String, _
ByVal strWhere As String, ByVal strGroupBy As String, ByVal blnHeader As Boolean) As Boolean
If Not System.IO.File.Exists(strFilename) Then
Return False
End If
Dim strFullPath As String = System.IO.Path.GetFullPath(strFilename)
Dim strFile As String = System.IO.Path.GetFileName(strFilename)
Dim strDir As String = System.IO.Path.GetDirectoryName(strFilename)
Dim strConnection As String
If blnHeader = True Then
            strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strDir & "\;" & _
"Extended Properties=""text;HDR=Yes;FMT=Delimited(,)"""
Else
            strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strDir & "\;" & _
"Extended Properties=""text;HDR=No;FMT=Delimited(,)"""
End If
Dim conCSV As OleDb.OleDbConnection
        conCSV = New OleDb.OleDbConnection(strConnection)
        conCSV.Open()
Dim strSQL As String
        strSQL = strSelectList & vbCrLf & "FROM [" & strFile & "]"
If strWhere.Length > 0 Then
            strSQL = strSQL & vbCrLf & strWhere
End If
If strGroupBy.Length > 0 Then
            strSQL = strSQL & vbCrLf & strGroupBy
End If
Dim cmdCSV As New OleDb.OleDbCommand(strSQL, conCSV)
Try
Dim daCSV As New OleDb.OleDbDataAdapter(cmdCSV)
            daCSV.Fill(dtCSVData)
Catch ex As Exception
            MessageBox.Show(ex.Message, "Error Importing Data", MessageBoxButtons.OK, MessageBoxIcon.Error)
Finally
            conCSV.Close()
            conCSV.Dispose()
End Try
Return dtCSVData.Rows.Count > 0
End Function

Private Function CreateSchema(ByVal strFileName As String) As Boolean
Dim ascii As System.Text.Encoding = System.Text.Encoding.ASCII
Dim swSchema As System.IO.StreamWriter = Nothing
Dim blnReturn As Boolean
Dim strSchemaPath As String = System.IO.Path.GetFileName(strFileName)
Try
            strSchemaPath = System.IO.Path.GetDirectoryName(strFileName) & "\Schema.ini"
            swSchema = My.Computer.FileSystem.OpenTextFileWriter(strSchemaPath, False, ascii)
Dim strFile As String = System.IO.Path.GetFileName(strFileName)
            swSchema.WriteLine("[" & strFileName & "]")
            swSchema.WriteLine("ColNameHeader=False")
            swSchema.WriteLine("Format=Delimited(,)")
            swSchema.WriteLine("Col1=Value1 Text")
            swSchema.WriteLine("Col2=Value2  Text")
            swSchema.WriteLine("Col3=SomeDate Date")
            swSchema.WriteLine("Col4=SomePoint1 Integer")
            swSchema.WriteLine("Col5=SomePoint2 Integer")
'Continue for all fields
            blnReturn = True
Catch ex As Exception
            blnReturn = False
Finally
If swSchema IsNot Nothing Then
                swSchema.Close()
End If
End Try
Return blnReturn
End Function

 

Submitted by Lee D. Johnson




test


clip_image002

 

test




VS 2008: Extension Methods


Visual Basic 2008 adds a new features called extension methods.   These allow you to add a method, or function to a type.  For this example we will add an IsGuid function to strings.  All extension methods must be placed in a module.  The function or method must be marked as an extension and the first argument is the type the method extends.

Imports System.Runtime.CompilerServices
Imports System.Text.RegularExpressions

Module Module1

    Sub Main()
        Dim g As String = "82ee4145-632c-42a1-83b9-57ec163eaa17"
        Dim g1 As String = "82ee4145-632c-42a1-83b9-57ec163ea17"

        Console.WriteLine(g & IIf(g.IsGuid, " is ", " is not ") & "a valid guid")
        Console.WriteLine(g1 & IIf(g1.IsGuid, " is ", " is not ") & "a valid guid")
    End Sub

End Module

Public Module MyExtensions

    <Extension()> _
    Public Function IsGuid(ByVal s As String) As Boolean
        Dim regGuid As New Regex("^(\{){0,1}[0-9a-fA-F]{8}\-[0-9a-fA-F]{4}\-[0-9a-fA-F]{4}\-[0-9a-fA-F]{4}\-[0-9a-fA-F]{12}(\}){0,1}$", RegexOptions.Compiled)
        Return regGuid.IsMatch(s)
    End Function
End Module




Making a REST service with VB and WCF


REST which stands for Representational State Transfer is a way of sending data over the Internet without an additional message layer.  Standard web services use soap for there message header.  In this example we will create a service that uses the northwind database to send a  list of the products for a category.

 

Lets start by create a new VB web application. In that web application add a Ado.Net Entities data model.  In that class add the northwind database's product class.

 

image

 

Now add a service named Service1 to the web application.  Lets start by modify the web.config for the service to support rest.  First remove the ServiceBehavior section and add a endpointBehaviors section for webHttp.  In the endpoint we have to change the binding to webHttpBinding and the behaviorConfiguration to the webBehavior we created.

 

    <system.serviceModel>
        <behaviors>
      <endpointBehaviors>
        <behavior name="webBehavior">
          <webHttp/>
        </behavior>
      </endpointBehaviors>
        </behaviors>
        <serviceHostingEnvironment aspNetCompatibilityEnabled="True"></serviceHostingEnvironment>
        <services>
            <service name="RestTest.Service1">
                <endpoint address="" behaviorConfiguration="webBehavior" binding="webHttpBinding" bindingConfiguration="" contract="RestTest.IService1">
                </endpoint>
            </service>
        </services>
    </system.serviceModel>

 

When we added the service we got 2 items the wcf service and an interface for the service.  Before we go any further we need to add a reference to system.servicebehavior.web  

 

In the Interface we need to define the how the categoryId is passed to the service.  In this example it expects product/categoryID in the url for the service

 

Imports System.ServiceModel
Imports System.ServiceModel.Web

<ServiceContract()> _
Public Interface IService1

    <OperationContract()> _
    <WebGet(UriTemplate:="Product/{categoryID}", ResponseFormat:=WebMessageFormat.Xml, BodyStyle:=WebMessageBodyStyle.Bare)> _
    Function GetProducts(ByVal categoryId As String) As List(Of Products)

End Interface

 

In the service we need to set the AspNetCompatibilityMode and write some code to return the products

 

Imports System.ServiceModel.Activation

<AspNetCompatibilityRequirements(RequirementsMode:=AspNetCompatibilityRequirementsMode.Allowed)> _
Public Class Service1
    Implements IService1

    Public Function GetProducts(ByVal categoryId As String) As System.Collections.Generic.List(Of Products) Implements IService1.GetProducts
        Dim dc As New NorthwindEntities
        Dim q = From p In dc.Products Select p Where p.CategoryID = CInt(categoryId)
        Return q.ToList
    End Function
End Class

 

To call the service you would use a url like http://localhost:2050/Service1.svc/Product/7

 

you will get an xml file like this returned

 

 

image



Getting Windows Mobile Device ID


I got email today asking me how to get the device ID from a pocket pc with vb

 

Imports System.Text

Public Class Form1

    <System.Runtime.InteropServices.DllImport("coredll.dll")> _
Private Shared Function GetDeviceUniqueID(ByVal appdata As Byte(), ByVal cbApplictionData As Integer, ByVal dwDeviceIDVersion As Integer, ByVal deviceIDOuput As Byte(), ByRef pcbDeviceIDOutput As Integer) As Integer
    End Function

    Private Function GetDeviceId(ByVal appData As String) As Byte()

        Dim appDataBytes As Byte() = System.Text.Encoding.ASCII.GetBytes(appData)
        Dim outputSize As Integer = 20
        Dim output(19) As Byte

        Dim result As Integer = GetDeviceUniqueID(appDataBytes, appDataBytes.Length, 1, output, outputSize)

        Return output

    End Function

    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim sbId As New StringBuilder

        Dim bID() As Byte = GetDeviceId("MyAppName")

        For Each b In bID
            sbId.Append(String.Format("{0:x2}", b))
        Next

        Debug.WriteLine(sbId.ToString)
    End Sub
End Class

 

References

 

http://www.peterfoot.net/GetDeviceUniqueIDForVB.aspx

http://msdn.microsoft.com/en-us/library/ms893522.aspx




1 2 3 4 5  ... Previous Next