[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 6710
  • Last Modified:

VB.net need to create a temporary Table ( Dataset)

I need to create a temporary Table

I will use a data grid that should be bound to a Temp Table ( Dataset ) which should be saved on the HD
Once i complete  what im doing i should be able to update from temp dataset to the SQL server table ...
0
Vartana
Asked:
Vartana
  • 4
  • 4
  • 3
  • +1
1 Solution
 
DotNetLover_BaanCommented:
Hi Vartana,
here is some example code for you... to give you an idea.

        'Create the table
        Dim Table1 As DataTable
        Table1 = New DataTable

        'Add columns to it
        Table1.Columns.Add("Id", Type.GetType("System.Int32"))
        Table1.Columns.Add("Name", Type.GetType("System.String"))

        'Add rows and fill with data
        Dim EditRow As DataRow
        EditRow = Table1.NewRow()
        EditRow(0) = 1
        EditRow(1) = "John"
        Table1.Rows.Add(EditRow)
        EditRow = Table1.NewRow()
        EditRow(0) = 2
        EditRow(1) = "Jane"
        Table1.Rows.Add(EditRow)

        'Create a dataset and add this table to it
        Dim DS As DataSet
        DS = New DataSet
        DS.Tables.Add(Table1)

So your dataset is ready now... Let me know what else you want to do.
-Baan
0
 
jnhorstCommented:
In your project menu, click Add New Item...  Choose the dataset icon and give your dataset a file name (say test.xsd).  You should see a yellowish design grid with nothing in it.  Leave that showing.  There is a Server Explorer window in the Visual Studio IDE (obviously I am assuming you are using VS).  If you have not done so already, create a connection to your SQL Server database in this window.  You will then have a tree node named "Tables" (as well as others).  Expand this.  With the dataset designer showing, drag the treeview node for the table in question onto the dataset designer.  A datatable will be created as part of the dataset for you.  You should see a box that has the column names and their datatypes from the table.

In your Windows form, go to the Data toolbar and double click the Dataset tool.  A dialog will appear with a dropdown of the datasets in the project.  Pick the one you just created, deselect "Genearte cached stronlgy typed dataset", and click OK.  A tray at the bottom of the form will show the instance of the dataset.  Right click this, select Properties, and rename this something like "dsTest".

You can now place a datagrid on a Windows form and set the DataSource to your this datatable.  The DataSource property should have a dropdown that will show your dsTest dataset.  The grid should automatically get all of the columns in the datatable.

You should then create a stored procedure to Select these records; a stored procedure to insert a record; and a stored procedure to update a record (you can create one to delete a record as well, if needed).  After you do this.  Go back to the Data toolbar and double click the SqlDataAdapter tool.  Follow the wizard by selecting the connection to your database, choose "Use Existing Stored Procedures".  Then select the right stored procedures from your database for the Select, Insert and Update commands (and Delete if you created one).  Click the next button and the wizard will notify you if it was able to create the command, and most importantly, generate the table mappings.

Put a "Load from Database" button on the form.  Here you want to call the Fill method of the data adapter, passing it the data table, to load up the datatable from the database, assuming of course you are connected to your network.

Also put a "Load from Disk" button on the form.  In this one you want to call the dataset's ReadXml() method, passing it the file name.  This will load up the dataset from the file.  Make sure to test for the file's existence to avoid errors the very first time you run the app.

Next, put a "Save to Disk" button on the form somewhere, and in its click event, you can call the dataset's WriteXml() method, which takes a filename as an argument.  This will save the dataset, along with all of the data you entered, in an xml file with the path\name.xml you provide.

Lastly, put a "Update to Database" button on the form.  Code the click event to call the Update() method of the data adapter, again taking the data table as its argument.

When you run the program and are hooked up to the network and want to get data from the database, click the Load from Database button.  Your grid should show the data.  Before closing down, click Save to Disk.  This will save the data in an xml file.  When using the app off-network, just click Load from Disk.  You can update and add records all you like.  When you update a record, the dataset flag's it as updated.  When you insert a record, it is likewise flagged as inserted.  These flags are saved to the disk when you click the Save to Disk button.  When you are on the network again, first click the Load from Disk button and then click the Update to Database button.  The data adapter will recognize the inserted and updated records and execute your update and insert stored procedures for each of these records.

Good luck!

John


0
 
VartanaAuthor Commented:
DotNetLover_Baan

What will happen if the user closes the form, All the data will be lost.
I need to be able to continue working when i reopen the application.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
VartanaAuthor Commented:
jnhorst

Can you please Past full code of such a form or perhaps send me a link to a project like that ?
0
 
jnhorstCommented:
Vartana:

I am leaving the office for the day.  I will get back to this after dinner tonight and try to post something for you to look at.  I will use the Northwind database that comes with SQL Server as my example and you can extrapolate from that.  But it will save the data as long as the user clicks the Save to Disk button to address your concerns above.

John
0
 
DotNetLover_BaanCommented:
ok... then save it to some database. or save all the tables to text files (or to an XML file as John said).

For TextFile:


Sub SaveToFile()
        Dim StreamWR As FileStream
        'Check if the file is there.
        If File.Exists("C:\data.txt") Then
            StreamWR = New FileStream("C:\data.txt", FileMode.Append, FileAccess.Write)
        Else
            StreamWR = New FileStream("C:\data.txt", FileMode.CreateNew, FileAccess.Write)
        End If
        Dim FileWriter As New StreamWriter(StreamWR)
        Dim dr As DataRow
        Dim i As Int32
        For Each dr In ds.Tables("Table1").Rows
            For i = 0 To ds.Tables("Table1").Columns.Count - 1
                FileWriter.Write(dr(i) & vbTab)
            Next
            FileWriter.WriteLine("")
        Next
        FileWriter.Close()
        StreamWR.Close()
End Sub

-Baan
0
 
VartanaAuthor Commented:
You gave me the save what about the read my friend.
0
 
DotNetLover_BaanCommented:
right on it dude...
0
 
DotNetLover_BaanCommented:
but change this part..

        If File.Exists("C:\data.txt") Then
            StreamWR = New FileStream("C:\data.txt", FileMode.Append, FileAccess.Write)
        Else
            StreamWR = New FileStream("C:\data.txt", FileMode.CreateNew, FileAccess.Write)
        End If

intead of the whole if condition... just use...
            StreamWR = New FileStream("C:\data.txt", FileMode.Create, FileAccess.Write)

...you ReadFromFile() is coming soon....
0
 
jnhorstCommented:
Vartana:

Looks like Baan has got you mostly covered...  The WriteXml and ReadXml options reduce the amount and complexity of the code, but Baan's options will work fine as well.

If you do want some examples, I'll be happy to post some tomorrow.

John
0
 
Mehrdad_YCommented:
The best solution is Dataset offline capability:
As I can understand from your question, you are already using a dataset so:
To Save your data on the HD:
    Me.[DataSet Name].WriteXML("Path Of File")

To Load your data from HD:
    Me.[DataSet Name].ReadXML("Path Of File")

To Update Data to SQL Server:
(Do what you have to do to update it normally)
Example: Me.SqlDataAdapter1.Update(Me.DataSet1)
0
 
VartanaAuthor Commented:
Mehrdad_Y  how can i just writexml on just 1 table in the dataset ?
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 4
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now