Таблица EmployeeDocuments не может быть создана в базе данных
Я пытаюсь создать базу данных и связанные таблицы. Все работает, кроме таблицы "EmployeeDocuments" (база данных и 4 таблицы созданы) . Это дает ошибку как:
В базе данных уже есть объект с именем FK_Employees_Companies1.
Мои коды, как показано ниже:
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();
}
}
}
3 ответа
Решение
Я использовал MyDbUtils.exe и экспортировал все процедуры и триггеры, и включил синтаксис для таблиц и создания базы данных, он работал очень хорошо;
Я использовал метод (SQLTEXTexec ()) для запуска всей текстовой строки:
затем; использовал метод CheckIfDBExist()) для создания базы данных и таблиц; до сих пор работал нормально;
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();
}
}
}
Это потому, что вы проверяете, существует ли таблица перед ее созданием, но вы всегда пытаетесь создать ограничения независимо от того, существуют они или нет, у вас есть что-то вроде:
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'Employees')
CREATE TABLE dbo.Employees (....)
ALTER TABLE dbo.Employees WITH CHECK ADD CONSTRAINT ....
С этим синтаксисом это только утверждение, следующее сразу за IF это будет условно, простым примером будет:
IF 1 = 0
PRINT '1';
PRINT '2';
Который просто напечатает "2". Если вы хотите, чтобы несколько операторов выполнялись условно, вам нужно заключить их в блок операторов (BEGIN/END)
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'Employees')
BEGIN
CREATE TABLE dbo.Employees (....)
ALTER TABLE dbo.Employees WITH CHECK ADD CONSTRAINT ....
END
Для чего бы это ни стоило, вы можете использовать дословный строковый литерал, чтобы сделать все немного более разборчивым (и более простым для редактирования). например
string query = @"IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'Employees')
BEGIN
CREATE TABLE dbo.Employees (....)
ALTER TABLE dbo.Employees WITH CHECK ADD CONSTRAINT ....
END;";
Попробуйте изменить имя ограничения, кажется, оно создано с тем же именем в вашей базе данных