Get header from SQL query VB.NET

Question

1.00/5 (1 vote)

See more:

Hi, I try to get data from sql server and export to csv file. But CSV file does not like as it should. And missing headers. Can you help me please?

What I have tried:

Private Sub DBExecuteQueryWriteToFile(ByVal sender As System.Object, ByVal e As System.EventArgs, Optional ByVal Delimiter As String = ",") Handles ziskat_btn.Click

       Dim SqlCommand As String
       Dim filePath As String = "C:\Users\cahafi\Desktop\vbexcel.csv"
       'Dim csvFileWriter = New StreamWriter(filePath, False, Encoding.GetEncoding("Windows-1250"))
       SqlCommand = ("SELECT TabPrikazMzdyAZmetky.IDPracoviste=VPrikazMzdyAZmetkyPracoviste.ID   LEFT OUTER JOIN TabKmenZbozi VPrikazMzdyAZmetkyKmenZbozi WITH(NOLOCK) ON TabPrikazMzdyAZmetky.IDTabKmen=VPrikazMzdyAZmetkyKmenZbozi.ID   LEFT OUTER JOIN TabPrikaz VPrikazMzdyAZmetkyPrikaz WITH(NOLOCK) ON VPrikazMzdyAZmetkyPrikaz.ID=TabPrikazMzdyAZmetky.IDPrikaz   LEFT OUTER JOIN TabCisZam VPrikazMzdyAZmetkyZamestnanec WITH(NOLOCK) ON TabPrikazMzdyAZmetky.Zamestnanec=VPrikazMzdyAZmetkyZamestnanec.ID   LEFT OUTER JOIN TabCSmeny VPrikazMzdyAZmetkySmena WITH(NOLOCK) ON VPrikazMzdyAZmetkySmena.ID=TabPrikazMzdyAZmetky.IDSmeny   LEFT OUTER JOIN TabCZavad VMzdyAZmetkyCZavad WITH(NOLOCK) ON VMzdyAZmetkyCZavad.ID=TabPrikazMzdyAZmetky.IDZavady   LEFT OUTER JOIN  TabPrikazMzdyAZmetky_EXT WITH(NOLOCK) ON TabPrikazMzdyAZmetky_EXT.ID=TabPrikazMzdyAZmetky.ID WHERE ((TabPrikazMzdyAZmetky.TypMzdy<>2)AND(TabPrikazMzdyAZmetky.DatPorizeni>'1.1.2019')AND(TabPrikazMzdyAZmetky.DatPorizeni<'" + Label1.Text + "') AND(VPrikazMzdyAZmetkyKmenZbozi.SkupZbo<>N'VYL'))AND(VPrikazMzdyAZmetkyKmenZbozi.SKP NOT LIKE N'%mohelnice%')AND(VPrikazMzdyAZmetkyKmenZbozi.SKP NOT LIKE N'%Mohelnice%')AND(VPrikazMzdyAZmetkyKmenZbozi.SkupZbo NOT LIKE N'%PMN%')ORDER BY TabPrikazMzdyAZmetky.DatPorizeni DESC")
       Dim bufferSize = 1024
       Dim cmd = New SqlCommand(SqlCommand, conn)

       conn.Open()

       Using FileObject As New FileStream(filePath, FileMode.OpenOrCreate, FileAccess.Write, FileShare.None, bufferSize)
           Using StreamWriterObj As New StreamWriter(FileObject, Encoding.GetEncoding("Windows-1250"))
               Using Reader As SqlDataReader = cmd.ExecuteReader()
                   Dim FieldCount As Integer = Reader.FieldCount
                   Do While Reader.Read()
                       StreamWriterObj.Write(Reader.Item(0))
                       For i As Integer = 0 To FieldCount - 1
                           StreamWriterObj.Write(Delimiter)
                           StreamWriterObj.Write(Reader.Item(i))
                       Next
                       StreamWriterObj.WriteLine()
                   Loop

               End Using
           End Using
       End Using
   End Sub

Posted 13-Sep-19 6:51am

Member 13711215

