//连接
protected void BtnLink_Click(object sender, EventArgs e)
{
if (TbIpAdd.Text.Trim() == string.Empty)
{
LbTooltip.Text = "请输入服务器地址！";
return;
}
if (TbUser.Text.Trim() == string.Empty)
{
LbTooltip.Text = "请输入用户名！";
return;
}
if (TbPwd.Text.Trim() == string.Empty)
{
LbTooltip.Text = "请输入密码！";
return;
}
SqlConnection conn = CodeToolsHelper.OpenCn();
try
{
GetDataBase();
LbTooltip.Text = "连接成功";
}
catch (Exception ex)
{
LbTooltip.Text = "连接失败";
}
}
/// <summary>
/// 获取数据库名称
/// </summary>
public void GetDataBase()
{
CodeToolsHelper.ConnStr = "server=" + TbIpAdd.Text.Trim() + ";user id=" + TbUser.Text.Trim() + ";pwd=" + TbPwd.Text.Trim() + ";";
string cmdText = "select name from master..sysdatabases";
DDLDataBase.DataTextField = "name";
DDLDataBase.DataValueField = "name";
DDLDataBase.DataSource = CodeToolsHelper.GetDataSet(cmdText).Tables[0];
DDLDataBase.DataBind();
GetDataTable();
DataBindColumns();
}
/// <summary>
/// 获取数据库对应的表
/// </summary>
public void GetDataTable()
{
CodeToolsHelper.ConnStr = "server=" + TbIpAdd.Text.Trim() + ";user id=" + TbUser.Text.Trim() + ";pwd=" + TbPwd.Text.Trim() + ";database=" + DDLDataBase.SelectedValue;
string cmdText = "select name from sysObjects where xtype='u' order by name";
DataTable ds = CodeToolsHelper.GetDataSet(cmdText).Tables[0];
DDLDataTable.DataTextField = "name";
DDLDataTable.DataValueField = "name";
DDLDataTable.DataSource = ds;
DDLDataTable.DataBind();
RepDataTable.DataSource = ds;
RepDataTable.DataBind();
RepTables.DataSource = ds;
RepTables.DataBind();
DataBindColumns();
}
/// <summary>
/// //获取数据表中字段的信息
/// </summary>
/// <param name="tableName">表名</param>
/// <returns></returns>
public DataTable GetColumnsToTable(string tableName)
{
//获取数据表中字段的信息
//string cmdText = "SELECT SysObjects.Name as TableName,SysColumns.Name as ColumnsName, "
// + "SysTypes.Name as ColumnsType,SysColumns.Length as ColumnsLength "
// + "FROM SysObjects,SysTypes,SysColumns WHERE (Sysobjects.Xtype='u' OR Sysobjects.Xtype ='v') "
// + "AND Sysobjects.Id = Syscolumns.Id AND SysTypes.XType = Syscolumns.XType "
// + "AND SysTypes.Name <> 'sysname' AND SysObjects.name = '" + tableName + "' ";
CodeToolsHelper.ConnStr = "server=" + TbIpAdd.Text.Trim() + ";user id=" + TbUser.Text.Trim() + ";pwd=" + TbPwd.Text.Trim() + ";database=" + DDLDataBase.SelectedValue;
string cmdText = "SELECT ColumnsName=C.name,ColumnDesc=ISNULL(PFD.[value],N''),ColumnsType=T.name "
+ "FROM sys.columns C INNER JOIN sys.objects O ON C.[object_id]=O.[object_id] "
+ "AND O.type='U' AND O.is_ms_shipped=0 INNER JOIN sys.types T ON C.user_type_id=T.user_type_id "
+ "LEFT JOIN sys.extended_properties PFD ON PFD.class=1 AND C.[object_id]=PFD.major_id AND C.column_id=PFD.minor_id "
+ "WHERE O.name=N'" + tableName + "' "
+ "ORDER BY O.name,C.column_id ";
return(CodeToolsHelper.GetDataSet(cmdText).Tables[0]);
}
