programmatically build database form

[Bob Garbados]

Is it possible to determine the tables, relationships, keys, columns, and
datatypes in a database? If so, how? I want to build an app that can
connect to a specified sql2000 database and construct a form for updating
data. Has anyone successfully done this? Thanks.

 

[microsoft.public.dotnet.framework.aspnet]

Bob

You can use OleDbConnection to get the information.
But regarding the updation , I am not sure about it. I never tried it
till date.
Let me know if you get a breakthough with OleDbConnection for the same.
~ Arvind

 

[bruce barker]

generally yes, the how depends on the database used. there is no standard.
if you use sql2000, and the .net account is dbo of the database you can do
it rather easily. you can query the database for any info you need, and
execute the ddl. everything you want to do is just a sql statement.

-- bruce (sqlwork.com)


| Is it possible to determine the tables, relationships, keys, columns, and
| datatypes in a database? If so, how? I want to build an app that can
| connect to a specified sql2000 database and construct a form for updating
| data. Has anyone successfully done this? Thanks.
|
|

 

[Bob Garbados]

I use sqlconnection because all of our dbs are sql2000. Then I just execute
sql statements to get the table names, stored proc names, etc. Building the
forms will be the tricky part...

Here's how I get the table and stored proc names and display them in
repeaters:
'Constants
Const GET_TABLE_NAMES as String = "Select NAME from SYSOBJECTS where
xtype='U'"
Const GET_STORED_PROC_NAMES as String = "Select NAME from SYSOBJECTS
where xtype='P'"

Public Sub btnGetDatabaseInfo_Click(ByVal sender As System.Object, ByVal
e As System.EventArgs) Handles btnGetDatabaseInfo.Click
'get connection string info
Dim dbServer as String
dim dbDatabase as String
dim dbUser as String
dim dbPassword as String

dbServer = txtServer.text
dbDatabase = txtDatabase.text
dbUser = txtUser.Text
dbPassword = txtPass.text

Dim conString as String
conString = "Server=" & dbServer & ";UID=" & dbUser & ";PWD=" &
dbPassword & ";Database=" & dbDatabase

'connect to database
Dim conDb as SqlConnection
conDb = New SqlConnection(conString)
conDb.Open

'get table names
Dim cmdTableNames as SqlCommand
cmdTableNames = New SqlCommand(GET_TABLE_NAMES, conDb)

Dim dtrTableNames As SqlDataReader
dtrTableNames = cmdTableNames.ExecuteReader

rptTableNames.DataSource = dtrTableNames
rptTableNames.DataBind()

dtrTableNames.Close()
'get stored procs
Dim cmdStoredProcs As SqlCommand
cmdStoredProcs = New SqlCommand(GET_STORED_PROC_NAMES, conDb)

Dim dtrStoredProcNames As SqlDataReader
dtrStoredProcNames = cmdStoredProcs.ExecuteReader

rptStoredProcNames.DataSource = dtrStoredProcNames
rptStoredProcNames.DataBind()

'clean up
conDb.Close()

End Sub

 

[David Jessee]

Take a look at Information_schema inside of Sql Server. That' will get you
a lot of the info you need.