Ibatisnet使用方法

通过NuGet添加引用包

添加provider.config

 

<?xml version="1.0" encoding="utf-8"?>
<providers 
xmlns="http://ibatis.apache.org/providers" 
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<clear/>
<provider 
    name="sqlServer1.0" 
    description="Microsoft SQL Server 7.0/2000, provider V1.0.3300.0 in framework .NET V1.0" 
    enabled="true" 
    assemblyName="System.Data, Version=1.0.3300.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" connectionClass="System.Data.SqlClient.SqlConnection" 
    commandClass="System.Data.SqlClient.SqlCommand" 
    parameterClass="System.Data.SqlClient.SqlParameter" 
    parameterDbTypeClass="System.Data.SqlDbType" 
    parameterDbTypeProperty="SqlDbType" 
    dataAdapterClass="System.Data.SqlClient.SqlDataAdapter" 
    commandBuilderClass="System.Data.SqlClient.SqlCommandBuilder" 
    usePositionalParameters="false" 
    useParameterPrefixInSql="true" 
    useParameterPrefixInParameter="true" 
    parameterPrefix="@"/>
<provider 
    name="sqlServer1.1" 
    description="Microsoft SQL Server 7.0/2000, provider V1.0.5000.0 in framework .NET V1.1" 
    enabled="true"
    default="true" 
    assemblyName="System.Data, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" connectionClass="System.Data.SqlClient.SqlConnection" 
    commandClass="System.Data.SqlClient.SqlCommand" 
    parameterClass="System.Data.SqlClient.SqlParameter" 
    parameterDbTypeClass="System.Data.SqlDbType" 
    parameterDbTypeProperty="SqlDbType" 
    dataAdapterClass="System.Data.SqlClient.SqlDataAdapter" 
    commandBuilderClass="System.Data.SqlClient.SqlCommandBuilder" 
    usePositionalParameters="false" 
    useParameterPrefixInSql="true" 
    useParameterPrefixInParameter="true" 
    parameterPrefix="@"/>
<provider
        name="sqlServer2.0"
        enabled="true"
        description="Microsoft SQL Server 2005, provider V2.0.50215.0 in framework .NET V2.0" 
        assemblyName="System.Data, Version=2.0.50215.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" 
        connectionClass="System.Data.SqlClient.SqlConnection" 
        commandClass="System.Data.SqlClient.SqlCommand"
        parameterClass="System.Data.SqlClient.SqlParameter"
        parameterDbTypeClass="System.Data.SqlDbType"
        parameterDbTypeProperty="SqlDbType"
        dataAdapterClass="System.Data.SqlClient.SqlDataAdapter"
        commandBuilderClass=" System.Data.SqlClient.SqlCommandBuilder"
        usePositionalParameters = "false"
        useParameterPrefixInSql = "true"
        useParameterPrefixInParameter = "true"    
        parameterPrefix="@"
        />    
<provider name="OleDb1.1" 
    description="OleDb, provider V1.0.5000.0 in framework .NET V1.1" 
    enabled="true"
    assemblyName="System.Data, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" connectionClass="System.Data.OleDb.OleDbConnection" 
    commandClass="System.Data.OleDb.OleDbCommand" 
    parameterClass="System.Data.OleDb.OleDbParameter" 
    parameterDbTypeClass="System.Data.OleDb.OleDbType" 
    parameterDbTypeProperty="OleDbType" 
    dataAdapterClass="System.Data.OleDb.OleDbDataAdapter" 
    commandBuilderClass="System.Data.OleDb.OleDbCommandBuilder" 
    usePositionalParameters="true" 
    useParameterPrefixInSql="false" 
    useParameterPrefixInParameter="false" 
    parameterPrefix=""/>
<provider 
    name="Odbc1.1" 
    description="Odbc, provider V1.0.5000.0 in framework .NET V1.1" 
    enabled="true" 
    assemblyName="System.Data, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" connectionClass="System.Data.Odbc.OdbcConnection" 
    commandClass="System.Data.Odbc.OdbcCommand" 
    parameterClass="System.Data.Odbc.OdbcParameter" 
    parameterDbTypeClass="System.Data.Odbc.OdbcType" 
    parameterDbTypeProperty="OdbcType" 
    dataAdapterClass="System.Data.Odbc.OdbcDataAdapter" 
    commandBuilderClass="System.Data.Odbc.OdbcCommandBuilder" 
    usePositionalParameters="true" 
    useParameterPrefixInSql="false" 
    useParameterPrefixInParameter="false" 
    parameterPrefix="@"/>
