Solved

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

Posted on 2004-08-25
12
5,131 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
 
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
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 Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

705 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now