The project requires to create an XML file. After reading online posts I decided to go with LINQ to XML. (Please keep in mind that I am new to LINQ.) I got stuck at retrieving the data from the DataTable into the XElements.
protected void XDocument()
{
XElement FloridaData = new XElement("submitted_file",
from vinList in dt.AsEnumerable()
select
new XElement("schema_version", "123.45"),
new XElement("company",
new XElement("company_code",
new XElement("transaction",
new XElement("transaction_type", vinList.Field<Int32>("transaction_type")),
new XElement("policy",
new XElement("policy_number", ""),
new XElement("policy_effective_date", ""),
new XElement("policy_through_date", ""),
new XElement("insurance_company_notes", ""),
new XElement("policy_holder",
new XElement("company_indicator", ""),
new XElement("first_name", ""),
new XElement("middle_name", ""),
new XElement("last_name", ""),
new XElement("name_suffix", ""),
new XElement("gender", ""),
new XElement("dob", ""),
new XElement("stree_address", ""),
new XElement("city", ""),
new XElement("state", ""),
new XElement("zipcode", ""),
new XElement("fl_dln", ""),
new XElement("fed_tin", ""),
new XElement("non_structured_name", ""),
new XElement("ph_transaction_type", ""),
new XElement("effective_date", ""),
new XElement("fr_case_number", ""),
new XElement("fr_certification_date", ""),
new XElement("fr_prepared_date", "")),
new XElement("vehicle",
new XElement("fleet_indicator", "read-data"),
new XElement("vin", ""),
new XElement("year", ""),
new XElement("make", ""),
new XElement("vehicle_effective_date", ""),
new XElement("level_of_coverage", ""),
new XElement("vehicle_transaction_type", "")),
new XElement("verification",
new XElement("tracking_number", " "),
new XElement("required_coverage_dt", ""),
new XElement("status_message", " "),
new XElement("days_late", " "),
new XElement("yes_confirmed", " "),
new XElement("confirmed", " "),
new XElement("unconfirmed", " "))
)))));
FloridaData.Save("C:\\Something.xml");
}
protected void GetGridViewData()
{
SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["DBConnection"]);
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "GetFloridaDmvData";
sqlda = new SqlDataAdapter(cmd);
cmd.Connection = conn;
try
{
conn.Open();
cmd.Parameters.Add("@RunProcess", SqlDbType.VarChar);
cmd.Parameters["@RunProcess"].Value = ddlTransaction.SelectedValue;
dt = new DataTable();
sqlda.Fill(dt);
gvData.DataSource = dt;
gvData.DataBind();
//IEnumerable<Vehicle> vinList = (IEnumerable<Vehicle>)gvData.DataSource;
}
catch (Exception ex)
{
lblMessage.Text = ex.Message;
lblMessage.Visible = true;
}
finally
{
conn.Close();
conn.Dispose();
}
}
Stored Procedure is long:
ALTER PROCEDURE [dbo].[GetFloridaDmvData]
(
@RunProcess int
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
--reduce network traffic due to the fact that your client will not receive the message indicating the number of rows affected by a Transact-SQL statement
SET NOCOUNT ON;
DECLARE @IFUID AS INT,
@FileName AS VARCHAR(30),
@CompCode AS INT,
@TransSold AS INT,
@TransInit AS INT,
@TransAdd AS INT,
@TransDelete AS INT,
@FEID AS INT,
@VehicleTransType AS INT,
@LevelCov AS VARCHAR(2),
@LevelCovTruck AS VARCHAR(2),
@CompIndicator AS VARCHAR(1),
@Fleet AS VARCHAR(1),
@FILL AS VARCHAR(5),
@NR AS INT,
@Empty AS VARCHAR(7),
@xml AS NVARCHAR(MAX),
@TotalCount AS INT,
@CurrentYear AS VARCHAR(4),
@PreviousYear AS VARCHAR(4),
@ExpYear AS VARCHAR(4),
@FirstPrevDate AS VARCHAR(10),
@FirstDate AS VARCHAR(10),
@SecondDate AS VARCHAR(10),
@ExpDate AS VARCHAR(10),
@ExpCurrentDate AS VARCHAR(10),
@TodayDay AS VARCHAR(2),
@TodayMonth AS VARCHAR(2);
SET @CurrentYear = YEAR(GETDATE());
SET @PreviousYear = YEAR(GETDATE()) - 1;
SET @ExpYear = YEAR(GETDATE()) + 1;
SET @TodayDay = DAY(GETDATE());
SET @TodayMonth = MONTH(GETDATE());
SET @FirstPrevDate = '04/01/' + @PreviousYear;
SET @FirstDate = '04/01/' + @CurrentYear;
SET @SecondDate = '12/31/' + @CurrentYear;
SET @ExpDate = '03/31/' + @ExpYear;
SET @ExpCurrentDate = '03/31/' + @CurrentYear;
SET @Empty = '';
--SET @FileName = 'C:\Repwest2015.xml'
SET @FileName = 'RepWest' + @TodayMonth + '/' + @TodayDay + '/' + @CurrentYear;
SET @IFUID = '123';
SET @CompCode = '13177';
SET @CompIndicator = 'Y';
SET @Fleet = 'N';
SET @FEID = '860274508';
SET @LevelCov = '02';
SET @LevelCovTruck = '03';
SET @TransInit = '20';
SET @TransSold = '13';
SET @TransAdd = '31';
SET @TransDelete = '32';
SET @FILL = 'something';
SET @NR = '123';
SET @TotalCount = (
SELECT COUNT(*) AS total_count
FROM Executive_And_SV_Vehicles
WHERE (Executive_And_SV_Vehicles.STATE = 'FL')
AND (Executive_And_SV_Vehicles.PRIMARY_DRIVER NOT LIKE '%sold%')
AND (Executive_And_SV_Vehicles.VERIFIED_THIS_YEAR = 'Y')
);
-- Insert statements for procedure here
SELECT
@CompCode AS 'company_code',
@TransInit AS 'transaction_type',
CASE
WHEN GETDATE() < @FirstDate
THEN SV_Policy.Policy + RIGHT(@PreviousYear, 2)
WHEN GETDATE() >= @FirstDate
THEN SV_Policy.Policy + RIGHT(@CurrentYear, 2)
END AS 'policy_number',
CASE
WHEN GETDATE() < @FirstDate
THEN CASE
WHEN Date_Added >= @FirstPrevDate
THEN REPLACE(CONVERT(DATETIME, Date_Added, 101), '/', '')
WHEN Date_Added < @FirstPrevDate
THEN REPLACE(CONVERT(VARCHAR(10), @FirstPrevDate, 101), '/', '')
END
WHEN GETDATE() >= @FirstDate
THEN CASE
WHEN Date_Added >= @FirstDate
THEN REPLACE(CONVERT(DATETIME, Date_Added, 101), '/', '')
WHEN Date_Added < @FirstDate
THEN REPLACE(CONVERT(VARCHAR(10), @FirstDate, 101), '/', '')
END
END AS 'policy_effective_date',
CASE
WHEN GETDATE() < @FirstDate
THEN REPLACE(CONVERT(VARCHAR(10), @ExpCurrentDate, 101), '/', '')
WHEN GETDATE() >= @FirstDate
THEN REPLACE(CONVERT(VARCHAR(10), @ExpDate, 101), '/', '')
END AS 'policy_through_date',
@Empty AS 'insurance_company_notes',
@CompIndicator AS 'company_indicator',
@Empty AS 'first_name',
@Empty AS 'middle_name',
@Empty AS 'last_name',
@Empty AS 'name_suffix',
@Empty AS 'gender',
@Empty AS 'dob',
Executive_And_SV_Vehicles.INSURED_ADDRESS AS 'street_address',
Executive_And_SV_Vehicles.INSURED_CITY AS 'city',
Executive_And_SV_Vehicles.INSURED_STATE AS 'state',
Executive_And_SV_Vehicles.INSURED_ZIPCODE AS 'zipcode',
@Empty AS 'fl_dln',
@FEID AS 'fed_tin',
Executive_And_SV_Vehicles.INSURED_NAME AS 'non_structured_name',
CASE
WHEN DATE_DELETED IS NOT NULL
AND PRIMARY_DRIVER LIKE '%sold%'
THEN @TransSold
WHEN DATE_DELETED IS NOT NULL
THEN @TransDelete
WHEN GETDATE() >= @FirstDate
THEN @TransInit --@TransAdd
WHEN GETDATE() < @FirstDate
THEN @TransInit
END AS 'ph_transaction_type',
CASE
WHEN GETDATE() < @FirstDate
THEN CASE
WHEN Date_Added >= @FirstPrevDate
THEN REPLACE(CONVERT(DATETIME, Date_Added, 101), '/', '')
WHEN Date_Added < @FirstPrevDate
THEN REPLACE(CONVERT(VARCHAR(10), @FirstPrevDate, 101), '/', '')
END
WHEN GETDATE() >= @FirstDate
THEN CASE
WHEN Date_Added >= @FirstDate
THEN REPLACE(CONVERT(DATETIME, Date_Added, 101), '/', '')
WHEN Date_Added < @FirstDate
THEN REPLACE(CONVERT(VARCHAR(10), @FirstDate, 101), '/', '')
END
END AS 'effective_date',
@Empty AS 'fr_case_number',
@Empty AS 'fr_certification_date',
@Empty AS 'fr_prepared_date',
@Fleet AS 'FleetIndicator',
Executive_And_SV_Vehicles.VIN AS 'vin',
Executive_And_SV_Vehicles.YEAR AS 'year',
Executive_And_SV_Vehicles.MAKE AS 'make',
REPLACE(CONVERT(DATETIME, Date_Added, 101), '/', '') AS 'vehicle_effective_date',
CASE
WHEN PRIMARY_DRIVER LIKE '%U-BOX DELIVERY TRUCK%'
THEN @LevelCovTruck
WHEN PRIMARY_DRIVER NOT LIKE '%U-BOX DELIVERY TRUCK%'
THEN @LevelCov
END AS 'level_of_coverage',
CASE
WHEN DATE_DELETED IS NOT NULL
AND PRIMARY_DRIVER LIKE '%sold%'
THEN @TransSold
WHEN DATE_DELETED IS NOT NULL
THEN @TransDelete
WHEN GETDATE() >= @FirstDate
THEN @TransInit --@TransAdd
WHEN GETDATE() < @FirstDate
THEN @TransInit
END AS 'vehicle_transaction_type',
@Empty AS 'tracking_number',
@Empty AS 'required_coverage_date',
@Empty AS 'status_message',
@Empty AS 'days_late',
@Empty AS 'yes_confirmed',
@Empty AS 'confirmed',
@Empty AS 'unconfirmed'
FROM SV_Policy
INNER JOIN Executive_And_SV_Vehicles
ON SV_Policy.ID = Executive_And_SV_Vehicles.SV_Policy
WHERE (Executive_And_SV_Vehicles.STATE = 'FL')
AND (Executive_And_SV_Vehicles.PRIMARY_DRIVER NOT LIKE '%sold%')
AND (Executive_And_SV_Vehicles.VERIFIED_THIS_YEAR = 'Y')
END
