如何创建允许用户选择Ms Sql Server / Database的Vb.Net Gui [英] How Do I Create A Vb.Net Gui That Allows The User To Select The Ms Sql Server/Database
问题描述
我第一次问一个问题所以请你好看:)
我正在学习(作为个人兴趣)使用VB.net和MS SQL为我所做的事情创建应用程序。
id就像创建一个gui登录屏幕,让我看到任何活动的MS SQL服务器和数据库与该实例相关联。
单击它然后提供登录详细信息以创建与该数据库的连接。
如果有人知道阅读材料会对此有所帮助那么我将非常感激。
我试过搜索但我的结果与创建连接有关在代码中。
感谢您的时间
-Darren
ok从以下非常有帮助的帖子我有以下代码
的简单表格,下拉列表中填写了所有找到的服务器
我想从中选择服务器下拉列表并按下刷新以填充列表视图。
仅使用列表视图,因为我还没有玩过那个。
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlClient
Public 类 Form1
私有 Sub Form1_Load(sender As System。 Object ,e As System.EventArgs)句柄 MyBase .Load
Dim dt As DataTable = SqlDataSourceEnumerator.Instance.GetDataSources
For 每个 dr As DataRow 在 dt .Rows
ddlInstances.Items.Add( String .Concat(dr( ServerName), \\, dr( InstanceName)))
下一步
结束 Sub
私有 Sub Button1_Click(发件人 As 系统。对象,e As System.EventArgs)句柄 Button1。点击
' 按此按钮用所选服务器实例上的数据库列表填充LVDB
结束 Sub
私有 Sub LVDBs_SelectedIndexChanged(sender As System。 Object ,e As System.EventArgs)< span class =code-keyword>句柄 LVDBs.SelectedIndexChanged
结束 Sub
结束 类
列出SQL服务器实例:
Dim instance As SqlDataSourceEnumerator = SqlDataSourceEnumerator.Instance
Dim dt As DataTable = instance.GetDataSources()
对于 每个行 As System.Data.DataRow 在 dt.Rows
Console.WriteLine( ServerName = {0},row( ServerName))
下一步
在特定服务器上列出SQL数据库:
使用 con 作为 新 SqlConnection(strConnect)
con.Open()
使用 cmd 作为 新 SqlCommand( sp_databases,con)
cmd.Co mmandType = CommandType.StoredProcedure
Dim 读取作为 SqlDataReader = cmd.ExecuteReader()
while read.Read()
Console.WriteLine( DirectCast (读取( DATABASE_NAME), String ))
结束
结束 使用
结束 使用显然,连接字符串需要登录详细信息,这是您无法自动获取的。
我需要根据我对您的问题的理解/解释做出一些假设。
1.您想知道本地网络中的哪些计算机系统运行MS SQL Server实例,
2.您希望查看每个计算机系统的数据库列表运行MS SQL Server实例。
要完成(1),您需要检索本地网络上所有计算机系统的列表。获得网络上所有计算机系统的列表后,您需要检查每个特定计算机系统上是否正在运行MS SQL实例。遗憾的是,由于其高级别的安全性,您将无法在没有该服务器的有效用户名和密码的情况下询问MS服务器。
代码示例适用于本地机器和那里有代码示例来检索远程计算机的进程列表。
' < span class =code-comment>检查MySQL是否在此系统上运行
私有 函数 IsMySQLRunning()作为 布尔
Dim proc() As System.Diagnostics.Process
试用
' 检查所有进程以查看MySQL是否正在运行
proc = System .Diagnostics.Process.G etProcessesByName( mysqld)
' < 0然后找到mysqld,= 0然后mysqld没有运行
如果 proc.Length> 0 然后
' < span class =code-comment> Found
返回 True
其他
' 未找到
MsgBox( 未发现MySQL服务器在此系统上运行。 & vbCrLf& _
请确认这是MySQL数据库服务器并且MySQL正在运行 ,_
MsgBoxStyle.Information)
返回 错误
结束 如果
Catch ex As 异常
' Damit,出了点问题。
MsgBox( 无法检查MySQL服务器是否在此系统上运行。& vbCrLf& _
ex.ToString,MsgBoxStyle.Information)
返回 错误
结束 尝试
结束 功能
要实现(2),您可以按照解决方案1进行操作超出我的直接范围,我不使用MS SQL。
好吧我切换到树视图显示相关的数据库,一切都运行良好。
谢谢你的帮助。
这是用于选择服务器和数据库的form1,如果你看到一些可以做的事情,请我可以从中学到更好,我很乐意听取你的意见。
进口 System.Data
Imports System.Data.Sql
Imports System.Data.SqlClient
Public 类 Form1
私有 myConn 作为 SqlConnection
私有 myCmd 作为 SqlCommand
公共 instance 作为 字符串
公共 database 作为 字符串
私有 Sub Form1_Load(sender As System。 Object ,e As System.EventArgs)句柄 MyBase 。加载
Dim dt As DataTable = SqlDataSourceEnumerator.Instance.GetDataSources
对于 每个 dr As DataRow In dt.Rows
ddlInstances.Items.Add( String .Concat(dr( ServerName), \,dr( InstanceName)))
下一步
结束 Sub
私有 Sub Button1_Click(发件人作为系统。 对象,e As System.EventArgs)句柄 Button1.Click
TVDBs.Nodes.Clear()
如果 ddlInstances.SelectedIndex = 0 那么
instance = ddlInstances.SelectedItem.ToString
myConn = 新 SqlConnection( 初始目录= Master;& _
数据源=& ddlInstances.SelectedItem.ToString& ;集成安全性= SSPI;)
myConn.Open()
使用 cmd 作为 新 SqlCommand( sp_databases,myConn)
cmd.CommandType = CommandType.StoredProcedure
Dim reader As SqlDataReader = cmd.ExecuteReader()
while reader.Read()
TVDBs.Nodes.Add(reader( DATABASE_NAME),reader( DATABASE_NAME), 0 , 1 )
结束while
结束 使用
myConn.Close()
其他
MessageBox.Show( < span class =code-string>请选择服务器)
结束 如果
结束 Sub
私有 Sub Button2_Click(发件人作为 System。 Object ,e As System.EventArgs) Handles Button2.Click
如果 TVDBs.SelectedNode 没什么 然后
MessageBox.Show( 请从列表中选择数据库)
其他
' MessageBox.Show(TVDBs.SelectedNode.Name.ToString)
database = TVDBs.SelectedNode.Name.ToString
Form2.Show()
结束 如果
结束 子
结束 类
my first time asking a question so please be nice :)
I'm learning(as a personal interest) to use VB.net and MS SQL to create applications for things that I do.
what id like is to create a gui log in screen that lets me see any active MS SQL servers and the database(s) associated to that instance.
click it and then provide login details to create the connection to that database.
if any one knows of reading materials that would help with this then that would be greatly appreciated.
I have tried searching but my results deal with creating the connection with in the code.
thanks for your time
-Darren
ok from the extremely helpful post below I have the following code
for a simple form with a drop down list filled with any found servers
I want to select the server from the drop down list and press refresh to populate a listview.
only using list view because I haven't played with that one yet.
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlClient
Public Class Form1
Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
Dim dt As DataTable = SqlDataSourceEnumerator.Instance.GetDataSources
For Each dr As DataRow In dt.Rows
ddlInstances.Items.Add(String.Concat(dr("ServerName"), "\\", dr("InstanceName")))
Next
End Sub
Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
'press this button to populate LVDBs with list of databases on selected server instance
End Sub
Private Sub LVDBs_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles LVDBs.SelectedIndexChanged
End Sub
End Class
List SQL server instances:
Dim instance As SqlDataSourceEnumerator = SqlDataSourceEnumerator.Instance Dim dt As DataTable = instance.GetDataSources() For Each row As System.Data.DataRow In dt.Rows Console.WriteLine("ServerName = {0}", row("ServerName")) Next
List SQL databases on a particular server:
Using con As New SqlConnection(strConnect) con.Open() Using cmd As New SqlCommand("sp_databases", con) cmd.CommandType = CommandType.StoredProcedure Dim read As SqlDataReader = cmd.ExecuteReader() While read.Read() Console.WriteLine(DirectCast(read("DATABASE_NAME"), String)) End While End Using End UsingObviously, the connection string needs login details, which you cannot get automatically.
I need to make a few assumptions here based on my understanding / interpretation of your question.
1. You want to know what computer systems in your local network runs a MS SQL Server instance,
2. You wish to see a list of databases for each computer system running an instance of MS SQL Server.
To accomplish (1) you will need to retrieve a list of all computer systems on your local network. Once you have a list of all computer systems on the network you will need to check to see if an instance of MS SQL is running on each specific computer system. Unfortunately you will not be allowed to interrogate a MS Server without a valid username and password for that server because of its high level security.
The code example is for the local machine and there are code examples out there to retrieve the process list of a remote computer.
'Check to see if MySQL is running on this system Private Function IsMySQLRunning() As Boolean Dim proc() As System.Diagnostics.Process Try 'Check all processes to see if MySQL is running proc = System.Diagnostics.Process.GetProcessesByName("mysqld") '<0 then mysqld was found, =0 then mysqld is not running If proc.Length > 0 Then 'Found Return True Else 'Not found MsgBox("MySQL Server was not found to be running on this system." & vbCrLf & _ "Please verify that this is the MySQL database server and that MySQL is running", _ MsgBoxStyle.Information) Return False End If Catch ex As Exception 'Damit, something went wrong. MsgBox("Unable to check if MySQL Server is running on this system." & vbCrLf & _ ex.ToString, MsgBoxStyle.Information) Return False End Try End Function
To achieve (2) you can follow Solution 1 as it is above my immediate scope and I don't work with MS SQL.
ok well I switched to treeview to display associated databases and all has worked very well.
thank you for helps guys.
this is form1 for selecting server and database and please if you see some thing that could be done better that I can learn from then I would love to hear from you.
Imports System.Data Imports System.Data.Sql Imports System.Data.SqlClient Public Class Form1 Private myConn As SqlConnection Private myCmd As SqlCommand Public instance As String Public database As String Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load Dim dt As DataTable = SqlDataSourceEnumerator.Instance.GetDataSources For Each dr As DataRow In dt.Rows ddlInstances.Items.Add(String.Concat(dr("ServerName"), "\", dr("InstanceName"))) Next End Sub Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click TVDBs.Nodes.Clear() If ddlInstances.SelectedIndex = 0 Then instance = ddlInstances.SelectedItem.ToString myConn = New SqlConnection("Initial Catalog=Master;" & _ "Data Source=" & ddlInstances.SelectedItem.ToString & ";Integrated Security=SSPI;") myConn.Open() Using cmd As New SqlCommand("sp_databases", myConn) cmd.CommandType = CommandType.StoredProcedure Dim reader As SqlDataReader = cmd.ExecuteReader() While reader.Read() TVDBs.Nodes.Add(reader("DATABASE_NAME"), reader("DATABASE_NAME"), 0, 1) End While End Using myConn.Close() Else MessageBox.Show("Please Select Server") End If End Sub Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click If TVDBs.SelectedNode Is Nothing Then MessageBox.Show("Please select Database From List") Else ' MessageBox.Show(TVDBs.SelectedNode.Name.ToString) database = TVDBs.SelectedNode.Name.ToString Form2.Show() End If End Sub End Class
这篇关于如何创建允许用户选择Ms Sql Server / Database的Vb.Net Gui的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!