if you need generation tools for sql server db you can use the following component CS2GEN
download the latest version .exe file
you can easily connect to your db and generate c# ORM and sql server script file for stored procedures
after finish installation you will get the following screen
choose db from left menu open database
enter you db connection information
enter you output directory to save your file
open the folder to get your files
you will find ready ORM Desktop sample or Web Application in c# you can use it as database Layer
and also you will find the .sql file related your database .
Open the script file .sql in management studio and choose stored Procedures you need to apply to your database to safe your time.
if you need to use c# ORM as db layer you should execute whole the file to keep stored procedures by name (don't change the name of stored procedures) if you will use the generated code .
----------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------
Manually Create Stored
first we will create table called Users table (run the following script to new database to create it )
USE [YourDBName]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Users](
[UserID] [int] IDENTITY(1,1) NOT NULL,
[UserFullName] [nvarchar](150) NOT NULL,
[LoginName] [varchar](50) NULL,
[LoginPassword] [varchar](50) NULL,
[Email] [varchar](100) NULL,
[Mobile] [varchar](15) NULL,
[Extension] [varchar](6) NULL,
[phone] [varchar](15) NULL,
[FaxNo] [varchar](15) NULL,
[Disabled] [bit] NULL,
[PrimaryGroup] [int] NULL,
[PrimaryProfile] [int] NULL,
[CreateDate] [datetime] NULL,
[CreatedBy] [int] NULL,
[LastLoginTime] [datetime] NULL,
[LastUpdateTime] [datetime] NULL,
[DegreeID] [int] NULL,
[themesColor] [nvarchar](50) NULL,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[UserID] 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
ALTER TABLE [dbo].[Users] WITH CHECK ADD CONSTRAINT [FK_Users_Degrees] FOREIGN KEY([DegreeID])
REFERENCES [dbo].[Degrees] ([DegreeID])
GO
ALTER TABLE [dbo].[Users] CHECK CONSTRAINT [FK_Users_Degrees]
GO
ALTER TABLE [dbo].[Users] WITH CHECK ADD CONSTRAINT [FK_Users_Groups] FOREIGN KEY([PrimaryGroup])
REFERENCES [dbo].[Groups] ([GroupID])
GO
ALTER TABLE [dbo].[Users] CHECK CONSTRAINT [FK_Users_Groups]
GO
ALTER TABLE [dbo].[Users] ADD CONSTRAINT [DF_Users_Disabled] DEFAULT ((0)) FOR [Disabled]
GO
----------------------------------------------------------
now we will create stored procedures for the table
1-insert Procedure
CREATEPROCEDURE [dbo].[InsertUser]
@UserFullName nvarchar(150)
,@LoginName varchar(50)
,@LoginPassword varchar(50)
,@Email varchar(100)
,@Mobile varchar(15)
,@Extension varchar(6)
,@Phone varchar(15)
,@FaxNo varchar(15)
,@Disabled bit
,@PrimaryGroup int
,@PrimaryProfile int
,@CreatedBy int
,@DegreeID int
as
begin
declare @flag char(1)
set @flag= '0'
IF NOT EXISTS (select * from Users where UserFullName = @UserFullName)
BEGIN
INSERT INTO [Users]
([UserFullName]
,[LoginName]
,[LoginPassword]
,[Email]
,[Mobile]
,[Extension]
,[phone]
,[FaxNo]
,[Disabled]
,[PrimaryGroup]
,[PrimaryProfile]
,[CreateDate]
,[CreatedBy]
,[DegreeID])
VALUES
(@UserFullName
,@LoginName
,@LoginPassword
,@Email
,@Mobile
,@Extension
,@phone
,@FaxNo
,@Disabled
,@PrimaryGroup
,@PrimaryProfile
,GETDATE()
,@CreatedBy
,@DegreeID)
set @flag = '1'
end
select @flag
end
------------------------------------------------------------------------------------
update stored procedure
CREATE procedure [dbo].[UpdateUser]
@UserID int
,@UserFullName nvarchar(150)
,@LoginName varchar(50)
,@LoginPassword varchar(50)
,@Email varchar(100)
,@Mobile varchar(15)
,@Extension varchar(6)
,@Phone varchar(15)
,@FaxNo varchar(15)
,@Disabled bit
,@PrimaryGroup int
,@PrimaryProfile int
,@DegreeID int
as
begin
UPDATE [Users]
set [UserFullName] = @UserFullName
,[LoginName] = @LoginName
,[LoginPassword] = @LoginPassword
,[Email] = @Email
,[Mobile] = @Mobile
,[Extension] = @Extension
,[phone] = @Phone
,[FaxNo] = @FaxNo
,[Disabled] = @Disabled
,[PrimaryGroup] = @PrimaryGroup
,[PrimaryProfile] = @PrimaryProfile
,[LastUpdateTime] = GETDATE()
,[DegreeID] = @DegreeID
WHERE UserID= @UserID
end
------------------------------------------------------------------------------------------------
delete Users
Create PROCEDURE [dbo].[DeleteUserInfo]
@ID int
as
begin
Declare @flag as int
set @flag=1
begin try
Delete FROM Group_Users where Group_Users.UserID = @ID
Delete FROM [Users] where UserID=@ID
end try
begin catch
set @flag = 0
end catch
select @flag
end
-------------------------------------------------------------------------------------------
search Users By Full Name
Create PROCEDURE [dbo].[UserSEARCH]
@name nvarchar(50)
as
begin
select [UserID]
,[UserFullName]
,[LoginName]
,[LoginPassword]
,[Email]
,[Mobile]
,[Extension]
,[phone]
,[FaxNo]
,[Disabled]
,[PrimaryGroup]
,[PrimaryProfile]
,[CreateDate]
,[CreatedBy]
,[LastLoginTime]
,[LastUpdateTime]
,Degrees.[DegreeID]
,Degrees.DegreeName as DegreeName
FROM [Users] inner join [Degrees]
on Degrees.DegreeID = Users.DegreeID
where LoginName like '%'+@name+'%' or UserFullName like '%'+@name+'%'
end
if you need to search table all columns and need to depends on passed query regardless of if the parameter passed is null or not passed at all
here's example to handle good search form stored procedure .
create the following table
USE [HOSPITAL_DB]
GO
/****** Object: Table [dbo].[Doc_Servers] Script Date: 01/23/2014 09:25:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Doc_Servers](
[DocServerID] [int] IDENTITY(1,1) NOT NULL,
[Location] [varchar](250) NOT NULL,
[Disabled] [bit] NULL,
[LibraryName] [nvarchar](100) NOT NULL,
[LibraryDesc] [nvarchar](300) NULL,
[IsPrivateLibrary] [bit] NULL,
CONSTRAINT [PK_Doc_Servers] PRIMARY KEY CLUSTERED
(
[DocServerID] 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
ALTER TABLE [dbo].[Doc_Servers] ADD CONSTRAINT [DF_Doc_Servers_Disabled] DEFAULT ((1)) FOR [Disabled]
GO
ALTER TABLE [dbo].[Doc_Servers] ADD CONSTRAINT [DF_Doc_Servers_IsPrivateLibrary] DEFAULT ((0)) FOR [IsPrivateLibrary]
GO
-----------------------------------------------------------------------------------------
search Procedure
ALTER PROCEDURE [dbo].[Doc_Servers_Search]
@DocServerID int = NULL,
@LibraryName nvarchar(100) = NULL
AS
BEGIN
SELECT [DocServerID],[Location],[Disabled],[LibraryName],[LibraryDesc]
FROM [Doc_Servers] T
WHERE (@DocServerID IS NULL OR T.DocServerID = @DocServerID)
and
(@LibraryName IS NULL OR T.LibraryName like '%'+@LibraryName+'%')
and (IsPrivateLibrary IS NULL OR IsPrivateLibrary <> 1)
END
the previous syntax is very good for search if you have multiple columns and need to generate one procedure to search any of these columns so if the parameter passed to procedure it will check it's value condition else it will ignore filter condition and set default parameter value to null so this is good way to handle stored procedure for search structure for any table for you .
to execute and test procedure use the following script
exec Doc_Servers_Search null, null
exec Doc_Servers_Search null, ''
exec Doc_Servers_Search null, 'Lib'
very important note if you need your varchar fields to be english Case sensitive or Insensitive
To create your search Case Sensetive use the following sample
(@LibraryName IS NULL OR T.LibraryName like '%'+@LibraryName+'%' Collate Latin1_General_CS_AI)
to create your search Case insensitive
(@LibraryName IS NULL OR T.LibraryName like '%'+@LibraryName+'%' Collate Latin1_General_CI_AI)
Example to test the two collation
begin
if ('a'='A' Collate Latin1_General_CI_AI)
print'same 1'
else
print 'different 1'
if ('a'='A' Collate Latin1_General_CS_AI)
print'same 2'
else
print 'different 2'
end
-----------------------------------------------------------------------------------------------
i hope this post help you to create your own insert , update , delete , search Templates .
