MyBatis.Net使用入门(二)

MyBatis.Net使用入门(二)

EntityModel模型层的类Customer.cs和Product.cs代码如下:

    public class Customer
    {
        public int CustomerID { get; set; }
        public string CustomerName { get; set; }
        public bool CustomerSex { get; set; }
        public int CustomerAge { get; set; }
        public string CustomerAddress { get; set; }
        public DateTime SignDate { get; set; }
    }

    [Serializable]
    public class Customer
    {
        public int CustomerID { get; set; }
        public string CustomerName { get; set; }
        public bool CustomerSex { get; set; }
        public int CustomerAge { get; set; }
        public string CustomerAddress { get; set; }
        public DateTime SignDate { get; set; }
    }

---

BLL层的类CustomerServer_BL.cs和ProductService_BL.cs代码如下:

 public class CustomerServer_BL
    {
        public void InsertCustomer(Customer getcustomer)
        {
            if (getcustomer != null)
            {
                CustomerService getcustomerservice = new CustomerService();
                getcustomerservice.InsertOperator(getcustomer);
            }
        }
    } 

 public class ProductService_BL
    {
        public string GetAllProductByCompany(string companyname)
        {
            return new ProductService().GetAllProductByCompany(companyname);
        }

        public void InsertProduct(Product getproduct)
        {
            if (getproduct != null)
                new ProductService().InsertProduct(getproduct);
        }

        public string DeleteProduct(int productid)
        {
            return new ServiceDataAccess_DL.ProductService().DeleteProductById(productid);
        }

        public string UpdateProduct(Product getproduct)
        {
            return new ServiceDataAccess_DL.ProductService().UpdateProductById(getproduct);
        }

        public List<Product> GetAllProductList()
        {
            return new ServiceDataAccess_DL.ProductService().GetAllProductList();
        }
    }

Service层的类CustomerService.cs和ProductService.cs代码如下:

     public class ProductService
    {
        public static SqlMapper _getsqlmapper = null;
        private IDaoManager _getdaoManager = null;
        private IProductMapDao _getproductMapDao = null;
        public ISqlMapper _getsqlManager = null;

        public ProductService()
        {
            DomSqlMapBuilder getdombuilder = new DomSqlMapBuilder();
            if (getdombuilder != null)
            {
                ProductService._getsqlmapper = getdombuilder.Configure() as SqlMapper;
            }
            _getsqlManager = Mapper.Instance();
        }

        public string GetAllProductByCompany(string companyname)
        {
            if (_getsqlManager != null)
            {
                IList getresultlist = _getsqlManager.QueryForList("GetAllProducts", companyname);
                return getresultlist.Count.ToString();
            }
            return "";
        }

        public void InsertProduct(Product getproduct)
        {
            ISqlMapper _getsqlManager = null;
            DomSqlMapBuilder getdombuilder = new DomSqlMapBuilder();

            if (getdombuilder != null)
                ProductService._getsqlmapper = getdombuilder.Configure() as SqlMapper;
            _getsqlManager = Mapper.Instance();

            if (_getsqlManager != null)
                _getsqlManager.Insert("InsertProduct", getproduct);
        }

        public string DeleteProductById(int ProductId)
        {
            if (_getsqlManager != null)
            {
                _getsqlManager.BeginTransaction();
                try
                {
                    int getcounter = Mapper.Instance().Delete("DeleteProduct", ProductId);
                    _getsqlManager.CommitTransaction();
                    return getcounter.ToString();
                }
                catch
                {
                    _getsqlManager.RollBackTransaction();
                    throw;
                }
            }
            return "";
        }

        public string UpdateProductById(Product getproduct)
        {
            string getresult = string.Empty;
            if (_getsqlManager == null)
                _getsqlManager = Mapper.Instance();
            else
            {
                _getsqlManager.BeginTransaction();
                try
                {
                    getresult = _getsqlManager.Update("UpdateProduct", getproduct).ToString();
                    _getsqlManager.CommitTransaction();
                }
                catch
                {
                    _getsqlManager.RollBackTransaction();
                    throw;
                }
            }
            return getresult;
        }

        public List<Product> GetAllProductList()
        {
            if (_getsqlManager != null)
            {
                List<Product> getproductlist = new List<Product>();
                getproductlist = _getsqlManager.QueryForList<Product>("SelectAllProduct", null).ToList<Product>();
                return getproductlist;
            }
            return null;
        }
    }

