本文實例講述了asp.net實現調用存儲過程并帶返回值的方法。分享給大家供大家參考，具體如下：
/// <summary>/// DataBase 的摘要說明/// </summary>public class DataBase{ /// <summary> ///DataBase 的摘要說明 /// </summary> protected static SqlConnection BaseSqlConnection = new SqlConnection();//連接對象 protected SqlCommand BaseSqlCommand = new SqlCommand(); //命令對象 public DataBase() { // // TODO: 在此處添加構造函數邏輯 // } protected void OpenConnection() { if (BaseSqlConnection.State == ConnectionState.Closed) //連接是否關閉 try { BaseSqlConnection.ConnectionString = ConfigurationManager.ConnectionStrings["productsunion"].ToString(); BaseSqlCommand.Connection = BaseSqlConnection; BaseSqlConnection.Open(); } catch (Exception ex) { throw new Exception(ex.Message); } } public void CloseConnection() { if (BaseSqlConnection.State == ConnectionState.Open) { BaseSqlConnection.Close(); BaseSqlConnection.Dispose(); BaseSqlCommand.Dispose(); } } public bool Proc_Return_Int(string proc_name, params SqlParameter[] cmdParms) { try { OpenConnection(); if (cmdParms != null) { foreach (SqlParameter parameter in cmdParms) { if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) && (parameter.Value == null)) { parameter.Value = DBNull.Value; } BaseSqlCommand.Parameters.Add(parameter); } BaseSqlCommand.CommandType = CommandType.StoredProcedure; BaseSqlCommand.CommandText = proc_name; BaseSqlCommand.ExecuteNonQuery(); if (BaseSqlCommand.Parameters["Return"].Value.ToString()== "0") { return true; } else { return false; } } else { return false; } } catch { return false; } finally { BaseSqlCommand.Parameters.Clear(); CloseConnection(); } }} 加入了一個組合類
public class SqlModel:ISqlModel{ #region ISqlModel 成員 public bool Proc_Return_Int(string proc_name, string[,] sArray) { try { if (sArray.GetLength(0) >= 1) { DataBase db = new DataBase(); SqlParameter[] sqlpar = new SqlParameter[sArray.GetLength(0)+1];//加入返回值 for (int i = 0; i < sArray.GetLength(0); i++) { sqlpar[i] = new SqlParameter(sArray[i,0], sArray[i,1]); } sqlpar[sArray.GetLength(0)] = new SqlParameter("Return", SqlDbType.Int); sqlpar[sArray.GetLength(0)].Direction = ParameterDirection.ReturnValue; if (db.Proc_Return_Int(proc_name, sqlpar)) { return true; } else { return false; } } else { return false; } } catch { return false; } } #endregion} 前臺調用
string[,] sArray = new string[3,2];sArray[0,0]="@parent_id";sArray[1,0]="@cn_name";sArray[2,0]="@en_name";sArray[0,1]="5";sArray[1,1]="aaaab";sArray[2,1]="cccccc";Factory.SqlModel sm = new Factory.SqlModel();sm.Proc_Return_Int("Product_Category_Insert", sArray); 存儲過程內容
ALTER PROCEDURE [dbo].[Product_Category_Insert] @parent_id int, @cn_Name nvarchar(50), @en_Name nvarchar(50)ASBEGIN SET NOCOUNT ON; DECLARE @ERR int SET @ERR=0 BEGIN TRAN IF @parent_id<0 OR ISNULL(@cn_Name,'')='' BEGIN SET @ERR=1 GOTO theEnd END IF(NOT EXISTS(SELECT Id FROM Product_Category WHERE Id=@parent_id)) BEGIN SET @ERR=2 GOTO theEnd END DECLARE @Id int,@Depth int,@ordering int SELECT @Id=ISNULL(MAX(Id)+1,1) FROM Product_Category--計算@Id IF @Parent_Id=0 BEGIN SET @Depth=1--計算@Depth SELECT @Ordering=ISNULL(MAX(Ordering)+1,1) FROM Product_Category--計算@OrderId END ELSE BEGIN SELECT @Depth=Depth+1 FROM Product_Category WHERE Id=@Parent_Id--計算@Depth,計算@Ordering時需要用到 SELECT @Ordering=MAX(Ordering)+1 FROM Product_Category--計算@Ordering WHERE Id=@Parent_Id UPDATE Product_Category SET Ordering=Ordering+1 WHERE Ordering>=@Ordering--向后移動插入位置后面的所有節點 END INSERT INTO Product_Category(Id,Parent_Id,cn_Name,en_name,Depth,Ordering) VALUES (@Id,@Parent_Id,@cn_Name,@en_name,@Depth,@Ordering) IF @@ERROR<>0 SET @ERR=-1 theEnd: IF @ERR=0 BEGIN COMMIT TRAN RETURN 0 END ELSE BEGIN ROLLBACK TRAN RETURN @ERR ENDEND
希望本文所述對大家asp.net程序設計有所幫助。
