Solved

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

Posted on 2004-08-25
12
5,396 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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 
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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SLMGR Switches Are Not Working On KMS Host 3 91
Visual Studio 2013 Shortcut (VB) 4 45
Sql server insert 13 28
Help with preventing downloading a zip file 10 35
Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

776 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