delphi中对MS SQL Server 2000的操作组件

2016-08-22 10:20:54来源:http://suguiyang.blog.51cto.com/1035725/417008作者:su_guiyang人点击


unit DataBaseOptionsComp;


interface


uses //使用到的包
SysUtils, Classes,DB,ADODB,Variants,ComObj,winsvc,Registry,Windows,Messages,Dialogs,IniOptionsComp;


//const //---常量定义


type
TDataBaseOptions = class(TComponent) //---定义组件,继承于TComponen


private


//------------------- 属性定义 ---------------------//


FADOConnection : TADOConnection ; //-- 操作的ADO对象
FIniOptions : TIniOptions ;


public
constructor Create(AOwner: TComponent); override; //-- 重写create 方法,对属性进行初始化
destructor Destroy; override; //-- 重写Destroy 方法,释放资源


//------------------- 功能方法定义-----------------------//
procedure SetADOConnection(const ADOConn:TADOConnection);
procedure SetIniOptions(const IniOps: TIniOptions);


Function ConnADO2DBServer:Boolean;
Function ConnADO2DataBase:Boolean;overload;
Function ConnADO2DataBase(DBName:string):Boolean;overload;
function ClearADOConn:Boolean;
Function GetLANSQLServerList:TStrings;
Function GetSqlServerStatus:Integer;
Function IsExistsMSSQL: Boolean;
Function StartMSSQL:Boolean;
function StopMSSQL:Boolean;
function GetDataBaseNameList:TStrings;
function AttachDataBase(DBName,DataBaseFilePath,LogFilePath:String):Boolean;
function DetachDataBase(DBName:string):Boolean;
function IsExistsDataBase(DBName:string):Boolean;
function IsExistsProcedure(DBName,procName:string):Boolean;
function DeleteProcedure(DBName,procName:string):Boolean;
function CreateSpKillProcOnMaster:Boolean;
function GetDBConnCount(DBName:string):Integer;
function DelDBConnCount(DBName:string):Boolean;
function BackUpDataBase(DBName,BackUpFilePath:String):boolean;
function RestoreDataBase(DBName,restoreFilePath:String):Boolean;


published
{ Published declarations }

//--- 对属性值进行读取和保存操作,如 Get和Set
property ADOConnection: TADOConnection read FADOConnection write SetADOConnection;
property IniOptions: TIniOptions read FIniOptions write SetIniOptions;


end;


procedure Register;



implementation


{ Property Access }
//-------------------- 对属性值的存取方法 --------------------//


procedure TDataBaseOptions.SetADOConnection(const ADOConn:TADOConnection);
begin
FADOConnection := ADOConn;
//ShowMessage('setADOConection');
end;


procedure TDataBaseOptions.SetIniOptions(const IniOps: TIniOptions);
begin
FIniOptions := IniOps;
//ShowMessage('SetIniOptions');
if FIniOptions.AutoLogon then
begin
ConnADO2DataBase;
end;
end;


//------------------------------------------------------------//


constructor TDataBaseOptions.Create(AOwner:TComponent);
begin
inherited Create(AOwner);
FADOConnection := TADOConnection.create(nil); //-- 初始化一个ADO对象
FIniOptions := TIniOptions.Create(nil);
ShowMessage('111create');
end;


destructor TDataBaseOptions.Destroy;
begin
ClearADOConn;
FADOConnection.Free;
FIniOptions.Free;
inherited Destroy;
end;


procedure Register;
begin
RegisterComponents('ADO', [TDataBaseOptions]);
end;


Function TDataBaseOptions.IsExistsMSSQL: Boolean;
//-------------------------------------------------------//
//------ IsExistsMSSQL 函数说明 ------//
//------ 函数作用:检测是否安装SQL Server ------//
//------ 返回值:true:已安装 ; false :未安装 ------//
//-------------------------------------------------------//
var
TmpReg: Tregistry;
begin
TmpReg := Tregistry.Create;
try
try
TmpReg.RootKey := HKEY_LOCAL_MACHINE;
if not TmpReg.OpenKey('Software/Microsoft/MSSQLServer/Setup', False) then
abort;
Result := (TmpReg.ReadString('SQLPath') <> '');
TmpReg.CloseKey;
except
Result := False;
end;
finally
TmpReg.Free;
end;
end;


