Procedure that validates the data in the table

[onmyway]

Hi guys (apologies if I posted in the wrong topic),

I hope you can help me. I am quite new to C# development, and would really appreciate your expertise.

I want a simple procedure/function I can call to validate the data in a table:

So this is what needs to happen:

1. Procedure that loops through a Table
2. Update certain fields based on some rules

Here are some examples of the rules to apply based upon the value of the specific field in the table:

1. 1.if [Data].[Finance_Project_Number] = Null/Blank
   ?Set [Processing_Result] = 2
   ?set [Processing_Result_Text] = ?No Project Number?
   
   
   2.if [Data].[Finance_Project_Region] = Null/Blank
   
   ?Set [Processing_Result] = 2
   ?set [Processing_Result_Text] = ?No Region?
   Also, on point no 2, how can I check for valid entries? Something like ?one of?: Gauteng, Free State, Limpopo etc. If it is ?one of? then it passes, otherwise;
   ?Set [Processing_Result] = 2
   ?set [Processing_Result_Text] = ?Incorrect Region Entered?
   
   
   3.Cross reference the customer number with another table.column: [Data].[Customer_Number] exists in [CustomerMaster].[Customer_Code]
   if not:
   ?Set [Processing_Result] = 2
   ?set [Processing_Result_Text] = ?Customer Number does not Exist?

I thank you in advance!

 

[jmcilhinney]

What exactly do you mean when you say "table"? Are you talking about a database table? An ADO.NET DataTable, typed or untyped? A DataGridView or other grid control? Something else?

Based on what you've said, I think that you would start with a loop (for or foreach) to go through the rows of your table. You would have a method for each rule that took a row as an argument, tested it and modified it appropriately. You would then call each of those methods on the current row inside the loop, either directly or via another method that called each of the individual methods.

 

[onmyway]

What exactly do you mean when you say "table"? Are you talking about a database table? An ADO.NET DataTable, typed or untyped? A DataGridView or other grid control? Something else?

Based on what you've said, I think that you would start with a loop (for or foreach) to go through the rows of your table. You would have a method for each rule that took a row as an argument, tested it and modified it appropriately. You would then call each of those methods on the current row inside the loop, either directly or via another method that called each of the individual methods.

My apologies - i was a bit vague.

I am creating a Windows Service app, and within it I want to validate the data in an existing SQL DB table, before I copy some of the data to a new SQL table.

