MySql操作

用C#写了个系统更新的小程序，用到MySql

MySQL 5.1参考手册

1.使用MySqlConnection

---

需要引用dll: MySql.Data.rar

连接数据库

string conStr = "Data Source=localhost;User Id=user;Password=pwd;charset='utf8'";
MySqlConnection connection = new MySqlConnection(Conn);
Connection.Open();

执行数据库数据操作

string cmdStr = "drop database `ilinju_a_tianjin`";
MySqlCommand command ＝ new MySqlCommand(cmdStr, connection);
command.ExecuteNonQuery();

为数据库插入函数

string scpStr = @"DELIMITER $$
/*!50003 CREATE DEFINER=`root`@`localhost` FUNCTION `getChildcata`(rootId INT) RETURNS varchar(1000) CHARSET utf8
BEGIN
         DECLARE sTemp VARCHAR(1000);
         DECLARE sTempChd VARCHAR(1000);
   
         SET sTemp = '$';
         SET sTempChd =cast(rootId as CHAR);
         WHILE sTempChd is not null DO
         SET sTemp = concat(sTemp,',',sTempChd);
         SELECT group_concat(id) INTO sTempChd FROM ilinju_category where FIND_IN_SET(pid,sTempChd)>0;
         END WHILE;
         RETURN sTemp;
    END */$$";
MySqlScript script = new MySqlScript(connection, scpStr);
script.Execute();

 2.cmd执行

---

ProcessStartInfo info = new ProcessStartInfo("cmd.exe");
info.CreateNoWindow = false;
info.UseShellExecute = false;
info.RedirectStandardInput = true;
info.RedirectStandardError = true;
info.RedirectStandardOutput = true;
Process p = new Process();
p.StartInfo = info;
p.Start();
p.StandardInput.WriteLine("C:");
p.StandardInput.WriteLine("cd 'C:\\Program Files (x86)\\MySQL\\MySQL Server 5.1\\bin'");
p.StandardInput.WriteLine(@"mysql -uroot -p123456 ilinju_a_tianjin <D:\test\AppProgramUpdate\bin\Debug\Update.sql");
p.Close();

 

DELIMITER $$ 表示这之后用$$作为程序执行完整SQL语句的标记，默认标记是分号(;)。

/*! ... */这个是为了能使数据库能移到其它的SQL服务器上。MYSQL具有其它SQL DBMS中不具备的扩展，可以写到这里面，在MYSQL里是执行的，移植到其它SQL服务器上只当成注释不执行