你如何在c＃中嵌入sql查询

时间：2021-02-06 23:35:41

I'm writing a new project in C# with a number of SQL queries in it. Some are relatively large and complex. I want to know what the best way to store them is. Ideally I would create stored procedures on the database, but the database is used by many other applications so it's better if I can keep the procedures which are specific to my application in my application.

我正在用C#编写一个新项目,其中包含许多SQL查询。有些相对较大且复杂。我想知道存储它们的最佳方法是什么。理想情况下,我会在数据库上创建存储过程,但许多其他应用程序使用该数据库,因此如果我可以在我的应用程序中保留特定于我的应用程序的过程,那就更好了。

Options seem to be:

选项似乎是:

  1. a string literal (const string query ="Select * From MyTable")
    • Pros: simple, short

      • 优点:简单,简短

    • Cons: no Syntax highlighting, messy for long queries

      • 缺点:没有语法高亮,凌乱的长查询

    2. 一个字符串文字(const string query =“Select * From MyTable”)优点:简单,简短缺点:没有语法高亮,凌乱的长查询

  2. Create a file for each query as QueryName.sql
    • Pros: syntax highlighting, neater for large, complex queries

      • 优点:语法突出显示,适用于大型复杂查询

    • Cons: lots of files for lots of queries (one query per file), maybe slower to read query from content file?

      • 缺点:大量查询的文件很多(每个文件一个查询),从内容文件中读取查询的速度可能较慢?

    3. 为QueryName.sql创建每个查询的文件优点:语法高亮,适用于大型复杂查询的缺点缺点:大量查询的文件(每个文件一个查询),从内容文件读取查询可能更慢?

  3. Any other ideas?

    4. 还有其他想法吗?

As an additional thought, is there a way to easily generate strongly typed class definitions from the SQL queries?

另外一个想法是,有没有办法从SQL查询中轻松生成强类型类定义?

3 个解决方案

#1

21  

Another option would be:

另一种选择是:

4: Create a file for each query as QueryName.sql

4:为QueryName.sql创建每个查询的文件

but make it an embedded resource in your assembly. That way, you don't have physical files on disk, but your SQL queries are nicely tucked into their own file in your Visual Studio solution, and you can easily extract the SQL text from those embedded resources into your SqlCommand objects.

但要使其成为装配中的嵌入式资源。这样,您在磁盘上没有物理文件,但您的SQL查询很好地隐藏在Visual Studio解决方案中的自己的文件中,您可以轻松地将这些嵌入资源中的SQL文本提取到SqlCommand对象中。

Check out these resources to get you started:

查看这些资源以帮助您入门:

#2

2  

Why not simply use Entity framework or Linq-to-SQL

为什么不简单地使用Entity框架或Linq-to-SQL

If you have a table named Foos yoiu end up with code like:

如果你有一个名为Foos yoiu的表最终得到如下代码:

using(var db = new MyEntityBase()){

    var selectedFoo = from foo in db.Foos
                      where foo.Bar > 4
                      select foo;
    //Do stuff
}

which in turns translate to SQL like:

它反过来转换为SQL,如:

select * from Foos where Bar = 4

the C# code above is all strongly typed and Entity framework will create all the needed data classes for you.

上面的C#代码都是强类型的,Entity框架将为您创建所有需要的数据类。

#3

0  

I woud go for any ORM like EF or Subsonic.

我想去EF或者亚力士等任何ORM。

#1

21  

Another option would be:

另一种选择是:

4: Create a file for each query as QueryName.sql

4:为QueryName.sql创建每个查询的文件

but make it an embedded resource in your assembly. That way, you don't have physical files on disk, but your SQL queries are nicely tucked into their own file in your Visual Studio solution, and you can easily extract the SQL text from those embedded resources into your SqlCommand objects.

但要使其成为装配中的嵌入式资源。这样,您在磁盘上没有物理文件,但您的SQL查询很好地隐藏在Visual Studio解决方案中的自己的文件中,您可以轻松地将这些嵌入资源中的SQL文本提取到SqlCommand对象中。

Check out these resources to get you started:

查看这些资源以帮助您入门:

#2

2  

Why not simply use Entity framework or Linq-to-SQL

为什么不简单地使用Entity框架或Linq-to-SQL

If you have a table named Foos yoiu end up with code like:

如果你有一个名为Foos yoiu的表最终得到如下代码:

using(var db = new MyEntityBase()){

    var selectedFoo = from foo in db.Foos
                      where foo.Bar > 4
                      select foo;
    //Do stuff
}

which in turns translate to SQL like:

它反过来转换为SQL,如:

select * from Foos where Bar = 4

the C# code above is all strongly typed and Entity framework will create all the needed data classes for you.

上面的C#代码都是强类型的,Entity框架将为您创建所有需要的数据类。

#3

0  

I woud go for any ORM like EF or Subsonic.

我想去EF或者亚力士等任何ORM。

标签：

相关文章