dot net操作sql服务器大全
'得到SQL服务器的列表
'必须安装SQL SERVER 2000 SP2 及以上版本
Dim I As Short
Dim sqlApp As New SQLDMO.Application()
Dim ServerName As SQLDMO.NameList
ServerName = sqlApp.ListAvailableSQLServers
For i = 1 To ServerName.Count
cbServer.Items.Add(ServerName.Item(i))
Next
'得到指定SQL服务器所有数据库的列表
Dim sqlApp As New SQLDMO.Application()
Dim oServer As New SQLDMO.SQLServer()
oServer.Connect("(local)", "sa", "sa")
cbDatabase.Items.Clear()
Dim db As SQLDMO.Database
For Each db In oServer.Databases
Me.cbDatabase.Items.Add(db.Name)
Next
Dim I As Short
Dim oServer As New SQLDMO.SQLServer()
oServer.Connect("(local)", "sa", "sa")
Dim db As New SQLDMO.Database()
For I = 1 To oServer.Databases.Count
If oServer.Databases.Item(I, "dbo").Name = "Northwind" Then Exit For
Next
If I > oServer.Databases.Count Then Exit Sub
db = oServer.Databases.Item(I, "dbo")
ListBox1.Items.Clear()
'得到所有的存储过程
For I = 1 To db.StoredProcedures.Count
ListBox1.Items.Add(db.StoredProcedures.Item(I, "dbo").Name)
Next
'得到所有的表
For I = 1 To db.Tables.Count
ListBox1.Items.Add(db.Tables.Item(I, "dbo").Name)
Next
' 得到所有的视图
For I = 1 To db.Views.Count
ListBox1.Items.Add(db.Views.Item(I, "dbo").Name)
Next
'̀添加进度条ProgressBar1控件
'̉引用Microsoft SQLDMO Object Library
'声明
Public WithEvents bkps As SQLDMO.Backup
'数据库备份操作
Private Sub btnBackUp_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBackUp.Click
Dim oSQLServer As New SQLDMO.SQLServer()
oSQLServer.LoginSecure = False
oSQLServer.Connect("(local)", "sa", "sa") '连接服务器
Me.Cursor = Windows.Forms.Cursors.WaitCursor
bkps = CreateObject("SQLDMO.Backup")
bkps.Database = "Northwind" '指定需备份的数据库
bkps.Action = 0
bkps.Files = "f:\Northwind.bak" '指定备份文件
bkps.Initialize = True
ProgressBar1.Value = 0
ProgressBar1.Maximum = 100
Me.Cursor = Windows.Forms.Cursors.Default()
Application.DoEvents()
Dim mouseCur As Cursor
Me.Cursor = Windows.Forms.Cursors.WaitCursor
bkps.SQLBackup(oSQLServer)
ProgressBar1.Value = 100
Application.DoEvents()
bkps = Nothing
Me.Cursor = Windows.Forms.Cursors.Default()
MsgBox("数据库备份完成", MsgBoxStyle.Information, "系统消息")
End Sub
'显示进度
Private Sub bkps_PercentComplete(ByVal Message As String, ByVal Percent As Integer) Handles bkps.PercentComplete
ProgressBar1.Value = ProgressBar1.Maximum * (Percent / 100)
End Sub
SQLDMO For C#
By Kevin Goss
Download SQLDMO.zip
Many times I have had a need to get at SQL Server details in my applications. Until recently I had to use API calls and bastardized ADO calls to get the information I needed. Now we have SQLDMO (SQL Distributed Management Objects) . Although not widely known or used, SQLDMO provides a very powerful set of functionality to do just about anything with an SQL Server from code. For the purposes of this example I will show how to retrieve a list of SQL Servers on your local network, how to connect to one, and how to retrieve a list of tables, stored procedures, or views from a server.
The SQLDMO object comes from the SQLDMO.dll that ships with SQL Server 2000. The dll itself is a COM object and you must reference it from your .net project as such. The IDE will create the necessary COM wrappers needed to use the library. NOTE: IF YOU USE THE STATEMENT "using SQLDMO;" IN YOUR APP YOU MAY GET AN ERROR.
(YOU MUST RE-REFERENCE THE COM OBJECT FOR THE SAMPLE APP TO WORK)
After referencing the COM object, you can begin using it quite easily.
All of the operations performed in the example use one or more of the following objects:
- SQLDMO.Application
- SQLDMO.SQLServer
- SQLDMO.Database
- SQLDMO.NameList
There are a multitude of objects available for actions such as backups and restores, but for the purpose of this article I decided to keep it simple to ease you into the world of SQLDMO.
Listing the available SQL Servers on your network is quite simple. First you need a references SQLDMO.Application object. Next you set an instance of SQLDMO.NameList to the return value of the SQLDMO.Application.ListAvailableSQLServers() method. The SQLDMO.NameList if a COM collection of the server names.
Keep in mind, calling COM objects is a little funky until you get used to it, but the conventions are similar with all of them. Here is example code which fills a combo box name cboServers with a list of all available SQL Servers on the local network:
SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
SQLDMO.NameList sqlServers = sqlApp.ListAvailableSQLServers();
for(int i=0;i<sqlServers.Count;i++)
{
object srv = sqlServers.Item(i + 1);
if(srv != null)
{
this.cboServers.Items.Add(srv);
}
}
if(this.cboServers.Items.Count > 0)
this.cboServers.SelectedIndex = 0;
else
this.cboServers.Text = "<No available SQL Servers>";
As you can see, this is quite simple. Just remember that COM collections start at an index of 1, not 0.
Connecting to a server and getting a list of databases is also fairly simple. The following code will take the chosen SQL Server in the combo box, connect to it (with a user name and password in 2 text boxes), and then poulates another combo box with a list of databases on the server.
SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
SQLDMO.SQLServer srv = new SQLDMO.SQLServerClass();
srv.Connect(this.cboServers.SelectedItem.ToString(),this.txtUser.Text,this.txtPassword.Text);
foreach(SQLDMO.Database db in srv.Databases)
{
if(db.Name!=null)
this.cboDatabase.Items.Add(db.Name);
}
Getting a list of objects by type is also a breeze with this library. Again, you make a connection to the database, and then you loop through the object collection.
SQLDMO.SQLServer srv = new SQLDMO.SQLServerClass();
srv.Connect(this.cboServers.SelectedItem.ToString(),this.txtUser.Text,this.txtPassword.Text);
for(int i=0;i<srv.Databases.Count;i++)
{
if(srv.Databases.Item(i+1,"dbo").Name == this.cboDatabase.SelectedItem.ToString())
{
SQLDMO._Database db= srv.Databases.Item(i+1,"dbo");
this.lstObjects.Items.Clear();
for(int j=0;j<db.StoredProcedures.Count;j++)
{
this.lstObjects.Items.Add(db.StoredProcedures.Item(j+1,"dbo").Name);
}
break;
}
}
Well folks, that is it for my SQLDMO beginners' tutorial. Please download the sample code and app to see it in action. As you can see, this is a much easier alternative when SQL information or control is needed. Happy coding!!!
