0

I have a SQL CLR stored procedure written in c# (.NET4). Its purpose is to allow a trigger on a table in a SQL Server 2012 database to call a web service which then processes the data in that table.

However, there are several different databases which will all have triggers using this assembly. My web service needs to know which database is triggered the call to it in order to know where to get the data from.

I could simply add a parameter to my stored procedure but I want to keep things simple from the database side. Is there any way, in .NET, to obtain information about the database to which the assembly is attached?

Mansfield
  • 14,445
  • 18
  • 76
  • 112
  • Short of calling *back* to the database somehow and querying who it is ("SELECT db_name()"), I'm not seeing any way to do it. – Richard Jun 14 '13 at 17:54
  • I see. I suppose if were calling back to the database you would already know the database name though :P – Mansfield Jun 14 '13 at 17:55
  • You can use a [Context Connection](http://msdn.microsoft.com/en-us/library/ms131053.aspx), so you don't have to specify the database name. I still think this isn't quite what you're looking for, though. – Richard Jun 14 '13 at 17:59

1 Answers1

2

Aah, found one:

This still opens a context connection to the database, but it's about the only way I can see.

using (SqlConnection conn = new SqlConnection("context connection=true"))
{
    conn.Open();
    string dbName = conn.Database
}

That's from an MSDN article. Also, the MSDN article on Context Connection.

Richard
  • 6,215
  • 4
  • 33
  • 48
  • @Mansfield, as one of the people who rejected the edit, I don't know if my comment got through: only edits which don't change the content of an answer will get through. Please put your observed corrections in a comment and request Richard to make the changes, that way the answer remains his. I'm sure you can see why this makes sense. – Neil Townsend Jun 14 '13 at 18:46
  • I'm all for improvements to the answer, for what it's worth. Let me know and I'll add them. – Richard Jun 14 '13 at 18:53
  • So am I, but it's reasonable to ask someone making suggestions for corrections to put them in a comment so the answerer can agree or disagree - if they went through as edits you can imagine all sorts of disputes breaking out in terms of who said what and whose answer it was ... – Neil Townsend Jun 14 '13 at 18:54
  • It works, but not until I changed `contextconnection` to `context connection`. Once you make that change I'll accept the answer :) – Mansfield Jun 14 '13 at 19:04
  • @NeilTownsend I saw your comment. I agree, except for something which is an obvious typo, which is typed correctly in a link he previously provided, I thought it would be okay and save everyone effort. – Mansfield Jun 14 '13 at 19:05
  • @Mansfield the challenge there is that it may be an obvious typo to you, but it's not always obvious to others! I looked long and hard and couldn't convince myself that it was absolutely, definitively a correct typo correction ... – Neil Townsend Jun 14 '13 at 19:08
  • @NeilTownsend I suppose I should have included the link he posted earlier to the official MSDN documenation: http://msdn.microsoft.com/en-us/library/ms131053.aspx. Still, I understand why you rejected it, and it's all resolved now :) – Mansfield Jun 14 '13 at 19:10
  • Aah, the space? Thanks. Fixed. :) – Richard Jun 14 '13 at 19:10