private void ReadFile()
        {
            DataTable hallSeatdt = new DataTable();
            hallSeatdt.Columns.AddRange(new DataColumn[HallSeatVarCount] {
                new DataColumn("HallGroupID", typeof(int)),
                new DataColumn("ShowSeatID", typeof(int)),
                new DataColumn("Color", typeof(string)),
                new DataColumn("Price", typeof(double)),
                new DataColumn("SeatRow", typeof(int)),
                new DataColumn("SeatNumber", typeof(int)),
                new DataColumn("IsReserved", typeof(int))
            });

            DataTable hallGroupdt = new DataTable();
            hallGroupdt.Columns.AddRange(new DataColumn[HallGroupVarCount] {
                new DataColumn("HallID", typeof(int)),
                new DataColumn("HallGroupID", typeof(int)),
                new DataColumn("Name", typeof(string)),
                new DataColumn("AZ", typeof(int))
            });

            DataTable halldt = new DataTable();
            halldt.Columns.AddRange(new DataColumn[HallVarCount] {
                new DataColumn("HallID", typeof(int)),
                new DataColumn("Name", typeof(string)),
                new DataColumn("TicketLimit", typeof(int))
            });

            XmlDocument doc = new XmlDocument();
            doc.Load(FileUpload.PostedFile.InputStream);
            try
            {
                foreach (XmlNode node in doc.DocumentElement.ChildNodes)
                {
                    halldt.Rows.Add(Convert.ToInt32(node.ChildNodes[0].InnerText),
                                            node.ChildNodes[1].InnerText,
                            Convert.ToInt32(node.ChildNodes[2].InnerText));
                    for (int i = HallVarCount; i < node.ChildNodes.Count; i++)
                    {
                        hallGroupdt.Rows.Add(Convert.ToInt32(node.ChildNodes[i].ChildNodes[0].InnerText),
                            Convert.ToInt32(node.ChildNodes[i].ChildNodes[1].InnerText),
                                            node.ChildNodes[i].ChildNodes[2].InnerText,
                            Convert.ToInt32(node.ChildNodes[i].ChildNodes[3].InnerText));

                        for (int j = HallGroupVarCount; j < node.ChildNodes[i].ChildNodes.Count; j++)
                        {
                            hallSeatdt.Rows.Add(
                                Convert.ToInt32(node.ChildNodes[i].ChildNodes[j].ChildNodes[1].InnerText),
                                Convert.ToInt32(node.ChildNodes[i].ChildNodes[j].ChildNodes[0].InnerText),
                                                node.ChildNodes[i].ChildNodes[j].ChildNodes[2].InnerText,
                                Convert.ToDouble(node.ChildNodes[i].ChildNodes[j].ChildNodes[3].InnerText),
                                Convert.ToInt32(node.ChildNodes[i].ChildNodes[j].ChildNodes[4].InnerText),
                                Convert.ToInt32(node.ChildNodes[i].ChildNodes[j].ChildNodes[6].InnerText),
                                Convert.ToBoolean(node.ChildNodes[i].ChildNodes[j].ChildNodes[8].InnerText) ? 1 : 0
                                );
                        }
                    }

                }
            }
            catch (Exception ex)
            {
                ViewState["Error"] = "Unexpected file content. " + ex.Message;
                return;
            }

            Connection.Open();
            SqlCommand cmd = new SqlCommand("InsertHalls", Connection);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@Halls", halldt);
            cmd.ExecuteNonQuery();

            cmd = new SqlCommand("InsertHallGroups", Connection);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@HallGroups", hallGroupdt);
            cmd.ExecuteNonQuery();

            cmd = new SqlCommand("InsertHallSeats", Connection);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@HallSeats", hallSeatdt);
            cmd.ExecuteNonQuery();

            Connection.Close();

            ViewState["Success"] = "Inserted to DB succesfully";
        }


/* ------------------------------------------------------------------------------------------------------------------------------------ */

CREATE TYPE HallType AS TABLE
(
HallID int,
Name varchar(100),
TicketLimit int
)
Go

CREATE TYPE HallGroupType AS TABLE
(
HallID int,
HallGroupID int,
Name varchar(100),
AZ int
)
Go

CREATE TYPE HallSeatType AS TABLE
(
.....
)
Go
/* ------------------------------------------------------------------------------------------------------------------------------------ */
CREATE PROCEDURE [InsertHallGroups]
@HallGroups HallGroupType Readonly
AS
	SET IDENTITY_INSERT HallGroup ON
BEGIN
	MERGE HallGroup AS target
	USING (
		SELECT h.HallID, h.HallGroupID, h.[Name], h.AZ
		FROM @HallGroups h
	) AS SOURCE(HallID, HallGroupId, [Name], AZ)
	ON (target.HallGroupID = source.hallGroupId)
	WHEN MATCHED THEN
		UPDATE SET HallID = source.HallID, [Name] = source.[Name], AZ = source.AZ
	WHEN NOT MATCHED THEN
		INSERT (HallID, HallGroupID, [Name], AZ)
		VALUES (source.HallID, source.HallGroupId, source.[Name], source.AZ);
END
	SET IDENTITY_INSERT HallGroup OFF

/* ------------------------------------------------------------------------------------------------------------------------------------ */

CREATE PROCEDURE [dbo].[InsertHallGroups]
@HallGroups HallGroupType Readonly
AS
	SET IDENTITY_INSERT HallGroup ON
BEGIN
	MERGE HallGroup AS target
	USING (
		SELECT h.HallID, h.HallGroupID, h.[Name], h.AZ
		FROM @HallGroups h
	) AS SOURCE(HallID, HallGroupId, [Name], AZ)
	ON (target.HallGroupID = source.hallGroupId)
	WHEN MATCHED THEN
		UPDATE SET HallID = source.HallID, [Name] = source.[Name], AZ = source.AZ
	WHEN NOT MATCHED THEN
		INSERT (HallID, HallGroupID, [Name], AZ)
		VALUES (source.HallID, source.HallGroupId, source.[Name], source.AZ);
END
	SET IDENTITY_INSERT HallGroup OFF

/* ------------------------------------------------------------------------------------------------------------------------------------ */

CREATE PROCEDURE [dbo].[InsertHallSeats]
	@HallSeats HallSeatType  readonly
AS
SET IDENTITY_INSERT HallSeat ON
BEGIN
	MERGE HallSeat AS target
	USING (
		SELECT ShowSeatID, HallGroupID, Color, Price, SeatRow, SeatNumber, IsReserved FROM @HallSeats
	)	AS SOURCE(ShowSeatID, HallGroupID, Color, Price, SeatRow, SeatNumber, IsReserved)
	ON (target.ShowSeatID = source.ShowSeatID)
	WHEN MATCHED THEN
		UPDATE SET	HallGroupID = source.HallGroupID,
					Color = source.Color,
					Price = source.Price,
					SeatRow = source.SeatRow,
					SeatNumber = source.SeatNumber,
					IsReserved = source.IsReserved
	WHEN NOT MATCHED THEN
		INSERT (ShowSeatID, HallGroupID, Color, Price, SeatRow, SeatNumber, IsReserved)
		VALUES (source.ShowSeatID, source.HallGroupID, source.Color, source.Price, source.SeatRow, source.SeatNumber, source.IsReserved );
END
SET IDENTITY_INSERT HallSeat OFF
/* ------------------------------------------------------------------------------------------------------------------------------------ */