How to create an html with column names and values corresponding to it from few relational tables in sql?

Question

1.00/5 (2 votes)

See more:

How to create an html with column names and values corresponding to it from
few relational tables in Sql?

I want to create an html as a table with column names and values corresponding to the same fetched from few tables in Sql.

I want as below with tr th td of table
FIELDNAME FIELDVALUE
FD1 VALUE1
FD1 VALUE2
FD2 VALUE1

What I have tried:

I tried to develop the same in the front end by fetching the data from few relational tables as needed using a Stored PROCEDURE in the front end and by calling the same again to pass the html table from the data in the dataset .

Posted 10-Feb-16 1:44am

ranio

Updated 10-Feb-16 1:49am

v3

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

RDBurmon · Answer 1 · 2016-02-10T03:54:00

Here is the solution, You can use this procedure. Call this proc from C# which will return the HTML code -

Tables considered -

SQL

CREATE TABLE tblEmployeeInfo (Id NVARCHAR(50),Name NVARCHAR(50))
CREATE TABLE tblEmployeeSalary (Id NVARCHAR(50),Salary INT,Currency NVARCHAR(3))

INSERT INTO tblEmployeeInfo 
SELECT 'EMP01','Joe D.'
UNION 
SELECT 'EMP02','Gemmy S.'
UNION 
SELECT 'EMP03','July C.'

INSERT INTO tblEmployeeSalary
SELECT 'EMP01',1245896,'USD'
UNION
SELECT 'EMP02',2545566,'USD'
UNION
SELECT 'EMP03',8547856,'USD'

Procedure

SQL

CREATE PROCEDURE spGetHTMLForEmployeeInfo(@EmployeeId NVARCHAR(50))
AS
BEGIN

DECLARE @tblEmployee AS TABLE (Idx INT IDENTITY(1,1),Id NVARCHAR(50),Name NVARCHAR(50),Salary INT,Currency NVARCHAR(3))
DECLARE @ttlrows AS INT
DECLARE @rowidx AS INT
DECLARE @HTMLCode AS VARCHAR(1000)
DECLARE @Id VARCHAR(50)
DECLARE @Name VARCHAR(50)
DECLARE @Salary INT
DECLARE @Currency VARCHAR(3)


SET @ttlrows =0
SET @rowidx=1

INSERT INTO @tblEmployee(Id ,Name ,Salary ,Currency)  
SELECT EI.Id ,EI.Name ,ES.Salary ,ES.Currency FROM tblEmployeeInfo EI INNER JOIN tblEmployeeSalary ES ON EI.Id=ES.Id
WHERE EI.Id=ISNULL(@EmployeeId,EI.Id)

SET @ttlrows =@@Rowcount

SET @HTMLCode='<HTML>'
SET @HTMLCode=@HTMLCode + '<style type="text/css">'
SET @HTMLCode=@HTMLCode + '.tg  {border-collapse:collapse;border-spacing:0;}'
SET @HTMLCode=@HTMLCode + '.tg td{font-family:Arial, sans-serif;font-size:14px;padding:10px 5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;}'
SET @HTMLCode=@HTMLCode + '.tg th{font-family:Arial, sans-serif;font-size:14px;font-weight:normal;padding:10px 5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;}'
SET @HTMLCode=@HTMLCode + '.tg .tg-yw4l{vertical-align:top}'
SET @HTMLCode=@HTMLCode + '</style>'
SET @HTMLCode=@HTMLCode + '<table class="tg">'

WHILE @rowidx <= @ttlrows  
BEGIN
SELECT @Id =Id, 
@Name =Name,
@Salary =Salary,
@Currency =Currency
FROM @tblEmployee WHERE idx=@rowidx


SET @HTMLCode=@HTMLCode + '  <tr>'
SET @HTMLCode=@HTMLCode + '    <th class="tg-yw4l">' + @Id  + '</th>'
SET @HTMLCode=@HTMLCode + '    <th class="tg-yw4l">' + @Name + '</th>'
SET @HTMLCode=@HTMLCode + '    <th class="tg-yw4l">' + CAST(@Salary AS VARCHAR(50))+ '</th>'
SET @HTMLCode=@HTMLCode + '    <th class="tg-yw4l">' + @Currency + '</th>'
SET @HTMLCode=@HTMLCode + '  </tr>'

SET @rowidx =@rowidx +1

END 

SET @HTMLCode=@HTMLCode + '  </table> </HTML>'

SELECT @HTMLCode

END