crud operation in asp.net mvc with signalr

in this article, i will show you crud operation in asp.net mvc with signalr,

----------- scripts ----------------

create table employee(
[id] [int] identity(1,1) primary key not null,
[name] [varchar](50) null,
[salary] [decimal](18, 2) null
)
go
create proc read_emp_rec
@id int
as
if @id=0
begin
select * from employee
end
else
begin
select * from employee where id=@id
end
go
create proc insert_emp_rec
@name varchar(50),
@salary decimal(18,2)
as
insert into employee (name,salary) values(@name,@salary)
go
create proc update_emp_rec
@id varchar(10),
@name varchar(50),
@salary decimal(18,2)
as
update employee set name=@name,salary=@salary where id=@id
go
create proc delete_emp_rec
@id varchar(10)
as
delete employee where id=@id

-------- controller ------------------

private SqlConnection con;
private SqlCommand cmd;
private void connection()
{
    con = new SqlConnection("Data Source=.;Integrated Security=true;Initial Catalog=TESTDB");
}

public List<Employee> GetData(int id = 0)
{
    List<Employee> Emplst = new List<Employee>();
    DataSet ds = new DataSet();
    connection();
    cmd = new SqlCommand("read_emp_rec", con);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@id", id);
    con.Open();
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    da.Fill(ds);
    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
    {
        Employee Empobj = new Employee();
        Empobj.id = Convert.ToInt32(ds.Tables[0].Rows[i]["id"].ToString());
        Empobj.name = ds.Tables[0].Rows[i]["name"].ToString();
        Empobj.salary = Convert.ToDecimal(ds.Tables[0].Rows[i]["salary"]);
        Emplst.Add(Empobj);
    }
    con.Close();
    return Emplst;
}

public ActionResult GetAllData()
{
    //add partial view & scaffolding template list
    var Emplst = GetData();
    return PartialView("_EmployeeList", Emplst);
}

public ActionResult Index()
{
    return View();
}

public ActionResult Create()
{
    //add scaffolding template create
    return View();
}

[HttpPost]
public ActionResult Create(Employee Empobj)
{
    try
    {
        connection();
        string spname = "insert_emp_rec";
        SqlCommand cmd = new SqlCommand(spname, con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@name", Empobj.name);
        cmd.Parameters.AddWithValue("@salary", Empobj.salary);
        con.Open();
        int i = cmd.ExecuteNonQuery();
        con.Close();
        EmployeeHub.BroadcastData();
    }
    catch (Exception ex)
    {
        ex.Message.ToString();
    }
    return View("Index");
}

public ActionResult Details(int id)
{
    //add scaffolding template details
    var Empdet = GetData(id).Where(x => x.id == id).FirstOrDefault();
    return View(Empdet);
}

public ActionResult Edit(int id)
{
    //add scaffolding template edit
    var Empdet = GetData(id).Where(x => x.id == id).FirstOrDefault();
    return View(Empdet);
}

[HttpPost]
public ActionResult Edit(Employee Empobj)
{
    try
    {
        connection();
        string spname = "update_emp_rec";
        SqlCommand cmd = new SqlCommand(spname, con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@id", Empobj.id);
        cmd.Parameters.AddWithValue("@name", Empobj.name);
        cmd.Parameters.AddWithValue("@salary", Empobj.salary);
        con.Open();
        int i = cmd.ExecuteNonQuery();
        con.Close();
        EmployeeHub.BroadcastData();
    }
    catch (Exception ex)
    {
        ex.Message.ToString();
    }
    return View("Index");
}

public ActionResult Delete(int id)
{
    //add scaffolding template delete
    var Empdet = GetData(id).Where(x => x.id == id).FirstOrDefault();
    return View(Empdet);
}

[HttpPost]
public ActionResult Delete(Employee Empobj)
{
    try
    {
        connection();
        string spname = "delete_emp_rec";
        SqlCommand cmd = new SqlCommand(spname, con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@id", Empobj.id);
        con.Open();
        int i = cmd.ExecuteNonQuery();
        con.Close();
        EmployeeHub.BroadcastData();
    }
    catch (Exception ex)
    {
        ex.Message.ToString();
    }
    return View("Index");
}

-----------integrate signalr -----------

step: 1 download nuget package : Install-Package Microsoft.AspNet.SignalR

step: 2 register SignalR middleware

once you have installed it let’s create OwinStartup Class. The following code adds a simple piece of middleware to the owin pipeline, implemented as a function that receives a Microsoft.Owin.IOwinContext instance. when the server receives an HTTP request, the owin pipeline invokes the middleware. The middleware sets the content type for the response and writes the response body.(/Startup)

public class Startup
{
public void Configuration(IAppBuilder app)
{
app.MapSignalR();
}
}

step: 3 create Hub class

After finishing the previous process, let’s create a Hub. A SignalR Hub make remote procedure calls (RPCs) from a server to connected clients and from clients to the server. add Hubs folder in soultion, than create EmployeeHub class in folder. (Hubs/EmployeeHub)


public class EmployeeHub : Hub
{
[HubMethodName("broadcastData")]
public static void BroadcastData()
{
IHubContext context = GlobalHost.ConnectionManager.GetHubContext<EmployeeHub>();
//the update client method will update the connected client about any recent changes in the server data
context.Clients.All.updatedData();
}
}

step: 4 update Index page

@{
ViewBag.Title = "Employee List";
}
<p>
@Html.ActionLink("Create New", "Create")
</p>
<div class="container">
<div class="table-responsive">
<div id="dataTable"></div>
</div>
</div>
@section JavaScript{
<script src="~/Scripts/jquery.signalR-2.4.1.min.js"></script>
<script src="/Signalr/Hubs"></script>
<script type="text/javascript">
$(function () {
// Reference the hub.
var hubNotif = $.connection.employeeHub;
// Start the connection.
$.connection.hub.start().done(function () {
GetAll();
});
// Notify while anyChanges.
hubNotif.client.updatedData = function () {
GetAll();
};
});

function GetAll() {
var model = $('#dataTable');
$.ajax({
url: '/Home/GetAllData',
contentType: 'application/html ; charset:utf-8',
type: 'GET',
dataType: 'html',
success: function (result) {
model.empty().append(result);
},
error: function (req, status, error) {
alert("error--" + req + "---" + status + "---" + error);
}
  });
}
</script>
}