public override void SetValues( CustomDataAccess.CustomDataRow row )
{
foreach ( CustomDataColumn column in row )
{
if ( column.Name == "CreatorRoleTypeID" )
creatorRoleTypeForTitle = Utility.ZeroIfNull( column.Value );
}
base.SetValues( row );
}
private void tsbConnect_Click(object sender, EventArgs e)
{
try
{
//非空判断
string strTableName = cbbTableName.Text.Trim();
string strWhere = rtbWhere.Text.Trim();
if (string.IsNullOrEmpty(strTableName) && string.IsNullOrEmpty(strWhere))
{
MsgHelper.ShowErr("表名和Where条件不能同时为空！");
return;
}
//得到服务器对象
_dbServer = uC_DbConnection1.GetDbServerInfo();
if (_dbServer == null)
{
return;
}
//得到数据库访问对象
ICustomDataAccess customDataAccess = new CustomDataAccess(_dbServer.DatabaseType, _dbServer);
//构造查询SQL
if (string.IsNullOrEmpty(strWhere)) //Where条件为空
{
_strMainSql = "SELECT * FROM " + strTableName;
}
else if (string.IsNullOrEmpty(strTableName))//表名为空，那么Where中为自定义SQL
{
_strMainSql = strWhere;
}
else //表名和Where条件都不为空，那么拼接语句
{
_strMainSql = "SELECT * FROM " + strTableName + " WHERE " + strWhere;
}
//查询数据
DataTable dtMain = customDataAccess.DataAccess.QueryHadParamSqlData(_strMainSql, _dicQuery);
dtMain.TableName = _strTableName;
bsTable.DataSource = dtMain;
//设置数据源
GlobalValue.Instance.SetPublicDataSource(new DataTable[] { dtMain });
dgvTableList.DataSource = bsTable;
}
catch (Exception ex)
{
MsgHelper.ShowErr(ex.Message);
}
}
public DbServerInfo GetDbServerInfo()
{
var DbServer = new DbServerInfo()
{
Database = txbDbName.Text.Trim(),
DatabaseType = (DataBaseType)int.Parse(cbbDatabaseType.SelectedValue.ToString()),
LoginMode = (cbbLoginType.SelectedValue == null || cbbLoginType.SelectedValue.ToString() == "1") ? LoginModeEnum.SQL : LoginModeEnum.Windows,
Password = txbPassword.Text.Trim(),
PortNo = txbPortNO.Text.Trim(),
SchemaName = txbSchemaName.Text.Trim(),
ServerName = txbServerIP.Text.Trim(),
UserName = txbUserName.Text.Trim(),
};
int iDbType = int.Parse(cbbDatabaseType.SelectedValue.ToString());
DataBaseType selectDBType = (DataBaseType)iDbType;
if (IsDbNameNotNull && string.IsNullOrEmpty(DbServer.Database))
{
MsgHelper.ShowErr("数据库名称不能为空！");
return(null);
}
switch (selectDBType)
{
case DataBaseType.SqlServer:
if (string.IsNullOrEmpty(DbServer.ServerName))
{
MsgHelper.ShowErr("服务器地址不能为空！");
return(null);
}
if (DbServer.LoginMode == LoginModeEnum.SQL)
{
if (string.IsNullOrEmpty(DbServer.UserName) || string.IsNullOrEmpty(DbServer.Password))
{
MsgHelper.ShowErr("用户名和密码都不能为空！");
return(null);
}
}
//显示登录类型
lblLoginType.Visible = true;
cbbLoginType.Visible = true;
break;
case DataBaseType.Oracle:
if (string.IsNullOrEmpty(DbServer.ServerName))
{
MsgHelper.ShowErr("TNS名称不能为空！");
return(null);
}
if (string.IsNullOrEmpty(DbServer.UserName) || string.IsNullOrEmpty(DbServer.Password))
{
MsgHelper.ShowErr("用户名和密码都不能为空！");
return(null);
}
break;
case DataBaseType.MySql:
if (string.IsNullOrEmpty(DbServer.ServerName))
{
MsgHelper.ShowErr("服务器地址不能为空！");
return(null);
}
if (string.IsNullOrEmpty(DbServer.UserName) || string.IsNullOrEmpty(DbServer.Password))
{
MsgHelper.ShowErr("用户名和密码都不能为空！");
return(null);
}
break;
case DataBaseType.SQLite:
if (string.IsNullOrEmpty(DbServer.ServerName))
{
MsgHelper.ShowErr("数据库文件路径不能为空！");
return(null);
}
break;
case DataBaseType.PostgreSql:
if (string.IsNullOrEmpty(DbServer.ServerName))
{
MsgHelper.ShowErr("服务器地址不能为空！");
return(null);
}
if (string.IsNullOrEmpty(DbServer.UserName) || string.IsNullOrEmpty(DbServer.Password))
{
MsgHelper.ShowErr("用户名和密码都不能为空！");
return(null);
}
break;
default:
throw new Exception("暂不支持该数据库类型！");
//break;
}
//得到数据库访问对象
CustomDac = new CustomDataAccess(selectDBType, DbServer);
UserTableList = CustomDac.DataAccess.GetUserTableList();//所有用户表
//返回
return(DbServer);
}
private void tsbImport_Click(object sender, EventArgs e)
{
try
{
dsExcel = new DataSet();
_dbServer = uC_DbConnection1.GetDbServerInfo();
DataTable dtMain;
DataTable dtSec;
if (_dbServer == null)
{
return;
}
string sTableName = cbbTableName.Text.Trim();
#region 读取数据库的表数据生成SQL处理
#region 确定SQL
if (_dbServer.DatabaseType == DataBaseType.SqlServer)
{
#region SqlServer读取数据库
if (!string.IsNullOrEmpty(_dbServer.SchemaName)) //有架构名时
{
//查询表
_strMainSql = string.Format(
@"SELECT B.NAME OWNER,A.NAME TABLE_NAME
FROM SYS.OBJECTS A
JOIN SYS.SCHEMAS B ON A.SCHEMA_ID=B.SCHEMA_ID
WHERE A.TYPE='U' AND A.NAME='{0}' AND B.NAME='{1}'"
, sTableName, _dbServer.SchemaName);
//查询表的所有列（不要的列在界面上删除）
_strSecondSql = string.Format(
@"SELECT A.COLID COLUMN_ID,A.NAME COLUMN_NAME,'' 固定值, '' 辅助查询值,
(SELECT TOP 1 NAME FROM SYS.TYPES WHERE USER_TYPE_ID = A.XUSERTYPE) DATA_TYPE,
A.LENGTH DATA_LENGTH,A.XPREC DATA_PRECISION,A.XSCALE DATA_SCALE,A.ISNULLABLE NULLABLE,
C.NAME AS OWNER,
B.NAME TABLE_NAME,A.COLSTAT
FROM SYSCOLUMNS A
JOIN (SELECT * FROM SYS.OBJECTS WHERE TYPE='U' AND NAME='{0}') B ON A.ID=B.OBJECT_ID
JOIN SYS.SCHEMAS C ON C.SCHEMA_ID=B.SCHEMA_ID
WHERE C.NAME='{1}'
ORDER BY A.COLID", sTableName, _dbServer.SchemaName);
}
else
{
//查询表
_strMainSql = string.Format(
@"SELECT B.NAME OWNER,A.NAME TABLE_NAME
FROM SYS.OBJECTS A
JOIN SYS.SCHEMAS B ON A.SCHEMA_ID=B.SCHEMA_ID WHERE A.TYPE='U' AND A.NAME='{0}'"
, sTableName);
//查询表的所有列（不要的列在界面上删除）
_strSecondSql = string.Format(
@"SELECT A.COLID COLUMN_ID,A.NAME COLUMN_NAME,'' 固定值, '' 辅助查询值,
(SELECT TOP 1 NAME FROM SYS.TYPES WHERE USER_TYPE_ID = A.XUSERTYPE) DATA_TYPE,
A.LENGTH DATA_LENGTH,A.XPREC DATA_PRECISION,A.XSCALE DATA_SCALE,A.ISNULLABLE NULLABLE,
(SELECT TOP 1 NAME FROM SYS.SCHEMAS WHERE SCHEMA_ID=B.SCHEMA_ID) OWNER,
B.NAME TABLE_NAME,A.COLSTAT
FROM SYSCOLUMNS A
JOIN (SELECT * FROM SYS.OBJECTS WHERE TYPE='U' AND NAME='{0}') B ON A.ID=B.OBJECT_ID
ORDER BY A.COLID",
sTableName);
}
#endregion
}
else if (_dbServer.DatabaseType == DataBaseType.Oracle)
{
#region Oracle读取数据库
if (!string.IsNullOrEmpty(_dbServer.SchemaName)) //当输入架构名称时
{
//查询表
_strMainSql = string.Format(
@"SELECT A.OWNER,A.TABLE_NAME
FROM ALL_TABLES A
WHERE UPPER(A.TABLE_NAME)=UPPER('{0}') AND UPPER(A.OWNER)=UPPER('{1}')",
sTableName, _dbServer.SchemaName);
//查询所有列
_strSecondSql = string.Format(
@"SELECT A.COLUMN_ID,A.COLUMN_NAME,'' 固定值,'' 辅助查询值,A.DATA_TYPE,A.DATA_LENGTH,
A.DATA_PRECISION,A.DATA_SCALE,A.NULLABLE,A.OWNER,A.TABLE_NAME
FROM ALL_TAB_COLS A
WHERE UPPER(A.TABLE_NAME)=UPPER('{0}') AND UPPER(A.OWNER)=UPPER('{1}')
ORDER BY A.COLUMN_ID", sTableName, _dbServer.SchemaName);
}
else
{
//查询表
_strMainSql = string.Format(
@"SELECT A.OWNER,A.TABLE_NAME
FROM ALL_TABLES A
WHERE UPPER(A.TABLE_NAME)=UPPER('{0}')",
sTableName);
//查询所有列
_strSecondSql = string.Format(
@"SELECT A.COLUMN_ID,A.COLUMN_NAME,'' 固定值,'' 辅助查询值,A.DATA_TYPE,A.DATA_LENGTH,
A.DATA_PRECISION,A.DATA_SCALE,A.NULLABLE,A.OWNER,A.TABLE_NAME
FROM ALL_TAB_COLS A WHERE UPPER(TABLE_NAME)=UPPER('{0}') ORDER BY A.COLUMN_ID",
sTableName);
}
#endregion
}
else if (_dbServer.DatabaseType == DataBaseType.MySql)
{
#region Mariadb读取数据库
if (string.IsNullOrEmpty(_dbServer.Database))
{
MsgHelper.ShowErr("数据库名不能为空！");
return;
}
if (string.IsNullOrEmpty(sTableName))
{
MsgHelper.ShowErr("表名不能为空！");
cbbTableName.Focus();
return;
}
//查询表
_strMainSql = string.Format(
@"SHOW TABLES
WHERE TABLES_IN_" + _dbServer.Database + " = LOWER('{0}')",
sTableName);
//查询所有列
_strSecondSql = string.Format(
@"SHOW COLUMNS FROM {0}",
sTableName);
#endregion
}
else if (_dbServer.DatabaseType == DataBaseType.PostgreSql)
{
#region PostgreSql读取数据库
if (!string.IsNullOrEmpty(_dbServer.SchemaName)) //当输入架构名称时
{
//查询表
_strMainSql = string.Format(
@"SELECT A.SCHEMANAME AS OWNER,A.TABLENAME AS TABLE_NAME
FROM PG_TABLES A
WHERE 1=1
AND UPPER(A.TABLENAME)=UPPER('{0}') AND UPPER(A.SCHEMANAME)=UPPER('{1}')",
sTableName, _dbServer.SchemaName);
//查询所有列
_strSecondSql = string.Format(
@" SELECT A.ORDINAL_POSITION as COLUMN_ID,a.COLUMN_NAME,'' 固定值,'' 辅助查询值,a.DATA_TYPE,
A.CHARACTER_MAXIMUM_LENGTH as DATA_LENGTH,A.NUMERIC_PRECISION as DATA_PRECISION,
A.NUMERIC_SCALE as DATA_SCALE,A.IS_NULLABLE as NULLABLE,A.TABLE_SCHEMA as owner,A.TABLE_NAME as TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS A
LEFT JOIN (SELECT C.ATTNAME AS COLUMN_NAME
FROM PG_CONSTRAINT A
JOIN PG_CLASS B
ON A.CONRELID = B.OID
JOIN PG_ATTRIBUTE C
ON C.ATTRELID = B.OID
AND C.ATTNUM = A.CONKEY[1]
JOIN PG_TYPE D
ON D.OID = C.ATTTYPID
WHERE UPPER(B.RELNAME) = UPPER('{0}')
AND A.CONTYPE = 'p') PK ON A.COLUMN_NAME = PK.COLUMN_NAME
WHERE UPPER(A.TABLE_SCHEMA)=UPPER('{1}')
AND UPPER(A.TABLE_NAME)=UPPER('{0}')
ORDER BY A.ORDINAL_POSITION",
sTableName, _dbServer.SchemaName);
}
else
{
//查询表
_strMainSql = string.Format(@"SELECT A.SCHEMANAME AS OWNER,A.TABLENAME AS TABLE_NAME
FROM PG_TABLES A
WHERE 1=1
AND UPPER(A.TABLENAME)=UPPER('{0}')",
sTableName);
//查询所有列
_strSecondSql = string.Format(
@" SELECT A.ORDINAL_POSITION AS COLUMN_ID,A.COLUMN_NAME,'' 固定值,'' 辅助查询值,A.DATA_TYPE,
A.CHARACTER_MAXIMUM_LENGTH AS DATA_LENGTH,A.NUMERIC_PRECISION AS DATA_PRECISION,
A.NUMERIC_SCALE AS DATA_SCALE,A.IS_NULLABLE AS NULLABLE,A.TABLE_SCHEMA AS OWNER,A.TABLE_NAME AS TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS A
LEFT JOIN (SELECT C.ATTNAME AS COLUMN_NAME
FROM PG_CONSTRAINT A
JOIN PG_CLASS B
ON A.CONRELID = B.OID
JOIN PG_ATTRIBUTE C
ON C.ATTRELID = B.OID
AND C.ATTNUM = A.CONKEY[1]
JOIN PG_TYPE D
ON D.OID = C.ATTTYPID
WHERE UPPER(B.RELNAME) = UPPER('{0}')
AND A.CONTYPE = 'P') PK ON A.COLUMN_NAME = PK.COLUMN_NAME
WHERE UPPER(A.TABLE_SCHEMA)=UPPER('PUBLIC')
AND UPPER(A.TABLE_NAME)=UPPER('{0}')
ORDER BY A.ORDINAL_POSITION",
sTableName);
}
#endregion
}
else if (_dbServer.DatabaseType == DataBaseType.SQLite)
{
#region SQLite读取数据库
//查询表:type,name,TBL_NAME,rootpage,sql
_strMainSql = string.Format(
@"SELECT TBL_NAME AS TABLE_NAME
FROM SQLITE_MASTER
WHERE UPPER(TYPE)= 'TABLE' AND UPPER(NAME)= ('{0}')",
sTableName);
//查询所有列:cid,name,type,notnull,dflt_value,pk
_strSecondSql = string.Format(
@"PRAGMA TABLE_INFO('{0}')",
sTableName);
#endregion
}
else
{
throw new Exception("暂不支持该数据库类型！");
}
#endregion
_dataAccess = new CustomDataAccess(_dbServer.DatabaseType, _dbServer);
_dicQueryCondition.Clear();
#region 查询或构造表
if (_dbServer.DatabaseType == DataBaseType.MySql || _dbServer.DatabaseType == DataBaseType.SQLite)
{
DataTable dtMainTemp = _dataAccess.DataAccess.QueryHadParamSqlData(_strMainSql, _dicQueryCondition);
DataTable dtSecTemp = _dataAccess.DataAccess.QueryHadParamSqlData(_strSecondSql, _dicQueryCondition);
if (dtMainTemp.Rows.Count == 0)
{
MsgHelper.ShowErr("表不存在！");
return;
}
//生成表和列
GenerateTableColumn(out dtMain, out dtSec);
dtMain.Rows[0]["TABLE_NAME"] = dtMainTemp.Rows[0][0];
int i = 1;
foreach (DataRow row in dtSecTemp.Rows)
{
DataRow drNew = dtSec.NewRow();
if (_dbServer.DatabaseType == DataBaseType.MySql)
{
drNew["COLUMN_ID"] = i;
drNew["COLUMN_NAME"] = row["Field"];
drNew["DATA_TYPE"] = row["Type"];
drNew["NULLABLE"] = row["Null"];
}
else
{
drNew["COLUMN_ID"] = row["cid"];
drNew["COLUMN_NAME"] = row["name"];
drNew["DATA_TYPE"] = row["type"];
drNew["NULLABLE"] = row["notnull"];
}
dtSec.Rows.Add(drNew);
i++;
}
}
else
{
dtMain = _dataAccess.DataAccess.QueryHadParamSqlData(_strMainSql, _dicQueryCondition);
dtSec = _dataAccess.DataAccess.QueryHadParamSqlData(_strSecondSql, _dicQueryCondition);
}
#endregion
dtMain.TableName = _strTableName;
bsTable.DataSource = dtMain;
dtSec.TableName = _strColName;
bsCos.DataSource = dtSec;
#region SqlServer自增长列处理
if (_dbServer.DatabaseType == DataBaseType.SqlServer)
{
if (dtSec.Select("COLSTAT>0").Length > 0)
{
ckbMainKeyInsert.Enabled = true;
}
else
{
ckbMainKeyInsert.Enabled = false;
}
}
else
{
ckbMainKeyInsert.Visible = false; //不可见
}
#endregion
//设置数据源
GlobalValue.Instance.SetPublicDataSource(new DataTable[] { dtMain, dtSec });
dgvTableList.DataSource = bsCos;
//dgvColList.DataSource = null;
//设置网格样式
bsCos.AllowNew = false;
foreach (DataGridViewColumn dgvc in dgvTableList.Columns)
{
dgvc.ReadOnly = true;
if (dgvc.Name == "固定值" || dgvc.Name == "辅助查询值")
{
dgvc.ReadOnly = false;
}
}
#endregion
//导入成功后处理
tsbAutoSQL.Enabled = true;
tsbExport.Enabled = true;
tabControl1.SelectedTab = tpImport;
//导入成功提示
lblInfo.Text = _strImportSuccess;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void tsbImport_Click(object sender, EventArgs e)
{
_dbServer = uC_DbConnection1.GetDbServerInfo();
if (_dbServer == null)
{
return;
}
#region 查询数据库中的源表
if (_dbServer.DatabaseType == DataBaseType.Oracle)
{
#region Oracle增删改查SQL生成
if (!string.IsNullOrEmpty(_dbServer.SchemaName))
{
#region 架构不为空
//查询表
_strMainSql = string.Format(
@"SELECT A.OWNER,
A.TABLE_NAME,
B.COMMENTS
FROM ALL_TABLES A
JOIN ALL_TAB_COMMENTS B
ON A.TABLE_NAME = B.TABLE_NAME AND A.OWNER=B.OWNER
WHERE UPPER(A.TABLE_NAME) = UPPER('{0}') and UPPER(A.OWNER) = UPPER('{1}')",
cbbTableName.Text.Trim(), _dbServer.SchemaName);
//查询所有列
_strSecondSql = string.Format(
@"selECT A.COLUMN_ID ,
A.COLUMN_NAME,
'' 固定值,
B.COMMENTS,
A.DATA_TYPE,
A.DATA_LENGTH,
A.DATA_PRECISION,
A.DATA_SCALE,
A.NULLABLE,
A.OWNER,
A.TABLE_NAME,
(select decode(BB.COLUMN_NAME,null,0,1)
from all_constraints AA
join all_cons_columns BB on AA.CONSTRAINT_NAME=BB.CONSTRAINT_NAME
where UPPER(AA.TABLE_NAME) = UPPER(A.TABLE_NAME)
AND UPPER(BB.COLUMN_NAME) = UPPER(A.COLUMN_NAME)
AND AA.OWNER=BB.OWNER AND AA.OWNER=A.OWNER
and AA.CONSTRAINT_TYPE='P' and rownum=1) IS_PK
FROM ALL_TAB_COLS A
JOIN ALL_COL_COMMENTS B ON A.TABLE_NAME=B.TABLE_NAME AND A.COLUMN_NAME=B.COLUMN_NAME AND A.OWNER=B.OWNER
WHERE UPPER(A.TABLE_NAME) = UPPER('{0}') and UPPER(A.OWNER) = UPPER('{1}')
ORDER BY A.COLUMN_ID",
cbbTableName.Text.Trim(), _dbServer.SchemaName);
#endregion
}
else
{
#region 架构为空
//查询表
_strMainSql = string.Format(
@"SELECT A.OWNER,
A.TABLE_NAME,
B.COMMENTS
FROM ALL_TABLES A
JOIN ALL_TAB_COMMENTS B
ON A.TABLE_NAME = B.TABLE_NAME AND A.OWNER=B.OWNER
WHERE UPPER(A.TABLE_NAME) = UPPER('{0}')",
cbbTableName.Text.Trim());
//查询所有列
_strSecondSql = string.Format(
@"selECT A.COLUMN_ID ,
A.COLUMN_NAME,
'' 固定值,
B.COMMENTS,
A.DATA_TYPE,
A.DATA_LENGTH,
A.DATA_PRECISION,
A.DATA_SCALE,
A.NULLABLE,
A.OWNER,
A.TABLE_NAME,
(select decode(BB.COLUMN_NAME,null,0,1)
from all_constraints AA
join all_cons_columns BB on AA.CONSTRAINT_NAME=BB.CONSTRAINT_NAME
where UPPER(AA.TABLE_NAME) = UPPER(A.TABLE_NAME)
and UPPER(BB.COLUMN_NAME) = UPPER(A.COLUMN_NAME)
and AA.OWNER=BB.OWNER AND AA.OWNER=A.OWNER
and AA.CONSTRAINT_TYPE='P' and rownum=1) IS_PK
FROM ALL_TAB_COLS A
JOIN ALL_COL_COMMENTS B ON A.TABLE_NAME=B.TABLE_NAME AND A.COLUMN_NAME=B.COLUMN_NAME
WHERE UPPER(A.TABLE_NAME) = UPPER('{0}')
ORDER BY A.COLUMN_ID",
cbbTableName.Text.Trim());
#endregion
}
#endregion
}
else if (_dbServer.DatabaseType == DataBaseType.SqlServer)
{
#region SQL Server增删改查SQL生成
if (!string.IsNullOrEmpty(_dbServer.SchemaName))
{
#region 架构不为空时
//查询表
_strMainSql = string.Format(
@"SELECT b.name owner ,
a.name table_name,
c.value COMMENTS
FROM sys.objects a
JOIN sys.schemas b ON a.schema_id = b.schema_id
JOIN sys.extended_properties C ON c.major_id=a.object_id AND C.minor_id=0
WHERE a.type = 'U'
AND a.name = '{0}' and b.name= '{1}'",
cbbTableName.Text.Trim(), _dbServer.SchemaName);
//查询表的所有列（不要的列在界面上删除）
_strSecondSql = string.Format(
@"SELECT a.colid COLUMN_ID,
a.NAME COLUMN_NAME ,
'' 固定值 ,
c.value COMMENTS,
( SELECT TOP 1
NAME
FROM sys.types
WHERE user_type_id = a.xusertype
) DATA_TYPE ,
a.length DATA_LENGTH ,
a.xprec DATA_PRECISION ,
a.xscale DATA_SCALE ,
a.isnullable ,
D.Name as OWNER ,
b.NAME TABLE_NAME ,
a.COLSTAT
FROM syscolumns a
JOIN ( SELECT *
FROM sys.objects
WHERE type = 'U'
AND name = '{0}'
) b ON a.id = b.object_id
JOIN sys.extended_properties C ON c.major_id = b.object_id
AND C.minor_id = a.colid
AND c.name = 'MS_Description'
JOIN sys.schemas D ON D.schema_id = b.SCHEMA_ID
where D.name='{1}'"
, cbbTableName.Text.Trim(), _dbServer.SchemaName);
#endregion
}
else
{
#region 架构为空时
//查询表
_strMainSql = string.Format(
@"SELECT b.name owner ,
a.name table_name,
c.value COMMENTS
FROM sys.objects a
JOIN sys.schemas b ON a.schema_id = b.schema_id
JOIN sys.extended_properties C ON c.major_id=a.object_id AND C.minor_id=0
WHERE a.type = 'U'
AND a.name = '{0}'",
cbbTableName.Text.Trim());
//查询表的所有列（不要的列在界面上删除）
_strSecondSql = string.Format(
@"SELECT a.colid COLUMN_ID,
a.NAME COLUMN_NAME ,
'' 固定值 ,
c.value COMMENTS,
( SELECT TOP 1
NAME
FROM sys.types
WHERE user_type_id = a.xusertype
) DATA_TYPE ,
a.length DATA_LENGTH ,
a.xprec DATA_PRECISION ,
a.xscale DATA_SCALE ,
a.isnullable ,
( SELECT TOP 1
name
FROM sys.schemas
WHERE schema_id = b.SCHEMA_ID
) OWNER ,
b.NAME TABLE_NAME ,
a.COLSTAT
FROM syscolumns a
JOIN ( SELECT *
FROM sys.objects
WHERE type = 'U'
AND name = '{0}'
) b ON a.id = b.object_id
JOIN sys.extended_properties C ON c.major_id = b.object_id
AND C.minor_id = a.colid
AND c.name = 'MS_Description'", cbbTableName.Text.Trim());
#endregion
}
#endregion
}
else
{
throw new Exception("暂不支持该数据库类型！");
}
#endregion
dsExcel = new DataSet();
#region 生成增删改查SQL
_dataAccess = new CustomDataAccess(_dbServer.DatabaseType, _dbServer);
DataTable dtMain = _dataAccess.DataAccess.QueryHadParamSqlData(_strMainSql, _dicQueryCondition);
dtMain.TableName = _strTableName;
bsTable.DataSource = dtMain;
DataTable dtSec = _dataAccess.DataAccess.QueryHadParamSqlData(_strSecondSql, _dicQueryCondition);
//增加条件列
dtSec.Columns.Add("条件", typeof(bool));
//增加选择列
DataColumn dcSelected = new DataColumn("选择", typeof(bool));
dcSelected.DefaultValue = Boolean.TrueString;
dtSec.Columns.Add(dcSelected);
dtSec.TableName = _strColName;
bsCos.DataSource = dtSec;
//设置数据源
GlobalValue.Instance.SetPublicDataSource(new DataTable[] { dtMain, dtSec });
dgvTableList.DataSource = bsTable;
dgvColList.DataSource = bsCos;
//设置网格样式
bsCos.AllowNew = false;
foreach (DataGridViewColumn dgvc in dgvColList.Columns)
{
dgvc.ReadOnly = true;
if (dgvc.Name == "固定值" || dgvc.Name == "条件" || dgvc.Name == "选择")
{
dgvc.ReadOnly = false;
}
}
dgvColList.Columns["条件"].DisplayIndex = 0;
dgvColList.Columns["条件"].Width = 60;
dgvColList.Columns["选择"].DisplayIndex = 0;
dgvColList.Columns["选择"].Width = 60;
dgvColList.Columns["COLUMN_ID"].Width = 40;
dgvColList.Columns["COMMENTS"].Width = 100;
#endregion
//导入成功后处理
tsbAutoSQL.Enabled = true;
tabControl1.SelectedTab = tpImport;
//导入成功提示
lblInfo.Text = _strImportSuccess;
}