Updated 13-Sep-19 8:46am

Add a Solution

Comments

Richard Deeming 13-Sep-19 12:54pm

Your code is vulnerable to SQL Injection[^]. NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]

Richard Deeming 13-Sep-19 12:55pm

And your file is missing headers because you never write any headers to the file.

Member 13711215 13-Sep-19 12:57pm

And how can I write headers?

2 solutions

Solution 1

See example here: C# and SQL Server – Exporting Data - Stuart's Computing Corner[^]

Posted 13-Sep-19 7:12am

RickZeeland

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

MadMyche · Accepted Answer · 2019-09-13T08:47:00

Solution 2

Avoid SQL Injection. There is no excuse as this was discovered over 20 years ago. Basically you need to drop a placeholder into the original query and add a parameter for that placeholder to the command object

SQL

-- AND (TabPrikazMzdyAZmetky.DatPorizeni<'" + Label1.Text + "')
-- replace with 
   AND (TabPrikazMzdyAZmetky.DatPorizeni<@Label1)

and

VB

Dim cmd = New SqlCommand(SqlCommand, conn)
' add new line
cmd.Parameters.AddWithValue("@Label1", Label1.Text)

file does not like as it should.
It looks like your first column may be written twice

VB

StreamWriterObj.Write(Reader.Item(0))		' column 0
For i As Integer = 0 To FieldCount - 1
	StreamWriterObj.Write(Delimiter)	' column 0 again when i = 0
	StreamWriterObj.Write(Reader.Item(i))

... and missing headers
Only 2 problems with this; CSV files actually do not have headers, but are typically just the first line written into the text file- the problem is that you never write that line to the file. Easy fix; before you go throught the loop of all the values, run your For i loop across the reader and use the GetName(i) property for each column

Posted 13-Sep-19 8:47am

MadMyche

Comments

Member 13711215 16-Sep-19 6:14am

Thank you, my code:

Try
Using FileObject As New FileStream(filePath, FileMode.OpenOrCreate, FileAccess.Write, FileShare.None, bufferSize)
Using StreamWriterObj As New StreamWriter(FileObject, Encoding.GetEncoding("Windows-1250"))
Using Reader As SqlDataReader = cmd.ExecuteReader()
Dim FieldCount As Integer = Reader.FieldCount - 1
StreamWriterObj.WriteLine(Reader.GetName(0) + ";" + Reader.GetName(1) + ";" + Reader.GetName(2) + ";" + Reader.GetName(3) + ";" + Reader.GetName(4) + ";" + Reader.GetName(5) + ";" + Reader.GetName(6) + ";" + Reader.GetName(7) + ";" + Reader.GetName(8) + ";" + Reader.GetName(9) + ";" + Reader.GetName(10) + ";" + Reader.GetName(11) + ";" + Reader.GetName(12) + ";" + Reader.GetName(13) + ";" + Reader.GetName(14) + ";" + Reader.GetName(15) + ";" + Reader.GetName(16) + ";" + Reader.GetName(17) + ";" + Reader.GetName(18) + ";" + Reader.GetName(19) + ";" + Reader.GetName(20) + ";" + Reader.GetName(21) + ";" + Reader.GetName(22) + ";" + Reader.GetName(23) + ";" + Reader.GetName(24) + ";" + Reader.GetName(25) + ";" + Reader.GetName(26) + ";" + Reader.GetName(27) + ";" + Reader.GetName(28) + ";")
Do While Reader.Read()
StreamWriterObj.Write(Reader.Item(0))

For i As Integer = 0 To FieldCount - 1
StreamWriterObj.Write(Delimiter)
StreamWriterObj.Write(Reader.Item(i))
Next
StreamWriterObj.WriteLine()
Loop

End Using
End Using
End Using
'MessageBox.Show("Export done successfully!")
Catch ex As Exception
mail(sender, e)
MessageBox.Show(ex.Message, "Warning", MessageBoxButtons.OK)
End Try

MadMyche 16-Sep-19 7:09am

and what has changed; in your code and in the results?