Make a generic method to call different stored procedure in .Net Core and Java
Angular Part
Login.html
<div class="col-md-6 rnd">
<h4 class="lblwhite ">Login</h4>
<form (ngSubmit)="onsubmit()" #form="ngForm">
<div class="form-group">
<label for="usr" class="lblwhite">User Name:</label>
<input type="text" required="true" name="usr" [(ngModel)]="login.usr" #usr="ngModel" class="form-control"
id="usr">
</div>
<div class="form-group">
<label for="pwd" class="lblwhite">Password:</label>
<input type="password" required="true" name="pass" [(ngModel)]="login.pass" #pass="ngModel" class="form-control"
id="pwd">
</div>
<div *ngIf="usr.invalid && (usr.dirty || usr.touched)" class="alert alert-danger">
<div *ngIf="usr.errors?.required">
Name is required.
</div>
</div>
<div *ngIf="pass.invalid && (pass.dirty || pass.touched)" class="alert alert-danger">
<div *ngIf="pass.errors?.required">
Password is required.
</div>
</div>
<button type="submit" [disabled]="form.invalid" class="btn btn-primary">Submit</button>
</form>
</div>
Login.ts
import { Component, OnInit } from '@angular/core';
import { LoginService } from '../service/login.service';
@Component({
selector: 'app-login',
templateUrl: './login.component.html',
styleUrls: ['./login.component.css'],
providers: [LoginService]
})
export class LoginComponent implements OnInit {
login = { usr: "", pass: "" }
constructor(private loginService: LoginService) { }
ngOnInit() {
}
onsubmit() {
this.loginService.login(this.login).subscribe(x => {
this.convert(x)
console.log(x)
})
}
convert(x) {
Object.keys(x).forEach(element => {
try {
x[element] = JSON.parse(x[element])
} catch (error) {
}
});
}
}
Service
import { Injectable } from '@angular/core';
import { HttpClient } from '@angular/common/http';
@Injectable({
providedIn: 'root'
})
export class LoginService {
constructor(private http: HttpClient) { }
login(login: { usr: string; pass: string; }): any {
return this.http.post("https://localhost:5001/api/login/loginuser", login)
}
saveEmployee(data)
{
return this.http.post("https://localhost:5001/api/login/saveEmployee", data)
}
}
Using .Net Core
Manage.cs
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using emsSrv.Models;
using Microsoft.EntityFrameworkCore;
namespace emsSrv.Datamanager
{
public static class manager
{
public static dynamic LoadData(string sqlSelect, string sqlParameters)
{
using (var ctx = new EmsContext())
{
var connection = new SqlConnection(ctx.Database.GetDbConnection().ConnectionString.ToString());
SqlCommand command = new SqlCommand(sqlSelect, connection);
command.CommandType = CommandType.StoredProcedure;
if(sqlParameters!="")
{
command.Parameters.AddWithValue("@data", sqlParameters);
}
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = command;
DataSet ds = new DataSet();
da.Fill(ds);
DataTable firstTable = ds.Tables[0];
var a = ToString(firstTable);
return a;
}
}
public static dynamic ToString(this DataTable dt)
{
string result = "";
foreach (DataRow row in dt.Rows)
{
foreach (DataColumn column in dt.Columns)
{
result = row[column].ToString();
}
}
return result;
}
}
}
In order to call different sp we just need to do this
var d = emsSrv.Datamanager.manager.LoadData("saveLeave", data.ToString());
return d;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using emsSrv.Datamanager;
using emsSrv.Models;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
namespace emsSrv.Controllers
{
[Route("api/values")]
[ApiController]
public class ValuesController : ControllerBase
{
[HttpGet]
public dynamic Get()
{
var context = new EmsContext();
var d = emsSrv.Datamanager.manager.LoadData("GetEmployee", "");
return d;
}
[HttpPost("saveLeave")]
public dynamic saveLeave([FromBody] dynamic data)
{
var d = emsSrv.Datamanager.manager.LoadData("saveLeave", data.ToString());
return d;
}
[HttpPost("saveAttendance")]
public dynamic saveAttendance([FromBody] dynamic data)
{
var d = emsSrv.Datamanager.manager.LoadData("saveAttendance", data.ToString());
return d;
}
}
}
Using java
Manger.java
/**
*
* @author sajal
*/
public class Manager {
public static String LoadData(String sqlSelect, String sqlParameters) {
String text = "error";
String sp = "";
try {
String url = "jdbc:sqlserver://localhost;databaseName=Ems;integratedSecurity=true";
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
} catch (ClassNotFoundException ex) {
Logger.getLogger(Manager.class.getName()).log(Level.SEVERE, null, ex);
}
Connection conn = DriverManager.getConnection(url);
sp = "{call " + sqlSelect + "(?)" + " }";
CallableStatement cs = conn.prepareCall(sp);
cs.setString(1, sqlParameters);
boolean isRs = cs.execute();
if (isRs) {
ResultSet rs = cs.getResultSet();
try {
if (rs.next()) {
text = rs.getString(1);
}
} finally {
conn.close();
rs.close();
}
}
} catch (SQLException ex) {
text += sp;
text += " , " + sqlParameters + " , ";
text += ex.toString();
}
return text;
}
}
Call like this
String getData = helloworld.Manager.LoadData("login", param.toString());
return getData;
Ems.java
@Stateless
@Path("/ems")
public class HelloWorldResource {
@GET
@Path("/employeelist")
@Produces("application/json")
public String employeelist() {
String getData = helloworld.Manager.LoadData("GetEmployee", "");
return getData;
}
@POST
@Path("/login")
@Produces("application/json")
public String login(String param) {
String getData = helloworld.Manager.LoadData("login", param.toString());
return getData;
}
}
Stored Procedure
create procedure [login]
@data nvarchar(max)
as
begin
declare @name nvarchar(50),@pass nvarchar(50),@msg nvarchar(50),@isSuccess bit
select @name=name,@pass=pass from openjson(@data)
with
(
name varchar(50) '$.usr',
pass varchar(50) '$.pass'
)
if exists(select 9 from Employee where Name= @name and Password=@pass)
begin
set @msg='logged in successfully'
set @isSuccess=1
end
else
begin
set @msg='user is not registered'
set @isSuccess=0
end
select @msg as msg,@isSuccess as isSuccess for json path
end
Thank you sir
ReplyDelete