Visual Studio 数据访问教程与概念

需积分: 9 134 浏览量更新于2024-07-18 收藏 2.97MB PDF 举报

"Access data in Visual Studio.pdf" 在Visual Studio中，开发者可以创建连接到各种数据库产品或服务、任何格式的数据的应用程序，无论这些数据位于本地机器、局域网还是公有、私有或混合云中。这个PDF文档涵盖了从基础到进阶的数据访问教程和概念，特别是针对.NET开发的工具和方法。 ### 概述 Visual Studio提供了广泛的数据工具，使得开发者能够轻松地在应用中接入和管理数据。无论是简单的数据应用还是复杂的n层架构数据应用，都有相应的支持。 ### 教程 1. **创建使用ADO.NET的简单数据应用**：通过ADO.NET，开发者可以直接与数据库进行交互，实现基本的CRUD（创建、读取、更新、删除）操作。 2. **创建使用WPF和Entity Framework的简单数据应用**：结合Windows Presentation Foundation（WPF）和Entity Framework，可以构建更现代的UI并利用ORM（对象关系映射）简化数据操作。 3. **从C++应用连接数据**：Visual Studio也支持C++开发者连接数据库，提供数据访问接口。 4. **使用O/R Designer创建LINQ to SQL类**：这是另一种ORM技术，通过设计工具创建与数据库表映射的类，便于使用LINQ查询数据。 ### 概念 1. **兼容的数据库系统**：Visual Studio支持多种数据库系统，如SQL Server、Oracle、MySQL、SQLite等。 2. **Visual Studio的数据工具**：包括用于.NET的工具，如数据集、数据源控件、Entity Framework工具等，帮助开发者高效地处理数据。 3. **Entity Framework工具**：提供模型第一、代码第一和数据库第一等多种开发模式，简化数据库操作。 4. **数据集工具**：数据集是内存中的数据表示，分为已类型化和未类型化的数据集，用于处理复杂的数据操作。 5. **TableAdapter概述**：TableAdapter是数据集的一部分，负责从数据库获取和更新数据。 6. **n层数据应用程序**：n层架构允许将数据访问、业务逻辑和用户界面分离开来，提高可维护性和复用性。 ### 如何操作指南 1. **创建数据库**：教程指导如何在Visual Studio中创建新的数据库项目。 2. **添加连接**：连接到本地或远程数据库，编辑和保存连接字符串。 3. **连接到Access数据库**：展示如何与Access数据库建立连接。 4. **添加.NET数据源**：将数据源添加到项目，以便在UI中展示或操作数据。 5. **使用LINQ to SQL工具**：利用语言集成查询（LINQ）进行数据操作。 6. **改变DataContext方法的返回类型**：自定义返回值以适应特定需求。 7. **将DataContext方法映射到存储过程**：使数据访问更灵活，允许使用预编译的SQL语句。 8. **配置继承使用O/R Designer**：在ORM模型中设置类之间的继承关系。 9. **创建映射到表和视图的LINQ to SQL类**：创建与数据库结构对应的类。 10. **扩展O/R Designer生成的代码**：根据项目需求对自动生成的代码进行扩展和定制。 11. **在LINQ to SQL类之间创建关联**：定义类之间的关系，如一对一、一对多或多对多。 12. **向实体类添加验证**：增强数据完整性，防止非法数据输入。 13. **自定义实体类的插入、更新和删除行为**：控制数据修改操作的细节。 14. **分配存储过程执行更新、插入和删除**：使用存储过程来处理数据的增删改操作，提升性能和安全性。通过以上内容，开发者可以全面了解如何在Visual Studio环境中构建和管理与数据交互的应用程序，无论是简单的数据绑定还是复杂的业务逻辑处理，都能得到充分的支持。

' Storage for ID values returned from the database.

Private parsedCustomerID As Integer

Private orderID As Integer

