LIOT/Framework/Bowin.Common/Linq/Sql/DbcontextExt.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Transactions;
using System.Data.Linq;
using System.Data.SqlClient;
using System.Data;
using System.ComponentModel;
using System.Linq.Expressions;

namespace Bowin.Common.Linq.Sql
{
    /// <summary>
    /// 扩展linqtoSql对表操作以及事务的简化方法
    /// creator:wufs
    /// </summary>
    public static class DbcontextExt
    {
        /// <summary>
        /// 新增数据
        /// </summary>
        /// <typeparam name="T">表实体</typeparam>
        /// <param name="databasecontext"></param>
        /// <param name="Entity">表实体对象</param>
        public static void Insert<T>(this DataContext databasecontext, T Entity) where T : class
        {
            databasecontext.GetTable<T>().InsertOnSubmit(Entity);
            databasecontext.SubmitChanges();

        }

        /// <summary>
        /// 更新数据
        /// </summary>
        /// <typeparam name="T">表实体</typeparam>
        /// <param name="databasecontext"></param>
        /// <param name="aWhere">条件</param>
        /// <param name="aEntity">更新字段描述</param>
        public static void Update<T>(this DataContext databasecontext, Func<T, bool> aWhere, Action<T> aEntity) where T : class
        {
            var sqlrows = databasecontext.GetTable<T>().AsQueryable().Where(aWhere);
            foreach (var row in sqlrows)
            {
                aEntity(row);
                
            }
            databasecontext.SubmitChanges();
        }


        public static void Update<T>(this DataContext databasecontext, Func<T, bool> aWhere) where T : class
        {

        }


        /// <summary>
        /// 删除数据
        /// </summary>
        /// <typeparam name="T">表实体</typeparam>
        /// <param name="databasecontext"></param>
        /// <param name="aWhere">条件</param>
        public static void Delete<T>(this DataContext databasecontext, Func<T, bool> aWhere) where T : class
        {
            var sqlrows = databasecontext.GetTable<T>().AsQueryable().Where(aWhere);
            databasecontext.GetTable<T>().DeleteAllOnSubmit<T>(sqlrows);
            databasecontext.SubmitChanges();

        }

        /// <summary>
        /// 获得表数据
        /// </summary>
        /// <typeparam name="T">表实体</typeparam>
        /// <param name="databasecontext"></param>
        /// <param name="aWhere">条件</param>
        /// <returns>表的结果数据列</returns>
        public static List<T> Get<T>(this DataContext databasecontext, Func<T, bool> aWhere) where T : class
        {
            return databasecontext.GetTable<T>().AsQueryable().Where(aWhere).ToList();
        }

        /// <summary>
        /// 带事务新增数据
        /// 必需调用_CommitTran()提交方法才生效
        /// </summary>
        /// <typeparam name="T">表实体</typeparam>
        /// <param name="databasecontext"></param>
        /// <param name="Entity">表实体对象</param>
        public static void _Insert<T>(this DataContext databasecontext, T Entity) where T : class
        {
            databasecontext.GetTable<T>().InsertOnSubmit(Entity);
        }

        /// <summary>
        /// 带事务更新数据
        /// 必需调用_CommitTran()提交方法才生效
        /// </summary>
        /// <typeparam name="T">表实体</typeparam>
        /// <param name="databasecontext"></param>
        /// <param name="aWhere">条件</param>
        /// <param name="aEntity">更新字段描述</param>
        public static void _Update<T>(this DataContext databasecontext,Func<T, bool> aWhere, Action<T> aEntity) where T : class
        {
            var sqlrows = databasecontext.GetTable<T>().AsQueryable().Where(aWhere);
            foreach (var row in sqlrows)
            {
                aEntity(row);

            }

        }

        public static void _Update<T>(this DataContext databasecontext, Func<T, bool> aWhere) where T : class
        {
        }


        /// <summary>
        /// 带事务删除数据
        /// 必需调用_CommitTran()提交方法才生效
        /// </summary>
        /// <typeparam name="T">表实体</typeparam>
        /// <param name="databasecontext"></param>
        /// <param name="aWhere">条件</param>
        public static void _Delete<T>(this DataContext databasecontext, Func<T, bool> aWhere) where T : class
        {
            var sqlrows = databasecontext.GetTable<T>().AsQueryable().Where(aWhere);
            databasecontext.GetTable<T>().DeleteAllOnSubmit<T>(sqlrows);

        }

        /// <summary>
        /// 带事务操作提交方法
        /// </summary>
        /// <param name="databasecontext"></param>
        public static void _CommitTran(this DataContext databasecontext)
        {
            databasecontext.SubmitChanges();
        }

