C# SqlSugar框架的学习使用（四）-- 插入数据的详细用法

Vaccae

发布于 2019-08-20 16:54:35

9K0

发布于 2019-08-20 16:54:35

文章被收录于专栏：微卡智享

前言

上一篇《C# SqlSugar框架的学习使用（三）-- 查询的多种用法》我们已经把SqlSugar的查询多种用法实现了，这篇我们就来说说插入数据的多种用法。

数据源

数据表 POSTemp

我们还是用前面介绍的数据库，因为这次插入时要讲一下存在自增长类型的数据，所以我们现在用POSTemp这个表，结构如下图：

程序SqlSugarTest

程序中加入插入的功能按钮等，如下图：

POSTemp类

using System;
using System.Linq;
using System.Text;
using SqlSugar;

namespace Model
{
    ///<summary>
    ///
    ///</summary>
    public partial class POSTemp
    {
        public POSTemp()
        {


        }
        /// <summary>
        /// 
        /// </summary>
        [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
        public int sn { get; set; }

        /// <summary>
        /// Desc:
        /// Default:
        /// Nullable:False
        /// </summary>
        public string Posno { get; set; }

        /// <summary>
        /// Desc:
        /// Default:
        /// Nullable:False
        /// </summary>           
        public string Posname { get; set; }

        /// <summary>
        /// Desc:
        /// Default:
        /// Nullable:True
        /// </summary>           
        public string OrgCode { get; set; }

        /// <summary>
        /// Desc:
        /// Default:
        /// Nullable:True
        /// </summary>           
        public string status { get; set; }

    }
}

代码演示

插入返回影响行数

     private void tsmnuinsertcount_Click(object sender, EventArgs e)
        {
            try
            {
                POSTemp pos = new POSTemp();
                pos.Posno = "0001";
                pos.Posname = "01款台";
                pos.OrgCode = "001";
                pos.status = "1";

                int count = _db.Insertable(pos).ExecuteCommand();
                TextShow("插入了" + count + "条数据");
            }
            catch (Exception ex)
            {
                TextShow(ex.Message);
            }
        }

插入返回自增列

        private void tsmnuinsertident_Click(object sender, EventArgs e)
        {
            try
            {
                POSTemp pos = new POSTemp();
                pos.Posno = "0002";
                pos.Posname = "02款台";
                pos.OrgCode = "001";
                pos.status = "1";

                int count = _db.Insertable(pos).ExecuteReturnIdentity(); 
                TextShow("插入数据成功，自增列序号为：" + count );
            }
            catch (Exception ex)
            {
                TextShow(ex.Message);
            }
        }

插入返回实体

        private void tsmnuinsertcls_Click(object sender, EventArgs e)
        {
            try
            {
                POSTemp pos = new POSTemp();
                pos.Posno = "0003";
                pos.Posname = "03款台";
                pos.OrgCode = "001";
                pos.status = "1";

                POSTemp retPosTemp = _db.Insertable(pos).ExecuteReturnEntity();
                TextShow("插入数据成功");
                TextShow("sn:" + retPosTemp.sn + "  Posno:" + retPosTemp.Posno + " PosName:" + retPosTemp.Posname);
                TextShow("OrgCode:" + retPosTemp.OrgCode + " status:" + retPosTemp.status);
            }
            catch (Exception ex)
            {
                TextShow(ex.Message);
            }
        }

只插入对应列

        private void tsmnuinsertcolumn_Click(object sender, EventArgs e)
        {
            try
            {
                POSTemp pos = new POSTemp();
                pos.Posno = "0004";
                pos.Posname = "04款台";
                pos.OrgCode = "001";
                pos.status = "1";

                int count = _db.Insertable(pos)
                    .InsertColumns(t => new {t.Posno, t.Posname})
                    .ExecuteReturnIdentity();
                TextShow("只插对应列数据成功，自增列序号为：" + count);
            }
            catch (Exception ex)
            {
                TextShow(ex.Message);
            }
        }

不插入对应列