<provider 
    name="oracle9.2" 
    description="Oracle, Oracle provider V9.2.0.401" 
    enabled="false" 
    assemblyName="Oracle.DataAccess, Version=9.2.0.401, Culture=neutral, PublicKeyToken=89b483f429c47342" connectionClass="Oracle.DataAccess.Client.OracleConnection" 
    commandClass="Oracle.DataAccess.Client.OracleCommand" 
    parameterClass="Oracle.DataAccess.Client.OracleParameter" 
    parameterDbTypeClass="Oracle.DataAccess.Client.OracleDbType" 
    parameterDbTypeProperty="OracleDbType" 
    dataAdapterClass="Oracle.DataAccess.Client.OracleDataAdapter" 
    commandBuilderClass="Oracle.DataAccess.Client.OracleCommandBuilder" 
    usePositionalParameters="false"
    useParameterPrefixInSql="true" 
    useParameterPrefixInParameter="false" 
    parameterPrefix=":" 
    useDeriveParameters="false"/>
<provider 
    name="oracle10.1" 
    description="Oracle, oracle provider V10.1.0.301"
    enabled="false" 
    assemblyName="Oracle.DataAccess, Version=10.1.0.301, Culture=neutral, PublicKeyToken=89b483f429c47342" connectionClass="Oracle.DataAccess.Client.OracleConnection" 
    commandClass="Oracle.DataAccess.Client.OracleCommand" 
    parameterClass="Oracle.DataAccess.Client.OracleParameter" 
    parameterDbTypeClass="Oracle.DataAccess.Client.OracleDbType" 
    parameterDbTypeProperty="OracleDbType" 
    dataAdapterClass="Oracle.DataAccess.Client.OracleDataAdapter" 
    commandBuilderClass="Oracle.DataAccess.Client.OracleCommandBuilder" 
    usePositionalParameters="true" 
    useParameterPrefixInSql="true"
    useParameterPrefixInParameter="true" 
    parameterPrefix=":" 
    useDeriveParameters="false"/>
<provider 
    name="oracleClient1.0" 
    description="Oracle, Microsoft provider V1.0.5000.0" 
    enabled="false" 
    assemblyName="System.Data.OracleClient, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" connectionClass="System.Data.OracleClient.OracleConnection" 
    commandClass="System.Data.OracleClient.OracleCommand" 
    parameterClass="System.Data.OracleClient.OracleParameter" 
    parameterDbTypeClass="System.Data.OracleClient.OracleType" 
    parameterDbTypeProperty="OracleType" 
    dataAdapterClass="System.Data.OracleClient.OracleDataAdapter" 
    commandBuilderClass="System.Data.OracleClient.OracleCommandBuilder" 
    usePositionalParameters="false" 
    useParameterPrefixInSql="true" 
    useParameterPrefixInParameter="false" 
    parameterPrefix=":"/>
<provider 
    name="ByteFx" 
    description="MySQL, ByteFx provider V0.7.6.15073" 
    enabled="false" 
    assemblyName="ByteFX.MySqlClient, Version=0.7.6.15073, Culture=neutral, PublicKeyToken=f2fef6fed1732fc1" connectionClass="ByteFX.Data.MySqlClient.MySqlConnection" 
    commandClass="ByteFX.Data.MySqlClient.MySqlCommand" 
    parameterClass="ByteFX.Data.MySqlClient.MySqlParameter" 
    parameterDbTypeClass="ByteFX.Data.MySqlClient.MySqlDbType" 
    parameterDbTypeProperty="MySqlDbType" 
    dataAdapterClass="ByteFX.Data.MySqlClient.MySqlDataAdapter" 
    commandBuilderClass="ByteFX.Data.MySqlClient.MySqlCommandBuilder" 
    usePositionalParameters="false" 
    useParameterPrefixInSql="true" 
    useParameterPrefixInParameter="true" 
    parameterPrefix="@"/>
<provider 
    name="MySql" 
    description="MySQL, MySQL provider V1.0.5.13785" 
    enabled="false" 
    assemblyName="MySql.Data, Version=1.0.5.13785, Culture=neutral, PublicKeyToken=c5687fc88969c44d" connectionClass="MySql.Data.MySqlClient.MySqlConnection" 
    commandClass="MySql.Data.MySqlClient.MySqlCommand" 
    parameterClass="MySql.Data.MySqlClient.MySqlParameter" 
    parameterDbTypeClass="MySql.Data.MySqlClient.MySqlDbType" 
    parameterDbTypeProperty="MySqlDbType" 
    dataAdapterClass="MySql.Data.MySqlClient.MySqlDataAdapter" 
    commandBuilderClass="MySql.Data.MySqlClient.MySqlCommandBuilder" 
    usePositionalParameters="false" 
    useParameterPrefixInSql="true" 
    useParameterPrefixInParameter="true" 
    parameterPrefix="?"/>
