I'm trying to create database and related tables. Everything works except from 'EmployeeDocuments' table (database and 4 tables were created) . It gives an error like:
There is already an object named 'FK_Employees_Companies1' in the database.
My codes are as below:
private void UserEntryForm_Activated(object sender, EventArgs e)
{
CheckIfDBExist();
}
int i;
private void CheckIfDBExist()
{
SqlConnection tmpConn = new SqlConnection("server=(local)\\SQLEXPRESS;user=sa;pwd=123");
string sqlCreateDBQuery = string.Format("if not exists (select name from sys.databases where name = 'Devrimer_v2222')create database Devrimer_v2222");
SqlCommand sqlCmd = new SqlCommand(sqlCreateDBQuery, tmpConn);
string constr = Tools.Baglanti.ConnectionString.ToString();
try
{
tmpConn.Open();
int sonuc = sqlCmd.ExecuteNonQuery();
if (sonuc != 0)
{
tmpConn.Close();
string query = "if not exists(select * from sys.tables where name = 'Companies' ";
query += ")";
query += "CREATE TABLE [dbo].[Companies]([Id][int] IDENTITY(1, 1) NOT NULL,";
query += "[Name][nvarchar](100) NULL,";
query += "[Adress][nvarchar](500) NULL,";
query += "[TelNo][char](20) NULL,";
query += "[Active][bit] NOT NULL CONSTRAINT[DF_Companies_Active] DEFAULT((1)),";
query += "CONSTRAINT[PK_Companies] PRIMARY KEY CLUSTERED([Id] ASC ) WITH(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,";
query += "IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,";
query += "ALLOW_PAGE_LOCKS = ON) ON[PRIMARY] ) ON[PRIMARY]";
query += " SET ANSI_PADDING OFF";
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(query))
{
cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
string docQuery = "if not exists(select * from sys.tables where name = 'Documents' ";
docQuery += ")";
docQuery += "CREATE TABLE [dbo].[Documents]([Id] [int] IDENTITY(1,1) NOT NULL,";
docQuery += "[Name] [nvarchar](50) NULL,";
docQuery += "[Description] [nvarchar](500) NULL,";
docQuery += "[ValidPeriod] [int] NULL,";
docQuery += "[PeriodType] [nchar](10) NULL,";
docQuery += "[Active] [bit] NOT NULL CONSTRAINT [DF_Documents_Active] DEFAULT ((1)), ";
docQuery += "CONSTRAINT [PK_Documents] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, ";
docQuery += "STATISTICS_NORECOMPUTE = OFF, ";
docQuery += "IGNORE_DUP_KEY = OFF, ";
docQuery += "ALLOW_ROW_LOCKS = ON, ";
docQuery += " ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]";
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(docQuery))
{
cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
string EmpQuery = "if not exists(select * from sys.tables where name = 'Employees' ";
EmpQuery += ")";
EmpQuery += "CREATE TABLE [dbo].[Employees]( [Id] [int] IDENTITY(1,1) NOT NULL,";
EmpQuery += "[Name] [nvarchar](50) NULL,";
EmpQuery += "[SurName] [nvarchar](50) NULL,";
EmpQuery += "[Title] [nvarchar](50) NULL,";
EmpQuery += "[Gender] [tinyint] NULL,";
EmpQuery += "[MaritalStatus] [tinyint] NULL,";
EmpQuery += "[Tckn] [char](11) NULL,";
EmpQuery += "[ReportTo] [int] NULL,";
EmpQuery += "[BirthDate] [date] NULL, ";
EmpQuery += "[TelNo] [char](20) NULL, ";
EmpQuery += "[Adress] [nvarchar](500) NULL, ";
EmpQuery += "[Email] [nvarchar](250) NULL, ";
EmpQuery += "[CompanyID] [int] NULL,";
EmpQuery += "[StartWorkingDate] [date] NULL, ";
EmpQuery += "[Active] [bit] NOT NULL CONSTRAINT [DF_Employees_Active] DEFAULT ((1)), ";
EmpQuery += " CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, ";
EmpQuery += "STATISTICS_NORECOMPUTE = OFF, ";
EmpQuery += "IGNORE_DUP_KEY = OFF, ";
EmpQuery += "ALLOW_ROW_LOCKS = ON, ";
EmpQuery += "ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] ";
EmpQuery += ";";
EmpQuery += "SET ANSI_PADDING OFF ";
EmpQuery += "ALTER TABLE [dbo].[Employees] WITH CHECK ADD CONSTRAINT [FK_Employees_Companies1] FOREIGN KEY([CompanyID]) REFERENCES [dbo].[Companies] ([Id]) ";
EmpQuery += ";";
EmpQuery += "ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [FK_Employees_Companies1] ";
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(EmpQuery))
{
cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
string userQuery = "if not exists(select * from sys.tables where name = 'Users' ";
userQuery += ")";
userQuery += "CREATE TABLE [dbo].[Users]( [Id] [int] IDENTITY(1,1) NOT NULL, ";
userQuery += "[EmployeID] [int] NULL, ";
userQuery += "[UserName] [nvarchar](50) NULL, ";
userQuery += "[Password] [nchar](10) NULL, ";
userQuery += "[Active] [bit] NOT NULL CONSTRAINT [DF_Users_Aktif] DEFAULT ((1)), ";
userQuery += " CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, ";
userQuery += "STATISTICS_NORECOMPUTE = OFF, ";
userQuery += "IGNORE_DUP_KEY = OFF, ";
userQuery += "ALLOW_ROW_LOCKS = ON,";
userQuery += "ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] ";
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(userQuery))
{
cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
string docEmpQuery = "if not exists(select * from sys.tables where name = 'EmployeeDocuments' ";
docEmpQuery += ")";
docEmpQuery += "CREATE TABLE [dbo].[EmployeeDocuments]( [EmployeeID] [int] NOT NULL, ";
docEmpQuery += "[DocumentID] [int] NOT NULL, ";
docEmpQuery += "[GivenDate] [date] NULL, ";
docEmpQuery += "[LastValidDate] [date] NULL, ";
docEmpQuery += "CONSTRAINT [PK_EmployeeDocuments] PRIMARY KEY CLUSTERED ( [EmployeeID] ASC, ";
docEmpQuery += "[DocumentID] ASC )WITH (PAD_INDEX = OFF, ";
docEmpQuery += "STATISTICS_NORECOMPUTE = OFF, ";
docEmpQuery += "IGNORE_DUP_KEY = OFF, ";
docEmpQuery += "ALLOW_ROW_LOCKS = ON,";
docEmpQuery += "ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]";
docEmpQuery += ";";
docEmpQuery += "ALTER TABLE [dbo].[EmployeeDocuments] WITH CHECK ADD CONSTRAINT [FK_EmployeeDocuments_Documents1] FOREIGN KEY([DocumentID])REFERENCES [dbo].[Documents] ([Id]) ";
docEmpQuery += ";";
docEmpQuery += "ALTER TABLE [dbo].[EmployeeDocuments] CHECK CONSTRAINT [FK_EmployeeDocuments_Documents1] ";
docEmpQuery += "; ";
docEmpQuery += "ALTER TABLE [dbo].[EmployeeDocuments] WITH CHECK ADD CONSTRAINT [FK_EmployeeDocuments_Employees1] FOREIGN KEY([EmployeeID]) REFERENCES [dbo].Employees] ([Id]) ";
docEmpQuery += ";";
docEmpQuery += "ALTER TABLE [dbo].[EmployeeDocuments] CHECK CONSTRAINT [FK_EmployeeDocuments_Employees1] ";
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(docEmpQuery))
{
cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
}
}
catch (Exception hata)
{
MessageBox.Show(string.Format(sqlCmd.CommandText.ToString() + "\n\nHata: " + hata.Message.ToString()));
}
finally
{
tmpConn.Close();
}
}
}
I have use MyDbUtils.exe and exported all procedures and triggers, and included syntax for the tables and database creation , It worked very well;
I have used a method (SQLTEXTexec ()) to run the all text string:
then; used CheckIfDBExist()) method to create database and tables; worked fine so far ;
private void SQLTEXTexec(string sql, SqlConnection conn)
{
string sqlBatch = string.Empty;
SqlCommand cmd = new SqlCommand(string.Empty, conn);
conn.Open();
sql += "\nGO"; // make sure last batch is executed.
try
{
foreach (string line in sql.Split(new string[2] { "\n", "\r" }, StringSplitOptions.RemoveEmptyEntries))
{
if (line.ToUpperInvariant().Trim() == "GO")
{
cmd.CommandText = sqlBatch;
cmd.ExecuteNonQuery();
sqlBatch = string.Empty;
}
else
{
sqlBatch += line + "\n";
}
}
}
finally
{
conn.Close();
}
private void CheckIfDBExist()
{
SqlConnection tmpConn = new SqlConnection("server=(local)\\SQLEXPRESS;user=sa;pwd=123");
//("if not exists (select name from sys.databases where name = 'Devrimer_v2222' ) create database Devrimer_v2222");
string sqlCreateDBQuery = string.Format("if not exists (select name from sys.databases where name = 'Devrimer_v2222')create database Devrimer_v2222");
SqlCommand sqlCmd = new SqlCommand(sqlCreateDBQuery, tmpConn);
string constr = Tools.Baglanti.ConnectionString.ToString();
try
{
tmpConn.Open();
int sonuc = sqlCmd.ExecuteNonQuery();
if (sonuc != 0) // database i silince çalışacak...
{
tmpConn.Close();
string query = @"if not exists (select name from sys.databases where name = 'Devrimer_v2222')
create database Devrimer_v2222
GO
use Devrimer_v2222
if not exists(select * from sys.tables where name = 'Companies')
CREATE TABLE [dbo].[Companies](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](100) NULL,
[Adress] [nvarchar](500) NULL,
[TelNo] [char](20) NULL,
[Active] [bit] NOT NULL CONSTRAINT [DF_Companies_Active] DEFAULT ((1)),
CONSTRAINT [PK_Companies] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE Devrimer_v2222
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[prc_Companies_Delete]') AND type = N'P')
exec('CREATE PROCEDURE [dbo].[prc_Companies_Delete] AS BEGIN SET NOCOUNT ON; END')
GO
ALTER proc [dbo].[prc_Companies_Delete]
@Id int
as
delete Companies
where
Id=@Id
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[prc_Companies_Insert]') AND type = N'P')
exec('CREATE PROCEDURE [dbo].[prc_Companies_Insert] AS BEGIN SET NOCOUNT ON; END')
GO
ALTER proc [dbo].[prc_Companies_Insert]
--@Id int, primary kolonu almıyoruz.
@Name nvarchar(100),
@Adress nvarchar(500),
@TelNo char(20),
@Active bit
as
insert Companies
values (@Name,@Adress,@TelNo,@Active)
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[prc_Companies_Select]') AND type = N'P')
exec('CREATE PROCEDURE [dbo].[prc_Companies_Select] AS BEGIN SET NOCOUNT ON; END')
GO
ALTER proc [dbo].[prc_Companies_Select]
as
select * from Companies where Active=1
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[prc_Companies_Update]') AND type = N'P')
exec('CREATE PROCEDURE [dbo].[prc_Companies_Update] AS BEGIN SET NOCOUNT ON; END')
GO
ALTER proc [dbo].[prc_Companies_Update]
@Id int,
@Name nvarchar(100),
@Adress nvarchar(500),
@TelNo char(20),
@Active bit
as
update Companies
set
--Id=@Id,
Name=@Name,
Adress=@Adress,
TelNo=@TelNo,
Active=@Active
where
Id=@Id
GO
USE Devrimer_v2222
GO
INSERT INTO [dbo].[Companies]
([Name]
,[Adress]
,[TelNo]
,[Active])
VALUES
('Anagold','Erzincan',0543456,1)
GO
use Devrimer_v2222
if not exists(select * from sys.tables where name = 'Documents')
begin
CREATE TABLE [dbo].[Documents](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NULL,
[Description] [nvarchar](500) NULL,
[ValidPeriod] [int] NULL,
[PeriodType] [nchar](10) NULL,
[Active] [bit] NOT NULL CONSTRAINT [DF_Documents_Active] DEFAULT ((1)),
CONSTRAINT [PK_Documents] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
use Devrimer_v2222
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[prc_Documents_Delete]') AND type = N'P')
exec('CREATE PROCEDURE [dbo].[prc_Documents_Delete] AS BEGIN SET NOCOUNT ON; END')
GO
ALTER proc [dbo].[prc_Documents_Delete]
@Id int
as
delete Documents
where
Id=@Id
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[prc_Documents_Insert]') AND type = N'P')
exec('CREATE PROCEDURE [dbo].[prc_Documents_Insert] AS BEGIN SET NOCOUNT ON; END')
GO
ALTER proc [dbo].[prc_Documents_Insert]
--@Id int, primary kolonu almıyoruz.
@Name nvarchar(50),
@Description nvarchar(500),
@ValidPeriod int,
@periodType char(10),
@Active bit
as
insert Documents
values (@Name,@Description,@ValidPeriod,@periodType,@Active)
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[prc_Documents_Select]') AND type = N'P')
exec('CREATE PROCEDURE [dbo].[prc_Documents_Select] AS BEGIN SET NOCOUNT ON; END')
GO
ALTER proc [dbo].[prc_Documents_Select]
as
select * from Documents where Active=1
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[prc_Documents_Update]') AND type = N'P')
exec('CREATE PROCEDURE [dbo].[prc_Documents_Update] AS BEGIN SET NOCOUNT ON; END')
GO
ALTER proc [dbo].[prc_Documents_Update]
@Id int,
@Name nvarchar(50),
@Description nvarchar(500),
@ValidPeriod int,
@PeriodType nchar(10),
@Active bit
as
update Documents
set
--Id=@Id,
Name=@Name,
Description=@Description,
ValidPeriod=@ValidPeriod,
PeriodType=@PeriodType,
Active=@Active
where
Id=@Id
GO
use Devrimer_v2222
GO
if not exists(select * from sys.tables where name = 'Employees')
begin
CREATE TABLE [dbo].[Employees](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NULL,
[SurName] [nvarchar](50) NULL,
[Title] [nvarchar](50) NULL,
[Gender] [tinyint] NULL,
[MaritalStatus] [tinyint] NULL,
[Tckn] [char](11) NULL,
[ReportTo] [int] NULL,
[BirthDate] [date] NULL,
[TelNo] [char](20) NULL,
[Adress] [nvarchar](500) NULL,
[Email] [nvarchar](250) NULL,
[CompanyID] [int] NULL,
[StartWorkingDate] [date] NULL,
[Active] [bit] NOT NULL CONSTRAINT [DF_Employees_Active] DEFAULT ((1)),
CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Employees] WITH CHECK ADD CONSTRAINT [FK_Employees_Companies1] FOREIGN KEY([CompanyID])
REFERENCES [dbo].[Companies] ([Id])
GO
ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [FK_Employees_Companies1]
GO
use Devrimer_v2222
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[prc_Employees_Delete]') AND type = N'P')
exec('CREATE PROCEDURE [dbo].[prc_Employees_Delete] AS BEGIN SET NOCOUNT ON; END')
GO
ALTER proc [dbo].[prc_Employees_Delete]
@Id int
as
delete Employees
where
Id=@Id
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[prc_Employees_Insert]') AND type = N'P')
exec('CREATE PROCEDURE [dbo].[prc_Employees_Insert] AS BEGIN SET NOCOUNT ON; END')
GO
ALTER proc [dbo].[prc_Employees_Insert]
--@Id int, primary kolonu almıyoruz.
@Name nvarchar(50),
@SurName nvarchar(50),
@Title nvarchar(50),
@Gender char(10),
@MaritalStatus char(10),
@Tckn char(11),
@ReportTo nvarchar(50),
@BirthDate date,
@TelNo char(20),
@Adress nvarchar(500),
@Email nvarchar(250),
@CompanyID int,
@StartWorkingDate date,
@Active bit
as
insert Employees
values (@Name,@SurName,@Title,@Gender,@MaritalStatus,@Tckn,@ReportTo,@BirthDate,
@TelNo,@Adress,@Email,@CompanyID,@StartWorkingDate,@Active)
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[prc_Employees_Select]') AND type = N'P')
exec('CREATE PROCEDURE [dbo].[prc_Employees_Select] AS BEGIN SET NOCOUNT ON; END')
GO
ALTER proc [dbo].[prc_Employees_Select]
as
Select
Id,
Name,
SurName,
Title,
case MaritalStatus
when 1 then 'Married'
when 2 then 'Single'
end as MaritalStatus,
case Gender
when 1 then 'Male'
when 2 then 'Female'
end as Gender,
Tckn,
ReportTo,
BirthDate,
TelNo,
Adress,
Email,
CompanyID,
StartWorkingDate
from Employees where Active = 1
GO
use Devrimer_v2222
GO
INSERT INTO [dbo].[Employees]
([Name]
,[SurName]
,[Title]
,[Gender]
,[MaritalStatus]
,[Tckn]
,[ReportTo]
,[BirthDate]
,[TelNo]
,[Adress]
,[Email]
,[CompanyID]
,[StartWorkingDate]
,[Active])
VALUES
('Özgür','Acar','MadenMühendisi',1,1,12345,1,'01.03.1974',1234,'Adres','oo@c.c',1,'01.03.2002',1)
GO
use Devrimer_v2222
if not exists(select * from sys.tables where name = 'Users')
begin
CREATE TABLE [dbo].[Users](
[Id] [int] IDENTITY(1,1) NOT NULL,
[EmployeID] [int] NULL,
[UserName] [nvarchar](50) NULL,
[Password] [nchar](10) NULL,
[Active] [bit] NOT NULL CONSTRAINT [DF_Users_Aktif] DEFAULT ((1)),
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
USE [Devrimer_v2222]
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[prc_Users_Delete]') AND type = N'P')
exec('CREATE PROCEDURE [dbo].[prc_Users_Delete] AS BEGIN SET NOCOUNT ON; END')
GO
ALTER proc [dbo].[prc_Users_Delete]
@Id int
as
delete Users
where
Id=@Id
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[prc_Users_Entry]') AND type = N'P')
exec('CREATE PROCEDURE [dbo].[prc_Users_Entry] AS BEGIN SET NOCOUNT ON; END')
GO
ALTER proc [dbo].[prc_Users_Entry]
@unm nvarchar (50),
@pas nvarchar (15)
as
select * from Users
where UserName=@unm and Password=@pas and Active=1
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[prc_Users_Insert]') AND type = N'P')
exec('CREATE PROCEDURE [dbo].[prc_Users_Insert] AS BEGIN SET NOCOUNT ON; END')
GO
ALTER proc [dbo].[prc_Users_Insert]
@EmployeID int,
@UserName nvarchar (50),
@Password nvarchar (10),
@Active bit
as
insert Users
values (@EmployeID, @UserName,@Password,@Active)
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[prc_Users_Select]') AND type = N'P')
exec('CREATE PROCEDURE [dbo].[prc_Users_Select] AS BEGIN SET NOCOUNT ON; END')
GO
ALTER proc [dbo].[prc_Users_Select]
as
select * from Users where Active=1
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[prc_Users_Update]') AND type = N'P')
exec('CREATE PROCEDURE [dbo].[prc_Users_Update] AS BEGIN SET NOCOUNT ON; END')
GO
ALTER proc [dbo].[prc_Users_Update]
@Id int,
@EmployeID int,
@UserName nvarchar(50),
@Password nvarchar(50),
@Active bit
as
update Users
set
--Id=@Id,
EmployeID=@EmployeID,
UserName=@UserName,
Password=@Password
where
Id=@Id
GO
use Devrimer_v2222
GO
INSERT INTO [dbo].[Users]
([EmployeID]
,[UserName]
,[Password]
,[Active])
VALUES
(1,1,1,1)
GO
use Devrimer_v2222
if not exists(select * from sys.tables where name = 'EmployeeDocuments')
begin
CREATE TABLE [dbo].[EmployeeDocuments](
[EmployeeID] [int] NOT NULL,
[DocumentID] [int] NOT NULL,
[GivenDate] [date] NULL,
[LastValidDate] [date] NULL,
CONSTRAINT [PK_EmployeeDocuments] PRIMARY KEY CLUSTERED
(
[EmployeeID] ASC,
[DocumentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
ALTER TABLE [dbo].[EmployeeDocuments] WITH CHECK ADD CONSTRAINT [FK_EmployeeDocuments_Documents1] FOREIGN KEY([DocumentID])
REFERENCES [dbo].[Documents] ([Id])
GO
ALTER TABLE [dbo].[EmployeeDocuments] CHECK CONSTRAINT [FK_EmployeeDocuments_Documents1]
GO
use Devrimer_v2222
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[prc_EmployeeDocuments_Delete]') AND type = N'P')
exec('CREATE PROCEDURE [dbo].[prc_EmployeeDocuments_Delete] AS BEGIN SET NOCOUNT ON; END')
GO
ALTER proc [dbo].[prc_EmployeeDocuments_Delete]
@EmployeID int,
@DocumentID int
as
delete EmployeeDocuments
where
DocumentID=@DocumentID and EmployeeID=@EmployeID
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[prc_EmployeeDocuments_Insert]') AND type = N'P')
exec('CREATE PROCEDURE [dbo].[prc_EmployeeDocuments_Insert] AS BEGIN SET NOCOUNT ON; END')
GO
ALTER proc [dbo].[prc_EmployeeDocuments_Insert]
@EmployeeID int,
@DocumentID int,
@GivenDate date,
@LastValidDate date
as
insert EmployeeDocuments
values (@EmployeeID,@DocumentID,@GivenDate,@LastValidDate)
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[prc_EmployeeDocuments_Select]') AND type = N'P')
exec('CREATE PROCEDURE [dbo].[prc_EmployeeDocuments_Select] AS BEGIN SET NOCOUNT ON; END')
GO
ALTER proc [dbo].[prc_EmployeeDocuments_Select]
as
select * from EmployeeDocuments
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[prc_EmployeeDocuments_Update]') AND type = N'P')
exec('CREATE PROCEDURE [dbo].[prc_EmployeeDocuments_Update] AS BEGIN SET NOCOUNT ON; END')
GO
ALTER proc [dbo].[prc_EmployeeDocuments_Update]
@EmployeeID int,
@DocumentID int,
@GivenDate date,
@LastValidDate date
as
update EmployeeDocuments
set
--Id=@Id,
EmployeeID=@EmployeeID,
DocumentID=@DocumentID,
GivenDate=@GivenDate,
LastValidDate=@LastValidDate
where
@EmployeeID=@EmployeeID
GO
use Devrimer_v2222
IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[trg_Delete_Companies]'))
DROP TRIGGER [dbo].[trg_Delete_Companies]
GO
create trigger [dbo].[trg_Delete_Companies]
on [dbo].[Companies]
instead of delete
as
declare @Id int
select @Id= Id from deleted -- 1. Atama yöntemi
set @Id =(select Id from deleted) -- 2. Atama Yöntemi - tek kayıt varsa
update Companies set Active=0 where Id=@Id
GO
IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[trg_Delete_Documents]'))
DROP TRIGGER [dbo].[trg_Delete_Documents]
GO
create trigger [dbo].[trg_Delete_Documents]
on [dbo].[Documents]
instead of delete
as
declare @Id int
select @Id= Id from deleted -- 1. Atama yöntemi
set @Id =(select Id from deleted) -- 2. Atama Yöntemi - tek kayıt varsa
update Documents set Active=0 where Id=@Id
GO
IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[trg_Delete_Employees]'))
DROP TRIGGER [dbo].[trg_Delete_Employees]
GO
create trigger [dbo].[trg_Delete_Employees]
on [dbo].[Employees]
instead of delete
as
declare @Id int
select @Id= Id from deleted -- 1. Atama yöntemi
set @Id =(select Id from deleted) -- 2. Atama Yöntemi - tek kayıt varsa
update Employees set Active=0 where Id=@Id
GO
IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[trg_Delete_Users]'))
DROP TRIGGER [dbo].[trg_Delete_Users]
GO
create trigger [dbo].[trg_Delete_Users]
on [dbo].[Users]
instead of delete
as
declare @Id int
--select @Id= Id from deleted -- 1. Atama yöntemi
set @Id =(select Id from deleted) -- 2. Atama Yöntemi - tek kayıt varsa
update Users set Active=0 where Id=@Id ";
SqlConnection con = Tools.Baglanti;
SQLTEXTexec(query, con);
}
}
catch (Exception hata)
{
// MessageBox.Show(string.Format(sqlCmd.CommandText.ToString() + "\n\nHata: " + hata.Message.ToString()));
}
finally
{
tmpConn.Close();
}
}
}