跟初学者学习IbatisNet第四篇

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

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

2，如何获取DbCommand？

3，如何返回DataTable,DataSet?

4,批量数据的插入……

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

 1  public virtual string GetSql(ISqlMapper sqlMap, string stateMentName, object paramObject)
 2         {
 3             string resultsql = string.Empty;
 4             try
 5             {
 6                 IMappedStatement statement = sqlMap.GetMappedStatement(stateMentName);
 7                 if (!sqlMap.IsSessionStarted)
 8                 {
 9                     sqlMap.OpenConnection();
10                 }
11                 RequestScope scope = statement.Statement.Sql.GetRequestScope(statement, paramObject, sqlMap.LocalSession);
12                 resultsql = scope.PreparedStatement.PreparedSql;
13             }
14             catch (Exception ex)
15             {
16                 resultsql = "获取SQL语句出现异常:" + ex.Message;
17             }
18             return resultsql;
19         }

代码

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

sqlMap.LocalSession 获取ISqlMapSession

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

看一下获取DbCommand的方法：

 1 /// <summary>
 2         /// 获取DbCommand
 3         /// </summary>
 4         /// <param name="sqlMapper">ISqlMapper</param>
 5         /// <param name="statementName">statement的id</param>
 6         /// <param name="paramObject">sql语句的参数</param>
 7         /// <returns>DbCommand</returns>
 8         protected virtual IDbCommand GetDbCommand(ISqlMapper sqlMapper, string statementName, object paramObject)
 9         {
10             IStatement statement = sqlMapper.GetMappedStatement(statementName).Statement;
11             IMappedStatement mapStatement = sqlMapper.GetMappedStatement(statementName);
12             ISqlMapSession session = new SqlMapSession(sqlMapper);
13 
14             if (sqlMapper.LocalSession != null)
15             {
16                 session = sqlMapper.LocalSession;
17             }
18             else
19             {
20                 session = sqlMapper.OpenConnection();
21             }
22 
23             RequestScope request = statement.Sql.GetRequestScope(mapStatement, paramObject, session);
24             mapStatement.PreparedCommand.Create(request, session as ISqlMapSession, statement, paramObject);
25             IDbCommand cmd = session.CreateCommand(CommandType.Text);
26             cmd.CommandText = request.IDbCommand.CommandText;
27             return cmd;
28         }

代码

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

 1   /// <summary>
 2         /// 获取全部信息以DataTable的形式返回
 3         /// </summary>
 4         /// <returns>DataTable</returns>
 5         public DataTable GetDatatTable()
 6         {
 7             DataSet ds = new DataSet();
 8             string sql = GetSql(SqlMap, "SelectAllClasses", null);
 9             IDbCommand cmd = GetDbCommand(SqlMap, "SelectAllClasses", null);
10             cmd.CommandType = CommandType.Text;
11             cmd.CommandText = sql;
12             IDbDataAdapter adapter = SqlMap.LocalSession.CreateDataAdapter(cmd);
13             adapter.Fill(ds);
14             return ds.Tables[0];
15         }

代码

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

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

 1      /// <summary>
 2         /// 返回DataTable
 3         /// </summary>
 4         /// <param name="sqlMapper">ISqlMapper</param>
 5         /// <param name="statementName">statement的id</param>
 6         /// <param name="paramObject">sql语句的参数</param>
 7         /// <returns>DataTable</returns>
 8         protected virtual DataSet QueryForDataSet(ISqlMapper sqlMapper, string statementName, object paramObject)
 9         {
10             DataSet ds = new DataSet();
11             IMappedStatement statement = sqlMapper.GetMappedStatement(statementName);
12             if (!sqlMapper.IsSessionStarted)
13             {
14                 sqlMapper.OpenConnection();
15             }
16             RequestScope scope = statement.Statement.Sql.GetRequestScope(statement, paramObject, sqlMapper.LocalSession);
17 
18             statement.PreparedCommand.Create(scope, sqlMapper.LocalSession, statement.Statement, paramObject);
19 
20             IDbCommand cmd = GetDbCommand(sqlMapper, statementName, paramObject);//SQL text command
21             sqlMapper.LocalSession.CreateDataAdapter(cmd).Fill(ds);
22             return ds;
23         }

