Data generator help!!

[Shum]

Hi everybody!!
I really need help for my assignment... I want to make an application
in c# to populate the database (sql server 2005), so that later on we
can use that large amount of data for mining etc.
So any of you who knows how to go about implementing this thing are
free to reply...
I was thinking of doing it this way: first get the list of tables in
the database, then get the data type of the coulumn, if the data type
is int then generate a random number and insert that number into the
field.. move onto the next column.... and this is how all the tables
will be filled...
but i dont know how to get the data types of the columns etc....
Please do reply with your valuable suggestions... and ideas.

Thanks

 

[Simon Harvey]

Hi everybody!!
I really need help for my assignment... I want to make an application
in c# to populate the database (sql server 2005), so that later on we
can use that large amount of data for mining etc.
So any of you who knows how to go about implementing this thing are
free to reply...
I was thinking of doing it this way: first get the list of tables in
the database, then get the data type of the coulumn, if the data type
is int then generate a random number and insert that number into the
field.. move onto the next column.... and this is how all the tables
will be filled...
but i dont know how to get the data types of the columns etc....
Please do reply with your valuable suggestions... and ideas.

Thanks

For something like this you would need to use ado.net 2.0's metadata
functionality to examine the structure of the db and tables etc.

You can find out more about these features at:

http://msdn2.microsoft.com/en-us/library/ms379543(VS.80).aspx

Hope that helps

Kindest Regards

Simon

 

[Jon Skeet [C# MVP]]

I really need help for my assignment... I want to make an application
in c# to populate the database (sql server 2005), so that later on we
can use that large amount of data for mining etc.
So any of you who knows how to go about implementing this thing are
free to reply...
I was thinking of doing it this way: first get the list of tables in
the database, then get the data type of the coulumn, if the data type
is int then generate a random number and insert that number into the
field.. move onto the next column.... and this is how all the tables
will be filled...

That sounds like a bad way of getting even slightly realistic data.
You should make your generator understand what the database is
actually representing, and try to model it. For instance, I've written
code to model users browsing to web sites, taking into account how
long a user might stay within a website, how often they will be
fetching data, what kind of concurrency to expect at different times
of day etc.

Using straight random values will give a completely different data
distribution to reality, which could well affect performance tuning
etc. Also, for fields that are used as foreign keys, you can't just
give a random ID, as you're likely to violate constraints.

Jon

 

[DeveloperX]

For something like this you would need to use ado.net 2.0's metadata
functionality to examine the structure of the db and tables etc.

You can find out more about these features at:

http://msdn2.microsoft.com/en-us/library/ms379543(VS.80).aspx

Hope that helps

Kindest Regards

Simon- Hide quoted text -

- Show quoted text -

Alternatively you can do it through SQL:
The following will retrieve table columns and datatypes. I left the
index stuff in at the end as I agree with what Jon says below. You
could use it to identify what fields are key fields and then do god
knows what with that


DECLARE @TableName AS varchar(50)
SELECT @TableName ='results' -- <--- change this to your table!

-- databases
SELECT name, dbid FROM master..sysdatabases

-- Table and columns
SELECT SO.Name as SO_Name, SO.ID as SO_ID, SC.name as SC_Name,
SC.colid AS SC_ColID, ST.name AS ST_Name
FROM sysobjects SO JOIN syscolumns SC ON SO.id = SC.id INNER JOIN
systypes ST ON SC.xtype = ST.xtype
WHERE SO.name LIKE @TableName AND SO.xtype = 'U'

-- Table indexes
SELECT @TableName AS SO_Name, SI.indid AS SI_IndID, SI.name AS
SI_Name
FROM sysindexes SI
WHERE SI.id IN (select id FROM sysobjects WHERE name LIKE @TableName
AND xtype = 'U')
AND SI.name NOT LIKE '_WA_Sys%'

-- Table indexes with index keys
SELECT SO.name AS SO_Name, SI.name as SI_Name, SC.name AS SC_Name
FROM sysobjects SO INNER JOIN syscolumns SC ON SO.id = SC.id
INNER JOIN sysindexkeys SIK ON SIK.id = SO.id AND SC.colid =
SIK.colid
INNER JOIN sysindexes SI ON SI.id = SO.id AND SIK.indid = SI.indid
WHERE SO.name LIKE @TableName AND
SI.name NOT LIKE '_WA_Sys%'
--ORDER BY SI_Name

 

[Shum]

Hmmm... yea im in great fix... someone told me to use data adapetr to
get the database information and datatypes... yea u r right i cant
just generate a random number for ID... dont know how to deal with
that...........

 

[RobinS]

I guess the question is, do you actually have some data you want to store
in the database? Your description made it sound like you were just going to
create some columns and put some random data in there.

Robin S.
-------------------------------------