Shulou(Shulou.com)06/02 Report--

This article will explain in detail how to use ADO.NET parameters. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

In data-driven applications, stored procedures have many advantages. By using stored procedures, database operations can be encapsulated in a single command, optimized for * performance and enhanced with additional security. Although you can call the corresponding stored procedure by passing the name of the stored procedure followed by the argument argument in the SQL statement, using the Parameters collection of ADO.NET DbCommand objects allows you to define stored procedure parameters more clearly and access output parameters and return values.

Using ADO.NET parameterized statements to be executed on the server using sp_executesql, sp_executesql allows query plans to be reused. Local cursors or variables in sp_executesql batch commands are not visible to batch commands that invoke sp_executesql. Changes in the database context persist only to the end of the sp_executesql statement.

When you use parameters on SqlCommand to execute a SQL Server stored procedure, the name of the parameter added to the Parameters collection must match the name of the parameter tag in the stored procedure. SQL Server's .NET Framework data access interface does not support question mark (?) Placeholders passed to SQL statements or stored procedures using the ADO.NET parameter. It treats the parameters in the stored procedure as named parameters and searches for matching parameter markers. For example, the CustOrderHist stored procedure is defined by using a parameter named @ CustomerID. When your code executes the stored procedure, it must also use a parameter named @ CustomerID.

This example demonstrates how to call the SQL Server stored procedure in the Northwind sample database. The name of the stored procedure is dbo.SalesByCategory, and it has an input parameter named @ CategoryName whose data type is nvarchar (15). This code creates a new SqlConnection within the using code block to release the connection at the end of the procedure. SqlCommand and SqlParameter objects are created and their properties are set. SqlDataReader executes the SqlCommand and returns the result set from the stored procedure to display the relevant output in the console window.

Instead of creating SqlCommand and SqlParameter objects and then setting properties in individual statements, you can choose to use any overloaded constructor to set multiple properties in a statement.

Visual Basic

Shared Sub GetSalesByCategory (ByVal connectionString As String, _ ByVal categoryName As String) Using connection As New SqlConnection (connectionString) 'Create the command and set its properties. Dim command As SqlCommand = New SqlCommand () command.Connection = connection command.CommandText = "SalesByCategory" command.CommandType = CommandType.StoredProcedure 'Add the input parameter and set its properties. Dim parameter As New SqlParameter () parameter.ParameterName = "@ CategoryName" parameter.SqlDbType = SqlDbType.NVarChar parameter.Direction = ParameterDirection.Input parameter.Value = categoryName 'Add the parameter to the Parameters collection. Command.Parameters.Add (parameter) 'Open the connection and execute the reader. Connection.Open () Dim reader As SqlDataReader = command.ExecuteReader () If reader.HasRows Then Do While reader.Read () Console.WriteLine ("{0}: {1command.ExecuteReader C}", _ reader (0), reader (1)) Loop Else Console.WriteLine ("No rows returned.") End If End Using End Sub

C#

Static void GetSalesByCategory (string connectionString, string categoryName) {using (SqlConnection connection = new SqlConnection (connectionString)) {/ / Create the command and set its properties. SqlCommand command = new SqlCommand (); command.Connection = connection; command.CommandText = "SalesByCategory"; command.CommandType = CommandType.StoredProcedure; / / Add the input parameter and set its properties. SqlParameter parameter = new SqlParameter (); parameter.ParameterName = "@ CategoryName"; parameter.SqlDbType = SqlDbType.NVarChar; parameter.Direction = ParameterDirection.Input; parameter.Value = categoryName; / / Add the parameter to the Parameters collection. Command.Parameters.Add (parameter); / / Open the connection and execute the reader. Connection.Open (); SqlDataReader reader = command.ExecuteReader (); if (reader.HasRows) {while (reader.Read ()) {Console.WriteLine ("{0}: {1command.ExecuteReader C}", reader [0], reader [1]);}} else {Console.WriteLine ("No rows found.");} reader.Close () }} this is the end of the article on "how to use ADO.NET parameters". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.