how to insert record using openxml

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) = ''

)

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 <= dggrade.Items.Count - 1; 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 <= dggrade.Items.Count - 1; 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 <= dggrade.Items.Count - 1; 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 <= dggrade.Items.Count - 1; 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() + "'";

{

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);

}

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

veeru singh10y ago
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 <= dggrade.Items.Count - 1; 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 <= dggrade.Items.Count - 1; 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 <= dggrade.Items.Count - 1; 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 <= dggrade.Items.Count - 1; 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 singh10y ago
sir i have some coding but not insert update data can you solve it
Midhun Tp10y ago
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