{TODO:检测SQLService服务的状态。}
Function TDataBaseOptions.GetSqlServerStatus:Integer;
//-------------------------------------------------------//
//------ GetSqlServerStatus 函数说明 ------//
//------ 函数作用:检测当前SQL Server服务器状态 ------//
//------ 返回值: 0:服务器已关闭 ------//
//------ 1:服务器正在运行 2:服务器已暂停 ------//
//------ 3:服务器正在启动(关闭-->启动) ------//
//------ 4:服务器正在关闭.(启动-->关闭) ------//
//------ 5:服务器正在继续 (暂停-->启动) ------//
//------ 6:服务器正在暂停 (启动-->暂停) ------//
//-------------------------------------------------------//
var
ServiceStatus:Integer;
SrvHandle: SC_HANDLE; //这些都在winsvc单元中定义的
Service_Status: _SERVICE_STATUS; //在winsvc单元中有定义
SrvStatus: Integer;
begin
ServiceStatus := -1 ;
//取sql server的状态,如果sql server正在运行则返回true ,否则返回 false
SrvHandle := OpenSCManager('', SERVICES_ACTIVE_DATABASE,SC_MANAGER_ALL_ACCESS);
SrvHandle := OpenService(SrvHandle, PChar('MSSQLServer'), SERVICE_QUERY_STATUS or SERVICE_START);
if QueryServiceStatus(SrvHandle,Service_Status) then
begin
//判断Sql Server服务的状态
SrvStatus := Service_Status.dwCurrentState;
case SrvStatus of
SERVICE_STOPPED: ServiceStatus := 0; //服务器已关闭
SERVICE_RUNNING: ServiceStatus := 1; //服务器正在运行
SERVICE_PAUSED: ServiceStatus := 2; // 服务器已暂停
SERVICE_START_PENDING: ServiceStatus := 3; //服务器正在启动(关闭-->启动)
SERVICE_STOP_PENDING: ServiceStatus := 4; //服务器正在关闭.(启动-->关闭)
SERVICE_CONTINUE_PENDING: ServiceStatus := 5; //服务器正在继续 (暂停-->启动)
SERVICE_PAUSE_PENDING: ServiceStatus := 6; //服务器正在暂停 (启动-->暂停)
end;
end ;
result := ServiceStatus ;
end;


function TDataBaseOptions.StartMSSQL:Boolean;
//-------------------------------------------------------//
//------ StartMSSQL 函数说明 ------//
//------ 函数作用:启动 SQL Server 服务器 ------//
//------ 返回值:true:启动成功 ;false :启动失败 ------//
//-------------------------------------------------------//
const
MSSQL_98StartCommand = 'scm -action 1 -pwd "%s "';
MSSQL_NTStartCommand = 'net start mssqlserver';
var
cmdCom:string;
Pass:string;
begin
Pass := '';
if not ((Win32MajorVersion >= 4) and (Win32Platform = VER_PLATFORM_WIN32_NT)) then
cmdCom := Format(MSSQL_98StartCommand,[Pass])
else
cmdCom := MSSQL_NTStartCommand;
try
WinExec(PChar(cmdCom),SW_HIDE);
Result := True;
except
Result := False;
end;


end;


