Call a SQL function in Entity Framework Core

Step 1: Create .net core MVC project with Entity framework

Step 2:  Create Function in SQL Server

create function funFullName(@firstName varchar(10),@lastName varchar(10))

RETURNS varchar(25)

AS

BEGIN

RETURN @firstName+' '+@lastName

END

Step 3:  Add Connection in "appsettings.json" file 

 "ConnectionStrings": {

    "SQLDB": "Server=.;Database=Dotnet_Core_MVC;Trusted_Connection=True;"

  },

Step 4:  Create Model class in "FuntionOutput.cs" under DBContext/Models folder

namespace Dotnet_Core_MVC.DBContext.Models

{

    public class FuntionOutput

    {

        public string FullName { get; set; }

    }

}

Step 5:  Create class in "DatabaseContext.cs" under DBContext folder

using Dotnet_Core_MVC.DBContext.Models;

using Microsoft.EntityFrameworkCore;

namespace Dotnet_Core_MVC.DBContext

{

    public class DatabaseContext : DbContext

    {

        public DatabaseContext(DbContextOptions<DatabaseContext> options) : base(options)

        {

        }

        //Funtion Call

        public virtual DbSet<FuntionOutput> funFullName { get; set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder)

        {

            modelBuilder.Entity<FuntionOutput>(e => e.HasNoKey());

        }

    }

}

Step 6:  Create Controller "Call_A_SQL_FunctionController.cs"  and call the SQL Function ass below code.

using Dotnet_Core_MVC.DBContext;

using Microsoft.AspNetCore.Mvc;

using Microsoft.EntityFrameworkCore;

using System.Linq;

namespace Dotnet_Core_MVC.Controllers

{

    public class Call_A_SQL_FunctionController : Controller

    {

        private readonly DatabaseContext _databaseContext;

        public Call_A_SQL_FunctionController(DatabaseContext databaseContext)

        {

            this._databaseContext = databaseContext;

        }

        public IActionResult Index()

        {

          ViewBag.functionOutput= _databaseContext.funFullName.FromSqlInterpolated($"select dbo.funFullName('adi','jc') as FullName").FirstOrDefault().FullName;

            return View();

        }

    }

}

Step 7:  Create View and add below code.

<h5>Function Output:</h5>@ViewBag.functionOutput

Step 8:  Open "Startup.cs" class and update "ConfigureServices" method as below.

 public void ConfigureServices(IServiceCollection services)

        {

            services.AddControllersWithViews();

            var ConnectionString = Configuration.GetConnectionString("SQLDB");

            //Entity Framework  

            services.AddDbContext<DatabaseContext>(options => options.UseSqlServer(ConnectionString));

        }

Output:

Github Code: https://github.com/adi501/Dotnet_Core_MVC

Comma Separated Value in SQL Query

 

--- create Table

create table Tbl_Exp(Id int identity(1,1) primary key,Emp_Name varchar(50),Working_Day varchar(5))

-- insert some test data

insert into Tbl_Exp values('Adi','M')

insert into Tbl_Exp values('Adi','T')

insert into Tbl_Exp values('Adi','W')

insert into Tbl_Exp values('Adi','TH')

insert into Tbl_Exp values('Pavan','F')

insert into Tbl_Exp values('Pavan','S')

insert into Tbl_Exp values('Pavan','M')

insert into Tbl_Exp values('Madhan','T')

insert into Tbl_Exp values('Madhan','W')

select * from Tbl_Exp

--how to use stuff [to replace some data], by using below query we are going to Replace 1st 2 letters with "***"

select stuff('abcdef',1,2,'***')

-- How to get comma separated value using XML path

Select ','+Working_Day from Tbl_Exp for xml path('')

-- how to remove 1 st values using stuff

select stuff((Select ','+Working_Day from Tbl_Exp for xml path('')),1,1,'')

-- Final Query

select distinct Emp_Name,

(select stuff((Select ','+Working_Day from Tbl_Exp as a where a.Emp_Name=b.Emp_Name for xml path('')),1,1,'') as a) as 'comma_separated'

from Tbl_Exp as b

Split Comma Separated List Without Using a Function in SQL Server

DECLARE @t TABLE
(
EmployeeID varchar(100),
Certs VARCHAR(8000)
)
INSERT @t VALUES ('Param1:','B.E.,MCA, MCDBA, PGDCA'), ('Param2:','M.Com.,B.Sc.'), ('Param1:','M.Sc.,M.Tech.')

