Signalr: Want to save chat conversation in sql server but not supporting

[niravpatel]

Hi David,

I had made chat application using signalR just like facebook chat, Now I want to preserve the chat conversation data into database and want to display that historyfor its corrusponding chat room,Can you please guide how to archive that? Does signalR support communication with sql server?
Also Can you please guide how can I maintain the connection ID so that I can reinitialize the chat after the postback just like a facebook chat app.

[ZeroHackeR]

First of all, if you are looking for help, just say you are looking for help. You don't know something, it doesn't mean SignalR is not supporting for it or it's an issue.

• Since you were saying something like FB chat, I assumed you already have your user log-in state management (userid in session or cookie or whatever).
• I suggest you use HUB.
• Once a user logged in and activates chat, then you start HUB connection and get SignalR's connection id.
• Since you already have userid of the connected user, save the mapping of SignalR's connection id and userid. (eg: Table Structure: user_id, conn_id, updated_date).
• when you received chat message from a connection id, then you can easily look up from which user it came from.
• Then, save the chat log using userid and retrieve the log using userid as well.

The key point is you don't care about the conn id. You got to map it with your own consistent unique key. Again, I would suggest not to maintain the conn id. It's better be random. If it's a must to maintain the conn id, then you can overwrite default conn id generator.

[niravpatel]

Hi ZeroHackeR,

Thanks for your reply, Does it means we can pass our own connection id ? and we can continue our chat after post back? I mean after reassigning all the details to the specific room will it continue the chat??

[ZeroHackeR]

• I meant, once a user logged in, you would have his/her userid, right?
• So, you save the userid in your session variable. Eg: in your app log in page, after a user is authenticated, save his userid in session. Eg: Session["userid"] = "123";
• Then, once user connected to chat, SignalR will generate a Connection ID. You may save the conn id and user id mapping to the DB as well (Optional, but it will be useful for verification and data mining. For the sake of simplicity, I will not talk about it here)

Here's the sample methods of the Chat server:

``` C#
public override System.Threading.Tasks.Task OnConnected()
{
// Get UserID. Assumed the user is logged before connecting to chat and userid is saved in session.
string UserID = (string)HttpContext.Current.Session["userid"];

      // Get ChatHistory and call the client function. See below
      this.GetHistory(UserID); 

      // Get ConnID
      string ConnID =  Context.ConnectionId; 

      // Save them in DB. You got to create a DB class to handle this. (Optional)
      DB.UpdateConnID(UserID, ConnID); 
}

private void GetHistory(UserID)
{
      // Get Chat History from DB. You got to create a DB class to handle this.
      string History = DB.GetChatHistory(UserID); 

      // Send Chat History to Client. You got to create chatHistory handler in Client side.
      Clients.Caller.chatHistory(History );           
}

 // This method is to be called by Client 
public void Chat(string Message)
{
      // Get UserID. Assumed the user is logged before connecting to chat and userid is saved in session.
     string UserID = (string)HttpContext.Current.Session["userid"]; 

     // Save Chat in DB. You got to create a DB class to handle this
     DB.SaveChat(UserID, Message); 

     // Send Chat Message to All connected Clients. You got to create chatMessage handler in Client side.
     Clients.All.chatMessage(Message);  
}

```

NOTE: I just wrote down here. Not tested yet.

[niravpatel]

Hi ZeroHackeR,

Again many thanks, Do you have any rough example? I am not expecting to make it working 100% I just want for the reference purpose.
Many Thanks

[raybooysen]

Can we take this to StackOverflow? This isn't an issue in SignalR and would benefit more in SO than here.

[ZeroHackeR]

@niravpatel I wrote 3 methods just for you. Why asking for more? If you don't know how to save to DB, then you better go to a library and find some C# books. You should try first based on them, before asking. Lets end our topic here.

@raybooysen Totally agreed.

[davidfowl]

Closing