Get all tables from a sql server

This functions returns all sql tables from a given connection as a list of string.

Public Shared Function GetTableList(ByVal strCon As String) As List(Of String)
      ' Create result
      Dim lstResult As New List(Of String)
 
      Try
        Using con As New SqlClient.SqlConnection(strCon)
 
          Try
            con.Open()
          Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical)
          End Try
 
          ' Create command and execute it
          Dim cmd As New SqlClient.SqlCommand("SELECT Table_Name FROM Information_Schema.Tables", con)
          Dim dr As SqlClient.SqlDataReader = cmd.ExecuteReader
 
          ' Add all columns to our list
          Do While dr.Read
            lstResult.Add(dr("Table_Name").ToString)
          Loop
 
        End Using
      Catch ex As Exception
        MsgBox(ex.Message, MsgBoxStyle.Critical)
      End Try
 
      Return lstResult
    End Function Read More

Get all columns from a sql table

Simple function to get all columns from a sql table.

Note: You should prepare the sql statement and check it for injections...

Public Shared Function GetColumnList(ByVal strCon As String, ByVal aDatasetName As String, ByVal aTablename As String) As List(Of String)
      Dim lstResult As New List(Of String)
      Using con As SqlConnection = New SqlConnection(strCon)
        Try
          con.Open()
          Dim ds As New DataSet(aDatasetName)
          Dim da As SqlDataAdapter = New SqlDataAdapter("SELECT Top 1 FROM " & aTablename, con)
          da.Fill(ds, aTablename)
          For Each dt As DataTable In ds.Tables
            For Each dc As DataColumn In dt.Columns
              lstResult.Add(dc.Caption)
            Next
          Next
        Catch ex As Exception
          MsgBox(ex.Message, MsgBoxStyle.Critical)
          Return Nothing
        End Try
      End Using
      Return lstResult
    End Function

Get all columns from a xml file

The following function returns all columns from a xml file as a list of string.

Public Shared Function GetColumnList(ByVal aFilename As String) As List(Of String)
      If Not File.Exists(aFilename) Then
        Throw New System.IO.FileNotFoundException("File not found!", aFilename)
        Exit Function
      End If
      Dim lstResult As New List(Of String)
      Dim ds As New DataSet
      Try
        ds.ReadXml(aFilename, XmlReadMode.InferSchema)
        For Each dt As DataTable In ds.Tables
          For Each dc As DataColumn In dt.Columns
            lstResult.Add(dc.Caption)
          Next
        Next
      Catch ex As Exception
        MsgBox(ex.Message, MsgBoxStyle.Critical)
      End Try
      Return lstResult
    End Function
  End Class

Get all tables from a XML File

The following function returns all tables in a list of string from a xml file.

Public Shared Function GetTableList(ByVal aFilename As String) As List(Of String)
      If Not File.Exists(aFilename) Then
        Throw New System.IO.FileNotFoundException("File not found!", aFilename)
        Exit Function
      End If
      Dim lstResult As New List(Of String)
      Dim ds As New DataSet
      Try
        ds.ReadXml(aFilename, XmlReadMode.InferSchema)
        For Each dt As DataTable In ds.Tables
          lstResult.Add(dt.TableName)
        Next
      Catch ex As Exception
        MsgBox(ex.Message, MsgBoxStyle.Critical)
      End Try
      Return lstResult
    End Function

VB .NET ReportViewer

Mit der folgende Methoden lässt sich ein Microsoft ReportViewer mit Daten aus einem XML File befüllen und anzeigen.
Den Report an sich kann man bequem im Visual Studio erstellen und abspeichern. Databindings und dergleichen sind nicht notwendig.

Die untere Methode ermöglicht das Speichern eines Reports als PDF-Datei.