---

     public  class CustomerService 
     {

      private static CustomerService _getcustomerService = new CustomerService();
      private IDaoManager _getDaoManager = null;
      private ICustomerDao _getbaseService = null;
      public static SqlMapper _getsqlmaper = null;

      public CustomerService()
      {
          #region Define the Base Map
          _getDaoManager = ServiceConfig.GetInstance().DaoManager;
          if (_getDaoManager != null)
              _getbaseService = _getDaoManager.GetDao(typeof(CustomerMapDao)) as  ICustomerDao;
          #endregion

          #region Use SqlMaper Style to Soleuv this Connection Problem fuck =---

          DomSqlMapBuilder getbuilder = new DomSqlMapBuilder();
          if (getbuilder != null)
              _getsqlmaper = getbuilder.Configure() as SqlMapper;
          #endregion

      }

      #region IBaseService Members

      public void InsertOperator(EntityModel.Customer getcustomer)
      {
          #region Define the Base Insert Operator By Serviceconfig  fuck this shit
           _getDaoManager.BeginTransaction();
            try
            {
                _getbaseService.InsertOperator(getcustomer);
                _getDaoManager.CommitTransaction();
            }
            catch
            {
                _getDaoManager.RollBackTransaction();
                throw;
            }
          #endregion 


          #region the Base Handle
          //if (_getsqlmaper != null)
          //{
          //    _getsqlmaper.BeginTransaction();
          //    try
          //    {
          //        _getsqlmaper.Insert("InsertCustomer", getcustomer);
          //        _getsqlmaper.CommitTransaction();
          //    }
          //    catch
          //    {
          //        _getsqlmaper.RollBackTransaction();
          //        throw;
          //    }
          //}
          #endregion   
      }

        public void DeleteOperator(int customerId)
        {
            throw new NotImplementedException();
        }

        public void UpdateOperator(EntityModel.Customer getcustomer, int customerid)
        {
            throw new NotImplementedException();
        }

        public void QueryOperator(int customerid)
        {
            throw new NotImplementedException();
        }

        #endregion
    }

