2.6 Разработка простой бд и по для работы с ней
В ходе практики была разработана хранимая процедура, генерирующая скрипт, позволяющий осуществить репликацию БД, и редактор шаблонов отчётных форм.
2.6.1 Хранимая процедура
Данная хранимая процедура принимает имя таблицы и возвращает скрипт, который будучи выполнен создаст копию вышеупомянутой таблицы.
Исходный код процедуры:
create PROCEDURE copy_table
@t_name varchar(max)
AS
BEGIN
declare @insert varchar(max)
declare @pol varchar(max)
declare @vl varchar(max)
declare @end varchar(max)
declare @b table(zn varchar(max))
declare @pol2 varchar(max)
declare @sel varchar(max)
declare @p1 varchar(max) = 'case when not(ISNUMERIC('
declare @p2 varchar(max) = '))=1 then '''''''' else '''' end + isnull(CONVERT(varchar(max), '
declare @p3 varchar(max) = '), ''NULL'') + case when not(ISNUMERIC('
declare @p4 varchar(max) = '))=1 then '''''''' else '''' end '
declare @from varchar(max)
declare @res varchar(max)
set @sel = 'select '
set @from = 'from ' + @t_name
select @pol = Isnull(@pol + ', ', '') + sys.columns.name
from sys.tables, sys.columns
where sys.tables.object_id = sys.columns.object_id
and sys.tables.name = @t_name
set @insert = 'INSERT into ' + @t_name + '('
set @vl = ') values('
set @end = ') '
select @pol2 = Isnull(@pol2 + '+'', ''+ ', '') + @p1 + sys.columns.name + @p2 + sys.columns.name + @p3 + sys.columns.name + @p4
from sys.tables, sys.columns
where sys.tables.object_id = sys.columns.object_id
and sys.tables.name = @t_name
set @pol2 = @sel + @pol2 + @from
insert into @b exec(@pol2)
select @res = ISNULL(@res, '') + @insert + @pol + @vl + zn + @end + '; '
from @b
if exists(select 1 from sys.tables, sys.columns where sys.tables.object_id = sys.columns.object_id and sys.tables.name = @t_name and sys.columns.is_identity = 1)
begin
set @res = ' set identity_insert ' + @t_name + ' on; ' + @res + ' set identity_insert ' +@t_name+ ' off; '
end
select @res
END
GO
Для примера создадим таблицу t1:
|
id(int, Не NULL) |
name(varchar(max), NULL) |
s2(int, NULL) |
s3(float, NULL) |
|
1 |
строка1 |
22 |
0,714001918580604 |
|
2 |
строка2 |
34 |
0,717504917177145 |
|
3 |
строка3 |
4 |
0,713778322925506 |
Выполним:
copy_table t1
Получим:
INSERT into t1(id, name, s2, s3)
values(1, 'строка1', 22, 0.714002)
INSERT into t1(id, name, s2, s3)
values(2, 'строка2', 34, 0.717505)
INSERT into t1(id, name, s2, s3)
values(3, 'строка3', 4, 0.713778)
2.6.2 Редактор шаблонов отчётных форм
Редактор шаблонов представляет собой программу, которая управляет документом MicrosoftWordпри помощиOLE. Программа получает из базы данных определённую информацию и даёт пользователю возможность вставить её в документ всего одним кликом. Данные в программу передаются двумя функциями, возвращающими табличное значение. Эти функции хранятся на сервере и могут быть изменены без внесения корректировок в исходный код программы.
Исходный код программы
Файл Unit1.cpp
//---------------------------------------------------------------------------
#include <vcl.h>
#pragma hdrstop
#include "Unit1.h"
//---------------------------------------------------------------------------
#pragma package(smart_init)
#pragma link "DBAccess"
#pragma link "MemDS"
#pragma link "MSAccess"
#pragma link "SdacVcl"
#pragma link "DBAccess"
#pragma resource "*.dfm"
TForm1 *Form1;
//---------------------------------------------------------------------------
__fastcall TForm1::TForm1(TComponent* Owner)
: TForm(Owner)
{
}
//---------------------------------------------------------------------------
void __fastcall TForm1::FormCreate(TObject *Sender){
if(ParamCount()==1){
if(FileExists(GetCurrentDir()+"\\"+ParamStr(1))){
if(iniConnect(GetCurrentDir()+"\\"+ParamStr(1), &V))
sql();
}
else{
if(FileExists(ParamStr(1))){
if(iniConnect(ParamStr(1), &V))
sql();
}
else
ShowMessage("Досадно, файл с именем "+ParamStr(1)+" не найден");
}
}
Form1->Show();
Form1->Left = 0;
Form1->Top = 140;
Form1->Height = Screen->Height-192;
Form1->Width = Screen->Width/5;
}
//---------------------------------------------------------------------------
void __fastcall TForm1::FormCanResize(TObject *Sender, int &NewWidth, int &NewHeight, bool &Resize){
DBGrid1->Height = (Form1->Height - 100)/2;
DBGrid2->Height = (Form1->Height - 100)/2;
DBGrid1->Width = (Form1->Width - 35);
DBGrid2->Width = (Form1->Width - 35);
DBGrid1->Top = 10;
DBGrid2->Top = DBGrid1->Top + DBGrid1->Height + 10;
DBGrid1->Left = 5;
DBGrid2->Left = 5;
DBGrid1->Columns->operator [](0)->Width = DBGrid1->Width;
DBGrid2->Columns->operator [](0)->Width = DBGrid2->Width;
}
//---------------------------------------------------------------------------
void __fastcall TForm1::N3Click(TObject *Sender){
MSConnection1->ConnectDialog = MSConnectDialog1;
if(MSConnectDialog1->Execute()){
sql();
}
}
//---------------------------------------------------------------------------
void __fastcall TForm1::N4Click(TObject *Sender){
if(OpenDialog1->Execute()){
open(OpenDialog1->FileName, &V);
Form1->Caption = "Редактор шаблонов - " + OpenDialog1->FileName;
}
}
void __fastcall TForm1::DBGrid1CellClick(TColumn *Column){
DBGrid2->DataSource->DataSet->Filter = "object_id = " + DBGrid1->DataSource->DataSet->FieldByName("object_id")->AsString;
DBGrid2->DataSource->DataSet->Filtered = true;
}
//---------------------------------------------------------------------------
void __fastcall TForm1::DBGrid1KeyUp(TObject *Sender, WORD &Key, TShiftState Shift){
DBGrid2->DataSource->DataSet->Filter = "object_id = " + DBGrid1->DataSource->DataSet->FieldByName("object_id")->AsString;
DBGrid2->DataSource->DataSet->Filtered = true;
}
//---------------------------------------------------------------------------
void __fastcall TForm1::DBGrid1KeyDown(TObject *Sender, WORD &Key, TShiftState Shift){
DBGrid2->DataSource->DataSet->Filter = "object_id = " + DBGrid1->DataSource->DataSet->FieldByName("object_id")->AsString;
DBGrid2->DataSource->DataSet->Filtered = true;
}
//---------------------------------------------------------------------------
void __fastcall TForm1::ini1Click(TObject *Sender){
if(OpenDialog2->Execute()){
if(FileExists(OpenDialog2->FileName)){
if(iniConnect(OpenDialog2->FileName, &V))
sql();
}
else
ShowMessage("Похоже, что такого файла с именем " +OpenDialog2->FileName+ " нету");
}
}
//---------------------------------------------------------------------------
void __fastcall TForm1::DBGrid2DblClick(TObject *Sender){
insert((DBGrid2->DataSource->DataSet->FieldByName("shablon")->AsString), V);
}
//---------------------------------------------------------------------------
void __fastcall TForm1::DBGrid2KeyPress(TObject *Sender, char &Key){
if(Key == 13)
insert((DBGrid2->DataSource->DataSet->FieldByName("shablon")->AsString), V);
}
//---------------------------------------------------------------------------
Файл Unit1.h
//---------------------------------------------------------------------------
#ifndef Unit1H
#define Unit1H
//---------------------------------------------------------------------------
#include <Classes.hpp>
#include <IniFiles.hpp>
#include <Controls.hpp>
#include <StdCtrls.hpp>
#include <Forms.hpp>
//#include <DBAccess.hpp>
#include "MemDS.hpp"
#include "MSAccess.hpp"
#include "SdacVcl.hpp"
#include <DB.hpp>
#include <DBGrids.hpp>
#include <Grids.hpp>
#include <OleCtnrs.hpp>
#include "DBAccess.hpp"
#include <ComCtrls.hpp>
#include <Menus.hpp>
#include <Dialogs.hpp>
//---------------------------------------------------------------------------
class TForm1 : public TForm
{
__published: // IDE-managed Components
TMSQuery *MSQuery1;
TMSDataSource *MSDataSource1;
TMSConnection *MSConnection1;
TDBGrid *DBGrid1;
TDBGrid *DBGrid2;
TMSDataSource *MSDataSource2;
TMSQuery *MSQuery2;
TMainMenu *MainMenu1;
TMenuItem *N1;
TMenuItem *N2;
TMenuItem *N3;
TMenuItem *N4;
//TMenuItem *N5;
//TMenuItem *N6;
TOpenDialog *OpenDialog1;
TSaveDialog *SaveDialog1;
TMenuItem *ini1;
TOpenDialog *OpenDialog2;
TMSConnectDialog *MSConnectDialog1;
void __fastcall FormCreate(TObject *Sender);
void __fastcall FormCanResize(TObject *Sender, int &NewWidth,
int &NewHeight, bool &Resize);
void __fastcall N3Click(TObject *Sender);
void __fastcall N4Click(TObject *Sender);
// void __fastcall N5Click(TObject *Sender);
// void __fastcall N6Click(TObject *Sender);
//void __fastcall N7Click(TObject *Sender);
void __fastcall DBGrid1CellClick(TColumn *Column);
void __fastcall DBGrid1KeyUp(TObject *Sender, WORD &Key,
TShiftState Shift);
void __fastcall DBGrid1KeyDown(TObject *Sender, WORD &Key,
TShiftState Shift);
void __fastcall ini1Click(TObject *Sender);
void __fastcall DBGrid2DblClick(TObject *Sender);
void __fastcall DBGrid2KeyPress(TObject *Sender, char &Key);
private: // User declarations
public: // User declarations
__fastcall TForm1(TComponent* Owner);
Variant V;
TIniFile *ini;
void sql(void){
MSQuery1->SQL->Clear();
MSQuery1->SQL->Text = "select object_id, name from [dbo].[fn_GetTableList]()";
MSQuery1->Open();
MSQuery2->SQL->Clear();
MSQuery2->SQL->Text = "select object_id, name, shablon from [dbo].[fn_GetColumnList]()";
MSQuery2->Open();
DBGrid1->Visible = true;
DBGrid2->Visible = true;
DBGrid2->DataSource->DataSet->Filter = "object_id = " + DBGrid1->DataSource->DataSet->FieldByName("object_id")->AsString;
DBGrid2->DataSource->DataSet->Filtered = true;
}
void open(AnsiString str, Variant *V){
*V = CreateOleObject("Word.Basic");
(*V).Exec(Procedure("FileOpen") << str);
(*V).Exec(Procedure("AppShow"));
return;
}
AnsiString pass(AnsiString str){
int x = str.Length()-1;
str = "*";
for(;x>0;x--){
str+="*";
}
return str;
}
bool iniConnect(AnsiString str, Variant *V){
bool x = true;
AnsiString err;
ini = new TIniFile(str);
MSConnection1->ConnectDialog = NULL;
if(ini->ValueExists("Database", "Database"))
MSConnection1->Database = ini->ReadString("Database","Database", NULL);
else{
x = false;
err += "Имя базы\n";
}
if(ini->ValueExists("info","userpwd"))
MSConnection1->Password = ini->ReadString("info","userpwd", NULL);
else{
x = false;
err += "Пароль\n";
}
if(ini->ValueExists("Database","LogId"))
MSConnection1->Username = ini->ReadString("Database","LogId", NULL);
else{
x=false;
err+= "Имя пользователя\n";
}
if(ini->ValueExists("Database","ServerName"))
MSConnection1->Server = ini->ReadString("Database","ServerName", NULL);
else{
x = false;
err += "Имя сервера\n";
}
if(ini->ValueExists("info","File_Name_TE"))
if(FileExists(GetCurrentDir()+"\\"+ ini->ReadString("info","File_Name_TE", NULL)))
open(GetCurrentDir()+"\\"+ ini->ReadString("info","File_Name_TE", NULL), V);
else
if(FileExists(ini->ReadString("info","File_Name_TE", NULL)))
open(ini->ReadString("info","File_Name_TE", NULL), V);
if(x){
try{
MSConnection1->Connect();
}
catch(...){
x = false;
ShowMessage("Досадно, не удалось подключиться, используя следующие параметры:\n Имя сервера: "+MSConnection1->Server+"\n Имя базы: "+MSConnection1->Database +"\n Имя пользователя: "+MSConnection1->Username+"\n Пароль: "+pass(MSConnection1->Password)+"\n");
}
}
else
ShowMessage("Подключение не выполнено, так как не удалось считать:\n"+err);
delete ini;
return x;
}
void insert(AnsiString x, Variant V){
try{
V.Exec(Procedure("Insert") << x);
}
catch(...){
ShowMessage("Что-то пошло не так. Возможно вы закрыли окноWord.\nОткройте его снова через менюФайл->Открыть");
}
return;
}
};
//---------------------------------------------------------------------------
extern PACKAGE TForm1 *Form1;
//---------------------------------------------------------------------------
#endif
В процессе работы программа обращается к некоторым серверным функциям.
fn_GetTableList – возвращает список таблиц, с которыми будет работать программа, и ихid. Ниже приведён её исходный код. Но стоит отметить, что это не окончательный её вид. В процессе использования программы, эта функция может меняться по усмотрению системного администратора. Здесь приведён её примерный вид.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_GetTableList]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_GetTableList]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_GetTableList] ()
RETURNS TABLE
AS
RETURN
(
select name, object_id
from sys.tables
where sys.tables.name = 'klidog' or sys.tables.name = 'rs_kl' or sys.tables.name = 'klient'
)
GO
fn_GetColumnList – Возвращает список полей, которые входят в таблицы, возвращаемые функциейfn_GetTableList,и имеют описание; id таблицы, к которой принадлежит поле; описание поля. Стоит упомянуть, что функция может быть изменена и приведён её примерный вид.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_GetColumnList]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_GetColumnList]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_GetColumnList] ()
RETURNS @vv TABLE ( object_id int, name varchar(100), shablon varchar(100) )--
AS
begin
insert @vv ( object_id, name, shablon)
select sys.columns.object_id, sys.columns.name, dbo.fn_getDescription((select name from sys.tables as s where s.object_id = t.object_id ) ,sys.columns.name )
from sys.columns , (select * from fn_gettablelist()) as t
where t.object_id = sys.columns.object_id
delete @vv where isnull(shablon,'') = ''
return
end
Стоит обратить внимание на то, что в функции fn_GetColumnList было обращение к функцииfn_getDescription. fn_getDescription не используется программой напрямую, но она необходима для работыfn_GetColumnList.fn_getDescription возвращает описание поля.
USE [orc]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_getDescription]
(
@table_name varchar(100),
@column_name varchar(100)
)
RETURNS varchar(100)
AS
BEGIN
declare @s varchar(100)
set @s = convert(varchar(100),isnull((SELECT value
FROM fn_listextendedproperty (NULL, 'schema', 'dbo', 'table', @table_name, 'column', @column_name)),''));
return @s
END
GO
GRANT EXECUTE ON [dbo].[fn_getDescription] TO [s_base] AS [dbo]
GO
Несколько скриншотов работы программы
Сразу после запуска пользователь увидит такое окно
Рисунок 2 – Окно программы
Далее необходимо выполнить подключение к БД. Это можно сделать, выбрав пунет меню Подключение -> Подключитьсяили нажав горячую клавишуF5
Рисунок 3 – Окно подключения к БД
После подключения мы увидим в верхней части окна таблицу значений, которые вернула функция fn_GetTableList, а в нижней -fn_GetColumnList
Рисунок 4 – Окно программы после подключения к БД
Далее необходимо выбрать документ MicrosoftWord. Можно через меню
Файл->Открытьили сочетанием клавишCtrl+O
Рисунок5 – Диалог выбора документа
Далее откроется окно MicrosoftWord. Теперь при двойном клике по нижней таблице, в документ будет вставляться описание поля
Рисунок6 – Вставка описания
Стоит отметить, что в программе есть возможность использования *.iniфайлов. Программа может считать из такого файла параметры подключения к БД и имя документаMicrosoftWord. Файл может быть выбран через диалог, который откроется при нажатии сочетания клавишCtrl+F5или при выборе пункта менюПодключение ->Подключиться. Так же имя файла может быть передано в качестве параметра при запуске программы.
Рисунок 7– Диалог выбора*.ini файла
Как можно заметить всю необходимую информацию программа получила из *.iniфайла
Рисунок 8 – Программа после открытия *.iniфайла
Содержимое файла 1.ini
[Database]
Database = "Jack"
ServerName = INFINITI
LogId = "Jack"
[info]
userpwd=//тут был пароль//
File_Name_TE = d:\проект\Hello.docx