Public Sub ShowReportViewer(ByVal aXMLFilename As String, ByVal aReportFilename As String, _
                              ByVal aDatasetName As String, ByVal aTableName As String, _
                              ByVal aDataTableIndex As Integer, _
                              Optional ByVal DoSaveReport As Boolean = False, Optional ByVal SaveReportAsFilename As String = "")
 
    If Not System.IO.File.Exists(aXMLFilename) Then
      MsgBox(String.Format("Die XML Datenquelle ""{0}"" wurde nicht gefunden!", aXMLFilename), MsgBoxStyle.Critical)
      Exit Sub
    End If
 
    If Not System.IO.File.Exists(aReportFilename) Then
      MsgBox(String.Format("Die Report Vorlage ""{0}"" wurde nicht gefunden!", aReportFilename), MsgBoxStyle.Critical)
      Exit Sub
    End If
 
    ' Create new DataSet and load Data from aXMLFilename into it
    Dim ds As New DataSet()
    ds.DataSetName = aDatasetName
    ds.ReadXml(aXMLFilename)
 
    ' Create Form
    Dim frm As New Windows.Forms.Form
    frm.StartPosition = Windows.Forms.FormStartPosition.CenterParent
    frm.Height = 400
    frm.Width = 400
 
    ' Create Report Data Source
    ' The most important part here is aDatasetName & "_" & aTablename
    Dim rds As New Microsoft.Reporting.WinForms.ReportDataSource(aDatasetName & "_" & aTableName, ds.Tables(aDataTableIndex))
 
    ' Create ReportViewer
    Dim rv As New Microsoft.Reporting.WinForms.ReportViewer
    rv.Dock = Windows.Forms.DockStyle.Fill
 
    ' Add ReportViewer to Form
    frm.Controls.Add(rv)
 
    ' Load Report Definition File
    Dim fs As New System.IO.FileStream(aReportFilename, IO.FileMode.Open)
    rv.LocalReport.LoadReportDefinition(fs)
 
    ' Add Report Data Source
    rv.LocalReport.DataSources.Clear()
    rv.LocalReport.DataSources.Add(rds)
    rv.RefreshReport()
    ' Save Report as File?
    If DoSaveReport Then
      SaveReport(rv.LocalReport, SaveReportAsFilename)
    End If
    ' Finally Show Form
    frm.ShowDialog()
  End Sub
Public Sub SaveReport(ByVal aLocalReport As Microsoft.Reporting.WinForms.LocalReport, ByVal aFilename As String, Optional ByVal aRenderFormat As String = "PDF")
    ' Todo: Check if aFilename already exists and prompt user to overwrite/skip
    Dim warnings As Microsoft.Reporting.WinForms.Warning() = Nothing
    Dim streamids As String() = Nothing
    Dim mimeType As String = Nothing
    Dim encoding As String = Nothing
    Dim extension As String = Nothing
    Dim bytes As Byte()
    Try
      bytes = aLocalReport.Render(aRenderFormat, Nothing, mimeType, encoding, extension, streamids, warnings)
      Dim fs As New IO.FileStream(aFilename, System.IO.FileMode.Create)
      fs.Write(bytes, 0, bytes.Length)
      fs.Close()
    Catch ex As Exception
      ' AddToLog(ex.Message)
    End Try
  End Sub

Beispielaufruf:

Public Sub Test()
    ShowReportViewer("C:\test.xml", "C:\repMain.rdlc", "NewDataSet", "Table", 0, True, "C:\test.pdf")
  End Sub

Ergänzung vom 17.02.2012:

Hier noch ein Beispiel, wie man einen Microsoft Report als eingebettete Ressource verwendet, den Report also nicht per Dateinamen angeben muss:

Dim bs As New System.Windows.Forms.BindingSource
    bs.DataMember = "dtReportDetails"
    bs.DataSource = Me
 
    Dim rds As New Microsoft.Reporting.WinForms.ReportDataSource("dsReport_dtReportDetails", bs)
 
    Dim rvMain As New Microsoft.Reporting.WinForms.ReportViewer
    rvMain.LocalReport.DataSources.Add(rds)
    rvMain.LocalReport.ReportEmbeddedResource = "Controlling.Neukunden.repSantaFuck.rdlc"
    rvMain.Dock = System.Windows.Forms.DockStyle.Fill
    rvMain.TabIndex = 0
    rvMain.RefreshReport()
 
    Dim frm As New Windows.Forms.Form
    frm.Controls.Add(rvMain)
    frm.StartPosition = Windows.Forms.FormStartPosition.CenterScreen
    frm.WindowState = Windows.Forms.FormWindowState.Maximized
    frm.ShowDialog() Read More

Howto: Zufallsfarbe unter Visual Basic .NET

Um unter Visual Basic .NET eine Zufallsfarbe zu erhalten, genügt folgender (beinahe) Einzeiler:

Public Shared Function GetRandomColor() As Color
    Dim col As New Color
    Static rnd As Random
    If rnd Is Nothing Then rnd = New Random
    col = Color.FromArgb(rnd.Next(1, 255), rnd.Next(1, 255), rnd.Next(1, 255))
    Return col
  End Function
← Zurück  1  4 5 6