Binding a Windows form to a TableAdapter through a Web Service

How can I use a TableAdapter object in a Windows Form aplication, when this TableAdapter object is returned from a Web Service? It's simple, but...

I wanted a simple thing:
1- define a TableAdapter in a Web Service project, to get a typed DataSet from the BD
2- return this TableAdater in a WebMethod
3- use the TableAdapter returned by the web service from a DataGridView in a Windows form application (referencing the web service, of course).

I tried many combinations before getting this worked, playing with objects and properties, and googling, and playing again... There is finally a recipe that works:

On the web service side:

1. Create a TableAdapter (by dragging adatabase table from the werver explorer and settinga select method). This create a DataSet (name of the *.xsd file), a DataTable (name in the top of the box) and a TableAdapter (with a "TableAdapter" suffix after the DataTable name).
   
2. Create a web method returning an instance of the new DataTable.
   

On the windows application side:

1. In the Windows client project, add a Web Reference to the class of the web service (like the default name "Service1").
   
2. Next, go on the design view of the web form. On the toolbox pane, some items are automatically added. There should be an item with the DataSet used in the web service; the name will be the "*.xsd" file name, not the DataTable or the TableAdapter name (default will be "DataSet1"). This should be in a section of the toolbox labeled with the web service namespace ("MyWebService components"). Drag this component on the web form. A DataSet object will be created.
3. Drag a BindingSource on the web form
4. In the properties of the new BindingSource object, set the datasource to the new DataSet objet. In the DataMember drop down list, you can now choose the name of the DataTable (similar to the TableAdapter name) within the DataSet.
5. In the DataGridView control properties, set the DataSource to the new DataBindingSource object.
6. All properties shoud now be ok.

To get the data populating the DataGridView on load, there is a line to write.In the Load() event of the form, add something like
Me.BindingSource1.DataSource = New [WebReferenceAliasName].[ServiceName]().[WebMethodName]()

It works fine this way.

Reading an Excel file as a DataTable

This method open a Excel file and retun it as a datatable.
It uses oleDB, wich is very powerful.

If your columns contains headers, they will be read as datacolumn names. This way, you can access datarows with theese names as indexers, ex: value = myRow("myColumnName").

Protected Function GetDatatableFromExcel(ByVal fileName As String, ByVal sheetName As String) As DataTable
  Dim conn As System.data.oledb.OleDbConnection
  Dim dataResult As New DataTable
  Try
  conn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
  + fileName + ";Extended Properties=Excel 8.0;")
  conn.Open()

  Dim command As New System.Data.OleDb.OleDbCommand(" SELECT * FROM [" + sheetName + "$]")
  command.Connection = conn
  Dim adaperForExcelBook As New OleDbDataAdapter
  adaperForExcelBook.SelectCommand = command
  adaperForExcelBook.Fill(dataResult)
  conn.Close()

  Catch err As Exception
  Throw New Exception("Error reading file: " + fileName)
  End Try

  Return dataResult
End Function

Converting a DataRow to a string

This can help to show a datarow content. Il shows the column name and values for all the column in the row in this format:
ColumnName=value; ColumnName2=value2;

