分享一個自己寫的C# SqlHelper

廢話不多說，先看下使用DEMO

[TestClass]
    public class UnitTest1
    {
        /// <summary>
        /// 插入資料
        /// </summary>
        [TestMethod]
        public void TestInsert()
        {
            var db = DbFactory.GetInstance();
            var listing = new Listing
            {
                RealName = "孫悟空",
                IsShow = true,
                MaxDate = DateTime.Now
            };

            var id = ConvertUtils.TypeConvert.ToInt(db.Insert<Listing>(listing, "ListingId"));

            Assert.AreEqual(id = 0, true, id.ToString());
        }
        /// <summary>
        /// 獲取單個實體
        /// </summary>
        [TestMethod]
        public void TestFirstOrDefault()
        {
            var db = DbFactory.GetInstance("testdb");
            var sql = new Sql("select * from Listing where ListingId =@listingid");
            sql.AddParameter("@listingid", 12);
            var l = db.FirstOrDefault<Listing>(sql);

            Assert.AreEqual(l == null, true, l.ToJson());
        }
        /// <summary>
        /// 獲取實體集合
        /// </summary>
        [TestMethod]
        public void TestQueryList()
        {
            var db = DbFactory.GetInstance("testdb");
            var sql = new Sql("select * from Listing where ListingId > 5");
            var list = db.Query<Listing>(sql);

            Assert.AreEqual(list == null, true, list.ToJson());
        }
        /// <summary>
        /// 查詢返回DataTable
        /// </summary>
        [TestMethod]
        public void TestQueryDataTable()
        {
            var db = DbFactory.GetInstance("testdb");
            var sql = new Sql("select * from Listing where ListingId > 5");
            var dt = db.Query(sql);

            Assert.AreEqual(dt, null, dt.ToJson());
        }
        /// <summary>
        /// SqlBulkInsert批量插入資料
        /// </summary>
        [TestMethod]
        public void TestSqlBulkInsert()
        {
            var db = DbFactory.GetInstance("testdb");
            var sql = new Sql("select * from Listing where ListingId > 5");
            var dt = db.Query(sql);
            string message;
            var rs = db.SqlBulkInsert(dt, 1000, out message);

            Assert.AreEqual(rs, false, message);
        }
        /// <summary>
        /// 返回COUNT
        /// </summary>
        [TestMethod]
        public void TestExecuteScalar()
        {
            var db = DbFactory.GetInstance("testdb");
            var sql = new Sql("select count(1) from Listing where ListingId > 5");
            var count = db.ExecuteScalar<int>(sql);

            Assert.AreEqual(count, 0, count.ToString());
        }
        /// <summary>
        /// 執行SQL語句
        /// </summary>
        [TestMethod]
        public void TestExecuteNoneQuery()
        {
            var db = DbFactory.GetInstance("testdb");
            var sql = new Sql("update Listing set IsShow=0 where ListingId = @lid ");
            sql.AddParameter("@listingid", 12);
            var count = db.ExecuteNoneQuery(sql);

            Assert.AreEqual(count, 0, count.ToString());
        }
        /// <summary>
        /// 執行儲存過程
        /// </summary>
        [TestMethod]
        public void TestQueryWithProc()
        {
            var sql = new Sql("DBO.PROC_GetRecordTipInfoByPager", System.Data.CommandType.StoredProcedure);
            sql.AddParameter("@name1", "table1");
            sql.AddParameter("@recordlist", "1,2,3,4");

            var dt = DbFactory.GetInstance("testdb_new").Query(sql);
            var list = DataConvert.DataTableToList<Listing>(dt);
        }
    }

原始碼請從附件下載！

內容均為作者獨立觀點，不代表八零IT人立場，如涉及侵權，請及時告知。