代码

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

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

 1 namespace Model
 2 {
 3     [Serializable]
 4     public class Calsses : Entity
 5     {
 6         private int calsses_id;
 7         /// <summary>
 8         /// 班级编号
 9         /// </summary>
10         [TableColumnAttribute(Description = "该属性是真实表所对应的列名")]
11         public int Calsses_id
12         {
13             get { return calsses_id; }
14             set { calsses_id = value; }
15         }
16         private string classes_name;
17         /// <summary>
18         /// 班级名称
19         /// </summary>
20         [TableColumnAttribute(Description = "该属性是真实表所对应的列名")]
21         public string Classes_name
22         {
23             get { return classes_name; }
24             set { classes_name = value; }
25         }
26         private string classes_description;
27         /// <summary>
28         /// 班级描述
29         /// </summary>
30         [TableColumnAttribute(Description = "该属性是真实表所对应的列名")]
31         public string Classes_description 
32         {
33             get { return classes_description; }
34             set { classes_description = value; }
35         }
36     }
37 }

代码

2，将泛型集合变成DataTable

 1  /// <summary>
 2         /// 将泛型变成DataTable
 3         /// </summary>
 4         /// <typeparam name="T"></typeparam>
 5         /// <param name="listModels">实体集合</param>
 6         /// <returns>转换后的DataTable</returns>
 7         public  DataTable CreateTable<T>(IList<T> listModels) where T : class
 8         {
 9             T model = default(T);
10             IList<string> listProperties = CreateModelProperty<T>(model);
11             DataTable dataTable = CreateTable(listProperties);
12             BindingFlags bf = BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.Public | BindingFlags.Static;
13             Type objType = typeof(T);
14             PropertyInfo[] propInfoArr = objType.GetProperties(bf);
15             foreach (T itemModel in listModels)
16             {
17                 DataRow dataRow = dataTable.NewRow();
18                 foreach (PropertyInfo item in propInfoArr)
19                 {
20                     string propName = item.Name;
21                     if (listProperties.Contains(propName))
22                     {
23                         object value = item.GetValue(itemModel, null);
24                         dataRow[propName] = value;
25                     }
26                 }
27                 dataTable.Rows.Add(dataRow);
28             }
29             return dataTable;
30         }

代码

3，执行插入命令

 1  /// <summary>
 2         /// 执行插入命令
 3         /// </summary>
 4         /// <param name="connStr">sql连接字符串</param>
 5         /// <param name="tableName">表名称</param>
 6         /// <param name="dt">组装好的要批量导入的datatable</param>
 7         /// <returns></returns>
 8         protected virtual bool ExecuteInsertCommand(string connStr, string tableName, DataTable dt)
 9         {
10             bool flag = false;
11             try
12             {
13                 using (System.Transactions.TransactionScope scope = new System.Transactions.TransactionScope())
14                 {
15                     using (SqlConnection conn = new SqlConnection(connStr))
16                     {
17                         conn.Open();
18                         using (SqlBulkCopy sbc = new SqlBulkCopy(conn))
19                         {
20                             sbc.DestinationTableName = tableName;
21                             sbc.BatchSize = 50000;
22                             sbc.BulkCopyTimeout = 180;
23                             for (int i = 0; i < dt.Columns.Count; i++)
24                             {
25                                 sbc.ColumnMappings.Add(dt.Columns[i].ColumnName,i);
26                             }
27                             sbc.WriteToServer(dt);
28                             flag = true;
29                             scope.Complete();
30                         }
31                     }
32                 }
33             }
34             catch (Exception ex)
35             {
36                 throw ex;
37             }
38             return flag;
39         }

代码

4，外部调用代码

 1  /// <summary>
 2         /// 外部调用的批量插入的代码
 3         /// </summary>
 4         /// <typeparam name="M"></typeparam>
 5         /// <param name="listModels">泛型集合</param>
 6         /// <returns>是否插入成功</returns>
 7         public bool BatchInsert<M>(IList<M> listModels) where M : class
 8         {
 9             bool flag = false;
10             try
11             {
12                 string connStr = SqlMap.DataSource.ConnectionString;
13                 string tbName = typeof(M).Name;
14                 DataTable dt = CreateTable<M>(listModels);
15                 flag = ExecuteInsertCommand(connStr, tbName, dt);
16             }
17             catch
18             {
19                 flag = false;
20             }
21             return flag;
22         }

代码

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

下面是我前台调用的代码

 1  public void BindDataTable()
 2         {
 3             // gdv_list.DataSource = service.GetDatatTable();
 4             //gdv_list.DataBind();
 5             gdv_list.DataSource = service.GetDataTableOther();
 6             gdv_list.DataBind();
 7             IList<Calsses> list = new List<Calsses>();
 8             for (int i = 0; i < 5; i++)
 9             {
10                 Calsses cal = new Calsses();
11                 cal.Classes_name = "吴庭智初学者课堂" + i.ToString();
12                 cal.Classes_description = "吴庭智初学者课堂 InatisNet第" + i.ToString() + "章";
13                 list.Add(cal);
14             }
15             bool flag = service.InsertAll(list);
16         }

代码

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

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