AShalata AShalata - 2 years ago 114
C# Question

validation on import from Excel to Database using ADO.net

I need to Append data from Excel to Database so I need to check for validation in Excel columns like if

dr[0]
is number or not and if any row in Excel is repeated in the database skip it and add only new records:

MaamoonKhalidIssueEntities db = new MaamoonKhalidIssueEntities();
foreach (DataTable table in result.Tables)
{
foreach (DataRow dr in table.Rows)
{
Person addtable = new Person()
{

nparent =Convert.ToInt32(dr[0]),
ncode = Convert.ToString(dr[1]),
nname = Convert.ToString(dr[2])

};
db.People.Add(addtable);
}


I Can't check on
dr[0]
because its object

Answer Source

You can write a ValidateNewPerson-Method. This method tries to fetch a person with the same ncode from the DB. If this succeeds, the record is not valid, if not, the person can be added:

public bool ValidateNewPerson(Person newPerson, MaamoonKhalidIssueEntities db)
{
    var dbPerson = db.People.Where(e => e.ncode == newPerson.ncode).FirstOrDefault();
    if (dbPerson == null)
        return true;
    else
        return false;
}

Now you can call this method in your foreach:

foreach (DataRow dr in table.Rows)
{
    Person addtable = new Person()
    {
        nparent =Convert.ToInt32(dr[0]),
        ncode = Convert.ToString(dr[1]),
        nname = Convert.ToString(dr[2])
    };
    if (ValidateNewPerson(addtable, db))
        db.People.Add(addtable);
 }        

If you want to use a Linq-Method, you can use Any, to request if any record in your db matches your criteria:

foreach (DataRow dr in table.Rows)
{
    Person addtable = new Person()
    {
        nparent =Convert.ToInt32(dr[0]),
        ncode = Convert.ToString(dr[1]),
        nname = Convert.ToString(dr[2])
    };
    if (!db.People.Any(p => p.ncode == addtable.ncode))
        db.People.Add(addtable);
 }        
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download