【www.gdgbn.com--面向对象编程】

.net c# datatable和dataset对象详解

a、对于1.6.1之前的版本，获得命令的方式可以通过requestscope的idbcommand属性，但是1.6.1版本的idbcommand属性返回的是ibatisnet.datamapper.commands.dbcommanddecorator对象，您可以注释代码验证一下。

b、网上有些文章贴的方法返回的dbcommand对象都是对于拼接sql语句而言，没有实现获取存储过程的dbcommand（有参数无参数的都要考虑）。本文在原有资料的基础上，尝试着做出改进，目前支持sql语句和存储过程。

///

/// 获取dbcommand,主要是针对存储过程
///

///
///
/// 参数
/// 参数字段
/// parameterdirection字典
///
///
protected virtual idbcommand getdbcommand(isqlmapper sqlmapper, string statementname, object paramobject, idictionary dictparam, idictionary dictparmdirection, commandtype cmdtype)
{
    if (cmdtype == commandtype.text)
    {
        return getdbcommand(sqlmapper, statementname, paramobject);
    }

    istatement statement = sqlmapper.getmappedstatement(statementname).statement;
    imappedstatement maps教程tatement = 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;
}

  返回dataset对象

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配置：

 
  where 1=1 and id=$id$

客户端的调用：

 int id = 1;
dataset ds = servicefactory.createpersonservice().getdsperson(id);
console.writeline(ds.getxml());

3、返回datatable对象
通过含output参数的存储过程

///

  /// 查询返回datatable，对于包括output参数的存储过程同样适用
  ///

  ///
  ///
  /// 参数
  /// 参数字典
  /// parameterdirection字典
  /// 返回的output参数值哈希表
  ///
  protected virtual datatable queryfordatatable(isqlmapper sqlmapper, string statementname, object paramobject, idictionary dictparam, idictionary 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];
  }

  测试的存储过程如下：

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

本文来源：http://www.gdgbn.com/jsp/28568/