Отчет по практике в УжНУ

END

CREATE OR ALTER TRIGGER MESTO_PARKING_BI0 FOR MESTO_PARKING

ACTIVE BEFORE INSERT POSITION 0

AS

BEGIN

IF (NEW.RN_MESTO IS NULL) THEN

NEW.RN_MESTO = GEN_ID(gen_mesto_parking_id,1);

END

CREATE OR ALTER TRIGGER TRANSPORT_BI0 FOR TRANSPORT

ACTIVE BEFORE INSERT POSITION 0

AS

BEGIN

IF (NEW.RN_TRANSPORT IS NULL) THEN

NEW.RN_TRANSPORT = GEN_ID(gen_transport_id,1);

END

Представления

CREATE VIEW VCLIENT(

RN_CLIENTS,

SURNAME,

NAME,

SECOND_NAME)

AS

select * from clients;

CREATE VIEW VCLIENTS(

RN_CLIENTS,

SURNAME,

NAME)

AS

select clients.rn_clients, clients.surname, clients.name from clients

where clients.surname like ‘В%’;

CREATE VIEW VMAGAZINE(

RN_EVENTS,

DATE_SOB,

TIME_V_EZD,

TIME_VYEZD,

EMPLOYERS,

TRANSPORT,

CLASS)

AS

select  magazine_events.rn_events,magazine_events.date_sob, magazine_events.time_v_ezd, magazine_events.time_vyezd, employers.surname, transport.mark, mesto_parking.class  from magazine_events, employers, transport, mesto_parking

where magazine_events.rn_employers = employers.rn_employers and

magazine_events.rn_transport = transport.rn_transport and

magazine_events.rn_mesto = mesto_parking.rn_mesto;

CREATE VIEW VTIME(

RN_EVENTS,

DATE_SOB,

TRANSPORT)

AS

select  magazine_events.rn_events,magazine_events.date_sob, transport.mark  from magazine_events, transport

where (magazine_events.rn_transport = transport.rn_transport) and (magazine_events.date_sob < ‘19.11.2022’);

CREATE VIEW VTRANSPORT(

RN_TRANSPORT,

MARC,

COLOUR,

STATE_NUM,

CLIENTS)

AS

select  transport.rn_transport, transport.mark, transport.colour, transport.state_num, clients.surname from transport, clients

where transport.rn_clients = clients.rn_clients;

Процедуры

CREATE OR ALTER PROCEDURE DEL_CLIENT (

rn smallint)

as

begin

begin

delete from clients where rn_clients  = :rn;

when sqlcode -530 do exception e_key;

end

suspend;

end

CREATE OR ALTER PROCEDURE INS_CLIENTS (

surname varchar(25),

name varchar(25),

second_name varchar(25))

as

begin

begin

insert into clients (surname,name,second_name) values (:surname,:name,:second_name);

when sqlcode -530 do exception e_key;

end

suspend;

end

CREATE OR ALTER PROCEDURE UPD_CLIENTS (

rn smallint,

surname varchar(25),

name varchar(25),

second_name varchar(25))

as

begin

update  clients

set surname=:surname, name=:name, second_name=:second_name

where rn_clients = :rn;

suspend;

end

Приложение 2. Сценарий работы программы

Листинг клиентского  приложения

Form1.s

using System;

using System.Data.OleDb;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Windows.Forms;

namespace Транспорт

