如何从 C# 连接到 SQL 数据库？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1345508/
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 do I connect to a SQL database from C#?
提问by RCIX
I am trying to write a local program management and install system for my home network, and I think I've got the technologies nailed down:
我正在尝试为我的家庭网络编写本地程序管理和安装系统，我想我已经掌握了技术：
- C#/.NET/WPF for the client
- Lua for installation scripting support (through LuaInterface)
- SQL Server Express for maintaining a database of programs
- 客户端的 C#/.NET/WPF
- Lua 用于安装脚本支持（通过 LuaInterface）
- 用于维护程序数据库的 SQL Server Express
However I'm unsure what specifically I'll use to connect C# to the database. Is there something built into the .NET framework for this? Bonus points if you have a suggestion on what I should use for interacting with said database.
但是我不确定我将使用什么来将 C# 连接到数据库。.NET 框架中是否为此内置了一些东西？如果您对我应该使用什么与所述数据库进行交互有建议，则加分。
采纳答案by marc_s
Check out
查看
I'm sure there's plenty more out there - just google for "ADO.NET" and "Tutorial" ......
我敢肯定还有更多的东西 - 只需在谷歌上搜索“ADO.NET”和“教程”......
UPDATE:
更新：
If you want to connect to your local SQL Server Express, and connect to the "Northwind" database, and read the top 5 customers from the "Customers" table, you'd have to do something like this:
如果要连接到本地 SQL Server Express，并连接到“Northwind”数据库，并从“Customers”表中读取前 5 个客户，则必须执行以下操作：
string connectionString = "server=(local)\SQLExpress;database=Northwind;integrated Security=SSPI;";
using(SqlConnection _con = new SqlConnection(connectionString))
{
string queryStatement = "SELECT TOP 5 * FROM dbo.Customers ORDER BY CustomerID";
using(SqlCommand _cmd = new SqlCommand(queryStatement, _con))
{
DataTable customerTable = new DataTable("Top5Customers");
SqlDataAdapter _dap = new SqlDataAdapter(_cmd);
_con.Open();
_dap.Fill(customerTable);
_con.Close();
}
}
Now you would have all 5 top customers from your Northwind database in the DataTable and you can inspect them, print them out, manipulate them - whatever you want to do.
现在，您将在 DataTable 中拥有 Northwind 数据库中的所有 5 个顶级客户，您可以检查它们、打印它们、操作它们——无论您想做什么。
That's ADO.NET in action!
这就是 ADO.NET 的作用！
As for the details of the connection string - what options you can use and what it should look like, check out the Connection Stringsweb site - it has tons of examples and explanations.
至于连接字符串的详细信息 - 您可以使用哪些选项以及它应该是什么样子，请查看连接字符串网站 - 它有大量示例和解释。
Marc
马克
回答by Noon Silk
Sure of course, you can just use the classes in System.Data.SqlClient, though most people will use an ORM. I use LLBLGen Pro.
当然，您可以只使用 System.Data.SqlClient 中的类，尽管大多数人会使用 ORM。我使用LLBLGen Pro。
回答by rahul
object is made for this.
对象是为此而制作的。
Eg:
例如：
SqlConnection conn = new SqlConnection(
"Data Source=(local);Initial Catalog=Northwind;Integrated Security=SSPI");
or
或者
SqlConnection conn = new SqlConnection(
"Data Source=DatabaseServer; Initial Catalog=Northwind; User ID=YourUserID; Password=YourPassword");
conn.Open(); // opens the database connection
Edit:
编辑：
After doing all your stuff you have to close the connection by
完成所有操作后，您必须通过以下方式关闭连接
conn.Close();
Data Source: Identifies the server. Could be local machine, machine domain name, or IP Address.
数据源：标识服务器。可以是本地机器、机器域名或 IP 地址。
Initial Catalog: Database name.
初始目录：数据库名称。
Integrated Security: Set to SSPI to make connection with user's Windows login
集成安全：设置为 SSPI 以连接用户的 Windows 登录
User ID: Name of user configured in SQL Server.
用户 ID：在 SQL Server 中配置的用户名。
Password: Password matching SQL Server User ID.
密码：与 SQL Server 用户 ID 匹配的密码。
回答by Anton Gogolev
To connect to SQL Server Express you need nothing but System.Data, which is a standard .NET assembly. Just use SqlXXXclasses and you'll be done.
要连接到 SQL Server Express，您只需要System.Data.NET 程序集。只需使用SqlXXX类，您就可以完成。
However, writing mundane ADO.NET code is very boring, so it's very common to use an ORM or less heavy-weight result-set mapper such as BLToolkit.
但是，编写普通的 ADO.NET 代码非常无聊，因此使用 ORM 或重量级较低的结果集映射器（例如BLToolkit ）非常常见。
And finally, consider using SQL Server CE. This is a fully ACID-compliant single-file embedded database engine which supports pretty much any feature you can expect form an SQL RDBMS.
最后，考虑使用 SQL Server CE。这是一个完全符合 ACID 的单文件嵌入式数据库引擎，它支持您可以从 SQL RDBMS 中获得的几乎所有功能。
回答by Adrian Grigore
回答by MRG
You can use ADO.Net and System.Data.SqlClient namespace for the same. I will advise you to go with Entities framework (ORM). Please find below links for Entity Framework walk through
您可以使用 ADO.Net 和 System.Data.SqlClient 命名空间。我会建议您使用实体框架 (ORM)。请在下面找到实体框架演练的链接
回答by Mr. Smith
I would recommend using Microsoft's Patterns & Practices Enterprise Library. You would specifically be using the The Data Access Application Block.
我建议使用Microsoft 的 Patterns & Practices Enterprise Library。您将专门使用The Data Access Application Block。
An excerpt from MSDN:
摘自 MSDN：
The Data Access Application Block provides the following benefits:
- It uses the functionality provided by ADO.NET 2.0 and with it, you can use ADO.NET functionality along with the application block's functionality.
- It reduces the need to write boilerplate code to perform standard tasks.
- It helps maintain consistent data access practices, both within an application and across the enterprise.
- It reduces difficulties in changing the database type.
- It relieves developers from learning different programming models for different types of databases.
- It reduces the amount of code that developers must write when they port applications to different types of databases.
数据访问应用程序块具有以下优点：
- 它使用 ADO.NET 2.0 提供的功能，通过它，您可以将 ADO.NET 功能与应用程序块的功能一起使用。
- 它减少了编写样板代码来执行标准任务的需要。
- 它有助于在应用程序内和整个企业内保持一致的数据访问实践。
- 它减少了更改数据库类型的困难。
- 它使开发人员无需为不同类型的数据库学习不同的编程模型。
- 它减少了开发人员在将应用程序移植到不同类型的数据库时必须编写的代码量。
I've used this method for years and it's been very successfull thus far. Good luck!
我已经使用这种方法很多年了，到目前为止它非常成功。祝你好运！
回答by cloud
I wish this will help just try these..
我希望这将有助于尝试这些..
@CLASS
@班级
using System.Data;
using System.Data.SqlClient;
namespace WindowsFormsApplication2
{
class clsDB
{
public SqlDataAdapter mDataAdapter = new SqlDataAdapter();
public DataSet mDataSet = new DataSet();
public SqlConnection mConn;
public clsDB()
{
mConn = new SqlConnection("Data Source=(the data source);Initial Catalog=sample;User ID=(the id);Password=(the password)");
}
public void SQLDB(string strSQL)
{
try
{
mDataAdapter = new SqlDataAdapter(new SqlCommand(strSQL, mConn));
mDataSet = new DataSet();
mDataAdapter.Fill(mDataSet);
}
catch (Exception ex)
{
throw ex;
}
finally
{
mConn.Close();
}
}
public void ClearRes()
{
mDataAdapter.Dispose();
mDataAdapter = null;
mDataSet.Dispose();
if (mConn.State != ConnectionState.Closed)
{
mConn.Close();
}
}
}
}
@LOGIN
@登录
public partial class Login : Form
{
clsDB x = new clsDB();
public Login()
{
InitializeComponent();
}
private void btnSubmit_Click(object sender, EventArgs e)
{
x.SQLDB("select * from tbl_accounts where u_username ='" + txtUser.Text + "' and u_password ='" + txtPass.Text + "'");
if (x.mDataSet.Tables[0].Rows.Count > 0)
{
Main a = new Main();
this.Hide();
a.Show();
}
else
{
MessageBox.Show("wrong username or password");
}
}
@MAIN ACCESS
@主要访问
namespace WindowsFormsApplication2
{
public partial class Main : Form
{
clsDB x = new clsDB();
public Main()
{
InitializeComponent();
}
private void btnAdd_Click(object sender, EventArgs e)
{
x.SQLDB("insert into tbl_info (u_lastname, u_firstname, u_middlename) values ('" + atxtLN.Text + "','" + atxtFN.Text + "','" + atxtFN.Text + "')");
fillgrid();
}
private void Main_Load(object sender, EventArgs e)
{
x.SQLDB(" select * from tbl_info ");
dgv1.DataSource = x.mDataSet.Tables[0];
fillgrid();
}
void fillgrid()
{
x.SQLDB("select * from tbl_info");
dgv1.DataSource = null;
dgv1.DataSource = x.mDataSet.Tables[0];
}
void search()
{
x.SQLDB("SELECT * from tbl_info where u_id like '" + etxtID.Text + "%' order by u_id");
if (x.mDataSet.Tables[0].Rows.Count > 0)
{
x.mDataAdapter.Fill(x.mDataSet, "tbl_info");
dgv1.DataSource = x.mDataSet.Tables["tbl_info"].DefaultView;
etxtLN.Text = dgv1.Rows[dgv1.CurrentRow.Index].Cells["u_lastname"].Value.ToString();
etxtFN.Text = dgv1.Rows[dgv1.CurrentRow.Index].Cells["u_firstname"].Value.ToString();
etxtMN.Text = dgv1.Rows[dgv1.CurrentRow.Index].Cells["u_middlename"].Value.ToString();
}
else if (etxtID.Text == "Type User ID to Edit")
{
etxtLN.Text = "";
etxtFN.Text = "";
etxtMN.Text = "";
}
else
{
etxtLN.Text = "";
etxtFN.Text = "";
etxtMN.Text = "";
}
}
private void etxtID_TextChanged(object sender, EventArgs e)
{
}
private void etxtID_Enter(object sender, EventArgs e)
{
etxtID.Text = "";
etxtID.ForeColor = Color.Black;
}
private void etxtID_Leave(object sender, EventArgs e)
{
if (etxtID.Text == "")
{
etxtID.ForeColor = Color.Gray;
etxtID.Text = "Type User ID to Edit";
x.SQLDB(" select * from tbl_info ");
dgv1.DataSource = x.mDataSet.Tables[0];
fillgrid();
}
}
private void etxtID_KeyUp(object sender, KeyEventArgs e)
{
search();
}
private void btnUpdate_Click(object sender, EventArgs e)
{
x.SQLDB("UPDATE tbl_info set u_lastname ='" + etxtLN.Text + "', u_firstname ='" + etxtFN.Text + "', u_middlename ='" + etxtMN.Text + "' where u_id =" + etxtID.Text);
MessageBox.Show("Operation Successful!");
fillgrid();
}
private void btnDelete_Click(object sender, EventArgs e)
{
x.SQLDB("delete from tbl_info where u_id =" + dtxtID.Text + "");
MessageBox.Show("Operation Successful!");
fillgrid();
}
}
}
