以前自己设计过一个project。当时还不知道有EF框架类型设计。但无形中却用到了此中框架概念。project用到的平台为windowxp(其它可适用windows2000,windows2003,windows7)sql server2000(其它可适用2005,2008,2012),powder designer,.NET Framework2.0(其它可适用3.5,4.0),微软Enterprise Library(经过自己改造,否则不能满足我自己的需要)。
此项目用到了一个生成Biz Class的工具,可以根据数据库自动生成Biz Class,当然这个时候还不知道CodeSmith,若知道可以省力不少啊。解决方式就是单独编写一套生成文件的应用程序,通过读取数据库中表结构信息,生成Biz Class文件。Biz Class中包含对当前业务的获取,新增,修改,删除,导出xml等功能。
其中获取,新增,修改,删除都是调用存储过程。存储过程也是自动生成。此处是通过存储过程生成存储过程,稍微繁琐些,就是需要一个一个表单独处理。(其实可以通过其他方式取出来放到单独的文件中执行)因为是在存储过程中print出来的,还需要手动执行下语句。
贴一下Biz Class源码:
/******************************************************************
** 文件名:xxxxx.cs
** Copyright (c) 上XXXXX公司
** 创建人:XXX
** 日 期:XXX
** 修改人:
** 日 期:
** 描 述:
**
** 版 本:V1.0
**----------------------------------------------------------------
******************************************************************/
//=================================================================
using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Xml;
using System.Web;
using System.Data.Common;
using System.Xml.Serialization;
using System.Runtime.Serialization;
using System.Runtime.Serialization.Formatters.Binary;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
namespace Platform.CustomerServer
{
/// <summary>
/// DepartmentInformation
///
/// </summary>
[Serializable]
[XmlRoot(ElementName="DepartmentInformation")]
public class DepartmentInformation
{
#region Define Private Variable
#region Define DBOperate Varuable
private Database db;
private DbCommand dbCommand;
#endregion
#region OldDepartmentID
/// <summary>
/// DepartmentID
///
/// </summary>
private string _OldDepartmentID;
#endregion
#region NewDepartmentID
/// <summary>
/// DepartmentID
///
/// </summary>
private string _NewDepartmentID;
#endregion
#region DepartmentID
/// <summary>
/// DepartmentID
///
/// </summary>
private string _DepartmentID;
#endregion
#region DepartmentAbbr
/// <summary>
/// DepartmentAbbr
///
/// </summary>
private string _DepartmentAbbr;
#endregion
#region DepartmentName
/// <summary>
/// DepartmentName
///
/// </summary>
private string _DepartmentName;
#endregion
#region DepartmentCancelFlag
/// <summary>
/// DepartmentCancelFlag
///
/// </summary>
private string _DepartmentCancelFlag;
#endregion
#region DepartmentRole
/// <summary>
/// DepartmentRole
///
/// </summary>
private string _DepartmentRole;
#endregion
#region CreateUser
/// <summary>
/// CreateUser
///
/// </summary>
private string _CreateUser;
#endregion
#region CreateDate
/// <summary>
/// CreateDate
///
/// </summary>
private DateTime _CreateDate;
#endregion
#region UpdateUser
/// <summary>
/// UpdateUser
///
/// </summary>
private string _UpdateUser;
#endregion
#region UpdateDate
/// <summary>
/// UpdateDate
///
/// </summary>
private DateTime _UpdateDate;
#endregion
#region DeptTimestamp
/// <summary>
/// DeptTimestamp
///
/// </summary>
private byte[] _DeptTimestamp;
#endregion
#endregion
#region Define public Properties - All contain Get/Set Statements
#region OldDepartmentID
/// <summary>
/// OldDepartmentID
///
/// </summary>
[XmlElement(ElementName="OldDepartmentID")]
public string OldDepartmentID
{
get
{
return _OldDepartmentID;
}
set
{
_OldDepartmentID = value;
}
}
#endregion
#region NewDepartmentID
/// <summary>
/// NewDepartmentID
///
/// </summary>
[XmlElement(ElementName="NewDepartmentID")]
public string NewDepartmentID
{
get
{
return _NewDepartmentID;
}
set
{
_NewDepartmentID = value;
}
}
#endregion
#region DepartmentID
/// <summary>
/// DepartmentID
///
/// </summary>
[XmlElement(ElementName="DepartmentID")]
public string DepartmentID
{
get
{
return _DepartmentID;
}
set
{
_DepartmentID = value;
}
}
#endregion
#region DepartmentAbbr
/// <summary>
/// DepartmentAbbr
///
/// </summary>
[XmlElement(ElementName="DepartmentAbbr")]
public string DepartmentAbbr
{
get
{
return _DepartmentAbbr;
}
set
{
_DepartmentAbbr = value;
}
}
#endregion
#region DepartmentName
/// <summary>
/// DepartmentName
///
/// </summary>
[XmlElement(ElementName="DepartmentName")]
public string DepartmentName
{
get
{
return _DepartmentName;
}
set
{
_DepartmentName = value;
}
}
#endregion
#region DepartmentCancelFlag
/// <summary>
/// DepartmentCancelFlag
///
/// </summary>
[XmlElement(ElementName="DepartmentCancelFlag")]
public string DepartmentCancelFlag
{
get
{
return _DepartmentCancelFlag;
}
set
{
_DepartmentCancelFlag = value;
}
}
#endregion
#region DepartmentRole
/// <summary>
/// DepartmentRole
///
/// </summary>
[XmlElement(ElementName="DepartmentRole")]
public string DepartmentRole
{
get
{
return _DepartmentRole;
}
set
{
_DepartmentRole = value;
}
}
#endregion
#region CreateUser
/// <summary>
/// CreateUser
///
/// </summary>
[XmlElement(ElementName="CreateUser")]
public string CreateUser
{
get
{
return _CreateUser;
}
set
{
_CreateUser = value;
}
}
#endregion
#region CreateDate
/// <summary>
/// CreateDate
///
/// </summary>
[XmlElement(ElementName="CreateDate")]
public DateTime CreateDate
{
get
{
return _CreateDate;
}
set
{
_CreateDate = value;
}
}
#endregion
#region UpdateUser
/// <summary>
/// UpdateUser
///
/// </summary>
[XmlElement(ElementName="UpdateUser")]
public string UpdateUser
{
get
{
return _UpdateUser;
}
set
{
_UpdateUser = value;
}
}
#endregion
#region UpdateDate
/// <summary>
/// UpdateDate
///
/// </summary>
[XmlElement(ElementName="UpdateDate")]
public DateTime UpdateDate
{
get
{
return _UpdateDate;
}
set
{
_UpdateDate = value;
}
}
#endregion
#region DeptTimestamp
/// <summary>
/// DeptTimestamp
///
/// </summary>
[XmlElement(ElementName="DeptTimestamp")]
public byte[] DeptTimestamp
{
get
{
return _DeptTimestamp;
}
set
{
_DeptTimestamp = value;
}
}
#endregion
#endregion
#region Define InsertFunction
/// <summary>
/// InsertFunction
/// 添加函数
/// </summary>
public int InsertFunction()
{
string sqlCommand = "SP_InsertDepartmentInformation";
// Create the Database object, using the default database service. The
// default database service is determined through configuration.
db = DatabaseFactory.CreateDatabase() as SqlDatabase;
dbCommand = db.GetStoredProcCommand(sqlCommand) as SqlCommand;
// Retrieve data from the specified category.
if((object)DepartmentID != null)
{
if(!DepartmentID.Equals(""))
{
db.AddInParameter(dbCommand, "@DepartmentID", DbType.String, 20, DepartmentID);
}
else
{
db.AddInParameter(dbCommand, "@DepartmentID",DbType.String, 20,DBNull.Value);
}
}
else
{
db.AddInParameter(dbCommand, "@DepartmentID",DbType.String, 20,DBNull.Value);
}
if((object)DepartmentAbbr != null)
{
if(!DepartmentAbbr.Equals(""))
{
db.AddInParameter(dbCommand, "@DepartmentAbbr", DbType.String, 20, DepartmentAbbr);
}
else
{
db.AddInParameter(dbCommand, "@DepartmentAbbr",DbType.String, 20,DBNull.Value);
}
}
else
{
db.AddInParameter(dbCommand, "@DepartmentAbbr",DbType.String, 20,DBNull.Value);
}
if((object)DepartmentName != null)
{
if(!DepartmentName.Equals(""))
{
db.AddInParameter(dbCommand, "@DepartmentName", DbType.String, 60, DepartmentName);
}
else
{
db.AddInParameter(dbCommand, "@DepartmentName",DbType.String, 60,DBNull.Value);
}
}
else
{
db.AddInParameter(dbCommand, "@DepartmentName",DbType.String, 60,DBNull.Value);
}
if((object)DepartmentCancelFlag != null)
{
if(!DepartmentCancelFlag.Equals(""))
{
db.AddInParameter(dbCommand, "@DepartmentCancelFlag", DbType.String, 1, DepartmentCancelFlag);
}
else
{
db.AddInParameter(dbCommand, "@DepartmentCancelFlag",DbType.String, 1,DBNull.Value);
}
}
else
{
db.AddInParameter(dbCommand, "@DepartmentCancelFlag",DbType.String, 1,DBNull.Value);
}
if((object)DepartmentRole != null)
{
if(!DepartmentRole.Equals(""))
{
db.AddInParameter(dbCommand, "@DepartmentRole", DbType.String, 20, DepartmentRole);
}
else
{
db.AddInParameter(dbCommand, "@DepartmentRole",DbType.String, 20,DBNull.Value);
}
}
else
{
db.AddInParameter(dbCommand, "@DepartmentRole",DbType.String, 20,DBNull.Value);
}
if((object)CreateUser != null)
{
if(!CreateUser.Equals(""))
{
db.AddInParameter(dbCommand, "@CreateUser", DbType.String, 20, CreateUser);
}
else
{
db.AddInParameter(dbCommand, "@CreateUser",DbType.String, 20,DBNull.Value);
}
}
else
{
db.AddInParameter(dbCommand, "@CreateUser",DbType.String, 20,DBNull.Value);
}
if((object)CreateDate != null)
{
if(CreateDate.Year>=1900)
{
db.AddInParameter(dbCommand, "@CreateDate", DbType.DateTime, 8, CreateDate);
}
else
{
db.AddInParameter(dbCommand, "@CreateDate",DbType.DateTime, 8,DBNull.Value);
}
}
else
{
db.AddInParameter(dbCommand, "@CreateDate",DbType.DateTime, 8,DBNull.Value);
}
if((object)UpdateUser != null)
{
if(!UpdateUser.Equals(""))
{
db.AddInParameter(dbCommand, "@UpdateUser", DbType.String, 20, UpdateUser);
}
else
{
db.AddInParameter(dbCommand, "@UpdateUser",DbType.String, 20,DBNull.Value);
}
}
else
{
db.AddInParameter(dbCommand, "@UpdateUser",DbType.String, 20,DBNull.Value);
}
if((object)UpdateDate != null)
{
if(UpdateDate.Year>=1900)
{
db.AddInParameter(dbCommand, "@UpdateDate", DbType.DateTime, 8, UpdateDate);
}
else
{
db.AddInParameter(dbCommand, "@UpdateDate",DbType.DateTime, 8,DBNull.Value);
}
}
else
{
db.AddInParameter(dbCommand, "@UpdateDate",DbType.DateTime, 8,DBNull.Value);
}
if((object)DeptTimestamp != null)
{
db.AddInParameter(dbCommand, "@DeptTimestamp", DbType.Binary, 8, DeptTimestamp);
}
else
{
db.AddInParameter(dbCommand, "@DeptTimestamp",DbType.Binary, 8,DBNull.Value);
}
return db.ExecuteNonQuery( dbCommand );
}
#endregion
#region Define InsertFunction
/// <summary>
/// InsertFunction
/// 添加函数
/// </summary>
public int InsertFunction( Database dba, DbTransaction transaction )
{
string sqlCommand = "SP_InsertDepartmentInformation";
// Create the Database object, using the default database service. The
// default database service is determined through configuration.
dbCommand = dba.GetStoredProcCommand(sqlCommand) as SqlCommand;
// Retrieve data from the specified category.
if((object)DepartmentID != null)
{
if(!DepartmentID.Equals(""))
{
dba.AddInParameter(dbCommand, "@DepartmentID", DbType.String, 20, DepartmentID);
}
else
{
dba.AddInParameter(dbCommand, "@DepartmentID",DbType.String, 20,DBNull.Value);
}
}
else
{
dba.AddInParameter(dbCommand, "@DepartmentID",DbType.String, 20,DBNull.Value);
}
if((object)DepartmentAbbr != null)
{
if(!DepartmentAbbr.Equals(""))
{
dba.AddInParameter(dbCommand, "@DepartmentAbbr", DbType.String, 20, DepartmentAbbr);
}
else
{
dba.AddInParameter(dbCommand, "@DepartmentAbbr",DbType.String, 20,DBNull.Value);
}
}
else
{
dba.AddInParameter(dbCommand, "@DepartmentAbbr",DbType.String, 20,DBNull.Value);
}
if((object)DepartmentName != null)
{
if(!DepartmentName.Equals(""))
{
dba.AddInParameter(dbCommand, "@DepartmentName", DbType.String, 60, DepartmentName);
}
else
{
dba.AddInParameter(dbCommand, "@DepartmentName",DbType.String, 60,DBNull.Value);
}
}
else
{
dba.AddInParameter(dbCommand, "@DepartmentName",DbType.String, 60,DBNull.Value);
}
if((object)DepartmentCancelFlag != null)
{
if(!DepartmentCancelFlag.Equals(""))
{
dba.AddInParameter(dbCommand, "@DepartmentCancelFlag", DbType.String, 1, DepartmentCancelFlag);
}
else
{
dba.AddInParameter(dbCommand, "@DepartmentCancelFlag",DbType.String, 1,DBNull.Value);
}
}
else
{
dba.AddInParameter(dbCommand, "@DepartmentCancelFlag",DbType.String, 1,DBNull.Value);
}
if((object)DepartmentRole != null)
{
if(!DepartmentRole.Equals(""))
{
dba.AddInParameter(dbCommand, "@DepartmentRole", DbType.String, 20, DepartmentRole);
}
else
{
dba.AddInParameter(dbCommand, "@DepartmentRole",DbType.String, 20,DBNull.Value);
}
}
else
{
dba.AddInParameter(dbCommand, "@DepartmentRole",DbType.String, 20,DBNull.Value);
}
if((object)CreateUser != null)
{
if(!CreateUser.Equals(""))
{
dba.AddInParameter(dbCommand, "@CreateUser", DbType.String, 20, CreateUser);
}
else
{
dba.AddInParameter(dbCommand, "@CreateUser",DbType.String, 20,DBNull.Value);
}
}
else
{
dba.AddInParameter(dbCommand, "@CreateUser",DbType.String, 20,DBNull.Value);
}
if((object)CreateDate != null)
{
if(CreateDate.Year>=1900)
{
dba.AddInParameter(dbCommand, "@CreateDate", DbType.DateTime, 8, CreateDate);
}
else
{
dba.AddInParameter(dbCommand, "@CreateDate",DbType.DateTime, 8,DBNull.Value);
}
}
else
{
dba.AddInParameter(dbCommand, "@CreateDate",DbType.DateTime, 8,DBNull.Value);
}
if((object)UpdateUser != null)
{
if(!UpdateUser.Equals(""))
{
dba.AddInParameter(dbCommand, "@UpdateUser", DbType.String, 20, UpdateUser);
}
else
{
dba.AddInParameter(dbCommand, "@UpdateUser",DbType.String, 20,DBNull.Value);
}
}
else
{
dba.AddInParameter(dbCommand, "@UpdateUser",DbType.String, 20,DBNull.Value);
}
if((object)UpdateDate != null)
{
if(UpdateDate.Year>=1900)
{
dba.AddInParameter(dbCommand, "@UpdateDate", DbType.DateTime, 8, UpdateDate);
}
else
{
dba.AddInParameter(dbCommand, "@UpdateDate",DbType.DateTime, 8,DBNull.Value);
}
}
else
{
dba.AddInParameter(dbCommand, "@UpdateDate",DbType.DateTime, 8,DBNull.Value);
}
if((object)DeptTimestamp != null)
{
dba.AddInParameter(dbCommand, "@DeptTimestamp", DbType.Binary, 8, DeptTimestamp);
}
else
{
dba.AddInParameter(dbCommand, "@DeptTimestamp",DbType.Binary, 8,DBNull.Value);
}
return dba.ExecuteNonQuery( dbCommand, transaction );
}
#endregion
#region Define UpdateFunction
/// <summary>
/// UpdateFunction
/// 更新函数
/// </summary>
/// <param name="SqlQuery">条件表达式(形式为 and ...)</param>
public int UpdateFunction( )
{
string sqlCommand = "SP_UpdateDepartmentInformation";
// Create the Database object, using the default database service. The
// default database service is determined through configuration.
db = DatabaseFactory.CreateDatabase() as SqlDatabase;
dbCommand = db.GetStoredProcCommand(sqlCommand) as SqlCommand;
// Retrieve data from the specified category.
if((object)OldDepartmentID != null)
{
if(!OldDepartmentID.Equals(""))
{
db.AddInParameter(dbCommand, "@OldDepartmentID", DbType.String, 20, OldDepartmentID);
}
else
{
db.AddInParameter(dbCommand, "@OldDepartmentID",DbType.String, 20,DBNull.Value);
}
}
else
{
db.AddInParameter(dbCommand, "@OldDepartmentID",DbType.String, 20,DBNull.Value);
}
if((object)NewDepartmentID != null)
{
if(!NewDepartmentID.Equals(""))
{
db.AddInParameter(dbCommand, "@NewDepartmentID", DbType.String, 20, NewDepartmentID);
}
else
{
db.AddInParameter(dbCommand, "@NewDepartmentID",DbType.String, 20,DBNull.Value);
}
}
else
{
db.AddInParameter(dbCommand, "@NewDepartmentID",DbType.String, 20,DBNull.Value);
}
if((object)DepartmentAbbr != null)
{
if(!DepartmentAbbr.Equals(""))
{
db.AddInParameter(dbCommand, "@DepartmentAbbr", DbType.String, 20, DepartmentAbbr);
}
else
{
db.AddInParameter(dbCommand, "@DepartmentAbbr",DbType.String, 20,DBNull.Value);
}
}
else
{
db.AddInParameter(dbCommand, "@DepartmentAbbr",DbType.String, 20,DBNull.Value);
}
if((object)DepartmentName != null)
{
if(!DepartmentName.Equals(""))
{
db.AddInParameter(dbCommand, "@DepartmentName", DbType.String, 60, DepartmentName);
}
else
{
db.AddInParameter(dbCommand, "@DepartmentName",DbType.String, 60,DBNull.Value);
}
}
else
{
db.AddInParameter(dbCommand, "@DepartmentName",DbType.String, 60,DBNull.Value);
}
if((object)DepartmentCancelFlag != null)
{
if(!DepartmentCancelFlag.Equals(""))
{
db.AddInParameter(dbCommand, "@DepartmentCancelFlag", DbType.String, 1, DepartmentCancelFlag);
}
else
{
db.AddInParameter(dbCommand, "@DepartmentCancelFlag",DbType.String, 1,DBNull.Value);
}
}
else
{
db.AddInParameter(dbCommand, "@DepartmentCancelFlag",DbType.String, 1,DBNull.Value);
}
if((object)DepartmentRole != null)
{
if(!DepartmentRole.Equals(""))
{
db.AddInParameter(dbCommand, "@DepartmentRole", DbType.String, 20, DepartmentRole);
}
else
{
db.AddInParameter(dbCommand, "@DepartmentRole",DbType.String, 20,DBNull.Value);
}
}
else
{
db.AddInParameter(dbCommand, "@DepartmentRole",DbType.String, 20,DBNull.Value);
}
if((object)CreateUser != null)
{
if(!CreateUser.Equals(""))
{
db.AddInParameter(dbCommand, "@CreateUser", DbType.String, 20, CreateUser);
}
else
{
db.AddInParameter(dbCommand, "@CreateUser",DbType.String, 20,DBNull.Value);
}
}
else
{
db.AddInParameter(dbCommand, "@CreateUser",DbType.String, 20,DBNull.Value);
}
if((object)CreateDate != null)
{
if(CreateDate.Year>=1900)
{
db.AddInParameter(dbCommand, "@CreateDate", DbType.DateTime, 8, CreateDate);
}
else
{
db.AddInParameter(dbCommand, "@CreateDate",DbType.DateTime, 8,DBNull.Value);
}
}
else
{
db.AddInParameter(dbCommand, "@CreateDate",DbType.DateTime, 8,DBNull.Value);
}
if((object)UpdateUser != null)
{
if(!UpdateUser.Equals(""))
{
db.AddInParameter(dbCommand, "@UpdateUser", DbType.String, 20, UpdateUser);
}
else
{
db.AddInParameter(dbCommand, "@UpdateUser",DbType.String, 20,DBNull.Value);
}
}
else
{
db.AddInParameter(dbCommand, "@UpdateUser",DbType.String, 20,DBNull.Value);
}
if((object)UpdateDate != null)
{
if(UpdateDate.Year>=1900)
{
db.AddInParameter(dbCommand, "@UpdateDate", DbType.DateTime, 8, UpdateDate);
}
else
{
db.AddInParameter(dbCommand, "@UpdateDate",DbType.DateTime, 8,DBNull.Value);
}
}
else
{
db.AddInParameter(dbCommand, "@UpdateDate",DbType.DateTime, 8,DBNull.Value);
}
if((object)DeptTimestamp != null)
{
db.AddInParameter(dbCommand, "@DeptTimestamp", DbType.Binary, 8, DeptTimestamp);
}
else
{
db.AddInParameter(dbCommand, "@DeptTimestamp",DbType.Binary, 8,DBNull.Value);
}
return db.ExecuteNonQuery( dbCommand );
}
#endregion
#region Define UpdateFunction
/// <summary>
/// UpdateFunction
/// 更新函数
/// </summary>
/// <param name="SqlQuery">条件表达式(形式为 and ...)</param>
public int UpdateFunction( Database dba, DbTransaction transaction )
{
string sqlCommand = "SP_UpdateDepartmentInformation";
// Create the Database object, using the default database service. The
// default database service is determined through configuration.
dbCommand = dba.GetStoredProcCommand(sqlCommand) as SqlCommand;
// Retrieve data from the specified category.
if((object)OldDepartmentID != null)
{
if(!OldDepartmentID.Equals(""))
{
dba.AddInParameter(dbCommand, "@OldDepartmentID", DbType.String, 20, OldDepartmentID);
}
else
{
dba.AddInParameter(dbCommand, "@OldDepartmentID",DbType.String, 20,DBNull.Value);
}
}
else
{
dba.AddInParameter(dbCommand, "@OldDepartmentID",DbType.String, 20,DBNull.Value);
}
if((object)NewDepartmentID != null)
{
if(!NewDepartmentID.Equals(""))
{
dba.AddInParameter(dbCommand, "@NewDepartmentID", DbType.String, 20, NewDepartmentID);
}
else
{
dba.AddInParameter(dbCommand, "@NewDepartmentID",DbType.String, 20,DBNull.Value);
}
}
else
{
dba.AddInParameter(dbCommand, "@NewDepartmentID",DbType.String, 20,DBNull.Value);
}
if((object)DepartmentAbbr != null)
{
if(!DepartmentAbbr.Equals(""))
{
dba.AddInParameter(dbCommand, "@DepartmentAbbr", DbType.String, 20, DepartmentAbbr);
}
else
{
dba.AddInParameter(dbCommand, "@DepartmentAbbr",DbType.String, 20,DBNull.Value);
}
}
else
{
dba.AddInParameter(dbCommand, "@DepartmentAbbr",DbType.String, 20,DBNull.Value);
}
if((object)DepartmentName != null)
{
if(!DepartmentName.Equals(""))
{
dba.AddInParameter(dbCommand, "@DepartmentName", DbType.String, 60, DepartmentName);
}
else
{
dba.AddInParameter(dbCommand, "@DepartmentName",DbType.String, 60,DBNull.Value);
}
}
else
{
dba.AddInParameter(dbCommand, "@DepartmentName",DbType.String, 60,DBNull.Value);
}
if((object)DepartmentCancelFlag != null)
{
if(!DepartmentCancelFlag.Equals(""))
{
dba.AddInParameter(dbCommand, "@DepartmentCancelFlag", DbType.String, 1, DepartmentCancelFlag);
}
else
{
dba.AddInParameter(dbCommand, "@DepartmentCancelFlag",DbType.String, 1,DBNull.Value);
}
}
else
{
dba.AddInParameter(dbCommand, "@DepartmentCancelFlag",DbType.String, 1,DBNull.Value);
}
if((object)DepartmentRole != null)
{
if(!DepartmentRole.Equals(""))
{
dba.AddInParameter(dbCommand, "@DepartmentRole", DbType.String, 20, DepartmentRole);
}
else
{
dba.AddInParameter(dbCommand, "@DepartmentRole",DbType.String, 20,DBNull.Value);
}
}
else
{
dba.AddInParameter(dbCommand, "@DepartmentRole",DbType.String, 20,DBNull.Value);
}
if((object)CreateUser != null)
{
if(!CreateUser.Equals(""))
{
dba.AddInParameter(dbCommand, "@CreateUser", DbType.String, 20, CreateUser);
}
else
{
dba.AddInParameter(dbCommand, "@CreateUser",DbType.String, 20,DBNull.Value);
}
}
else
{
dba.AddInParameter(dbCommand, "@CreateUser",DbType.String, 20,DBNull.Value);
}
if((object)CreateDate != null)
{
if(CreateDate.Year>=1900)
{
dba.AddInParameter(dbCommand, "@CreateDate", DbType.DateTime, 8, CreateDate);
}
else
{
dba.AddInParameter(dbCommand, "@CreateDate",DbType.DateTime, 8,DBNull.Value);
}
}
else
{
dba.AddInParameter(dbCommand, "@CreateDate",DbType.DateTime, 8,DBNull.Value);
}
if((object)UpdateUser != null)
{
if(!UpdateUser.Equals(""))
{
dba.AddInParameter(dbCommand, "@UpdateUser", DbType.String, 20, UpdateUser);
}
else
{
dba.AddInParameter(dbCommand, "@UpdateUser",DbType.String, 20,DBNull.Value);
}
}
else
{
dba.AddInParameter(dbCommand, "@UpdateUser",DbType.String, 20,DBNull.Value);
}
if((object)UpdateDate != null)
{
if(UpdateDate.Year>=1900)
{
dba.AddInParameter(dbCommand, "@UpdateDate", DbType.DateTime, 8, UpdateDate);
}
else
{
dba.AddInParameter(dbCommand, "@UpdateDate",DbType.DateTime, 8,DBNull.Value);
}
}
else
{
dba.AddInParameter(dbCommand, "@UpdateDate",DbType.DateTime, 8,DBNull.Value);
}
if((object)DeptTimestamp != null)
{
dba.AddInParameter(dbCommand, "@DeptTimestamp", DbType.Binary, 8, DeptTimestamp);
}
else
{
dba.AddInParameter(dbCommand, "@DeptTimestamp",DbType.Binary, 8,DBNull.Value);
}
return dba.ExecuteNonQuery( dbCommand, transaction );
}
#endregion
#region Define DeleteFunction
/// <summary>
/// DeleteFunction
/// 删除函数
/// </summary>
/// <param name="SqlQuery">条件表达式(形式为 and ...)</param>
public int DeleteFunction( )
{
string sqlCommand = "SP_DeleteDepartmentInformation";
// Create the Database object, using the default database service. The
// default database service is determined through configuration.
db = DatabaseFactory.CreateDatabase() as SqlDatabase;
dbCommand = db.GetStoredProcCommand(sqlCommand) as SqlCommand;
// Retrieve data from the specified category.
if((object)DepartmentID != null)
{
db.AddInParameter(dbCommand, "@DepartmentID",DbType.String, 20,DepartmentID);
}
else
{
db.AddInParameter(dbCommand, "@DepartmentID",DbType.String, 20,DBNull.Value);
}
return db.ExecuteNonQuery( dbCommand );
}
#endregion
#region Define DeleteFunction
/// <summary>
/// DeleteFunction
/// 删除函数
/// </summary>
/// <param name="SqlQuery">条件表达式(形式为 and ...)</param>
public int DeleteFunction( Database dba, DbTransaction transaction )
{
string sqlCommand = "SP_DeleteDepartmentInformation";
// Create the Database object, using the default database service. The
// default database service is determined through configuration.
dbCommand = dba.GetStoredProcCommand(sqlCommand) as SqlCommand;
// Retrieve data from the specified category.
if((object)DepartmentID != null)
{
dba.AddInParameter(dbCommand, "@DepartmentID",DbType.String, 20,DepartmentID);
}
else
{
dba.AddInParameter(dbCommand, "@DepartmentID",DbType.String, 20,DBNull.Value);
}
return dba.ExecuteNonQuery( dbCommand, transaction );
}
#endregion
#region Define SelectFunction
/// <summary>
/// SelectFunction
/// Get Single Record
/// </summary>
/// <param name="SqlQuery">查询条件</param>
/// <returns>bool 是否取得该记录</returns>
public bool SelectFunction( string SqlQuery )
{
// Create the Database object, using the default database service. The
// default database service is determined through configuration.
db = DatabaseFactory.CreateDatabase();
dbCommand = db.GetSqlStringCommand(SqlQuery);
DataTable dataTable;
dataTable = db.ExecuteDataSet(dbCommand).Tables[0];
if(dataTable.Rows.Count > 0)
{
this.OldDepartmentID = (string)Convert.ChangeType(dataTable.Rows[0]["DepartmentID"],typeof(string));
this.DepartmentID = (string)Convert.ChangeType(dataTable.Rows[0]["DepartmentID"],typeof(string));
this.DepartmentAbbr = (string)Convert.ChangeType(dataTable.Rows[0]["DepartmentAbbr"],typeof(string));
this.DepartmentName = (string)Convert.ChangeType(dataTable.Rows[0]["DepartmentName"],typeof(string));
this.DepartmentCancelFlag = (string)Convert.ChangeType(dataTable.Rows[0]["DepartmentCancelFlag"],typeof(string));
this.DepartmentRole = (string)Convert.ChangeType(dataTable.Rows[0]["DepartmentRole"],typeof(string));
this.CreateUser = (string)Convert.ChangeType(dataTable.Rows[0]["CreateUser"],typeof(string));
if(dataTable.Rows[0]["CreateDate"] == DBNull.Value )
{
string strDateTime = "1753/1/1";
this.CreateDate = DateTime.Parse( strDateTime );
}
else
{
this.CreateDate = (DateTime)Convert.ChangeType(dataTable.Rows[0]["CreateDate"],typeof(DateTime));
}
this.UpdateUser = (string)Convert.ChangeType(dataTable.Rows[0]["UpdateUser"],typeof(string));
if(dataTable.Rows[0]["UpdateDate"] == DBNull.Value )
{
string strDateTime = "1753/1/1";
this.UpdateDate = DateTime.Parse( strDateTime );
}
else
{
this.UpdateDate = (DateTime)Convert.ChangeType(dataTable.Rows[0]["UpdateDate"],typeof(DateTime));
}
this.DeptTimestamp = (byte[])Convert.ChangeType(dataTable.Rows[0]["DeptTimestamp"],typeof(byte[]));
return true;
}
else
{
return false;
}
}
#endregion
#region Define SelectFunction
/// <summary>
/// SelectFunction
/// Get Single Record
/// </summary>
/// <param name="SqlQuery">查询条件</param>
/// <returns>bool 是否取得该记录</returns>
public bool SelectFunction( string SqlQuery, Database dba, DbTransaction transaction )
{
// Create the Database object, using the default database service. The
// default database service is determined through configuration.
dbCommand = dba.GetSqlStringCommand(SqlQuery);
DataTable dataTable;
dataTable = dba.ExecuteDataSet( dbCommand, transaction ).Tables[0];
if(dataTable.Rows.Count > 0)
{
this.OldDepartmentID = (string)Convert.ChangeType(dataTable.Rows[0]["DepartmentID"],typeof(string));
this.DepartmentID = (string)Convert.ChangeType(dataTable.Rows[0]["DepartmentID"],typeof(string));
this.DepartmentAbbr = (string)Convert.ChangeType(dataTable.Rows[0]["DepartmentAbbr"],typeof(string));
this.DepartmentName = (string)Convert.ChangeType(dataTable.Rows[0]["DepartmentName"],typeof(string));
this.DepartmentCancelFlag = (string)Convert.ChangeType(dataTable.Rows[0]["DepartmentCancelFlag"],typeof(string));
this.DepartmentRole = (string)Convert.ChangeType(dataTable.Rows[0]["DepartmentRole"],typeof(string));
this.CreateUser = (string)Convert.ChangeType(dataTable.Rows[0]["CreateUser"],typeof(string));
if(dataTable.Rows[0]["CreateDate"] == DBNull.Value )
{
string strDateTime = "1753/1/1";
this.CreateDate = DateTime.Parse( strDateTime );
}
else
{
this.CreateDate = (DateTime)Convert.ChangeType(dataTable.Rows[0]["CreateDate"],typeof(DateTime));
}
this.UpdateUser = (string)Convert.ChangeType(dataTable.Rows[0]["UpdateUser"],typeof(string));
if(dataTable.Rows[0]["UpdateDate"] == DBNull.Value )
{
string strDateTime = "1753/1/1";
this.UpdateDate = DateTime.Parse( strDateTime );
}
else
{
this.UpdateDate = (DateTime)Convert.ChangeType(dataTable.Rows[0]["UpdateDate"],typeof(DateTime));
}
this.DeptTimestamp = (byte[])Convert.ChangeType(dataTable.Rows[0]["DeptTimestamp"],typeof(byte[]));
return true;
}
else
{
return false;
}
}
#endregion
#region Define QueryFunction
/// <summary>
/// QueryFunction
/// Get Accord with Records
/// </summary>
/// <param name="QueryString">QueryString</param>
/// <param name="dataTable">out DataTable</param>
/// <returns>ReturnRecordCount</returns>
public int QueryFunction(string QueryString,out DataTable dataTable)
{
// Create the Database object, using the default database service. The
// default database service is determined through configuration.
db = DatabaseFactory.CreateDatabase();
dbCommand = db.GetSqlStringCommand(QueryString);
dataTable = db.ExecuteDataSet(dbCommand).Tables[0];
return dataTable.Rows.Count;
}
#endregion
#region Define QueryFunction
/// <summary>
/// QueryFunction
/// Get Accord with Records
/// </summary>
/// <param name="QueryString">QueryString</param>
/// <param name="dataReader">out SqlDataReader</param>
public void QueryFunction(string QueryString,out SqlDataReader dataReader)
{
string SqlQuery = "Select * From [DepartmentInformation] Where 1=1 " + QueryString;
// Create the Database object, using the default database service. The
// default database service is determined through configuration.
db = DatabaseFactory.CreateDatabase();
dbCommand = db.GetSqlStringCommand(SqlQuery);
dataReader = (SqlDataReader)db.ExecuteReader(dbCommand);
}
#endregion
#region Define QueryFunction
/// <summary>
/// QueryFunction
/// Get Accord with Records
/// </summary>
/// <param name="QueryString">QueryString</param>
/// <param name="dataTable">out DataTable</param>
/// <returns>ReturnRecordCount</returns>
public int QueryFunction( string QueryString, Database dba, DbTransaction transaction, out DataTable dataTable)
{
// Create the Database object, using the default database service. The
// default database service is determined through configuration.
dbCommand = dba.GetSqlStringCommand(QueryString);
dataTable = dba.ExecuteDataSet(dbCommand).Tables[0];
return dataTable.Rows.Count;
}
#endregion
#region Define QueryFunction
/// <summary>
/// QueryFunction
/// Get Accord with Records
/// </summary>
/// <param name="QueryString">QueryString</param>
/// <param name="dataReader">out SqlDataReader</param>
public void QueryFunction( string QueryString, Database dba, DbTransaction transaction, out SqlDataReader dataReader)
{
string SqlQuery = "Select * From [DepartmentInformation] Where 1=1 " + QueryString;
// Create the Database object, using the default database service. The
// default database service is determined through configuration.
dbCommand = dba.GetSqlStringCommand(SqlQuery);
dataReader = (SqlDataReader)dba.ExecuteReader(dbCommand,transaction);
}
#endregion
#region Define XmlSerializeOut
/// <summary>
/// XmlSerializeOut
/// Serialize the C# Class to XML
/// </summary>
public void XmlSerializeOut()
{
StreamWriter strWriter = File.CreateText("DepartmentInformation.xml");
XmlSerializer XmlSer = new XmlSerializer(typeof(DepartmentInformation));
XmlSer.Serialize(strWriter,this);
strWriter.Close();
}
#endregion
#region Define BinarySerializeOut
/// <summary>
/// BinarySerializeOut
/// Serialize the C# Class to File
/// </summary>
public void BinarySerializeOut()
{
IFormatter objFormatterToStream = new BinaryFormatter();
Stream toStream = new FileStream("DepartmentInformation.bin",FileMode.Create,FileAccess.Write,FileShare.None);
objFormatterToStream.Serialize(toStream,(DepartmentInformation)this);
toStream.Close();
}
#endregion
#region Define GetParamterValue
/// <summary>
/// GetParamterValue
/// 得到Insert后的返回值仅当使用SCOPE_IDENTITY()时有效
/// </summary>
public object GetParamterValue()
{
return db.GetParameterValue(dbCommand, "@ReturnValue");
}
#endregion
}
}
存储过程源码:
1、INSERT:
/****** Object: StoredProcedure [dbo].[SP_InsertDEPARTMENT_LIST] Script Date: 01/10/2013 23:53:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
----------------------------------------------------------------------------
-- Insert a single record into DEPARTMENT_LIST
--DL_TimeStamp
----------------------------------------------------------------------------
ALTER PROC [dbo].[SP_InsertDEPARTMENT_LIST]
@DL_Name varchar(100),
@DL_DutyDescribe varchar(800) = NULL,
@DL_TimeStamp timestamp = NULL,
@DL_Creator int = NULL,
@DL_CreateTime datetime = NULL,
@DL_Updator int = NULL,
@DL_UpdateTime datetime = NULL,
@DL_Flag tinyint = NULL
AS
INSERT DEPARTMENT_LIST(DL_Name, DL_DutyDescribe, DL_TimeStamp, DL_Creator, DL_CreateTime, DL_Updator, DL_UpdateTime, DL_Flag)
VALUES (@DL_Name, @DL_DutyDescribe, @DL_TimeStamp, @DL_Creator, @DL_CreateTime, @DL_Updator, @DL_UpdateTime, @DL_Flag)
RETURN SCOPE_IDENTITY()
2、UPDATE:
/****** Object: StoredProcedure [dbo].[SP_UpdateDEPARTMENT_LIST] Script Date: 01/10/2013 23:54:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
----------------------------------------------------------------------------
-- Update a single record in DEPARTMENT_LIST
----------------------------------------------------------------------------
ALTER PROC [dbo].[SP_UpdateDEPARTMENT_LIST]
@OldDL_ID int,
@NewDL_ID int,
@DL_Name varchar(100),
@DL_DutyDescribe varchar(800) = NULL,
@DL_TimeStamp timestamp = NULL,
@DL_Creator int = NULL,
@DL_CreateTime datetime = NULL,
@DL_Updator int = NULL,
@DL_UpdateTime datetime = NULL,
@DL_Flag tinyint = NULL
AS
UPDATE DEPARTMENT_LIST
SET DL_ID = @NewDL_ID,
DL_Name = @DL_Name,
DL_DutyDescribe = @DL_DutyDescribe,
DL_Creator = @DL_Creator,
DL_CreateTime = @DL_CreateTime,
DL_Updator = @DL_Updator,
DL_UpdateTime = @DL_UpdateTime,
DL_Flag = COALESCE(@DL_Flag, 1)
WHERE DL_ID = @OldDL_ID
AND DL_TimeStamp = @DL_TimeStamp
3、DELETE:
/****** Object: StoredProcedure [dbo].[SP_DeleteDEPARTMENT_LIST] Script Date: 01/10/2013 23:55:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
----------------------------------------------------------------------------
-- Delete a single record from DEPARTMENT_LIST
----------------------------------------------------------------------------
ALTER PROC [dbo].[SP_DeleteDEPARTMENT_LIST]
@DL_ID int
AS
DELETE DEPARTMENT_LIST
WHERE DL_ID = @DL_ID
上述存储过程需要注意的地方是UPDATE的,它用到了一个类型为TimeStamp的作为条件,因此更新之前需要有原值。
可以单个Biz Class Entity更新。
可以加事务控制多个Biz Class Entity更新。
您所有需要做的就是从界面上获取数据,赋值给Biz Class Entity,调用相应的DML对应的函数处理。
上述内容并不完全符合当前EF框架概念。仅仅是为了偷懒,少写些sql语句做了上述工作。
希望上述内容能对您有所参考借鉴。