        /// <summary>
        /// 分布式事务应用方法
        /// </summary>
        /// <param name="actions"></param>
        public static void _TranExecute(params Action[] actions)
        {
            _TranExecute(System.Transactions.IsolationLevel.ReadCommitted, 60, actions);
        }

        /// <summary>
        /// 分布式事务应用方法
        /// </summary>
        public static void _TranExecute(int timeOut, params Action[] actions)
        {
            _TranExecute(System.Transactions.IsolationLevel.ReadCommitted, timeOut, actions);
        }

        /// <summary>
        /// 分布式事务应用方法
        /// </summary>
        public static void _TranExecute(System.Transactions.IsolationLevel level, params Action[] actions)
        {
            _TranExecute(level, 60, actions);
        }

        /// <summary>
        /// 分布式事务应用方法
        /// </summary>
        public static void _TranExecute(System.Transactions.IsolationLevel level, int timeOut, params Action[] actions)
        {
            if (actions == null || actions.Length == 0)
            {
                return;
            }

            TransactionOptions options = new TransactionOptions();
            options.IsolationLevel = level;
            options.Timeout = new TimeSpan(0, 0, timeOut);
            using (TransactionScope tran = new TransactionScope(TransactionScopeOption.Required, options))
            {
                Array.ForEach<Action>(actions, a => a());

                tran.Complete();
            }
        }

        public static DataSet ExecuteQuerySqlDS(this DataContext databasecontext,CommandType comType, string sql,Dictionary<string,object> dicobjs)
        {
            SqlCommand com = new SqlCommand();
            com.CommandText = sql;
            com.Connection = databasecontext.Connection as SqlConnection; 
            com.CommandType = comType;
            com.CommandTimeout = databasecontext.CommandTimeout;

            foreach (var obj in dicobjs)
            {
                SqlParameter sqlParam = new SqlParameter();
                sqlParam.ParameterName = obj.Key;
                sqlParam.Value = obj.Value;
                com.Parameters.Add(sqlParam);
            }

            SqlDataAdapter sqlad = new SqlDataAdapter(com);
            DataSet ds = new DataSet("root");
            sqlad.Fill(ds);

            return ds;

        }

        public static DataSet ExecuteQuerySqlDS(this DataContext databasecontext,CommandType comType, string sql)
        {
            var dics = new Dictionary<string, object>();

            return databasecontext.ExecuteQuerySqlDS(comType, sql, dics);

        }

        public static DataSet ExecuteQuerySqlDS(this DataContext databasecontext, string sql, Dictionary<string, object> dics)
        {

            return databasecontext.ExecuteQuerySqlDS(CommandType.Text, sql, dics);

        }

        public static DataSet ExecuteQuerySqlDS(this DataContext databasecontext, string sql)
        {
            var dics=new Dictionary<string,object>();

            return databasecontext.ExecuteQuerySqlDS(CommandType.Text,sql, dics);

        }

        public static DataTable GetBulkCopyDataTable(this DataContext databasecontext, string TableName)
        {
            DataTable resultdt = new DataTable(TableName.Replace("[","").Replace("]",""));

            string sql = string.Format("select * from {0} where 1=2",TableName);

            SqlCommand com = new SqlCommand();
            com.CommandText = sql;
            com.Connection = databasecontext.Connection as SqlConnection;
            com.CommandType = CommandType.Text;
            com.CommandTimeout = databasecontext.CommandTimeout;

            SqlDataAdapter sqlad = new SqlDataAdapter(com);

            sqlad.Fill(resultdt);

            return resultdt;

        }

        public static bool ExecuteBulkCopy(this DataContext databasecontext, DataTable dt)
        {
            bool result = true;

            var sconn = databasecontext.Connection as SqlConnection;

            if (sconn.State == ConnectionState.Closed)
            {
                sconn.Open();
            }

            var stran = sconn.BeginTransaction();

            try
            {
                using (SqlBulkCopy bcp = new SqlBulkCopy(sconn, SqlBulkCopyOptions.Default, stran))
                {
                    bcp.DestinationTableName = dt.TableName;
                    bcp.WriteToServer(dt);
                    stran.Commit();

                    if (sconn.State == ConnectionState.Open)
                        sconn.Close();
                }
            }
            catch (Exception ex)
            {
                result = false;
                stran.Rollback();
                if (sconn.State == ConnectionState.Open)
                    sconn.Close();
            }
            

            return result;

        }

        
    }
}