SQL类说明

使用方法:1.配置配置文件connectionStringsaddname=DefautConnectionStringconnectionString=server=192.168.134.153;Databas/connectionStringsappSettingsaddkey=MaxCountvalue=100//appSettings添加下面代码到你的配置文件内Winfrom:app.configWEB:Web.config说明:DefautConnectionString数据库连接字符串(因为此类支持多库操作所以它是默认的链接字符串)MaxCount对象池最大对象数其他配置:关于多库配置:connectionStringsaddname=DefautConnectionStringconnectionString=server=192.168.134.153;Databasaddname=data1connectionString=server=192.168.134.153;Database=AutoAudit1;Useaddname=data2connectionString=server=192.168.134.153;Database=AutoAudit2;UserIaddname=data3connectionString=server=192.168.134.153;Database=AutoAudit3;UserI/connectionStringsappSettings你可以通过这样配置配置文件以达到连接多个数据库2.创建SqlExecuteXY对象常规创建方法:using(SqlXY.SqlExecuteXYsqlobj=newSqlXY.SqlExecuteXY()){//sqlobj...SQL操作}其他创建方法:SqlXY.SqlExecuteXYsqlobj=newSqlXY.SqlExecuteXY())try{//sqlobj...SQL操作}finally{sql.Dispose();}注意:此SQLExecuteXY对象使用完后一定要Dispose()否者它将无法被对象池回收,后果自己设想.2.1多库的对象创建如果使用不带参数的构造函数那么创建出来的SQL对象的连接字符串将是DefautConnectionString所配置的字符串,如何达到多库访问其实很简单.只要在构造方法里面带入配置连接字符串的name属性就行好了例:SqlXY.SqlExecuteXYsqlobj=newSqlXY.SqlExecuteXY(data2))try{//sqlobj...SQL操作}finally{sql.Dispose();}或者using(SqlXY.SqlExecuteXYsqlobj=newSqlXY.SqlExecuteXY(data2)){//sqlobj...SQL操作}最常用的语句SELECT:1.使用SqlExecuteScalar返回第一行第一列数据库代码:/******对象:Table[dbo].[UserInfo]脚本日期:12/09/201014:52:05******/SETANSI_NULLSONGOSETQUOTED_IDENTIFIERONGOSETANSI_PADDINGONGOCREATETABLE[dbo].[UserInfo]([UserName][varchar](50)COLLATEChinese_PRC_CI_ASNOTNULL,[PassWord][varchar](50)COLLATEChinese_PRC_CI_ASNOTNULL,[Count][int]NOTNULLCONSTRAINT[DF_UserInfo_Count]DEFAULT((5)),[Rex][int]NOTNULLCONSTRAINT[DF_UserInfo_Rex]DEFAULT((0)),[UserAllList][text]COLLATEChinese_PRC_CI_ASNOTNULLCONSTRAINT[DF_UserInfo_UserAllList]DEFAULT(''),[QQ][varchar](50)COLLATEChinese_PRC_CI_ASNOTNULLCONSTRAINT[DF_UserInfo_QQ]DEFAULT((0)),CONSTRAINT[PK_UserInfo]PRIMARYKEYCLUSTERED([UserName]ASCC#:using(SqlExecuteXYsqlobj=newSqlExecuteXY()){stringusername=sqlobj.SqlExecuteScalar(SELECT[UserName]FROM[UserInfo]W{newSqlParameter(@PassWord,password2)}).ToString();}2.返回dataset:using(SqlExecuteXYsqlobj=newSqlExecuteXY()){DataSetds=sqlobj.SqlExcuteDataSet(SELECT*FROM[UserInfo]WHERE[PassWo{newSqlParameter(@PassWord,password2)});}3.返回SqlDataReaderusing(SqlExecuteXYsqlobj=newSqlExecuteXY()){SqlDataReaderdr=sqlobj.SqlExecuteReader(SELECT*FROM[UserInfo]WHERE[{newSqlParameter(@PassWord,password2)});}SELECT的类化表操作:类化表,学过LINQTOSQL的一定不会陌生.就是将表看成一个类.把里面的一行数据看成一个对象.那么表数据就是这个对象的集合将上面的表映射成类的代码:publicclassUserInfo{publicstringUserName{get;set;}publicstringPassWord{get;set;}publicintCount{get;set;}publicintRex{get;set;}publicstringUserAllList{get;set;}publicstringQQ{get;set;}}关于列于C#类型的转换请看考微软的DBTYPE类说明1.返回所有表中的集合using(SqlExecuteXYsqlobj=newSqlExecuteXY()){ListUserInfouserlist=sqlobj.SqlExcuteSelectObjectUserInfo(SELECT*}2.查找所有QQ为123321的集合using(SqlExecuteXYsqlobj=newSqlExecuteXY()){ListUserInfouserlist=sqlobj.SqlExcuteSelectObjectUserInfo(SELECT*F{newSqlParameter(@QQ,123321)});}添加更新删除操作:添加更新删除通常使用SqlExecuteNonQuery方法案例:UPDATA:using(SqlExecuteXYsqlobj=newSqlExecuteXY()){sqlobj.SqlExecuteNonQuery(UPDATEUserInfoSETPassWord=@PassWordW{newSqlParameter(@UserName,123456),newSqlParameter(@PassWord,newpassword)});}INSERT:using(SqlExecuteXYsqlobj=newSqlExecuteXY()){sqlobj.SqlExecuteNonQuery(insertinto[UserInfo]([UserName],[PassWord],[Use{newSqlParameter(@UserName,123456),newSqlParameter(@PassWord,password),newSqlParameter(@UserAllList,c-b-a),newSqlParameter(@QQ,332213)});}DELETE:using(SqlExecuteXYsqlobj=newSqlExecuteXY()){sqlobj.SqlExecuteNonQuery(DELETE[UserInfo]WHEREQQ=@QQ);,newSqlPa{newSqlParameter(@QQ,332213)});}基于类化表的INSERTUPDATEDELETEINSERT:UserInfotemp1=newUserInfo();temp1.UserName=abc;temp1.PassWord=password;temp1.UserAllList=a-c-b;temp1.QQ=887799;using(SqlExecuteXYsqlobj=newSqlExecuteXY()){sqlobj.SqlExcuteUpdateOrInsertOrDeleteObjectUserInfo(insertinto[UserInfo]([U}UPDATE:temp1.PassWord=ccccc;using(SqlExecuteXYsqlobj=newSqlExecuteXY()){sqlobj.SqlExcuteUpdateOrInsertOrDeleteObjectUserInfo(UPDATEUserInfoSET}DELETE:UserInfotemp1=newUserInfo();temp1.UserName=abc;using(SqlExecuteXYsqlobj=newSqlExecuteXY()){sqlobj.SqlExcuteUpdateOrInsertOrDeleteObjectUserInfo(DELETEUserInfoWHE}调用存储过程:注意:要将bolIsProcedure设置成TRUEusing(SqlExecuteXYsqlobj=newSqlExecuteXY()){System.Data.SqlClient.SqlParameteropertype=newSystem.Data.SqlClient.SqlParaopertype.Direction=System.Data.ParameterDirection.Output;opertype.Value=DBNull.Value;System.Data.SqlClient.SqlParameterscore=newSystem.Data.SqlClient.SqlParametescore.Direction=System.Data.ParameterDirection.Output;score.Value=DBNull.Value;System.Data.SqlClient.SqlParametertitledesc=newSystem.Data.SqlClient.SqlParamtitledesc.Direction=System.Data.ParameterDirection.Output;titledesc.Value=DBNull.Value;System.Data.SqlClient.SqlParameterretval=newSystem.Data.SqlClient.SqlParamete事务的处理:BeginTrans()开始一个事务CommitTrans()提交事务RollbackTrans()回滚事务UserInfotemp1=newUserInfo();temp1.UserName=abc;using(SqlExecuteXYsqlobj=newSqlExe