SqlSugar框架日常查询测试可用

最新推荐文章于 2023-12-27 20:31:53 发布

北街末雨o

最新推荐文章于 2023-12-27 20:31:53 发布

阅读量129

点赞数

文章标签： c# sql

本文链接：https://blog.csdn.net/qq_43186632/article/details/132873008

版权

    /// <summary>
    /// 记录查询日志log
    /// </summary>
    /// <param name="query_text">查询</param>
    /// <param name="localIP">IP地址</param>
    void InsertToLogQuery(string query_text,string localIP)
    {
        try
        {
            connection.Open();
            //开启事务
            db.Ado.BeginTran();
            #region 执行
            localIP = string.Empty;
            using (Socket socket = new Socket(AddressFamily.InterNetwork, SocketType.Dgram, 0))
            {
                socket.Connect("8.8.8.8", 65530);
                IPEndPoint endPoint = socket.LocalEndPoint as IPEndPoint;
                localIP = endPoint.Address.ToString();
            }

            query_text = "SELECT TOP 100    qs.last_execution_time,    DB_NAME(st.dbid) AS database_name,    OBJECT_NAME(st.objectid, st.dbid) AS object_name,    SUBSTRING(st.text, (qs.statement_start_offset / 2) + 1,        ((CASE qs.statement_end_offset           WHEN - 1 THEN DATALENGTH(st.text)           ELSE qs.statement_end_offset          END - qs.statement_start_offset)/ 2)+1) AS executed_sql FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st ORDER BY qs.last_execution_time DESC; ";
            //参数
            var query_text1 = new SugarParameter("@query_text", query_text);
            var localIP1 = new SugarParameter("@ip_address", localIP);
            //执行存储过程
            //db.Ado.UseStoredProcedure().GetDataTable("LogQuery", query_text1, localIP1);
            #endregion 执行
            //提交事务
            db.Ado.CommitTran();

            //查询
            var data = db.Queryable<tbiz_worktask>().Where(x => x.work_id == 20000 || x.work_id == 20001).ToList();
            if (data.Count > 0)
            {
                
            }

            //动态表名 ，动态条件 
            //var list = db.Queryable<dynamic>().AS("query_logs ").Where("id=@id", new { id = 1 }).ToList();//不需要实体
            var list = db.Queryable<query_logs>().Where(it => it.id == 1 || it.Query_text.Contains("from")).ToList();
            if (list.Count >0)
            {

            }

            //等同于IN
            var getAll1 = db.Queryable<query_logs>()
            .Where(it => SqlFunc.Subqueryable<tbiz_worktask>().Where(s => s.work_id == it.id).Any()).ToList();

            //等同于NotIN
            var getAll2 = db.Queryable<query_logs>()
            .Where(it => SqlFunc.Subqueryable<tbiz_worktask>().Where(s => s.work_id == it.id).NotAny()).ToList();


            string xxx = "";
            string xxx2 = "";
            // 注册OnLogExecuting事件
            db.Aop.OnLogExecuting = (sqls, pars) =>
            {
                xxx = ("执行SQL：" + sqls);
                xxx2 = ("参数：" + pars.ToJson());
            };
            //子查询中使用Sql
            string sql = " (select top 1 id from [query_logs] ) ";
            var lis = db.Queryable<query_logs>()
                          .Where(i => SqlFunc.Subqueryable<query_logs>().Where(s => s.id == 1).Any()).ToSqlString();

            xxx = xxx2 + xxx;

        }
        catch (Exception ex)
        {
            //回滚事务
            db.Ado.RollbackTran();
            throw ex;
        }
    }