Generate a database table creation script using. NET, similar to SQL Server

(Share with others) exploitation. NET to generate a database table creation script, similar to SQL Server to write the CREATE statement of the table in our RDIFramework. NET code generator, there is an application that automatically generates the CREATE statement of the table through the database table, as shown in: many methods have been explored before this function is implemented, and finally MSSQLSERVER

(Share with others) exploitation. NET to generate a database table creation script, similar to SQL Server to write the CREATE statement of the table in our RDIFramework. NET code generator, there is an application that automatically generates the CREATE statement of the table through the database table, as shown in: many methods have been explored before this function is implemented, and finally MSSQLSERVER

(Share with others) Exploitation. NETGenerate a database table creation script, similarSqlServerCompileCREATEStatement

In our RDIFramework. NET code generator, there is an application that automatically generates the CREATE statement of the table through the database table, as shown in:

Many methods have been explored before this function is implemented, and finally the built-in dll file of MSSQLSERVER is used. First show the effect of this function generation, and then share the code and method. You are welcome to discuss other feasible methods. Thank you.

The CREATE statement of the generated table is exactly the same as that of the SQL Server Enterprise Manager. Now let's take a look at how to implement it. As I said above, I used the dll file method that comes with SQLSERVER to complete it. Therefore, we must first reference the MSSQLSERVER dll file. For example,D: \ Program Files \ Microsoft SQL Server \", Open the directory"D: \ Program Files \ Microsoft SQL Server \ 100 \ SDK \ Assemblies", You can see all the dll files of SQLSERVER. In fact, through these dll files, we can complete functions like SQLSERVER Enterprise Manager, which is very powerful and depends on how you use them, here, we will only discuss the issue. We need to add two dll file references in our project:

Microsoft. SqlServer. ConnectionInfo. dll

Microsoft. SqlServer. Management. Sdk. Sfc. dll

As shown in:

　　

After referencing the above two dll files, add the namespace in our project:

using Microsoft.SqlServer.Management.Common;using Microsoft.SqlServer.Management.Smo;

Now, we can use the class library provided by MSSQLSERVER to generate the CREATE statement for the table.

All the Code created is as follows:

Private void ScriptOption () {scriptOption. continueScriptingOnError = true; scriptOption. includeIfNotExists = true; scriptOption. noCollation = true; scriptOption. scriptDrops = false; scriptOption. continueScriptingOnError = true; // scriptOption. driAllConstraints = true; scriptOption. withDependencies = false; scriptOption. driForeignKeys = true; scriptOption. driPrimaryKey = true; scriptOption. driDefaults = true; scriptOption. driChecks = true; scriptOption. driUniqueKeys = true; scriptOption. triggers = true; scriptOption. extendedProperties = true; scriptOption. noIdentities = false ;}////// Generate the DDL statement of the table specified by SQL Server ///Private void GenerateSqlServerDDL () {// you do not need to generate the generated data again to save resources. If (! String. isNullOrEmpty (textEditorDDL. text) & textEditorDDL. text. trim (). length> 10) {return;} ScriptOption (); ServerConnection sqlConnection = null; try {StringBuilder sbOutPut = new StringBuilder (); if (dbSet. connectStr. toLower (). contains ("integrated security") // Windows Authentication {sqlConnection = new ServerConnection (dbSet. server);} else // SqlServer authentication {string [] linkDataArray = dbSet. connectStr. split (';'); string userName = string. empty; string pwd = string. empty; foreach (string str in linkDataArray) {if (str. toLower (). replace ("",""). contains ("userid =") {userName = str. split ('=') [1];} if (str. toLower (). replace ("",""). contains ("password") {pwd = str. split ('=') [1];} sqlConnection = new ServerConnection (dbSet. server, userName, pwd);} Server sqlServer = new Server (sqlConnection); Table table = sqlServer. databases [dbSet. dbName]. tables [txtName. text]; string ids; // The table script sbOutPut = new StringBuilder (); sbOutPut. appendLine (); sCollection = table. script (scriptOption); foreach (String str in sCollection) {// here fix the smo bug if (str. contains ("add default") & str. contains ("') AND type = 'D'") {ids = str. substring (str. indexOf ("OBJECT_ID (n'") + "OBJECT_ID (n '". length, str. indexOf ("') AND type = 'D'")-str. indexOf ("OBJECT_ID (n'")-"OBJECT_ID (n '". length); sbOutPut. appendLine (str. insert (str. indexOf ("add default") + 4, "CONSTRAINT" + ids);} else sbOutPut. appendLine (str); sbOutPut. appendLine ("GO");} // generates the stored procedure this. textEditorDDL. setCodeEditorContent ("SQL", sbOutPut. toString (); this. textEditorDDL. saveFileName = this. tableName + ". SQL "; sbOutPut = new StringBuilder ();} catch (Exception ex) {LogHelper. writeException (ex);} finally {sqlConnection. disconnect ();}}

Note: textEditorDDL is the control after the CREATE statement is generated.

Welcome to discuss other methods. Thank you!

If you have any guidance or help, click here. Thank you!