MySQL database operations in Web Service

MySQL database operations in Web Service

 

Note: This article is an article in The Flex + Web Service + MySQL series blog.

 

In the previous article, we realized connecting to the MySQL database in Web Service, which is actually connecting to the MySQL database in ASP. NET and operating the database. However, in practice, we must first determine whether the connected database exists. The previous article has already introduced how to judge whether the database exists, this blog describes how to create a database when the database to be connected does not exist.

 

In factCodeThere are many ways to create a database, such as SQL Server's osql and iSQL, but I didn't find such a good command in MySQL, so I had to find another way. When reading the MySQL ctor/NET documentation, I found the class of mysqlscript, which is described as follows:

Provides a class capable of executing a SQL script containing multiple SQL statements including create procedure statements that require changing thedelimiter

 

Obviously, you can use this class to execute a MySQL script. Has a simple data script file, has been posted in the previous article (http://blog.csdn.net/li_007/archive/2008/12/15/3522956.aspx), syntax can refer to it, there are a lot of MySQL advanced script syntax can refer to MySQL help.

 

check help to know that the script of mysqlscript is a string. the SQL script file is read into a string. The specific implementation is as follows:

<Br/> private string strcurdir = ""; <br/> Public Service () <br/>{< br/> // uncomment the following line if using designed components <br/> // initializecomponent (); <br/> strcurdir = appdomain. currentdomain. basedirectory. tostring (); <br/>}< br/> private string getmysqlscripts () <br/>{< br/> streamreader filereader = file. opentext (strcurdir + "// app_data // new_db. SQL"); <br/> string strtext = ""; <Br/> while (-1! = Filereader. peek () <br/>{< br/> string STR = filereader. readline (). trim (); <br/> If (true = Str. contains ("--") // to exit. code in the SQL script <br/>{< br/> STR = ""; <br/>}< br/> else <br/>{< br/> strtext + = STR; <br/>}< br/> filereader. close (); <br/> return strtext; <br/>}< br/> then, it is the specific application of the mysqlscript class. It is actually very simple, just like mysqlcommand, the Code is as follows: <br/> private void onscriptcompleted (Object sender, eventargs ARGs) <br/>{< br/> streamwriter runinfo = new streamwriter (strcurdir + "// app_runinfo // appruninfo.txt"); <br/> runinfo. writeline (datetime. now. tolocaltime (). tostring () + "successfully executed. "); <br/> runinfo. close (); <br/>}< br/> private void onscripterror (Object sender, mysqlscripterroreventargs ARGs) <br/>{< br/> streamwriter runinfo = new streamwriter (strcurdir + "// app_runinfo // appruninfo.txt"); <br/> runinfo. writeline (datetime. now. tostring () + "error:" <br/> + args. statementtext. tostring (); <br/> runinfo. close (); <br/>}< br/> private void initdatabase () <br/>{< br/> string query = "select Schema_name from information_schema.schemata "; <br/> mysqlconnection conn = new mysqlconnection ("Server = 127.0.0.1; user = root; Password = 1; database = information_schema "); <br/> mysqlcommand command = new mysqlcommand (query, Conn); <br/> Conn. open (); <br/> mysqldataadapter da = new mysqldataadapter (); <br/> da. selectcommand = command; <br/> dataset DS = new dataset (); <br/> da. fill (DS); <br/> string STR = Ds. getxml (); <br/> If (false = Str. contains ("new_db") // if the database to be connected does not exist, use a script to create <br/>{< br/> string scripts = getmysqlscripts (); <br/> mysqlscript myscript = new mysqlscript (Conn, scripts); <br/> myscript. error + = new mysqlscripterroreventhandler (onscripterror); // Error Event Response Function <br/> myscript. scriptcompleted + = new eventhandler (onscriptcompleted); // The Script successfully executes the Event Response Function <br/> myscript. execute (); <br/>}< br/> Conn. close (); <br/>}< br/>

Note that in actual projects, you need to add Exception Handling for database connection, script execution, file read/write, and so on. This article is also a demonstration of some of the functions of the project, and also a record of my learning.