C# 我应该如何从 WCF 服务解决方案访问 SQL Server？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10247351/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How should I access SQL Server from a WCF service solution?
提问by Sam
I have some existing WCF code that accesses SQL Server 2005, but honestly I've come to mistrust that developer's methods, so I want to know how this should be done correctly and professionally. I need to be able to pass an SQL statement to a method (within the WCF service, not from the client) that returns the resultant dataset (to the method in WCF that called it, not the client). I'm not interested in entity frameworks or other abstraction layers. I need to run SQL, DML, and hopefully DDL too.
我有一些现有的 WCF 代码可以访问 SQL Server 2005，但老实说，我开始不信任该开发人员的方法，所以我想知道应该如何正确和专业地完成此操作。我需要能够将 SQL 语句传递给返回结果数据集的方法（在 WCF 服务中，而不是从客户端）（到 WCF 中调用它的方法，而不是客户端）。我对实体框架或其他抽象层不感兴趣。我需要运行 SQL、DML，希望也能运行 DDL。
I also want to know how to manage the connections.
我也想知道如何管理连接。
Please point out your thoughts on better alternatives if you feel like it. I'm prepared to listen.
如果您愿意，请指出您对更好选择的想法。我准备听。
采纳答案by Sam
- Save your connection string in the configuration file.
- Connect to the Database as much as you like, Connection Poolwill take care of that you do not have to worry about it.
Always use
usingwhen dealing with sql connections.Using (sqlConn = new SqlConnection(ConnString)) { }Make your own POCOs to return the result to the client, you can either make them in seperate assembly and share it in both projects (WCF and client) or you can just add them to the WCF and when you create the proxy at the client side you will have access to them.
- 将您的连接字符串保存在配置文件中。
- 尽可能多地连接到数据库，连接池会照顾你，你不必担心。
using处理sql连接时总是使用。Using (sqlConn = new SqlConnection(ConnString)) { }制作您自己的 POCO 以将结果返回给客户端，您可以在单独的程序集中制作它们并在两个项目（WCF 和客户端）中共享，也可以将它们添加到 WCF 并在客户端创建代理时您将可以访问它们。
Here is a sample layout:
这是一个示例布局：
public Poco Foo(long id)
{
try
{
using (SqlConnection SqlConn = new SqlConnection(ConnString))
{
// execute your commands and do your stuff
return Poco;
}
}
catch (Exception ex)
{
Logger.Log(ex.ToString());
return null;
}
}
UPDATE
更新
Here is an example of how to return a 'DataSet` to the client, I do not recommend this but it will work:
这是如何将“DataSet”返回给客户端的示例，我不建议这样做，但它会起作用：
public DataSet Foo(long id)
{
try
{
using (SqlConnection SqlConn = new SqlConnection(ConnString))
{
SqlCommand sqlCmd = new SqlCommand("Select * From users where userid=@id", SqlConn);
sqlCmd.Parameters.Add("@id", SqlDbType.BigInt).Value = id;
DataSet ds = new DataSet();
using (SqlDataAdapter da = new SqlDataAdapter(sqlCmd))
{
da.Fill(ds, "Users");
}
return ds;
}
}
catch (Exception ex)
{
Logger.Log(ex.ToString);
return null;
}
}
回答by ken2k
Honestly, you shouldn't access SQL Serverfrom a WCF service.
老实说，您不应该从 WCF 服务访问SQL Server。
You should access datafrom a WCF Service, without knowing there is SQL Server under the hood.
您应该从 WCF Service访问数据，而不知道幕后有 SQL Server。
Exposing a webservice that accepts a SQL statement looks definitely like a terrible idea to me. You don't want to expose your database to the client of your service for a lot of reasons (security...etc.).
公开接受 SQL 语句的 Web 服务对我来说绝对是一个糟糕的主意。出于多种原因（安全性等），您不想将数据库暴露给服务的客户端。
What you could (should) do is to write a service that returns the data you actually want to return. For instance:
您可以（应该）做的是编写一个返回您实际想要返回的数据的服务。例如：
[DataContract]
public class Customer
{
[DataMember]
public string Name { get; set; }
}
[ServiceContract]
public interface IService
{
Customer GetCustomer(int customerId);
}
[ServiceBehavior]
public class Service
{
[OperationContract]
public Customer GetCustomer(int customerId)
{
// Insert DB-related implementation of your query:
// - you could hard-code a SQL query
// - you could use Entity-Framework or other ORM
//
// First, create your connection to your database
// Then query
// Then close your connection
//
// Example with SQL connection
// Connection string comes from server configuration (app.config or whatever)
using (SqlConnection cn = new SqlConnection(connectionString))
{
Customer res = new Customer();
// query here
Customer.Name = XXX; // From DB result
}
}
}
On client side:
在客户端：
ServiceClient proxy = new ServiceClient();
Customer myCustomer = proxy.GetCustomer(42);
You really shouldn't think about reusing SQL connections. The connection pool of your database will handle this for you. Creating/closing a connection to the DB is a light operation. Creating a new connection for each webservice call will avoid LOTS of trouble (connection lifetime...etc.).
您真的不应该考虑重用 SQL 连接。您的数据库的连接池将为您处理此问题。创建/关闭与数据库的连接是一个轻量级的操作。为每个 webservice 调用创建一个新连接将避免很多麻烦（连接寿命......等）。
Use stateless services as much as possible, it'll save you a lot of time (concurrency issues, object lifetime management...).
尽可能使用无状态服务，它会为您节省大量时间（并发问题、对象生命周期管理...）。
