Call Stored Procedure using Web API with Nullable Parameters using C# in ASP.Net

skp
 
on Dec 26, 2019 06:45 AM
4878 Views

I have a stored procedure in which there are multiple parameters, how to write a web api call that stored procedure with either using a single paramater or multiple parameters in a single web api get method using c#?

ALTER PROCEDURE [dbo].[STUDENT_DETAILS]
    @STARTDATE DATETIME = NULL,
    @ENDDATE DATETIME = NULL,
    @STUDENTID INT = NULL,
    @DEPTID INT = NULL,
    @COLLEGEID INT = NULL,
    @SECTIONID INT = NULL

 

        [HttpGet]
        [ActionName("getStudentDetails")]
        public string getStudentDetails(string StartDate, string EndDate, int StudentId, int DeptId, int CollegeId, int SectionId)
        {
            SqlConnection myConnection = new SqlConnection();
            myConnection.ConnectionString = ConfigurationManager.ConnectionStrings[""].ConnectionString;
            SqlCommand sqlCmd = new SqlCommand();
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.CommandText = "STUDENT_DETAILS";
            sqlCmd.Connection = myConnection;
            sqlCmd.Parameters.AddWithValue("@StartDate", StartDate);
            sqlCmd.Parameters.AddWithValue("@EndDate", EndDate);
            sqlCmd.Parameters.AddWithValue("@StudentId", StudentId);
            sqlCmd.Parameters.AddWithValue("@DeptId", DeptId);
            sqlCmd.Parameters.AddWithValue("@CollegeId", CollegeId);
            sqlCmd.Parameters.AddWithValue("@SectionId", SectionId);
            SqlDataAdapter sda = new SqlDataAdapter(sqlCmd);
            DataSet ds = new DataSet();
            string jsonString = string.Empty;
            myConnection.Open();
            sda.Fill(ds);
            myConnection.Close();
            jsonString = JsonConvert.SerializeObject(ds.Tables[0]);
            return jsonString;
        }

If the user selects only start and end date, it must display the details based on only the start and end date, while keeping the other Id's as null. Likewise for all. 

Note: single get method

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Dec 30, 2019 02:30 AM

You need to pass Nullable Parameters to the Procedure when select only start and end date.

Check with below code.

[HttpGet]
[ActionName("getStudentDetails")]
public string getStudentDetails(string StartDate, string EndDate, int StudentId, int DeptId, int CollegeId, int SectionId)
{
    SqlConnection myConnection = new SqlConnection();
    myConnection.ConnectionString = ConfigurationManager.ConnectionStrings[""].ConnectionString;
    SqlCommand sqlCmd = new SqlCommand();
    sqlCmd.CommandType = CommandType.StoredProcedure;
    sqlCmd.CommandText = "STUDENT_DETAILS";
    sqlCmd.Connection = myConnection;
    sqlCmd.Parameters.AddWithValue("@StartDate", StartDate);
    sqlCmd.Parameters.AddWithValue("@EndDate", EndDate);
    sqlCmd.Parameters.AddWithValue("@StudentId", StudentId > 0 ? StudentId : (object)DBNull.Value);
    sqlCmd.Parameters.AddWithValue("@DeptId", DeptId > 0 ? DeptId : (object)DBNull.Value);
    sqlCmd.Parameters.AddWithValue("@CollegeId", CollegeId > 0 ? CollegeId : (object)DBNull.Value);
    sqlCmd.Parameters.AddWithValue("@SectionId", SectionId > 0 ? SectionId : (object)DBNull.Value);
    SqlDataAdapter sda = new SqlDataAdapter(sqlCmd);
    DataSet ds = new DataSet();
    string jsonString = string.Empty;
    myConnection.Open();
    sda.Fill(ds);
    myConnection.Close();
    jsonString = JsonConvert.SerializeObject(ds.Tables[0]);
    return jsonString;
}

And in where condition check the columns with nullable parameters.

ALTER PROCEDURE [dbo].[STUDENT_DETAILS]
    @STARTDATE DATETIME = NULL,
    @ENDDATE DATETIME = NULL,
    @STUDENTID INT = NULL,
    @DEPTID INT = NULL,
    @COLLEGEID INT = NULL,
    @SECTIONID INT = NULL
AS
BEGIN
	SELECT * FROM Table
	WHERE BDate BETWEEN @STARTDATE AND @ENDDATE
	AND (StudentId = @STUDENTID OR @STUDENTID IS NULL)
	AND (DeptId = @DEPTID OR @DEPTID IS NULL)
	AND (CollegeId = @COLLEGEID OR @COLLEGEID IS NULL)
	AND (SectionId = @SECTIONID OR @SECTIONID IS NULL)
END

 

skp
 
on Jan 03, 2020 12:59 AM

 

[HttpGet]
[ActionName("getStudentDetails")]
public string getStudentDetails(string StartDate, string EndDate, int? StudentId = null, int? DeptId = null, int? CollegeId = null, int? SectionId = null)
{
    SqlConnection myConnection = new SqlConnection();
    myConnection.ConnectionString = ConfigurationManager.ConnectionStrings[""].ConnectionString;
    SqlCommand sqlCmd = new SqlCommand();
    sqlCmd.CommandType = CommandType.StoredProcedure;
    sqlCmd.CommandText = "STUDENT_DETAILS";
    sqlCmd.Connection = myConnection;
    sqlCmd.Parameters.AddWithValue("@StartDate", StartDate);
    sqlCmd.Parameters.AddWithValue("@EndDate", EndDate);
    sqlCmd.Parameters.AddWithValue("@StudentId", StudentId);
    sqlCmd.Parameters.AddWithValue("@DeptId", DeptId);
    sqlCmd.Parameters.AddWithValue("@CollegeId", CollegeId);
    sqlCmd.Parameters.AddWithValue("@SectionId", SectionId);
    SqlDataAdapter sda = new SqlDataAdapter(sqlCmd);
    DataSet ds = new DataSet();
    string jsonString = string.Empty;
    myConnection.Open();
    sda.Fill(ds);
    myConnection.Close();
    jsonString = JsonConvert.SerializeObject(ds.Tables[0]);
    return jsonString;
}

Instead of using OR condition in sql, we can use COALESCE function

DeptId = COALESCE(@DEPTID, DEPTID)