function TDataBaseOptions.StopMSSQL:Boolean;
//-------------------------------------------------------//
//------ StopMSSQL 函数说明 ------//
//------ 函数作用:关闭 SQL Server 服务器 ------//
//------ 返回值:true:关闭成功 ;false :关闭失败 ------//
//------ 如果当前服务器未启动,返回true ------//
//-------------------------------------------------------//
const
MSSQL_98StopCommand = 'scm -action 6';
MSSQL_NTStopCommand = 'net stop mssqlserver';
begin
try
if not ((Win32MajorVersion >= 4) and (Win32Platform = VER_PLATFORM_WIN32_NT)) then
WinExec(MSSQL_98StopCommand,SW_HIDE)
else
WinExec(MSSQL_NTStopCommand,SW_HIDE);
Result := True;
except
Result := False;
end;
end;


Function TDataBaseOptions.ConnADO2DBServer:Boolean;
//-------------------------------------------------------//
//------ ConnADO2DBServer 函数说明 ------//
//------ 函数作用:连接 SQL Server 服务器 ------//
//------ 返回值:true:连接成功 ;false :连接失败 ------//
//------ 如果当前服务器未启动,返回false ------//
//-------------------------------------------------------//
begin
if GetSqlServerStatus=1 then
begin
with FADOConnection do
begin
Connected:=false;
ShowMessage('22');
if (FIniOptions.AuthenticateBySQLServer) then
ConnectionString:=Format('Provider=SQLOLEDB.1;Password=%s;Persist Security Info=True;User ID=%s;Data Source=%s',[FIniOptions.Password,FIniOptions.UserID,FIniOptions.ServerName])
else
ConnectionString:=Format('Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Data Source=%s',[FIniOptions.ServerName]);
try
Connected:=true;
result := True;
except
ConnectionString := '';
Connected:=false;
result := false;
end;
end;
end
else
Result := false;
end;


Function TDataBaseOptions.ConnADO2DataBase:Boolean;
//-------------------------------------------------------//
//------ ConnADO2DataBase 函数说明 ------//
//------ 函数作用:连接SQL Server服务器上的数据库 ------//
//------ 根据属性FDataBaseName的值去连接 ------//
//------ 返回值:true:连接成功 ;false :连接失败 ------//
//------ 如果当前服务器未启动,返回false ------//
//-------------------------------------------------------//
begin
if GetSqlServerStatus=1 then
begin
if ConnADO2DBServer then
begin
with FADOConnection do
begin
Connected:=false;
if( FIniOptions.AuthenticateBySQLServer )then
ConnectionString:=Format('Provider=SQLOLEDB.1;Password=%s;Persist Security Info=True;User ID=%s;Initial Catalog=%s;Data Source=%s',[FIniOptions.Password,FIniOptions.UserID,FIniOptions.DataBaseName,FIniOptions.ServerName])
else
ConnectionString:=Format('Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=%s;Data Source=%s',[FIniOptions.DataBaseName,FIniOptions.ServerName]);
try
Connected:=true;
result := True;
except
ConnectionString := '';
Connected:=false;
result := false;
end;
end;
end
else Result := False;
end
else Result := false;
end;


Function TDataBaseOptions.ConnADO2DataBase(DBName:string):Boolean;
//-------------------------------------------------------//
//------ ConnADO2DataBase 函数说明 ------//
//------ 函数作用:连接SQL Server服务器上的数据库 ------//
//------ 根据传入的参数:DBName 去连接 ------//
//------ 返回值:true:连接成功 ;false :连接失败 ------//
//------ 如果当前服务器未启动,返回false ------//
//-------------------------------------------------------//
begin
if GetSqlServerStatus=1 then
begin
if ConnADO2DBServer then
begin
with FADOConnection do
begin
Connected:=false;
if(FIniOptions.AuthenticateBySQLServer)then
ConnectionString:=Format('Provider=SQLOLEDB.1;Password=%s;Persist Security Info=True;User ID=%s;Initial Catalog=%s;Data Source=%s',[FIniOptions.Password,FIniOptions.UserID,DBName,FIniOptions.ServerName])
else
ConnectionString:=Format('Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=%s;Data Source=%s',[DBName,FIniOptions.ServerName]);
try
Connected:=true;
result := True;
except
ConnectionString := '';
Connected:=false;
result := false;
end;
end;
end
else Result := False;
end
else Result := false;
end;


