About dynamically creating databases in. Net (including tables, views ...) Refer to other people's code...

Recently, it was very big because we needed to do something to dynamically generate a database, but later I referred to some articles and finally pieced them together...

 

Take the first place in the code ..

Using system;
Using system. configuration;
Using system. Data;
Using system. LINQ;
Using system. Web;
Using system. Web. Security;
Using system. Web. UI;
Using system. Web. UI. htmlcontrols;
Using system. Web. UI. webcontrols;
Using system. Web. UI. webcontrols. webparts;
Using system. xml. LINQ;
Using system. Data. sqlclient;
Using system. collections;
Using system. IO;

Public partial class default2: system. Web. UI. Page
{
Protected void page_load (Object sender, eventargs E)
{
If (! Ispostback)
{
If (execfile ())
{
Response. Write ("success ");
String constr = "Data Source =.; initial catalog = sqltest111; Integrated Security = true"; // define link character seek
Sqlconnection conn = new sqlconnection (constr );
Conn. open ();
Sqlcommand cmd = new sqlcommand ();
Cmd. Connection = conn;
Arraylist lists = default2.executesqlfile (server. mappath ("script. SQL"); // call the executesqlfile () method to return the arraylist object;
String teststr; // defines the variable for traversing the arraylist;
Foreach (string varcommandtext in lists)
{
Teststr = varcommandtext; // The traversal operator value;
// Response. Write (teststr + "| @ | <br> ");
Cmd. commandtext = teststr; // assign an SQL statement to sqlcommand;
Cmd. executenonquery (); // Execute
}
Arraylist listssss = default2.executesqlfile (server. mappath ("script111. SQL"); // call the executesqlfile () method to return the arraylist object;
// Define the variable for traversing the arraylist;
Foreach (string varcommandtext in listssss)
{
Teststr = varcommandtext; // The traversal operator value;
// Response. Write (teststr + "| @ | <br> ");
Cmd. commandtext = teststr; // assign an SQL statement to sqlcommand;
Cmd. executenonquery (); // Execute
}
Conn. Close ();
}
}
}
/// <Summary>
/// Create a connection START process to create a database
/// </Summary>
/// <Returns> </returns>
Private bool execfile ()
{
Try
{
String connstr = "Data Source =.; user id = sa; Password = 123; persist Security info = false; packet size = 4096 ";
Executesql (connstr, "Master", "create database" + "sqltest111"); // call executenonquery () to create a database
System. Diagnostics. Process sqlprocess = new system. Diagnostics. Process (); // create a process
Sqlprocess. startinfo. filename = "osql.exe"; // osql is a practical tool for connecting to the server based on the ODBC driver (see the SQL help manual)
// String STR = @ "C: \ Program Files \ Microsoft SQL Server \ MSSQL \ data ";
Sqlprocess. startinfo. arguments = "-U sa-P Sa-D sqltest-I c :\\ Program Files \ Microsoft SQL Server \ MSSQL \ data "; // obtain the parameters of the Startup Program
Sqlprocess. startinfo. windowstyle = system. Diagnostics. processwindowstyle. Hidden; // The Window status of the calling process, which is hidden in the background.
Sqlprocess. Start ();
Sqlprocess. waitforexit ();
Sqlprocess. Close ();
Return true;
}
Catch (exception ex)
{
Throw ex;
}
}
/// <Summary>
/// Create a database and call executenonquery () for execution
/// </Summary>
/// <Param name = "conn"> </param>
/// <Param name = "databasename"> </param>
/// <Param name = "SQL"> </param>
Private void executesql (string Conn, string databasename, string SQL)
{
System. Data. sqlclient. sqlconnection mysqlconnection = new system. Data. sqlclient. sqlconnection (conn );
System. Data. sqlclient. sqlcommand command = new system. Data. sqlclient. sqlcommand (SQL, mysqlconnection );
Command. Connection. open ();
Command. Connection. changedatabase (databasename );
Try
{
Command. executenonquery ();
}
Finally
{
Command. Connection. Close ();
}
}

Public static arraylist executesqlfile (string varfilename)
{
//
// Todo: Read the. SQL script file.
//
Streamreader sr = file. opentext (varfilename); // you can specify the file path and complete file name.
Arraylist alsql = new arraylist (); // each read language name is saved to the arraylist
String commandtext = "";
String varline = "";
While (Sr. Peek ()>-1)
{
Varline = Sr. Readline ();
If (varline = "")
{
Continue;
}
If (varline! = "Go ")
{
Commandtext + = varline;
Commandtext + = "";
}
Else
{
Alsql. Add (commandtext );
Commandtext = "";
}
}

Sr. Close ();
Return alsql;
}
}

In the script. SQL file, click Generate script in the database. After the script is generated, copy the file to the project... In addition, this file contains a view...

 

PS: note that during database creation, you must first create a database, create a table, and create a view .. And so on. Sometimes, when you generate a script, all the things are generated in one piece. What you need to do is to generate the tables and views in the script .. Separate them into different ***. SQL files and create them in sequence... In this way, you can... ....