How can I use, in Visual Studio 2017, the "C# Interactive" window to query a source in my "Data Connections"

Question

I'm connected to an external SQL server in my "Data Connections" (in the "Server Explorer" view). I can right click my SQL source and click "New Query" to quickly look up data with SQL statements.

I would like to use LINQ instead and I think the "C# Interactive" window would be a good and quick way to do this. My problem is that I don't know how to access my 'open' data connection. The name of the database or tables are not recognized.

Answer 1

Yes, you can right click on your main project in Solution Explorer and click Initialize Interacive with Project. This will build you projects and import all the dlls into the interactive window for you. Then you can start scratching!

For example, using Entity Framework you will need to stand up your DbContext. Enter something like...

> var context = new My.Namespace.MyDataContext("blah blah blah");

Where I have written the "blah blah blah" you need to add your connection string. The interactive console does not know about your .config files so you need to provide the connection string.

Note: To be able to do this make sure you have the nameOrConnectionString constructor override on your data context.

Now that you have the context it is as simple as normally querying the context...

> context.Users.Where(u => u.IsActive).Select(u => u).ToList()

Important
Take note that I have left the semicolon (;) off the end of the query. This is important as it tells the console to output the value of the query/command/line of code. Nothing will happen if you leave this off.

Answer 2

I got this to work by creating a class library that opens a connection to an EF data model, importing the DLL into the C# interactive window, and executing Linq statements against the data model.

First, create the class library, add the EF data model, and modify your DbContext (entities) class to use the constructor that takes a connection string. You need to do this to use this library from the c# interactive window, because if you don't, the interactive window will be looking for an app.config file with the connection string.

public partial class YourDBEntities : DbContext
{
    public YourDBEntities(string connectionString)
        : base(connectionString)
    {
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        throw new UnintentionalCodeFirstException();
    }

    ....
}

If your class library, add a class with a static method for getting a data context:

public class AccessorClass
{
    public static YourDBEntities GetDataContext()
    {
        return new YourDBEntities("metadata=res://*/Model1.csdl|res://*/Model1.ssdl|res://*/Model1.msl;provider=System.Data.SqlClient;provider connection string=\";data source=xxxxxxx;initial catalog=xxxxxxx;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework\";");
    }
}

Compile the class library, and then import the DLL into your interactive window, and query away:

> #r "C:\Path...\bin\Debug\YourClassLibrary.dll"
> using YourClassLibrary;
> using (var ctx = AccessorClass.GetDataContext())
. {
.     Console.Write(ctx.Orders.Where(c => c.ProjectID == 309).Count().ToString());
. }

Answer 3

The solution I am proposing may not be exactly what you are looking for, but I think it will help you figuring out what you need. One way I have done sth similar is by creating a DA library and using that in C# Interactive Window. Below is the sample:

I would have a class library project, MyProject.MyDA:

namespace MyDa
{
    public class CustomerDa
    {
        public DataTable LoadData(string sqlCommandText = "")
        {
            //do your try catch finally and all the good stuff
            var connString = @"Data Source=ServerName;Initial Catalog=AdventureWorks2014;Integrated Security=SSPI;";
            var conn = new SqlConnection(connString);
            SqlDataReader dataReader;
            //you could accept the command text as a parameter
            string sql = "select top 10 * FROM [AdventureWorks2014].[HumanResources].[Department]";
            var result = new DataTable("Department");
            conn.Open();
            SqlCommand command = new SqlCommand(sql, conn);
            dataReader = command.ExecuteReader();
            result.Load(dataReader);
            dataReader.Close();
            command.Dispose();
            conn.Close();
            //instead of a datatable, return your object
            return result;
        }
    }
}

Build your DA project, now in C# Interactive, you would do sth like:

> #r "D:\blah\Blah\MyDa\bin\Debug\MyDa.dll"
> using MyDa;
> var a = new CustomerDa();
> var r = a.LoadData();
> r.Rows[0]
DataRow { HasErrors=false, ItemArray=object[4] { 1, "Engineering", "Research and Development", [4/30/2008 12:00:00 AM] }, RowError="", RowState=Unchanged, Table=[] }
> r.Rows.Count //you can do all the good LINQ stuff now on the result
10

You can do it this way, but I feel this flow requires more work and ceremony than I would like and is still imperfect. Anyways, that's one way to accomplish what you are looking for. I would also recommend using LinqPad if you prefer to query using LINQ.

Answer 4

This Sould work !

    SELECT 
    DB_NAME(dbid) as DBName, 
    COUNT(dbid) as NoOfConnections,
    loginame as LoginName
    FROM
    sys.sysprocesses
    WHERE 
    dbid > 0
    GROUP BY 
dbid, loginame

You can check this link find number of open connection on database