Execute SQL files remotely through .NET in SQL Server_PHP tutorial

During the project development process, you often encounter modifications to table structures, stored procedures, etc. when operating the database. At this time, the general operations are performed directly through the Enterprise Manager in SQL Server, or query Analyzer to execute relevant statements. In order to be safe in practical applications, we generally have as little contact with the database server as possible. If we have a good programming style for the database, we usually generate changes to the database server. A SQL file, and updating data tables and stored procedures through .NET programs will be more convenient.

First we need to transfer the SQL file to the corresponding WEB directory. It is best to copy this file to a protected place that is not easily accessible by remote users.

Microsoft's SQL Server product group has written this operation page. You can see it by visiting http://www.codeplex.com/sqlhost/Wiki/View.aspx?title=UploadAndExecute Click the link RunSQL.aspx to download a page named FileDownload.aspx. In the code, we can see the two parameters that have been defined. The code is as follows:


// Url of the T-SQL file you want to run
　string fileUrl = @"http://<>/<>.sql";
　// Connection string to the server you want to execute against
　string connectionString = @"<>";




fileUrl represents the storage path address and name of the uploaded SQL file, connectionString represents the database connection string, and then you need Upload FileDownload.aspx to the WEB directory of the server, which needs to be directly accessible through the domain name.

Then access the remote FileDownload.aspx web page through the browser. At this time, the page on the remote server will analyze the .SQL file and execute all SQL statements in it. After successful execution, "T-SQL file executed successfully" will be prompted, otherwise specific error information will be prompted.

For security, after running the SQL script file, delete the FileDownload.aspx web page and SQL file from the remote server.

If we need to retain this function and may frequently operate some SQL statement groups during maintenance, we can modify FileDownload.aspx and make the following improvements:

1) Add some permission functions to prohibit illegal users from accessing this page. For example, only the background administrator is allowed to operate;

2) You can upload local SQL files through the background and upload them to the specified directory on the server. Then this page dynamically reads this file;

3) The database connection string is also read in the form of WEB.CONFIG to facilitate unified management of the database.

Interested readers can click here for further expansion. In this way, a very flexible database operation page has been built through .NET, so that every time we upgrade or patch the product, we can create an entire SQL file for maintenance of database operations.

http://www.bkjia.com/PHPjc/364077.htmlwww.bkjia.comtruehttp: //www.bkjia.com/PHPjc/364077.htmlTechArticleDuring the project development process, modifications to table structures, stored procedures, etc. are often encountered when operating the database. , at this time the general operations are through enterprise management directly in SQL Server...