Hi Everyone,
I have a SQL Database which is not relational database. It was designed and created long time ago and I need to export some data to CSV file.
Here is my code:
Imports System.Data.SqlClient
Imports System
Imports System.Data
Imports System.IO
Imports System.Data.OleDb
Public Class frmFirstExp
Public dtResult As New DataTable
Private Sub btnExport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExport.Click
Dim i As Integer
Dim j As Integer
Dim SQL As String = " "
Dim con As SqlConnection
Dim ds As New DataSet
Dim dsRec As New DataSet
Dim AD As SqlDataAdapter
Dim ADrec As SqlDataAdapter
Dim dtRec As New DataTable
Dim dtRecAdd As New DataTable
SQL = "Select distinct ID from AddInItems "
con = New SqlConnection("Data Source=TESTSHAREPOINT;Initial Catalog=Suncorp_062608;User ID=fShang;Password=12345; ")
con.Open()
AD = New SqlDataAdapter(SQL, con)
AD.Fill(ds, "tblAllRec ")
CreateDataTable()
For i = 0 To ds.Tables(0).Rows.Count - 1
ADrec = New SqlDataAdapter(SQLString(ds.Tables(0).Rows(i).Item(0).ToString), con)
ADrec.Fill(dtRec)
Dim workRow As DataRow
workRow = dtResult.NewRow()
workRow(0) = dtRec.Rows(0).Item(0).ToString
workRow(1) = dtRec.Rows(0).Item(1).ToString
workRow(2) = dtRec.Rows(0).Item(2).ToString
workRow(3) = dtRec.Rows(0).Item(3).ToString
workRow(4) = dtRec.Rows(0).Item(4).ToString
workRow(5) = dtRec.Rows(0).Item(5).ToString
workRow(6) = dtRec.Rows(0).Item(6).ToString
workRow(7) = dtRec.Rows(0).Item(7).ToString
workRow(8) = dtRec.Rows(0).Item(8).ToString
workRow(9) = dtRec.Rows(0).Item(9).ToString
workRow(10) = dtRec.Rows(0).Item(10).ToString
workRow(11) = dtRec.Rows(0).Item(11).ToString
workRow(12) = dtRec.Rows(0).Item(12).ToString
workRow(13) = dtRec.Rows(0).Item(13).ToString
workRow(14) = dtRec.Rows(0).Item(14).ToString
workRow(15) = dtRec.Rows(0).Item(15).ToString
workRow(16) = dtRec.Rows(0).Item(16).ToString
workRow(17) = dtRec.Rows(0).Item(17).ToString
workRow(18) = dtRec.Rows(0).Item(18).ToString
workRow(19) = dtRec.Rows(0).Item(19).ToString
workRow(20) = dtRec.Rows(0).Item(20).ToString
workRow(21) = dtRec.Rows(0).Item(21).ToString
workRow(22) = dtRec.Rows(0).Item(22).ToString
workRow(23) = dtRec.Rows(0).Item(23).ToString
workRow(24) = dtRec.Rows(0).Item(24).ToString
workRow(25) = dtRec.Rows(0).Item(25).ToString
workRow(26) = dtRec.Rows(0).Item(26).ToString
workRow(27) = dtRec.Rows(0).Item(27).ToString
workRow(28) = dtRec.Rows(0).Item(28).ToString
workRow(29) = dtRec.Rows(0).Item(29).ToString
workRow(30) = dtRec.Rows(0).Item(30).ToString
workRow(31) = dtRec.Rows(0).Item(31).ToString
workRow(32) = dtRec.Rows(0).Item(32).ToString
workRow(33) = dtRec.Rows(0).Item(33).ToString
workRow(34) = dtRec.Rows(0).Item(34).ToString
workRow(35) = dtRec.Rows(0).Item(35).ToString
workRow(36) = dtRec.Rows(0).Item(36).ToString
workRow(37) = dtRec.Rows(0).Item(37).ToString
dtResult.Rows.Add(workRow)
Next
Dim dt As DataTable = ds.Tables(0)
Using Write As New System.IO.StreamWriter("C:\Documents and Settings\shosseinirad\My Documents\Suncorp\FirstExport.csv ")
clsImpExp.ProduceCSV(dtResult, Write, True)’Exporting Data
End Using
End Sub
Function SQLString(ByVal RefID As String) As String
Dim Sql As String
Sql = "select A.ID as ID, IsDeleted, CreationDate, LastModified, "
Sql &= "(select t4.Value from "
Sql &= "tabs t1 join Sections t2 on (t2.FK_TabID=t1.ID) "
Sql &= "join Fields t3 on (t3.FK_SectionID=t2.ID) "
Sql &= "join StringValues t4 on (t4.FK_FieldID=t3.ID) "
Sql &= "where t1.name='Sale Info' and t2.Name='Property Type' and t3.Label='Property Type' "
Sql &= "and t4.FK_ReferenceID=A.ID "
Sql &= ") as Property_Type, "
Sql &= "where t1.name='Sale Info' and t2.Name='Property Type' and t3.Label='Subtype' "
Sql &= ") as Subtype, "
Sql &= "where t1.name='Sale Info' and t2.Name='Location Address' and t3.Label='Address' "
Sql &= ") as Address, "
Sql &= "where t1.name='Sale Info' and t2.Name='Location Address' and t3.Label='Development Name' "
Sql &= ") as Development_Name, "
Sql &= "where t1.name='Sale Info' and t2.Name='Location Address' and t3.Label='Property Name' "
Sql &= ") as Property_Name, "
Sql &= "where t1.name='Sale Info' and t2.Name='Location Address' and t3.Label='City' "
Sql &= ") as City, "
Sql &= "where t1.name='Sale Info' and t2.Name='Location Address' and t3.Label='State/Province' "
Sql &= ") as State_Province, "
Sql &= "where t1.name='Sale Info' and t2.Name='Location Address' and t3.Label='Location Description' "
Sql &= ") as Location_Description, "
Sql &= "where t1.name='Sale Info' and t2.Name='Location Address' and t3.Label='Township' "
Sql &= ") as Township, "
Sql &= "where t1.name='Property ID' and t2.Name='Public Records' and t3.Label='Legal Description' "
Sql &= ") as Legal_Description, "
Sql &= "where t1.name='Land Related' and t2.Name='Zoning Information' and t3.Label='Zoning Code' "
Sql &= ") as Zoning_Code, "
Sql &= "where t1.name='Land Related' and t2.Name='Zoning Information' and t3.Label='Zoning Jurisdiction' "
Sql &= ") as Zoning_Jurisdiction, "
Sql &= "where t1.name='Land Related' and t2.Name='Zoning Information' and t3.Label='Zoning Description' "
Sql &= ") as Zoning_Description, "
Sql &= "isnull((select t4.Value from "
Sql &= "join DecimalValues t4 on (t4.FK_FieldID=t3.ID) "
Sql &= "where t1.name='Transaction Data' and t2.Name='General Information' and t3.Label='Sale Price' "
Sql &= "),0) as Sale_Price, "
Sql &= "convert(varchar(12),(select t4.Value from "
Sql &= "join DateTimeValues t4 on (t4.FK_FieldID=t3.ID) "
Sql &= "where t1.name='Transaction Data' and t2.Name='General Information' and t3.Label='Contract Date' "
Sql &= "),9) as Contract_Date, "
Sql &= "where t1.name='Transaction Data' and t2.Name='General Information' and t3.Label='Grantor / Seller' "
Sql &= ") as Grantor_Seller, "
Sql &= "where t1.name='Transaction Data' and t2.Name='General Information' and t3.Label='Grantee / Buyer' "
Sql &= ") as Grantee_Buyer, "
Sql &= "where t1.name='Transaction Data' and t2.Name='General Information' and t3.Label='Assessed Value - Total ($)' "
Sql &= "),0) as A_Value_Total, "
Sql &= "where t1.name='Transaction Data' and t2.Name='General Information' and t3.Label='Assessed Value - Land ($)' "
Sql &= "),0) as A_Value_Land, "
Sql &= "isnull((SELECT t4.[Value] "
Sql &= "FROM dbo.Tabs t1 INNER JOIN "
Sql &= "dbo.Sections t2 ON t2.FK_TabID = t1.ID INNER JOIN "
Sql &= "dbo.Fields t3 ON t3.FK_SectionID = t2.ID INNER JOIN "
Sql &= "dbo.DecimalValues t4 ON t4.FK_FieldID = t3.ID "
Sql &= "WHERE (t4.FK_ReferenceID = A.ID) AND (t3.Label = 'Assessed Value - Improvements ($)') AND (t2.Name = 'General Information') AND "
Sql &= "(t1.Name = 'Transaction Data') "
Sql &= "),0) as A_Value_Improve, "
Sql &= "where t1.name='Transaction Data' and t2.Name='General Information' and t3.Label='Cash Equivalent Sale Price ($)' "
Sql &= "),0) as C_E_Sale_Price, "
Sql &= "where t1.name='Transaction Data' and t2.Name='General Information' and t3.Label='Total Tax Amount' "
Sql &= "),0) as Tax_Amount, "
Sql &= "where t1.name='Building' and t2.Name='Building Information' and t3.Label='Construction Type' "
Sql &= ") as Construction_Type, "
Sql &= "where t1.name='Building' and t2.Name='Building Information' and t3.Label='Number of Units' "
Sql &= ") as Number_of_Units, "
Sql &= "where t1.name='Building' and t2.Name='Building Information' and t3.Label='Property Condition' "
Sql &= ") as Property_Condition, "
Sql &= "where t1.name='Building' and t2.Name='Building Information' and t3.Label='Construction Description' "
Sql &= ") as Construction_Description, "
Sql &= "where t1.name='Building' and t2.Name='Building Information' and t3.Label='Year Built' "
Sql &= ") as Year_Built, "
Sql &= "(SELECT t4.[Value] "
Sql &= "dbo.IntegerValues t4 ON t4.FK_FieldID = t3.ID "
Sql &= "WHERE (t4.FK_ReferenceID =A.ID) AND (t3.Label = 'Gross Building Area (SF)') AND (t2.Name = 'Gross Building Area') AND (t1.Name = 'Building Size') "
Sql &= ") as Gross_Building_Area_SF, "
Sql &= "WHERE (t4.FK_ReferenceID =A.ID) AND (t3.Label = 'Usable Area (SF)') AND (t2.Name = 'Usable Area') AND (t1.Name = 'Building Size') "
Sql &= "),0) as Usable_Area_SF, "
Sql &= "(SELECT DISTINCT t4.[Value] "
Sql &= "FROM dbo.Sections t2 INNER JOIN "
Sql &= "dbo.Fields t3 ON t2.ID = t3.FK_SectionID INNER JOIN "
Sql &= " dbo.Tabs t1 ON t2.FK_TabID = t1.ID INNER JOIN "
Sql &= " dbo.StringValues t4 ON t3.ID = t4.FK_FieldID "
Sql &= "WHERE (t3.Label = 'Gross Land Area (Acres)') AND (t2.Name = 'Land Area Informaton') AND (t1.Name = 'Land Related') and (A.ID=t4.FK_ReferenceID) "
Sql &= ") as Grs_Land_Area_AC, "
Sql &= "WHERE (t4.FK_ReferenceID =A.ID) AND (t3.Label = 'Gross Land Area (SF)') AND (t2.Name = 'Land Area Informaton') AND (t1.Name = 'Land Related') "
Sql &= ") as Grs_Land_Area_SF, "
Sql &= "WHERE (t4.FK_ReferenceID=A.ID) AND (t3.Label = 'Shape') AND (t2.Name = 'Land Area Informaton') AND (t1.Name = 'Land Related') "
Sql &= ") as Shape, "
Sql &= " dbo.Fields t3 ON t2.ID = t3.FK_SectionID INNER JOIN "
Sql &= "dbo.Tabs t1 ON t2.FK_TabID = t1.ID INNER JOIN "
Sql &= "WHERE (t4.FK_ReferenceID =A.ID) AND (t3.Label = 'Frontage (Feet)') AND (t2.Name = 'Land Area Informaton') AND (t1.Name = 'Land Related') "
Sql &= ") as Frontage_Feet, "
Sql &= "WHERE (t4.FK_ReferenceID =A.ID) AND (t3.Label = 'Depth (Feet)') AND (t2.Name = 'Land Area Informaton') AND (t1.Name = 'Land Related') "
Sql &= ") as Depth_Feet, "
Sql &= "WHERE (t4.FK_ReferenceID =A.ID) AND (t3.Label = 'Lot Size Dimensions') AND (t2.Name = 'Land Area Informaton') AND (t1.Name = 'Land Related') "
Sql &= ") as Lot_Size_Dimensions "
Sql &= "from AddinItems A "
Sql &= "where(A.ID=' " & RefID & "') "
Sql &= "order by Property_Type "
SQLString = Sql
End Function
Private Sub CreateDataTable()
dtResult.Columns.Add(New DataColumn("ID"))
dtResult.Columns.Add(New DataColumn("IsDeleted"))
dtResult.Columns.Add(New DataColumn("CreationDate"))
dtResult.Columns.Add(New DataColumn("LastModified"))
dtResult.Columns.Add(New DataColumn("Property_Type"))
dtResult.Columns.Add(New DataColumn("Subtype"))
dtResult.Columns.Add(New DataColumn("Address"))
dtResult.Columns.Add(New DataColumn("Development_Name"))
dtResult.Columns.Add(New DataColumn("Property_Name"))
dtResult.Columns.Add(New DataColumn("City"))
dtResult.Columns.Add(New DataColumn("State_Province"))
dtResult.Columns.Add(New DataColumn("Location_Description"))
dtResult.Columns.Add(New DataColumn("Township"))
dtResult.Columns.Add(New DataColumn("Legal_Description"))
dtResult.Columns.Add(New DataColumn("Zoning_Code"))
dtResult.Columns.Add(New DataColumn("Zoning_Jurisdiction"))
dtResult.Columns.Add(New DataColumn("Zoning_Description"))
dtResult.Columns.Add(New DataColumn("Sale_Price"))
dtResult.Columns.Add(New DataColumn("Contract_Date"))
dtResult.Columns.Add(New DataColumn("Grantor_Seller"))
dtResult.Columns.Add(New DataColumn("Grantee_Buyer"))
dtResult.Columns.Add(New DataColumn("A_Value_Total"))
dtResult.Columns.Add(New DataColumn("A_Value_Land"))
dtResult.Columns.Add(New DataColumn("A_Value_Improve"))
dtResult.Columns.Add(New DataColumn("C_E_Sale_Price"))
dtResult.Columns.Add(New DataColumn("Tax_Amount"))
dtResult.Columns.Add(New DataColumn("Construction_Type"))
dtResult.Columns.Add(New DataColumn("Number_of_Units"))
dtResult.Columns.Add(New DataColumn("Property_Condition"))
dtResult.Columns.Add(New DataColumn("Construction_Description"))
dtResult.Columns.Add(New DataColumn("Year_Built"))
dtResult.Columns.Add(New DataColumn("Gross_Building_Area_SF"))
dtResult.Columns.Add(New DataColumn("Usable_Area_SF"))
dtResult.Columns.Add(New DataColumn("Grs_Land_Area_AC"))
dtResult.Columns.Add(New DataColumn("Grs_Land_Area_SF"))
dtResult.Columns.Add(New DataColumn("Shape"))
dtResult.Columns.Add(New DataColumn("Frontage_Feet"))
dtResult.Columns.Add(New DataColumn("Depth_Feet"))
dtResult.Columns.Add(New DataColumn("Lot_Size_Dimensions"))
End Class
I wander if there is a faster way to do this. This process takes about 6 minutes for 431 records but this is just a part of real database. There are 10000 records in real database.
Is there any other way to do that?