Thread: moving dataset in to a database

OK. First, take a read of DW2 link in my signature, section "Creating a Simple Data App". Also read DNU link.. It's a small tip you need to know

Once you know how to do data access properly, as in DW2, you can:

Read your text file into the strongly typed datatable, instead of a weakly typed one (reuse your existing code, just change some names, mostly)
Upload the data to the database with one line of code: myTableAdapter.Update(myDataTable)

can we read a text file in to a strongly typed data set?? I know tht its possible with a XML file.. but... well you should be well informed.. so plzzzz gimme a link.. to read a delimited text file in to a strongly typed data set.. I am guessing tht this dataset should be created by using the schema from the databse.. nd not the text file..

Originally Posted by liwaste

can we read a text file in to a strongly typed data set?? I know tht its possible with a XML file.. but... well you should be well informed.. so plzzzz gimme a link.. to read a delimited text file in to a strongly typed data set.. I am guessing tht this dataset should be created by using the schema from the databse.. nd not the text file..

I have 3 ways:

Either use the Microsoft Jet driver in text mode, with a schema.ini that lays out the columns, making the column names the same as in the typed set, then a simple select query can be used to fill the table

Or use a bit of software I wrote called MutatingReader, that performs on-the-fly find and replace on a stream of data (rather like unix SED) which I use in many cases (of my own code) to effectively convert a text file into dataset-compatible XML (i have a tool that helps, but this method is NOT easy to set up)

Or read the file in classic style, split it yourself (or use the .net provided delimited file reading routiens), put the rows into the table yourself, and then upload it..

Originally Posted by liwaste

can we read a text file in to a strongly typed data set??

ps; of course - you already did it with a weakly typed one according to your first post (strongly typed should be no different)

but the fill method of the table adapter only takes a strongly typed data set of the particular data table type.. isnt it.. but right now I will be having a dataset with column names as same as the data base table.. will i be able to pass this dataset in to the fill method? plese give me a code sample so tht i can understand better...

thank you soo much for your replies...

but a strongly typed table is a table.. ergo my assertion is that you use your existing weakly typed code that fills a datatable, to fill a strongly typed table:


Dim strongDT as New StrongTypedDataTable

myWeakAdapterReadFromFile.Fill(strongDT)

myStrongTA.Update(strongDT)


Youre implying you already did the weak adapter part

well.. I did manage to get my weak dataset to get in to the database.. hehe

here is my code..

Code:

Dim dbConstr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=.\App_Data\prasanna.mdb;"
Dim dbConn As New OleDb.OleDbConnection(dbConstr)



' dbDS.Tables(0) = dt.Copy

Try
    'set the insert command

    Dim strCmd As String = "INSERT INTO [tbl_gis] (id_polygon,clim_acronym,soil_acronym,lu_acronym,lusub_acronym,area)" & _
                                         "VALUES ( @id_polygon,@clim_acronym, @soil_acronym, @lu_acronym, @lusub_acronym, @area)"
    Dim insertCommand As New OleDb.OleDbCommand(strCmd, dbConn)

    insertCommand.Parameters.Add("@id_polygon", OleDb.OleDbType.VarChar, 50, "polygonID")
    insertCommand.Parameters.Add("@clim_acronym", OleDb.OleDbType.VarChar, 20, "climate")
    insertCommand.Parameters.Add("@soil_acronym", OleDb.OleDbType.VarChar, 20, "soil")
    insertCommand.Parameters.Add("@lu_acronym", OleDb.OleDbType.VarChar, 20, "lutype")
    insertCommand.Parameters.Add("@lusub_acronym", OleDb.OleDbType.VarChar, 20, "lusubtype")
    insertCommand.Parameters.Add("@area", OleDb.OleDbType.VarChar, 50, "area")

    ' Dim trans As OleDb.OleDbTransaction
    'dbConn.Open()
    'trans = dbConn.BeginTransaction
    Dim adapter As New OleDb.OleDbDataAdapter()
    adapter.InsertCommand = insertCommand
    adapter.TableMappings.Add("tbl_gis", ds.Tables(0).TableName.ToString)

    adapter.Update(ds, "tbl_gis")
    'trans.Commit()
    dbConn.Close()
Catch ex As Exception
    MessageBox.Show(ex.ToString)
    DisplayOleDbErrorCollection(ex)
End Try
Dim dbAdapter As New OleDb.OleDbDataAdapter("Select * from tbl_gis", dbConn)
Dim dbDS As New DataSet
dbAdapter.Fill(dbDS, "tbl_gis")
DataGridView2.DataSource = dbDS.Tables(0)

As you can see all the parameters are text, but actully the polygonID and the area are integers. Is there a way to cast the parameter? I know I have used something like this before, but I forgot..

And another thing.. I am able to see the new data in the datagridview2, but not in the database itself. Do I have to commit the transaction? If yes, then please show me how..

Appreciate your quick replies..

Originally Posted by liwaste

As you can see all the parameters are text, but actully the polygonID and the area are integers. Is there a way to cast the parameter?

Well you declare the parameter type to be VarChar. I recommend you declare it to be something more numeric and then when reading the numerical string out of the file, use:
Convert.ToInt32(str)

And another thing.. I am able to see the new data in the datagridview2, but not in the database itself.

Read the DNU link in my signature?

I am back
thanks for your help.. I am able to move the data set in to the database with the following codeTry
'set the insert command

Dim strCmd As String = "INSERT INTO [tbl_gis_temp] (clim_acronym,soil_acronym,lu_acronym,lusub_acrony m,area)" & _
"VALUES ( @clim_acronym, @soil_acronym, @lu_acronym, @lusub_acronym, @area)"
Dim insertCommand As New OleDb.OleDbCommand(strCmd, dbConn)

'insertCommand.Parameters.Add("@id_polygon", OleDb.OleDbType.Integer, 50, "polygonID")
insertCommand.Parameters.Add("@clim_acronym", OleDb.OleDbType.VarChar, 20, "climate")
insertCommand.Parameters.Add("@soil_acronym", OleDb.OleDbType.VarChar, 20, "soil")
insertCommand.Parameters.Add("@lu_acronym", OleDb.OleDbType.VarChar, 20, "lutype")
insertCommand.Parameters.Add("@lusub_acronym", OleDb.OleDbType.VarChar, 20, "lusubtype")
insertCommand.Parameters.Add("@area", OleDb.OleDbType.Double, 50, "area")

'Dim trans As OleDb.OleDbTransaction
dbConn.Open()

'trans = dbConn.BeginTransaction

Dim adapter As New OleDb.OleDbDataAdapter("Select * from " & strFileName, dbConn)
'Dim As New OleDb.OleDbDataAdapter()
adapter.InsertCommand = insertCommand
'adapter.InsertCommand.Transaction = trans
adapter.TableMappings.Add("tbl_gis_temp", ds.Tables(0).TableName.ToString)
adapter.Update(ds, "tbl_gis_temp")

'trans.Commit()
dbConn.Close()
Catch ex As Exception
MessageBox.Show(ex.ToString)
'DisplayOleDbErrorCollection(ex)
End Try

but I have more than 400 thousand records in my data set.. nd this process takes 15 mins to complete.. please help me out.. suggest me a faster way to to the same .. we are not using SQL server so was not able to use sql bulk copy.. hope there is some way