<provider name="SQLite3"
    description="SQLite, SQLite.NET provider V0.21.1869.3794"
    enabled="false"
    assemblyName="SQLite.NET, Version=0.21.1869.3794, Culture=neutral, PublicKeyToken=c273bd375e695f9c" 
    connectionClass="Finisar.SQLite.SQLiteConnection"
    commandClass="Finisar.SQLite.SQLiteCommand"
    parameterClass="Finisar.SQLite.SQLiteParameter"
    parameterDbTypeClass="System.Data.DbType, System.Data"
    parameterDbTypeProperty="DbType"
    dataAdapterClass="Finisar.SQLite.SQLiteDataAdapter"
    commandBuilderClass="Finisar.SQLite.SQLiteCommandBuilder"
    usePositionalParameters="false"
    useParameterPrefixInSql="true"
    useParameterPrefixInParameter="true"
    parameterPrefix="@"
    setDbParameterPrecision="false"
    setDbParameterScale="false"/>
<provider
    name="Firebird1.7" 
    description="Firebird, Firebird SQL .NET provider V1.7.0.33200" 
    enabled="false" 
    assemblyName="FirebirdSql.Data.Firebird, Version=1.7.0.33200, Culture=neutral, PublicKeyToken=fa843d180294369d" connectionClass="FirebirdSql.Data.Firebird.FbConnection" 
    commandClass="FirebirdSql.Data.Firebird.FbCommand" 
    parameterClass="FirebirdSql.Data.Firebird.FbParameter" 
    parameterDbTypeClass="FirebirdSql.Data.Firebird.FbDbType" 
    parameterDbTypeProperty="FbDbType" 
    dataAdapterClass="FirebirdSql.Data.Firebird.FbDataAdapter" 
    commandBuilderClass="FirebirdSql.Data.Firebird.FbCommandBuilder" 
    usePositionalParameters="false" 
    useParameterPrefixInSql="true" 
    useParameterPrefixInParameter="true" 
    parameterPrefix="@"/>
<provider
    name="PostgreSql0.7" 
    description="PostgreSql, Npgsql provider V0.7.0.0" 
    enabled="false" 
    assemblyName="Npgsql, Version=0.7.0.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7" 
    connectionClass="Npgsql.NpgsqlConnection" 
    commandClass="Npgsql.NpgsqlCommand" 
    parameterClass="Npgsql.NpgsqlParameter" 
    parameterDbTypeClass="NpgsqlTypes.NpgsqlDbType" 
    parameterDbTypeProperty="NpgsqlDbType" 
    dataAdapterClass="Npgsql.NpgsqlDataAdapter" 
    commandBuilderClass="Npgsql.NpgsqlCommandBuilder" 
    usePositionalParameters="false" 
    useParameterPrefixInSql="true" 
    useParameterPrefixInParameter="true" 
    parameterPrefix=":"/>
<provider
    name="PostgreSql0.7.1" 
    description="PostgreSql, Npgsql provider V0.7.1.0" 
    enabled="false" 
    assemblyName="Npgsql, Version=0.7.1.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7" 
    connectionClass="Npgsql.NpgsqlConnection" 
    commandClass="Npgsql.NpgsqlCommand" 
    parameterClass="Npgsql.NpgsqlParameter" 
    parameterDbTypeClass="NpgsqlTypes.NpgsqlDbType" 
    parameterDbTypeProperty="NpgsqlDbType" 
    dataAdapterClass="Npgsql.NpgsqlDataAdapter" 
    commandBuilderClass="Npgsql.NpgsqlCommandBuilder" 
    usePositionalParameters="false" 
    useParameterPrefixInSql="true" 
    useParameterPrefixInParameter="true" 
    parameterPrefix=":"/>    
<provider 
    name="iDb2.10" 
    description="IBM DB2 Provider, V 10.0" 
    enabled="false" 
    assemblyName="IBM.Data.DB2.iSeries, Version=10.0.0.0,Culture=neutral, PublicKeyToken=9cdb2ebfb1f93a26, Custom=null" connectionClass="IBM.Data.DB2.iSeries.iDB2Connection" 
    commandClass="IBM.Data.DB2.iSeries.iDB2Command" 
    parameterClass="IBM.Data.DB2.iSeries.iDB2Parameter" 
    parameterDbTypeClass="IBM.Data.DB2.iSeries.iDB2DbType" 
    parameterDbTypeProperty="iDB2DbType" 
    dataAdapterClass="IBM.Data.DB2.iSeries.iDB2DataAdapter" 
    commandBuilderClass="IBM.Data.DB2.iSeries.iDB2CommandBuilder" 
    usePositionalParameters="true" 
    useParameterPrefixInSql="false" 
    useParameterPrefixInParameter="false" 
    parameterPrefix=""/>
