Please help: Creating a table in a sql server database

[Paul]

Hi,

I wish to be able to add tables to a sql server database at runtime
from my asp.net application. As well as creating fields I also wish to
be able to create indexes on selected fields and to assign user
permissions. (I need to assign permissions to the table object as I
will be using sp_executesql or exec to execute a string).

I'm thinking there are 3 possible ways to do this:

1. Create the table entirely using ADO.NET
2. Create a template table in sql server, copy the table and rename it
using ADO.NET by either calling a stored procedure or directly using
ADO.NET.
3. Calling a stored procedure that contains all the SQL necesary to
create the table, indexes and permissions.

I have read in a posting on a newsgroup that you cannot use ADO.NET
methods directly to create objects in a database meaning method 1 is
out. Is this correct?

I'm not sure of the SQL that would enable me to copy the structure of
the template table. I've read about the SELECT INTO but that does not
recreate indexes and presumably permissions? If that is so how would I
go about getting a reference to the new table from within my app to
create them - or would I better off using method 3.

If I use the method 3 will I be able to execute the stored procedure
from within my asp.net application? Is it only a matter of calling the
stored procedure from within my app using the credentials of a user
with administrative rights in my connection and making sure that the
appropriate permissions have been assigned in my database?

Thanks

Paul

 

[Miha Markic]

Hi,

Hi,

I wish to be able to add tables to a sql server database at runtime
from my asp.net application. As well as creating fields I also wish to
be able to create indexes on selected fields and to assign user
permissions. (I need to assign permissions to the table object as I
will be using sp_executesql or exec to execute a string).

I'm thinking there are 3 possible ways to do this:

1. Create the table entirely using ADO.NET
2. Create a template table in sql server, copy the table and rename it
using ADO.NET by either calling a stored procedure or directly using
ADO.NET.
3. Calling a stored procedure that contains all the SQL necesary to
create the table, indexes and permissions.

I have read in a posting on a newsgroup that you cannot use ADO.NET
methods directly to create objects in a database meaning method 1 is
out. Is this correct?

Yes. You might use ADOX though.

I'm not sure of the SQL that would enable me to copy the structure of
the template table. I've read about the SELECT INTO but that does not
recreate indexes and presumably permissions? If that is so how would I
go about getting a reference to the new table from within my app to
create them - or would I better off using method 3.

Yes, method 3 would be fine. Or, you can execute the same body of stored
procedure by invoking (the same) sql commands from the code.

If I use the method 3 will I be able to execute the stored procedure
from within my asp.net application? Is it only a matter of calling the
stored procedure from within my app using the credentials of a user
with administrative rights in my connection and making sure that the
appropriate permissions have been assigned in my database?

Yes, certainly.