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()); }