調用預存程序樣本

問：
我建了一預存程序，名為：may_user,傳入參數為：@user varchar(20),@passw varchar(20);
不知在asp.net下如何調用它？謝
______________________________________________________________________________________________
答1：
SqlConnection nwindConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind");

SqlCommand salesCMD = new SqlCommand("SalesByCategory", nwindConn);
salesCMD.CommandType = CommandType.StoredProcedure;

一個例子
SqlParameter myParm = salesCMD.Parameters.Add("@CategoryName", SqlDbType.NVarChar, 15);
myParm.Value = "Beverages";

nwindConn.Open();

SqlDataReader myReader = salesCMD.ExecuteReader();

Console.WriteLine("{0}, {1}", myReader.GetName(0), myReader.GetName(1));

while (myReader.Read())
{
  Console.WriteLine("{0}, ${1}", myReader.GetString(0), myReader.GetDecimal(1));
}

myReader.Close();
nwindConn.Close();
______________________________________________________________________________________________
答2：
myCommand = new SqlCommand("Client_UpdateClients", myConnection);   
   

   // Mark the Command as a SPROC
   myCommand.CommandType = CommandType.StoredProcedure;

   // Add Parameters to SPROC
   myCommand.Parameters.Add("@ID", SqlDbType.Int).Value =client.ID ;
   myCommand.Parameters.Add("@ClientCode", SqlDbType.VarChar,20).Value =client.ClientCode;
   myCommand.Parameters.Add("@ShortName",SqlDbType.VarChar,20).Value =client.ShortName;
   myCommand.Parameters.Add("@LongName", SqlDbType.VarChar,50).Value =client.LongName;
   myCommand.Parameters.Add("@Province", SqlDbType.VarChar,20).Value =client.Province;
   myCommand.Parameters.Add("@County", SqlDbType.VarChar,20).Value =client.County ;
   myCommand.Parameters.Add("@EnterpriseTypes", SqlDbType.VarChar,20).Value =client.EnterpriseTypes ;
   myCommand.Parameters.Add("@ClientTypes", SqlDbType.VarChar,20).Value =client.ClientTypes ;
   myCommand.Parameters.Add("@Address",SqlDbType.VarChar,50).Value =client.Address;
   myCommand.Parameters.Add("@Telephone", SqlDbType.VarChar,20).Value =client.Telephone;
   myCommand.Parameters.Add("@Fax", SqlDbType.VarChar,20).Value =client.Fax ;
   myCommand.Parameters.Add("@Email", SqlDbType.VarChar,50).Value =client.Email ;
   myCommand.Parameters.Add("@PostCode", SqlDbType.VarChar,20).Value =client.PostCode ;
   myCommand.Parameters.Add("@Account", SqlDbType.VarChar,20).Value = client.Account;
   myCommand.Parameters.Add("@Taxno", SqlDbType.VarChar,20).Value = client.Taxno;
   myCommand.Parameters.Add("@Contactor", SqlDbType.VarChar,20).Value = client.Contactor;
   myCommand.Parameters.Add("@HandSet", SqlDbType.VarChar,50).Value = client.HandSet;
   myCommand.Parameters.Add("@ReMark", SqlDbType.Text).Value = client.ReMark;
   myCommand.Parameters.Add("@Credit", SqlDbType.VarChar,20).Value =client.Credit ;

   // Execute the command
   myConnection.Open();

   myCommand.ExecuteNonQuery();
   
   myConnection.Close();
______________________________________________________________________________________________
答3：
  public DataSet GetAllEditiones()
  {
   SqlDataAdapter sqlDAArticleEditionesGet = new SqlDataAdapter("Article_AllEditiones_Get",DataBaseConnection);
   sqlDAArticleEditionesGet.SelectCommand.CommandType = CommandType.StoredProcedure;

   DataSet sqlDSArticleEditionesGet = new DataSet();
   sqlDAArticleEditionesGet.Fill(sqlDSArticleEditionesGet);

   return sqlDSArticleEditionesGet;
  }
______________________________________________________________________________________________
答4：
string sql="[procedure name]"
SqlConnection conn=new SqlConnection("...");
SqlCommand comm=new (sql,conn);
comm.CommandType=CommandType.StoredProcedure
comm.Parameters.Add(new SqlParamenter("@Year",SqlDbType.Int,4));
comm.Parameters["@Year"].value=2003

______________________________________________________________________________________________
答5：
//  CREATE PROCEDURE Article_Editiones_Get @editionPosition int  AS
//   SELECT
//    *
//   FROM
//    Article_Editiones
//   WHERE EditionPosition = @editionPosition
//  GO
  public DataSet GetEditiones(int editionPosition)
  {
   SqlDataAdapter sqlDAArticleEditionesGet = new SqlDataAdapter("Article_Editiones_Get",DataBaseConnection);
   sqlDAArticleEditionesGet.SelectCommand.CommandType = CommandType.StoredProcedure;

   sqlDAArticleEditionesGet.SelectCommand.Parameters.Add(new SqlParameter("@EditionPosition",SqlDbType.Int,4));
   sqlDAArticleEditionesGet.SelectCommand.Parameters["@EditionPosition"].Value = editionPosition;

      DataSet sqlDSArticleEditionesGet = new DataSet();
   sqlDAArticleEditionesGet.Fill(sqlDSArticleEditionesGet);

   return sqlDSArticleEditionesGet;
  }
