在 C# WPF 中连接到 SQL Server 的最佳方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32977270/
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
Best method to connect to SQL Server in C# WPF
提问by version2.xx
I'm a beginner.
我是初学者。
I already found a way to connect to SQL SERVER using the codes below:
我已经找到了一种使用以下代码连接到 SQL SERVER 的方法：
private void getListBtn_Click(object sender, RoutedEventArgs e)
{
SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=myDB;Integrated Security=true;");
SqlDataAdapter sda = new SqlDataAdapter("SELECT ID,Date,Name,City FROM Info;", con);
DataTable dt = new DataTable();
sda.Fill(dt);
dataGridForm.ItemsSource = dt.DefaultView;
I also wanted to get number of rows from a TABLE and set it to a label, But it's not a good idea to copy and paste this code again, I want to have a method for sqlconnection so i won't rewrite this code again and again for every single query.
我还想从 TABLE 中获取行数并将其设置为标签，但是再次复制和粘贴此代码不是一个好主意，我想要一种用于 sqlconnection 的方法，因此我不会再次重写此代码并且再次为每个查询。
Sorry i'm an absolute beginner, 3 days since i started learning C# WPF.
对不起，我是一个绝对的初学者，自从我开始学习 C# WPF 3 天以来。
回答by Ashish
First thing this is not related to WPF, this is general coding even I would not consider this to be related to .net.
首先这与 WPF 无关，这是通用编码，即使我不认为这与 .net 相关。
For your current problem to show the count, you dont have to make a call again. You can get the count from the datatable row count. But, I would suggest few things:
对于您当前显示计数的问题，您不必再次拨打电话。您可以从数据表行计数中获取计数。但是，我建议几件事：
- You should have one or different separate layers like business, data access etc. as per your needs.
- You should not give the connection as the way you have provided here.
- You can choose to use any ORMs like entity framework, NHibernate etc based on your needs. This just a direction, you can choose to stick with ADO.Net as you have it your choice. But I would definitely suggest to throw in more layers to avoid duplicate codes and more structured approach.
- 根据您的需要，您应该拥有一个或不同的独立层，例如业务、数据访问等。
- 您不应该按照您在此处提供的方式提供连接。
- 您可以根据需要选择使用任何 ORM，如实体框架、NHibernate 等。这只是一个方向，您可以根据自己的选择选择坚持使用 ADO.Net。但我绝对建议投入更多层以避免重复代码和更结构化的方法。
回答by Nicolas C
Yes some frameworks and/or ADO's solutions are good and maybe the best "professionnal" approch, you say you're a beginner and I was it not so far ;-).
是的，某些框架和/或 ADO 的解决方案很好，也许是最好的“专业”方法，您说您是初学者，而我到目前为止还没有;-)。
So the simpliest way is to add a new class for the sql connection. In example add a Sqlconnect.cs class.
所以最简单的方法就是为sql连接添加一个新的类。在示例中添加一个 Sqlconnect.cs 类。
using System.Data.SqlClient;
public class Sqlconnect
{
public SqlConnection Con { get; set; }//the object
private string conString { get; set; }//the string to store your connection parameters
}
This class will have a method to open the connection and one to close it.
这个类将有一个打开连接的方法和一个关闭它的方法。
public void conOpen()
{
conString = "Data Source=..."; //the same as you post in your post
Con = new SqlConnection(conString);//
try
{
Con.Open();//try to open the connection
}
catch (Exception ex)
{
//you do stuff if the connection can't open, returning a massagebox with the error, write the error in a log.txt file...
}
}
public void conClose()
{
Con.Close();//close the connection
}
In your other(s) classe(s) where you need a sql query you first instantiate an new object.
在您需要 sql 查询的其他类中，您首先实例化一个新对象。
private void getListBtn_Click(object sender, RoutedEventArg e)
{
Sqlconnect con = new Sqlconnect();//instantiate a new object 'Con' from the class Sqlconnect.cs
con.conOpen();//method to open the connection.
//you should test if the connection is open or not
if(con!= null && con.State == ConnectionState.Open)//youtest if the object exist and if his state is open
{
//make your query
SqlDataAdapter sda = new SqlDataAdapter("your query", con);
//etc
con.conClose();//close your connection
}
else
{
//the connection failed so do some stuff, messagebox...as you want
return;//close the event
}
}
this example need some ameliorations, it's evident but I wrote it like this to be clearest.
这个例子需要一些改进，很明显，但我这样写是为了最清楚。
