Temporary table in VB.NET

Hello friends,

I am developing an application where i want this VB.net application to accept the data from the user then store it in a table for temporary basis, Do some functions & when all done I want this table to store the data in a back-end database. After this again for a new user this temporary table will be empty & ready to accept the new data!

I don't know if this is possible or not in this way?

Any helps would be appreciated!

So you want a table to be displayed and then the table to be hidden?
If yes, then why not using the .visible = false?

here is some example code for you... to give you an idea.

Code:

---

    '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

Code:

---

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

---

Hope this example helps you to achive what you want to do! here i am saving the dataset to a file on hard disk! this you can do to save in a database as you never mentioned the database you are working with?

I do this sort of thing all the time, but generally I create tables that
persist. If I need to truncate them, I do so. Sometimes I just delete then
by calling one sp and then rebuild them with another.

Here's a simple example, creating the 'nhtable' table:

Code:

---

Dim ocmdnh As SqlCommand

ocmdnh = New SqlCommand("exec sp_makenhtable", oconn)

ocmdnh.ExecuteNonQuery()

'open nhtable

Dim danhtable As New SqlDataAdapter("select * from nhtable", oconn)

Dim dsnhtable As New DataSet("nhtable")

danhtable.Fill(dsnhtable, "nhtable")

the sp sp_makenhtable deletes the table and then rebuilds it, and then fills
it with data; in sql server it looks like this:

CREATE PROCEDURE sp_makenhtable AS
if exists (select * from information_schema.tables where table_name =
'nhtable')
drop table nhtable
select imcacct, brname, addr, addr2, city, st, zip, draw, bundlect,
issuecode into nhtable from manifest where draw < 300


Sometimes the build is more sophisiticated, adding primary keys, indices,
etc. Sometimes I even delete and dynamically build the sp itself. I will
do this when the sp has variable needs that are not easily passed into the
sp itself. In that case, I will call it to create it and then call it again
to exec it:

Dim ocmd As SqlCommand

ocmd = New SqlCommand("exec sp_dropsp_buildbranchlistandtable", oconn)

ocmd.ExecuteNonQuery()

Dim creationstring As String

If glf_custstat = "" Then

creationstring = "CREATE PROCEDURE sp_buildbranchlist AS "

creationstring += " select imcacct, brname, addr, addr2, city, st, zip,
custstat into branchlist from branches"

creationstring += vbCrLf & "where imcacct in " & longstring

Else

creationstring = "CREATE PROCEDURE sp_buildbranchlist AS "

creationstring += " select imcacct, brname, addr, addr2, city, st, zip,
custstat into branchlist from branches"

creationstring += vbCrLf & "where imcacct in " & longstring & " and custstat
= '" & glf_custstat & Chr(39)

End If

Dim sqladapt As New SqlDataAdapter

sqladapt.SelectCommand = New SqlCommand(creationstring, oconn)

Try

sqladapt.SelectCommand.ExecuteNonQuery()

Catch ex As Exception

MessageBox.Show(ex.Message)

End Try

ocmd = New SqlCommand("exec sp_buildbranchlist", oconn)

Try

ocmd.ExecuteNonQuery()

Catch ex As Exception

MessageBox.Show(ex.Message)

End Try

---

Let me know if you have questions about these approaches; I'll be glad to
help.

TO create a table in Vb.net application (temporary) then storing to SQL server

Code:

---

Please try the following code:

    Private Sub BuildDataSet()

 

        Dim objDS As New Data.DataSet("CustomerOrders")

        Dim dtCustomers As Data.DataTable = objDS.Tables.Add("Customers")

        Dim dtOrders As Data.DataTable = objDS.Tables.Add("Orders")

        Dim objDR As Data.DataRow

 

        With dtCustomers

            .Columns.Add("CustomerID", Type.GetType("System.Int32"))

            .Columns.Add("FirstName", Type.GetType("System.String"))

            .Columns.Add("LastName", Type.GetType("System.String"))

            .Columns.Add("Phone", Type.GetType("System.String"))

            .Columns.Add("Email", Type.GetType("System.String"))

        End With

 

        With dtOrders

            .Columns.Add("CustomerID", Type.GetType("System.Int32"))

            .Columns.Add("OrderID", Type.GetType("System.Int32"))

            .Columns.Add("OrderAmount", Type.GetType("System.Double"))

            .Columns.Add("OrderDate", Type.GetType("System.DateTime"))

        End With

 

        objDS.Relations.Add("r_Customers_Orders", _

        objDS.Tables("Customers").Columns("CustomerID"), _

        objDS.Tables("Orders").Columns("CustomerID"))

 

        objDR = dtCustomers.NewRow()

        objDR("CustomerID") = 1

        objDR("FirstName") = "Miriam"

        objDR("LastName") = "McCarthy"

        objDR("Phone") = "555-1212"

        objDR("Email") = "tweety@hotmail.com"

        dtCustomers.Rows.Add(objDR)

 

        objDR = dtOrders.NewRow()

        objDR("CustomerID") = 1

        objDR("OrderID") = 22

        objDR("OrderAmount") = 0

        objDR("OrderDate") = #11/10/1997#

        dtOrders.Rows.Add(objDR)

 

        Console.WriteLine(objDS.GetXml())

 

    End Sub

---