We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

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

ziorrinfotech
on
Medium Priority
229 Views
Last Modified: 2013-12-17
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

Comment
Watch Question

CERTIFIED EXPERT
Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

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

how can i do this
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.