Creating an Oracle Database and Schema Programmatically by ADO.NET

Question

How can I create the oracle database programmatically in ADO.NET and the schema for it using the password userId +, so I can just go to my unnecessary tool in the sql oracle developer tool, where I just create the connection:

• connection_name
• UserId (schema)
• password

Answer 1

I have done this with SQL before, but have never tried with ADO.NET ...

string connectionString = "..."; string oracleDataPath = "C:\\PATH_TO_ORADATA\\"; string username = "NEW_USER"; string password = "NEW_PWD"; string schema = "NEW_SCHEMA"; using (OracleConnection conn = new OracleConnection(connectionString)) { conn.Open(); OracleCommand cmd = conn.CreateCommand(); cmd.CommandText = "CREATE TABLESPACE \"" + schema + "\" DATAFILE '" + oracleDataPath + schema + ".DBF' SIZE 10M AUTOEXTEND ON NEXT 1M"; cmd.ExecuteNonQuery(); cmd.CommandText = "CREATE USER \"" + username + "\" IDENTIFIED BY \"" + password + "\" DEFAULT TABLESPACE \"" + schema + "\" TEMPORARY TABLESPACE TEMP"; cmd.ExecuteNonQuery(); cmd.CommandText = "GRANT CONNECT TO \"" + username + "\""; cmd.ExecuteNonQuery(); cmd.CommandText = "ALTER USER \"" + username + "\" QUOTA UNLIMITED ON \"" + schema + "\""; cmd.ExecuteNonQuery(); } 

Use the ADMIN / DBA account in the connection string.
Install oracleDataPath with the path where your Oracle stores its data files.

Let me know if this works :-)