Vartana
asked on
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 ...
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 ...
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
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
ASKER
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.
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.
ASKER
jnhorst
Can you please Past full code of such a form or perhaps send me a link to a project like that ?
Can you please Past full code of such a form or perhaps send me a link to a project like that ?
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
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
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")
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").Column
FileWriter.Write(dr(i) & vbTab)
Next
FileWriter.WriteLine("")
Next
FileWriter.Close()
StreamWR.Close()
End Sub
-Baan
For TextFile:
Sub SaveToFile()
Dim StreamWR As FileStream
'Check if the file is there.
If File.Exists("C:\data.txt")
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").Column
FileWriter.Write(dr(i) & vbTab)
Next
FileWriter.WriteLine("")
Next
FileWriter.Close()
StreamWR.Close()
End Sub
-Baan
ASKER
You gave me the save what about the read my friend.
right on it dude...
but change this part..
If File.Exists("C:\data.txt")
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....
If File.Exists("C:\data.txt")
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....
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Mehrdad_Y how can i just writexml on just 1 table in the dataset ?
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",
'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