- <DATA>
- <VALUE>
<ROLL_NO>1</ROLL_NO>
<STUDENTNAME>mangal</STUDENTNAME>
<SYSTEM_NO>2</SYSTEM_NO>
<PASSWORD>smsj</PASSWORD>
<EMAIL_ID>h</EMAIL_ID>
<BATCH>2K13</BATCH>
</VALUE>
- <VALUE>
<ROLL_NO>12</ROLL_NO>
<STUDENTNAME>sdfgh</STUDENTNAME>
<SYSTEM_NO>123</SYSTEM_NO>
<PASSWORD>dfg</PASSWORD>
<EMAIL_ID>sdf</EMAIL_ID>
<BATCH>2K13</BATCH>
</VALUE>
- <VALUE>
<ROLL_NO>3</ROLL_NO>
<STUDENTNAME>23</STUDENTNAME>
<SYSTEM_NO>12</SYSTEM_NO>
<PASSWORD>3456df</PASSWORD>
<EMAIL_ID>g</EMAIL_ID>
<BATCH>2K13</BATCH>
</VALUE>
</DATA>
this is my xml code..
private void btnSaveData_Click(object sender, EventArgs e)
{
try
{
if (GrdStudentDetail.Rows.Count == 0)
{
MessageBox.Show("Nothing To Save");
return;
}
string Batch = cbxCreateBatch.Text;
string LoginTable = Batch.Insert(0, "STUDENTLOGINDETAIL");
BUSINESSLAYER objBUSINESSLAYER = new BUSINESSLAYER();
string ErrMsg = "";
int Ctr = 0;
int[] arrRollNo = new int[1];
string[] arrStudentName = new string[1];
int[] arrSystemNo = new int[1];
string[] arrPassword = new string[1];
string[] arrEmailId = new string[1];
string[] arrBatch = new string[1];
for (int i = 0; i < GrdStudentDetail.Rows.Count - 1; i++)
{
if (GrdStudentDetail.Rows[i].Cells[0].Value.ToString() != "" && GrdStudentDetail.Rows[i].Cells[1].Value.ToString() != "" && GrdStudentDetail.Rows[i].Cells[2].Value.ToString() != "" && GrdStudentDetail.Rows[i].Cells[4].Value.ToString() != "")
{
if (Ctr > 0)
{
Array.Resize(ref arrRollNo, arrRollNo.Length + 1);
Array.Resize(ref arrStudentName, arrStudentName.Length + 1);
Array.Resize(ref arrSystemNo, arrSystemNo.Length + 1);
Array.Resize(ref arrPassword, arrPassword.Length + 1);
Array.Resize(ref arrEmailId, arrEmailId.Length + 1);
Array.Resize(ref arrBatch, arrBatch.Length + 1);
}
arrRollNo[i] = Int32.Parse(GrdStudentDetail.Rows[i].Cells[0].Value.ToString());
arrStudentName[i] = GrdStudentDetail.Rows[i].Cells[1].Value.ToString();
arrSystemNo[i] = Int32.Parse(GrdStudentDetail.Rows[i].Cells[2].Value.ToString());
arrPassword[i] = GrdStudentDetail.Rows[i].Cells[3].Value.ToString();
arrEmailId[i] = GrdStudentDetail.Rows[i].Cells[4].Value.ToString();
arrBatch[i] = GrdStudentDetail.Rows[i].Cells[5].Value.ToString();
Ctr++;
}
else
if (i == GrdStudentDetail.Rows.Count - 1)
ErrMsg = "";
else
ErrMsg = "Please Fill All Fields ..";
}
if (ErrMsg != "")
MessageBox.Show(ErrMsg);
else
{
string[] xmlFieldNames = new string[6];
xmlFieldNames[0] = "ROLL_NO";
xmlFieldNames[1] = "STUDENTNAME";
xmlFieldNames[2] = "SYSTEM_NO";
xmlFieldNames[3] = "PASSWORD";
xmlFieldNames[4] = "EMAIL_ID";
xmlFieldNames[5] = "BATCH";
string[,] Values = new string[Ctr, 6];
for (int i = 0; i < Ctr; i++)
{
Values[i, 0] = arrRollNo[i].ToString();
Values[i, 1] = arrStudentName[i];
Values[i, 2] = arrSystemNo[i].ToString();
Values[i, 3] = arrPassword[i];
Values[i, 4] = arrEmailId[i];
Values[i, 5] = cbxCreateBatch.Text;
}
XMLDOCUMENTATION objXMLDOCUMENTATION = new XMLDOCUMENTATION();
XmlDocument doc1 = objXMLDOCUMENTATION.BuildStringToXml(Values, xmlFieldNames, Ctr);
Hashtable hashtable = new Hashtable();
hashtable.Add("@XMLDATA", doc1.DocumentElement.OuterXml);
string S = doc1.DocumentElement.OuterXml.ToString();
hashtable.Add("@LOGINTABLE", LoginTable);
string Output = objBUSINESSLAYER.ExecuteProcedure("BULKINSERTSUDENTDETAIL", hashtable);
MessageBox.Show(Output);
if (Output == "SUCCESS")
{
GrdStudentDetail.Visible = false;
btnSaveData.Visible = false;
gbxUpdPassword.Visible = true;
dt = objBUSINESSLAYER.SqlDataAdapterQuery("Select * from " + LoginTable);
GrdStudentDetail.DataSource = dt;
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
}
this is my C sharp code...
and below is my xml file creating function
public XmlDocument BuildStringToXml(string[,] arrXmlData, string[] FieldName, int RowsCount)
{
XmlDocument doc = new XmlDocument();
XmlNode docNode = doc.CreateXmlDeclaration("1.0","UTF-8", null);
doc.AppendChild(docNode);
XmlNode productsNode = doc.CreateElement("DATA");
doc.AppendChild(productsNode);
for (int i = 0; i < RowsCount; i++)
{
XmlNode productNode = doc.CreateElement("VALUE");
productsNode.AppendChild(productNode);
for (int j = 0; j < FieldName.Length; j++)
{
XmlNode nameNode = doc.CreateElement(FieldName[j]);
nameNode.AppendChild(doc.CreateTextNode(arrXmlData[i, j]));
productNode.AppendChild(nameNode);
}
}
return doc;
}
and below the function for add parameter is below
public String ExecuteProcedure(string ProcedureName, Hashtable hashtable)
{
OpenConnection();
cmd = new SqlCommand(ProcedureName, con);
cmd.CommandType = CommandType.StoredProcedure;
foreach (DictionaryEntry entry in hashtable)
{
Type KeyType = entry.Value.GetType();
if (KeyType.Name == "Int32")
cmd.Parameters.Add(entry.Key.ToString(), SqlDbType.Int).Value = Int32.Parse(entry.Value.ToString());
if (KeyType.Name == "String")
cmd.Parameters.Add(entry.Key.ToString(), SqlDbType.VarChar, 250).Value = entry.Value.ToString();
if (KeyType.Name == "XmlDocument")
cmd.Parameters.Add(entry.Key.ToString(), SqlDbType.Xml).Value = entry.Value.ToString();
}
cmd.Parameters.Add("@OUT", SqlDbType.VarChar, 200).Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
string Output = cmd.Parameters[cmd.Parameters.Count - 1].Value.ToString();
CloseConnection();
return Output;
}
and sql procedure is ...
GO
ALTER PROCEDURE BULKINSERTSUDENTDETAIL
(
@XMLDATA XML,
@LOGINTABLE VARCHAR(25),
@OUT VARCHAR(1000) OUTPUT
)
AS
BEGIN
SET @OUT = ' ';
DECLARE @XRECCOUNT INT;
DECLARE @COUNT INT;
create TABLE XMLDATA_TABLE
(
ROLL_NO INT,
STUDENTNAME VARCHAR(25),
SYSTEM_NO INT,
PASSWORD VARCHAR(20),
EMAIL_ID VARCHAR(40),
BATCH VARCHAR(5)
);
DECLARE @SQL NVARCHAR(2000);
DECLARE @XROLL_NO INT;
DECLARE @XSTUDENTNAME VARCHAR(25);
DECLARE @XSYSTEM_NO INT;
DECLARE @XPASSWORD VARCHAR(20);
DECLARE @XEMAIL_ID VARCHAR(40);
DECLARE @XBATCH VARCHAR(5);
print convert(nvarchar(max),@xmldata);
INSERT INTO XMLDATA_TABLE(ROLL_NO, STUDENTNAME, SYSTEM_NO, PASSWORD,EMAIL_ID, BATCH)
SELECT PARAMVALUES.value.query('ROLL_NO').value('.','INT') ROLL_NO,
PARAMVALUES.value.query('STUDENTNAME').value('.','NVARCHAR(25)') STUDENTNAME,
PARAMVALUES.value.query('SYSTEM_NO').value('.','INT') SYSTEM_NO,
PARAMVALUES.value.query('PASSWORD').value('.','NVARCHAR(20)') PASSWORD,
PARAMVALUES.value.query('EMAIL_ID').value('.','NVARCHAR(40)') EMAIL_ID,
PARAMVALUES.value.query('BATCH').value('.','NVARCHAR(5)') BATCH
FROM @XMLDATA.nodes('/DATA/VALUE') AS PARAMVALUES(value) ;
SET @COUNT = (SELECT COUNT (*) FROM XMLDATA_TABLE);
IF @COUNT = 0
BEGIN
SET @OUT ='AN ERROR OCCURED';
RETURN;
END;
ELSE
BEGIN
BEGIN TRANSACTION
SET @SQL =('INSERT INTO '+ @LOGINTABLE+'(ROLL_NO,STUDENTNAME,SYSTEM_NO,PASSWORD,EMAIL_ID,BATCH)
SELECT ROLL_NO,STUDENTNAME,SYSTEM_NO,PASSWORD,EMAIL_ID,BATCH FROM XMLDATA_TABLE');
EXEC SP_EXECUTESQL @SQL,N'@XMLDATA_@LOGINTABLE VARCHAR(30)',@LOGINTABLE;
drop table xmldata_table;
COMMIT;
set @out = 'SUCCESS';
END;
END;
GO
Now i got the xml parsing line 1 character 250 .unexpected end of input ..
please solve the problem ...
thanks in advance