用IBatisNet实现简单的CRUD FOR .NET 2.0（附源码）

用IBatisNet实现简单的CRUD FOR .NET 2.0（附源码）

   什么是IBatisNet就不多说了，上来就做，用visual studio 2005

   一、新建一个数据库
   

CREATE   TABLE   [ dbo ] . [ users ]  (
     [ LogonID ]   [ varchar ]  ( 100 ) COLLATE Chinese_PRC_CI_AS  NOT   NULL  ,
     [ Password ]   [ varchar ]  ( 100 ) COLLATE Chinese_PRC_CI_AS  NULL  ,
     [ Name ]   [ varchar ]  ( 100 ) COLLATE Chinese_PRC_CI_AS  NULL  ,
     [ EmailAddress ]   [ varchar ]  ( 100 ) COLLATE Chinese_PRC_CI_AS  NULL  ,
     CONSTRAINT   [ PK_users ]   PRIMARY   KEY    CLUSTERED  
    (
         [ LogonID ]
    )
) 

   
   二、添加引用
   新建个web site，把IBatisNet.Common.dll，IBatisNet.DataMapper.dll，Castle.DynamicProxy.dll放到bin里，只引用IBatisNet.DataMapper.dll就行了


   三、配置文件

      1、 providers.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"
        enabled ="false"
        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"
        enabled ="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 ="OleDb1.1"  
        default ="false"         
        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"  
        enabled ="false"  
        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"
        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 ="System.Data.Client.OracleDbType"
        parameterDbTypeProperty ="OracleDbType"
        dataAdapterClass ="Oracle.DataAccess.Client.OracleDataAdapter"
        commandBuilderClass ="Oracle.DataAccess.Client.OracleCommandBuilder"
        usePositionalParameters  = "false"
        useParameterPrefixInSql  = "true"
        useParameterPrefixInParameter  = "false"
        parameterPrefix =":"  
     />
     < provider
         name ="ByteFx"
        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 ="@"
     />
</ providers >
    

      怒长呀，用那个数据库，就把哪个的“provider”里的“enable”设置成“true”就行了。


      2、properties.config

<? xml version="1.0" encoding="utf-8"  ?>  
< settings >
     < add  key ="provider"  value ="sqlServer1.1"   />
     < add 
         key ="connectionString"  
        value ="server=.;database=llcode;uid=sa;pwd=123"   />
</ settings >

      一看就明白呀。provider指定是.net framework 1.1环境下sqlserver 2000；connectionString是数据库连接串。


      3、sqlmap.config

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

     < properties  resource ="properties.config" />
   
     < settings >
         < setting  useStatementNamespaces ="false" />
         < setting  cacheModelsEnabled ="true" />
     </ settings >
    
     < database >     
         < provider  name ="${provider}" />
         < dataSource  name ="IBatisNetTextDataSource"   connectionString ="${connectionString}" />
     </ database >

     < sqlMaps >
         < sqlMap  resource ="test.xml" />
     </ sqlMaps >

</ sqlMapConfig >

   
      a)“<properties resource="properties.config"/>”就是把properties.config拿过来。
      b)“${................}”就是取key的value
      c)“<sqlMap resource="test.xml"/>”后面会写这个xml。


      4、test.xml

<? xml version="1.0" encoding="utf-8"  ?>  

< sqlMap  namespace ="model"  xmlns ="http://ibatis.apache.org/mapping"  xmlns:xsi ="http://www.w3.org/2001/XMLSchema-instance" >  

     <!--  XML "behind" document for the People service class.  -->

     < alias >
         < typeAlias  alias ="model"  type ="Test.model"   />
     </ alias >
    
     < resultMaps >
         < resultMap  id ="SelectResult"  class ="model" >
             < result  property ="LogonID"  column ="LogonID"   />
             < result  property ="Name"  column ="Name"   />
             < result  property ="Password"  column ="Password"   />
             < result  property ="EmailAddress"  column ="EmailAddress"   />
         </ resultMap >
     </ resultMaps >
    
     < statements >
    
         < select  id ="Select"  parameterClass ="string"  resultMap ="SelectResult" >
            select
            *
            from users
             < dynamic  prepend ="WHERE" >
                 < isParameterPresent >
          LogonID= #value#
         </ isParameterPresent >
             </ dynamic >
         </ select >

     < insert  id ="Insert"  parameterClass ="model"  resultClass ="int" >
      insert into users
      (LogonID, Name, Password, EmailAddress)
      values
      (#LogonID#, #Name#, #Password#, #EmailAddress#)
     </ insert >

     < update  id ="Update"  parameterClass ="model"  resultClass ="int" >
      update users set
      Name = #Name#,
      Password = #Password#,
      EmailAddress = #EmailAddress#
      where LogonID = #LogonID#
     </ update >

     < delete  id ="Delete"  parameterClass ="string" >
      delete from users
      where logonID = #value#
     </ delete >
    
     </ statements >
    
</ sqlMap >

      a)“<typeAlias alias="model" type="Test.model" />”type指的是实体类
      b)“<resultMaps />”数据库映射过来的，property实体类属性，column数据库例名
      c)“<statements />”就是sql语句了，“#.........#”夹着的就是参数


   四、model.cs（实体层）

