最强.NET SQLServer 操作类 SqlExecuteXY 源代码发布使用教程文档

来源：互联网发布：雨花网络问政php 编辑：程序博客网时间：2024/06/09 18:33

这个类是自己写的,一直用了好久,个人感觉非常好用.常常繁琐的 SQL 操作往往一两条语句就能实现.而且性能卓越,不用考虑线程安全问题.

所以和大家分享下并且还支持多库操作

源代码下载地址:

http://luyikk.download.csdn.net/

里面寻找最新的 SqlExecuteXY 项目

使用方法:

1.配置配置文件

添加下面代码到你的配置文件内

Winfrom:app.config

WEB: Web.config

说明:

DefautConnectionString 数据库连接字符串 (因为此类支持多库操作所以它是默认的链接字符串)

MaxCount 对象池最大对象数

其他配置:

关于多库配置:

你可以通过这样配置配置文件以达到连接多个数据库

2.创建SqlExecuteXY 对象

常规创建方法:

using(SqlXY.SqlExecuteXY sqlobj = new SqlXY.SqlExecuteXY()){ //sqlobj... SQL操作}

其他创建方法:

SqlXY.SqlExecuteXY sqlobj = new SqlXY.SqlExecuteXY())try{ //sqlobj... SQL操作} finally{ sql.Dispose();}

注意:此SQLExecuteXY对象使用完后一定要Dispose()否者它将无法被对象池回收,后果自己设想.

2.1 多库的对象创建

如果使用不带参数的构造函数那么创建出来的SQL 对象的连接字符串将是 DefautConnectionString 所配置的字符串,如何达到多库访问

其实很简单. 只要在构造方法里面带入配置连接字符串的 name 属性就行好了

例:

SqlXY.SqlExecuteXY sqlobj = new SqlXY.SqlExecuteXY("data2"))try{ //sqlobj... SQL操作} finally{ sql.Dispose();}

或者

using(SqlXY.SqlExecuteXY sqlobj = new SqlXY.SqlExecuteXY("data2")){ //sqlobj... SQL操作}

最常用的语句 SELECT :

1.使用SqlExecuteScalar 返回第一行第一列

数据库代码:

/****** 对象: Table [dbo].[UserInfo] 脚本日期: 12/09/2010 14:52:05 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[UserInfo]([UserName] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,[PassWord] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,[Count] [int] NOT NULL CONSTRAINT [DF_UserInfo_Count] DEFAULT ((5)),[Rex] [int] NOT NULL CONSTRAINT [DF_UserInfo_Rex] DEFAULT ((0)),[UserAllList] [text] COLLATE Chinese_PRC_CI_AS NOT NULL CONSTRAINT [DF_UserInfo_UserAllList] DEFAULT (''),[QQ] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL CONSTRAINT [DF_UserInfo_QQ] DEFAULT ((0)), CONSTRAINT [PK_UserInfo] PRIMARY KEY CLUSTERED ([UserName] ASC)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GOSET ANSI_PADDING OFFinsert into [UserInfo]([UserName],[PassWord],[UserAllList],[QQ]) values('123123','password1','a-b-c','123321');insert into [UserInfo]([UserName],[PassWord],[UserAllList],[QQ]) values('123456','password2','a-b-c','123321');

C#:

using (SqlExecuteXY sqlobj = new SqlExecuteXY()) { string username= sqlobj.SqlExecuteScalar("SELECT [UserName] FROM [UserInfo] WHERE [PassWord]=@PassWord", new SqlParameter[] { new SqlParameter("@PassWord","password2") }).ToString(); }

2.返回dataset:

using (SqlExecuteXY sqlobj = new SqlExecuteXY()) { DataSet ds = sqlobj.SqlExcuteDataSet("SELECT * FROM [UserInfo] WHERE [PassWord]=@PassWord", new SqlParameter[] { new SqlParameter("@PassWord","password2") }); }

3.返回SqlDataReader

using (SqlExecuteXY sqlobj = new SqlExecuteXY()) { SqlDataReader dr = sqlobj.SqlExecuteReader("SELECT * FROM [UserInfo] WHERE [PassWord]=@PassWord", new SqlParameter[] { new SqlParameter("@PassWord","password2") }); }

SELECT 的类化表操作:

类化表,学过LINQ TO SQL 的一定不会陌生.就是将表看成一个类.把里面的一行数据看成一个对象.那么表数据就是这个对象的集合

将上面的表映射成类的代码:

public class UserInfo { public string UserName { get; set; } public string PassWord { get; set; } public int Count { get; set; } public int Rex { get; set; } public string UserAllList { get; set; } public string QQ { get; set; } }

关于列于C#类型的转换请看考微软的 DBTYPE 类说明

1.返回所有表中的集合

using (SqlExecuteXY sqlobj = new SqlExecuteXY()) { List<UserInfo> userlist = sqlobj.SqlExcuteSelectObject<UserInfo>("SELECT * FROM UserInfo"); }

2.查找所有QQ为 123321的集合

using (SqlExecuteXY sqlobj = new SqlExecuteXY()) { List<UserInfo> userlist = sqlobj.SqlExcuteSelectObject<UserInfo>("SELECT * FROM UserInfo WHERE QQ=@QQ",new SqlParameter[] { new SqlParameter("@QQ","123321") }); }

添加更新删除操作:

添加更新删除通常使用 SqlExecuteNonQuery 方法

案例:

UPDATA:

using (SqlExecuteXY sqlobj = new SqlExecuteXY()) { sqlobj.SqlExecuteNonQuery("UPDATE UserInfo SET PassWord=@PassWord WHERE UserName=@UserName",new SqlParameter[] { new SqlParameter("@UserName","123456"), new SqlParameter("@PassWord","newpassword") }); }

INSERT:

using (SqlExecuteXY sqlobj = new SqlExecuteXY()) { sqlobj.SqlExecuteNonQuery("insert into [UserInfo]([UserName],[PassWord],[UserAllList],[QQ]) values(@UserName,@PassWord,@UserAllList,@QQ);", new SqlParameter[] { new SqlParameter("@UserName","123456"), new SqlParameter("@PassWord","password"), new SqlParameter("@UserAllList","c-b-a"), new SqlParameter("@QQ","332213") }); }

DELETE:

using (SqlExecuteXY sqlobj = new SqlExecuteXY()) { sqlobj.SqlExecuteNonQuery("DELETE [UserInfo] WHERE QQ=@QQ);", new SqlParameter[] { new SqlParameter("@QQ","332213") }); }

基于类化表的INSERT UPDATE DELETE

INSERT :

UserInfo temp1 = new UserInfo(); temp1.UserName = "abc"; temp1.PassWord = "password"; temp1.UserAllList = "a-c-b"; temp1.QQ = "887799"; using (SqlExecuteXY sqlobj = new SqlExecuteXY()) { sqlobj.SqlExcuteUpdateOrInsertOrDeleteObject<UserInfo>("insert into [UserInfo]([UserName],[PassWord],[UserAllList],[QQ]) values(@UserName,@PassWord,@UserAllList,@QQ);",temp1); }

UPDATE:

temp1.PassWord = "ccccc"; using (SqlExecuteXY sqlobj = new SqlExecuteXY()) { sqlobj.SqlExcuteUpdateOrInsertOrDeleteObject<UserInfo>("UPDATE UserInfo SET PassWord=@PassWord WHERE UserName=@UserName", temp1); }

DELETE:

UserInfo temp1 = new UserInfo(); temp1.UserName = "abc"; using (SqlExecuteXY sqlobj = new SqlExecuteXY()) { sqlobj.SqlExcuteUpdateOrInsertOrDeleteObject<UserInfo>("DELETE UserInfo WHERE UserName=@UserName", temp1); }

调用存储过程:

注意:要将bolIsProcedure 设置成 TRUE

using (SqlExecuteXY sqlobj = new SqlExecuteXY()){ System.Data.SqlClient.SqlParameter opertype = new System.Data.SqlClient.SqlParameter("@opertype", System.Data.SqlDbType.Int); opertype.Direction = System.Data.ParameterDirection.Output; opertype.Value = DBNull.Value; System.Data.SqlClient.SqlParameter score = new System.Data.SqlClient.SqlParameter("@score", System.Data.SqlDbType.Decimal); score.Direction = System.Data.ParameterDirection.Output; score.Value = DBNull.Value; System.Data.SqlClient.SqlParameter titledesc = new System.Data.SqlClient.SqlParameter("@titledesc", System.Data.SqlDbType.NVarChar, 500); titledesc.Direction = System.Data.ParameterDirection.Output; titledesc.Value = DBNull.Value; System.Data.SqlClient.SqlParameter retval = new System.Data.SqlClient.SqlParameter("@retval", System.Data.SqlDbType.Int); retval.Direction = System.Data.ParameterDirection.Output; retval.Value = DBNull.Value; sqlobj.SqlExecuteNonQuery("lp_CheckOrder_WanMei", new System.Data.SqlClient.SqlParameter[] { new System.Data.SqlClient.SqlParameter("@orderno",p.OrderID), opertype, score, titledesc, retval }, true); int Retavl = (int)(retval.Value);}

事务的处理:

BeginTrans() 开始一个事务

CommitTrans()提交事务
RollbackTrans()回滚事务

UserInfo temp1 = new UserInfo(); temp1.UserName = "abc"; using (SqlExecuteXY sqlobj = new SqlExecuteXY()) { sqlobj.BeginTrans(); try { sqlobj.SqlExcuteUpdateOrInsertOrDeleteObject<UserInfo>("DELETE UserInfo WHERE UserName=@UserName", temp1); sqlobj.CommitTrans(); } catch { sqlobj.RollbackTrans(); } }

大部分操作都在这里,我相信大家可以举一反三

谢谢

最强.NET SQLServer 操作类 SqlExecuteXY 源代码发布 使用教程文档

最强.NET SQLServer 操作类 SqlExecuteXY 源代码发布使用教程文档