如何在Visual Studio 2017中使用“C＃Interactive”窗口查询“数据连接”中的源代码

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.

我在“数据连接”（在“服务器资源管理器”视图中）连接到外部SQL服务器。我可以右键单击我的SQL源并单击“新建查询”以使用SQL语句快速查找数据。

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.

我想改用LINQ，我认为“C＃Interactive”窗口是一个很好的快速方法。我的问题是我不知道如何访问我的“开放”数据连接。无法识别数据库的名称。

4 个解决方案

#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!

是的，您可以在解决方案资源管理器中右键单击主项目，然后单击“使用项目初始化Interacive”。这将构建您的项目并将所有dll导入到交互式窗口中。然后你就可以开始刮擦了！

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

例如，使用Entity Framework，您需要站起来使用DbContext。输入类似......

> 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.

在我写“blah blah blah”的地方你需要添加你的连接字符串。交互式控制台不知道您的.config文件，因此您需要提供连接字符串。

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

注意：为了能够执行此操作，请确保在数据上下文中具有nameOrConnectionString构造函数覆盖。

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.

重要请注意，我已将分号（;）从查询末尾移开。这很重要，因为它告诉控制台输出查询/命令/代码行的值。如果你放弃它，什么都不会发生。

#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.

我通过创建一个类库来打开与EF数据模型的连接，将DLL导入C＃交互式窗口，并对数据模型执行Linq语句。

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.

首先，创建类库，添加EF数据模型，并修改DbContext（实体）类以使用带有连接字符串的构造函数。您需要这样做才能在c＃interactive窗口中使用此库，因为如果不这样做，交互式窗口将查找带有连接字符串的app.config文件。

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:

编译类库，然后将DLL导入交互式窗口，并查询：

> #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());
. }

#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:

我提出的解决方案可能并不完全符合您的要求，但我认为它可以帮助您找出您需要的内容。我做过类似的一种方法是创建一个DA库并在C＃Interactive Window中使用它。以下是样本：

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

我会有一个类库项目，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:

现在在C＃Interactive中构建你的DA项目，你会这样做：

> #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.

你可以这样做，但我觉得这个流程需要比我想要的更多的工作和仪式，并且仍然是不完美的。无论如何，这是实现您所需要的一种方式。如果您更喜欢使用LINQ查询，我还建议使用LinqPad。

#4

-5

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

您可以在此链接中查找数据库中的打开连接数

#1