using  System;
using  System.Data;
using  System.Configuration;
using  System.Web;
using  System.Web.Security;
using  System.Web.UI;
using  System.Web.UI.WebControls;
using  System.Web.UI.WebControls.WebParts;
using  System.Web.UI.HtmlControls;


namespace  Test
{
     ///   <summary>
     ///  Summary description for model
     ///   </summary>
     public   class  model
    {
         public  model()
        {
             //
             //  TODO: Add constructor logic here
             //
        }

         private   string  _LogonID;
         public   string  LogonID
        {
             get  {  return  _LogonID; }
             set  { _LogonID  =  value; }
        }

         private   string  _Name;
         public   string  Name
        {
             get  {  return  _Name; }
             set  { _Name  =  value; }
        }

         private   string  _Password;
         public   string  Password
        {
             get  {  return  _Password; }
             set  { _Password  =  value; }
        }

         private   string  _EmailAddress;
         public   string  EmailAddress
        {
             get  {  return  _EmailAddress; }
             set  { _EmailAddress  =  value; }
        }


    }
}

     闲麻烦，找个代码生成工具就ok了

   
   五、crud.cs（实现CRUD的方法 ）

using  System;
using  System.Data;
using  System.Configuration;
using  System.Web;
using  System.Web.Security;
using  System.Web.UI;
using  System.Web.UI.WebControls;
using  System.Web.UI.WebControls.WebParts;
using  System.Web.UI.HtmlControls;

using  System.Collections;

using  IBatisNet.DataMapper;

namespace  Test
{
     ///   <summary>
     ///  Summary description for crud
     ///   </summary>
     public   class  crud
    {
         public  crud()
        {
             //
             //  TODO: Add constructor logic here
             //
        }

         public  IList SelectAll()
        {
             return  Mapper.Instance().QueryForList( " Select " ,  null );
        }

         public  IList SelectOne( string  uid)
        {
             return  Mapper.Instance().QueryForList( " Select " , uid);
        }

         public   string  Insert(model m)
        {
             if  (((IList)Mapper.Instance().QueryForList( " Select " , m.LogonID)).Count  ==   0 )
            {
                Mapper.Instance().Insert( " Insert " , m);

                 return   "" ;
            }
             else
            {
                 return   " 用户名重复！ " ;
            }
             //  Insert is designed so that it can return the new key
             //  but we are not utilizing that feature here
        }

         public   int  Delete( string  uid)
        {
             return  Mapper.Instance().Delete( " Delete " , uid);
        }

         public   int  Update(model m)
        {
             return  Mapper.Instance().Update( " Update " , m);
        }

    }
}

   比较好明白，第一个参数对应test.xml里的statements里的那些节点；第二个参数就是sql语言的参数，参数多的话就用实体类传递。


   六、webui层

      1、Default.aspx

<% @ Page Language = " C# "  AutoEventWireup = " true "   CodeFile = " Default.aspx.cs "  Inherits = " _Default "   %>

<! DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd" >

< html  xmlns ="http://www.w3.org/1999/xhtml"   >
< head  runat ="server" >
     < title > IBatisNet测试 </ title >
     < style >
        table,body { font-size : 12px }
     </ style >
