Stored Procedures in Entity FrameWork Core ASP.Net Web API Core

 

   using (SqlConnection cn = getConnectionString())

            {

                string sql = "StoreProcName";

                using (SqlCommand sqlCmd = new SqlCommand(sql, cn))

                {

                    sqlCmd.CommandType = CommandType.StoredProcedure;

                    sqlCmd.Parameters.AddWithValue("@Param", "paramValue");

                    //cn.Open();

                    using (SqlDataAdapter sqlAdapter = new SqlDataAdapter(sqlCmd))

                    {

                        sqlAdapter.Fill(ds);

                    }

                }

            }

            ds.Tables[0].Columns.Add("AreasOfInterest");

            main = ds.Tables[0].ToList<Main>();

            dates = ds.Tables[1].ToList<Dates>();

            casOptions = ds.Tables[2].ToList<ModelName>();

          

Stored Procedures in Entity FrameWork ASP.Net Web API

  var dataSet = new DataSet();

            using (var context = new DataBase())

            {

                using (var con = new SqlConnection(context.Database.Connection.ConnectionString))

                {

                    using (var command = new SqlCommand("Sample_proc", con))

                    {

                        command.CommandType = CommandType.StoredProcedure;

                        command.Parameters.AddWithValue("@empId", id);

                        using (var adapter = new SqlDataAdapter(command))

                        {

                            adapter.Fill(dataSet);

                        }

                    }

                }

  var item1 = dataSet.Tables[0].AsEnumerable().Select(x => new Role

                {

                    RoleId = Convert.ToInt16(x["RoleId"] == DBNull.Value ? null : x["RoleId"]),

                    RoleDescription = $"{x["RoleDescription"]}",

                    Display = Convert.ToBoolean(x["Display"] == DBNull.Value ? null : x["Display"]),

                    CreatedById = Convert.ToInt32(x["CreatedById"] == DBNull.Value ? null : x["CreatedById"]),

                    CreatedTime = Convert.ToDateTime(x["CreatedTime"] == DBNull.Value ? null : x["CreatedTime"]),

                    EditedById = Convert.ToInt32(x["EditedById"] == DBNull.Value ? null : x["EditedById"]),

                    EditedTime = Convert.ToDateTime(x["EditedTime"] == DBNull.Value ? null : x["EditedTime"]),

                    //RowId = (byte[])x["RowId"],

                    DisplayName = $"{x["DisplayName"]}"

                }).ToList();

                var item2 = dataSet.Tables[1].AsEnumerable().Select(x => new RoleCode

                {

                    RoleCode1 = $"{x["RoleCode"]}",

                    DeleteFlag = Convert.ToBoolean(x["DeleteFlag"] == DBNull.Value ? null : x["DeleteFlag"]),

                    EditFlag = Convert.ToBoolean(x["EditFlag"] == DBNull.Value ? null : x["EditFlag"]),

                    CreatedById = Convert.ToInt32(x["CreatedById"]),

                    CreatedTime = Convert.ToDateTime(x["CreatedTime"]),

                    EditedById = Convert.ToInt32(x["EditedById"] == DBNull.Value ? null : x["EditedById"]),

                    EditedTime = Convert.ToDateTime(x["EditedTime"] == DBNull.Value ? null : x["EditedTime"]),

                    //RowId = (byte[])x["RowId"],

                }).ToList();

}

}

WEB API Authentication using Azure AD

 using System;

using System.Collections.Generic;

using System.Configuration;

using System.IdentityModel.Tokens;

using System.Linq;

using Microsoft.Owin.Security;

using Microsoft.Owin.Security.ActiveDirectory;

using Owin;

using Microsoft.Owin.Security.Cookies;

using System.Globalization;

using Microsoft.Owin.Security.OpenIdConnect;

namespace Elite.Service

{

    public partial class Startup

    {

        // For more information on configuring authentication, please visit http://go.microsoft.com/fwlink/?LinkId=301864

        public void ConfigureAuth(IAppBuilder app)

        {

            try

            {

                app.SetDefaultSignInAsAuthenticationType(CookieAuthenticationDefaults.AuthenticationType);

                app.UseCookieAuthentication(new CookieAuthenticationOptions

                {

                    ExpireTimeSpan = new TimeSpan(1, 0, 0)

                });

                app.UseOpenIdConnectAuthentication(

                      new OpenIdConnectAuthenticationOptions

                      {

                          ClientId = ConfigurationManager.AppSettings["ida:ClientID"],

                          Authority = String.Format(CultureInfo.InvariantCulture, ConfigurationManager.AppSettings["ida:AADInstance"], ConfigurationManager.AppSettings["ida:Tenant"]),

                      });

                app.UseWindowsAzureActiveDirectoryBearerAuthentication(

                    new WindowsAzureActiveDirectoryBearerAuthenticationOptions

                    {

                        //Audience = ConfigurationManager.AppSettings["ida:Audience"],

                        Tenant = ConfigurationManager.AppSettings["ida:Tenant"],

                        TokenValidationParameters = new TokenValidationParameters { SaveSigninToken = true, ValidAudience = ConfigurationManager.AppSettings["ida:Audience"] }

                    });

            }

            catch (Exception ex)

            {

                // DB Error Logging

                // App Insights Logging

            }

        }

    }

}