moving dataset in to a database

[liwaste]

Hello all.. I have a dataset .. with one datatable.. which i generated by reading a textfile..

Now I want to move this dataset /datatable in to an access database table

this table is already built with all constraints in place..

I want to move it as a whole.. as apposed to loopin through the dataset..

there is one single catch.. the order of the datacolumns in the dataset do not match with the databse's column..

please help me out.. i have been searching for this for a loooong time..

thanks

Prasanna

 

[cjard]

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)

 

[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..

 

[cjard]

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..

 

[cjard]

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)

 

[liwaste]

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...

 

[cjard]

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

 

[liwaste]

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

here is my 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..

 

[cjard]

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?

 

[liwaste]

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_acronym,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

 

[jmcilhinney]

Are you saying that you're saving 400,000 new records to the database? If so then of course it's going to take time.

If only a small proportion of the total records in the DataTable are being saved then call GetChanges on the DataTable first, then pass the result your Update call. Just note that you will have to explicitly call AcceptChanges on the original DataTable if the update is successful.

 

[liwaste]

yes I am adding 400,000 new records..

its sad tht i cant even have a batch update.. on a oledb adapter..

Is there a way to directly parse a text file in to acess data base.. so tht i dont have to put it in a dataset in the frst case..

thanks for your reply..

hope I get this thing working..

 

[jmcilhinney]

You can do great things with SQL Server in VB.NET because Microsoft have created a great-deal of SQL Server-specific functionality. There is no Access-specific functionality. The OleDb namespace any OLEDB data source, not just Access. Also, the Jet OLEDB provider supports many more data sources than just Access. If you want the best performance out of an Access database then use the Access application. Maybe it offers the functionality to import a text file directly but I'm not aware of it.

 

[liwaste]

Thanks for your replies.. well I kinda figured out a way to pull in the text file directly in to the database..

here is the code

Try
Dim dbConstr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=..\App_Data\prasanna.mdb;"

Dim conn As New OleDb.OleDbConnection(dbConstr)
conn.Open()
Dim AccessCommand As New System.Data.OleDb.OleDbCommand(" SELECT * INTO [tbl_gis_temp1] FROM [Text;DATABASE=" & strFilePath & ";HDR=Yes;FMT=Delimited].[" & strFileName & "]", conn)

AccessCommand.ExecuteNonQuery()
conn.Close()



Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try

so this works fine for comma delimited files.. i think if it has any other delimiter.. then shuld put in a schema.ini file in there .. will work on it tomorow.. nd it took like 10 seconds its pretty neat.. as far as my application is concerned..

I think I will be askin further questions on concatinating column values in to string..

 

[cjard]

Maybe it offers the functionality to import a text file directly but I'm not aware of it.

I always thought it did.. I'm sure I remember somewhere, a wizard askking how the file was delimited, what headers it had etc