csharp:Dapper Sample



You can find Dapper on Google Code here: http://code.google.com/p/dapper-dot-net/ and the GitHub distro here: https://github.com/SamSaffron/dapper-dot-net.

///<summary>
        /// 追加记录
        ///</summary>
        ///<param name="BookPlaceListInfo"></param>
        ///<returns></returns>
        public int InsertBookPlaceList(BookPlaceListInfo bookPlaceList)
        {
            int ret = 0;
            try
            {
                List<BookPlaceListInfo> list=new List<BookPlaceListInfo>();
                list.Add(bookPlaceList);
                StringBuilder str = new StringBuilder();
                str.Append("INSERT INTO BookPlaceList ");
                str.Append("([BookPlaceName] ,[BookPlaceCode] ,[BookPlaceParent]) VALUES ");
                str.Append("(@BookPlaceName ,@BookPlaceCode,@BookPlaceParent)");
                ret=SqlMapperUtil.InsertMultiple<BookPlaceListInfo>(str.ToString(),list, SqlHelper.ConnectionString);

            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return ret;
        }
        /// <summary>
        /// 存储过程
        /// 追加记录
        /// </summary>
        /// <param name="bookPlaceList"></param>
        /// <returns></returns>
        public int InsertBookPlaceListProc(BookPlaceListInfo bookPlaceList)
        {
            int ret = 0;
            try
            {
                string strProc = "proc_Insert_BookPlaceList";//存储过程
                var pamar = new { BookPlaceName = bookPlaceList.BookPlaceName, BookPlaceCode = bookPlaceList.BookPlaceCode, BookPlaceParent = bookPlaceList.BookPlaceParent };
                ret = SqlMapperUtil.InsertUpdateOrDeleteStoredProc(strProc, pamar, SqlHelper.ConnectionString);

            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return ret;
        }

        /// <summary>
        /// 追加多条记录
        /// </summary>
        /// <param name="bookPlaceList"></param>
        /// <returns></returns>
        public int InsertBookPlaceListMore(List<BookPlaceListInfo> bookPlaceList)
        {
            int ret = 0;
            try
            {

                StringBuilder str = new StringBuilder();
                str.Append("INSERT INTO BookPlaceList ");
                str.Append("([BookPlaceName] ,[BookPlaceCode] ,BookPlaceParent]) VALUES ");
                str.Append("(@BookPlaceName ,@BookPlaceCode,@BookPlaceParent)");
                ret = SqlMapperUtil.InsertMultiple<BookPlaceListInfo>(str.ToString(), bookPlaceList, SqlHelper.ConnectionString);

            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return ret;
        }

        ///<summary>
        ///修改记录
        ///</summary>
        ///<param name="BookPlaceListInfo"></param>
        ///<returns></returns>
        public int UpdateBookPlaceList(BookPlaceListInfo bookPlaceList)
        {
            int ret = 0;
            try
            {
                List<BookPlaceListInfo> list = new List<BookPlaceListInfo>();
                list.Add(bookPlaceList);
                StringBuilder str = new StringBuilder();
                str.Append("UPDATE BookPlaceList SET ");
                str.Append("[BookPlaceName]=@BookPlaceName ,");
                str.Append("[BookPlaceCode]=@BookPlaceCode,");
                str.Append("[BookPlaceParent]=@BookPlaceParent");
                str.Append(" where ");
                str.Append("[BookPlaceID]=@BookPlaceID");
                ret = SqlMapperUtil.InsertMultiple<BookPlaceListInfo>(str.ToString(), list, SqlHelper.ConnectionString);

            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return ret;
        }
        /// <summary>
        /// 存储过程
        /// </summary>
        /// <param name="bookPlaceList"></param>
        /// <returns></returns>
        public int UpdateBookPlaceListProc(BookPlaceListInfo bookPlaceList)
        {
            int ret = 0;
            try
            {
                string strProc = "proc_Update_BookPlaceList";//存储过程
                var pamar = new { BookPlaceName = bookPlaceList.BookPlaceName, BookPlaceCode = bookPlaceList.BookPlaceCode, BookPlaceParent = bookPlaceList.BookPlaceParent, BookPlaceID=bookPlaceList.BookPlaceID };
                ret = SqlMapperUtil.InsertUpdateOrDeleteStoredProc(strProc, pamar, SqlHelper.ConnectionString);
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return ret;
        }
        ///<summary>
        /// 删除记录
        ///</summary>
        ///<param name="bookPlaceIDInfo"></param>
        ///<returns></returns>
        public bool DeleteBookPlaceList(int bookPlaceID)
        {
            bool ret = false;
            try
            {
                int temp = 0;
                StringBuilder str = new StringBuilder();
                str.Append("DELETE  BookPlaceList WHERE BookPlaceID = @BookPlaceID");
                temp = SqlMapperUtil.InsertUpdateOrDeleteSql(str.ToString(), new { BookPlaceID = bookPlaceID }, SqlHelper.ConnectionString);

                if (temp != 0)
                {
                    ret = true;
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return ret;
        }
        /// <summary>
        /// 存储过程
        /// </summary>
        /// <param name="bookPlaceID"></param>
        /// <returns></returns>
        public bool DeleteBookPlaceListProc(int bookPlaceID)
        {
            bool ret = false;
            try
            {
                int temp = 0;
                string strProc = "proc_Delete_BookPlaceList";//存储过程
                var pamar = new { BookPlaceID = bookPlaceID };
                temp = SqlMapperUtil.InsertUpdateOrDeleteStoredProc(strProc, new { BookPlaceID = bookPlaceID }, SqlHelper.ConnectionString);

                if (temp != 0)
                {
                    ret = true;
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return ret;
        }
        ///<summary>
        /// 查询记录
        ///</summary>
        ///<param name="bookPlaceIDInfo"></param>
        ///<returns></returns>
        public BookPlaceListInfo SelectBookPlaceList(int bookPlaceID)
        {
            BookPlaceListInfo bookPlaceList = null;
            try
            {
                StringBuilder str = new StringBuilder();
                str.Append("SELECT * FROM BookPlaceList WHERE BookPlaceID = @BookPlaceID");
                bookPlaceList = SqlMapperUtil.SqlWithParamsSingle<BookPlaceListInfo>(str.ToString(), new { BookPlaceID = bookPlaceID }, SqlHelper.ConnectionString);

            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return bookPlaceList;
        }
        /// <summary>
        /// 存储过程查询记录
        /// 涂聚文
        /// 20150726
        /// </summary>
        /// <param name="bookPlaceID"></param>
        /// <returns></returns>
        public BookPlaceListInfo SelectBookPlaceListProc(int bookPlaceID)
        {
            BookPlaceListInfo bookPlaceList = null;
            try
            {
                string strProc = "proc_Select_BookPlaceList";//存储过程
                bookPlaceList = SqlMapperUtil.StoredProcWithParamsSingle<BookPlaceListInfo>(strProc, new { BookPlaceID = bookPlaceID }, SqlHelper.ConnectionString);

            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return bookPlaceList;
        }
        ///<summary>
        /// 查询所有记录
        ///</summary>
        ///<returns></returns>
        public List<BookPlaceListInfo> SelectBookPlaceListAll()
        {
            List<BookPlaceListInfo> list = new List<BookPlaceListInfo>();
            try
            {
                string str = "SELECT * FROM BookPlaceList";
                list = SqlMapperUtil.SqlWithParams<BookPlaceListInfo>(str, null, SqlHelper.ConnectionString);
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return list;
        }
        /// <summary>
        /// 存储过程
        /// Geovin Du
        /// 查询所有记录
        /// </summary>
        /// <returns></returns>
        public List<BookPlaceListInfo> SelectBookPlaceListProc()
        {
            List<BookPlaceListInfo> list = new List<BookPlaceListInfo>();
            try
            {
                string strProc = "proc_Select_BookPlaceListAll"; //存储过程
                list = SqlMapperUtil.StoredProcWithParams<BookPlaceListInfo>(strProc, null, SqlHelper.ConnectionString);
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return list;
        }

测试

/// <summary>
       /// 编辑
       /// </summary>
       /// <param name="sender"></param>
       /// <param name="e"></param>
       private void dataGridView1_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
       {
           EditForm edit = new EditForm();
           edit.Text = "";
           edit.Operator = 2;
           edit.BookPlaceCode =(!object.Equals(dataGridView1.Rows[e.RowIndex].Cells["BookPlaceCode"].Value,null))?dataGridView1.Rows[e.RowIndex].Cells["BookPlaceCode"].Value.ToString():"";
           edit.BookPlaceID = int.Parse(dataGridView1.Rows[e.RowIndex].Cells["BookPlaceID"].Value.ToString());
           edit.BookPlaceParent = int.Parse(dataGridView1.Rows[e.RowIndex].Cells["BookPlaceParent"].Value.ToString());
           edit.BookPlaceName = dataGridView1.Rows[e.RowIndex].Cells["BookPlaceName"].Value.ToString();
           if (edit.ShowDialog() == DialogResult.OK)
           {
               this.dataGridView1.DataSource = bookPlaceListBLL.SelectBookPlaceListAll();
               //this.dataGridView1.DataSource = bookPlaceListBLL.SelectBookPlaceListProc(); //存储过程
           }

       }
       /// <summary>
       /// 添加
       /// </summary>
       /// <param name="sender"></param>
       /// <param name="e"></param>
       private void button1_Click(object sender, EventArgs e)
       {
           EditForm edit = new EditForm();
           edit.Text = "";
           edit.Operator = 1;
           edit.BookPlaceParent = int.Parse(dataGridView1.CurrentRow.Cells["BookPlaceParent"].Value.ToString());
           if (edit.ShowDialog() == DialogResult.OK)
           {
               this.dataGridView1.DataSource = bookPlaceListBLL.SelectBookPlaceListAll();
               //this.dataGridView1.DataSource = bookPlaceListBLL.SelectBookPlaceListProc(); //存储过程
           }
       }
       /// <summary>
       /// 删除
       /// </summary>
       /// <param name="sender"></param>
       /// <param name="e"></param>
       private void button2_Click(object sender, EventArgs e)
       {
           int id = int.Parse(dataGridView1.CurrentRow.Cells["BookPlaceID"].Value.ToString());
           //bool k = bookPlaceListBLL.DeleteBookPlaceList(id);//SQL
           bool k = bookPlaceListBLL.DeleteBookPlaceListProc(id);//存储过程
           if (k)
           {
               this.dataGridView1.DataSource = bookPlaceListBLL.SelectBookPlaceListAll();
               MessageBox.Show("ok");

           }

       }
       /// <summary>
       /// 查询
       /// </summary>
       /// <param name="sender"></param>
       /// <param name="e"></param>
       private void button3_Click(object sender, EventArgs e)
       {
           int id = int.Parse(dataGridView1.CurrentRow.Cells["BookPlaceID"].Value.ToString());
           BookPlaceListInfo info = new BookPlaceListInfo();
           //info = bookPlaceListBLL.SelectBookPlaceList(id);//SQL
           info = bookPlaceListBLL.SelectBookPlaceListProc(id);//存储过程 涂聚文注
           if (!object.Equals(info, null))
           {
               MessageBox.Show(info.BookPlaceName);
           }
       }

/// <summary>
       /// 添加,编辑
       /// </summary>
       /// <param name="sender"></param>
       /// <param name="e"></param>
       private void button1_Click(object sender, EventArgs e)
       {
           BookPlaceListInfo info = new BookPlaceListInfo();

           info.BookPlaceCode = this.textBox2.Text.Trim();
           info.BookPlaceName = this.textBox1.Text.Trim();
           info.BookPlaceParent = int.Parse(this.textBox3.Text.Trim());
           int k = 0;
           if (Operator == 1)
           {
               //k = bookPlaceListBLL.InsertBookPlaceList(info);//SQL
               k = bookPlaceListBLL.InsertBookPlaceListProc(info);//添加,存储过程
               if (k > 0)
               {
                   DialogResult dresult = MessageBox.Show("添加記錄成功！", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                   if (dresult == DialogResult.OK)
                   {
                       this.Close();
                       this.DialogResult = DialogResult.OK;
                   }

               }
           }
           if (Operator == 2)
           {
               info.BookPlaceID = BookPlaceID;
               //k = bookPlaceListBLL.UpdateBookPlaceList(info);//SQL
               k = bookPlaceListBLL.UpdateBookPlaceListProc(info);//编辑存储过程
               if (k > 0)
               {
                   //
                   DialogResult dresult = MessageBox.Show("修改記錄成功！", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                   if (dresult == DialogResult.OK)
                   {
                       this.Close();
                       this.DialogResult = DialogResult.OK;
                   }

               }
           }
       }

与SqlHelper比批量数据插入快近一半