blocks|key|1405741|text|我们使用的一种方法是使用一个连接到DB的类和调用过程的方法，并在method参数中提供过程名称。所以所有的SQL代码都在这个过程中。我们将对不同的返回类型使用重载|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1405742|class+ConnectToSQL()
{
++++++++//connectSql+code+(read+from+setting+file+i+assume)

++++++++XMLDataDocument+runProcedure(string+procedureName);
++++++++int+runProcedure(string+procedureName);

++++++++//etc....
}|code-block|syntax|javascript|1405743|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

One method we used is to have a class that would connect to the DB and methods to call procedures and in the method parameter you would provide the procedure name. so all the SQL code is in the procedure. we would use overloads for the different return types

<pre><code>class ConnectToSQL()
{
 //connectSql code (read from setting file i assume)

 XMLDataDocument runProcedure(string procedureName);
 int runProcedure(string procedureName);

 //etc....
}
</code></pre>

blocks|key|1620616|text|嗯，有很多选择-所以这真的取决于你最迫切的需求是什么:-)|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1620617|一种方法可能是在VS解决方案中将SQL语句创建为文本文件，并在“构建操作”中将它们标记为“嵌入式资源”。这样，SQL就包含在生成的程序集中，并且可以在运行时使用.NET框架的ResourceManifestStream从其中检索：|1620618|private+string+LoadSQLStatement(string+statementName)
{
++++string+sqlStatement+=+string.Empty;

++++string+namespacePart+=+"ConsoleApplication1";
++++string+resourceName+=+namespacePart+%2B+"."+%2B+statementName;

++++using(Stream+stm+=+Assembly.GetExecutingAssembly().GetManifestResourceStream(resourceName))
++++{
++++++++if+(stm+!=+null)
++++++++{
++++++++++++sqlStatement+=+new+StreamReader(stm).ReadToEnd();
++++++++}
++++}

++++return+sqlStatement;
}|code-block|syntax|javascript|1620619|您需要将"ConsoleApplication1“替换为您的实际名称空间，即sql语句文件所在的名称空间。您需要通过完全限定名称来引用它们。然后，您可以使用以下行加载您的SQL语句：|1620620|string+mySQLStatement+=+LoadSQLStatement("MySQLStatement.sql");|1620621|然而，这使得查询相当“静态”，例如，你不能在运行时配置和更改它们-它们被直接烘焙到编译后的二进制位中。但另一方面，在VS中，您可以将C#程序代码和SQL语句很好地分离开来。|1620622|如果您需要能够在运行时调整和更改它们，我会将它们放入一个单独的SQL表中，该表包含一个关键字和实际的SQL查询作为字段。然后，您可以根据需要检索它们并执行它们。由于它们在数据库表中，您还可以随意更改、修复和修改它们-甚至在运行时-而不必重新部署整个应用程序。|1620623|Marc|1620624|entityMap^0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|V|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|W|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|X|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|Y|8|@]|9|@]|A|$G|H]]|$1|M|3|N|5|6|7|Z|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|10|8|@]|9|@]|A|$]]|$1|Q|3|R|5|6|7|11|8|@]|9|@]|A|$]]|$1|S|3|-4|5|6|7|12|8|@]|9|@]|A|$]]]|T|$]]

Well, there's a lot of options - so it really depends on what your most pressing needs are :-)

One approach might be to create SQL statements as text files inside your VS solution, and mark them as "embedded resource" in the "build action". That way, the SQL is included in your resulting assembly, and can be retrieved from it at runtime using the ResourceManifestStream of the .NET framework:

<pre><code>private string LoadSQLStatement(string statementName)
{
 string sqlStatement = string.Empty;

 string namespacePart = "ConsoleApplication1";
 string resourceName = namespacePart + "." + statementName;

 using(Stream stm = Assembly.GetExecutingAssembly().GetManifestResourceStream(resourceName))
 {
 if (stm != null)
 {
 sqlStatement = new StreamReader(stm).ReadToEnd();
 }
 }

 return sqlStatement;
}
</code></pre>

You need to replace "ConsoleApplication1" with your actual namespace, in which the sql statement files reside. You need to reference them by means of the fully qualified name. Then you can load your SQL statement with this line:

<pre><code>string mySQLStatement = LoadSQLStatement("MySQLStatement.sql");
</code></pre>

This however makes the queries rather "static", e.g. you cannot configure and change them at runtime - they're baked right into the compiled binary bits. But on the other hand, in VS, you have a nice clean separation between your C# program code, and the SQL statements.

If you need to be able to possibly tweak and change them at runtime, I'd put them into a single SQL table which contains e.g. a keyword and the actual SQL query as fields. You can then retrieve them as needed, and execute them. Since they're in the database table, you can also change, fix, amend them at will - even at runtime - without having to re-deploy your whole app.

Marc

blocks|key|1405897|text|当我真的需要它时，我将查询放入单独的*.sql文件中，然后将它们包含到Resources.resx中。其中有一个“文件”部分，允许您包含嵌入式资源文件。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1405898|在此之后，我可以使用生成的Resources.MyQuery属性，它既保证了资源的存在，又使我不必编写自定义的资源加载方法。|1405899|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