''' <summary>

''' Verifies that the customer name text box is not empty.

''' </summary>

Private ReadOnly Property IsCustomerNameValid As Boolean

Get

If txtCustomerName.Text = "" Then

MessageBox.Show("Please enter a name.")

Return False

Else

Return True

End If

End Get

End Property

''' <summary>

''' Verifies the order data is valid.

''' </summary>

Private Function IsOrderDataValid() As Boolean

' Verify that CustomerID is present.

If txtCustomerID.Text = "" Then

MessageBox.Show("Please create a customer account before placing order.")

Return False

' Verify that order amount isn't 0.

ElseIf (numOrderAmount.Value < 1) Then

MessageBox.Show("Please specify an order amount.")

Return False

Else

' Order can be submitted.

Return True

End If

End Function

''' <summary>

''' Clears values from controls.

''' </summary>

Private Sub ClearForm()

txtCustomerName.Clear()

txtCustomerID.Clear()

dtpOrderDate.Value = DateTime.Now

numOrderAmount.Value = 0

Me.parsedCustomerID = 0

End Sub

/// <summary>

/// Creates a new customer by calling the Sales.uspNewCustomer stored procedure.

/// </summary>

private void btnCreateAccount_Click(object sender, EventArgs e)

{

if (IsCustomerNameValid())

{

// Create the connection.

using (SqlConnection connection = new SqlConnection(Properties.Settings.Default.connString))

{

// Create a SqlCommand, and identify it as a stored procedure.

using (SqlCommand sqlCommand = new SqlCommand("Sales.uspNewCustomer", connection))

{

sqlCommand.CommandType = CommandType.StoredProcedure;

3. Complete the method bodies for the four button click event handlers as shown in the following code.

// Add input parameter for the stored procedure and specify what to use as its value.

sqlCommand.Parameters.Add(new SqlParameter("@CustomerName", SqlDbType.NVarChar, 40));

sqlCommand.Parameters["@CustomerName"].Value = txtCustomerName.Text;

// Add the output parameter.

sqlCommand.Parameters.Add(new SqlParameter("@CustomerID", SqlDbType.Int));

sqlCommand.Parameters["@CustomerID"].Direction = ParameterDirection.Output;

try

{

connection.Open();

// Run the stored procedure.

sqlCommand.ExecuteNonQuery();

// Customer ID is an IDENTITY value from the database.

this.parsedCustomerID = (int)sqlCommand.Parameters["@CustomerID"].Value;

// Put the Customer ID value into the read-only text box.

this.txtCustomerID.Text = Convert.ToString(parsedCustomerID);

}

catch

{

MessageBox.Show("Customer ID was not returned. Account could not be created.");

}

finally

{

connection.Close();

}

/// <summary>

/// Calls the Sales.uspPlaceNewOrder stored procedure to place an order.

/// </summary>

private void btnPlaceOrder_Click(object sender, EventArgs e)

{

// Ensure the required input is present.

if (IsOrderDataValid())

{

// Create the connection.

using (SqlConnection connection = new SqlConnection(Properties.Settings.Default.connString))

{

// Create SqlCommand and identify it as a stored procedure.

using (SqlCommand sqlCommand = new SqlCommand("Sales.uspPlaceNewOrder", connection))

{

sqlCommand.CommandType = CommandType.StoredProcedure;

// Add the @CustomerID input parameter, which was obtained from uspNewCustomer.

sqlCommand.Parameters.Add(new SqlParameter("@CustomerID", SqlDbType.Int));

sqlCommand.Parameters["@CustomerID"].Value = this.parsedCustomerID;

// Add the @OrderDate input parameter.

sqlCommand.Parameters.Add(new SqlParameter("@OrderDate", SqlDbType.DateTime, 8));

sqlCommand.Parameters["@OrderDate"].Value = dtpOrderDate.Value;

// Add the @Amount order amount input parameter.

sqlCommand.Parameters.Add(new SqlParameter("@Amount", SqlDbType.Int));

sqlCommand.Parameters["@Amount"].Value = numOrderAmount.Value;

// Add the @Status order status input parameter.

// For a new order, the status is always O (open).

sqlCommand.Parameters.Add(new SqlParameter("@Status", SqlDbType.Char, 1));

sqlCommand.Parameters["@Status"].Value = "O";

// Add the return value for the stored procedure, which is the order ID.

sqlCommand.Parameters.Add(new SqlParameter("@RC", SqlDbType.Int));

sqlCommand.Parameters["@RC"].Direction = ParameterDirection.ReturnValue;

try

{

//Open connection.

connection.Open();

// Run the stored procedure.

sqlCommand.ExecuteNonQuery();

// Display the order number.

this.orderID = (int)sqlCommand.Parameters["@RC"].Value;

MessageBox.Show("Order number " + this.orderID + " has been submitted.");

}

catch

{

MessageBox.Show("Order could not be placed.");

}

finally

{

connection.Close();

}

/// <summary>

/// Clears the form data so another new account can be created.

/// </summary>

private void btnAddAnotherAccount_Click(object sender, EventArgs e)

{

this.ClearForm();

}

/// <summary>

/// Closes the form/dialog box.

/// </summary>

private void btnAddFinish_Click(object sender, EventArgs e)

{

this.Close();

}

''' <summary>