</ head >
< body >
     < form  id ="form1"  runat ="server" >
     < div >
    
         < asp:Panel  ID ="pnlAdd"  runat ="server" >
    
             < asp:TextBox  ID ="txtLogonID"  runat ="server" ></ asp:TextBox >< br  />
             < asp:TextBox  ID ="txtPassword"  runat ="server" ></ asp:TextBox >< br  />
             < asp:TextBox  ID ="txtName"  runat ="server" ></ asp:TextBox >< br  />
             < asp:TextBox  ID ="txtEmailAddress"  runat ="server" ></ asp:TextBox >< br  />
            
             < p >
                 < asp:Button  ID ="btnAdd"  runat ="server"  Text ="添加"  OnClick ="btnAdd_Click"   />
             </ p >
        
         </ asp:Panel >
    
         < asp:GridView  ID ="GridView1"  runat ="server"  DataKeyNames ="LogonID"  CellPadding ="3"  GridLines ="None"  AutoGenerateColumns ="False"  BackColor ="White"  BorderColor ="White"  BorderStyle ="Ridge"  BorderWidth ="2px"  CellSpacing ="1"  OnRowDeleting ="GridView1_RowDeleting"  OnRowUpdating ="GridView1_RowUpdating"  OnRowCancelingEdit ="GridView1_RowCancelingEdit"  OnRowEditing ="GridView1_RowEditing" >
             < FooterStyle  BackColor ="#C6C3C6"  ForeColor ="Black"   />
             < RowStyle  BackColor ="#DEDFDE"  ForeColor ="Black"   />
             < SelectedRowStyle  BackColor ="#9471DE"  Font-Bold ="True"  ForeColor ="White"   />
             < PagerStyle  BackColor ="#C6C3C6"  ForeColor ="Black"  HorizontalAlign ="Right"   />
             < HeaderStyle  BackColor ="#4A3C8C"  Font-Bold ="True"  ForeColor ="#E7E7FF"   />
            
             < Columns >
                 < asp:HyperLinkField  DataTextField ="LogonID"  HeaderText ="登陆ID"  DataNavigateUrlFields ="LogonID"  DataNavigateUrlFormatString ="?logonid={0}"  Target ="_blank"   />
                 < asp:BoundField  DataField ="Password"  HeaderText ="密码"   />
                 < asp:BoundField  DataField ="Name"  HeaderText ="名字"   />
                 < asp:BoundField  DataField ="EmailAddress"  HeaderText ="电子邮件"   />
                 < asp:CommandField  HeaderText ="删除"  ShowDeleteButton ="True"  ShowHeader ="True"  DeleteText ="删除"   />
                 < asp:CommandField  CancelText ="取消"  EditText ="编辑"  HeaderText ="编辑"  ShowEditButton ="True"
                    UpdateText ="更新"   />
             </ Columns >
            
         </ asp:GridView >
    
     </ div >
            
     </ form >
</ body >
</ html >

      放个GridView显示数据，再弄几个文本框和一个按钮用来添加数据


      2、Default.aspx.cs

using  System;
using  System.Data;
using  System.Configuration;
using  System.Web;
using  System.Web.Security;
using  System.Web.UI;
using  System.Web.UI.WebControls;
using  System.Web.UI.WebControls.WebParts;
using  System.Web.UI.HtmlControls;

using  Test;

public  partial  class  _Default : System.Web.UI.Page 
{
    crud c  =   new  crud();

     protected   void  Page_Load( object  sender, EventArgs e)
    {
         if  ( ! this .IsPostBack)
        {
             string  strId  =  Request.QueryString[ " logonid " ];

             if  (strId  ==   null )
            {
                ShowAll();
            }
             else
            {
                GridView1.DataSource  =  c.SelectOne(strId);
                GridView1.DataBind();

                pnlAdd.Visible  =   false ;
            }
        }
    }

     void  ShowAll()
    {
        GridView1.DataSource  =  c.SelectAll();
        GridView1.DataBind();
    }

     protected   void  btnAdd_Click( object  sender, EventArgs e)
    {
        model m  =   new  model();
        m.LogonID  =  txtLogonID.Text.Trim();
        m.Password  =  txtPassword.Text.Trim();
        m.Name  =  txtName.Text.Trim();
        m.EmailAddress  =  txtEmailAddress.Text.Trim();

        Response.Write( " <font color='red'> "   +  c.Insert(m)  +   " </font> " );

        GridView1.EditIndex  =   - 1 ;

        ShowAll();

        txtLogonID.Text  =   "" ;
        txtPassword.Text  =   "" ;
        txtName.Text  =   "" ;
        txtEmailAddress.Text  =   "" ;
    }

     protected   void  GridView1_RowDeleting( object  sender, GridViewDeleteEventArgs e)
    {
        c.Delete(GridView1.DataKeys[e.RowIndex].Value.ToString());

        GridView1.EditIndex  =   - 1 ;

        ShowAll();
    }

     protected   void  GridView1_RowUpdating( object  sender, GridViewUpdateEventArgs e)
    {
        model m  =   new  model();
        m.LogonID  =  ( (HyperLink)GridView1.Rows[e.RowIndex].Cells[ 0 ].Controls[ 0 ] ).Text;
        m.Password  =  ( (TextBox)GridView1.Rows[e.RowIndex].Cells[ 1 ].Controls[ 0 ] ).Text;
        m.Name  =  ( (TextBox)GridView1.Rows[e.RowIndex].Cells[ 2 ].Controls[ 0 ] ).Text;
        m.EmailAddress  =  ( (TextBox)GridView1.Rows[e.RowIndex].Cells[ 3 ].Controls[ 0 ] ).Text;

        c.Update(m);

        GridView1.EditIndex  =   - 1 ;

        ShowAll();
    }

     protected   void  GridView1_RowEditing( object  sender, GridViewEditEventArgs e)
    {
        GridView1.EditIndex  =  e.NewEditIndex;

        ShowAll();
    }
     protected   void  GridView1_RowCancelingEdit( object  sender, GridViewCancelEditEventArgs e)
    {
        GridView1.EditIndex  =   - 1 ;

        ShowAll();
    }
}

      这个就没啥好说的了。调用CRUD的那些方法就行了。