______________________________________________________________________________________________
答6：
com.CommandType=CommandType.StoredProcedure
com.ExcuteNoquery()
______________________________________________________________________________________________
答7：
調用你的預存程序，只需要2行代碼，
樓上的代碼都不需要自己寫。

http://expert.csdn.net/Expert/topic/2292/2292594.xml?temp=7.053775E-02

______________________________________________________________________________________________
答8：
sqlcon=new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings[0]);
     
strcom="exec P_Del"+" '"+Request.QueryString["t1"]+"','"+Request.QueryString["t2"]+"','"+Request.QueryString["t3"]+"','"+Convert.ToDateTime(Request.QueryString["DOld"])+"','"+Convert.ToDateTime(Request.QueryString["DNew"])+"'";

SqlCommand sqlcom=new SqlCommand(strcom,sqlcon);
sqlcon.Open();
sqlcom.ExecuteNonQuery();

可以和SQL的查詢分析器一樣用。
______________________________________________________________________________________________
答11：
調用mycomm.CommandType=CommandType.StoredProcedure;是否需要引用什麼名稱空間呀
______________________________________________________________________________________________
答12：
System.Data
System.Data.SqlClient
______________________________________________________________________________________________
答13：
這兩個我引用了的呀，但不知道他為什麼會出現以下錯誤，請大俠指教
怎麼你這句報錯呀，系統報類、結構或介面成員聲明中的標記“=”無效
salesCMD.CommandType = CommandType.StoredProcedure;

______________________________________________________________________________________________

答15：
Protected Friend Function ExecuteDataTable(ByRef rsTmp As DataTable, ByVal spName As String, ByVal aryParams() As SqlParameter) As Boolean
            Dim _tmpCmd As SqlCommand
            Dim _tmpDS As New DataSet
            Dim _tmpSDA As SqlDataAdapter
            Dim returnTmp As Boolean

            objConn = New SqlConnection(ConnectionString)
            _tmpCmd = New SqlCommand(spName, objConn)
            _tmpCmd.CommandType = CommandType.StoredProcedure

            Dim i As Integer
            For i = 0 To aryParams.Length - 1
                _tmpCmd.Parameters.Add(aryParams(i))
            Next

            Try
                objConn.Open()
                _tmpSDA = New SqlDataAdapter(_tmpCmd)
                _tmpSDA.Fill(_tmpDS, "myTable")
            Catch ex As System.Data.SqlClient.SqlException
                Throw ex
                returnTmp = False
            Finally
                _tmpSDA.Dispose()
                _tmpCmd.Dispose()
                _tmpSDA = Nothing
                _tmpCmd = Nothing
                objConn.Dispose()
                objConn = Nothing
                rsTmp = _tmpDS.Tables("myTable")
                returnTmp = True
            End Try

            Return returnTmp
        End Function

        Public Function StatisticStock(ByVal NO As String, _
                ByVal TableName As String, _
                ByVal Action As ActionStatus) As Integer

            Dim dbTmp As New DBCls
            Dim _tmpParam() As SqlParameter
            ReDim _tmpParam(3)
            Dim returnTmp As Integer
            Dim _tmpTab As DataTable

            _tmpParam(0) = New SqlParameter("@NO", SqlDbType.Char, 10)
            _tmpParam(0).Value = NO
            _tmpParam(1) = New SqlParameter("@TableName", SqlDbType.Char, 20)
            _tmpParam(1).Value = TableName
            _tmpParam(2) = New SqlParameter("@Action", SqlDbType.Char)
            _tmpParam(2).Value = Action.ToString
            _tmpParam(3) = New SqlParameter("return_value", SqlDbType.Int)
            _tmpParam(3).Direction = ParameterDirection.ReturnValue

            If dbTmp.ExecuteDataTable(_tmpTab, "Storage_StatisticStock", _tmpParam) Then
                returnTmp = _tmpParam(3).Value
                _tmpParam = Nothing
                dbTmp = Nothing
                Return returnTmp
            Else
                Return 0
            End If
        End Function

______________________________________________________________________________________________
答16：
有可能我粘貼過來變成全形的原因了
你替換一下"="就可以了
名字空間引用
using System.Data.SqlClient;

______________________________________________________________________________________________
答17：
SqlConnection nwindConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind");
//SalesByCategory預存程序名稱
SqlCommand salesCMD = new SqlCommand("SalesByCategory", nwindConn);
salesCMD.CommandType = CommandType.StoredProcedure;

//增加參數：名稱，類型，長度
SqlParameter myParm = salesCMD.Parameters.Add("@CategoryName", SqlDbType.NVarChar, 15);
//傳入的參數值
myParm.Value = "Beverages";

nwindConn.Open();
//執行
SqlDataReader myReader = salesCMD.ExecuteReader();