function TDataBaseOptions.ClearADOConn:Boolean;
//-------------------------------------------------------//
//------ ClearADOConn 函数说明 ------//
//------ 函数作用:断开并清除FADOConnection的连接 ------//
//------ 返回值:true:清除成功 ;false :清除失败 ------//
//------ 如果当前ADO正处于连接状态,则先关闭 ------//
//-------------------------------------------------------//
begin
with FADOConnection do
begin
if connected then
begin
try
connected := False;
ConnectionString := '';
result := true;
except
result := False;
end;
end
else
begin
ConnectionString := '';
result := True;
end;
end;
end;


Function TDataBaseOptions.GetLANSQLServerList:TStrings;
//-------------------------------------------------------//
//------ GetLANSQLServerList 函数说明 ------//
//------ 函数作用:获取局域网SQL Server服务器列表 ------//
//------ 返回值:SQL Server服务器名称列表 ------//
//------ (有时无法获取本机的服务器名称) ------//
//-------------------------------------------------------//
var
SQLServerObject,ServerList:Variant;
i,nServers:integer;
SQLServersName:TStrings;
begin
SQLServersName := tstringlist.Create;
try
SQLServerObject := CreateOleObject('SQLDMO.Application');
ServerList := SQLServerObject.ListAvailableSQLServers;
nServers:=ServerList.Count;
for i := 1 to nservers do
begin
SQLServersName.Add(ServerList.Item(i));
end;
finally
SQLServerObject:=Unassigned;
serverList:=Unassigned;
end;
Result := SQLServersName;
end;


function TDataBaseOptions.GetDataBaseNameList:TStrings;
//-------------------------------------------------------//
//------ GetDataBaseNameList 函数说明 ------//
//------ 函数作用:获取服务器上的数据库列表 ------//
//------ 返回值:数据库名称列表 ------//
//------ 先连接服务器,再获取数据库列表 ------//
//-------------------------------------------------------//
var
adoQ_temp:TADOQuery ;
DataBaseNameList:TStrings;
begin
DataBaseNameList := TStringlist.Create;
if ConnADO2DBServer then
begin
adoQ_temp:= TADOQuery.Create(nil);
adoQ_temp.Connection := FADOConnection ;
with adoQ_temp do
begin
Close;
SQL.Clear;
SQL.Add(' select name from master..sysdatabases');
try
Open;
while not Eof do
begin
DataBaseNameList.Add(Fields[0].AsString);
Next;
end;
finally
free;
result := DataBaseNameList;
end;
end;
end
else Result := DataBaseNameList;
end;


function TDataBaseOptions.AttachDataBase(DBName,DataBaseFilePath,LogFilePath:String):Boolean;
//-------------------------------------------------------//
//------ AttachDataBase 函数说明 ------//
//------ 函数作用:附加指定路径,指定名称的数据库 ------//
//------ 返回值:是否附加成功 ------//
//------ 先连接服务器,再附加数据库 ------//
//-------------------------------------------------------//
var
adoQ_temp:TADOQuery ;
begin
adoQ_temp:= TADOQuery.Create(nil);
adoQ_temp.Connection := FADOConnection ;
if ConnADO2DBServer then
begin
with adoQ_temp do
begin
Close;
SQL.Clear;
SQL.Add(format('EXEC sp_attach_db ''%s'' , ''%s'' , ''%s''',[DBName,DataBaseFilePath,LogFilePath]));
try
execsql;
result := true;
except
result := false;
end;
Free;
end;
end
else result := False;
end;


