USE OF SqlXml
//namespace
System.Xml;
//creating
instance of XmlDocument Class next Generating XmlNode and Element
XmlDocument docConfig = new XmlDocument();
XmlNode xmlNode = docConfig.CreateNode(XmlNodeType.XmlDeclaration, "", "");
XmlElement rootElement = docConfig.CreateElement("SCHEDULES");
docConfig.AppendChild(rootElement);
//this
function is place in loop to generate XML String
//Create_XML
function details is given below
this.Create_XML(docConfig, lblTimeID.Text.ToString(),
_sDate.AddDays(cd).ToString("yyyy-MM-dd"), _day1[i].ToString(), i.ToString(), "",
_subId[1].Trim(),"", _subId[0].ToString());
//after
completion of above loop we generate SqlXml instance that is understand by SQL
Sever.
StringWriter sw = new StringWriter();
XmlTextWriter tx = new XmlTextWriter(sw);
docConfig.WriteTo(tx);
StringReader transactionXml = new StringReader(sw.ToString());
XmlTextReader xmlReader = new XmlTextReader(transactionXml);
SqlXml sqlXml = new SqlXml(xmlReader);
psc.ScMainID
= scm_id;
psc.CollegeID
= _strSession[1];
psc.CreatedBy
= _strSession[2];
psc.TrackingID
= _strSession[3];
d.fnInsScheduleClassFromXML(psc,
sqlXml);
//Create_XML
function details;
private void Create_XML(XmlDocument docConfig, string CLASS_TIMING_ID, string DAY_DATE,
string DAY_NAME,
string DAY_NUMBER, string
FACULTY_BATCH_SUBJECT_ID, string FACULTY_ID,
string SUBJECT_TYPE, string SUBJECT_ID
)
{
Try
{
XmlElement hedder = docConfig.CreateElement("SCHEDULE");
docConfig.DocumentElement.PrependChild(hedder);
docConfig.ChildNodes.Item(0).AppendChild(hedder);
// Create
<installationid> Node
XmlElement environmentElement = docConfig.CreateElement("CLASS_TIMING_ID");
XmlText environText = docConfig.CreateTextNode(CLASS_TIMING_ID);
environmentElement.AppendChild(environText);
hedder.PrependChild(environmentElement);
//
Create <environment> Node
XmlElement environmentElement0 = docConfig.CreateElement("DAY_DATE");
XmlText environText0 = docConfig.CreateTextNode(DAY_DATE);
environmentElement0.AppendChild(environText0);
hedder.PrependChild(environmentElement0);
//
Create <environment> Node
XmlElement environmentElement1 = docConfig.CreateElement("DAY_NAME");
XmlText environText1 = docConfig.CreateTextNode(DAY_NAME);
environmentElement1.AppendChild(environText1);
hedder.PrependChild(environmentElement1);
//
Create <environment> Node
XmlElement environmentElement2 = docConfig.CreateElement("DAY_NUMBER");
XmlText environText2 = docConfig.CreateTextNode(DAY_NUMBER);
environmentElement2.AppendChild(environText2);
hedder.PrependChild(environmentElement2);
//
Create <environment> Node
XmlElement
environmentElement3 = docConfig.CreateElement("FACULTY_BATCH_SUBJECT_ID");
XmlText environText3 =
docConfig.CreateTextNode(FACULTY_BATCH_SUBJECT_ID);
environmentElement3.AppendChild(environText3);
hedder.PrependChild(environmentElement3);
//
Create <environment> Node
XmlElement environmentElement4 = docConfig.CreateElement("FACULTY_ID");
XmlText environText4 = docConfig.CreateTextNode(FACULTY_ID);
environmentElement4.AppendChild(environText4);
hedder.PrependChild(environmentElement4);
//
Create <environment> Node
XmlElement environmentElement5 = docConfig.CreateElement("SUBJECT_TYPE");
XmlText environText5 = docConfig.CreateTextNode(SUBJECT_TYPE);
environmentElement5.AppendChild(environText5);
hedder.PrependChild(environmentElement5);
//
Create <environment> Node
XmlElement environmentElement6 = docConfig.CreateElement("SUBJECT_ID");
XmlText environText6 = docConfig.CreateTextNode(SUBJECT_ID);
environmentElement6.AppendChild(environText6);
hedder.PrependChild(environmentElement6);
}
catch (Exception ex)
{
}
}
//PROC
that is used to insertion record in table by XML DataType String
CREATE PROC [dbo].[INS_COLLEGE_SCHEDULE_CLASS_XML]
(
@XML_SCHEDULE XML,
@SC_MAIN_ID NVARCHAR(100),
@COLLEGE_ID NVARCHAR(100),
@TRACKING_ID NVARCHAR(100),
@CREATED_BY NVARCHAR(100)
)
AS
BEGIN TRY
BEGIN TRAN
DECLARE @MAXID NVARCHAR(50)
DECLARE
@COUNT BIGINT
DECLARE
@NEWIDNO BIGINT
DECLARE
@NEWID NVARCHAR(50)
DECLARE
@NAME NVARCHAR(100)
BEGIN
--------------------------------------------------------------------------------------------------------
DECLARE
@CLASS_TIMING_ID NVARCHAR(100),
@DAY_DATE DATETIME,
@DAY_NAME NVARCHAR(100),
@DAY_NUMBER NVARCHAR(100),
@FACULTY_BATCH_SUBJECT_ID NVARCHAR(100),
@FACULTY_ID NVARCHAR(100),
@SUBJECT_TYPE NVARCHAR(100),
@SUBJECT_ID NVARCHAR(100)
DECLARE MY_DATA CURSOR FOR
SELECT
c.value('(./CLASS_TIMING_ID)[1]','VARCHAR(100)') AS CLASS_TIMING_ID,
c.value('(./DAY_DATE)[1]','VARCHAR(100)') AS DAY_DATE,
c.value('(./DAY_NAME)[1]','VARCHAR(100)') AS DAY_NAME,
c.value('(./DAY_NUMBER)[1]','VARCHAR(100)') AS DAY_NUMBER,
c.value('(./FACULTY_BATCH_SUBJECT_ID)[1]','VARCHAR(100)') AS
FACULTY_BATCH_SUBJECT_ID,
c.value('(./FACULTY_ID)[1]','VARCHAR(100)') AS FACULTY_ID,
c.value('(./SUBJECT_TYPE)[1]','VARCHAR(100)') AS SUBJECT_TYPE,
c.value('(./SUBJECT_ID)[1]','VARCHAR(100)') AS SUBJECT_ID
FROM
@XML_SCHEDULE.nodes('/SCHEDULES/SCHEDULE') AS T(c)
OPEN
MY_DATA
FETCH NEXT FROM MY_DATA INTO @CLASS_TIMING_ID,@DAY_DATE,@DAY_NAME,@DAY_NUMBER,@FACULTY_BATCH_SUBJECT_ID,
@FACULTY_ID,@SUBJECT_TYPE,@SUBJECT_ID
WHILE @@FETCH_STATUS =
0
BEGIN
---------------------------------------------------------------------------
SELECT
@COUNT =
COUNT(SCHEDULE_CLASS_ID),@MAXID = MAX(SCHEDULE_CLASS_ID)
FROM
COLLEGE_SCHEDULE_CLASS WHERE COLLEGE_ID=@COLLEGE_ID
IF
@COUNT>0
BEGIN
SELECT @MAXID = SUBSTRING(@MAXID,4,10)
SELECT @NEWIDNO =CONVERT(INT,@MAXID)+1
SELECT @MAXID = LTRIM(RTRIM(STR(@NEWIDNO)))
SELECT @NEWID = 'SCI'+ REPLICATE('0',10-LEN(@MAXID))+@MAXID
END
ELSE
BEGIN
SET
@NEWID = 'SCI' + '0000000001'
END
------------------------------------------------------------------------
INSERT INTO COLLEGE_SCHEDULE_CLASS(SCHEDULE_CLASS_ID,SC_MAIN_ID,CLASS_TIMING_ID,DAY_DATE,
DAY_NAME,DAY_NUMBER,FACULTY_BATCH_SUBJECT_ID,FACULTY_ID,SUBJECT_TYPE,
SUBJECT_ID,COLLEGE_ID,TRACKING_ID,
CREATED_BY,CREATED_DATE,UPDATED_DATE,DELETED_DATE,ACTIVE_FLAG,DELETED_FLAG
)
VALUES(@NEWID,@SC_MAIN_ID,@CLASS_TIMING_ID,@DAY_DATE,@DAY_NAME,@DAY_NUMBER,
@FACULTY_BATCH_SUBJECT_ID,@FACULTY_ID,@SUBJECT_TYPE,@SUBJECT_ID,
@COLLEGE_ID,@TRACKING_ID,@CREATED_BY,GETDATE(),GETDATE(),'1900-01-01','Y','N')
FETCH NEXT FROM MY_DATA INTO @CLASS_TIMING_ID,@DAY_DATE,@DAY_NAME,@DAY_NUMBER,@FACULTY_BATCH_SUBJECT_ID,
@FACULTY_ID,@SUBJECT_TYPE,@SUBJECT_ID
END
CLOSE
MY_DATA
DEALLOCATE
MY_DATA
------------------------------------------------------------------------------------------
COMMIT TRAN
END
END TRY
BEGIN CATCH
ROLLBACK TRAN
END CATCH
REQUIRMENT OF THIS CODE:
There is
challenge of inserting thousands of record in one click. And by this code we
achieve very easily without creating any heavy object.
No comments:
Post a Comment