       private void tsmnuinsertignore_Click(object sender, EventArgs e)
        {
            try
            {
                POSTemp pos = new POSTemp();
                pos.Posno = "0005";
                pos.Posname = "05款台";
                pos.OrgCode = "001";
                pos.status = "1";

                int count = _db.Insertable(pos)
                    .IgnoreColumns(t => new { t.OrgCode })
                    .ExecuteReturnIdentity();
                TextShow("不插对应列数据成功，自增列序号为：" + count);
            }
            catch (Exception ex)
            {
                TextShow(ex.Message);
            }
        }

根据条件指定不插入列

        private void tsmnuinsertignorewhere_Click(object sender, EventArgs e)
        {
            try
            {
                POSTemp pos = new POSTemp();
                pos.Posno = "0006";
                pos.Posname = "06款台";
                pos.OrgCode = "001";
                pos.status = "1";

                int count = _db.Insertable(pos)
                    .IgnoreColumns(t => t == "status")
                    .ExecuteReturnIdentity();
                TextShow("根据条件不插对应列数据成功，自增列序号为：" + count);
            }
            catch (Exception ex)
            {
                TextShow(ex.Message);
            }
        }

List中所有列不插入

        private void tsmnuinsertignorelist_Click(object sender, EventArgs e)
{
            try
            {
                POSTemp pos = new POSTemp();
                pos.Posno = "0007";
                pos.Posname = "07款台";
                pos.OrgCode = "001";
                pos.status = "1";

                List<string> list= new List<string>();
                list.Add("OrgCode");
                list.Add("status");

                int count = _db.Insertable(pos)
                    .IgnoreColumns(t => list.Contains(t))
                    .ExecuteReturnIdentity();
                TextShow("根据条件不插对应列数据成功，自增列序号为：" + count);
            }
            catch (Exception ex)
            {
                TextShow(ex.Message);
            }
        }

插入使用锁

        private void tsmnuinsertlock_Click(object sender, EventArgs e)
        {
            try
            {
                POSTemp pos = new POSTemp();
                pos.Posno = "0008";
                pos.Posname = "08款台";
                pos.OrgCode = "001";
                pos.status = "1";

                int count = _db.Insertable(pos)
                    .With(SqlWith.UpdLock)
                    .ExecuteReturnIdentity();
                TextShow("插入使用锁数据成功，自增列序号为：" + count);
            }
            catch (Exception ex)
            {
                TextShow(ex.Message);
            }
        }

批量插入（性能很快不用操心）

        private void tsmnuinsertarray_Click(object sender, EventArgs e)
        {
            try
            {
                List<POSTemp> listpos = new List<POSTemp>();
                for (int i = 0; i < 3; i++)
                {
                    POSTemp pos = new POSTemp();
                    pos.Posno = "001"+i;
                    pos.Posname = pos.Posno + "款台";
                    pos.OrgCode = "001";
                    pos.status = "1";
                    listpos.Add(pos);
                }

                int count = _db.Insertable(listpos.ToArray())
                    .ExecuteCommand();
                TextShow("List插入数据，成功插入" + count + "条数据");
            }
            catch (Exception ex)
            {
                TextShow(ex.Message);
            }
        }

匿名对象和字典的支持

        private void tsmnuinsertdictionary_Click(object sender, EventArgs e)
        {
            try
            {
                int count = _db.Insertable<POSTemp>(
                        new { Posno = "0013", Posname = "13款台" })
                    .ExecuteReturnIdentity();
                TextShow("匿名对插成功插入成功，自增序号为：" + count);

                count = _db.Insertable<POSTemp>(
                        new Dictionary<string, object>()
                        {
                            { "Posno","0014"},
                            {"Posname","14款台" }
                        })
                    .ExecuteReturnIdentity();
                TextShow("字典方式插成功插入成功，自增序号为：" + count);
            }
            catch (Exception ex)
            {
                TextShow(ex.Message);
            }
        }

将A表数据插入B表

我们将POSTemp的表数据插入到POS中，先看下图，POSTemp中有13条数据，POS中没有数据

插入代码

        private void tsmnuinsertAtoB_Click(object sender, EventArgs e)
        {
            try
            {
                int count = _db.Insertable(_db.Queryable<POSTemp>()
                     .Select<POS>().ToList()).ExecuteCommand();
                TextShow("从POSTemp表中向POS表中成功插入了" + count + "条数据");
            }
            catch (Exception ex)
            {
                TextShow(ex.Message);
            }
        }