function TDataBaseOptions.DetachDataBase(DBName:string):Boolean;
//-------------------------------------------------------//
//------ DetachDataBase 函数说明 ------//
//------ 函数作用:分离指定名称的数据库 ------//
//------ 返回值:是否分离成功 ------//
//------ 先连接服务器,然后清楚数据库存在的 ------//
//------ 连接,再附加数据库 ------//
//-------------------------------------------------------//
var
adoQ_temp:TADOQuery ;
begin
adoQ_temp:= TADOQuery.Create(nil);
adoQ_temp.Connection := FADOConnection ;
if ConnADO2DBServer then
begin
if DelDBConnCount(DBName) then
begin
with adoQ_temp do
begin
Close;
SQL.Clear;
SQL.Add(format('exec sp_detach_db ''%s''',[DBName]));
try
execsql;
result := true;
except
result := false;
end;
Free;
end;
end
else result := False;
end
else result := False;
end;


function TDataBaseOptions.IsExistsDataBase(DBName:string):Boolean;
//-------------------------------------------------------//
//------ IsExistsDataBase 函数说明 ------//
//------ 函数作用:判断指定名称的数据库是否存在 ------//
//------ 返回值:存在为true,不存在为false ------//
//------ 先连接服务器,再判断数据库是否存在 ------//
//-------------------------------------------------------//
var
adoQ_temp:TADOQuery ;
begin
adoQ_temp:= TADOQuery.Create(nil);
adoQ_temp.Connection := FADOConnection ;
if ConnADO2DBServer then
begin
with adoQ_temp do
begin
Close;
SQL.Clear;
sql.add('use master;');
sql.add(format('select count(name) as DB_Count from sysdatabases where name=''%s''',[DBName]));
try
open;
if (FieldByName('DB_Count').Value) then
result := True
else
result := False;
except
result := false;
end;
Free;
end;
end
else result := False;
end;


function TDataBaseOptions.IsExistsProcedure(DBName,procName:string):Boolean;
//---------------------------------------------------------------------//
//------ IsExistsProcedure 函数说明 ------//
//------ 函数作用:判断指定数据库上是否存在指定的存储过程 ------//
//------ 返回值:存在为true,不存在为false ------//
//------ 先连接服务器,再判断存储过程是否存在 ------//
//---------------------------------------------------------------------//
var
adoQ_temp:TADOQuery ;
begin
adoQ_temp:= TADOQuery.Create(nil);
adoQ_temp.Connection := FADOConnection ;
if ConnADO2DBServer then
begin
with adoQ_temp do
begin
Close;
SQL.Clear;
sql.add(format('use %s;',[DBName]));
sql.add('select * from sysobjects');
sql.add(format(' where id = object_id(N''[%s]'') ',[procName]));
sql.add('and OBJECTPROPERTY(id, N''IsProcedure'') = 1 ');
try
open;
if not EOF then
result := True
else
result := False;
except
result := false;
end;
Free;
end;
end
else result := False;
end;


function TDataBaseOptions.DeleteProcedure(DBName,procName:string):Boolean;
//---------------------------------------------------------------------//
//------ DeleteProcedure 函数说明 ------//
//------ 函数作用:删除指定数据库上指定的存储过程 ------//
//------ 返回值:删除成功为true,不成功为false ------//
//------ 先连接服务器,再判断存储过程是否存在,存在再删除 ------//
//---------------------------------------------------------------------//
var
adoQ_temp:TADOQuery ;
begin
adoQ_temp:= TADOQuery.Create(nil);
adoQ_temp.Connection := FADOConnection ;
if ConnADO2DBServer then
begin
if IsExistsProcedure(DBName,procName) then
begin
with adoQ_temp do
begin
Close;
SQL.Clear;
sql.add(format('use %s;',[DBName]));
sql.add(format('drop procedure %s',[procName]));
try
execsql;
result := true;
except
result := false;
end;
Free;
end;
end
else result := false;
end
else result := False;
end;