''' Creates a new account by executing the Sales.uspNewCustomer

''' stored procedure on the database.

''' </summary>

Private Sub btnCreateAccount_Click(sender As Object, e As EventArgs) Handles btnCreateAccount.Click

' Ensure a customer name has been entered.

If IsCustomerNameValid Then

' Create the SqlConnection object.

Using connection As New SqlConnection(My.Settings.connString)

' Create a SqlCommand, and identify the command type as a stored procedure.

Using sqlCommand As New SqlCommand("Sales.uspNewCustomer", connection)

sqlCommand.CommandType = CommandType.StoredProcedure

' Add the customer name input parameter for the stored procedure.

sqlCommand.Parameters.Add(New SqlParameter("@CustomerName", SqlDbType.NVarChar, 40))

sqlCommand.Parameters("@CustomerName").Value = txtCustomerName.Text

' Add the customer ID output parameter.

sqlCommand.Parameters.Add(New SqlParameter("@CustomerID", SqlDbType.Int))

sqlCommand.Parameters("@CustomerID").Direction = ParameterDirection.Output

Try

' Open the connection.

connection.Open()

' Run the stored procedure.

sqlCommand.ExecuteNonQuery()

' Convert the Customer ID value to an Integer.

Me.parsedCustomerID = CInt(sqlCommand.Parameters("@CustomerID").Value)

' Insert the customer ID into the corresponding text box.

Me.txtCustomerID.Text = Convert.ToString(parsedCustomerID)

Catch

MessageBox.Show("Customer ID was not returned. Account could not be created.")

Finally

' Close the connection.

connection.Close()

End Try

End Using

End If

End Sub

''' <summary>

''' Places the order by executing the Sales.uspPlaceNewOrder

''' stored procedure on the database.

