Data Validation Procedure in C#

Question

Hi guys 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: Procedure that loops through the Staging Table 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. 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!

Manas Mohapatra · Answer

Your idea may be correct: data validation in windows service. But it may leads performance overhead in your application because you will get all records from DB then loop over data. If you want to use Database then create a stored procedure and implement all logic there. It would be better.

Nishant Mittal · Answer

Dont u dare bring all the data from DB to code level and then loop it :( Its really a bad practice ...go for Stored Procedure. you dont need to check condition the query in Sp will do it for you

onmyway365 . · Answer

Hi Nishant Thank you for your response. I am writing a C# windows service that validates the data in a Staging table and then move certain records to a different table. I was thinking of having a function called via a timed method. Something like this (i just don't have the knowledge how to write it): public static void DataValidation() { using (var stageContext = new StagingTableDataContext()) foreach (var row in stageContext.ProjectMasters) { //Have my validation here if (row.Processing_Signal == U) { row.Resolution_Message = Test; } stageContext.SubmitChanges(); } } So I have all my rules in this function. I hope it makes sense.

Manas Mohapatra · Answer

Just follow below Stored Procedure. It is a just template for you to implement logic not exact one: CREATE Procedure uspAddRecords() AS BEGIN Create TABLE #TempTable(ID int identity(11) Code varchar(10) Model varchar(10) Num int Qty int); insert into #TempTable(Code Model Num Qty) select Code Model Num Qty from stagingDb.Table1; Declare @TempID int @Code varchar(10) @Model varchar(10) @Num int @Qty int; WHILE((select Count(ID) from #TempTable) > 0) BEGIN SET @Qty = 0; select TOP 1 @TempID= ID @Code = Code @Model = Model @Num = Num @Qty = Qty from #TempTable ORDER BY 1; -- Implement your own logic to Finance project etc. DELETE FROM #TempTable WHERE ID = @TempID; END DROP TABLE #TempTable; END

Nishant Mittal · Answer

Hi.. i dont think you need any and funtion in this.. U just need sql script to do the rest.. update table set Processing_Result = 2  Processing_Result_Text = ' No Region ' where [Data].[Finance_Project_ Number] = Null Like this

Nishant Mittal · Answer

PLease accept the and if it help u

onmyway365 . · Answer

@ Nishant Thank you for the advice - I am still a newb and appreciate all the help suggestions and best practices!

onmyway365 . · Answer

@Manas - Stored Procedure does sound like a good idea. :) Do you perhaps have some syntax suggestions for me?

onmyway365 . · Answer

@Manas Thank you for the suggestion. However it is an existing table - I simply want to validate the data of before I copy it to a different table via a C# windows service.