Here is your solution -
Create one window form in C# -
1) One combo box (Name - cmbStudentId)
2) One Button (Name - button1)
3) one DataGridView (Name -dtgrdvStudFee)
Create below two tables in SQL SERVER and insert data
CREATE TABLE FeeAssigned ([FEE HEAD] NVARCHAR(50), APR INT,MAY INT,JUN INT,JUL INT,AUG INT,SEP INT,OCT INT,NOV INT,DEC INT,JAN INT,FEB INT,MAR INT,StudentId NVARCHAR(50))
CREATE TABLE FeeExemption ([FEE HEAD] NVARCHAR(50), APR INT,MAY INT,JUN INT,JUL INT,AUG INT,SEP INT,OCT INT,NOV INT,DEC INT,JAN INT,FEB INT,MAR INT,StudentId NVARCHAR(50))
INSERT INTO FeeAssigned
SELECT 'TUITION FEE',120,120,120,120,120,120,120,120,120,120,120,120,'STUD01'
UNION ALL
SELECT 'TRAN FEE',50,50,50,50,50,50,50,50,50,50,50,50,'STUD01'
INSERT INTO FeeExemption
SELECT 'TUITION FEE',10,10,10,10,10,10,10,10,10,10,10,10,'STUD01'
UNION ALL
SELECT 'TRAN FEE',10,10,10,10,10,10,10,10,10,10,10,10,'STUD01'
Then Create this procedure which will return the Fee calculation for student
CREATE PROC GetFeeStructureForStud(@StudentId NVARCHAR(50))
AS
BEGIN
SELECT FA.StudentId,FA.[FEE HEAD],
FA.APR - ISNULL(FET.APR,0) AS APR,
FA.MAY - ISNULL(FET.MAY,0) AS MAY,
FA.JUN - ISNULL(FET.JUN,0) AS JUN,
FA.JUL - ISNULL(FET.JUL,0) AS JUL,
FA.AUG - ISNULL(FET.AUG,0) AS AUG,
FA.SEP - ISNULL(FET.SEP,0) AS SEP,
FA.OCT - ISNULL(FET.OCT,0) AS OCT,
FA.NOV - ISNULL(FET.NOV,0) AS NOV,
FA.DEC - ISNULL(FET.DEC,0) AS DEC,
FA.JAN - ISNULL(FET.JAN,0) AS JAN,
FA.FEB - ISNULL(FET.FEB,0) AS FEB,
FA.MAR - ISNULL(FET.MAR,0) AS MAR
FROM FeeAssigned FA LEFT JOIN
(SELECT FE.StudentId,FE.[FEE HEAD],
SUM(FE.APR) AS APR,
SUM(FE.MAY) AS MAY,
SUM(FE.JUN) AS JUN,
SUM(FE.JUL) AS JUL,
SUM(FE.AUG) AS AUG,
SUM(FE.SEP) AS SEP,
SUM(FE.OCT) AS OCT,
SUM(FE.NOV) AS NOV,
SUM(FE.DEC) AS DEC,
SUM(FE.JAN) AS JAN,
SUM(FE.FEB) AS FEB,
SUM(FE.MAR) AS MAR
FROM FeeExemption FE
GROUP BY FE.StudentId,FE.[FEE HEAD]) AS FET ON (FA.StudentId=FET.StudentId) AND (FA.[FEE HEAD]=FET.[FEE HEAD])
WHERE FA.StudentId=@StudentId
END
And Finally our C# code to display data in datagridview
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace _1073993_I_have_two_table_FeeAssigned_FeeExemption
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
string connetionString = null;
SqlConnection cnn;
connetionString = @"Data Source=RDBurmonPC\SQLEXPRESS;Initial Catalog=Test;User ID=Test;Password=Test@123";
cnn = new SqlConnection(connetionString);
cnn.Open();
string query = "select DISTINCT StudentId from FeeAssigned";
SqlCommand cmd = new SqlCommand(query, cnn);
cmd.CommandText = query;
SqlDataReader drd = cmd.ExecuteReader();
while (drd.Read())
{
cmbStudentId.Items.Add(drd["StudentId"].ToString());
}
drd.Close();
cnn.Close();
}
private void button1_Click(object sender, EventArgs e)
{
string connetionString = null;
SqlConnection cnn;
connetionString = @"Data Source=RDBurmonPC\SQLEXPRESS;Initial Catalog=Test;User ID=Test;Password=Test@123";
cnn = new SqlConnection(connetionString);
cnn.Open();
string query;
query = string.Concat("EXEC GetFeeStructureForStud '" , cmbStudentId.Text.ToString() , "'");
SqlDataAdapter dataAdapter = new SqlDataAdapter(query, cnn);
SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);
DataSet ds = new DataSet();
dataAdapter.Fill(ds);
dtgrdvStudFee.ReadOnly = true;
dtgrdvStudFee.DataSource = ds.Tables[0];
cnn.Close();
}
}
}
I hope this will work for you and if yes then vote and accept the answer
Thanks - RDBurmon