/// <summary>
/// 登录模式
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void cmbUserType_SelectedIndexChanged(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(this.txtSqlServerName.Text.Trim()))
{
MessageBox.Show(@"请输入有效的服务器名称，哈");
return;
}
if (sender is ComboBox)
{
var ckbUserType = sender as ComboBox;
DataBaseConnectionConfig.ServerName = this.txtSqlServerName.Text.Trim();
switch (ckbUserType.SelectedIndex)
{
case 0:
this.PL_user.Enabled = false;
DataBaseConnectionConfig.ConnectionTypeValue = "integrated security=sspi";
GlobalContext.SqlConnectionString = DataBaseConnectionConfig.GetDataBaseConnectionString(string.Empty);
break;
case 1:
this.PL_user.Enabled = true;
this.txtUsername.Enabled = true;
this.txtPassword.Enabled = true;
break;
default:
break;
}
}
}
/// <summary>
/// 根据数据名获取所有表集合
/// </summary>
/// <param name="databaseName"></param>
/// <returns></returns>
public static List <TableContext> GetTableContextListByDataBaseName(string databaseName)
{
var list = new List <TableContext>();
const string sql = @"SELECT sysobject.name,extendpro.value FROM sysobjects AS sysobject
LEFT JOIN sys.extended_properties AS extendpro ON sysobject.id=extendpro.major_id AND extendpro.minor_id=0 AND extendpro.name='MS_Description'
WHERE sysobject.xtype ='u' order by sysobject.NAME
";
var cmd = new SqlCommand(sql);
using (var dataReader = new SqlDatabase(DataBaseConnectionConfig.GetDataBaseConnectionString(databaseName)).ExecuteReader(cmd))
{
// Processing code
while (dataReader.Read())
{
var table = new TableContext();
table.Name = dataReader.GetString(0);
table.Description = dataReader.GetValue(1) == null ?string.Empty:dataReader.GetValue(1).ToString();
//table.ColumnList = GetColumnContextListByTableName(table.TableName);
list.Add(table);
}
}
return(list);
}
/// <summary>
/// 确认环境
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void BT_OK_Click(object sender, EventArgs e)
{
if (this.cmbUserType.SelectedIndex == -1)
{
MessageBox.Show(@"请选择服务器认证方式,哈");
return;
}
if (this.cmbUserType.SelectedIndex == 1)
{
if (string.IsNullOrEmpty(this.txtUsername.Text.Trim()))
{
MessageBox.Show(@"请输入登录用户名，哈");
return;
}
if (string.IsNullOrEmpty(this.txtPassword.Text.Trim()))
{
MessageBox.Show(@"请输入登录密码，哈");
return;
}
GlobalContext.SqlConnectionString = DataBaseConnectionConfig.GetDataBaseConnectionString(string.Empty);
DataBaseConnectionConfig.ConnectionTypeValue = string.Format("uid={0};pwd={1}", this.txtUsername.Text.Trim(), this.txtPassword.Text.Trim());
}
this.loginThread = new Thread(new ThreadStart(this.LoginAction));
this.loginThread.Start();
}
public static List <ColumnContext> GetColumnContextListByTableName(string databaseName, string tableName)
{
var list = new List <ColumnContext>();
var sql = @" select
col.COLUMN_NAME as columnName,
(case when PKeyCol.COLUMN_NAME is null then '' else 'PK' end) +
(case when KeyCol2.COLUMN_NAME is null then ''
when NOT PKeyCol.COLUMN_NAME is null then ',FK' else 'FK' end) as kk,--PK=主键,FK=外键,PKFK=是主键同时外键
col.DATA_TYPE as dataType,
(case when CHARACTER_MAXIMUM_LENGTH is null then '' else CAST(CHARACTER_MAXIMUM_LENGTH as varchar(50)) end) as columnLength,
col.IS_NULLABLE as isNullAble,--NO=不能为空,YES=可空
col.COLUMN_DEFAULT as defaultValue,
ISNULL (CAST(coldesc.[value] AS nvarchar(50)) , '') AS columnDescription
from INFORMATION_SCHEMA.COLUMNS as col
LEFT OUTER JOIN
(select COLUMN_NAME,TABLE_NAME FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE KeyCol
LEFT OUTER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RefCol ON
KeyCol.CONSTRAINT_CATALOG=RefCol.CONSTRAINT_CATALOG AND
KeyCol.CONSTRAINT_NAME=RefCol.CONSTRAINT_NAME
WHERE RefCol.CONSTRAINT_NAME IS NULL) PKeyCol
ON PKeyCol.COLUMN_NAME=Col.COLUMN_NAME AND PKeyCol.TABLE_NAME=Col.TABLE_NAME
LEFT OUTER JOIN
(INFORMATION_SCHEMA.KEY_COLUMN_USAGE KeyCol2
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RefCol2 ON
KeyCol2.CONSTRAINT_CATALOG=RefCol2.CONSTRAINT_CATALOG AND
KeyCol2.CONSTRAINT_NAME=RefCol2.CONSTRAINT_NAME)
ON KeyCol2.COLUMN_NAME=Col.COLUMN_NAME AND KeyCol2.TABLE_NAME=Col.TABLE_NAME
LEFT OUTER JOIN ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', '" + tableName + "', 'column', default) as coldesc ON col.COLUMN_NAME = coldesc.objname COLLATE Chinese_PRC_CI_AS where col.TABLE_NAME='" + tableName + "'";
var cmd = new SqlCommand(sql);
using (var dataReader = new SqlDatabase(DataBaseConnectionConfig.GetDataBaseConnectionString(databaseName)).ExecuteReader(cmd))
{
while (dataReader.Read())
{
var column = new ColumnContext();
column.Name = dataReader.GetString(0);//列名
column.IsPk = dataReader.GetString(1).Equals("PK");
column.IsFk = dataReader.GetString(1).Equals("FK");
column.DateType = dataReader.GetString(2); //数据类型
column.Length = string.IsNullOrEmpty(dataReader.GetString(3)) ? 0 : int.Parse(dataReader.GetString(3)); //数据长度
column.IsNull = dataReader.GetString(4).ToLower().Equals("yes"); //是否为空
column.DefaultValue = dataReader.GetValue(5) == null ? string.Empty : dataReader.GetValue(5).ToString(); //string.IsNullOrEmpty(dataReader.GetString(5)) ? string.Empty : dataReader.GetString(5);//默认值
column.NameDescription = dataReader.GetString(6); //字段说明
list.Add(column);
}
}
return(list);
}