I was thinking in the lines of this (I just don't know how to do this exactly):

        public static void Validate()
        {


            using (var stageContext = new StagingTableDataContext())
            {
                var data = stageContext.ProjectMasters;


                foreach (var sourceDataEntry in data)
                {


                    if (sourceDataEntry.Finance_Project_Number == null)
                    {
                        sourceDataEntry.Processing_Result = 2;
                        sourceDataEntry.Processing_Result_Text = "No Project Number";
                    }
                    else if (sourceDataEntry.Finance_Project_Number == "")
                    {
                        sourceDataEntry.Processing_Result = 2;
                        sourceDataEntry.Processing_Result_Text = "No Project Number";
                    }
                    stageContext.SubmitChanges();


                }
            }
        }

 

[jmcilhinney]

So you appear to be using Entity Framework or is it LINQ to SQL? That's information that you should be providing because it could have a bearing on the best way to achieve your aim. What you've suggested is basically it though. As I said, I would tend to write a method for each rule but it's not strictly necessary.

 

[onmyway]

Thank you for the help

I am using LINQ to SQL. This is what I have thus far:

2 Questions:

1. Any suggestions on the commented section?
2. Should I rather use a stored procedure?

public static void Validate()
        {
            using (var stageContext = new StagingTableDataContext())
            {
                var data = stageContext.ProjectMasters;

                foreach (var sourceDataEntry in data)
                {
                    //Project Number
                    if (sourceDataEntry.Finance_Project_Number == null)
                    {
                        sourceDataEntry.Processing_Result = 2;
                        sourceDataEntry.Processing_Result_Text = "No Project Number";
                        sourceDataEntry.Processed_Datetime = DateTime.Now;
                    }
                    else if (sourceDataEntry.Finance_Project_Number == "")
                    {
                        sourceDataEntry.Processing_Result = 2;
                        sourceDataEntry.Processing_Result_Text = "No Project Number";
                        sourceDataEntry.Processed_Datetime = DateTime.Now;
                    }
                    //Project Type
                    if (sourceDataEntry.Finance_Project_Type == null)
                    {
                        sourceDataEntry.Processing_Result = 2;
                        sourceDataEntry.Processing_Result_Text = "No Project Type";
                        sourceDataEntry.Processed_Datetime = DateTime.Now;
                    }
                    else if (sourceDataEntry.Finance_Project_Type == "")
                    {
                        sourceDataEntry.Processing_Result = 2;
                        sourceDataEntry.Processing_Result_Text = "No Project Type";
                        sourceDataEntry.Processed_Datetime = DateTime.Now;
                    }
                    //Project Region
                    if (sourceDataEntry.Finance_Project_Region == null)
                    {
                        sourceDataEntry.Processing_Result = 2;
                        sourceDataEntry.Processing_Result_Text = "No Project Type";
                        sourceDataEntry.Processed_Datetime = DateTime.Now;
                    }
                    else if (sourceDataEntry.Finance_Project_Region == "")
                    {
                        sourceDataEntry.Processing_Result = 2;
                        sourceDataEntry.Processing_Result_Text = "No Project Region";
                        sourceDataEntry.Processed_Datetime = DateTime.Now;
                    }
                    //if (sourceDataEntry.Finance_Project_Region != "Gauteng") ||
                    //        (sourceDataEntry.Finance_Project_Region != "Free State") ||
                    //        (sourceDataEntry.Finance_Project_Region != "Limpopo") ||
                    //        (sourceDataEntry.Finance_Project_Region != "Kwazulu Natal") ||
                    //        (sourceDataEntry.Finance_Project_Region != "Western Cape");
                    //{
                    //    sourceDataEntry.Processing_Result = 2;
                    //    sourceDataEntry.Processing_Result_Text = "Incorrect Province";
                    //    sourceDataEntry.Processed_Datetime = DateTime.Now;
                    //}

                    stageContext.SubmitChanges();

                }
            }
        }

 

[jmcilhinney]

1. The commented section is wrong, as you probably guessed. You need to AND those conditions, not OR them. That said, you can just put all the values into an array and call Contains once.

2. You could if you wanted to but there's no real need to. To my mind, using an ORM tends to lend itself towards not using sprocs.

 

[onmyway]

Thanks!

1. I have tried && but get a message "invalid expression term" - any suggestion on what I am doing wrong?
2. I have done some light reading (quickly), and it seems that sprocs is not always the way to go. Also, this app is not a large app, so I should be fine with the method I chose.

 

[jmcilhinney]

1. I have tried && but get a message "invalid expression term" - any suggestion on what I am doing wrong?

You need to either get rid of all the opening and closing parentheses and just wrap the entire Boolean expression in one set or else leave what you have and wrap the lot in an extra set of parentheses. Also, get rid of the semicolon after the last parenthesis.

2. I have done some light reading (quickly), and it seems that sprocs is not always the way to go. Also, this app is not a large app, so I should be fine with the method I chose.

As an application developer, sprocs should generally be your last choice. Those coming from a DBA's perspective often prefer them because they like to have as much logic in the database as possible. An application developer should generally have the opposite approach, because logic in a database is harder to deal with for several reasons. I'd only tend to use sprocs for heavy duty database work for which they offer a significant performance boost.

 

[onmyway]

You need to either get rid of all the opening and closing parentheses and just wrap the entire Boolean expression in one set or else leave what you have and wrap the lot in an extra set of parentheses. Also, get rid of the semicolon after the last parenthesis.

Thank you - this works!

As an application developer, sprocs should generally be your last choice. Those coming from a DBA's perspective often prefer them because they like to have as much logic in the database as possible. An application developer should generally have the opposite approach, because logic in a database is harder to deal with for several reasons. I'd only tend to use sprocs for heavy duty database work for which they offer a significant performance boost.

Noted - thank you for the tip! :tears_of_joy:

 

[onmyway]

How do I mark the thread an Answered?

 

[jmcilhinney]

How do I mark the thread an Answered?

Edit your first post and change the prefix to Answered. It's a good idea to set that prefix to Question when posting in the first place, unless it's not a question of course.

 

[onmyway]

Edit your first post and change the prefix to Answered. It's a good idea to set that prefix to Question when posting in the first place, unless it's not a question of course.

When editing my first post, i only had 2 prefix options; No prefix, and question. I changed to no prefix to test it, and now there is no prefix at all.