function TDataBaseOptions.CreateSpKillProcOnMaster:Boolean;
var
adoQ_temp:TADOQuery ;
begin
adoQ_temp:= TADOQuery.Create(nil);
adoQ_temp.Connection := FADOConnection ;
if ConnADO2DBServer then
begin
with adoQ_temp do
begin
Close;
SQL.Clear;
sql.add('use master;');
try
ExecSQL;
except
result := false;
end;
SQL.Clear;
sql.add('create procedure sp_kill_processes(@dbname varchar(100))');
sql.add('as begin ');
sql.add(' declare @pro_kill nvarchar(255) ');
sql.add(' declare tasklist_cursor cursor for select ''kill '' + ');
sql.add(' convert(varchar(5),spid) + '' -- '' + p.loginame ');
sql.add(' from sysprocesses p,sysdatabases d ');
sql.add(' where p.dbid = d.dbid and d.name = @dbname ');
sql.add(' open tasklist_cursor ');
sql.add(' fetch next from tasklist_cursor into @pro_kill ');
sql.add(' while(@@fetch_status = 0) ');
sql.add(' begin ');
sql.add(' exec(@pro_kill) ');
sql.add(' if(@@error <> 0) return 0 ');
sql.add(' fetch next from tasklist_cursor into @pro_kill ');
sql.add(' end ');
sql.add(' close tasklist_cursor ');
sql.add(' deallocate tasklist_cursor ');
sql.add(' return 1 ');
sql.add('end ');
try
execsql;
result := true;
except
result := false;
end;
free;
end;
end
else result := False;
end;


function TDataBaseOptions.GetDBConnCount(DBName:string):Integer;
var
adoQ_temp:TADOQuery ;
begin
adoQ_temp:= TADOQuery.Create(nil);
adoQ_temp.Connection := FADOConnection ;
if ConnADO2DBServer then
begin
with adoQ_temp do
begin
Close;
SQL.Clear;
sql.add('use master;');
sql.Add('select count(*) as connCount');
sql.Add('from [master].[dbo].[sysprocesses]');
sql.Add('where dbid in ( select dbid from [master].[dbo].[sysdatabases]');
sql.Add(format('where name=''%s'')',[DBName]));
try
open;
result := FieldByName('connCount').Value ;
except
result := -1;
end;
Free;
end;
end
else result := -1;
end;


function TDataBaseOptions.DelDBConnCount(DBName:string):Boolean;
var
adoQ_temp:TADOQuery ;
begin
adoQ_temp:= TADOQuery.Create(nil);
adoQ_temp.Connection := FADOConnection ;
if ConnADO2DBServer then
begin
if not IsExistsProcedure('master','sp_kill_processes') then
begin
if not CreateSpKillProcOnMaster then
result := False;
end;
with adoQ_temp do
begin
Close;
SQL.Clear;
sql.add('use master;');
sql.Add(format('exec [master].[dbo].[sp_kill_processes] %s',[DBName]));
try
execsql;
result := True ;
except
result := False;
end;
Free;
end;
end
else result := False;
end;


function TDataBaseOptions.BackUpDataBase(DBName,backUpFilePath:String):Boolean;
var
adoQ_temp:TADOQuery ;
begin
adoQ_temp:= TADOQuery.Create(nil);
adoQ_temp.Connection := FADOConnection ;
if ConnADO2DBServer then
begin
with adoQ_temp do
begin
Close;
SQL.Clear;
sql.Add(format('backup database %s to disk=''%s''',[DBName,backUpFilePath]));
try
execsql;
result := True ;
except
result := False;
end;
Free;
end;
end
else result := False;
end;


function TDataBaseOptions.RestoreDataBase(DBName,restoreFilePath:String):Boolean;
var
adoQ_temp:TADOQuery ;
begin
adoQ_temp:= TADOQuery.Create(nil);
adoQ_temp.Connection := FADOConnection ;
if DelDBConnCount(DBName) then
begin
with adoQ_temp do
begin
Close;
SQL.Clear;
SQL.ADD('use master;');
SQL.ADD(format('restore database %s from disk=''%s''',[DBName,restoreFilePath]));
try
execsql;
result := True ;
except
result := False;
end;
Free;
end;
end
else result := false;
end;



end.


最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台