发布：2014/9/26 14:55:30作者：管理员 来源：本站 浏览次数：1291

这一章我们主要介绍一下IbatisNet里面的其它用法主要有以下几点：

     1，如何得到运行时ibatis.net动态生成的SQL语句？

     2，如何获取DbCommand？

     3，如何返回DataTable,DataSet?

     4,批量数据的插入……

首先看一下获取的sql语句的方法：

public virtual string GetSql(ISqlMapper sqlMap, string stateMentName, object paramObject)

        {

            string resultsql = string.Empty;

            try

            {

                IMappedStatement statement = sqlMap.GetMappedStatement(stateMentName);

                if (!sqlMap.IsSessionStarted)

                {

                    sqlMap.OpenConnection();

                }

                RequestScope scope = statement.Statement.Sql.GetRequestScope(statement, paramObject, sqlMap.LocalSession);

                resultsql = scope.PreparedStatement.PreparedSql;

            }

            catch (Exception ex)

            {

                resultsql = "获取SQL语句出现异常:" + ex.Message;

            }

            return resultsql;

        }

代码

 IMappedStatement statement = sqlMapper.GetMappedStatement(statementName); 获取MappedStatement对象

sqlMap.LocalSession 获取ISqlMapSession

RequestScope代表一个请求范围内所有的处理数据，例如mapped statement,要执行的IDbCommand,当前的ResultMap和ParameterMap等

看一下获取DbCommand的方法：

/// <summary>

        /// 获取DbCommand

        /// </summary>

        /// <param name="sqlMapper">ISqlMapper</param>

        /// <param name="statementName">statement的id</param>

        /// <param name="paramObject">sql语句的参数</param>

        /// <returns>DbCommand</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 cmd;

        }

代码

下面我们来看一下如何返回DataTable,DataSet?

/// <summary>

        /// 获取全部信息以DataTable的形式返回

        /// </summary>

        /// <returns>DataTable</returns>

        public DataTable GetDatatTable()

        {

            DataSet ds = new DataSet();

            string sql = GetSql(SqlMap, "SelectAllClasses", null);

            IDbCommand cmd = GetDbCommand(SqlMap, "SelectAllClasses", null);

            cmd.CommandType = CommandType.Text;

            cmd.CommandText = sql;

            IDbDataAdapter adapter = SqlMap.LocalSession.CreateDataAdapter(cmd);

            adapter.Fill(ds);

            return ds.Tables[0];

        }

代码

这个方法大家很熟悉吧，跟ado.net差不多吧。这就是把IDbCommand，sql语句提取出来单独执行一下（自我感觉有点啰嗦）；

下面是另外一种返回DataTable的方法：

/// <summary>

        /// 返回DataTable

        /// </summary>

        /// <param name="sqlMapper">ISqlMapper</param>

        /// <param name="statementName">statement的id</param>

        /// <param name="paramObject">sql语句的参数</param>

        /// <returns>DataTable</returns>

        protected virtual DataSet QueryForDataSet(ISqlMapper sqlMapper, string statementName, object paramObject)

        {

            DataSet ds = new DataSet();

            IMappedStatement statement = sqlMapper.GetMappedStatement(statementName);

            if (!sqlMapper.IsSessionStarted)

            {

                sqlMapper.OpenConnection();

            }

            RequestScope scope = statement.Statement.Sql.GetRequestScope(statement, paramObject, sqlMapper.LocalSession);

            statement.PreparedCommand.Create(scope, sqlMapper.LocalSession, statement.Statement, paramObject);

            IDbCommand cmd = GetDbCommand(sqlMapper, statementName, paramObject);//SQL text command

            sqlMapper.LocalSession.CreateDataAdapter(cmd).Fill(ds);

            return ds;

        }

代码

 好了  下面我们来看一下批量插入数据：

1，首先我们要给实体做好描述

namespace Model

{

    [Serializable]

    public class Calsses : Entity

    {

        private int calsses_id;

        /// <summary>

        /// 班级编号

        /// </summary>

        [TableColumnAttribute(Description = "该属性是真实表所对应的列名")]

        public int Calsses_id

        {

            get { return calsses_id; }

            set { calsses_id = value; }

        }

        private string classes_name;

        /// <summary>

        /// 班级名称

        /// </summary>

        [TableColumnAttribute(Description = "该属性是真实表所对应的列名")]

