存儲過程 兩種方法的實例

protected void btnSend_Click(object sender, EventArgs e)
   2: {
   3:     int temp = -1;
   4:     int exhibitionID = int.Parse(Request.QueryString["ConList"]);
   5:     int exhibitorType = 0;
   6:     
   7:     int mCustomerID = 0;
   8:     if (Session["UserID"] != null)
   9:     {
  10:         ViewState["CID"] = myShare.GetAObject(true, 1, "CustomerID", "Customers", "UserID='" + Session["UserID"].ToString() + "'").ToString();
  11:         if (!string.IsNullOrEmpty(ViewState["CID"].ToString()))
  12:         {
  13:             mCustomerID = Convert.ToInt32(ViewState["CID"].ToString());
  14:         }
  15:     }
  16:     string company = this.txtCompay.Text.Trim();
  17:     string name = this.txtName.Text;
  18:     string tel = this.txtTel.Text;
  19:     string email = this.txtEmail.Text;
  20:  
  21:     SqlConnection con = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"]);
  22:  
  23:     SqlCommand com = new SqlCommand("ExpoApply", con);
  24:     com.CommandType = CommandType.StoredProcedure;
  25:  
  26:     SqlParameter parCustomerID = new SqlParameter("@CustomerID", SqlDbType.Int);
  27:     parCustomerID.Value = mCustomerID;
  28:     com.Parameters.Add(parCustomerID);
  29:  
  30:     SqlParameter parCompanyName = new SqlParameter("@CompanyName", SqlDbType.NVarChar, 100);
  31:     parCompanyName.Value = company;
  32:     com.Parameters.Add(parCompanyName);
  33:  
  34:     SqlParameter parContact = new SqlParameter("@Contact", SqlDbType.VarChar, 50);
  35:     parContact.Value = name;
  36:     com.Parameters.Add(parContact);
  37:  
  38:     SqlParameter parEmail = new SqlParameter("@Email", SqlDbType.VarChar, 50);
  39:     parEmail.Value = email;
  40:     com.Parameters.Add(parEmail);
  41:  
  42:  
  43:     SqlParameter parReturnValue = new SqlParameter("@ReturnValue", SqlDbType.Int);
  44:     parReturnValue.Direction = ParameterDirection.Output;
  45:     com.Parameters.Add(parReturnValue);
  46:  
  47:     try
  48:     {
  49:         con.Open();
  50:         com.ExecuteNonQuery();
  51:  
  52:         temp = int.Parse(com.Parameters["@ReturnValue"].Value.ToString());
  53:  
  54:         if (temp == -1)
  55:         {
  56:             myShare.WebMessageBox(this.Page, "信息發佈時出錯，請重試！");
  57:         }
  58:         else if (temp == 0)
  59:         {
  60:             myShare.WebMessageBox(this.Page, "信息發佈失敗，請重試！");
  61:         }
  62:         else
  63:         {
  64:             ClientScript.RegisterStartupScript(ClientScript.GetType(), "提示", "<script>alert('信息發佈成功！');window.location='DiscountExpoList.aspx'</script>");
  65:         }
  66:  
  67:     }
  68:     catch (Exception ex)
  69:     {
  70:         Response.Write(ex.Message);
  71:     }
  72:  
  73: }

1: SqlConnection con = null;
   2: try
   3: {
   4:     con = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"]);
   5:     SqlCommand com = new SqlCommand();
   6:     com.Connection = con;
   7:     com.CommandType = CommandType.StoredProcedure;
   8:     com.CommandText = "ExpoApply";
   9:     con.Open();
  10:     SqlParameter[] sp = 
  11:      {
  12:          new SqlParameter("@CustomerID",SqlDbType.Int)
  13:          ,new SqlParameter("@CompanyName",SqlDbType.NVarChar,100)
  14:          ,new SqlParameter("@Contact",SqlDbType.VarChar,50)
  15:          ,new SqlParameter("@Email",SqlDbType.VarChar,50)
  16:          //...   
  17:          ,new SqlParameter("@ReturnValue",SqlDbType.Int)
  18:  
  19:       };
  20:     sp[0].Value = mCustomerID;
  21:     sp[1].Value = company;
  22:     sp[2].Value = name;
  23:     sp[3].Value = email;
  24:     //  ...
  25:     sp[16].Direction = ParameterDirection.Output;
  26:     com.Parameters.AddRange(sp);
  27:     com.ExecuteNonQuery();
  28:     temp = Convert.ToInt32(com.Parameters["@ReturnValue"].Value);
  29:     con.Close();
  30: }
  31:  
  32: catch (SqlException se)
  33: {
  34: }
  35: finally
  36: {
  37:     con.Close();
  38: }
  39:  
  40: if (temp == -1)
  41: {
  42:     myShare.WebMessageBox(this.Page, "信息發佈時出錯，請重試！");
  43: }
  44: else if (temp == 0)
  45: {
  46:     myShare.WebMessageBox(this.Page, "信息發佈失敗，請重試！");
  47: }
  48: else
  49: {
  50:     ClientScript.RegisterStartupScript(ClientScript.GetType(), "提示", "<script>alert('信息發佈成功！');window.location='DiscountExpoList.aspx'</script>");
  51: }