殊途同归,殊途同归，ado.net快速实现MySql的CRUD

on 2011-9-13 in 博文摘选 | 0 Comment
正在学习MySql编程,安装了官方的UPDATE test.Person SET Age=Age+1,Remark=CONCAT(FirstName,LastName);
上面的sql语句执行是没有问题的。除非需要更新的列指定不明，执行的时候，sql编辑器会给出警告： Column '***' in field list is ambiguous。这就说明某一列指代不明。
4、删除
删除的功能和sql server非常相似，一条一条删除（DELETE FROM *** WHERE ***）无疑是最基础最常用的，这里不做示例展示。
和删除相关的就是清空表数据，sql server下的TRUNCATE TABLE *** 对mysql同样适用。
下面着重介绍删除重复记录的实现。
在sql server下，我们需要删除某列的重复记录（示例是FirstName相同，保留的列是ID最小的记录）很简单：
deleteduplicate DELETE FROM Person WHERE Id NOT IN (SELECT MIN(Id) AS MinId FROM Person GROUP BY FirstName )
ps：要找出表中某个字段的重复值，记得曾经在这篇随笔里提及过。原理就是count+分组： select 字段名,count(字段名) from 表名 group by 字段名 having count(字段名)>1
如果将上面的sql语句放在mysql下面执行，会给出错误警告：Error Code: 1093. You can't specify target table 'Person' for update in FROM clause。
同样的功能，在mysql里的实现如下：
mysqldeleteduplicateDELETE FROM test.Person WHERE Id NOT IN (SELECT MinId FROM (SELECT MIN(Id) AS MinId FROM test.Person GROUP BY FirstName) AS tmp);

感觉mysql的这种写法比较迂回一点，好坏不做评价。

三、需要注意的几点

1、插入的记录不合法
在插入的时候，比如插入FirstName的值超过了varchar（16）的长度，则抛出mysqlexception，并提示：Data too long for column 'FirstName' at row 1。这点类似于sql server下的二进制字节流截断的异常。
2、mysql语句的参数传递
经测试，在mysql的存储过程中传递一个和列名Id完全一样的叫Id的参数是相当危险的。在我的测试中，删除存储过程DeletePerson如下：
DeletePersonCREATE DEFINER=`root`@`localhost` PROCEDURE `DeletePerson`( Id int ) BEGIN DELETE FROM Person WHERE Id=Id; END

获取一条的存储过程如下：
GetPersonByIDCREATE DEFINER=`root`@`localhost` PROCEDURE `GetPersonByID`( Id int ) BEGIN SELECT Id,FirstName,LastName,Age,CreateDate,UpdateDate,State,Remark FROM test.Person WHERE Id=Id; END

更新一条记录的存储过程如下：
updatepersonCREATE DEFINER=`root`@`localhost` PROCEDURE `UpdatePerson`( Id int, FirstName varchar(16), LastName varchar(16), Age int, CreateDate datetime, UpdateDate datetime, State int, Remark varchar(256) ) BEGIN UPDATE test.Person SET FirstName=FirstName, LastName=LastName, Age=Age, CreateDate=CreateDate, UpdateDate=UpdateDate, State=State, Remark=Remark WHERE Id=Id; END