        public string Classes_name

        {

            get { return classes_name; }

            set { classes_name = value; }

        }

        private string classes_description;

        /// <summary>

        /// 班级描述

        /// </summary>

        [TableColumnAttribute(Description = "该属性是真实表所对应的列名")]

        public string Classes_description

        {

            get { return classes_description; }

            set { classes_description = value; }

        }

    }

}

代码

2， 将泛型集合变成DataTable

/// <summary>

        /// 将泛型变成DataTable

        /// </summary>

        /// <typeparam name="T"></typeparam>

        /// <param name="listModels">实体集合</param>

        /// <returns>转换后的DataTable</returns>

        public  DataTable CreateTable<T>(IList<T> listModels) where T : class

        {

            T model = default(T);

            IList<string> listProperties = CreateModelProperty<T>(model);

            DataTable dataTable = CreateTable(listProperties);

            BindingFlags bf = BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.Public | BindingFlags.Static;

            Type objType = typeof(T);

            PropertyInfo[] propInfoArr = objType.GetProperties(bf);

            foreach (T itemModel in listModels)

            {

                DataRow dataRow = dataTable.NewRow();

                foreach (PropertyInfo item in propInfoArr)

                {

                    string propName = item.Name;

                    if (listProperties.Contains(propName))

                    {

                        object value = item.GetValue(itemModel, null);

                        dataRow[propName] = value;

                    }

                }

                dataTable.Rows.Add(dataRow);

            }

            return dataTable;

        }

代码

3，执行插入命令

/// <summary>

        /// 执行插入命令

        /// </summary>

        /// <param name="connStr">sql连接字符串</param>

        /// <param name="tableName">表名称</param>

        /// <param name="dt">组装好的要批量导入的datatable</param>

        /// <returns></returns>

        protected virtual bool ExecuteInsertCommand(string connStr, string tableName, DataTable dt)

        {

            bool flag = false;

            try

            {

                using (System.Transactions.TransactionScope scope = new System.Transactions.TransactionScope())

                {

                    using (SqlConnection conn = new SqlConnection(connStr))

                    {

                        conn.Open();

                        using (SqlBulkCopy sbc = new SqlBulkCopy(conn))

                        {

                            sbc.DestinationTableName = tableName;

                            sbc.BatchSize = 50000;

                            sbc.BulkCopyTimeout = 180;

                            for (int i = 0; i < dt.Columns.Count; i++)

                            {

                                sbc.ColumnMappings.Add(dt.Columns[i].ColumnName,i);

                            }

                            sbc.WriteToServer(dt);

                            flag = true;

                            scope.Complete();

                        }

                    }

                }

            }

            catch (Exception ex)

            {

                throw ex;

            }

            return flag;

        }

代码

4，外部调用代码

/// <summary>

        /// 外部调用的批量插入的代码

        /// </summary>

        /// <typeparam name="M"></typeparam>

        /// <param name="listModels">泛型集合</param>

        /// <returns>是否插入成功</returns>

        public bool BatchInsert<M>(IList<M> listModels) where M : class

        {

            bool flag = false;

            try

            {

                string connStr = SqlMap.DataSource.ConnectionString;

                string tbName = typeof(M).Name;

                DataTable dt = CreateTable<M>(listModels);

                flag = ExecuteInsertCommand(connStr, tbName, dt);

            }

            catch

            {

                flag = false;

            }

            return flag;

        }

代码

上面的代码就不一一介绍了，有什么不懂的，可以google,也可以问我。

下面是我前台调用的代码

public void BindDataTable()

        {

            // gdv_list.DataSource = service.GetDatatTable();

            //gdv_list.DataBind();

            gdv_list.DataSource = service.GetDataTableOther();

            gdv_list.DataBind();

            IList<Calsses> list = new List<Calsses>();

            for (int i = 0; i < 5; i++)

            {

                Calsses cal = new Calsses();

                cal.Classes_name = "吴庭智初学者课堂" + i.ToString();

                cal.Classes_description = "吴庭智初学者课堂 InatisNet第" + i.ToString() + "章";

                list.Add(cal);

            }

            bool flag = service.InsertAll(list);

        }

代码

好了，今天就说到这儿吧，下一章主要说一下IbatisNet的缓存机制。希望大家共同思考，共同学习 进步

源码下载：http://download.csdn.net/detail/woaixiaozhi/5854593