CREATE TABLE [dbo].[majikanAG_subMajikan_1](
	[idx] [int] IDENTITY(-2147483648,1) NOT NULL,
	[batch_Id] [uniqueidentifier] NOT NULL,
	[icNo (Baru)] [varchar](100) NULL,
	[icNo (Lama)] [varchar](100) NULL,
	[payerNme] [varchar](300) NULL,
	[zakatAmount] [decimal](10, 2) NULL,
	[subMajikan] [varchar](100) NULL,
 CONSTRAINT [PK__majikanA__51EFEBF8002AF460] PRIMARY KEY CLUSTERED 
(
	[idx] ASC,
	[batch_Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE PROCEDURE [dbo].[addAGSummary_SubMajikan_Process1]
@agItem xml,
@batch_Id uniqueidentifier output
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

set transaction isolation level repeatable read
Begin transaction

Begin Try

select @batch_Id=NEWID()

insert into majikanAG_subMajikan_1(batch_Id, [icNo (Baru)], [icNo (Lama)],
payerNme, zakatAmount, subMajikan)
select @batch_Id,
a.b.value('icNo[1]','varchar(200)') as icNo, --as input1,
a.b.value('icNoOld[1]','varchar(15)') as icNoOld, --as input2,
upper(a.b.value('payerNme[1]','varchar(100)')) as payerNme, --as input3,
--a.b.value('amt[1]','decimal(10,2)') as amt, --as input4,
a.b.value('amt[1]','varchar(100)') as amt, --as input4,
a.b.value('subCd[1]','varchar(100)') as subCd --as input5,
from
@agItem.nodes('/data/ag') a(b)

COMMIT transaction
End Try


Begin Catch
-- Whoops, there was an error
--IF @@TRANCOUNT > 0
ROLLBACK transaction

-- Raise an error with the details of the exception
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(),
@ErrSeverity = ERROR_SEVERITY()

RAISERROR(@ErrMsg, @ErrSeverity, 1)

End Catch
	
END

XDocument doc = new XDocument(
        new XDeclaration("1.0", "utf-8", "yes"),
                new XElement("data"));

        //Flag to skip header line of text file 
        bool isHeaderLine = true; 


        //string xmlData = "<data>";
        string _strFileLocation = MapPath("~/Content/AG-Persekutuan-Bln-09-2011.txt");
        string batch_Id = string.Empty;
        try
        {
            //System.Threading.Thread.Sleep(5000);
            //myAG objAG = new myAG();

            using (StreamReader sr = new StreamReader(_strFileLocation))
            {
                while (sr.Peek() >= 0)
                {
                    string currentLine = sr.ReadLine();

                    //Skip first line of file 
                    if (isHeaderLine)
                    {
                        isHeaderLine = false;
                        continue;
                    }

                    XElement xmlData = new XElement("ag",
                            new XElement("icNo", currentLine.Substring(20, 6) + "-" +
                            currentLine.Substring(26, 2) +
                            "-" + currentLine.Substring(28, 4)),
                            new XElement("icNoOld", currentLine.Substring(20, 12)),
                            new XElement("payerNme", currentLine.Substring(51, 40)),
                            new XElement("amt", currentLine.Substring(92, 9)),
                            new XElement("subCd", currentLine.Substring(6, 4))
                            );

                    doc.Element("data").Add(xmlData); 
                    
                }


            }

            
            SqlConnection con = new SqlConnection(SQLConn);
            con.Open();
            SqlCommand cmd = new SqlCommand("addAGSummary_SubMajikan_Process1", con);
            cmd.CommandTimeout = 20;
            cmd.CommandType = CommandType.StoredProcedure;

            //send value to sp
            cmd.Parameters.AddWithValue("@agItem", doc.ToString(SaveOptions.DisableFormatting));

            SqlParameter newSqlParam = new SqlParameter();
            newSqlParam.ParameterName = "@batch_Id";
            newSqlParam.SqlDbType = SqlDbType.UniqueIdentifier;
            newSqlParam.Direction = ParameterDirection.Output;
            cmd.Parameters.Add(newSqlParam);

            cmd.ExecuteNonQuery();
            batch_Id = cmd.Parameters["@batch_Id"].Value.ToString();


            //}

            //Response.Redirect("_longRunningResult.aspx?batch_Id=" + Server.UrlEncode(batch_Id) + "", false);
            lblMsg.Text = "Successful transactions....";
        }
        catch (Exception ex)
        { lblMsg.Text = ex.Message; }

cmd.Parameters.Add("@Value", SqlDbType.Xml).Value = doc.ToString();