从本地SQL Server数据库检索数据的最直接的方法是什么? [英] What is the most straightforward way to retrieve data from a local SQL Server database?
问题描述
我并不需要/想任何安全措施(用户名/密码等) - 我只是想创建从AdventureWorks精简版数据库(AdventureWorksLT2012_Data.mdf)检索数据的一个简单的例子,在存储数据一个通用列表。
我有以下代码来查询MS Access数据库。除了连接字符串和查询语句,它基本上是一样的吗?
public SQLServerPOCRepository()
{$ b使用$ b(VAR康恩=新的OleDbConnection(
@供应商= Microsoft.ACE.OLEDB.12.0;用户ID =用户;密码=合格;数据源= C:\SWin\DATA\SDAT42。 MDB;喷射OLEDB:系统数据库= C:\SWin\Data\wgeg.mdw))
{
使用(VAR CMD = conn.CreateCommand())
$ { b $ b cmd.CommandText =SELECT td_duckbill_accounts.dept_no,IIF(ISNULL(t_accounts.name),'未提供姓名,t_accounts.name)AS的名字从t_accounts INNER JOIN td_duckbill_accounts ON t_accounts.account_no = td_duckbill_accounts.account_no ORDER BY td_duckbill_accounts .dept_no;
cmd.CommandType = CommandType.Text;
conn.Open();
int i = 1;使用(OleDbDataReader oleDbD8aReader = cmd.ExecuteReader())
{
,而(oleDbD8aReader = NULL&放大器;!&安培; oleDbD8aReader.Read())
{
INT duckbillNum = oleDbD8aReader.GetInt16(0);
string duckbillName = oleDbD8aReader.GetString(1);
添加(新Platypus {Id = i,dbillNum = duckbillNum,Name = duckbillName});
i ++;
}
}
}
}
}
$ b b
Add()
方法使用模型 Platypus
类的实例填充通用列表。 p>
您需要更新您的连接字符串使用 System.Data.SqlClient的
提供程序,以及集成安全
。
< add name =AdvWorksLT_ConnectionString
connectionString =AttachDBFilename = C:\MyApplication\ AdventureWorks。 MDF; Integrated Security = True; User Instance = true
providerName =System.Data.SqlClient/>
此外,您还需要使用 SqlConnection
, SqlCommand
和 SqlDataReader
,而不是 OleDbConnection
c $ c> OleDbCommand 和 OleDbDataReader
。
I don't need/want any security measures (username/password, etc.) - I just want to create a simple example of retrieving data from the AdventureWorks "lite" database (AdventureWorksLT2012_Data.mdf), storing that data in a generic list.
I have the following code to query an MS Access database. Would it be basically the same except for the connection string and query statement?
public SQLServerPOCRepository()
{
using (var conn = new OleDbConnection(
@"Provider=Microsoft.ACE.OLEDB.12.0;User ID=User;Password=Pass;Data Source=C:\SWin\DATA\SDAT42.MDB;Jet OLEDB:System database=C:\SWin\Data\wgeg.mdw"))
{
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT td_duckbill_accounts.dept_no, IIF(ISNULL(t_accounts.name),'No Name provided',t_accounts.name) AS name FROM t_accounts INNER JOIN td_duckbill_accounts ON t_accounts.account_no = td_duckbill_accounts.account_no ORDER BY td_duckbill_accounts.dept_no";
cmd.CommandType = CommandType.Text;
conn.Open();
int i = 1;
using (OleDbDataReader oleDbD8aReader = cmd.ExecuteReader())
{
while (oleDbD8aReader != null && oleDbD8aReader.Read())
{
int duckbillNum = oleDbD8aReader.GetInt16(0);
string duckbillName = oleDbD8aReader.GetString(1);
Add(new Platypus { Id = i, dbillNum = duckbillNum, Name = duckbillName });
i++;
}
}
}
}
}
The Add()
method populates a generic list with an instance of the model Platypus
class.
You need to update your connection string to use the System.Data.SqlClient
provider, along with Integrated Security
.
<add name="AdvWorksLT_ConnectionString"
connectionString="AttachDBFilename=C:\MyApplication\AdventureWorks.MDF;Integrated Security=True;User Instance=true"
providerName="System.Data.SqlClient"/>
Also, you need to use a SqlConnection
, SqlCommand
and SqlDataReader
instead of the OleDbConnection
, OleDbCommand
, and OleDbDataReader
.
这篇关于从本地SQL Server数据库检索数据的最直接的方法是什么?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!