Wednesday, February 11, 2015
Today we will be calling a stored procedure in SQL Server that we've created earlier in this blog call addProduct. The stored procedure takes the following input parameters.
1. First you need the namespaces
2. Then get the Northwind connection string value from the Web.config file
3. Now call the stored procedure and output the result from the SqlDataReader
Blogs In the T-SQL Series:
1. First you need the namespaces
using System.Web.Configuration; using System.Data.SqlClient; using System.Data;
2. Then get the Northwind connection string value from the Web.config file
string connectString = WebConfigurationManager.ConnectionStrings["NorthwindConnectionString"]. ConnectionString;
3. Now call the stored procedure and output the result from the SqlDataReader
using (SqlConnection conn = new SqlConnection(connectString)) { conn.Open(); SqlCommand cmd = new SqlCommand(); cmd.CommandText = "addProduct"; cmd.CommandType = CommandType.StoredProcedure; cmd.Connection = conn; SqlParameter productName = new SqlParameter("@ProductName", "Teh"); productName.SqlDbType = SqlDbType.NVarChar; productName.Direction = ParameterDirection.Input; cmd.Parameters.Add(productName); SqlParameter supplerID = new SqlParameter("@SupplierID", 1); supplerID.SqlDbType = SqlDbType.Int; supplerID.Direction = ParameterDirection.Input; cmd.Parameters.Add(supplerID); SqlParameter categoryID = new SqlParameter("@CategoryID", 1); categoryID.SqlDbType = SqlDbType.Int; categoryID.Direction = ParameterDirection.Input; cmd.Parameters.Add(categoryID); SqlParameter quantityPerUnit = new SqlParameter("@QuantityPerUnit", "20 boxes of 12 oz."); quantityPerUnit.SqlDbType = SqlDbType.NVarChar; quantityPerUnit.Direction = ParameterDirection.Input; cmd.Parameters.Add(quantityPerUnit); SqlParameter unitPrice = new SqlParameter("@UnitPrice", 12.99); unitPrice.SqlDbType = SqlDbType.Money; unitPrice.Direction = ParameterDirection.Input; cmd.Parameters.Add(unitPrice); SqlParameter unitsInStock = new SqlParameter("@UnitsInStock", 6); unitsInStock.SqlDbType = SqlDbType.SmallInt; unitsInStock.Direction = ParameterDirection.Input; cmd.Parameters.Add(unitsInStock); SqlParameter reorderLevel = new SqlParameter("@ReorderLevel", 2); reorderLevel.SqlDbType = SqlDbType.SmallInt; reorderLevel.Direction = ParameterDirection.Input; cmd.Parameters.Add(reorderLevel); SqlParameter discontinued = new SqlParameter("@Discontinued", false); discontinued.SqlDbType = SqlDbType.Bit; discontinued.Direction = ParameterDirection.Input; cmd.Parameters.Add(discontinued); int rowsAffected = cmd.ExecuteNonQuery(); Response.Write(rowsAffected); }In the above code you add the parameters required by the addProduct stored procedure. You specify the name, type, and value. Then add it to command object's parameters list. Then you execute the ExecuteNonQuery() method because you are not get a resultset back or a scalar value. The ExecuteNonQuery() method returns an int value, usually the rows that were affected value.
Blogs In the T-SQL Series:
- T-SQL: Stored Procedure (INSERT), INSERT A New Product In Northwind Part 1
- ASP.NET : Stored Procedures (INSERT), Insert a new Northwind Product Part 2
- T-SQL: Stored Procedures (DELETE), DELETE An Existing Northwind Product Part 3
- T-SQL: Stored Procedures (UPDATE), UPDATE An Existing Product In Northwind Part 4
- T-SQL: Stored Procedures (SELECT), SELECT Products and The Supplier Part 5
- ASP.NET: Calling Stored Procedure With A Parameter With SqlParameter Part 6
- ASP.NET: Get a Single Value From a Stored Procedure Part 7
- SQL Server: Granting Access Permissions to Stored Procedures For IIS Part 8
Subscribe to:
Post Comments (Atom)
Search This Blog
Tags
Web Development
Linux
Javascript
DATA
CentOS
ASPNET
SQL Server
Cloud Computing
ASP.NET Core
ASP.NET MVC
SQL
Virtualization
AWS
Database
ADO.NET
AngularJS
C#
CSS
EC2
Iaas
System Administrator
Azure
Computer Programming
JQuery
Coding
ASP.NET MVC 5
Entity Framework Core
Web Design
Infrastructure
Networking
Visual Studio
Errors
T-SQL
Ubuntu
Stored Procedures
ACME Bank
Bootstrap
Computer Networking
Entity Framework
Load Balancer
MongoDB
NoSQL
Node.js
Oracle
VirtualBox
Container
Docker
Fedora
Java
Source Control
git
ExpressJS
MySQL
NuGet
Blogger
Blogging
Bower.js
Data Science
JSON
JavaEE
Web Api
DBMS
DevOps
HTML5
MVC
SPA
Storage
github
AJAX
Big Data
Design Pattern
Eclipse IDE
Elastic IP
GIMP
Graphics Design
Heroku
Linux Mint
Postman
R
SSL
Security
Visual Studio Code
ASP.NET MVC 4
CLI
Linux Commands
Powershell
Python
Server
Software Development
Subnets
Telerik
VPC
Windows Server 2016
angular-seed
font-awesome
log4net
servlets
tomcat
AWS CloudWatch
Active Directory
Angular
Blockchain
Collections
Compatibility
Cryptocurrency
DIgital Life
DNS
Downloads
Google Blogger
Google Chrome
Google Fonts
Hadoop
IAM
KnockoutJS
LINQ
Linux Performance
Logging
Mobile-First
Open Source
Prototype
R Programming
Responsive
Route 53
S3
SELinux
Software
Unix
View
Web Forms
WildFly
XML
cshtml
githu
Mua vé tại Aivivu, tham khảo
ReplyDeletevé máy bay đi Mỹ tháng nào rẻ nhất
mua vé máy bay từ mỹ về vn
giá vé máy bay từ canada về Việt Nam
lịch bay từ nhật về việt nam hôm nay
vé máy bay vietjet từ hàn quốc về việt nam
Vé máy bay từ Đài Loan về VN
chuyến bay chuyên gia về việt nam