ASP.NET中如何调用存储过程_Asp.Net开发技巧

用ASP.NET与SQL SERVER可是缘份最好了，稍大的程序一般第一先考虑的是SQL SERVER，只是一些很考虑经济的才使用ACCESS等了。用SQL SERVER，为了使数据库的效率更好，一般都会才取存储过程，因存储过程执行速度快，并且可以实现一些高级的查询等功能。比如传入一些数据参数，但执行的SQL过程可能不同等。

　　下面就来个例子，建立一新的角色，要求角色的名字不能重复，以下是一存储过程。 　　

CREATE PROCEDURE sp_AccountRole_Create

@CategoryID int,
@RoleName nvarchar(10),
@Description nvarchar(50),
@RoleID int output
AS
    DECLARE @Count int

    -- 查找是否有相同名称的记录
    SELECT @Count = Count(RoleID) FROM Account_Role WHERE
        RoleName = @RoleName

    IF @Count = 0

        INSERT INTO Account_Role 
        (CategoryID, RoleName, Description) valueS
        (@CategoryID, @RoleName, @Description)

        SET @RoleID = @@IDENTITY

        RETURN 1
GO

　　
　　执行存储过程的C#过程：

　　

SqlConnection DbConnection = new SqlConnection(mConnectionString);
SqlCommand command = new SqlCommand( "sp_AccountRole_Create", DbConnection );
DbConnection.Open(connectString);
// 废置SqlCommand的属性为存储过程
command.CommandType = CommandType.StoredProcedure;

command.Parameters.Add("@CategoryID", SqlDBType.Int, 4);
command.Parameters.Add("@RoleName", SqlDBType.NVarChar, 10);
command.Parameters.Add("@Description", SqlDBType.NVarChar, 50);
command.Parameters.Add("@RoleID", SqlDBType.Int, 4);
// 返回值
command.Parameters.Add("Returnvalue",
            SqlDBType.Int,
            4,        // Size
            ParameterDirection.Returnvalue,
            false,        // is nullable 
            0,        // byte precision
            0,        // byte scale
            string.Empty,
            DataRowVersion.Default,
            null );

command.parameters["@CategoryID"].value = permission.CategoryID;
command.parameters["@RoleName"].value = permission.PermissionName;
command.parameters["@Description"].value = permission.Description;
// 可以返回新的ID值
command.parameters["@RoleID"].Direction = ParameterDirection.Output;

int rowsAffected = command.ExecuteNonQuery();
int result = command.parameters["Returnvalue"].value;
int newID = command.parameters["@RoleID"].value;

　　功能挺强的吧，可以得到三个值，分别是行影响值，存储过程返回值，新的ID值。

motorcycle decalsholiday mens fashionwinter leather motorcycle jacketshelborne beach hotel7 night western caribbean cruisepurple suede bagsChanel-20130-BLcaribbean travel guidenorwegian cruise line employmentplus size spring jacketbig canvas paintingschildren travel accessoriesdisney cruise western caribbeanthomas tank engine concertleather pantssimplicity sewing machine s11095870-8habitat housesdisney cruise stateroomswind star cruise linewhole sale suppliescosta cruises 2010leather braiding suppliespolish jokesblack clutch headvin diesel jacketsummer spring pattaya beach pattayanutsert installation toolchristian dior heelsM93129dior spring 2009cheap sunglasses for womenbest budget luggagediscount leather storesmarc jacobs knockoff handbagsgrandbury waterfront vacation rentalmy new handbagmarrs leather shopstoo long a winterhollland america cruisescheap yellow pursesportraits famous artistsenvironmentally friendly luggageleather sofa cleancoleman high tech lightweight 21 carry on uprightbath soapsuede backpackslast minute vacation deals europeoil painting brushrosemary beach pet friendlydiscount hermes bagsluxor night clubrenaissance leather goodsprada handbags 2008tool box kitv belt pulley manufacturerswomen snow jacketiwc da vinciantique steamer trunks valuepretty spring dressjessica simpson shoesll bean dogscosta fortuna itineraryleather vinyl repair kitruby pokemondolce & gabbanawinter leather glovesbuy ed hardy handbagsspring summer fall and springsummer jewelry trendsspring tours egyptcruises vacation packagesrubber stamp padswatercolor art historycrystal cruises caribbeandsquared spring summeramerican tourister 3 piece luggagenorthwest carry on luggageleathers oilsnap latchescruise ship illness 2010skyway carry luggagecaribbean hotel jobsbest cruise companycheap beach real estateremax rentalsromantic vacation packagesclean leather couchvacation homes salework chapssandestin condo rentalscoach furniturewedding cake picturesroberto cavalli jeansghurka leather goodsvictorinox casual traveler wheeled luggageholland american caribbean cruiseswholesale handbags lotssovereign cruise clubkids cruise freejuicy couture ukour vacation with cruise america reviewsjerry art supplies njocean sports folly beachstretched canvas printhiking trailscabrillo inn at the beach reviewscheap handbags from chinatv show reviewsdeals winter jacketsleather laptop messenger bag shopmeditation shawlsfrank mullerchristmas river cruiseretro canvas artmodel tall ship plans2003 mini cooper partsbrand name bags for cheapversacewallets louis vuittonarchitectural boat tourpainting easelswiss gear ibex laptop backpackantler trolley bagsdesigner shoulder handbagtunnocks tea cake baghartmann luggage websitediesel shirtroyal caribbean international legend seasleather furniture cleaning tipslong leather coatscaribbean cruise companiesdecalworld cruise mapfashion typessmall white paper bagsluxury beach resorts usasport shower curtainszippered leather walletcayucos shoreline innlightweight north face jacketevening clutch shopcontemporary portrait artistgucci classic beige monogram backpack khakihaider ackermannsew shoulder bagcruises discountsquote leather n canvasdiecast aircraft modelsthe fendi spy bag

Replica lv Hangbags

Replica Hangbags

Hangbags

replica handbags

replica handbags

replica handbags

replica handbags

handbags

replica handbags replica handbags replica handbags replica handbags replica handbags