public static DAL.DO_ColumnDescription.UOList_ColumnDescription GetColumnDescription(ProjectParameter projParam, string tableName) { DAL.DO_ColumnDescription da = new GetTogether.Studio.Database.DAL.DO_ColumnDescription(); ConnectionInformation conInfo = new ConnectionInformation(); conInfo.ConnectionString = projParam.ConnectionString; conInfo.DbType = projParam.DatabaseTypeForCodeEngineer; da.SetConnectionInformation(conInfo); string sql = string.Empty; switch (projParam.DatabaseTypeForCodeEngineer) { case DatabaseType.MySQL: sql = string.Format("SELECT C.Column_name as Name,case when C.extra='auto_increment' then 1 else 0 end as AutoIncrement FROM information_schema.`COLUMNS` C where C.table_schema='{0}' and table_name='{1}' and column_key='PRI'", BLL.BO_Common.GetDatabase(projParam), tableName); break; case DatabaseType.Oracle: break; case DatabaseType.SQLServer: sql = string.Format("select c.name column_name,p.value remark from sysproperties p join sysobjects o on p.id=o.id join syscolumns c on o.id=c.id and c.colid=p.smallid where p.name='MS_Description' and o.name='{0}'", tableName); try { return da.GetList(sql); } catch { string sqlRetry = string.Format("select c.name AS [Name],p.value Remark from sys.extended_properties p join sysobjects o on p.major_id=o.id join syscolumns c on o.id=c.id and c.colid=p.minor_id where p.name='MS_Description' and o.name='{0}'", tableName); return da.GetList(sqlRetry); } default: break; } return da.GetList(sql); }
public static DAL.DO_ColumnDetail.UOList_ColumnDetail GetColumnDetail(ProjectParameter projParam, string tableName) { DAL.DO_ColumnDetail da = new GetTogether.Studio.Database.DAL.DO_ColumnDetail(); ConnectionInformation conInfo = new ConnectionInformation(); conInfo.ConnectionString = projParam.ConnectionString; conInfo.DbType = projParam.DatabaseTypeForCodeEngineer; da.SetConnectionInformation(conInfo); string sql = string.Empty; switch (projParam.DatabaseTypeForCodeEngineer) { case DatabaseType.MySQL: sql = string.Format("select column_name AS ColumnName,column_default AS DefaultValue,case when is_nullable='YES' THEN 1 ELSE 0 end AS IsNullable,data_type AS DataType,character_maximum_length as MaxLength from INFORMATION_SCHEMA.COLUMNS where table_name='{0}'", tableName); break; case DatabaseType.Oracle: break; case DatabaseType.SQLServer: sql = string.Format("select column_name AS ColumnName,column_default AS DefaultValue,case when is_nullable='YES' THEN 1 ELSE 0 end AS IsNullable,data_type AS DataType,character_maximum_length as MaxLength from INFORMATION_SCHEMA.COLUMNS where table_name='{0}'", tableName); break; default: break; } return da.GetList(sql); }
public static void SetConnectionInformation(ConnectionInformation connInfo, ProjectParameter projParam) { connInfo.ConnectionString = projParam.ConnectionString; connInfo.DbType = projParam.DatabaseTypeForCodeEngineer; }
public static DAL.DO_PrimaryKey.UOList_PrimaryKey GetPrimaryKey(ProjectParameter projParam, string tableName) { string sql = string.Empty; switch (projParam.DatabaseTypeForCodeEngineer) { case DatabaseType.MySQL: sql = string.Format("SELECT C.Column_name as Name,case when C.extra='auto_increment' then 1 else 0 end as AutoIncrement FROM information_schema.`COLUMNS` C where C.table_schema='{0}' and table_name='{1}' and column_key='PRI' ORDER BY C.Column_name", BLL.BO_Common.GetDatabase(projParam), tableName); break; case DatabaseType.Oracle: break; case DatabaseType.SQLServer: sql = string.Format("select syscolumns.name AS [Name],columnproperty(syscolumns.id, syscolumns.name, 'IsIdentity') AS AutoIncrement from syscolumns,sysobjects,sysindexes,sysindexkeys where syscolumns.id = object_id('{0}') and sysobjects.xtype = 'pk' and sysobjects.parent_obj = syscolumns.id and sysindexes.id = syscolumns.id and sysobjects.name = sysindexes.name and sysindexkeys.id = syscolumns.id and sysindexkeys.indid = sysindexes.indid and syscolumns.colid = sysindexkeys.colid ORDER BY [Name]", tableName); break; default: break; } DAL.DO_PrimaryKey da = new GetTogether.Studio.Database.DAL.DO_PrimaryKey(); ConnectionInformation conInfo = new ConnectionInformation(); conInfo.ConnectionString = projParam.ConnectionString; conInfo.DbType = projParam.DatabaseTypeForCodeEngineer; da.SetConnectionInformation(conInfo); GetTogether.Studio.Database.DAL.DO_PrimaryKey.UOList_PrimaryKey primaryKeys = da.GetList(sql); if (primaryKeys == null || primaryKeys.Count == 0) { List<string> autoIncrementColumns = BLL.Table.GetAutoIncrement(projParam, tableName); primaryKeys = new GetTogether.Studio.Database.DAL.DO_PrimaryKey.UOList_PrimaryKey(); foreach (string pk in autoIncrementColumns) { Database.DAL.DO_PrimaryKey.UO_PrimaryKey upk = new GetTogether.Studio.Database.DAL.DO_PrimaryKey.UO_PrimaryKey(); upk.Name = pk; primaryKeys.Add(upk); } } return primaryKeys; }