public List <PrivilegeCategory> LoadPrivilegeCategorysByCreated(DateTime created)
{
string sql = @"SELECT [CategoryID]
, [CategoryName]
, [IconClass]
, [Sequence]
, [Created]
, [CreatedBy]
, [Modified]
, [ModifiedBy]
FROM [BE_PrivilegeCategory] WHERE [Created]=@Created" ;
SqlCommand cmd = new SqlCommand(sql, this.conn, this.trans);
SqlParameter pCreated = new SqlParameter("Created", created);
pCreated.SqlDbType = SqlDbType.DateTime;
cmd.Parameters.Add(pCreated);
List <PrivilegeCategory> ret = new List <PrivilegeCategory>();
SqlDataReader dr = cmd.ExecuteReader();
try
{
while (dr.Read())
{
PrivilegeCategory iret = new PrivilegeCategory();
if (!Convert.IsDBNull(dr["CategoryID"]))
{
iret.CategoryID = (Guid)dr["CategoryID"];
}
iret.CategoryName = dr["CategoryName"].ToString();
iret.IconClass = dr["IconClass"].ToString();
if (!Convert.IsDBNull(dr["Sequence"]))
{
iret.Sequence = (int)dr["Sequence"];
}
if (!Convert.IsDBNull(dr["Created"]))
{
iret.Created = (DateTime)dr["Created"];
}
iret.CreatedBy = dr["CreatedBy"].ToString();
if (!Convert.IsDBNull(dr["Modified"]))
{
iret.Modified = (DateTime)dr["Modified"];
}
iret.ModifiedBy = dr["ModifiedBy"].ToString();
ret.Add(iret);
}
}
finally
{
dr.Close();
}
return(ret);
}
public int LoadPrivilegeCategoryByCategoryID(PrivilegeCategory obj)
{
string sql = @"SELECT [CategoryID]
, [CategoryName]
, [IconClass]
, [Sequence]
, [Created]
, [CreatedBy]
, [Modified]
, [ModifiedBy]
FROM [BE_PrivilegeCategory] WITH(NOLOCK) WHERE [CategoryID]=@CategoryID" ;
SqlCommand cmd = new SqlCommand(sql, this.conn, this.trans);
SqlParameter pCategoryID = new SqlParameter("CategoryID", Convert2DBnull(obj.CategoryID));
pCategoryID.SqlDbType = SqlDbType.UniqueIdentifier;
cmd.Parameters.Add(pCategoryID);
int ret = 0;
SqlDataReader dr = cmd.ExecuteReader();
try
{
while (dr.Read())
{
if (!Convert.IsDBNull(dr["CategoryID"]))
{
obj.CategoryID = (Guid)dr["CategoryID"];
}
obj.CategoryName = dr["CategoryName"].ToString();
obj.IconClass = dr["IconClass"].ToString();
if (!Convert.IsDBNull(dr["Sequence"]))
{
obj.Sequence = (int)dr["Sequence"];
}
if (!Convert.IsDBNull(dr["Created"]))
{
obj.Created = (DateTime)dr["Created"];
}
obj.CreatedBy = dr["CreatedBy"].ToString();
if (!Convert.IsDBNull(dr["Modified"]))
{
obj.Modified = (DateTime)dr["Modified"];
}
obj.ModifiedBy = dr["ModifiedBy"].ToString();
ret += 1;
}
}
finally
{
dr.Close();
}
return(ret);
}
public int UpdatePrivilegeCategoryByCategoryName(PrivilegeCategory obj)
{
string sql = @"UPDATE [BE_PrivilegeCategory] SET [CategoryID]=@CategoryID
, [IconClass]=@IconClass
, [Sequence]=@Sequence
, [Created]=@Created
, [CreatedBy]=@CreatedBy
, [Modified]=@Modified
, [ModifiedBy]=@ModifiedBy
WHERE [CategoryName]=@CategoryName" ;
SqlCommand cmd = new SqlCommand(sql, this.conn, this.trans);
SqlParameter pCategoryID = new SqlParameter("CategoryID", Convert2DBnull(obj.CategoryID));
pCategoryID.SqlDbType = SqlDbType.UniqueIdentifier;
cmd.Parameters.Add(pCategoryID);
SqlParameter pIconClass = new SqlParameter("IconClass", Convert2DBnull(obj.IconClass));
pIconClass.SqlDbType = SqlDbType.NVarChar;
cmd.Parameters.Add(pIconClass);
SqlParameter pSequence = new SqlParameter("Sequence", Convert2DBnull(obj.Sequence));
pSequence.SqlDbType = SqlDbType.Int;
cmd.Parameters.Add(pSequence);
SqlParameter pCreated = new SqlParameter("Created", Convert2DBnull(obj.Created));
pCreated.SqlDbType = SqlDbType.DateTime;
cmd.Parameters.Add(pCreated);
SqlParameter pCreatedBy = new SqlParameter("CreatedBy", Convert2DBnull(obj.CreatedBy));
pCreatedBy.SqlDbType = SqlDbType.NVarChar;
cmd.Parameters.Add(pCreatedBy);
SqlParameter pModified = new SqlParameter("Modified", Convert2DBnull(obj.Modified));
pModified.SqlDbType = SqlDbType.DateTime;
cmd.Parameters.Add(pModified);
SqlParameter pModifiedBy = new SqlParameter("ModifiedBy", Convert2DBnull(obj.ModifiedBy));
pModifiedBy.SqlDbType = SqlDbType.NVarChar;
cmd.Parameters.Add(pModifiedBy);
SqlParameter pCategoryName = new SqlParameter("CategoryName", Convert2DBnull(obj.CategoryName));
pCategoryName.SqlDbType = SqlDbType.NVarChar;
cmd.Parameters.Add(pCategoryName);
return(cmd.ExecuteNonQuery());
}
public int InsertPrivilegeCategory(PrivilegeCategory obj)
{
string sql = @"INSERT INTO[BE_PrivilegeCategory]([CategoryID]
, [CategoryName]
, [IconClass]
, [Sequence]
, [Created]
, [CreatedBy]
, [Modified]
, [ModifiedBy]
) VALUES(@CategoryID
, @CategoryName
, @IconClass
, @Sequence
, @Created
, @CreatedBy
, @Modified
, @ModifiedBy
)" ;
SqlCommand cmd = new SqlCommand(sql, this.conn, this.trans);
SqlParameter pCategoryID = new SqlParameter("CategoryID", Convert2DBnull(obj.CategoryID));
pCategoryID.SqlDbType = SqlDbType.UniqueIdentifier;
cmd.Parameters.Add(pCategoryID);
SqlParameter pCategoryName = new SqlParameter("CategoryName", Convert2DBnull(obj.CategoryName));
pCategoryName.SqlDbType = SqlDbType.NVarChar;
cmd.Parameters.Add(pCategoryName);
SqlParameter pIconClass = new SqlParameter("IconClass", Convert2DBnull(obj.IconClass));
pIconClass.SqlDbType = SqlDbType.NVarChar;
cmd.Parameters.Add(pIconClass);
SqlParameter pSequence = new SqlParameter("Sequence", Convert2DBnull(obj.Sequence));
pSequence.SqlDbType = SqlDbType.Int;
cmd.Parameters.Add(pSequence);
SqlParameter pCreated = new SqlParameter("Created", Convert2DBnull(obj.Created));
pCreated.SqlDbType = SqlDbType.DateTime;
cmd.Parameters.Add(pCreated);
SqlParameter pCreatedBy = new SqlParameter("CreatedBy", Convert2DBnull(obj.CreatedBy));
pCreatedBy.SqlDbType = SqlDbType.NVarChar;
cmd.Parameters.Add(pCreatedBy);
SqlParameter pModified = new SqlParameter("Modified", Convert2DBnull(obj.Modified));
pModified.SqlDbType = SqlDbType.DateTime;
cmd.Parameters.Add(pModified);
SqlParameter pModifiedBy = new SqlParameter("ModifiedBy", Convert2DBnull(obj.ModifiedBy));
pModifiedBy.SqlDbType = SqlDbType.NVarChar;
cmd.Parameters.Add(pModifiedBy);
return(cmd.ExecuteNonQuery());
}
