ASP.Net: Set DataSet column value based on Stored Procedure value using C# and VB.Net

makumbi
 
on Jul 25, 2022 11:06 PM
316 Views

Please help how can i call an SQL Server function to assign grades

DECLARE @Test AS TABLE(r1 INT,r2 INT,r3 INT,r4 INT,r5 INT,r6 INT,r7 INT,r8 INT,r9 INT,r10 INT,r11 INT,r12 INT,r13 INT,r14 INT,r15 INT,r16 INT,r17 INT,r18 INT)
INSERT INTO @Test VALUES(0,39,40,44,45,49,50,54,55,59,60,69,70,79,80,89,90,100)
 
DECLARE @Value INT = 45
SELECT CASE
    WHEN @Value >= r1 AND @Value <= r2 THEN 'F9'
    WHEN @Value >= r3 AND @Value <= r4 THEN 'P8'
    WHEN @Value >= r5 AND @Value <= r6 THEN 'P7'
    WHEN @Value >= r7 AND @Value <= r8 THEN 'C6'
    WHEN @Value >= r9 AND @Value <= r10 THEN 'C5'
    WHEN @Value >= r11 AND @Value <= r12 THEN 'C4'
    WHEN @Value >= r13 AND @Value <= r14 THEN 'C3'
    WHEN @Value >= r15 AND @Value <= r16 THEN 'D2'
    WHEN @Value >= r17 AND @Value <= r18 THEN 'D1'
    END AS 'Grade'
FROM @Test

my Dataset record i wanted the grades in to be filled automatically from the function

Name Class Stream engscore engrade sciencescore sciencegrade sstscore sstgrade mathsscore mathsgrade
WAFOYOYRWOTH SHALOM PIUS P6 BLUE 96   91   81   90  
TCHELU FRIDAH VIRGINIA P6 BLUE 87   92   73   95  
ODONGO ANDREW P6 BLUE 92   95   89   89  
Download FREE API for Word, Excel and PDF in ASP.Net: Download
PrinceG
 
on Jul 26, 2022 05:16 AM

Hi makumbi,

Create a procedure and return the grade as per the number by looping the DataSet. Then use the DataSet to display the record with grades.

Please refer below sample.

SQL

CREATE PROCEDURE [MarkSheet_GetMarkSheet]
	@Value  VARCHAR(20)
AS 
BEGIN
	DECLARE @Test AS TABLE(r1 INT,r2 INT,r3 INT,r4 INT,r5 INT,r6 INT,r7 INT,r8 INT,r9 INT,r10 INT,r11 INT,r12 INT,r13 INT,r14 INT,r15 INT,r16 INT,r17 INT,r18 INT)
	INSERT INTO @Test VALUES(0,39,40,44,45,49,50,54,55,59,60,69,70,79,80,89,90,100)
  
	SELECT CASE
		WHEN @Value >= r1 AND @Value <= r2 THEN 'F9'
		WHEN @Value >= r3 AND @Value <= r4 THEN 'P8'
		WHEN @Value >= r5 AND @Value <= r6 THEN 'P7'
		WHEN @Value >= r7 AND @Value <= r8 THEN 'C6'
		WHEN @Value >= r9 AND @Value <= r10 THEN 'C5'
		WHEN @Value >= r11 AND @Value <= r12 THEN 'C4'
		WHEN @Value >= r13 AND @Value <= r14 THEN 'C3'
		WHEN @Value >= r15 AND @Value <= r16 THEN 'D2'
		WHEN @Value >= r17 AND @Value <= r18 THEN 'D1'
		END AS 'Grade'
	FROM @Test
END

HTML

<asp:GridView ID="gvDetails" runat="server" AutoGenerateColumns="false">
    <Columns>
        <asp:BoundField DataField="Name" HeaderText="Name" />
        <asp:BoundField DataField="Class" HeaderText="Class" />
        <asp:BoundField DataField="Stream" HeaderText="Stream" />
        <asp:BoundField DataField="engscore" HeaderText="engscore" />
        <asp:BoundField DataField="enggrade" HeaderText="enggrade" />
        <asp:BoundField DataField="sciencescore" HeaderText="sciencescore" />
        <asp:BoundField DataField="sciencegrade" HeaderText="sciencegrade" />
        <asp:BoundField DataField="sstscore" HeaderText="sstscore" />
        <asp:BoundField DataField="sstGrade" HeaderText="sstGrade" />
        <asp:BoundField DataField="Mathsscore" HeaderText="Mathsscore" />
        <asp:BoundField DataField="Mathsgrade" HeaderText="Mathsgrade" />
    </Columns>
</asp:GridView>

Namespace

C#

using System.Data;
using System.Data.SqlClient;
using System.Configuration;

