none
create sql table in vb.net code

    Question

  • Hi,

    create a table in vb.net connect with sql server.

    i know create a sql table in sql server 2000. but i don't know vb.net using runtime create a table.

    pleasew help me.
    thanks

    Tuesday, February 13, 2007 3:40 PM

Answers

  • a.s.viswa,

    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

     

    Thursday, February 22, 2007 1:59 AM

All replies

  • Hi

    Do you want the TSQL ddl statement for creating a table (temp or perm) or are you looking for the code to create a connection and issue a command?

    Richard

    Tuesday, February 13, 2007 4:36 PM
  • Hi

    I am using  visual basic .net application,

    that vb.net generate a code for create a sql table .

    i am creating  vb.net only not for sql server.

    please help me.





    Wednesday, February 14, 2007 6:52 AM
  • Hi

    Sorry but I still do not understand what you are looking for. 

    Do you want to create a table in an application written in VB.NET but not in a database?

    Do you mean you want to create a temporary in-memory data table?

    Do you mean that you want to create a table in a database that is not SQL Server.  If so, which database are you using?

    Richard

    Wednesday, February 14, 2007 9:35 AM
  • Hi

    yes.create a table in an application written in VB.NET but not in a database.

    but store in sql server 2000 or 2005.
    Thursday, February 15, 2007 4:33 AM
  • a.s.viswa,

    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

     

    Thursday, February 22, 2007 1:59 AM
  • Supposing you are adding only 1 datatable to an in memory dataset. How can you add a column as the primary key? 

    I noticed that you added a relationship using customerid between 2 tables.  Does this make the customerid as a primary key?
    Wednesday, July 16, 2008 11:30 PM