但是执行的效果远不是期望的那样，真正的效果依次是删除所有记录、选取出所有记录和更新所有记录。这样很容易造成误删除或者误读取或者错误更新。解决的方法相当简单，给参数起个不是Id的别名即可。
那么通过sql语句而不是存储过程实现增删改查的参数传递如何呢？
我们不妨以update方法一试：
updateperson var id = 1; string sql = "UPDATE test.Person SET Age=Age,UpdateDate=NOW() WHERE Id=Id;"; var paramKeys = new string[] { "Age", "Id" }; var paramVals = new object[] { 25, id }; //这样写同样更新了所有记录,但是Age字段并没有更新 var affectNum = MySqlHelper.ExecuteNonQuery(strSqlConn, sql, CommandType.Text, MySqlHelper.PrepareParameters(paramKeys, paramVals)); Console.WriteLine("update affect rows:{0}", affectNum); //sql = "UPDATE test.Person SET Age=v_Age,UpdateDate=NOW() WHERE Id=v_Id;"; //paramKeys = new string[] { "v_Age", "v_Id" }; //paramVals = new object[] { 25, id }; ////这样写抛出异常:Unknown column 'v_Id' IN 'where clause' //affectNum = MySqlHelper.ExecuteNonQuery(strSqlConn, sql, CommandType.Text, MySqlHelper.PrepareParameters(paramKeys, paramVals)); //Console.WriteLine("update affect rows:{0}", affectNum); sql = "UPDATE test.Person SET Age=@v_Age,UpdateDate=NOW() WHERE Id=@v_Id;"; paramKeys = new string[] { "v_Age", "v_Id" }; paramVals = new object[] { 25, id }; //这样写更新一条记录成功 affectNum = MySqlHelper.ExecuteNonQuery(strSqlConn, sql, CommandType.Text, MySqlHelper.PrepareParameters(paramKeys, paramVals)); Console.WriteLine("update affect rows:{0}", affectNum); sql = "UPDATE test.Person SET Age=Age+1,UpdateDate=NOW() WHERE Id=@v_Id;"; paramKeys = new string[] { "v_Id" }; paramVals = new object[] { id }; //这样写更新一条记录也是成功的 affectNum = MySqlHelper.ExecuteNonQuery(strSqlConn, sql, CommandType.Text, MySqlHelper.PrepareParameters(paramKeys, paramVals)); Console.WriteLine("update affect rows:{0}", affectNum); Console.Read();

实际效果果然非常诡异。
这里我大胆推测，官方提供的MySql 的C#客户端沿袭了Sql Server下的某些写法，所以，直接执行sql语句的时候，建议传递的参数必须带上“@”符号（经本地测试，增删改查都可以这么写；虽然@在存储过程里是个敏感字符，但是c#程序里调用存储过程传参也是可以使用@符号的），这个和前面拼接sql批量插入的地方有某种程度的巧合，而实际上人家可能就是这么设计传参的。
PS：我的一个牛人同事告诉我mysql存储过程传参的一些基本用法，其中着重强调的就是参数传递尽量按照mysql的标准来写，而不要沿袭sql server的写法，否则可能会造成不小困扰。他有非常丰富的开发经历，虽为经验之谈，经测试验证果然非常正确，今后必须汲取。
3、mysql的特殊语法和函数
我们知道，主流关系型数据库有多种，它们都有各自的特点和适用环境。所以如果一个人哪怕是多么了不起的dba，他也不太可能轻易地将各个平台的数据库知识都了然如胸。从这个层面来讲，学习通用而基础的sql原理和知识显得尤为重要，CSDN和博客园有很多优秀文章值得细细品读和学习。
对于像我一样的新手，我认为学好基础的sql，再深入熟练使用不同厂商的特殊语法和内置函数方能游刃有余，mysql的特殊语法和函数当然是一个重要的补充。这几天学到了不少mysql的“独特”写法，这里说它独特，主要还是先入为主，毕竟我个人使用sql server的开发经验远远高于mysql。准备将mysql和sql server的一些常见用法做个对比，这里不再一一列举。

四、打造自己的简易ORM

在之前介绍ado.net的几篇文章中，自己动手实现了一些类似orm的帮助类库（曾经改进了几次，但是不太令人满意），针对sql server的实现相对比较简单，而对mysql和oracle的一直没有动手。在文章最后的下载demo中，我参考了sql server的风格封又重新实现封装了个MySqlHelper（不是官方的那个MySqlHelper），感觉还不错。精力有限，有时间我可能也会把mysql的功能和sqlserver的实现整合在一起，当然还是等用熟了MySql再动手。 最后，求推荐一款简单易用的针对MySQL的ORM。
demo下载：demo
参考：
http://dev.mysql.com/doc/refman/5.6/en/connector-net.html
MySQL 5.1参考手册
http://kb.cnblogs.com/page/86245/
MySQL索引背后的数据结构及算法原理
SqlBulkCopy
O’REILLY <>
Tags:  殊途同归四叶青 殊途同归什么意思 殊途同归的意思 殊途同归txt 殊途同归

延伸阅读

最新评论

发表评论