When I really need it, I put the queries into individual *.sql files, then include them into Resources.resx. There is a 'Files' section in it, which allows you to include Embedded Resource files.

After that, I can use generated Resources.MyQuery property which both guarantees that resource exists and saves me from writing a custom resource load method.

blocks|key|1620768|text|如果我必须同时为SQL和Access创建应用程序，我会使用一些IDAL接口，即具有公共功能实现和从DALCommon继承的独立DALSql和DALAccess的DALCommon，以及一些特定的东西，如异常、事务处理、安全性等。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1620769|我过去常常将存储过程名称或查询保存在资源文件中。|1620770|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

If i'd had to create application for both SQL and Access, I'd use some IDAL interface, DALCommon with common functionality implementation and separate DALSql and DALAccess, inherited from DALCommon, with some specific stuff, like exceptions, transactions handling, security etc. 
I used to keep stored procedure names or queries in resource files.

blocks|key|1620864|text|有时，就像使用自定义报告应用程序一样，您真的需要接受阻抗不匹配，并特别重视SQL。在这些情况下，我建议这样做:对于包含SQL字符串的每个模块，创建一个静态"SQL“类来容纳所有这些字符串。一些SQL字符串可能需要参数，因此要保持一致，并将每个字符串放在其自己的静态方法后面。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1620865|我只为偶尔定制的报告应用程序这样做，但它总是工作得很好，感觉很清新和解放。几个月后再回来做一个增强，发现所有的SQL都在一个SQL.cs文件中等着你，这真是太好了。只需读取一个文件，它就会全部返回，并且通常这是唯一需要更改的文件。|1620866|在这些情况下，我认为没有必要在资源或其他地方隐藏SQL。当SQL很重要的时候，它就很重要。有趣的是，现在越来越多的开发人员自由地将SQL与C#混合在一起，包括我相信这个网站，因为本质上，这就是LINQ。|1620867|最后，一如既往地，确保您不会受到SQL注入攻击。尤其是当涉及到用户输入时，请确保使用某种参数化，而不是使用字符串连接。|1620868|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|J|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|K|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|L|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|M|8|@]|9|@]|A|$]]|$1|H|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|I|$]]

Sometimes, like with custom reporting apps, you really need to embrace the impedance mismatch, and give special importance to the SQL. In these cases I recommend the following: For each module that contains SQL strings, create a single static "SQL" class to hold them all. Some of the SQL strings will likely require parameters, so be consistent and put each string behind it's own static method.

I only do this for the occasional custom reporting app, but it always works out great and feels refreshing and liberating. And it's quite nice to come back months later to make an enhancement, and find all of the SQL waiting for you in a single SQL.cs file. Just by reading that one file, it all comes back, and often this is the only file that needs to be changed. 

I don't see a need in these cases for hiding the SQL in resources or elsewhere. When SQL is important, then it's important. Interestingly, more and more developers are now freely mixing SQL with C#, including I believe this site, because essentially, that's what LINQ is.

Finally, as always, make sure you are not susceptible to SQL injection attacks. Especially if user input is involved, make sure you are using some kind of parameterization and that you are not using string concatenation.

blocks|key|1406112|text|如果SQL+Query有一个类似的"where“原因，那么上面显示的嵌入解决方案可能不起作用，但是对于相同的查询，下一次运行需要PropertyID='113‘，因为PropertyID是读入的。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1406113|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Embedding solutions shown above may not work if SQL Query has a "where" cause like , but for the same Query the next run needs PropertyID='113' as the PropertyID is read-in.

I will likely be responsible for porting a vb6 application to c#. This application is a windows app that interacts with an access db. The data access is encapsulated in basic business objects. One class for one table basically. The existing vb6 business objects read and write to the DB via DAO. I have written DALs and ORMs a few times before but they all targeted SQL Server only. This one will need to target access and sql server. In previous projects, I would place the SQL strings in the private parts of the business object and maybe move the redundant sql code like connecting, creating command, in into a common base class to reduce the code.

This time, i'm thinking about writing the SQL strings into a .settings file or some other key/value type text file. I would then write a sql utility to edit this file and allow me to run and test the parameterized queries. These queries would be referenced by name in the business object instead of embedding the sql into code.

I know a standard approach is to create a DAL for each targeted database and have the configuration state which DAL to use. I really don't want to create the two DAL classes for each database. It seems like it would be less code if I just referenced the correct query by keyname and have the proper type of connection.

So, are you guys doing things like this? How would or have you approached this problem? 
What works best for you?

Thanks!

Where do you put SQL Statements in your c# projects?

SQL注入攻击

SQL Server

数据库

我可能会负责将vb6应用程序移植到c#。此应用程序是与access数据库交互的windows应用程序。数据访问被封装在基本业务对象中。基本上，一个类对应一个表。现有的vb6业务对象通过DAO对数据库进行读写。我以前写过几次DAL和ORM，但它们都只针对SQL Server。这将需要以access和sql server为...

问您将SQL语句放在c#项目中的什么位置？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问您将SQL语句放在c#项目中的什么位置？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问您将SQL语句放在c#项目中的什么位置？
EN