//去重操作 columnList = columnList.GroupBy(p => p.ColumnName).S...

schangxiang@126.com

2025-05-07 06c2653afafc14fac832ba7ba39624b040d39ec5

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
using GenerateCode_GEBrilliantFactory;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
 
namespace GenerateCode_WeiBen_WMS.DataBaseFactory
{
    public class MySqlDataBaseServiceHandle : DataBaseServiceHandle
    {
        public override List<ColumnModel> GetColumnList(string tableName, string connStr)
        {
            //string strSql = $"SELECT COLUMN_NAME as ColumnName, COLUMN_TYPE as DataType, IS_NULLABLE as IsNullable, CHARACTER_MAXIMUM_LENGTH as DataLength,COLUMN_COMMENT as Description " +
            //                       $"FROM INFORMATION_SCHEMA.COLUMNS " +
            //                       $"WHERE  TABLE_NAME = '{tableName}'";
            string strSql = $"SELECT  c.COLUMN_NAME as ColumnName, c.DATA_TYPE as DataType, c.IS_NULLABLE as IsNullable, c.CHARACTER_MAXIMUM_LENGTH as DataLength, c.COLUMN_COMMENT as Description,c.NUMERIC_SCALE as MyPrecision, " +
                $"CASE WHEN kcu.COLUMN_NAME IS NOT NULL THEN 'true' ELSE 'false' END AS IsPrimaryKey " +
                $"FROM INFORMATION_SCHEMA.COLUMNS c " +
                $"LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu " +
                $"ON c.TABLE_SCHEMA = kcu.TABLE_SCHEMA " +
                $"AND c.TABLE_NAME = kcu.TABLE_NAME " +
                $"AND c.COLUMN_NAME = kcu.COLUMN_NAME " +
                $"AND kcu.CONSTRAINT_NAME = 'PRIMARY' " +
                $"WHERE c.TABLE_NAME = '{tableName}'" +
                $"ORDER BY c.ORDINAL_POSITION";
 
 
            List<ColumnModel> columnList = new List<ColumnModel>();
            try
            {
                DataSet ds = MySqlHelper.Query(connStr, strSql);
                columnList = DataTableToList(ds.Tables[0]);
                //去重操作
                columnList = columnList.GroupBy(p => p.ColumnName).Select(g => g.First()).ToList();
            }
            catch
            {
                throw;
            }
            finally
            {
            }
            return columnList;
        }
 
        public override List<ColumnModel> DataTableToList(DataTable dt)
        {
            try
            {
                List<ColumnModel> modelList = new List<ColumnModel>();
                int rowsCount = dt.Rows.Count;
                if (rowsCount > 0)
                {
                    ColumnModel model;
                    for (int n = 0; n < rowsCount; n++)
                    {
                        model = new ColumnModel();
                        //if (dt.Rows[n]["Precision"]!=null && dt.Rows[n]["Precision"].ToString() != "")
                        //{
                        //    model.Precision = int.Parse(dt.Rows[n]["Precision"].ToString());
                        //}
                        //if (dt.Rows[n]["Scale"].ToString() != "")
                        //{
                        //    model.Scale = int.Parse(dt.Rows[n]["Scale"].ToString());
                        //}
                        model.ColumnName = dt.Rows[n]["ColumnName"].ToString();
                        model.DataLength = dt.Rows[n]["DataLength"].ToString();
                        model.DataType = dt.Rows[n]["DataType"].ToString();
 
                        if (model.DataType.ToUpper() == "nvarchar".ToUpper())
                        {
                            if (model.DataLength != "-1")
                            {
                                model.DataLength = ((int)Convert.ToInt32(model.DataLength) / 2).ToString();
                            }
                        }
 
                        model.Description = dt.Rows[n]["Description"].ToString();
                        if (dt.Rows[n]["IsNullable"].ToString() != "")
                        {
                            if ((dt.Rows[n]["IsNullable"].ToString() == "1") || (dt.Rows[n]["IsNullable"].ToString().ToLower() == "true") || (dt.Rows[n]["IsNullable"].ToString().ToLower() == "yes"))
                            {
                                model.IsNullable = true;
                            }
                            else
                            {
                                model.IsNullable = false;
                            }
                        }
                        if (dt.Rows[n]["IsPrimaryKey"].ToString() != "")
                        {
                            if ((dt.Rows[n]["IsPrimaryKey"].ToString() == "1") || (dt.Rows[n]["IsPrimaryKey"].ToString().ToLower() == "true"))
                            {
                                model.IsPrimaryKey = true;
                            }
                            else
                            {
                                model.IsPrimaryKey = false;
                            }
                        }
 
                        modelList.Add(model);
                    }
                }
                return modelList;
            }
            catch (Exception)
            {
 
                throw;
            }
        }
    }
}