web层的Maps文件夹下CustomerMap.xml和ProductMap.xml:

    <?xml version="1.0" encoding="utf-8" ?>
    <sqlMap namespace="EntityModel" xmlns="http://ibatis.apache.org/mapping" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <alias>
        <typeAlias alias="Customer" type="EntityModel.Customer, EntityModel" />
    </alias>

    <!--Mapping Class To Sql Realation Teable-->
    <resultMaps>
        <resultMap id="CustomerResult" class="Customer">
            <result property="CustomerID" column="Customer_ID"/>
            <result property="CustomerName" column="Customer_Name"/>
            <result property="CustomerSex" column="Customer_Sex"/>
            <result property="CustomerAge" column="Customer_Age" />
            <result property="CustomerAddress" column="Customer_Address" />
            <result property="SignDate" column="SignOn_Data" />
        </resultMap>
    </resultMaps>

    <!--Mapping The SQl StateMent about CRUD chenkai-->
    <statements>
        <Insert id="InsertCustomer"  parameterClass="Customer">
            INSERT INTO  dbo.Customer
            ( Customer_Name ,
            Customer_Sex ,
            Customer_Age ,
            Customer_Address ,
            SignOn_Data
            )
            VALUES  ( #CustomerName# , #CustomerSex# , #CustomerAge# , #CustomerAddress#,#SignDate# )
        </Insert>

        <Update id="UpdateCustomer" parameterClass="Customer">
            UPDATE dbo.Customer SET Customer_Name=#CustomerName#,
            Customer_Sex=#CustomerSex#,
            Customer_Age=#CustomerAge#,
            Customer_Address=#CustomerAddress#,
            SignOn_Data=#SignDate#
            WHERE Customer_ID=#CustomerID#
        </Update>

        <Delete id="DeleteCustomer" parameterClass="Customer">
            DELETE FROM dbo.Customer WHERE Customer_ID=#CustomerID#
        </Delete>

        <Select id="SelectCustomer" resultClass="Customer" parameterClass="int">
            SELECT * FROM dbo.Customer WHERE Customer_ID=#CustomerID#
        </Select>
    </statements>
    </sqlMap>

---

    <?xml version="1.0" encoding="utf-8" ?>
    <sqlMap namespace="EntityModel" xmlns="http://ibatis.apache.org/mapping" 
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
        <alias>
        <typeAlias alias="Product" type="EntityModel.Product, EntityModel" />
        </alias>

    <resultMaps>
        <resultMap id="ProductResult" class="Product">
            <result property="ProductId" column="ProductId"/>
            <result property="ProductName" column="Product_Name"/>
            <result property="ProductCompany" column="Product_Company"/>
            <result property="SignDate" column="SignDate" />
            <result property="UpdateData" column="UpdateData" />
        </resultMap>
    </resultMaps>

    <statements>
        <select id="GetAllProducts" parameterClass="string" resultClass="hashtable">
            SELECT * FROM Product WHERE Product_Company=#companyname#
        </select>
        <insert id="InsertProduct" parameterClass="EntityModel.Product">
            INSERT  dbo.Product ( Product_Name ,Product_Company)
            VALUES  (#ProductName# , #ProductCompany#)
        </insert>
        <delete id="DeleteProduct" parameterClass="int" restultClass="int">
            DELETE FROM dbo.Product WHERE ProductID=#ProductId#
        </delete>
        <update id="UpdateProduct" parameterClass="EntityModel.Product" restltClass="int">
            UPDATE Product SET Product_Name=#ProductName# WHERE ProductID=#ProductId#
        </update>
        <select id="SelectAllProduct" resultMap="ProductResult" >
            SELECT * FROM Product
        </select>
    </statements>
    </sqlMap>

Providers.config的代码如下:

<?xml version="1.0"?>

<providers xmlns="http://ibatis.apache.org/providers"
           xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <clear />
  <!--SqlServer 2.0 SQl-->
  <provider
    name="sqlServer2.0"
    enabled="true"
    description="Microsoft SQL Server, provider V2.0.0.0 in framework .NET V2.0"
    assemblyName="System.Data, Version=2.0.0.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="@"
    allowMARS="false" />
  <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"
    allowMARS="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"
    allowMARS="false" />
  <!--Oracle Support-->
  <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=":"
    allowMARS="false" />
  <!--MySql Support-->
  <provider
    name="MySql"
    description="MySQL, MySQL provider 1.0.7.30072"
    enabled="false"
    assemblyName="MySql.Data, Version=1.0.7.30072, 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="?"
    allowMARS="false" />
  <!--SQLite 3 Support-->
  <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"
            allowMARS="false" />
  <!--PostgreSql Support-->
  <provider
    name="PostgreSql0.99.1.0"
    description="PostgreSql, Npgsql provider V0.99.1.0"
    enabled="false"
    assemblyName="Npgsql, Version=0.99.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=":"
    allowMARS="true" />
 </providers>

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">

    <!--BAsic Setting About Configuration-->
    <settings>
        <setting useStatementNamespaces="false"/>
        <setting cacheModelsEnabled="true"/>
    </settings>

    <providers resource="Providers.config"/>
   <!--DataBase Connection Configuration-->
    <database>
        <provider name="sqlServer2.0" />
        <dataSource name="SqlServer" connectionString="data source=WZY-PC;database=BaseCardDB;user id=sa;password=wu199010;" />
    </database>

    <sqlMaps>
        <sqlMap resource="Maps/CustomerMap.xml"/>
        <sqlMap resource="Maps/ProductMap.xml"/>
    </sqlMaps>
</sqlMapConfig>

MainPage.aspx页面代码:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="MainPage.aspx.cs" Inherits="CustomerWeb_UI.MainPage" %>

<!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>Test OF MyBatis Data Access Famework</title>
        <script type="text/javascript" src="http://cdn.jquerytools.org/1.2.5/full/jquery.tools.min.js"></script>
        <link rel="stylesheet" type="text/css" href="http://static.flowplayer.org/tools/css/standalone.css" />
        <link rel="stylesheet" type="text/css" href="http://static.flowplayer.org/tools/css/tabs.css" />
        <script type="text/javascript" src="JavaScript/jquery.tablesorter.js"></script>
        <link rel="stylesheet" type="text/css" href="CSS/style.css" />
        <script type="text/javascript">
            $(document).ready(function ()
            {
                    $("#myTable").tablesorter();
             }
            );

            $(document).ready(function() {
                    $("#myTable").tablesorter({ sortList: [[0, 0], [1, 0]] });
                }
            );
        </script>
        <style type="text/css">
        /* tab pane styling */
            .panes div {
                display: none;
                padding: 15px 10px;
                border: 1px solid #999;
                border-top: 0;
                height: 300px;
                font-size: 14px;
                background-color: #fff;
            }

            #firstDiv {
                display:block;

            }
        </style>
    </head>

    <body>
        <form id="Main_Service_Form" runat="server">
            <!-- This JavaScript snippet activates those tabs -->
            <script type="text/javascript">
                // perform JavaScript after the document is scriptable.
                $(function() {
                    // setup ul.tabs to work as tabs for each div directly under div.panes
                    $("ul.tabs").tabs("div.panes > div");
                });
            </script>

            <!-- tab "panes" -->
            <div class="panes">
                <div runat="server" id="firstDiv">
                    <table id="myTable" class="tablesorter">
                        <thead>
                            <tr>
                                <th>Product ID</th>
                                <th>产品名称</th>
                                <th>产品公司</th>
                                <th>登录日期</th>
                                <th>更新日期</th>
                            </tr>
                        </thead>

                        <tbody>
                            <%= this.RefreshDateFromDevice() %>
                        </tbody>
                    </table>
                </div>
                <div>Second tab content</div>
                <div>Third tab content</div>
                <div>Delete TAble</div>
            </div>

            <div>
                <asp:Button CssClass="panes" ID="InsertCus_But" runat="server" Height="30px"  OnClick="InsertCus_But_Click" Text="更新数据" Width="130px" />
                <asp:Button ID="RefreshDate_But" runat="server" Text="刷新数据" OnClick="RefreshDate_But_Click" />
            </div>


        </form>
    </body>
</html>

后台代码:

 public partial class MainPage : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void InsertCus_But_Click(object sender, EventArgs e)
        {
            //CustomerServer_BL getcustomer_bl = new CustomerServer_BL();
            //getcustomer_bl.InsertCustomer(new Customer()
            //{
            //    CustomerName = "chenkai",
            //    CustomerAge = 28,
            //    CustomerAddress = "Beijing",
            //    CustomerSex = true
            //});

            ProductService_BL getproductBl = new ProductService_BL();
            //getproductBl.InsertProduct(new Product()
            //{
            //    ProductName = "Windows  phone 7 Deskshop-54",
            //    SignDate=DateTime.Now,
            //    ProductCompany = "Auto-Desk"
            //});

           //string getdelcount=getproductBl.DeleteProduct(3);


           // string getcount = getproductBl.GetAllProductByCompany("MS");
           // if (!string.IsNullOrEmpty(getcount))
           // Response.Write("获取总的数据量:" + getcount+" 删除数据数量:"+getdelcount);

            string getresult = getproductBl.UpdateProduct(new Product
            {
                ProductId = 1,
                ProductName = "Widows  phone 7 Device",
                ProductCompany = "Tommy Frank and MS Team"
            });
            Response.Write("成功更新数据:" + getresult);
        }


        public List<Product> getAllProductList = new List<Product>();
        protected void RefreshDate_But_Click(object sender, EventArgs e)
        {
            //this.getAllProductList = new ProductService_BL().GetAllProductByCompany();
            //this.RefreshDateFromDevice(); 
            this.getAllProductList = new ProductService_BL().GetAllProductList();
            this.RefreshDateFromDevice(); 
        }

        public string RefreshDateFromDevice()
        {
            StringBuilder getbuilder = new StringBuilder();
            if (this.getAllProductList.Count > 0)
            {
                foreach (Product getproduct in getAllProductList)
                {
                    getbuilder.AppendFormat("<tr> ");
                    getbuilder.AppendFormat("<td>{0}</td> ",getproduct.ProductId);
                    getbuilder.AppendFormat("<td>{0}</td>",getproduct.ProductName);
                    getbuilder.AppendFormat("<td>{0}</td>", getproduct.ProductCompany);
                    getbuilder.AppendFormat("<td>{0}</td>",getproduct.SignDate);
                    getbuilder.AppendFormat("<td>{0}</td>",getproduct.UpdateData);
                    getbuilder.AppendFormat("</tr> ");
                }
            }
            return getbuilder.ToString();
        }
    }

结果如图: