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);
}
