Binding source problem

Question

FallenPaladin 0 Junior Poster in Training

17 Years Ago

Hi, I am hoping that someone can help.

I am trying to validate and compaire date entries made by a user.
The dates represent contract start and end dates. This has a one to many relation ship with a client table. A client can have one or more contracts.

However the contracts must not over lap. I have got the basic framework for my validation, however I need to compaire the start date of a new contract to the end date of the previouse.

I have tried determining the end date using the binding source, but all i get is the underlying data table.

please can anyone point me in the right direction.

.net:c# database-design

2 Contributors
3 Replies
127 Views
2 Days Discussion Span
Latest Post 17 Years Ago Latest Post by kvprajapati

Dani AI

Generated 9 Months Ago

A few practical points to clear up what’s happening and how to make the validation robust and fast.

BindingSource vs. database

When a BindingSource is bound to a DataTable the items you get from it are DataRowView objects. Cast bindingSource.Current or bindingSource[index] to DataRowView and read the End_Date as a DateTime (don’t compare ToShortDateString values). Also make sure the view is sorted by Start_Date so Position - 1 really means “previous contract” in time.

Example (access previous row safely):

var prev = tblContractBindingSource.Position > 0
    ? tblContractBindingSource[tblContractBindingSource.Position - 1] as DataRowView
    : null;

DateTime? prevEnd = prev != null ? prev.Row.Field<DateTime?>("End_Date") : null;

Database-side checks and performance

Querying the database for a single existence check is cheap if you filter correctly and have an index on the client/employee key (and on the date columns if you do range checks). Prefer a targeted query that asks “is there any contract for this EmpID that overlaps this new range?” rather than pulling the whole table, e.g.:

bool overlaps = dc.tblContracts.Any(c =>
    c.EmpID == empId &&
    c.ID != currentContractId &&       // exclude current record when editing
    c.Start_Date <= newEnd &&
    c.End_Date >= newStart);

Other practical notes

Parse user input into DateTime with DateTime.TryParseExact (explicit format + culture) and compare DateTime values, not strings.
Handle open-ended contracts (null End_Date) explicitly.
To avoid race conditions, perform the check-and-insert inside a single DB transaction or a stored procedure that does the check then inserts if no overlap.
Avoid empty catch blocks: surface DB errors to logs and fail validation gracefully.

For : sorting the BindingSource and reading the DataRowView directly will let you validate against the neighbor rows without fetching the whole table. For : using LINQ/SQL for a single Any()/Max() query is fine — just keep the queries narrow and indexed.

All 3 Replies

kvprajapati 1,826 Posting Genius

17 Years Ago

Which post contains your problem definition? If you talking about database then use SQL statements or linq.

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

FallenPaladin 0 Junior Poster in Training · Answer 1 · 2009-05-20T17:21:59+00:00

Hi have been trying various things to get around the problem I have.

One option I have come acros is to interigate the contract table from the database and retreave all entries in the list for the required foriegn key, using linq.

However I am concerned that this will impact on performance, especially as the table grows.

I have also noticed that the binding source can be copyed to a list, however I have been unable to implement this.

Has anyone any thoughts on iether of these two possible solutions

Thanks

FallenPaladin 0 Junior Poster in Training · Answer 2 · 2009-05-21T19:14:05+00:00

Hi....again :S
I have written this as the validation method, please could some one look at it for me and suggest any improvments. The ValidateDate method uses a try catch block to validate the date. It has one overlad, the overload evaluates the first argument to be less than the second.

Thank you.

private bool validateEntries()
        {
            bool isValid = true;
            if (hasChanged)
            {
                //Method to validate user entries.
                //The method utilises the text of the input boxes and the binding source to evaluate if the date has a preceding date.

                //clear all errors
                dateValidator.Clear();
                if (start_DateTextBox.Enabled == true)
                {
                    //If the text box is enabled must be the last record
                    if ((tblContractBindingSource.Count > 1) && (tblContractBindingSource.Count == tblContractBindingSource.Position + 1))
                    {
                        //Validate against previouse end date
                        try
                        {
                            LinqLoginDataContext dc = new LinqLoginDataContext();
                            var startDate = (from conStarts in dc.tblContracts
                                             where conStarts.EmpID == int.Parse(empIDTextBox.Text)
                                             select new
                                             {
                                                 conStarts.End_Date
                                             }).Max();

                            if (!ValidateDate(startDate.End_Date.Value.ToShortDateString(), start_DateTextBox.Text.Trim()))
                            {
                                dateValidator.SetError(end_DateTextBox, "Invalid date. Must be in the format dd/mm/yyyy and before the " + startDate.End_Date.Value.ToShortDateString());
                                isValid = false;
                            }
                        }
                        catch
                        {
                            //Error
                            //Validate the start date only
                            MessageBox.Show("Database connection error. Unable to varify the end date of previouse contract", "Connection Error");
                        }
                    }
                    if (end_DateTextBox.Text != "")
                    {
                        //Validate the start date against the end date
                        if (!ValidateDate(start_DateTextBox.Text.Trim(), end_DateTextBox.Text.Trim()))
                        {
                            dateValidator.SetError(start_DateTextBox, "Invalid date. Must be in the format dd/mm/yyyy and before the end date.");
                            isValid = false;
                        }
                    }
                    else
                    {
                        //Validate start date only
                        if (!ValidateDate(start_DateTextBox.Text.Trim()))
                        {
                            dateValidator.SetError(start_DateTextBox, "Invalid date. Must be in the format dd/mm/yyyy.");
                            isValid = false;
                        }
                    }
                }
                else
                {
                    //Validate end date only
                    if ((tblContractBindingSource.Count > 1) && (tblContractBindingSource.Count != tblContractBindingSource.Position + 1))
                    {
                        //if not the last entry look for next entry and validate end date to be before next start date
                        try
                        {
                            LinqLoginDataContext dc = new LinqLoginDataContext();
                            var startDates = from conStarts in dc.tblContracts
                                             where conStarts.EmpID == int.Parse(empIDTextBox.Text) && conStarts.Start_Date > DateTime.Parse(start_DateTextBox.Text)
                                             select new
                                             {
                                                 conStarts.Start_Date
                                             };

                            //This is a waistfull iteration and would possibly only report the finall comparison
                            foreach (var conStart in startDates)
                            {
                                if (!ValidateDate(end_DateTextBox.Text.Trim(), conStart.Start_Date.ToShortDateString()))
                                {
                                    dateValidator.SetError(end_DateTextBox, "Invalid date. Must be in the format dd/mm/yyyy and before the " + conStart.Start_Date.ToShortDateString());
                                    isValid = false;
                                }
                            }
                        }
                        catch
                        {
                            //To catch an error, not need to be reported to user but date must be validated
                            if (!ValidateDate(start_DateTextBox.Text.Trim(), end_DateTextBox.Text.Trim()))
                            {
                                dateValidator.SetError(end_DateTextBox, "Invalid date. Must be in the format dd/mm/yyyy and after the start date.");
                                isValid = false;
                            }
                        }
                    }

                    else
                    {
                        //Validate against start date
                        if (!ValidateDate(start_DateTextBox.Text.Trim(), end_DateTextBox.Text.Trim()))
                        {
                            dateValidator.SetError(end_DateTextBox, "Invalid date. Must be in the format dd/mm/yyyy and after the start date.");
                            isValid = false;
                        }
                    }
                }
            }
            return isValid;
        }

Binding source problem

Dani AI

Recommended Answers Collapse Answers

All 3 Replies

Recommended Answers