{

public partial class Form1 : Form

{

public Form1()

{

InitializeComponent();

}

private void Form1_Load(object sender, EventArgs e)

{

// TODO: This line of code loads data into the ‘dataSet1.VMAGAZINE’ table. You can move, or remove it, as needed.

this.vMAGAZINETableAdapter.Fill(this.dataSet1.VMAGAZINE);

// TODO: This line of code loads data into the ‘dataSet1.MAGAZINE_EVENTS’ table. You can move, or remove it, as needed.

this.eMPLOYERSTableAdapter.Fill(this.dataSet1.EMPLOYERS);

// TODO: This line of code loads data into the ‘dataSet1.MESTO_PARKING’ table. You can move, or remove it, as needed.

this.mESTO_PARKINGTableAdapter.Fill(this.dataSet1.MESTO_PARKING);

// TODO: This line of code loads data into the ‘dataSet1.TRANSPORT’ table. You can move, or remove it, as needed.

this.tRANSPORTTableAdapter.Fill(this.dataSet1.TRANSPORT);

// TODO: This line of code loads data into the ‘dataSet1.CLIENTS’ table. You can move, or remove it, as needed.

this.cLIENTSTableAdapter.Fill(this.dataSet1.CLIENTS);

}

private void выходToolStripMenuItem_Click(object sender, EventArgs e)

{

Close();

}

private void button6_Click(object sender, EventArgs e)

{

//Очистка всех полей

textBox1.Text = «»;

textBox2.Text = «»;

textBox3.Text = «»;

textBox4.Text = «»;

textBox5.Text = «»;

textBox6.Text = «»;

textBox7.Text = «»;

textBox8.Text = «»;

textBox9.Text = «»;

textBox10.Text = «»;

}

private void button7_Click(object sender, EventArgs e)

{

//Показать все записи таблицы

cLIENTSBindingSource.Filter = «»;

}

private void button4_Click(object sender, EventArgs e)

{

//Поиск записи

cLIENTSBindingSource.Filter = «SURNAME like ‘» + textBox2.Text + «%'»;

}

private void button5_Click(object sender, EventArgs e)

{

//Фильтрация записи

if (radioButton1.Checked == true)

cLIENTSBindingSource.Filter = «SURNAME like ‘%» + textBox3.Text + «%'»;

if (radioButton2.Checked == true)

cLIENTSBindingSource.Filter = «NAME like ‘%» + textBox3.Text + «%'»;

}

private void button3_Click(object sender, EventArgs e)

{

//Добавление записи

cLIENTSBindingSource.Filter = «»;

OleDbConnection con = new OleDbConnection

(«Provider=LCPI.IBProvider.3; Data Source=localhost:T:\\home\\localhost\\G140705_SAFONOVA.FDB; Persist Security Info=True;Password=masterkey;User ID=sysdba; ctype=WIN1251;auto_commit=True»);

con.Open();

OleDbTransaction trans = con.BeginTransaction();

OleDbCommand com = new OleDbCommand(«EXECUTE PROCEDURE INS_CLIENTS (:SURNAME, :NAME, :SECOND_NAME)», con, trans);

com.Parameters.Add(«SURNAME», OleDbType.Char);

com.Parameters[«SURNAME»].Value = textBox4.Text;

com.Parameters.Add(«NAME», OleDbType.Char);

com.Parameters[«NAME»].Value = textBox5.Text;

com.Parameters.Add(«SECOND_NAME», OleDbType.Char);

com.Parameters[«SECOND_NAME»].Value = textBox6.Text;

trans.Commit();

com.ExecuteScalar();

this.cLIENTSTableAdapter.Fill(this.dataSet1.CLIENTS);

con.Close();

textBox4.Text = «»;

textBox5.Text = «»;

textBox6.Text = «»;

}

private void button1_Click(object sender, EventArgs e)

{

//Удаление записи

cLIENTSBindingSource.Filter = «»;

OleDbConnection con = new OleDbConnection

(«Provider=LCPI.IBProvider.3; Data Source=localhost:T:\\home\\localhost\\G140705_SAFONOVA.FDB; Persist Security Info=True;Password=masterkey;User ID=sysdba; ctype=WIN1251;auto_commit=True»);

con.Open();

OleDbTransaction trans = con.BeginTransaction();

Приложение 3. Листинг программы

MainWindow.xaml

<Window x:Class=»XamlApp.MainWindow»

xmlns=»http://schemas.microsoft.com/winfx/2006/xaml/presentation»

xmlns:x=»http://schemas.microsoft.com/winfx/2006/xaml»

xmlns:d=»http://schemas.microsoft.com/expression/blend/2008″

xmlns:mc=»http://schemas.openxmlformats.org/markup-compatibility/2006″

xmlns:local=»clr-namespace:XamlApp»

mc:Ignorable=»d»

Title=»MainWindow» Height=»350″ Width=»525″>

<Grid x:Name=»grid1″>

<Button x:Name=»button1″  Width=»100″ Height=»30″ Content=»Кнопка» />

</Grid>

</Window>

<Window x:Class=»XamlApp.MainWindow»

xmlns=»http://schemas.microsoft.com/winfx/2006/xaml/presentation»

xmlns:x=»http://schemas.microsoft.com/winfx/2006/xaml»

xmlns:d=»http://schemas.microsoft.com/expression/blend/2008″

xmlns:mc=»http://schemas.openxmlformats.org/markup-compatibility/2006″

xmlns:local=»clr-namespace:XamlApp»

mc:Ignorable=»d»

Title=»MainWindow» Height=»350″ Width=»525″>

<Grid x:Name=»grid1″>

<Button x:Name=»button1″  Width=»100″ Height=»30″ Content=»Кнопка» />

</Grid>

</Window>

<Window x:Class=»XamlApp.MainWindow»

xmlns=»http://schemas.microsoft.com/winfx/2006/xaml/presentation»

xmlns:x=»http://schemas.microsoft.com/winfx/2006/xaml»

xmlns:d=»http://schemas.microsoft.com/expression/blend/2008″

xmlns:mc=»http://schemas.openxmlformats.org/markup-compatibility/2006″

xmlns:local=»clr-namespace:XamlApp»

mc:Ignorable=»d»

Title=»MainWindow» Height=»350″ Width=»525″>

<Grid x:Name=»grid1″>

<Button x:Name=»button1″  Width=»100″ Height=»30″ Content=»Кнопка» />

</Grid>

</Window>

<Window x:Class=»XamlApp.MainWindow»

xmlns=»http://schemas.microsoft.com/winfx/2006/xaml/presentation»

xmlns:x=»http://schemas.microsoft.com/winfx/2006/xaml»

xmlns:d=»http://schemas.microsoft.com/expression/blend/2008″

xmlns:mc=»http://schemas.openxmlformats.org/markup-compatibility/2006″

xmlns:local=»clr-namespace:XamlApp»

mc:Ignorable=»d»

Title=»MainWindow» Height=»350″ Width=»525″>

<Grid x:Name=»grid1″>

<Button x:Name=»button1″  Width=»100″ Height=»30″ Content=»Кнопка» />

</Grid>

</Window>