这一章我们主要介绍一下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 ///2 /// 获取DbCommand 3 /// 4 /// ISqlMapper 5 /// statement的id 6 /// sql语句的参数 7 ///DbCommand 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 else19 {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 ///2 /// 获取全部信息以DataTable的形式返回 3 /// 4 ///DataTable 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 ///2 /// 返回DataTable 3 /// 4 /// ISqlMapper 5 /// statement的id 6 /// sql语句的参数 7 ///DataTable 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 command21 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 /// 8 /// 班级编号 9 /// 10 [TableColumnAttribute(Description = "该属性是真实表所对应的列名")]11 public int Calsses_id12 {13 get { return calsses_id; }14 set { calsses_id = value; }15 }16 private string classes_name;17 /// 18 /// 班级名称19 /// 20 [TableColumnAttribute(Description = "该属性是真实表所对应的列名")]21 public string Classes_name22 {23 get { return classes_name; }24 set { classes_name = value; }25 }26 private string classes_description;27 /// 28 /// 班级描述29 /// 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 ///2 /// 将泛型变成DataTable 3 /// 4 ///5 /// 实体集合 6 /// 转换后的DataTable 7 public DataTable CreateTable(IList listModels) where T : class 8 { 9 T model = default(T);10 IList listProperties = CreateModelProperty (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 ///2 /// 执行插入命令 3 /// 4 /// sql连接字符串 5 /// 表名称 6 /// 组装好的要批量导入的datatable 7 ///8 protected virtual bool ExecuteInsertCommand(string connStr, string tableName, DataTable dt) 9 {10 bool flag = false;11 try12 {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 ///2 /// 外部调用的批量插入的代码 3 /// 4 ///5 /// 泛型集合 6 /// 是否插入成功 7 public bool BatchInsert(IList listModels) where M : class 8 { 9 bool flag = false;10 try11 {12 string connStr = SqlMap.DataSource.ConnectionString;13 string tbName = typeof(M).Name;14 DataTable dt = CreateTable (listModels);15 flag = ExecuteInsertCommand(connStr, tbName, dt);16 }17 catch18 {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 list = new List (); 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的缓存机制。希望大家共同思考，共同学习 进步
源码下载：