打造自己的 C# WinForm 应用程序的 SQL Server 连接配置界面
在C# WinForm 应用程序非常需要一个 SQL Server 连接配置界面,许多时候,因 SQL Server 服务器地址变更或 数据库登录账户 变更引起的连接失败等情况,客户就可能打电话“找麻烦”。既然这样,还不如提供一个可视化的配置界面,并在用户手册中说明使用方法,尽可能避免这种小问题带来的烦恼。为此,我将自己无聊时写的连接配置源码贴出来给初学者参考,以备不时之需!
想必大家都很熟悉VS服务器资源管理器中的【添加连接】对话框吧!下面是它的截图:
再看看我模仿这个对话框打造的WinForm程序SQL Server 连接配置界面:
呵呵!有几分相似吧!需要的朋友可以参考下面的源码。这份源码是完整的,粘贴到VS中即可使用。
窗体源码:
- using System;
- using System.Collections.Generic;
- using System.ComponentModel;
- using System.Data;
- using System.Drawing;
- using System.Text;
- using System.IO;
- using System.Windows.Forms;
- using System.Data.Sql;
- using System.Data.SqlClient;
- using System.Configuration;
- namespace CodingMouse.CMCSharpSDK.UI.Forms
- {
- /// <summary>
- /// 数据库连接配置界面
- /// </summary>
- public partial class frmConnectionConfig : Form
- {
- #region Private Members
- /// <summary>
- /// SQL Server 连接字符串创建者对象
- /// </summary>
- SqlConnectionStringBuilder _connectionStringBuilder = null;
- /// <summary>
- /// 当前应用程序名称
- /// </summary>
- string _applicationName;
- #endregion
- #region Private Methods
- /// <summary>
- /// 获取 本地网络所有 SQL Server 实例(数据源)
- /// </summary>
- private void GetSqlDataSource()
- {
- // 显示提示信息
- string msg = "正在获取本地网络所有 SQL Server 服务器信息 ...";
- this.toolTip.ToolTipIcon = ToolTipIcon.Info;
- this.toolTip.ToolTipTitle = "请稍候...";
- Point showLocation = new Point(
- this.lblServer.Left + 2,
- this.lblServer.Top + this.lblServer.Height);
- this.toolTip.Show(msg, this, showLocation, 1000);
- // 创建 提供了一种枚举本地网络内的所有可用 SQL Server 实例的机制 的实例
- SqlDataSourceEnumerator sdsEnum = SqlDataSourceEnumerator.Instance;
- // 调用 检索包含有关所有可见 SQL Server 2000 或 SQL Server 2005 实例的信息的 DataTable 的方法
- DataTable serverDt = sdsEnum.GetDataSources();
- // 创建新列以拼接 ServerName 以及 InstanceName 列的内容(以此构建连接字符串的 Server / Data Source 项)
- DataColumn dcDataSource = new DataColumn("SqlDataSourceName", typeof(string));
- // 将新列添加到 DataTable
- serverDt.Columns.Add(dcDataSource);
- // 创建新列以用中文方式显示 IsClustered 列内容
- DataColumn dcIsClustered = new DataColumn("IsClusteredCHS", typeof(string));
- // 将新列添加到 DataTable
- serverDt.Columns.Add(dcIsClustered);
- // 遍历 DataTable 并给新列赋予拼接后的值
- foreach (DataRow dataRow in serverDt.Rows)
- {
- if (!string.IsNullOrEmpty(Convert.ToString(dataRow["InstanceName"])))
- dataRow["SqlDataSourceName"] = string.Format(@"{0}/{1}",
- Convert.ToString(dataRow["ServerName"]),
- Convert.ToString(dataRow["InstanceName"]));
- else
- dataRow["SqlDataSourceName"] = string.Format(@"{0}",
- Convert.ToString(dataRow["ServerName"]));
- dataRow["IsClusteredCHS"] =
- (Convert.ToString(dataRow["IsClustered"]).Trim().ToUpper() == "NO")
- ? "否" : ((Convert.ToString(dataRow["IsClustered"]).Trim().ToUpper() == "YES")
- ? "是" : dataRow["IsClustered"]);
- }
- // 如果包含数据行
- if (serverDt.Rows.Count > 0)
- {
- // 创建窗体数据源封装类实例并封装 DataTable
- BindingSource source = new BindingSource();
- source.DataSource = serverDt;
- // 设置 ComboBox 数据源
- this.cboSqlDataSource.DataSource = source;
- this.cboSqlDataSource.DisplayMember = "SqlDataSourceName";
- this.cboSqlDataSource.ValueMember = "SqlDataSourceName";
- // 设置 DataGridView 数据源
- this.dgvServerInfo.DataSource = source;
- // 设置中文列名
- this.dgvServerInfo.Columns["SqlDataSourceName"].HeaderText = "服务器名";
- this.dgvServerInfo.Columns["SqlDataSourceName"].DisplayIndex = 0;
- this.dgvServerInfo.Columns["ServerName"].HeaderText = "服务器物理名称";
- this.dgvServerInfo.Columns["ServerName"].DisplayIndex = 1;
- this.dgvServerInfo.Columns["ServerName"].Visible = false;
- this.dgvServerInfo.Columns["InstanceName"].HeaderText = "实例名";
- this.dgvServerInfo.Columns["InstanceName"].DisplayIndex = 2;
- this.dgvServerInfo.Columns["InstanceName"].Visible = false;
- this.dgvServerInfo.Columns["IsClustered"].HeaderText = "群集信息";
- this.dgvServerInfo.Columns["IsClustered"].DisplayIndex = 3;
- this.dgvServerInfo.Columns["IsClustered"].Visible = false;
- this.dgvServerInfo.Columns["IsClusteredCHS"].HeaderText = "属于群集";
- this.dgvServerInfo.Columns["IsClusteredCHS"].DisplayIndex = 4;
- this.dgvServerInfo.Columns["Version"].HeaderText = "版本";
- this.dgvServerInfo.Columns["Version"].DisplayIndex = 5;
- }
- }
- /// <summary>
- /// 获取当前服务器上所有数据库名称
- /// </summary>
- private void GetDataBaseName()
- {
- // 显示提示信息
- string msg = string.Format("正在获取服务器 [{0}] 上的数据库信息 ...", cboSqlDataSource.Text.Trim());
- this.toolTip.ToolTipIcon = ToolTipIcon.Info;
- this.toolTip.ToolTipTitle = "请稍候...";
- Point showLocation = new Point(
- this.lblServer.Left + 2,
- this.lblServer.Top + this.lblServer.Height);
- this.toolTip.Show(msg, this, showLocation, 1000);
- // 查询服务器上所有数据库的 SQL 查询命令
- string sqlTxt = "Select [Name] From [SysDatabases] Order By [Name]";
- // 保存结果的 DataTable
- DataTable dataBaseDt = new DataTable();
- // 创建连接对象
- using (SqlConnection con = new SqlConnection(GetConnectionString()))
- {
- // 执行查询
- try
- {
- // 创建适配器对象
- using (SqlDataAdapter adp = new SqlDataAdapter(sqlTxt, con))
- {
- // 将查询结果填充到 DataTable
- adp.Fill(dataBaseDt);
- }
- }
- catch { } // 不弹出异常消息
- }
- // 如果 DataTable 包含数据行
- if (dataBaseDt.Rows.Count > 0)
- {
- // 创建窗体数据绑定对象
- BindingSource source = new BindingSource();
- source.DataSource = dataBaseDt;
- // 将结果绑定到数据库列表
- cboDataBaseName.DataSource = source;
- cboDataBaseName.DisplayMember = "Name";
- cboDataBaseName.ValueMember = "Name";
- }
- else
- {
- // 移除数据库列表
- cboDataBaseName.DataSource = null;
- }
- }
- /// <summary>
- /// 获取连接字符串
- /// </summary>
- private string GetConnectionString()
- {
- // 重新创建连接字符串创建者
- _connectionStringBuilder = new SqlConnectionStringBuilder();
- // 获取服务器名称
- if (!string.IsNullOrEmpty(cboSqlDataSource.Text.Trim()))
- _connectionStringBuilder.DataSource = cboSqlDataSource.Text.Trim();
- // 获取登录类型
- if (rdoValidateBySQLServer.Checked)
- {
- if (!string.IsNullOrEmpty(txtUserName.Text.Trim()))
- _connectionStringBuilder.UserID = txtUserName.Text.Trim();
- if (!string.IsNullOrEmpty(txtPassword.Text.Trim()))
- _connectionStringBuilder.Password = txtPassword.Text.Trim();
- }
- // 获取默认数据库
- if (!string.IsNullOrEmpty(cboDataBaseName.Text.Trim())
- || !string.IsNullOrEmpty(txtDataBaseFilePath.Text.Trim())
- || !string.IsNullOrEmpty(txtLogicalName.Text.Trim()))
- {
- // 如果是附加一个数据库文件
- if (rdoAttachADataBaseFile.Checked)
- {
- _connectionStringBuilder.AttachDBFilename = txtDataBaseFilePath.Text.Trim();
- _connectionStringBuilder.InitialCatalog = txtLogicalName.Text.Trim();
- }
- else
- _connectionStringBuilder.InitialCatalog = cboDataBaseName.Text.Trim();
- }
- // 调整连接字符串
- if (rdoValidateByWindows.Checked)
- _connectionStringBuilder.IntegratedSecurity = true;
- // 返回连接字符串
- return _connectionStringBuilder.ConnectionString;
- }
- #endregion
- #region Public Methods
- /// <summary>
- /// 无参构造
- /// </summary>
- public frmConnectionConfig(string applicationName)
- {
- // 构建设计器控件
- InitializeComponent();
- // 保存当前应用程序名称
- _applicationName = applicationName;
- // 创建连接字符串创建者
- _connectionStringBuilder = new SqlConnectionStringBuilder();
- _connectionStringBuilder.IntegratedSecurity = true;