Шпаргалка по предмету "Программирование и компьютеры"

СОЗДАНИЕ  ТАБЛИЦ

 

CREATE TABLE employee(

ID   INT   NOT NULL IDENTITY(1,1) CONSTRAINT [Employee_PK_ID] PRIMARY KEY,

FirstName NVARCHAR(50),

LastName NVARCHAR(50)

)

GO

 

CREATE TABLE work_on(

ID   INT   IDENTITY(1, 1),

ProjectID INT,

EmployeeID INT   NOT NULL CONSTRAINT [Works_on_FK_EmployeeID] REFERENCES employee(ID),

[Job]  NVARCHAR(20)

)

GO

 

CREATE TABLE employee_log(

ID   INT   IDENTITY(1,1),

[Date]  DATETIME NOT NULL CONSTRAINT [DF_Employee_log_Date] DEFAULT(GETDATE()),

Operation NVARCHAR(20),

[FirstName] NVARCHAR(50),

[LastName] NVARCHAR(50)

)

GO

 

СОЗДАНИЕ ПРОЦЕДУР

-- EXEC people_count_on_project @ProjectID = 1

CREATE PROCEDURE people_count_on_project(

@ProjectID INT

)

AS

IF EXISTS(SELECT ID FROM work_on wo WHERE wo.ProjectID = @ProjectID)

SELECT COUNT(ID) FROM work_on wo WHERE wo.ProjectID = @ProjectID

ELSE

SELECT 'Not this project in system'

GO

 

CREATE PROCEDURE all_developers

AS

SELECT e.FirstName, e.LastName, wo.[Job]

FROM employee e

INNER JOIN work_on wo ON e.ID = wo.EmployeeID

WHERE wo.[Job] LIKE '%Developer%'

GO

 

ВСТАВКА В ТАБЛИЦЫ

 

INSERT INTO employee(FirstName, LastName)

VALUES ('Chester', 'Benninghton'),

('Mike', 'Shinoda'),

('Rob', 'Bourdon'),

('Joe', 'Hahn')

GO

 

INSERT INTO work_on(ProjectID, EmployeeID, [Job])

VALUES (1, 1, 'Manager'),

(1, 2, '.NET Developer'),

(2, 1, 'Manaager'),

(2, 3, 'SQL Developer'),

(2, 4, 'QA')

GO

 

СОЗДАНИЕ ФУНКЦИЙ

-- SELECT * FROM dbo.fn_employee_activity(2)

CREATE FUNCTION fn_employee_activity(

@EmployeeID INT

)

RETURNS @Result TABLE(ID INT, ProjectID INT)

AS

BEGIN

INSERT INTO @Result (ID, ProjectID)

SELECT wo.ID, wo.ProjectID FROM work_on wo WHERE wo.EmployeeID = @EmployeeID

 

RETURN

END

GO

 

-- SELECT dbo.fn_employee_id_get('Mike', 'Shinoda')

CREATE FUNCTION fn_employee_id_get(

@FirstName NVARCHAR(20), @LastName NVARCHAR(50)

)

RETURNS INT

AS

BEGIN

RETURN(SELECT e.ID FROM employee e WHERE e.FirstName = @FirstName AND e.LastName = @LastName)

END

GO

 

СОЗДАНИЕ ВЬЮШЕК

CREATE VIEW employee_info

AS

SELECT e.ID, e.FirstName, e.LastName, wo.ProjectID, wo.[Job]

FROM employee e

INNER JOIN work_on wo ON e.ID = wo.EmployeeID

 

СОЗДАНИЕ ТРИГЕРОВ

CREATE TRIGGER employee_log_modify ON dbo.employee

AFTER INSERT, UPDATE, DELETE

AS

-- INSERT INTO employee(FirstName, LastName) VALUES ('John', 'Smith')

IF (SELECT COUNT(*) FROM INSERTED) > 0 AND (SELECT COUNT(*) FROM DELETED) = 0

INSERT INTO employee_log (Operation, FirstName, LastName)

SELECT 'Insert', i.FirstName, i.LastName FROM INSERTED as i

 

--UPDATE employee SET FirstName = 'Jack' WHERE ID = 5

IF (SELECT COUNT(*) FROM INSERTED) > 0 AND (SELECT COUNT(*) FROM DELETED) > 0

BEGIN

INSERT INTO employee_log (Operation, FirstName, LastName)

SELECT 'Update: deleted row', d.FirstName, d.LastName FROM DELETED as d

 

INSERT INTO employee_log (Operation, FirstName, LastName)

SELECT 'Update: added row', i.FirstName, i.LastName FROM INSERTED as i

END

 

-- DELETE FROM employee WHERE ID = 5

IF (SELECT COUNT(*) FROM INSERTED) = 0 AND (SELECT COUNT(*) FROM DELETED) > 0

INSERT INTO employee_log (Operation, FirstName, LastName)

SELECT 'Delete', d.FirstName, d.LastName FROM DELETED as d

GO

 

CREATE TRIGGER work_on_modify ON dbo.work_on

AFTER INSERT, UPDATE, DELETE

AS

-- UPDATE work_on SET [Job] = 'Manager' WHERE ID = 3

SELECT 'Seccussfull' AS [Message]

GO

 

 

private static void ReadOrderData(string connectionString)

{

    string queryString =

        "SELECT OrderID, CustomerID FROM dbo.Orders;";

    using (SqlConnection connection = new SqlConnection(

               connectionString))

    {

        SqlCommand command = new SqlCommand(

            queryString, connection);

        connection.Open();

        SqlDataReader reader = command.ExecuteReader();

        try

        {

            while (reader.Read())

            {

                Console.WriteLine(String.Format("{0}, {1}",

                    reader[0], reader[1]));

            }

        }

        finally

        {

            reader.Close();

        }

    }

}

 

using System;

using System.Data.SqlClient;

 

class Program

{

    static void Main()

    {

string connectionString = ConsoleApplication1.Properties.Settings.Default.ConnectionString;

 

using (SqlConnection con = new SqlConnection(connectionString))

{

con.Open();

 

using (SqlCommand command = new SqlCommand("SELECT TOP 2 * FROM Dogs1", con))

using (SqlDataReader reader = command.ExecuteReader())

{

while (reader.Read())

{

Console.WriteLine("{0} {1} {2}",

reader.GetInt32(0), reader.GetString(1), reader.GetString(2));

}

}

}

    }

}

 

private static void CreateCommand(string queryString,

    string connectionString)

{

    using (SqlConnection connection = new SqlConnection(

               connectionString))

    {

        SqlCommand command = new SqlCommand(queryString, connection);

        command.Connection.Open();

        command.ExecuteNonQuery();

    }

}