How To Run A Database Script File From Delphi?

I want to do the following. 1) Create a database. 2) Run a script when creates tables, stored procedures, etc. (this script is created by SMS 'generate scripts' option) I found

Solution 1:

Rob the GO statement is not recognized by ADO, so you must remove from your script before execute.

Now to check if a database exist you can execute a query like this

selectCOUNT(*) from sys.databases where name='yourdatabasename'

check this very basic sample

assume which you have a script like this

CREATETABLE Dummy.[dbo].tblUsers(ID INT, UserName VARCHAR(50))
GO
INSERTINTO Dummy.[dbo].tblUsers (ID, UserName) VALUES (1, 'Jill')
GO
INSERTINTO Dummy.[dbo].tblUsers (ID, UserName) VALUES (2, 'John')
GO
INSERTINTO Dummy.[dbo].tblUsers (ID, UserName) VALUES (3, 'Jack')
GO

Now to execute this sentence you can do something like this

const//in this case the script is inside of a const string but can be loaded from a file as well
Script=
'CREATE TABLE Dummy.[dbo].tblUsers(ID INT, UserName VARCHAR(50)) '+#13#10+'GO '+#13#10+'INSERT INTO Dummy.[dbo].tblUsers (ID, UserName) VALUES (1, ''Jill'') '+#13#10+'GO '+#13#10+'INSERT INTO Dummy.[dbo].tblUsers (ID, UserName) VALUES (2, ''John'') '+#13#10+'GO '+#13#10+'INSERT INTO Dummy.[dbo].tblUsers (ID, UserName) VALUES (3, ''Jack'') '+#13#10+'GO ';

var
  DatabaseExist : Boolean;
  i             : Integer;
begin
  try//check the connectionif not ADOConnection1.Connected then
      ADOConnection1.Connected:=True;
      //make the query to check if the database called Dummy exist  
      ADOQuery1.SQL.Add(Format('select COUNT(*) from sys.databases where name=%s',[QuotedStr('Dummy')]));
      ADOQuery1.Open;
      try//get the returned value, if is greater than 0 then exist 
       DatabaseExist:=ADOQuery1.Fields[0].AsInteger>0;
      finally
       ADOQuery1.Close;
      end;


      if not DatabaseExist then
      begin
       //create the database if not exist
       ADOQuery1.SQL.Text:=Format('Create Database %s',['Dummy']);
       ADOQuery1.ExecSQL;
       ADOQuery1.Close;

       //load the script, remember can be load from a file too  
       ADOQuery1.SQL.Text:=Script;
       //parse the script to remove the GO statementsfor i := ADOQuery1.SQL.Count-1 downto 0doif StartsText('GO',ADOQuery1.SQL[i]) then
           ADOQuery1.SQL.Delete(i);
       //execute the script
       ADOQuery1.ExecSQL;
       ADOQuery1.Close;
      end;
  except
      on E:Exceptiondo
        ShowMessage(E.Message);
  end;

end;