Protected Function DataRowToString(ByVal dr As DataRow) As String
    ''''
    ''' For debugging purpose, per example : convert a datarow to a string 
    ''' represantation with all columns contents
    Dim result As String = ""
    result += Environment.NewLine
    For Each col As DataColumn In dr.Table.Columns
    result += col.ColumnName + "=" + dr(col.ColumnName).ToString() + " ;"
    Next
    Return result
End Function

Passing parameter to Crystal Report in VB.NET

Here is how to pass a parameter value to a Crystal Report objet at runtime. There are two methods, depending if the report is to be exported as a file or opened with a viewer.
Required namespace:

Imports System.Collections.Generic
Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Shared

EXAMPLE 1: parameter to a report exported as a file

'loading the report with dynamic data 
myCrystalReport.Load()
myCrystalReport.SetDataSource(myDataSet)
myCrystalReport.Refresh()
'Passing the parameter
Dim param As New ParameterDiscreteValue
param.Value = "my value"
myCrystalReport.ParameterFields("parameterName").CurrentValues.Add(param)

'exporting as .PDF

myCrystalReport.ExportToDisk(ExportFormatType.PortableDocFormat, strFileName)

EXAMPLE 2 : parameter to a report opened with a crystal viewer:

   'Loading the report with dynamic data 
 myCrystalReport.SetDataSource(myDataSet)
 myCrystalReport.Refresh()

   'Creating and binding the report viewer
 Dim frmViewer As New CrystalDecisions.Windows.Forms.CrystalReportViewer()
 frmViewer.ReportSource = myCrystalReport


   'Passing the parameter
 Dim oField As New ParameterField()
 Dim oVal As New ParameterDiscreteValue()
 oField.ParameterFieldName = "parameterName"
 oVal.Value = " my parameter value "
 oField.CurrentValues.Add(oVal)
 frmViewer.ParameterFieldInfo.Add(oField)

 'Showing the viewer
 frmViewer.Show()

It works with Crystal Report 10.

Simple function for converting a .NET DataTable to CSV format.

Need to convert a datatable directly to CSV string, write the datatable in a CSV file, or export it in a Excel compatible format?

This method can be used for creating a .csv string or file that can be opened with Excel.
Excel can import CSV, Excel parses automatically CSV data when the CSV file have a ".xls" extension.

''' Exporting a datatable to an Excel file
Public Sub WriteDataTableToCsvFile(ByVal dtTowrite As DataTable, ByVal strFileName As String)
Dim sw As New System.IO.StreamWriter(strFileName, False, System.Text.Encoding.Unicode)
' see function below
dim strContent as string = ConvertDataTableToCSV(dtTowrite, ControlChars.Tab)
sw.Write(strContent)
sw.Close()
End Sub

'''  Converting a datatable to as comma separated value string format, compatible for Excel.
''' This method dynamically create columns headers.
''' The method ensures thant the separator value will be removed from 
''' the datatable fields if it is already there, for consistency purpose.
Public Function ConvertDataTableToCSV(ByVal dtTowrite As DataTable, _
 ByVal strColSeparator As String) As String
 ' Line breaks are reserved for separating rows
 If (strColSeparator = Environment.NewLine) Then
  Throw New Exception("A new line cannot be used for a csv columns separator.")

 Dim sb As New System.Text.StringBuilder
 Dim intNbCols As Integer = dtTowrite.Columns.Count

 'Write headers
 For i As Integer = 0 To intNbCols - 1
     sb.Append(dtTowrite.Columns(i).ColumnName.Replace(strColSeparator, " ") _
     + strColSeparator)
 Next
 sb.Append(Environment.NewLine)

 'write data
 For Each line As DataRow In dtTowrite.Rows
     For i As Integer = 0 To intNbCols - 1
         sb.Append(line(i).ToString().Replace(strColSeparator, " ") + _
         strColSeparator)
     Next
     sb.Append(Environment.NewLine)
 Next

 Return sb.ToString()
End Function

That's all. It can be tested with the following code:

Dim dt As New DataTable()
dt.Columns.Add(New DataColumn("date", GetType(System.DateTime)))
GetType(System.DateTime)))
dt.Columns.Add(New DataColumn("name", GetType(System.String)))
dt.Columns.Add(New DataColumn("id", GetType(System.Int32)))


Dim line As DataRow = dt.NewRow()
line("date") = Now()
line("name") = "Albert Einstein"
line("id") = 28847
line("test") = "sdfiom " + ControlChars.Tab + " pok po"
dt.Rows.Add(line)

line = dt.NewRow()
line("date") = Now().AddYears(99)
line("name") = "Charles Darwin"
line("id") = 28847
line("test") = "sdfsdfsdfsdfsdf"
dt.Rows.Add(line)

line = dt.NewRow()
line("date") = Now().AddDays(2)
line("name") = "Sigmund Freud"
line("id") = 23423423
' ligne("test") = EMPTY ROW
dt.Rows.Add(line)

Dim strTest As String = ConvertDataTableToCSV(dt, ControlChars.Tab)
Dim sw As New System.IO.StreamWriter("C:\Test.xls")
sw.Write(strTest)
sw.Close()