''' </summary>

Private Sub btnPlaceOrder_Click(sender As Object, e As EventArgs) Handles btnPlaceOrder.Click

If IsOrderDataValid() Then

' Create the connection.

Using connection As New SqlConnection(My.Settings.connString)

' Create SqlCommand and identify it as a stored procedure.

Using sqlCommand As New SqlCommand("Sales.uspPlaceNewOrder", connection)

sqlCommand.CommandType = CommandType.StoredProcedure

' Add the @CustomerID parameter, which was an output parameter from uspNewCustomer.

sqlCommand.Parameters.Add(New SqlParameter("@CustomerID", SqlDbType.Int))

sqlCommand.Parameters("@CustomerID").Value = Me.parsedCustomerID

' Add the @OrderDate parameter.

sqlCommand.Parameters.Add(New SqlParameter("@OrderDate", SqlDbType.DateTime, 8))

sqlCommand.Parameters("@OrderDate").Value = dtpOrderDate.Value

' Add the @Amount parameter.

sqlCommand.Parameters.Add(New SqlParameter("@Amount", SqlDbType.Int))

sqlCommand.Parameters("@Amount").Value = numOrderAmount.Value

' Add the @Status parameter. For a new order, the status is always O (open).

sqlCommand.Parameters.Add(New SqlParameter("@Status", SqlDbType.[Char], 1))

sqlCommand.Parameters("@Status").Value = "O"

' Add a return value parameter for the stored procedure, which is the orderID.

sqlCommand.Parameters.Add(New SqlParameter("@RC", SqlDbType.Int))

sqlCommand.Parameters("@RC").Direction = ParameterDirection.ReturnValue

Try

' Open connection.

connection.Open()

' Run the stored procedure.

sqlCommand.ExecuteNonQuery()

' Display the order number.

Me.orderID = CInt(sqlCommand.Parameters("@RC").Value)

MessageBox.Show("Order number " & (Me.orderID).ToString & " has been submitted.")

Catch

FillOrCancel formFillOrCancel form

Create auto

generated event handlersCreate auto

generated event handlers

Add code for the FillOrCancel form logicAdd code for the FillOrCancel form logic

Catch

' A simple catch.

MessageBox.Show("Order could not be placed.")

Finally

' Always close a connection after you finish using it,

' so that it can be released to the connection pool.

connection.Close()

End Try

End Using

End If

End Sub

''' <summary>

''' Resets the form for another new account.

''' </summary>

Private Sub btnAddAnotherAccount_Click(sender As Object, e As EventArgs) Handles

btnAddAnotherAccount.Click

Me.ClearForm()

End Sub

''' <summary>

''' Closes the NewCustomer form and returns focus to the Navigation form.

''' </summary>

Private Sub btnAddFinish_Click(sender As Object, e As EventArgs) Handles btnAddFinish.Click

Me.Close()

End Sub

The FillOrCancel form runs a query to return an order when you enter an order ID and then click the Find Order

button. The returned row appears in a read

only data grid. You can mark the order as canceled

(

)

if you select the

Cancel Order button, or you can mark the order as filled

(

)

if you select the Fill Order button. If you select the

Find Order button again, the updated row appears.

Create empty Click event handlers for the four buttons on the FillOrCancel form by double

clicking the buttons.

Double

clicking the buttons also adds auto

generated code in the Designer code file that enables a button click to

raise an event.

To complete the FillOrCancel form logic, follow these steps.

using System.Data.SqlClient;

using System.Text.RegularExpressions;

Imports System.Data.SqlClient

Imports System.Text.RegularExpressions

1. Bring the following two namespaces into scope so that you don't have to fully qualify the names of their

members.

2. Add a variable and helper method to the class as shown in the following code.

剩余341页未读，继续阅读

cadn_wang

粉丝: 0
资源: 6

Visual Studio 数据访问教程与概念

VisualStudio 2019 c++ SQL SERVER

sqlsugar5.0 怎么连接access需要多少版本的System.Data.OleDb包

C#+visual studio+access的登录和注册功能

Visual Studio 2022创建二进制流文件

错误 1 未定义类型“System.Data.OleDbConnection”。

VS如何连接 Access 数据库文件

visual studio 1.实现三维图形的坐标系之间的变换（世界坐标、物坐标、屏幕坐标）以及三维图形几何变换。修改示例图形为给定的模型，将立方体顶点的三维坐标（x，y，z）及变量改为三棱锥顶点的三维坐标及变量相关代码

vs中通过输入存进access数据库

怎么把access中的数据连接到aspx的页面并显示

net core三层架构代码生成器

最新资源