public List <RoomDesignerFile> LoadRoomDesignerFilesByRemark(string remark)
{
string sql = @"SELECT [FileID]
, [DesignerID]
, [Title]
, [FileName]
, [Remark]
, [FileURL]
, [Created]
, [CreatedBy]
, [Modified]
, [ModifiedBy]
FROM [BE_RoomDesignerFile] WHERE [Remark]=@Remark" ;
SqlCommand cmd = new SqlCommand(sql, this.conn, this.trans);
SqlParameter pRemark = new SqlParameter("Remark", remark);
pRemark.SqlDbType = SqlDbType.NVarChar;
cmd.Parameters.Add(pRemark);
List <RoomDesignerFile> ret = new List <RoomDesignerFile>();
SqlDataReader dr = cmd.ExecuteReader();
try
{
while (dr.Read())
{
RoomDesignerFile iret = new RoomDesignerFile();
if (!Convert.IsDBNull(dr["FileID"]))
{
iret.FileID = (Guid)dr["FileID"];
}
if (!Convert.IsDBNull(dr["DesignerID"]))
{
iret.DesignerID = (Guid)dr["DesignerID"];
}
iret.Title = dr["Title"].ToString();
iret.FileName = dr["FileName"].ToString();
iret.Remark = dr["Remark"].ToString();
iret.FileURL = dr["FileURL"].ToString();
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 LoadRoomDesignerFileByFileID(RoomDesignerFile obj)
{
string sql = @"SELECT [FileID]
, [DesignerID]
, [Title]
, [FileName]
, [Remark]
, [FileURL]
, [Created]
, [CreatedBy]
, [Modified]
, [ModifiedBy]
FROM [BE_RoomDesignerFile] WITH(NOLOCK) WHERE [FileID]=@FileID" ;
SqlCommand cmd = new SqlCommand(sql, this.conn, this.trans);
SqlParameter pFileID = new SqlParameter("FileID", Convert2DBnull(obj.FileID));
pFileID.SqlDbType = SqlDbType.UniqueIdentifier;
cmd.Parameters.Add(pFileID);
int ret = 0;
SqlDataReader dr = cmd.ExecuteReader();
try
{
while (dr.Read())
{
if (!Convert.IsDBNull(dr["FileID"]))
{
obj.FileID = (Guid)dr["FileID"];
}
if (!Convert.IsDBNull(dr["DesignerID"]))
{
obj.DesignerID = (Guid)dr["DesignerID"];
}
obj.Title = dr["Title"].ToString();
obj.FileName = dr["FileName"].ToString();
obj.Remark = dr["Remark"].ToString();
obj.FileURL = dr["FileURL"].ToString();
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 UpdateRoomDesignerFileByFileID(RoomDesignerFile obj)
{
string sql = @"UPDATE [BE_RoomDesignerFile] SET [DesignerID]=@DesignerID
, [Title]=@Title
, [FileName]=@FileName
, [Remark]=@Remark
, [FileURL]=@FileURL
, [Created]=@Created
, [CreatedBy]=@CreatedBy
, [Modified]=@Modified
, [ModifiedBy]=@ModifiedBy
WHERE [FileID]=@FileID" ;
SqlCommand cmd = new SqlCommand(sql, this.conn, this.trans);
SqlParameter pDesignerID = new SqlParameter("DesignerID", Convert2DBnull(obj.DesignerID));
pDesignerID.SqlDbType = SqlDbType.UniqueIdentifier;
cmd.Parameters.Add(pDesignerID);
SqlParameter pTitle = new SqlParameter("Title", Convert2DBnull(obj.Title));
pTitle.SqlDbType = SqlDbType.NVarChar;
cmd.Parameters.Add(pTitle);
SqlParameter pFileName = new SqlParameter("FileName", Convert2DBnull(obj.FileName));
pFileName.SqlDbType = SqlDbType.NVarChar;
cmd.Parameters.Add(pFileName);
SqlParameter pRemark = new SqlParameter("Remark", Convert2DBnull(obj.Remark));
pRemark.SqlDbType = SqlDbType.NVarChar;
cmd.Parameters.Add(pRemark);
SqlParameter pFileURL = new SqlParameter("FileURL", Convert2DBnull(obj.FileURL));
pFileURL.SqlDbType = SqlDbType.NVarChar;
cmd.Parameters.Add(pFileURL);
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 pFileID = new SqlParameter("FileID", Convert2DBnull(obj.FileID));
pFileID.SqlDbType = SqlDbType.UniqueIdentifier;
cmd.Parameters.Add(pFileID);
return(cmd.ExecuteNonQuery());
}
public int InsertRoomDesignerFile(RoomDesignerFile obj)
{
string sql = @"INSERT INTO[BE_RoomDesignerFile]([FileID]
, [DesignerID]
, [Title]
, [FileName]
, [Remark]
, [FileURL]
, [Created]
, [CreatedBy]
, [Modified]
, [ModifiedBy]
) VALUES(@FileID
, @DesignerID
, @Title
, @FileName
, @Remark
, @FileURL
, @Created
, @CreatedBy
, @Modified
, @ModifiedBy
)" ;
SqlCommand cmd = new SqlCommand(sql, this.conn, this.trans);
SqlParameter pFileID = new SqlParameter("FileID", Convert2DBnull(obj.FileID));
pFileID.SqlDbType = SqlDbType.UniqueIdentifier;
cmd.Parameters.Add(pFileID);
SqlParameter pDesignerID = new SqlParameter("DesignerID", Convert2DBnull(obj.DesignerID));
pDesignerID.SqlDbType = SqlDbType.UniqueIdentifier;
cmd.Parameters.Add(pDesignerID);
SqlParameter pTitle = new SqlParameter("Title", Convert2DBnull(obj.Title));
pTitle.SqlDbType = SqlDbType.NVarChar;
cmd.Parameters.Add(pTitle);
SqlParameter pFileName = new SqlParameter("FileName", Convert2DBnull(obj.FileName));
pFileName.SqlDbType = SqlDbType.NVarChar;
cmd.Parameters.Add(pFileName);
SqlParameter pRemark = new SqlParameter("Remark", Convert2DBnull(obj.Remark));
pRemark.SqlDbType = SqlDbType.NVarChar;
cmd.Parameters.Add(pRemark);
SqlParameter pFileURL = new SqlParameter("FileURL", Convert2DBnull(obj.FileURL));
pFileURL.SqlDbType = SqlDbType.NVarChar;
cmd.Parameters.Add(pFileURL);
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());
}
