public List <TransportMain> LoadTransportMainsByCreated(DateTime created)
{
string sql = @"SELECT [TransportID]
, [TransportNo]
, [CarID]
, [Source]
, [Target]
, [Price]
, [Created]
, [CreatedBy]
FROM [BE_TransportMain] 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 <TransportMain> ret = new List <TransportMain>();
SqlDataReader dr = cmd.ExecuteReader();
try
{
while (dr.Read())
{
TransportMain iret = new TransportMain();
if (!Convert.IsDBNull(dr["TransportID"]))
{
iret.TransportID = (Guid)dr["TransportID"];
}
iret.TransportNo = dr["TransportNo"].ToString();
if (!Convert.IsDBNull(dr["CarID"]))
{
iret.CarID = (Guid)dr["CarID"];
}
iret.Source = dr["Source"].ToString();
iret.Target = dr["Target"].ToString();
if (!Convert.IsDBNull(dr["Price"]))
{
iret.Price = (decimal)dr["Price"];
}
if (!Convert.IsDBNull(dr["Created"]))
{
iret.Created = (DateTime)dr["Created"];
}
iret.CreatedBy = dr["CreatedBy"].ToString();
ret.Add(iret);
}
}
finally
{
dr.Close();
}
return(ret);
}
public int LoadTransportMainByTransportID(TransportMain obj)
{
string sql = @"SELECT [TransportID]
, [TransportNo]
, [CarID]
, [Source]
, [Target]
, [Price]
, [Created]
, [CreatedBy]
FROM [BE_TransportMain] WITH(NOLOCK) WHERE [TransportID]=@TransportID" ;
SqlCommand cmd = new SqlCommand(sql, this.conn, this.trans);
SqlParameter pTransportID = new SqlParameter("TransportID", Convert2DBnull(obj.TransportID));
pTransportID.SqlDbType = SqlDbType.UniqueIdentifier;
cmd.Parameters.Add(pTransportID);
int ret = 0;
SqlDataReader dr = cmd.ExecuteReader();
try
{
while (dr.Read())
{
if (!Convert.IsDBNull(dr["TransportID"]))
{
obj.TransportID = (Guid)dr["TransportID"];
}
obj.TransportNo = dr["TransportNo"].ToString();
if (!Convert.IsDBNull(dr["CarID"]))
{
obj.CarID = (Guid)dr["CarID"];
}
obj.Source = dr["Source"].ToString();
obj.Target = dr["Target"].ToString();
if (!Convert.IsDBNull(dr["Price"]))
{
obj.Price = (decimal)dr["Price"];
}
if (!Convert.IsDBNull(dr["Created"]))
{
obj.Created = (DateTime)dr["Created"];
}
obj.CreatedBy = dr["CreatedBy"].ToString();
ret += 1;
}
}
finally
{
dr.Close();
}
return(ret);
}
public int UpdateTransportMainByTransportID(TransportMain obj)
{
string sql = @"UPDATE [BE_TransportMain] SET [TransportNo]=@TransportNo
, [CarID]=@CarID
, [Source]=@Source
, [Target]=@Target
, [Price]=@Price
, [Created]=@Created
, [CreatedBy]=@CreatedBy
WHERE [TransportID]=@TransportID" ;
SqlCommand cmd = new SqlCommand(sql, this.conn, this.trans);
SqlParameter pTransportNo = new SqlParameter("TransportNo", Convert2DBnull(obj.TransportNo));
pTransportNo.SqlDbType = SqlDbType.VarChar;
cmd.Parameters.Add(pTransportNo);
SqlParameter pCarID = new SqlParameter("CarID", Convert2DBnull(obj.CarID));
pCarID.SqlDbType = SqlDbType.UniqueIdentifier;
cmd.Parameters.Add(pCarID);
SqlParameter pSource = new SqlParameter("Source", Convert2DBnull(obj.Source));
pSource.SqlDbType = SqlDbType.NVarChar;
cmd.Parameters.Add(pSource);
SqlParameter pTarget = new SqlParameter("Target", Convert2DBnull(obj.Target));
pTarget.SqlDbType = SqlDbType.NVarChar;
cmd.Parameters.Add(pTarget);
SqlParameter pPrice = new SqlParameter("Price", Convert2DBnull(obj.Price));
pPrice.SqlDbType = SqlDbType.Decimal;
cmd.Parameters.Add(pPrice);
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 pTransportID = new SqlParameter("TransportID", Convert2DBnull(obj.TransportID));
pTransportID.SqlDbType = SqlDbType.UniqueIdentifier;
cmd.Parameters.Add(pTransportID);
return(cmd.ExecuteNonQuery());
}
public int InsertTransportMain(TransportMain obj)
{
string sql = @"INSERT INTO[BE_TransportMain]([TransportID]
, [TransportNo]
, [CarID]
, [Source]
, [Target]
, [Price]
, [Created]
, [CreatedBy]
) VALUES(@TransportID
, @TransportNo
, @CarID
, @Source
, @Target
, @Price
, @Created
, @CreatedBy
)" ;
SqlCommand cmd = new SqlCommand(sql, this.conn, this.trans);
SqlParameter pTransportID = new SqlParameter("TransportID", Convert2DBnull(obj.TransportID));
pTransportID.SqlDbType = SqlDbType.UniqueIdentifier;
cmd.Parameters.Add(pTransportID);
SqlParameter pTransportNo = new SqlParameter("TransportNo", Convert2DBnull(obj.TransportNo));
pTransportNo.SqlDbType = SqlDbType.VarChar;
cmd.Parameters.Add(pTransportNo);
SqlParameter pCarID = new SqlParameter("CarID", Convert2DBnull(obj.CarID));
pCarID.SqlDbType = SqlDbType.UniqueIdentifier;
cmd.Parameters.Add(pCarID);
SqlParameter pSource = new SqlParameter("Source", Convert2DBnull(obj.Source));
pSource.SqlDbType = SqlDbType.NVarChar;
cmd.Parameters.Add(pSource);
SqlParameter pTarget = new SqlParameter("Target", Convert2DBnull(obj.Target));
pTarget.SqlDbType = SqlDbType.NVarChar;
cmd.Parameters.Add(pTarget);
SqlParameter pPrice = new SqlParameter("Price", Convert2DBnull(obj.Price));
pPrice.SqlDbType = SqlDbType.Decimal;
cmd.Parameters.Add(pPrice);
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);
return(cmd.ExecuteNonQuery());
}
