Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 220
  • Last Modified:

how to insert one row at a time from each table exits in dataset

I am reading one XML file and storing data in 5 different datable, now these are related to each other.
here is my req.
I want to insert one row of each table a time, so that one complete node can be inserted in to the database.
and also I want to make sure that if some records contains error then all the records before this records must be committed or permanently stored into the database.
right now i am using transaction and looping of each records some thing like shown below.
I want to know is there is any proper or good way to do this.
_transaction = _connection.BeginTransaction();
        try {
           // InsertAudit(sourceID);
 
            foreach(DataRow PArow in _dataset.Tables["ProductCategory"].Rows) {
                if (CheckRecords(PArow,"ProductCategory") == false) {
                    InsertProductCategory(PArow);
                }                
                foreach (DataRow Productrow in _dataset.Tables["Product"].Rows) {
                    if (PArow["OID"].ToString() == Productrow["PrductCatID"].ToString()){
                        if (CheckRecords(Productrow, "Product") == false) {
                            InsertProduct(Productrow);
                        }
                        foreach (DataRow IssueProductrow in _dataset.Tables["IssueProduct"].Rows) {
                            if (Productrow["OID"].ToString() == IssueProductrow["ProductID"].ToString()) {
                                if (CheckRecords(IssueProductrow, "IssueProduct") == false) {
                                    InsertIssueProduct(IssueProductrow);
                                } 
                            }
                        }
                    }
                }
            }
 
            foreach (DataRow Issuerow in _dataset.Tables["Issue"].Rows) {
                if (CheckRecords(Issuerow, "Issue") == false) {
                    InsertIssue(Issuerow);
                    foreach (DataRow IssueProductXIssuerow in _dataset.Tables["IssueProductXIssue"].Rows) {
                        if (Issuerow["OID"].ToString() == IssueProductXIssuerow["IssueID"].ToString()) {
                            InsertIssueProductXIssue(IssueProductXIssuerow);
                        }
                    }
                }                
            } 
           
            foreach (DataRow rowArticle in _dataset.Tables["Article"].Rows) {
                if (Convert.ToInt16(rowArticle["Inserted"]) == 1) {
                    CheckRecords(rowArticle, "Article");
                    InsertArticle(rowArticle);
                    
                    foreach (DataRow rowAV in _dataset.Tables["ArticleVersion"].Rows) {
                        if (rowArticle["OID"].ToString() == rowAV["ArticleID"].ToString()) {
                            CheckRecords(rowAV, "ArticleVersion");
                            InsertArticleVersion(rowAV);
                                                 
                            foreach (DataRow rowPA in _dataset.Tables["ProjectAudience"].Rows) {
                                if (rowAV["OID"].ToString() == rowPA["ArticleVersionID"].ToString()) {
                                    CheckRecords(rowPA, "ProjectAudience");
                                    InsertProjectAudience(rowPA);
                                }
                            }                       
                            foreach (DataRow rowAC in _dataset.Tables["ArticleContent"].Rows) {
                                if (rowAV["OID"].ToString() == rowAC["ArticleVersionID"].ToString()) {
                                    CheckRecords(rowAC, "ArticleContent");
                                    InsertArticleContent(rowAC);
                                }
                            }
                            foreach (DataRow rowImages in _dataset.Tables["Images"].Rows) {
                                if (rowAV["OID"].ToString() == rowImages["ArticleVersionID"].ToString()) {
                                    CheckRecords(rowImages, "Images");
                                    InsertImages(rowImages);
                                }
                            }      
                        }   
                    }      
                }
            }
            _transaction.Commit();
        } catch(Exception e) {
            _transaction.Rollback();
        }

Open in new window

0
ziorrinfotech
Asked:
ziorrinfotech
1 Solution
 
DabasCommented:
One problem that is apparent from the code above is that you have an all or nothing situation here:

One exception anywhere, will cause your whole transaction to roll back.
Is this what you want?

You are also trying to do too much in one method.
For example, I would suggest you move the handling of Product to another method, and then again, from there call the method that handles Issue Product.

You are also repeating the same code all the time. you probably can write just one method that accepts a table name and the condition that needs to be passed to CheckRecords and call that instead

Just a few suggestions as to what would be the proper and good way to do this in my opinion


Dabas
0
 
ziorrinfotechAuthor Commented:
no I want to insert one row of each table at a time.

how can i do this
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now