how to insert record using openxml

Question

how to insert data in databse  using openxml

veeru singh · Answer

it is my proc -------- alter PROCEDURE [dbo].[ppInsertUpdateGradeBatch_Portal] ( @intructorID int, @semesterID int, @courseID int , @xmlStringDegreeAudit ntext, @xmlStringGrade ntext , ---@Module varchar(15), @UserID varchar(50), @ChangeDateTime datetime, @xmlWF ntext , @xmlstringFinalGrade ntext , @StatementType nvarchar(20) = '' ) AS Begin DECLARE @hdoc int, @Return INTEGER SET @Return=0 ----------------Trans Open-------------------- BEGIN TRANSACTION --Insert/Update tsDegreeAudit Exec sp_xml_preparedocument @hdoc OUTPUT, @xmlStringDegreeAudit --IF @xmlStringDegreeAudit is null Or @xmlStringGrade is null --GOTO Hell Select * from OPENXML(@hdoc,'ROOT/tsDegreeAudit',2) WITH (DegreeAuditID INTEGER, EduPlanID INTEGER, FinalGrade VARCHAR(5), InstructorID INTEGER) tmpXML IF @StatementType = 'Update' begin UPDATE tsDegreeAudit SET tsDegreeAudit.FinalGrade = tmpXML.FinalGrade, tsDegreeAudit.InstructorID = tmpXML.InstructorID FROM OpenXML(@hDoc,'ROOT/tsDegreeAudit' , 2) WITH (DegreeAuditID INTEGER, EduPlanID INTEGER, FinalGrade VARCHAR(5), InstructorID INTEGER) tmpXML , tsDegreeAudit WHERE tsDegreeAudit.EduPlanID = tmpXML.EduPlanID And tmpXML.DegreeAuditID -1 END IF (@@error 0) GOTO Hell INSERT into tsDegreeAudit (EduPlanID, FinalGrade,InstructorID) SELECT EduPlanID, FinalGrade,InstructorID FROM OpenXML(@hDoc,'/ROOT/tsDegreeAudit', 2) WITH (DegreeAuditID INTEGER, EduPlanID INTEGER, FinalGrade VARCHAR(5),InstructorID INTEGER) tmpXML WHERE tmpXML.DegreeAuditID=-1 IF (@@error 0) GOTO Hell exec sp_xml_removedocument @hdoc --Insert/Update ttGrade Exec sp_xml_preparedocument @hdoc OUTPUT, @xmlStringGrade Select * FROM OpenXML(@hDoc,'ROOT/ttGrade', 2) WITH (DegreeAuditID INT,GradeTypeID VARCHAR(50),Grade VARCHAR(5),EduPlanID INT)TmpXML --Case 1 UPDATE ttGrade SET ttGrade.Grade=TmpXML.Grade FROM OPENXML(@hdoc,'ROOT/ttGrade',2) WITH (DegreeAuditID INT,GradeTypeID VARCHAR(50),Grade VARCHAR(5),EduPlanID INT)TmpXML WHERE ttGrade.DegreeAuditID=TmpXML.DegreeAuditID and ttGrade.GradeTypeID=TmpXML.GradeTypeID And TmpXML.DegreeAuditID -1 IF (@@error 0) GOTO Hell --Case 2 for Inserting Those Records which are in tsDegreeAudit But Not In ttGrade INSERT INTO ttGrade (DegreeAuditID,GradeTypeID,Grade) SELECT tsDegreeAudit.DegreeAuditID,TmpXML.GradeTypeID, TmpXML.Grade FROM OPENXML(@hdoc,'ROOT/ttGrade',2) WITH (DegreeAuditID INT,GradeTypeID VARCHAR(50),Grade VARCHAR(10),EduPlanID INT)TmpXML JOIN tsDegreeAudit on TmpXML.EduPlanID=tsDegreeAudit.EduPlanID WHERE TmpXML.DegreeAuditID -1 And TmpXML.DegreeAuditID in(select DA.DegreeAuditID from tsDegreeAudit DA) And TmpXML.DegreeAuditID not in(SELECT DG.DegreeAuditID from ttGrade DG) IF (@@error 0) GOTO Hell INSERT INTO ttGrade (DegreeAuditID,GradeTypeID,Grade) SELECT tsDegreeAudit.DegreeAuditID,TmpXML.GradeTypeID, TmpXML.Grade FROM OPENXML(@hdoc,'ROOT/ttGrade',2) WITH (DegreeAuditID INT,GradeTypeID VARCHAR(50),Grade VARCHAR(10),EduPlanID INT)TmpXML JOIN tsDegreeAudit on TmpXML.EduPlanID=tsDegreeAudit.EduPlanID WHERE TmpXML.DegreeAuditID=-1 IF (@@error 0) GOTO Hell --insert tsfinalgrade -- Exec sp_xml_preparedocument @hdoc OUTPUT, @xmlstringFinalGrade -- IF @xmlstringFinalGrade is null Or @xmlStringGrade is null -- GOTO Hell -- Select * -- from OPENXML(@hdoc,'ROOT/tsFinalGradeLog ',2) -- WITH (FinalGradeLogID INTEGER, EduPlanID INTEGER, FinalGrade VARCHAR(5), UserID varchar(50)) tmpXML -- UPDATE tsFinalGradeLog --SET tsFinalGradeLog.FinalGrade = tmpXML.FinalGrade, -- tsFinalGradeLog.UserID = tmpXML.UserID --FROM OpenXML(@hDoc,'ROOT/tsFinalGradeLog' , 2) --WITH (FinalGradeLogID INTEGER, EduPlanID INTEGER, FinalGrade VARCHAR(5), UserID varchar(50),ChangeDateTime datetime) tmpXML , tsFinalGradeLog --WHERE tsFinalGradeLog.EduPlanID = tmpXML.EduPlanID And tmpXML.FinalGradeLogID -1 --IF (@@error 0) -- GOTO Hell --INSERT into tsFinalGradeLog (EduPlanID, FinalGrade,UserID) --SELECT EduPlanID, FinalGrade,UserID FROM OpenXML(@hDoc,'ROOT/tsFinalGradeLog', 2) --WITH (FinalGradeLogID INTEGER, EduPlanID INTEGER, FinalGrade VARCHAR(5),UserID varchar(50),ChangeDateTime datetime) tmpXML --WHERE tmpXML.FinalGradeLogID=-1 --IF (@@error 0) exec sp_xml_removedocument @hdoc --Insert/Update/Delete tsWFCourse Exec sp_xml_preparedocument @hdoc OUTPUT, @xmlWF Select * FROM OpenXML(@hDoc,'ROOT/tsWFCourse', 2) WITH (DegreeAuditID INT,Grade VARCHAR(5),Status INT)TmpXML Delete from tsWFCourse where tsWFCourse.DegreeAuditID in (Select DegreeAuditId From OPENXML(@hdoc,'ROOT/tsWFCourse',2) WITH (DegreeAuditID INT,Grade VARCHAR(5),Status INT) TmpXML Where TmpXML.Status=2) IF (@@error 0) GOTO Hell Update tsWFCourse set tsWFCourse.Grade=TmpXML.Grade FROM OPENXML(@hdoc,'ROOT/tsWFCourse',2) WITH (DegreeAuditID INT,Grade VARCHAR(5),Status INT)TmpXML where tsWFCourse.DegreeAuditID=TmpXML.DegreeAuditID And TmpXML.Status = 1 IF (@@error 0) GOTO Hell Insert into tsWFCourse(DegreeAuditID,Grade,EntryDate) SELECT TmpXML.DegreeAuditID,TmpXML.Grade,Cast(GetDate() as varchar(25)) FROM OPENXML(@hdoc,'ROOT/tsWFCourse',2) WITH (DegreeAuditID INT,Grade VARCHAR(5),Status INT)TmpXML Where TmpXML.Status = 0 IF (@@error 0) GOTO Hell exec sp_xml_removedocument @hdoc --------------------------- if @@error=0 begin Commit Transaction Return 0 end Hell: RollBack transaction RaisError ('Error Occured',1,1) Return 1 End it is my c# coddng ------ private void dggrade_ItemDataBound(object sender, System.Web.UI.WebControls.DataGridItemEventArgs e) { if (e.Item.ItemType==ListItemType.AlternatingItem | e.Item.ItemType== ListItemType.Item) { ((DropDownList)(e.Item.Cells[6].FindControl("dgquarter"))).DataSource=DS; ((DropDownList)(e.Item.Cells[6].FindControl("dgquarter"))).DataTextField="Grade"; ((DropDownList)(e.Item.Cells[6].FindControl("dgquarter"))).DataBind(); ((DropDownList)(e.Item.Cells[6].FindControl("dgquarter"))).Items.Insert(0,""); ((DropDownList)(e.Item.Cells[6].FindControl("dgquarter"))).Items.FindByText(((Label)(e.Item.Cells[6].FindControl("lblqua"))).Text).Selected=true; ((DropDownList)(e.Item.Cells[7].FindControl("dgMid"))).DataSource=DS; ((DropDownList)(e.Item.Cells[7].FindControl("dgMid"))).DataTextField="Grade"; ((DropDownList)(e.Item.Cells[7].FindControl("dgMid"))).DataBind(); ((DropDownList)(e.Item.Cells[7].FindControl("dgMid"))).Items.Insert(0,""); ((DropDownList)(e.Item.Cells[7].FindControl("dgMid"))).Items.FindByText(((Label)(e.Item.Cells[7].FindControl("lblqua4"))).Text).Selected=true; ((DropDownList)(e.Item.Cells[8].FindControl("dgtwel"))).DataSource=DS; ((DropDownList)(e.Item.Cells[8].FindControl("dgtwel"))).DataTextField="Grade"; ((DropDownList)(e.Item.Cells[8].FindControl("dgtwel"))).DataBind(); ((DropDownList)(e.Item.Cells[8].FindControl("dgtwel"))).Items.Insert(0,""); ((DropDownList)(e.Item.Cells[8].FindControl("dgtwel"))).Items.FindByText(((Label)(e.Item.Cells[8].FindControl("lblqua12"))).Text).Selected=true; ((DropDownList)(e.Item.Cells[9].FindControl("dgfinal"))).DataSource=DS; ((DropDownList)(e.Item.Cells[9].FindControl("dgfinal"))).DataTextField="Grade"; ((DropDownList)(e.Item.Cells[9].FindControl("dgfinal"))).DataBind(); ((DropDownList)(e.Item.Cells[9].FindControl("dgfinal"))).Items.Insert(0,""); ((DropDownList)(e.Item.Cells[9].FindControl("dgfinal"))).Items.FindByText(((Label)(e.Item.Cells[9].FindControl("lblquaf"))).Text).Selected=true; } } #region Web Form Designer generated code override protected void OnInit(EventArgs e) { // // CODEGEN: This call is required by the ASP.NET Web Form Designer. // InitializeComponent(); base.OnInit(e); } /// /// Required method for Designer support - do not modify /// the contents of this method with the code editor. /// private void InitializeComponent() { this.dggrade.ItemDataBound += new System.Web.UI.WebControls.DataGridItemEventHandler(this.dggrade_ItemDataBound); this.BtnSubmit.Click += new System.Web.UI.ImageClickEventHandler(this.BtnSubmit_Click); } #endregion private void BtnSubmit_Click(object sender, System.Web.UI.ImageClickEventArgs e) { SqlConnection sqlcon = Dbobject.GetConnection(); int i; string DegreeAuditID = "", InstructorID; InstructorID = Session["UserID"].ToString(); string EduPlanID=""; string FinalGrade=""; // string FinalGradeLogID = "", UserID; string ChangeDateTime = DateTime.Now.ToString(); // UserID = Session["UserID"].ToString(); System.Text.StringBuilder xmlStr = new System.Text.StringBuilder(); System.Text.StringBuilder xmlgradestr = new System.Text.StringBuilder(); System.Text.StringBuilder xmlwf = new System.Text.StringBuilder(); System.Text.StringBuilder xmlstringFinalGrade = new System.Text.StringBuilder(); xmlStr.Append(" "); for (i = 0; i { DegreeAuditID = dggrade.Items[i].Cells[1].Text; if (DegreeAuditID == " ") { DegreeAuditID = "-1"; } // if (DegreeAuditID.ToString()=="0") // { // DegreeAuditID =dggrade.Items[i].Cells[0].Text; // } // xmlStr.Append(" " + DegreeAuditID.ToString() + " " + dggrade.Items[i].Cells[0].Text + " " + ((DropDownList)(dggrade.Items[i].Cells[9].FindControl("dgfinal"))).SelectedItem.Text + " " + Session["UserID"].ToString() + " "); // xmlStr.Append(" " + dggrade.Items[i].Cells[0].Text + " " + DegreeAuditID.ToString() + " " + "DAG00701" + " " + ((DropDownList)(dggrade.Items[i].Cells[6].FindControl("dgquarter"))).SelectedItem.Text + " ") ; // xmlStr.Append(" " + dggrade.Items[i].Cells[0].Text + " " + DegreeAuditID.ToString() + " " + "DAG00702" + " " + ((DropDownList)(dggrade.Items[i].Cells[7].FindControl("dgMid"))).SelectedItem.Text + " ") ; // xmlStr.Append(" " + dggrade.Items[i].Cells[0].Text + " " + DegreeAuditID.ToString() + " " + "DAG00703" + " " + ((DropDownList)(dggrade.Items[i].Cells[8].FindControl("dgtwel"))).SelectedItem.Text + " ") ; // xmlStr.Append(" " + dggrade.Items[i].Cells[0].Text + " "); xmlStr.Append(" " + DegreeAuditID.ToString() + " "); xmlStr.Append(" " + InstructorID + " "); xmlStr.Append(" " + ((DropDownList)(dggrade.Items[i].Cells[9].FindControl("dgfinal"))).SelectedItem.Text + " "); //xmlStr.Append(" " + ((DropDownList)(dggrade.Items[i].Cells[7].FindControl("dgMid"))).SelectedItem.Text + " "); // xmlStr.Append(" " + ((DropDownList)(dggrade.Items[i].Cells[8].FindControl("dgtwel"))).SelectedItem.Text + " "); } //*******added bydharmendra for issue track change grade ---------********// xmlstringFinalGrade.Append(" "); for (i = 0; i { EduPlanID = dggrade.Items[i].Cells[0].Text; FinalGrade = ((DropDownList)(dggrade.Items[i].Cells[9].FindControl("dgfinal"))).SelectedItem.Text; if (EduPlanID == "i") { EduPlanID = "i"; } sqlcon.Open(); { string module = "WebPortal"; //System.Text.StringBuilder xmlstringFinalGrade = new System.Text.StringBuilder(); SqlCommand cmd = new SqlCommand("finalgrade", sqlcon); //SqlCommand cmd1 = new SqlCommand("select MAX(FinalGradeLogID) from tsFinalGradeLog",sqlcon); // object FinalGradeLogID = Convert.ToInt32(cmd1.ExecuteScalar()); cmd.CommandType = CommandType.StoredProcedure; // cmd.Parameters.Add("@FinalGradeLogID", Convert.ToInt32(FinalGradeLogID)+1); cmd.Parameters.Add("@EduplanID", EduPlanID.ToString()); //SqlCommand cmd2 = new SqlCommand("insert into tsFinalGradeLog(FinalGradeLogID,EduplanID,UserID,ChangeDateTime,FinalGrade)values('"+FinalGradeLogID.ToString()+"','"+dggrade.Items[i].Cells[0].Text+"','"+Session["FacultyName"].ToString()+"','"+DateTime.Now.ToString()+"','"+((DropDownList)(dggrade.Items[i].Cells[9].FindControl("dgfinal"))).SelectedItem.Text+"'"); cmd.Parameters.Add("@Module", module.ToString()); cmd.Parameters.AddWithValue("@StatementType", "Insert"); cmd.Parameters.Add("@UserID", Session["FacultyName"].ToString()); cmd.Parameters.Add("@ChangeDateTime", DateTime.Now.ToString()); if (string.IsNullOrEmpty(FinalGrade)) cmd.Parameters.Add("FinalGrade", DBNull.Value); else cmd.Parameters.Add("@FinalGrade", FinalGrade.ToString()); cmd.ExecuteNonQuery(); sqlcon.Close(); } } xmlStr.Append(" "); //**********Added by Pawan for Issue 10784 on 29-DEC-2007************** xmlgradestr.Append(" "); for (i = 0; i { DegreeAuditID = dggrade.Items[i].Cells[1].Text; if (DegreeAuditID == " ") { DegreeAuditID = "-1"; } xmlgradestr.Append(" " + dggrade.Items[i].Cells[0].Text + " " + DegreeAuditID.ToString() + " " + "DAG00701" + " " + ((DropDownList)(dggrade.Items[i].Cells[6].FindControl("dgquarter"))).SelectedItem.Text + " "); xmlgradestr.Append(" " + dggrade.Items[i].Cells[0].Text + " " + DegreeAuditID.ToString() + " " + "DAG00702" + " " + ((DropDownList)(dggrade.Items[i].Cells[7].FindControl("dgMid"))).SelectedItem.Text + " "); xmlgradestr.Append(" " + dggrade.Items[i].Cells[0].Text + " " + DegreeAuditID.ToString() + " " + "DAG00703" + " " + ((DropDownList)(dggrade.Items[i].Cells[8].FindControl("dgtwel"))).SelectedItem.Text + " "); // xmlgradestr.Append(" " + "DAG00701" + " "+((DropDownList)(dggrade.Items[i].Cells[6].FindControl("dgquarter"))).SelectedItem.Text + " "); // xmlgradestr.Append(" " + "DAG00702" + " "+((DropDownList)(dggrade.Items[i].Cells[7].FindControl("dgquarter"))).SelectedItem.Text + " "); // xmlgradestr.Append(" " + "DAG00703" + " "+((DropDownList)(dggrade.Items[i].Cells[8].FindControl("dgquarter"))).SelectedItem.Text + " "); } xmlgradestr.Append(" "); //PA***********Insert =0; Update=1;Delete=2************** xmlwf.Append(" "); SqlConnection scon = Dbobject.GetConnection(); scon.Open(); for (int j = 0; j { // DegreeAuditID=dggrade.Items[i].Cells[1].Text; // if(DegreeAuditID == " ") // { // DegreeAuditID = "-1"; // } string dgid = dggrade.Items[j].Cells[1].Text; if (dgid == " ") { dgid = "-1"; } DropDownList ddl = (DropDownList)(dggrade.Items[j].Cells[9].FindControl("dgFinal")); string str = ddl.SelectedItem.Text; if (ddl.SelectedItem.Text != "") { SqlCommand cmdrd = new SqlCommand("select * from tsWFCourse where DegreeAuditID='" + dgid + "'", scon); //cmd.CommandText="select * from tsWFCourse where DegreeAuditID='"+dgid+"'"; SqlDataReader sdr = cmdrd.ExecuteReader(); if (sdr.Read()) { if (sdr[5].ToString() == "W" || sdr[5].ToString() == "F") { if (ddl.SelectedItem.Text == "W" || ddl.SelectedItem.Text == "F") { } else { xmlwf.Append(" " + dgid + " " + ddl.SelectedItem.Text + " " + 2 + " "); // cmdwf.CommandText="delete from tsWFCourse where DegreeAuditId='"+dgid+"'"; // cmdwf.ExecuteNonQuery(); } } if (ddl.SelectedItem.Text == "W" || ddl.SelectedItem.Text == "F") { xmlwf.Append(" " + dgid + " " + ddl.SelectedItem.Text + " " + 1 + " "); // cmdwf.CommandText="update tsWFCourse set Grade='"+ddl.SelectedItem.Text+"' where DegreeAuditId='"+dgid+"'"; // cmdwf.ExecuteNonQuery(); } } else { if (ddl.SelectedItem.Text == "W" || ddl.SelectedItem.Text == "F") { xmlwf.Append(" " + dgid + " " + ddl.SelectedItem.Text + " " + 0 + " "); // cmdwf.CommandText="Insert into tsWFCourse(DegreeAuditID,EntryDate,Grade) values('"+dgid+"','"+DateTime.Now.ToString()+"','"+ddl.SelectedItem.Text+"')"; // cmdwf.ExecuteNonQuery(); } } sdr.Close(); } //****************************************** } scon.Close(); sqlcon.Open(); { xmlwf.Append(" "); //************************************************** int NumberOfAttempts = 0; // string StrQry = "ppInsertUpdateGradeBatch_Portal " + Session["UserID"].ToString() + "," + Session["cSemesterID"] + "," + Session["cCourseID"] + ",'" + xmlStr.ToString() + "','" + xmlgradestr.ToString() + "','" + xmlwf.ToString() + "'"; string StrQry = "ppInsertUpdateGradeBatch_Portal " + Session["UserID"].ToString() + "," + Session["cSemesterID"] + "," + Session["cCourseID"] + ",'" + xmlStr.ToString() + "','" + xmlstringFinalGrade.ToString() + "','" + xmlgradestr.ToString() + "'"; do { try { SqlParameter prm1 = new SqlParameter("@xmlStringDegreeAudit",xmlStr.ToString()); prm1.Direction = ParameterDirection.ReturnValue; SqlCommand cmd = new SqlCommand(StrQry, sqlcon); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(prm1); cmd.CommandText = "ppInsertUpdateGradeBatch_Portal"; if ((int)i == 0) { //lblSavedInfo.Visible=true; lblErr.Visible = false; } else { lblErr.Text = "Server is busy, please try later."; lblErr.Visible = true; lblSavedInfo.Visible = false; } break; lblSavedInfo.Visible = true; lblErr.Visible = false; break; } catch (Exception ex) { try { NumberOfAttempts++; LogError(ex.Message + " [" + ex.ToString() + "]"); int iRnds = 0; Random rnd = new Random(); iRnds = rnd.Next(500, 3000); WaitFor(iRnds); if (NumberOfAttempts >= 5) { lblErr.Text = "Server is busy, please try later."; lblErr.Visible = true; lblSavedInfo.Visible = false; break; } } catch (SqlException ex1) { lblErr.Text = "Server is busy, please try later."; lblErr.Visible = true; lblSavedInfo.Visible = false; break; } } } while (true); sqlcon.Close(); if (fieldcount == 0) { Response.Redirect("Batch_Grade_Conf.aspx"); } //FillGrid(); // } } protected void dggrade_SelectedIndexChanged(object sender, System.EventArgs e) { } private void Button1_Click(object sender, System.EventArgs e) { Response.Redirect("Batch_Grade.aspx"); } private void WaitFor(int mSec) { System.Threading.Thread.Sleep(mSec); } private void LogError(string Msg) { if (Msg.Length>500) Msg=Msg.Substring(0,500); Dbobject.ExecuteNonQry("Insert into ErrorLog values ('WebPortal faculty grade.', '" + DateTime.Now.ToString("MM/dd/yyyy hh:mm:ss") + "', '" + Msg + "')", CommandType.Text); }

veeru singh · Answer

sir i have some coding but not insert update data can you solve it

Midhun Tp · Answer

Hi, Please follow below articles- http://www.c-sharpcorner.com/uploadfile/c3a146/insert-bulk-data-into-sql-server-in-asp-net-using-openxml/ http://www.dotnetfunda.com/articles/show/2746/insert-update-using-openxml-in-sql-server

how to insert record using openxml

Answers (3)