Data Validation Procedure in C#

onmyway365 .

10y1.1kVisual Basic .NET

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!

9 Replies

Know the answer? Post it — somebody with the same question will find it here.

Manas Mohapatra10y ago
Accepted 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.
+2
Nishant Mittal10y ago
PLease accept the and if it help u
0
onmyway365 .10y ago
@ Nishant Thank you for the advice - I am still a newb, and appreciate all the help, suggestions and best practices!
0
Nishant Mittal10y ago
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
+1
onmyway365 .10y ago
@Manas - Stored Procedure does sound like a good idea. :) Do you perhaps have some syntax suggestions for me?
0
onmyway365 .10y ago
@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.
0
onmyway365 .10y ago
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.
+1
Manas Mohapatra10y ago
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(1,1), 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
+1
Nishant Mittal10y ago
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
+1