VB.Net

Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        DataTable dt = new DataTable();
        dt.Columns.AddRange(new DataColumn[11] {new DataColumn("Name"),
                                                new DataColumn("Class"),
                                                new DataColumn("Stream"),
                                                new DataColumn("engscore", typeof(int)),
                                                new DataColumn("enggrade"),
                                                new DataColumn("sciencescore", typeof(int)),
                                                new DataColumn("sciencegrade"),
                                                new DataColumn("sstscore", typeof(int)),
                                                new DataColumn("sstGrade"),
                                                new DataColumn("Mathsscore", typeof(int)),
                                                new DataColumn("Mathsgrade") });
        dt.Rows.Add("WAFOYOYRWOTH SHALOM PIUS", "P6", "Blue", 96, "", 91, "", 81, "", 90, "");
        dt.Rows.Add("TCHELU FRIDAH VIRGINIA", "P6", "Blue", 87, "", 92, "", 73, "", 95, "");
        dt.Rows.Add("ODONGO ANDREW", "P6", "Blue", 92, "", 95, "", 89, "", 89, "");
        dt.Rows.Add("TWINOMUJJUNI BRIAN", "P6", "Blue", 85, "", 89, "", 63, "", 91, "");
        dt.Rows.Add("ENACHU DORCUS", "P6", "Blue", 81, "", 84, "", 63, "", 96, "");
        dt.Rows.Add("MASABA LETICIA", "P6", "Blue", 89, "", 74, "", 60, "", 72, "");

        foreach (DataRow dr in dt.Rows)
        {
            dr["enggrade"] = GetGrade(dr["engscore"].ToString());
            dr["sciencegrade"] = GetGrade(dr["sciencescore"].ToString());
            dr["sstGrade"] = GetGrade(dr["sstscore"].ToString());
            dr["Mathsgrade"] = GetGrade(dr["Mathsscore"].ToString());
        }

        gvDetails.DataSource = dt;
        gvDetails.DataBind();
    }
}

private string GetGrade(string number)
{
    string grade = string.Empty;
    string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(conString))
    {
        con.Open();
        using (SqlCommand cmd = new SqlCommand("MarkSheet_GetMarkSheet", con))
        {
            cmd.Parameters.AddWithValue("@Value", number);
            cmd.CommandType = CommandType.StoredProcedure;
            grade = Convert.ToString(cmd.ExecuteScalar());
            con.Close();
        }
    }
    return grade;
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not Me.IsPostBack Then
        Dim dt As DataTable = New DataTable()
        dt.Columns.AddRange(New DataColumn(10) {New DataColumn("Name"),
                                                New DataColumn("Class"),
                                                New DataColumn("Stream"),
                                                New DataColumn("engscore", GetType(Integer)),
                                                New DataColumn("enggrade"),
                                                New DataColumn("sciencescore", GetType(Integer)),
                                                New DataColumn("sciencegrade"),
                                                New DataColumn("sstscore", GetType(Integer)),
                                                New DataColumn("sstGrade"),
                                                New DataColumn("Mathsscore", GetType(Integer)),
                                                New DataColumn("Mathsgrade")})
        dt.Rows.Add("WAFOYOYRWOTH SHALOM PIUS", "P6", "Blue", 96, "", 91, "", 81, "", 90, "")
        dt.Rows.Add("TCHELU FRIDAH VIRGINIA", "P6", "Blue", 87, "", 92, "", 73, "", 95, "")
        dt.Rows.Add("ODONGO ANDREW", "P6", "Blue", 92, "", 95, "", 89, "", 89, "")
        dt.Rows.Add("TWINOMUJJUNI BRIAN", "P6", "Blue", 85, "", 89, "", 63, "", 91, "")
        dt.Rows.Add("ENACHU DORCUS", "P6", "Blue", 81, "", 84, "", 63, "", 96, "")
        dt.Rows.Add("MASABA LETICIA", "P6", "Blue", 89, "", 74, "", 60, "", 72, "")

        For Each dr As DataRow In dt.Rows
            dr("enggrade") = GetGrade(dr("engscore").ToString())
            dr("sciencegrade") = GetGrade(dr("sciencescore").ToString())
            dr("sstGrade") = GetGrade(dr("sstscore").ToString())
            dr("Mathsgrade") = GetGrade(dr("Mathsscore").ToString())
        Next

        gvDetails.DataSource = dt
        gvDetails.DataBind()
    End If
End Sub

Private Function GetGrade(ByVal number As String) As String
    Dim grade As String = String.Empty
    Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(conString)
        con.Open()
        Using cmd As SqlCommand = New SqlCommand("MarkSheet_GetMarkSheet", con)
            cmd.Parameters.AddWithValue("@Value", number)
            cmd.CommandType = CommandType.StoredProcedure
            grade = Convert.ToString(cmd.ExecuteScalar())
            con.Close()
        End Using
    End Using
    Return grade
End Function

Screenshot