• 设为首页
  • 点击收藏
  • 手机版
    手机扫一扫访问
    迪恩网络手机版
  • 关注官方公众号
    微信扫一扫关注
    公众号

c#遍历Mysql所有表所有列,查找目标数据

原作者: [db:作者] 来自: [db:来源] 收藏 邀请
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApp1
{
    class Program
    {
        static void Main(string[] args)
        {
            List<MyTable> list = GetTableList();
            Query(list, "1111aaaa");
            Console.WriteLine("over");
            Console.ReadLine();
        }

        static List<MyTable> GetTableList()
        {
            using (MySqlConnection conn = GetConnection())
            {
                Dictionary<string, MyTable> dic = new Dictionary<string, MyTable>();
                MySqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "select table_name, column_name from information_schema.columns where table_schema = 'mydb';";
                using (MySqlDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        string table = reader.GetString("table_name");
                        string column = reader.GetString("column_name");
                        if (dic.ContainsKey(table))
                        {
                            dic[table].ColumnList.Add(column);
                        }
                        else
                        {
                            MyTable t = new MyTable();
                            t.Table = table;
                            t.ColumnList.Add(column);
                            dic.Add(t.Table, t);
                        }
                    }
                }
                return dic.Values.ToList();
            }
        }

        static void Query(List<MyTable> list, string str)
        {
            using (MySqlConnection conn = GetConnection())
            {
                MySqlCommand cmd = conn.CreateCommand();
                foreach (MyTable table in list)
                {
                    foreach (string column in table.ColumnList)
                    {
                        cmd.CommandText = string.Format("select count(*) from {0} where `{1}` like '%{2}%'", table.Table, column, str);
                        object obj = cmd.ExecuteScalar();
                        if (Convert.ToInt32(obj) > 0)
                        {
                            Console.WriteLine(string.Format("TableName: {0}, ColumnName: {1}", table.Table, column));
                        }
                    }
                }
            }
        }

        static MySqlConnection GetConnection()
        {
            MySqlConnection conn = new MySqlConnection("server=localhost;port=3306;user id=userid;password=pass;database=mydb;pooling=true;ConnectionTimeout=1800");
            conn.Open();
            return conn;
        }
    }

    public class MyTable
    {
        public string Table { get; set; }
        public List<string> ColumnList { get; set; } = new List<string>();
    }
}

  


鲜花

握手

雷人

路过

鸡蛋
该文章已有0人参与评论

请发表评论

全部评论

专题导读
热门推荐
热门话题
阅读排行榜

扫描微信二维码

查看手机版网站

随时了解更新最新资讯

139-2527-9053

在线客服(服务时间 9:00~18:00)

在线QQ客服
地址:深圳市南山区西丽大学城创智工业园
电邮:jeky_zhao#qq.com
移动电话:139-2527-9053

Powered by 互联科技 X3.4© 2001-2213 极客世界.|Sitemap