During the project development process, the operation of the database is often encountered in the process of table structure, stored procedures and other modifications, when the general operation is done directly in SQL Server Enterprise Manager in the operation, or Query Analyzer to execute the relevant statements, and we in the actual application for security, Generally can be as few as possible to contact the database server, if the database aspect if there is a good programming style, we generally will be changed to generate a SQL file, and through. NET program for data tables and stored procedure updates will be more convenient to operate.
First, we need to upload the SQL file to the corresponding web directory, preferably to copy the file to a protected location that is inaccessible to a remote user.
Microsoft's SQL Server product group has written this operation page, by visiting the Http://www.codeplex.com/sqlhost/Wiki/View.aspx?title=UploadAndExecute page, You can see runsql.aspx this link, click on it to download a page called filedownload.aspx, in the code we can see the defined two parameters, the code is as follows:
Url of the T-SQL file you want to run
String fileUrl = @ "Http://<>/<>.sql";
Connection string to the server 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 needs to upload filedownload.aspx to the server's web directory, which needs to be accessed directly through the domain name.
The Remote filedownload.aspx Web page is then accessed through a browser, and the pages on the remote server are analyzed. SQL file, and executes all of the SQL statements. After successful execution, "T-SQL file executed successfully" will be prompted, otherwise the specific error message will be prompted.
For security, after running the SQL script file, remove the Filedownload.aspx Web page and the SQL file from the remote server.
If we need to keep this feature, and we may be able to operate some SQL statement groups during maintenance, we can change the filedownload.aspx for the following improvements:
1) Add permission section function, prohibit illegal users to access this page, such as only allow the background administrator to operate;
2) The local SQL file can be uploaded to the server to specify the directory, and then this page dynamically read this file;
3) The database connection string is also read in the form of Web. config to facilitate the unified management of the database.
Interested readers can expand further by this. So it goes through. NET has built a very flexible database Operations page, so we in every time the product upgrade or patching, the database operation, we can build an entire SQL file for maintenance.
http://www.bkjia.com/PHPjc/364077.html www.bkjia.com true http://www.bkjia.com/PHPjc/364077.html techarticle During the project development process, the operation of the database is often encountered in the process of table structure, stored procedures and other modifications, then the general operation is directly in the SQL Server Enterprise Management ...
