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 FinalGradeInstructorID) SELECT EduPlanID FinalGradeInstructorID 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 INTGradeTypeID 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 INTGradeTypeID 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 (DegreeAuditIDGradeTypeIDGrade) SELECT tsDegreeAudit.DegreeAuditIDTmpXML.GradeTypeID TmpXML.Grade FROM OPENXML(@hdoc'ROOT/ttGrade'2) WITH (DegreeAuditID INTGradeTypeID 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 (DegreeAuditIDGradeTypeIDGrade) SELECT tsDegreeAudit.DegreeAuditIDTmpXML.GradeTypeID TmpXML.Grade FROM OPENXML(@hdoc'ROOT/ttGrade'2) WITH (DegreeAuditID INTGradeTypeID 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 FinalGradeUserID) --SELECT EduPlanID FinalGradeUserID 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 INTGrade VARCHAR(5)Status INT)TmpXML Delete from tsWFCourse where tsWFCourse.DegreeAuditID in (Select DegreeAuditId From OPENXML(@hdoc'ROOT/tsWFCourse'2) WITH (DegreeAuditID INTGrade 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 INTGrade VARCHAR(5)Status INT)TmpXML where tsWFCourse.DegreeAuditID=TmpXML.DegreeAuditID And TmpXML.Status = 1 IF (@@error 0) GOTO Hell Insert into tsWFCourse(DegreeAuditIDGradeEntryDate) SELECT TmpXML.DegreeAuditIDTmpXML.GradeCast(GetDate() as varchar(25)) FROM OPENXML(@hdoc'ROOT/tsWFCourse'2) WITH (DegreeAuditID INTGrade 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'11) 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 tsFinalGradeLogsqlcon); // 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(FinalGradeLogIDEduplanIDUserIDChangeDateTimeFinalGrade)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(DegreeAuditIDEntryDateGrade) 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(@xmlStringDegreeAuditxmlStr.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(0500); 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 T P · 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