public static SqlColumnInfo GetDeleteColumnNameAndValue(string table_name, List <SqlColumnInfo> current) { List <SqlColumnInfo> result = new List <SqlColumnInfo>(); var tbInfo = dataList.Find(p => p.db_name == Cache_Next.GetDbName() && p.type == 0); if (tbInfo != null) { if (tbInfo.extendInfo != null && tbInfo.extendInfo.type == 0 && tbInfo.extendInfo.v1 == 1) { var item = current.Find(p => p.Name == tbInfo.extendInfo.name); if (item != null) { SqlColumnInfo col = new SqlColumnInfo() { Name = item.Name, DefaultValue = tbInfo.extendInfo.value }; return(col); } } } return(null); }
public List <SqlColumnInfo> GetColumnsList(string server, string name, string pwd, int port, string dbname, string tablename) { string mysqlConnectionStr = string.Format("server={0};uid={1};pwd={2};database={4};port={3};", server, name, pwd, port, dbname); string selectSql = string.Format("select COLUMN_NAME,COLUMN_DEFAULT,IS_NULLABLE,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,COLUMN_KEY,EXTRA,COLUMN_COMMENT from information_schema.columns where TABLE_SCHEMA='{1}' and table_name='{0}'", tablename, dbname); List <SqlColumnInfo> result = new List <SqlColumnInfo>(); using (MySqlConnection sqlcn = new MySqlConnection(mysqlConnectionStr)) { using (MySqlDataReader mysqldr = MySqlHelper2.ExecuteDataReader(sqlcn, CommandType.Text, selectSql)) { while (mysqldr.Read()) { SqlColumnInfo info = new SqlColumnInfo(); info.Name = mysqldr["COLUMN_NAME"] == DBNull.Value ? "" : mysqldr["COLUMN_NAME"].ToString(); info.DbType = mysqldr["DATA_TYPE"] == DBNull.Value ? "" : mysqldr["DATA_TYPE"].ToString(); info.Length = mysqldr["CHARACTER_MAXIMUM_LENGTH"] == DBNull.Value ? "" : mysqldr["CHARACTER_MAXIMUM_LENGTH"].ToString(); info.IsMainKey = (mysqldr["COLUMN_KEY"] == DBNull.Value ? "" : mysqldr["COLUMN_KEY"].ToString()) == "PRI"; info.IsAllowNull = (mysqldr["IS_NULLABLE"] == DBNull.Value ? "" : mysqldr["IS_NULLABLE"].ToString()) == "YES"; info.DefaultValue = mysqldr["COLUMN_DEFAULT"] == DBNull.Value ? "[null]" : mysqldr["COLUMN_DEFAULT"].ToString(); info.IsAutoIncrement = (mysqldr["EXTRA"] == DBNull.Value ? "" : mysqldr["EXTRA"].ToString()) == "auto_increment"; info.Comment = mysqldr["COLUMN_COMMENT"] == DBNull.Value ? "" : mysqldr["COLUMN_COMMENT"].ToString(); result.Add(info); } } } return(result); }
public List <SqlColumnInfo> GetColumnsList(string server, string name, string pwd, int port, string dbname, string tablename) { string mysqlConnectionStr = string.Format("server={0};uid={1};pwd={2};database={4};", server, name, pwd, port, dbname); string selectSql = string.Format(@"SELECT 表名=case when a.colorder=1 then d.name else '' end, 字段名=a.name, 标识=case when COLUMNPROPERTY(a.id,a.name,'IsIdentity')=1 then '√' else '' end, 主键=case when exists(SELECT 1 FROM sysobjects where xtype= 'PK' and name in ( SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid ))) then '√' else '' end, 类型=b.name, 占用字节数=a.length, 长度=COLUMNPROPERTY(a.id,a.name, 'PRECISION'), 小数位数=isnull(COLUMNPROPERTY(a.id,a.name, 'Scale'),0), 允许空=case when a.isnullable=1 then '√'else '' end, 默认值=isnull(e.text, ''), 字段说明=isnull(g.[value], '') FROM syscolumns a left join systypes b on a.xtype=b.xusertype inner join sysobjects d on a.id=d.id and d.xtype= 'U' and d.name <> 'dtproperties' and d.name = '{0}' left join syscomments e on a.cdefault=e.id left join sys.extended_properties g on a.id=g.major_id and a.colid=g.minor_id and g.name='MS_Description' order by a.id,a.colorder", tablename); List <SqlColumnInfo> result = new List <SqlColumnInfo>(); using (SqlConnection sqlcn = new SqlConnection(mysqlConnectionStr)) { SqlCommand sqlcm = new SqlCommand(); sqlcm.Connection = sqlcn; sqlcm.CommandText = selectSql; sqlcm.CommandType = CommandType.Text; sqlcn.Open(); using (SqlDataReader sqldr = sqlcm.ExecuteReader()) { while (sqldr.Read()) { SqlColumnInfo info = new SqlColumnInfo(); info.Name = sqldr["字段名"] == DBNull.Value ? "" : sqldr["字段名"].ToString(); info.DbType = sqldr["类型"] == DBNull.Value ? "" : sqldr["类型"].ToString(); info.Length = sqldr["长度"] == DBNull.Value ? "" : sqldr["长度"].ToString(); info.IsMainKey = (sqldr["主键"] == DBNull.Value ? "" : sqldr["主键"].ToString()) == "√"; info.IsAllowNull = (sqldr["允许空"] == DBNull.Value ? "" : sqldr["允许空"].ToString()) == "√"; info.DefaultValue = sqldr["默认值"] == DBNull.Value ? "[null]" : sqldr["默认值"].ToString(); info.IsAutoIncrement = (sqldr["标识"] == DBNull.Value ? "" : sqldr["标识"].ToString()) == "√"; info.Comment = sqldr["字段说明"] == DBNull.Value ? "" : sqldr["字段说明"].ToString(); result.Add(info); } } } return(result); }