如题。要返回一个ADO.NET对象好像没有使用ORM的必要,而且从编程的角度看这样的实现一点也不OO,但是实际的开发场景中还是会碰到这种需求的。下面我就借鉴前人的经验,结合实际的示例,再总结一下。如果您认真看完,应该可以体会得到我的一些尝试,而不是人云亦云的照搬代码。
1、获得DbCommand对象
对于SQL语句,方法如下:
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
/// <summary>
/// SQL语?句?,?获?取?DbCommand
/// </summary>
/// <param name="sqlMapper"></param>
/// <param name="statementName"></param>
/// <param name="paramObject"></param>
/// <returns></returns>
protected virtual IDbCommand GetDbCommand(ISqlMapper sqlMapper, string statementName, object paramObject)
{
IStatement statement = sqlMapper.GetMappedStatement(statementName).Statement;
IMappedStatement mapStatement = sqlMapper.GetMappedStatement(statementName);
ISqlMapSession session = new SqlMapSession(sqlMapper);
if (sqlMapper.LocalSession != null)
{
session = sqlMapper.LocalSession;
}
else
{
session = sqlMapper.OpenConnection();
}
RequestScope request = statement.Sql.GetRequestScope(mapStatement, paramObject, session);
mapStatement.PreparedCommand.Create(request, session as ISqlMapSession, statement, paramObject);
IDbCommand cmd = session.CreateCommand(CommandType.Text);
cmd.CommandText = request.IDbCommand.CommandText;
//return request.IDbCommand;
return cmd;
}
对于存储过程,因为对于参数类型的不同,需要多几步处理(因为需要多维护一个参数字典和其对应的ParameterDirection字典):
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
|
/// <summary>
/// 获取DbCommand,主要是针对存储过程
/// </summary>
/// <param name="sqlMapper"></param>
/// <param name="statementName"></param>
/// <param name="paramObject">参数</param>
/// <param name="dictParam">参数字段</param>
/// <param name="dictParmDirection">ParameterDirection字典</param>
/// <param name="cmdType"></param>
/// <returns></returns>
protected
virtual
IDbCommand GetDbCommand(ISqlMapper sqlMapper,
string
statementName,
object
paramObject, IDictionary dictParam, IDictionary<
string
, ParameterDirection> dictParmDirection, CommandType cmdType)
{
if
(cmdType == CommandType.Text)
{
return
GetDbCommand(sqlMapper, statementName, paramObject);
}
IStatement statement = sqlMapper.GetMappedStatement(statementName).Statement;
IMappedStatement mapStatement = sqlMapper.GetMappedStatement(statementName);
ISqlMapSession session =
new
SqlMapSession(sqlMapper);
if
(sqlMapper.LocalSession !=
null
)
{
session = sqlMapper.LocalSession;
}
else
{
session = sqlMapper.OpenConnection();
}
RequestScope request = statement.Sql.GetRequestScope(mapStatement, paramObject, session);
mapStatement.PreparedCommand.Create(request, session
as
ISqlMapSession, statement, paramObject);
IDbCommand cmd = session.CreateCommand(cmdType);
cmd.CommandText = request.IDbCommand.CommandText;
if
(cmdType != CommandType.StoredProcedure || dictParam ==
null
)
{
return
cmd;
}
foreach
(DictionaryEntry de
in
dictParam)
//存储过程
{
string
key = de.Key.ToString();
IDbDataParameter dbParam = cmd.CreateParameter();
dbParam.ParameterName = key;
dbParam.Value = de.Value;
if
(dictParmDirection !=
null
&& dictParmDirection.ContainsKey(key))
{
dbParam.Direction = dictParmDirection[key];
//ParameterDirection
}
cmd.Parameters.Add(dbParam);
}
return
cmd;
}
|
代码写得可能还有改进的必要,有需要从事这方面开发的童鞋,如果您看着有更好的办法请不吝赐教。
备注:
a、对于1.6.1之前的版本,获得命令的方式可以通过RequestScope的IDbCommand属性,但是1.6.1版本的IDbCommand属性返回的是IBatisNet.DataMapper.Commands.DbCommandDecorator对象,您可以注释代码验证一下。
b、网上有些文章贴的方法返回的DbCommand对象都是对于拼接SQL语句而言,没有实现获取存储过程的DbCommand(有参数无参数的都要考虑)。本文在原有资料的基础上,尝试着做出改进,目前支持SQL语句和存储过程。
2、返回DataSet对象
通过SQL语句,获取DataSet:
1
2
3
4
5
|
public
DataSet GetDSPerson(
int
id)
{
string
sql =
this
.GetRuntimeSql(
this
.SqlMapper,
this
.GetStatementName(
"GetDSPerson"
), id);
return
this
.QueryForDataSet(
this
.SqlMapper,
this
.GetStatementName(
"GetDSPerson"
), id);
}
|
XML配置:
1
2
3
4
|
<
select
id="GetDSPerson" parameterClass="int" resultClass="System.Data.DataSet">
<
include
refid="CommonPersonColumns4Select"></
include
>
WHERE 1=1 AND Id=$id$
</
select
>
|
客户端的调用:
1
2
3
|
int
id = 1;
DataSet ds = ServiceFactory.CreatePersonService().GetDSPerson(id);
Console.WriteLine(ds.GetXml());
|
执行结果返回如下:
3、返回DataTable对象
a、通过SQL语句
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
/// <summary>
/// 通?用?的?执′行DSQL语?句?以?DataTable的?方?式?得?到?返う?回?的?结á果?(xml文?件t中D参?数簓要癮使?用?$标括?记?的?占?位?参?数簓)
/// </summary>
/// <param name="sqlMapper"></param>
/// <param name="statementName"></param>
/// <param name="paramObject"></param>
/// <returns></returns>
protected virtual DataTable QueryForDataTable(ISqlMapper sqlMapper, string statementName, object paramObject)
{
DataSet ds = new DataSet();
bool isSessionLocal = false;
IDalSession session = sqlMapper.LocalSession;
if (session == null)
{
session = new SqlMapSession(sqlMapper);
session.OpenConnection();
isSessionLocal = true;
}
IDbCommand cmd = GetDbCommand(sqlMapper, statementName, paramObject);//SQL text command
try
{
cmd.Connection = session.Connection;
IDbDataAdapter adapter = session.CreateDataAdapter(cmd);
adapter.Fill(ds);
}
finally
{
if (isSessionLocal)
{
session.CloseConnection();
}
}
return ds.Tables[0];
}
这个相对简单,因为前面2中已经得到了DataSet,DataTable的提取就轻而易举了。
b、通过含OUTPUT参数的存储过程
这个地方主要就是改进后的GetDbCommand重载方法的使用,
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
|
/// <summary>
/// 查询返回DataTable,对于包括OUTPUT参数的存储过程同样适用
/// </summary>
/// <param name="sqlMapper"></param>
/// <param name="statementName"></param>
/// <param name="paramObject">参数</param>
/// <param name="dictParam">参数字典</param>
/// <param name="dictParamDirection">ParameterDirection字典</param>
/// <param name="htOutPutParameter">返回的Output参数值哈希表</param>
/// <returns></returns>
protected
virtual
DataTable QueryForDataTable(ISqlMapper sqlMapper,
string
statementName,
object
paramObject, IDictionary dictParam, IDictionary<
string
, ParameterDirection> dictParamDirection,
out
Hashtable htOutPutParameter)
{
DataSet ds =
new
DataSet();
bool
isSessionLocal =
false
;
ISqlMapSession session = sqlMapper.LocalSession;
if
(session ==
null
)
{
session =
new
SqlMapSession(sqlMapper);
session.OpenConnection();
isSessionLocal =
true
;
}
IDbCommand cmd = GetDbCommand(sqlMapper, statementName, paramObject, dictParam, dictParamDirection, CommandType.StoredProcedure);
//存储过程
try
{
cmd.Connection = session.Connection;
IDbDataAdapter adapter = session.CreateDataAdapter(cmd);
adapter.Fill(ds);
}
finally
{
if
(isSessionLocal)
{
session.CloseConnection();
}
}
htOutPutParameter =
new
Hashtable();
foreach
(IDataParameter parameter
in
cmd.Parameters)
{
if
(parameter.Direction == ParameterDirection.Output)
{
htOutPutParameter[parameter.ParameterName] = parameter.Value;
}
}
return
ds.Tables[0];
}
|
测试的存储过程如下:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
USE [TestDb]
GO
--根据id查询某人 并返回所有人中,最大体重,最小身高
CREATE
PROCEDURE
[dbo].[usp_GetPersonById]
@MaxWeight
float
output
,
@MinHeight
float
output
,
@Id
int
AS
BEGIN
SELECT
Id,
FirstName,
LastName,
Weight,
Height
FROM
Person
WHERE
Id=@Id
SET
@MaxWeight= (
SELECT
MAX
(Weight)
FROM
Person)
SET
@MinHeight= (
SELECT
MIN
(Height)
FROM
Person)
END
|
本文的示例测试通过,返回的结果如下:
从上图中,我们可以看到最大体重是200,最矮身高是177。
4、小结和注意点
a、返回ADO.NET对象的方法,iBatis拼接的SQL语句必须通过而不是熟悉的#符号连接,这个会有众所周知的SQL注入的风险。
b、我还没有实际尝试过最新版本的iBatis,对于较新的版本,不知道本文的方法还适不适用。
c、我参考这篇文章的时候,发现说有一个无法返回output参数的问题。我尝试着重现这个问题。功夫不负有心人,本文示例中我已经实现好了,给自己鼓励一下。