ServiceStack.OrmLite

1、管理 NuGet 添加引用
     Install-Package ServiceStack.OrmLite.Core
     Install-Package ServiceStack.OrmLite.SqlServer.Core

2、ConnectionFactory

using ServiceStack.OrmLite;
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;

namespace Test.Repository
{
    public class ConnectionFactory
    {
        public IDbConnection GetConnection(string strConn)
        {
            return strConn.ToDbConnection(GetDialectProvider());
        }

        public IDbConnection OpenDBConnection(string strConn)
        {
            var dbFactory = new OrmLiteConnectionFactory(strConn, GetDialectProvider());
            return dbFactory.OpenDbConnection();
        }

        private IOrmLiteDialectProvider GetDialectProvider()
        {
            var sqlProvider = SqlServerDialect.Provider;
            sqlProvider.GetStringConverter().UseUnicode = true;
            sqlProvider.GetDateTimeConverter().DateStyle = DateTimeKind.Utc;

            return sqlProvider;
        }
    }
}

3、Repository

using ServiceStack.OrmLite;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using Test.Models;

namespace Test.Repository
{
    public interface IAssetRepository
    {
        void Insert(AssetView data);
        List<AssetView> List();
        List<AssetView> FindByIds(int eid, List<int> ids);
        AssetView FindById(int eid, int id);
        AssetView GetById(int id);
        void Update(List<AssetView> dbAssets);
        void Update(AssetView dbAsset);
        void UpdateIncludedInBill(int eid, List<int> assetIds, int? assetIncludedBill);
    }

    public class AssetRepository : IAssetRepository
    {
        private ConnectionFactory _connFab;

        public AssetRepository(ConnectionFactory connFeb)
        {
            _connFab = connFeb;
        }

        public void Insert(AssetView data)
        {
            List<long> ids = new List<long>();
            List<AssetView> list = new List<AssetView>();
            for (int i = 0; i < 5; i++)
            {
                list.Add(new AssetView { Id = i, Name = "test" + i.ToString() });
            }

            //using (var sqlConn = _connFab.OpenDBConnection(_cfg.DBConnectionString1))
            using (var sqlConn = _connFab.OpenDBConnection("Data Source=.;Initial Catalog=dbassetcheck;User ID=sa;Password=000000;"))
            {
                sqlConn.Insert(data);
                sqlConn.Insert<AssetView>(data);
                sqlConn.InsertAll<AssetView>(list);

                #region
                long newId = sqlConn.Insert<AssetView>(data, selectIdentity: true);
                AssetView model = sqlConn.SingleById<AssetView>(newId);
                #endregion

                #region
                foreach (var item in list)
                {
                    long id = sqlConn.Insert(item, selectIdentity: true);
                    ids.Add(id);
                }
                #endregion

            }
        }

        public List<AssetView> List()
        {
            throw new NotImplementedException();
        }

        public List<AssetView> FindByIds(int eid, List<int> ids)
        {
            string sql = string.Format("select * from asset where a.EId = @EId and a.Id in ({0})", string.Join(",", ids));
            //using (var sqlConn = _connFab.OpenDBConnection(_cfg.DBConnectionString1))
            using (var sqlConn = _connFab.OpenDBConnection("Data Source=.;Initial Catalog=dbassetcheck;User ID=sa;Password=000000;"))
            {
                return sqlConn.SqlList<AssetView>(sql, new List<SqlParameter>() { new SqlParameter("EId", eid) });
            }
        }

        public AssetView FindById(int eid, int id)
        {
            AssetView item = FindByIds(eid, new List<int>() { id }).FirstOrDefault();

            if (item != null)
            {
                item.Specs = GetNullToDefault(item.Specs);
                item.SNNo = GetNullToDefault(item.SNNo);
                item.Name = GetNullToDefault(item.Name);
            }
            return item;
        }

        public AssetView GetById(int id)
        {
            string sql = string.Format(@"select * From Asset where EId = {0} and BillNo = {1}", 1, 100);
            string sql2 = string.Format(@"select Id from Asset where AssetBorrowRevertId = {0}", 1);
            string sql3 = @" select count(1) From AssetBorrowRevert where EId =@EId and UserEmployeeId =@UserEmployeeId and SignatureStatus=1 ";
            string sql4 = string.Format(@"select top 1 No
                                            from AssetborrowRevert where EId = @EId and IsBorrow = 1 and No like 'JY{0}%'
                                            order by No desc", DateTime.Now.ToString("yyyyMMdd"));

            //using (var sqlConn = _connFab.OpenDBConnection(_cfg.DBConnectionString1))
            using (var sqlConn = _connFab.OpenDBConnection("Data Source=.;Initial Catalog=dbassetcheck;User ID=sa;Password=000000;"))
            {
                sqlConn.Single<AssetView>(sql);

                List<int> arr = sqlConn.Select<int>(sql2);

                sqlConn.Select<int>(sql3, new { UserEmployeeId = 1, EId = 1 }).First();

                var dbNo = sqlConn.Single<string>(sql4, new List<SqlParameter>() { new SqlParameter("EID", 1) });

                return sqlConn.SingleById<AssetView>(id);
            }
        }


        public void Update(List<AssetView> dbAssets)
        {
            //using (var sqlConn = _connFab.OpenDBConnection(_cfg.DBConnectionString1))
            using (var sqlConn = _connFab.OpenDBConnection("Data Source=.;Initial Catalog=dbassetcheck;User ID=sa;Password=000000;"))
            {
                sqlConn.Update(dbAssets);
            }
        }

        public void Update(AssetView dbAsset)
        {
            //using (var sqlConn = _connFab.OpenDBConnection(_cfg.DBConnectionString1))
            using (var sqlConn = _connFab.OpenDBConnection("Data Source=.;Initial Catalog=dbassetcheck;User ID=sa;Password=000000;"))
            {
                sqlConn.Update(dbAsset);
            }
        }

        public void UpdateIncludedInBill(int eid, List<int> assetIds, int? assetIncludedBill)
        {
            string sql = string.Format(@"update asset 
                            set IncludedInBill = {0}
                            where EId = {1} and Id in ({2})", assetIncludedBill, eid, string.Join(",", assetIds));

            string sql2 = string.Format(@"update PendingSignatures
	                                        set Status = {0}, BillHandleUserId={3}
	                                        where EId = {1} and BillNo = {2}", 0, eid, "", 1);

            string sql3 = string.Format(@"select * From Asset where EId = {0} and BillNo = {1}", eid, 100);

            //using (var sqlConn = _connFab.OpenDBConnection(_cfg.DBConnectionString1))
            using (var sqlConn = _connFab.OpenDBConnection("Data Source=.;Initial Catalog=dbassetcheck;User ID=sa;Password=000000;"))
            {
                sqlConn.ExecuteSql(sql);
                sqlConn.ExecuteNonQuery(sql2);
                sqlConn.Single<AssetView>(sql3);
            }
        }

        public T GetNullToDefault<T>(T value)
        {
            try
            {
                if (value == null)
                    return default(T);
                else
                    return (T)value;
            }
            catch (Exception)
            {
                return default(T);
            }
        }
    }
}