Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2004-08-25
12
5,549 Views
Last Modified: 2008-01-09
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
Comment
Question by:Vartana
  • 4
  • 4
  • 3
  • +1
12 Comments
 
LVL 18

Expert Comment

by:DotNetLover_Baan
ID: 11898317
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
 
LVL 10

Expert Comment

by:jnhorst
ID: 11898342
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
 
LVL 1

Author Comment

by:Vartana
ID: 11898379
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 1

Author Comment

by:Vartana
ID: 11898388
jnhorst

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

Expert Comment

by:jnhorst
ID: 11898423
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
 
LVL 18

Expert Comment

by:DotNetLover_Baan
ID: 11898425
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
 
LVL 1

Author Comment

by:Vartana
ID: 11898432
You gave me the save what about the read my friend.
0
 
LVL 18

Expert Comment

by:DotNetLover_Baan
ID: 11898449
right on it dude...
0
 
LVL 18

Expert Comment

by:DotNetLover_Baan
ID: 11898458
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
 
LVL 10

Expert Comment

by:jnhorst
ID: 11899488
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
 
LVL 2

Accepted Solution

by:
Mehrdad_Y earned 500 total points
ID: 11900779
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
 
LVL 1

Author Comment

by:Vartana
ID: 11967646
Mehrdad_Y  how can i just writexml on just 1 table in the dataset ?
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

837 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question