Storing chat conversations in sql database using s

Question

I'm developing a class library that contains generic methods for these scenarios:

• Live support chat (1 on 1 private text chat, with many admins and guests)
• Rooms with many users where you can send broadcast and private messages

These two features above are already implemented and now it's necessary for my application to save messages.

My question is, what is the best way to store chat conversations in a SQL database:

1. Everytime I click send, I insert the message in the database?

2. Create a List for each user and everytime I click send, the message is saved on the list of the user who sent the message. Then if a user disconnects, I'm going to iterate the list of messages and for each message insert all of them in the db.

Are there other solutions?

What I'm doing now is the following. I have this method which is located on my Hub class:

public void saveMessagetoDB(string userName, string message)
{
    var ctx = new TestEntities1();

    var msg = new tbl_Conversation {Msg = message};
    ctx.tbl_Conversation.Add(msg); 
    ctx.SaveChanges();           
}

I call this method saveMessagetoDB on my client side HTML file like this:

$('#btnSendMessage').click(function () {
       var msg = $("#txtMessage").val();

       if (msg.length > 0) {

           var userName = $('#hUserName').val();
           // <<<<<-- ***** Return to Server [  SaveMessagetoDB  ] *****
           objHub.server.saveMessagetoDB(userName, msg);

Answer 1

If you decide to store the chats in a database, then you will need to insert/update the messages as they happen.

If you are using the PersistentConnection then you can hook into the OnReceivedAsync event and insert / update data from that event:

protected override Task OnConnectedAsync(IRequest request, string connectionId)
{
    _clients.Add(connectionId, string.Empty);
    ChatData chatData = new ChatData("Server", "A new user has joined the room.");
    return Connection.Broadcast(chatData);
}

Or in the SignalR class that inherits from Hub, you can persist to the Db right before you have notified any clients.

Answer 2

SignalR is great for a chat application and you wouldn't even need to store anything in SQL unless you want to create a transcript of the chat at a later time (which may not even be necessary).

I suggest getting the chat working with SignalR first (don't do anything with sql). Then once that is working you can put SQL logging as necessary in your signalR hub.

It most likely makes the most sense to write to sql on each message.