SELECT EmployeeID,
EmployeeID+LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))) AS Certs
FROM
(
SELECT EmployeeID,CAST('<XMLRoot><RowData>' + REPLACE(Certs,',','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x
FROM   @t
)t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)


Output:

How to configure database mail in sql server 2016 and above

GO
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO

EXECUTE msdb.dbo.sysmail_add_profile_sp 
    @profile_name = 'TestDB_Mail', 
    @description = 'Profile used for sending outgoing notifications using Gmail.' ; 
GO

-- Grant access to the profile to the DBMailUsers role  
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp 
    @profile_name = 'TestDB_Mail', 
    @principal_name = 'public', 
    @is_default = 1 ;
GO

-- Create a Database Mail account  
EXECUTE msdb.dbo.sysmail_add_account_sp 
    @account_name = 'Gmail', 
    @description = 'Test mail.', 
    @email_address = 'XXXXXXXX', 
    @display_name = 'Automated Mailer', 
    @mailserver_name = 'smtp.gmail.com',
    @port = 25,
    @enable_ssl = 1,
    @username = 'XXXXXXX',
    @password = 'XXXXXX' ; 
GO

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp 
    @profile_name = 'TestDB_Mail', 
    @account_name = 'Gmail', 
    @sequence_number =1 ; 
GO

--To Send email

EXEC msdb.dbo.sp_send_dbmail
     @profile_name = 'TestDB_Mail',
     @recipients = 'XXXXXX',
     @body = 'The database mail configuration was completed successfully1.',
     @subject = 'Automated Success Message';
GO

----Rrecall Everything

EXECUTE msdb.dbo.sysmail_delete_profileaccount_sp @profile_name = 'TestDB_Mail'
EXECUTE msdb.dbo.sysmail_delete_principalprofile_sp @profile_name = 'TestDB_Mail'
EXECUTE msdb.dbo.sysmail_delete_account_sp @account_name = 'Gmail'
EXECUTE msdb.dbo.sysmail_delete_profile_sp @profile_name = 'TestDB_Mail'
GO

Send email from SQL Server stored procedure

Step 1: Enable Reconfigure in SQL by using below script.

sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

sp_configure 'Ole Automation Procedures', 1;

GO

RECONFIGURE;

GO

Step 2: Create Stored procedure by using below script

CREATE PROCEDURE [dbo].[sp_send_mail]

@from varchar(500) ,

@to varchar(500) ,

@subject varchar(500),

@body varchar(4000) ,

@bodytype varchar(10),

@output_mesg varchar(10) output,

@output_desc varchar(1000) output

AS

DECLARE @imsg int

DECLARE @hr int

DECLARE @source varchar(255)

DECLARE @description varchar(500)

EXEC @hr = sp_oacreate 'cdo.message', @imsg out

--SendUsing Specifies Whether to send using port (2) or using pickup directory (1)

EXEC @hr = sp_oasetproperty @imsg,

'configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").value','2'

--SMTP Server

EXEC @hr = sp_oasetproperty @imsg, 

  'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").value', 

  'smtp.gmail.com' 

--UserName

EXEC @hr = sp_oasetproperty @imsg, 

  'configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusername").value', 

  'sender@gmail.com' 

--Password

EXEC @hr = sp_oasetproperty @imsg, 

  'configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendpassword").value', 

  'xxxxxx' 

--UseSSL

EXEC @hr = sp_oasetproperty @imsg, 

  'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpusessl").value', 

  'True' 

--PORT 

EXEC @hr = sp_oasetproperty @imsg, 

  'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserverport").value', 

  '465' 

--Requires Aunthentication None(0) / Basic(1)

EXEC @hr = sp_oasetproperty @imsg, 

  'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate").value', 

  '1' 

EXEC @hr = sp_oamethod @imsg, 'configuration.fields.update', null

EXEC @hr = sp_oasetproperty @imsg, 'to', @to

EXEC @hr = sp_oasetproperty @imsg, 'from', @from

EXEC @hr = sp_oasetproperty @imsg, 'subject', @subject

-- if you are using html e-mail, use 'htmlbody' instead of 'textbody'.

EXEC @hr = sp_oasetproperty @imsg, @bodytype, @body

EXEC @hr = sp_oamethod @imsg, 'send', null

SET @output_mesg = 'Success'

-- sample error handling.

IF @hr <>0 

SELECT @hr

BEGIN

EXEC @hr = sp_oageterrorinfo null, @source out, @description out

IF @hr = 0

BEGIN

--set @output_desc = ' source: ' + @source

set @output_desc =  @description

END

ELSE

BEGIN

SET @output_desc = ' sp_oageterrorinfo failed'

END

IF not @output_desc is NULL

SET @output_mesg = 'Error'

END

EXEC @hr = sp_oadestroy @imsg

Step 3: Execute Stored procedure (to send email to sender) by using below script

DECLARE @out_desc varchar(1000),

@out_mesg varchar(10)

EXEC sp_send_mail 'sender@gmail.com',

'receiver@gmail.com',

'Hello', 

'<b>This is s Test Mail</b>',

'htmlbody', @output_mesg = @out_mesg output, @output_desc = @out_desc output

PRINT @out_mesg

PRINT @out_desc

How to search multiple values in a single column using stored procedure

Create Procedure

create procedure sp_search(@lnames AS varchar(100))

as

begin

SELECT * FROM tbl_Locations

WHERE CHARINDEX(',' + locationName + ',', ',' + @lnames + ',') > 0

end

Execute:

exec sp_search 'Govindapura,Basaveshwara Nagar,Bhadranna Layout'

How to get total number of tables in a database in SQL?

SELECT COUNT(*) as TotalTables FROM sys.tables WHERE type in ('u')