CodeSmith
(1)时间就是金钱,效率就是生命,开发一个系统本来需要两个月的时间,你如果用了6个月,那就是让公司支出三倍的工资给你,本来你应该一个月拿6000块钱,那么相反,你一个月只能拿到2000,或者企业亏损,一个企业就会倒闭。
(2)使用CodeSmith可以生成松耦合的三层,中间使用存储过程,使程序性能有显著的提高。
(3)处理数据库表信息部分字段null的问题,避免在程序运行中产生异常。
(4)使用SqlHelper解决一个大项目同时连接Oracle和Sqlserver的分布式应用程序的问题
子曰:工欲善其事,必先利其器
1 解决数据库中表中某些列是null 的问题 避免程序运行中产生异常。
数据访问层只须这样写
public static IList<UserInfo> GetUserInfosBySql( string safeSql )
{
List<UserInfo> list = new List<UserInfo>();
try
{
SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.AppConnectionString, CommandType.Text,safeSql, null);
while (reader.Read())
{
UserInfo userInfo = new UserInfo(reader);
list.Add(userInfo);
}
if(list.Count>0)
{
reader.Close();
return list;
}
else
{
reader.Close();
return null;
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return null;
}
}
就不用每个地方都给属性赋值了,并且提高了面向对象的思想。
2 如果你的表是UserInfo表,自动在业务逻辑层生成注册和登录
public static bool Login(string loginId, string loginPwd, out UserInfo validitUser)
{
UserInfo user=GetUserInfoByLoginId(loginId);
if (user == null)
{
validitUser = null;
return false;
}
if (user.LoginPwd.Trim() == loginPwd.Trim())
{
validitUser = user;
return true;
}
else
{
validitUser = null;
return false;
}
}
public static UserInfo GetUserInfoByLoginId(string loginId)
{
return UserInfoService.GetUserInfoByLoginId(loginId);
}
public static bool Register(UserInfo addUser)
{
UserInfo user=GetUserInfoByLoginId(addUser.LoginId);
if (user == null)
{
if (AddUserInfo(addUser) > 0)
{
return true;
}
else
{
return false;
}
}
else
{
return false;
}
}
3 使用SqlParameter,解决sql注入问题
try
{
SqlParameter[] para = new SqlParameter[]
{
new SqlParameter("@Id", id),
new SqlParameter("@LoginId",loginId),
new SqlParameter("@LoginPwd",loginPwd),
new SqlParameter("@Sex",sex),
new SqlParameter("@RealName",realName),
new SqlParameter("@Address",address),
new SqlParameter("@Phone",phone),
new SqlParameter("@Mail",mail),
new SqlParameter("@UserStateId",userStateId),
new SqlParameter("@IsOnLine",isOnLine),
new SqlParameter("@UserRoleId", userRoleId)
};
int i =SqlHelper.ExecuteNonQuery(SqlHelper.AppConnectionString, CommandType.Text, sql, para);
return i;
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
return 0;
}
}
生成需要的存储过程
----------------------------if Exist,Drop
IF OBJECT_ID(N'[dbo].[usp_InsertUserInfo]') IS NOT NULL
DROP PROCEDURE [dbo].[usp_InsertUserInfo]
IF OBJECT_ID(N'[dbo].[usp_UpdateUserInfo]') IS NOT NULL
DROP PROCEDURE [dbo].[usp_UpdateUserInfo]
IF OBJECT_ID(N'[dbo].[usp_InsertUpdateUserInfo]') IS NOT NULL
DROP PROCEDURE [dbo].[usp_InsertUpdateUserInfo]
IF OBJECT_ID(N'[dbo].[usp_DeleteUserInfo]') IS NOT NULL
DROP PROCEDURE [dbo].[usp_DeleteUserInfo]
IF OBJECT_ID(N'[dbo].[usp_DeleteUserInfosDynamic]') IS NOT NULL
DROP PROCEDURE [dbo].[usp_DeleteUserInfosDynamic]
IF OBJECT_ID(N'[dbo].[usp_SelectUserInfo]') IS NOT NULL
DROP PROCEDURE [dbo].[usp_SelectUserInfo]
IF OBJECT_ID(N'[dbo].[usp_SelectUserInfosDynamic]') IS NOT NULL
DROP PROCEDURE [dbo].[usp_SelectUserInfosDynamic]
IF OBJECT_ID(N'[dbo].[usp_SelectUserInfosAll]') IS NOT NULL
DROP PROCEDURE [dbo].[usp_SelectUserInfosAll]
IF OBJECT_ID(N'[dbo].[usp_SelectUserInfosByUserRoleId]') IS NOT NULL
DROP PROCEDURE [dbo].[usp_SelectUserInfosByUserRoleId]
IF OBJECT_ID(N'[dbo].[usp_SelectUserInfosByUserStateId]') IS NOT NULL
DROP PROCEDURE [dbo].[usp_SelectUserInfosByUserStateId]
IF OBJECT_ID(N'[dbo].[usp_SelectUserInfosByLoginId]') IS NOT NULL
DROP PROCEDURE [dbo].[usp_SelectUserInfosByLoginId]
IF OBJECT_ID(N'[dbo].[usp_SelectUserInfosByMail]') IS NOT NULL
DROP PROCEDURE [dbo].[usp_SelectUserInfosByMail]
IF OBJECT_ID(N'[dbo].[usp_DeleteUserInfosByUserRoleId]') IS NOT NULL
DROP PROCEDURE [dbo].[usp_DeleteUserInfosByUserRoleId]
IF OBJECT_ID(N'[dbo].[usp_DeleteUserInfosByUserStateId]') IS NOT NULL
DROP PROCEDURE [dbo].[usp_DeleteUserInfosByUserStateId]
IF OBJECT_ID(N'[dbo].[usp_DeleteUserInfosByLoginId]') IS NOT NULL
DROP PROCEDURE [dbo].[usp_DeleteUserInfosByLoginId]
IF OBJECT_ID(N'[dbo].[usp_DeleteUserInfosByMail]') IS NOT NULL
DROP PROCEDURE [dbo].[usp_DeleteUserInfosByMail]
GO
------------------------------------------------------------------------------------------------------------------------
-- Generated By: =====Zhao jia dong=====
-- Procedure Name: [dbo].[usp_InsertUserInfo]
-- Date Generated: 2011年4月27日
------------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[usp_InsertUserInfo]
@LoginId nvarchar(50),
@LoginPwd nvarchar(50),
@Sex char(4),
@RealName nvarchar(50),
@Address nvarchar(200),
@Phone nvarchar(100),
@Mail nvarchar(100),
@UserStateId int,
@IsOnLine int,
@UserRoleId int,
@Id int OUTPUT
AS
INSERT INTO [dbo].[UserInfo] (
[LoginId],
[LoginPwd],
[Sex],
[RealName],
[Address],
[Phone],
[Mail],
[UserStateId],
[IsOnLine],
[UserRoleId]
) VALUES (
@LoginId,
@LoginPwd,
@Sex,
@RealName,
@Address,
@Phone,
@Mail,
@UserStateId,
@IsOnLine,
@UserRoleId
)
SET @Id = SCOPE_IDENTITY()
select @Id
GO
------------------------------------------------------------------------------------------------------------------------
-- Generated By: =====Zhao jia dong=====
-- Procedure Name: [dbo].[usp_UpdateUserInfo]
-- Date Generated: 2011年4月27日
------------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[usp_UpdateUserInfo]
@Id int,
@LoginId nvarchar(50),
@LoginPwd nvarchar(50),
@Sex char(4),
@RealName nvarchar(50),
@Address nvarchar(200),
@Phone nvarchar(100),
@Mail nvarchar(100),
@UserStateId int,
@IsOnLine int,
@UserRoleId int
AS
UPDATE [dbo].[UserInfo] SET
[LoginId] = @LoginId,
[LoginPwd] = @LoginPwd,
[Sex] = @Sex,
[RealName] = @RealName,
[Address] = @Address,
[Phone] = @Phone,
[Mail] = @Mail,
[UserStateId] = @UserStateId,
[IsOnLine] = @IsOnLine,
[UserRoleId] = @UserRoleId
WHERE
[Id] = @Id
GO
------------------------------------------------------------------------------------------------------------------------
-- Generated By: =====Zhao jia dong=====
-- Procedure Name: [dbo].[usp_InsertUpdateUserInfo]
-- Date Generated: 2011年4月27日
------------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[usp_InsertUpdateUserInfo]
@Id int,
@LoginId nvarchar(50),
@LoginPwd nvarchar(50),
@Sex char(4),
@RealName nvarchar(50),
@Address nvarchar(200),
@Phone nvarchar(100),
@Mail nvarchar(100),
@UserStateId int,
@IsOnLine int,
@UserRoleId int
AS
IF EXISTS(SELECT [Id] FROM [dbo].[UserInfo] WHERE [Id] = @Id)
BEGIN
UPDATE [dbo].[UserInfo] SET
[LoginId] = @LoginId,
[LoginPwd] = @LoginPwd,
[Sex] = @Sex,
[RealName] = @RealName,
[Address] = @Address,
[Phone] = @Phone,
[Mail] = @Mail,
[UserStateId] = @UserStateId,
[IsOnLine] = @IsOnLine,
[UserRoleId] = @UserRoleId
WHERE
[Id] = @Id
END
ELSE
BEGIN
INSERT INTO [dbo].[UserInfo] (
[Id],
[LoginId],
[LoginPwd],
[Sex],
[RealName],
[Address],
[Phone],
[Mail],
[UserStateId],
[IsOnLine],
[UserRoleId]
) VALUES (
@Id,
@LoginId,
@LoginPwd,
@Sex,
@RealName,
@Address,
@Phone,
@Mail,
@UserStateId,
@IsOnLine,
@UserRoleId
)
END
GO
------------------------------------------------------------------------------------------------------------------------
-- Generated By: =====Zhao jia dong=====
-- Procedure Name: [dbo].[usp_DeleteUserInfo]
-- Date Generated: 2011年4月27日
------------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[usp_DeleteUserInfo]
@Id int
AS
DELETE FROM [dbo].[UserInfo]
WHERE
[Id] = @Id
GO
------------------------------------------------------------------------------------------------------------------------
-- Generated By: =====Zhao jia dong=====
-- Procedure Name: [dbo].[usp_DeleteUserInfosByUserRoleId]
-- Date Generated: 2011年4月27日
------------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[usp_DeleteUserInfosByUserRoleId]
@UserRoleId int
AS
DELETE FROM [dbo].[UserInfo]
WHERE
[UserRoleId] = @UserRoleId
GO
------------------------------------------------------------------------------------------------------------------------
-- Generated By: =====Zhao jia dong=====
-- Procedure Name: [dbo].[usp_DeleteUserInfosByUserStateId]
-- Date Generated: 2011年4月27日
------------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[usp_DeleteUserInfosByUserStateId]
@UserStateId int
AS
DELETE FROM [dbo].[UserInfo]
WHERE
[UserStateId] = @UserStateId
GO
------------------------------------------------------------------------------------------------------------------------
-- Generated By: =====Zhao jia dong=====
-- Procedure Name: [dbo].[usp_DeleteUserInfosByLoginId]
-- Date Generated: 2011年4月27日
------------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[usp_DeleteUserInfosByLoginId]
@LoginId nvarchar(50)
AS
DELETE FROM [dbo].[UserInfo]
WHERE
[LoginId] = @LoginId
GO
------------------------------------------------------------------------------------------------------------------------
-- Generated By: =====Zhao jia dong=====
-- Procedure Name: [dbo].[usp_DeleteUserInfosByMail]
-- Date Generated: 2011年4月27日
------------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[usp_DeleteUserInfosByMail]
@Mail nvarchar(100)
AS
DELETE FROM [dbo].[UserInfo]
WHERE
[Mail] = @Mail
GO
------------------------------------------------------------------------------------------------------------------------
-- Generated By: =====Zhao jia dong=====
-- Procedure Name: [dbo].[usp_DeleteUserInfosDynamic]
-- Date Generated: 2011年4月27日
------------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[usp_DeleteUserInfosDynamic]
@WhereCondition nvarchar(500)
AS
DECLARE @SQL nvarchar(3250)
SET @SQL = '
DELETE FROM
[dbo].[UserInfo]
WHERE
' + @WhereCondition
EXEC sp_executesql @SQL
GO
------------------------------------------------------------------------------------------------------------------------
-- Generated By: =====Zhao jia dong=====
-- Procedure Name: [dbo].[usp_SelectUserInfo]
-- Date Generated: 2011年4月27日
------------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[usp_SelectUserInfo]
@Id int
AS
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT
[Id],
[LoginId],
[LoginPwd],
[Sex],
[RealName],
[Address],
[Phone],
[Mail],
[UserStateId],
[IsOnLine],
[UserRoleId]
FROM
[dbo].[UserInfo]
WHERE
[Id] = @Id
GO
------------------------------------------------------------------------------------------------------------------------
-- Generated By: =====Zhao jia dong=====
-- Procedure Name: [dbo].[usp_SelectUserInfosByUserRoleId]
-- Date Generated: 2011年4月27日
------------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[usp_SelectUserInfosByUserRoleId]
@UserRoleId int
AS
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT
[Id],
[LoginId],
[LoginPwd],
[Sex],
[RealName],
[Address],
[Phone],
[Mail],
[UserStateId],
[IsOnLine],
[UserRoleId]
FROM
[dbo].[UserInfo]
WHERE
[UserRoleId] = @UserRoleId
GO
------------------------------------------------------------------------------------------------------------------------
-- Generated By: =====Zhao jia dong=====
-- Procedure Name: [dbo].[usp_SelectUserInfosByUserStateId]
-- Date Generated: 2011年4月27日
------------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[usp_SelectUserInfosByUserStateId]
@UserStateId int
AS
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT
[Id],
[LoginId],
[LoginPwd],
[Sex],
[RealName],
[Address],
[Phone],
[Mail],
[UserStateId],
[IsOnLine],
[UserRoleId]
FROM
[dbo].[UserInfo]
WHERE
[UserStateId] = @UserStateId
GO
------------------------------------------------------------------------------------------------------------------------
-- Generated By: =====Zhao jia dong=====
-- Procedure Name: [dbo].[usp_SelectUserInfosByLoginId]
-- Date Generated: 2011年4月27日
------------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[usp_SelectUserInfosByLoginId]
@LoginId nvarchar(50)
AS
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT
[Id],
[LoginId],
[LoginPwd],
[Sex],
[RealName],
[Address],
[Phone],
[Mail],
[UserStateId],
[IsOnLine],
[UserRoleId]
FROM
[dbo].[UserInfo]
WHERE
[LoginId] = @LoginId
GO
------------------------------------------------------------------------------------------------------------------------
-- Generated By: =====Zhao jia dong=====
-- Procedure Name: [dbo].[usp_SelectUserInfosByMail]
-- Date Generated: 2011年4月27日
------------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[usp_SelectUserInfosByMail]
@Mail nvarchar(100)
AS
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT
[Id],
[LoginId],
[LoginPwd],
[Sex],
[RealName],
[Address],
[Phone],
[Mail],
[UserStateId],
[IsOnLine],
[UserRoleId]
FROM
[dbo].[UserInfo]
WHERE
[Mail] = @Mail
GO
------------------------------------------------------------------------------------------------------------------------
-- Generated By: =====Zhao jia dong=====
-- Procedure Name: [dbo].[usp_SelectUserInfosDynamic]
-- Date Generated: 2011年4月27日
------------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[usp_SelectUserInfosDynamic]
@WhereCondition nvarchar(500),
@OrderByExpression nvarchar(250) = NULL
AS
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
DECLARE @SQL nvarchar(3250)
SET @SQL = '
SELECT
[Id],
[LoginId],
[LoginPwd],
[Sex],
[RealName],
[Address],
[Phone],
[Mail],
[UserStateId],
[IsOnLine],
[UserRoleId]
FROM
[dbo].[UserInfo]
WHERE
' + @WhereCondition
IF @OrderByExpression IS NOT NULL AND LEN(@OrderByExpression) > 0
BEGIN
SET @SQL = @SQL + '
ORDER BY
' + @OrderByExpression
END
EXEC sp_executesql @SQL
GO
------------------------------------------------------------------------------------------------------------------------
-- Generated By: =====Zhao jia dong=====
-- Procedure Name: [dbo].[usp_SelectUserInfosAll]
-- Date Generated: 2011年4月27日
------------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[usp_SelectUserInfosAll]
AS
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT
[Id],
[LoginId],
[LoginPwd],
[Sex],
[RealName],
[Address],
[Phone],
[Mail],
[UserStateId],
[IsOnLine],
[UserRoleId]
FROM
[dbo].[UserInfo]
GO
------------------------------------------------------------------------------------------------------------------------
-- Generated By: =====Zhao jia dong=====
-- Procedure Name: [dbo].[usp_SelectUserInfosPaged]
-- Date Generated: 2011年4月27日
------------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[usp_SelectUserInfosPaged]
AS
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT
[Id],
[LoginId],
[LoginPwd],
[Sex],
[RealName],
[Address],
[Phone],
[Mail],
[UserStateId],
[IsOnLine],
[UserRoleId]
FROM
[dbo].[UserInfo]
GO