<provider 
    name="Informix" 
    description="Informix NET Provider, 2.81.0.0" 
    enabled="false" 
    assemblyName="IBM.Data.Informix, Version=2.81.0.0, Culture=neutral, PublicKeyToken=7c307b91aa13d208" 
    connectionClass="IBM.Data.Informix.IfxConnection" 
    commandClass="IBM.Data.Informix.IfxCommand" 
    parameterClass="IBM.Data.Informix.IfxParameter" 
    parameterDbTypeClass="IBM.Data.Informix.IfxType" 
    parameterDbTypeProperty="IfxType" 
    dataAdapterClass="IBM.Data.Informix.IfxDataAdapter" 
    commandBuilderClass="IBM.Data.Informix.IfxCommandBuilder" 
    usePositionalParameters = "true" 
    useParameterPrefixInSql = "false" 
    useParameterPrefixInParameter = "false" 
    useDeriveParameters="false" 
    />
</providers>

 

添加数据库配置映射文件

<?xml version="1.0" encoding="utf-8"?>
<sqlMapConfig xmlns="http://ibatis.apache.org/dataMapper" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" >
  <settings>
    <setting useStatementNamespaces="false"/>
    <setting cacheModelsEnabled="true"/>
    <setting validateSqlMap="false"/>
  </settings>
  <!-- 加载数据库驱动 -->
  <providers embedded="providers.config,ConsoleApplication1"/>
  <database>
    <provider name="sqlServer2.0"/>
    <!--<dataSource name="iBatisNet" connectionString="data source=${datasource};database=${database};user id=${userid};password=${password};connection reset=false;connection lifetime=5; min pool size=1; max pool size=50"/>-->
    <dataSource name="iBatisNet" connectionString="data source=.;database=Test;user id=sa;password=sa;connection reset=false;connection lifetime=5; min pool size=1; max pool size=50"/>
  </database>

  <sqlMaps>
    <!-- 所有的xml文件-->
    <sqlMap embedded="Person.xml,ConsoleApplication1"/>
    <sqlMap embedded="Book.xml,ConsoleApplication1"/>
  </sqlMaps>
</sqlMapConfig>

providers embedded :使用的provider数据库驱动配置文件

provider name:provider使用的配置文件中的数据库名,还有连接字符串

 

sqlmaps下制定实体映射的xml文件

<sqlMap embedded="Person.xml,ConsoleApplication1"/>

Person.xml为实体类映射xml文件

ConsoleApplication1:表示类库名称

 

配置实体Xml的映射

<?xml version="1.0" encoding="utf-8" ?>
<sqlMap namespace="Person" xmlns="http://ibatis.apache.org/mapping"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" >
  <!--配置你的实体类的别名，一般的都是一个实体类对应一个xml-->
  <alias>
    <typeAlias alias="Person" type="ConsoleApplication1.Person,ConsoleApplication1" />
  </alias>

  <!--找到你的实体类与数据库字段的对应关系，如果数据库字段与实体类一样 这个可以省略不要-->
  <resultMaps>
    <resultMap id="SelectAllResult" class="Person">
      <result property="Id" column="calsses_id" />
      <result property="Name" column="classes_name" />
      <result property="Description" column="classes_description" />
    </resultMap>
  </resultMaps>
  <statements>
    <!--查询操作 id是你在操作类（也就是我的项目中的service类）中要用到的，resultMap表示返回结果的类型-->
    <select id="SelectAllPerson" resultMap="SelectAllResult">
      select *
      from Person
    </select>
  </statements>
</sqlMap>

sqlmap 命名空间名字声明为实体名字

<alias>声明xml映射的实体类ConsoleApplication1.Person,ConsoleApplication1

<resultMaps> 配置映射实体与数据库对应字段，如果相同可以不配置

<statements> 配置实体类操作

 

获取ISqlMapper对象

public static ISqlMapper SqlMap;
        private static readonly object syncObj = new object();
        static void Main(string[] args)
        {
            if (SqlMap == null) 
          {
              lock (syncObj) 
              {
                  if (SqlMap == null)
                  {
                      Assembly assembly = Assembly.Load("ConsoleApplication1");
                      Stream stream = assembly.GetManifestResourceStream("ConsoleApplication1.db_test.config");
                      DomSqlMapBuilder builder = new DomSqlMapBuilder();
                      SqlMap = builder.Configure(stream);
                  }
              }
          }

 

IList<Book> persons = SqlMap.QueryForList<Book>("SelectAllBook", null);
            foreach (var person in persons)
            {
                Console.WriteLine(person.Bname);
            }
            Console.ReadLine();