Wiki Link: [discussion:476]
Need to populate database programmatically... 

Jun 26 2008 at 5:15 AM
Hi There.
I watched the series of videos, and wondered; "How can I add data to the database by automatically by code?" I am using visual studio 2008.

I want to take input from user, generate number sets from this input, then put the number sets into the database. Depending on the input, there could be anywhere from 64 to 4096 individual number sets all refering to the user input.
The user will then be able to manipulate the number sets and add descriptions, names, etc., with the original input as reference (also to be stored in the database)

I also need to know how to add columns (or remove columns) from the grid view depending on the user's input at run time.

Is this even possible?

Jun 30 2008 at 3:47 PM
Edited Jun 30 2008 at 3:50 PM
Hello Greg;

Not sure if this is the direction you are going but here are some ideas to maybe research until someone else gets you a more complete answer:

Forgive me if this is too basic or not even in the general ball park of what you are looking for:

Also note that in the code examples a sometimes it is converting my variable names to italics and dropping the underscore character...hope you can still make sense of it?

You could create a custom class for helping to work with the data
Part of that class could be a constructor or method to write the data to the database

I just recently had a project that had to programmatically write to the database (up until that point all my apps pretty much just had the user entering info):

The general process I use is as follows:
1. store a connection string and use that to open a connection to the database useing the .open
a. example: Dim connection As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data\LOGISTICS.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True")
b. note that you would replace everything is quotes above with whatever your connection string would be. One way to help come up with your connection string is when you are creating a new datasource open the connection string portion of the info box and you can copy/paste from it.
c. -- this uses the connectoin string you created above and tries to open it.
2. once I had an open connection I used the sqldatareader to read the data into memory string variables
3. at that point I am having to create a text file so I am using the textout.write but it does not sound like that is what you are trying to do, so...
4. another section of the program I am needing to write data out to the database - I created a class with properties to store what I needed and methods to write the properties I just set to the database
5. I open a connection the same as above
6. I dim a string as a New SqlCommand()
a: Dim cmdInsertData As New SqlCommand()
7. I use parameters and move data into them
a: Dim prmPackageStatus As New SqlParameter("@Packagestatus", mpackage_status)
b: this will create a variable called prmPackageStatus and put the data stored in the private variable mpacakgestatus into it
c: in my case m_pacakge status happens to be filled in by use of a class but you could get data into it anyway you wanted.
8. Call the connection using your connectoin string and your sqlcommand:
a: cmdInsertData.Connection = connection
9. I then build a string that holds the exact sql command I want to run
a: Dim sqlStatementBuild As String = "insert into Package (Packagestatus) values (@Packagestatus,)"
10. move that statement into your sqlcommand
a: cmdInsertData.CommandText = sqlStatementBuild
11. open a connection, write the data and close the connection: (remember the word connection below is a string varaible holding my connection string)

I do now if this helps or not, but it is one way to programmatically access a database. Keep in mind that this is my first program to do this so I am sure there are better ways!
As a matter of fact if anybody knows a better way I would love to hear it!

Not sure about the adding columns from the grid view -- if you still need an answer repost here and I would be glad to research it would certainly be useful to know how to do that.

Joe Ruder

Page view tracker