Console App To Create Table & Composite Key

[VBN70212]

To Create Table & Composite Key

Hello, Friends:

I am using VB.Net 2005 Express and ADO.NET 2

I have a console application that requires me to use VB.NET and ADO.NET

Briefly, I need to create a schema and a table based on that schema, then
I need to bulk copy (or bulk insert) comma-delimited records into said table.

Then, or simultaneously without buffering, said table needs to be saved to
a SQL Server client database, here Sql Server Express.

My table, Table1, consists of the following fields:

Str1: An 8-character string (8 bytes)
Str2: Also an 8-characer string (8 bytes)
Str3: A 4-character string (4 bytes)
Num1: An Unsighed integer (UInteger, 4 bytes)
Num2: A positive decimal number (type single, 4 bytes)

I also need a primary key, Primary1, which is a composite key in the form
of a string: Primary1 = Str1+Str2+Str3

Depending on the situation, I may need an alternate composite key, Primary2,
that is made up of Primary 1 + Num1. But since Num1 is an unsigned
integer, not a string, I like to see an example of the code that does this.

This is how far I've gone before getting stuck:

Dim Table1 As New DataTable("Table1")
 
Dim Str1 As New DataColumn("Str1")
Str1.DataType = GetType(String)
Str1.MaxLength = 8
Str1.Unique = False
Str1.AllowDBNull = False
Str1.Caption = "Str1"
Table1.Columns.Add(Str1)
 
Dim Str2 As New DataColumn("Str2")
Str2.DataType = GetType(String)
Str2.MaxLength = 8
Str2.Unique = False
Str2.AllowDBNull = False
Str2.Caption = "Str2"
Table1.Columns.Add(Str2)
 
Dim Str3 As New DataColumn("Str3")
Str3.DataType = GetType(String)
Str3.MaxLength = 4
Str3.Unique = False
Str3.AllowDBNull = False
Str3.Caption = "Str3"
Table1.Columns.Add(Str3)
 
Dim Num1 As New DataColumn("Num1")
Num1.DataType = GetType(UInteger)
Num1.Unique = False
Num1.AllowDBNull = False
Num1.Caption = "Num1"
Table1.Columns.Add(Num1)
 
Dim Num2 As New DataColumn("Num2")
Num2.DataType = GetType(Single)
Num2.Unique = False
Num2.AllowDBNull = False
Num2.Caption = "Num2"
Table1.Columns.Add(Num2)

'If the above code has errors, please kindly advise.

'Below is syntax for setting simple primary key

Table1.PrimaryKey = New DataColumn(){Table1.Columns(Str1)}

'However, I need help on creating composite key
'which is a string from joining Str1, Str2 & Str3
'this is where I need some help and examples
'It would seem that the following format applies for
'creating a composite key, but I would like some advice
'and opinions from the experts

Table1.PrimaryKey = New DataColumn() _
{Table1.Columns(Str1), Table1.Columns(Str2), _
Table1.Columns(Str3)}

If the above is right, then can one simply add one more
column, Num1, to create the alternative primary key, i.e.
Primary2, which is equal to Primary1+Num1, as follows:

Table1.PrimaryKey = New DataColumn() _
{Table1.Columns(Str1), Table1.Columns(Str2), _
Table1.Columns(Str3), Table1.Columns(Num1)}

Any help and advice are much appreciated. If there
are some archives on this website that would shed
light on the issues raised, giving the links to them
would be very beneficial too.

 

[cjard]

One thing I dont understand.. if you know the schema right now, why must you create it at runtime? Create it at design time, in the dataset designer (though it wil lpretty much do the code you have here it also goes way, way further)

Another point of note.. if you know the schema, why dont you make a schema.ini file and use the Jet database driver to read the file straight into a datatable.. you can essentially say "SELECT * FROM <filename>" and vb will fill all the contents into the fields for you, converting the datataypes etc..

Im pretty sure, using the designer, and a schema.ini, that I could solve your problem in about 10 lines of code..