JAK: Wywoływanie sparametryzowanej procedury przechowywanej za pomocą technologii ADO.NET i programu Visual C# .NET

Wersja tego artykułu dla programu Microsoft Visual Basic .NET: 308049 .

Wersja tego artykułu dla programu Microsoft Visual C++ .NET: 310071 .

Wersja tego artykułu dla programu Microsoft Visual J# .NET: 320627 .

W TYM ZADANIU

Streszczenie

Istnieje kilka sposobów użycia technologii ADO.NET do wywołania procedury przechowywanej oraz zwrócenia wartości i parametrów:
  • Użycie obiektu
    DataSet
    do zebrania zwróconych wierszy i pracy z tymi wierszami oraz z wartościami zwracanymi i parametrami zwracanymi.
  • Użycie obiektu
    DataReader
    do zebrania zwróconych wierszy, przejścia przez te wiersze, a następnie zebrania wartości zwracanych i parametrów zwracanych.
  • Użycie metody
    ExecuteScalar
    do zwrócenia wartości z pierwszej kolumny pierwszego wiersza wyników z wartościami zwracanymi i parametrami zwracanymi. Ta metoda jest najbardziej użyteczna w przypadku funkcji agregujących.
  • Użycie metody
    ExecuteNonQuery
    do zwrócenia tylko parametrów zwracanych i wartości zwracanych. Wszelkie zwrócone wiersze są odrzucane. Ta metoda jest najbardziej użyteczna w przypadku wykonywania kwerend funkcjonalnych.
W tym artykule pokazano ostatnie trzy metody, z użyciem obiektów
SqlCommand
i
OleDbCommand. Upewnij się, że kopiujesz tylko kod dla zarządzanego dostawcy, którego używasz. Jeśli nie masz pewności, jakiego zarządzanego dostawcy używasz, odwiedź następującą witrynę Microsoft Developer Network w sieci Web:
W każdym z przykładów pokazanych w tym artykule parametry są dodawane do kolekcji
Parameters
obiektu
Command. Używając obiektu
SqlCommand, nie trzeba dodawać tych parametrów w żadnej określonej kolejności, ale muszą one mieć poprawną nazwę. Używając obiektu
OleDbCommand, trzeba dodawać te parametry we właściwej kolejności i nie można używać ich według nazwy.


powrót do początku

Użycie obiektu DataReader do zwrócenia wierszy i parametrów

Obiektu
DataReader
można użyć do zwrócenia progresywnego strumienia danych tylko do odczytu. Informacje zawarte w obiekcie
DataReader
mogą pochodzić z procedury przechowywanej. W tym przykładzie obiekt
DataReader
jest używany do uruchomienia procedury przechowywanej, która ma parametr wejściowy i wyjściowy, a następnie przejścia przez rekordy w celu wyświetlenia parametrów zwracanych.
  1. Utwórz następującą procedurę przechowywaną na serwerze z programem Microsoft SQL Server:
    Create Procedure TestProcedure
    (
    @au_idIN varchar (11),
    @numTitlesOUT Integer OUTPUT
    )
    AS

    select A.au_fname, A.au_lname, T.title
    from authors as A join titleauthor as TA on
    A.au_id=TA.au_id
    join titles as T
    on T.title_id=TA.title_id
    where A.au_id=@au_idIN
    set @numTitlesOUT = @@Rowcount
    return (5)
  2. Utwórz nowy projekt aplikacji programu Visual C# .NET dla systemu Windows.
  3. Użyj instrukcji
    using
    dla obszarów nazw
    System
    i
    System.Data, aby w dalszej części kodu nie trzeba było określać deklaracji w tych obszarach nazw. Dodaj ten kod na początku modułu kodu Form. Upewnij się, że kopiujesz tylko kod dla wybranego dostawcy.
    Klient SQL
    using System.Data.SqlClient;
    Dostawca OLE DB Data Provider
    using System.Data.OleDb;
  4. Zastąp kod w prywatnej procedurze zdarzenia
    Form_Load
    następującym kodem:
    Klient SQL
    SqlConnection PubsConn = new SqlConnection 
    ("Data Source=server;integrated " +
    "Security=sspi;initial catalog=pubs;");
    SqlCommand testCMD = new SqlCommand
    ("TestProcedure", PubsConn);

    testCMD.CommandType = CommandType.StoredProcedure;

    SqlParameter RetVal = testCMD.Parameters.Add
    ("RetVal", SqlDbType.Int);
    RetVal.Direction = ParameterDirection.ReturnValue;
    SqlParameter IdIn = testCMD.Parameters.Add
    ("@au_idIN", SqlDbType.VarChar, 11);
    IdIn.Direction = ParameterDirection.Input;
    SqlParameter NumTitles = testCMD.Parameters.Add
    ("@numtitlesout", SqlDbType.VarChar, 11);
    NumTitles.Direction = ParameterDirection.Output ;

    IdIn.Value = "213-46-8915";
    PubsConn.Open();

    SqlDataReader myReader = testCMD.ExecuteReader();
    Console.WriteLine ("Tytuły książek tego autora:");
    while (myReader.Read())
    {
    Console.WriteLine ("{0}", myReader.GetString (2));
    };
    myReader.Close() ;
    Console.WriteLine("Liczba wierszy: " + NumTitles.Value );
    Console.WriteLine("Wartość zwracana: " + RetVal.Value);
    Dostawca OLE DB Data Provider
    OleDbConnection PubsConn = new OleDbConnection 
    ("Provider=SQLOLEDB;Data Source=server;" +
    "integrated Security=sspi;initial catalog=pubs;");
    OleDbCommand testCMD = new OleDbCommand
    ("TestProcedure", PubsConn);

    testCMD.CommandType = CommandType.StoredProcedure;

    OleDbParameter RetVal = testCMD.Parameters.Add
    ("RetVal", OleDbType.Integer);RetVal.Direction = ParameterDirection.ReturnValue;
    OleDbParameter IdIn = testCMD.Parameters.Add
    ("@au_idIN", OleDbType.VarChar, 11);
    IdIn.Direction = ParameterDirection.Input;
    OleDbParameter NumTitles = testCMD.Parameters.Add
    ("@numtitlesout", OleDbType.VarChar, 11);
    NumTitles.Direction = ParameterDirection.Output;

    IdIn.Value = "213-46-8915";

    PubsConn.Open();

    OleDbDataReader myReader = testCMD.ExecuteReader();
    Console.WriteLine ("Tytuły książek tego autora:");
    while (myReader.Read())
    {
    Console.WriteLine ("{0}", myReader.GetString (2));
    };
    myReader.Close() ;
    Console.WriteLine("Liczba wierszy: " + NumTitles.Value );
    Console.WriteLine("Wartość zwracana: " + RetVal.Value);
  5. Zmodyfikuj ciąg połączenia dla obiektu
    Connection, tak aby wskazywał serwer z programem SQL Server.
  6. Uruchom kod. Zauważ, że obiekt
    DataReader
    pobiera rekordy, a następnie zwraca wartości parametrów. Aby przejść przez zwrócone rekordy, możesz użyć metody
    Read
    obiektu
    DataReader.


    W oknie Output są wyświetlane tytuły dwóch książek, wartość zwracana 5 i parametr wyjściowy, który zawiera liczbę rekordów (2). Zauważ, że musisz zamknąć obiekt
    DataReader
    w kodzie, aby wyświetlić wartości parametrów. Zauważ również, że jeśli obiekt
    DataReader
    jest zamknięty, nie musisz przechodzić przez wszystkie rekordy, aby wyświetlić parametry zwracane.
powrót do początku

Użycie metody ExecuteScalar obiektu Command

Metody
ExecuteScalar
obiektu
Command
można użyć do pobrania wartości parametrów. Ponadto metoda
ExecuteScalar
zwraca pierwszą kolumnę pierwszego wiersza procedury przechowywanej. Ta metoda jest najbardziej użyteczna w przypadku funkcji agregujących, jak w poniższym przykładzie.
  1. Utwórz następującą procedurę przechowywaną na serwerze z programem SQL Server:
    Create Procedure TestProcedure2
    (
    @au_idIN varchar (11)
    )
    As
    /* set nocount on */
    select count (T.title)
    from authors as A join titleauthor as TA on
    A.au_id=TA.au_id
    join titles as T
    on T.title_id=TA.title_id
    where A.au_id=@au_idIN
    Return(5)
  2. Utwórz nowy projekt aplikacji programu Visual C# .NET dla systemu Windows.
  3. Użyj instrukcji
    using
    dla obszarów nazw
    System
    i
    System.Data, aby w dalszej części kodu nie trzeba było określać deklaracji w tych obszarach nazw. Dodaj ten kod na początku modułu kodu Form. Upewnij się, że kopiujesz tylko kod dla wybranego dostawcy.
    Klient SQL
    using System.Data.SqlClient;
    Dostawca OLE DB Data Provider
    using System.Data.OleDb;
  4. Dodaj następujący kod do procedury zdarzenia
    Form_Load:
    Klient SQL
    string strCount;
    SqlConnection PubsConn = new SqlConnection
    ("Data Source=server;integrated " +
    "Security=sspi;initial catalog=pubs;");
    SqlCommand testCMD = new SqlCommand
    ("TestProcedure2", PubsConn);

    testCMD.CommandType = CommandType.StoredProcedure;

    SqlParameter RetVal = testCMD.Parameters.Add
    ("RetVal", SqlDbType.Int);
    RetVal.Direction = ParameterDirection.ReturnValue;
    SqlParameter IdIn = testCMD.Parameters.Add
    ("@au_idIN", SqlDbType.VarChar, 11);
    IdIn.Direction = ParameterDirection.Input;

    IdIn.Value = "213-46-8915";

    PubsConn.Open();

    strCount =testCMD.ExecuteScalar ().ToString() ;

    Console.WriteLine("Liczba wierszy: " + strCount );
    Console.WriteLine("Wartość zwracana: " + RetVal.Value);
    Dostawca OLE DB Data Provider
    string strCount;
    OleDbConnection PubsConn = new OleDbConnection
    ("Provider=SQLOLEDB;Data Source=server;" +
    "integrated Security=sspi;initial catalog=pubs;");
    OleDbCommand testCMD = new OleDbCommand
    ("TestProcedure2", PubsConn);

    testCMD.CommandType = CommandType.StoredProcedure;

    OleDbParameter RetVal = testCMD.Parameters.Add
    ("RetVal", OleDbType.Integer);
    RetVal.Direction = ParameterDirection.ReturnValue;
    OleDbParameter IdIn = testCMD.Parameters.Add
    ("@au_idIN", OleDbType.VarChar, 11);
    IdIn.Direction = ParameterDirection.Input;

    IdIn.Value = "213-46-8915";

    PubsConn.Open();

    strCount = testCMD.ExecuteScalar().ToString() ;

    Console.WriteLine("Liczba wierszy: " + strCount);
    Console.WriteLine("Wartość zwracana: " + RetVal.Value);
  5. Zmodyfikuj ciąg połączenia dla obiektu
    Connection, tak aby wskazywał serwer z programem SQL Server.
  6. Uruchom kod. Zauważ, że metoda
    ExecuteScalar
    obiektu
    Command
    zwraca parametry. Metoda
    ExecuteScalar
    zwraca również wartość kolumny 1, wiersza 1 zwróconego zestawu wierszy. Dlatego wartość
    intCount
    jest wynikiem funkcji count z procedury przechowywanej.
powrót do początku

Użycie metody ExecuteNonQuery obiektu Command

W tym przykładzie metoda
ExecuteNonQuery
jest używana do uruchomienia kwerendy i zwrócenia wartości parametrów. Metoda ExecuteNonQuery
zwraca również liczbę rekordów, na które wpływa uruchomienie kwerendy. Jednak metoda
ExecuteNonQuery
nie zwraca żadnych wierszy ani kolumn z procedury przechowywanej.


Metoda
ExecuteNonQuery
jest najbardziej użyteczna w przypadku używania instrukcji INSERT, UPDATE lub DELETE, jeśli żądaną informacją jest tylko liczba wierszy, które uległy zmianie. W przypadku procedury przechowywanej, w której jest używana tylko instrukcja SELECT, zwracana jest wartość -1, ponieważ kwerenda nie zmienia żadnych wierszy.
  1. Utwórz następującą procedurę przechowywaną na serwerze z programem SQL Server:
    Create Procedure TestProcedure3
    (
    @au_idIN varchar (11),
    @au_fnam varchar (30)
    )

    As
    /* set nocount on */
    Update authors set au_fname = @au_fnam
    where au_id = @au_idin
    return (5)
  2. Utwórz nowy projekt aplikacji programu Visual C# .NET dla systemu Windows.
  3. Użyj instrukcji
    using
    dla obszarów nazw
    System
    i
    System.Data, aby w dalszej części kodu nie trzeba było określać deklaracji w tych obszarach nazw. Dodaj ten kod na początku modułu kodu Form. Upewnij się, że kopiujesz tylko kod dla wybranego dostawcy.
    Klient SQL
    using System.Data.SqlClient;
    Dostawca OLE DB Data Provider
    using System.Data.OleDb;
  4. Zastąp kod prywatnej procedury zdarzenia
    Form1_Load
    w module kodu Form1 następującym kodem:
    Klient SQL
    string strRowAffect;
    SqlConnection PubsConn = new SqlConnection
    ("Data Source=server;integrated Security=sspi;" +
    "initial catalog=pubs;");
    SqlCommand testCMD = new SqlCommand
    ("TestProcedure3", PubsConn);

    testCMD.CommandType = CommandType.StoredProcedure;

    SqlParameter RetVal = testCMD.Parameters.Add
    ("RetVal", SqlDbType.Int);
    RetVal.Direction = ParameterDirection.ReturnValue;
    SqlParameter IdIn = testCMD.Parameters.Add
    ("@au_idIN", SqlDbType.VarChar, 11);
    IdIn.Direction = ParameterDirection.Input;
    SqlParameter FnameIn = testCMD.Parameters.Add
    ("@au_fnam", SqlDbType.VarChar, 30);
    FnameIn.Direction = ParameterDirection.Input;

    IdIn.Value = "213-46-8915";
    FnameIn.Value = "Marjorie";

    PubsConn.Open();

    strRowAffect =testCMD.ExecuteNonQuery ().ToString() ;

    Console.WriteLine("Liczba wierszy: " + strRowAffect );
    Console.WriteLine("Wartość zwracana: " + RetVal.Value);
    Dostawca OLE DB Data Provider
    int intRowAffected;
    OleDbConnection PubsConn = new OleDbConnection
    ("Provider=SQLOLEDB;Data Source=server;" +
    "integrated Security=sspi;initial catalog=pubs;");
    OleDbCommand testCMD = new OleDbCommand
    ("TestProcedure3", PubsConn);

    testCMD.CommandType = CommandType.StoredProcedure;

    OleDbParameter RetVal = testCMD.Parameters.Add
    ("RetVal", OleDbType.Integer);
    RetVal.Direction = ParameterDirection.ReturnValue;
    OleDbParameter IdIn = testCMD.Parameters.Add
    ("@au_idIN", OleDbType.VarChar, 11);
    IdIn.Direction = ParameterDirection.Input;
    OleDbParameter FnameIn = testCMD.Parameters.Add
    ("@au_fname", OleDbType.VarChar, 30);
    FnameIn.Direction = ParameterDirection.Input;

    IdIn.Value = "213-46-8915";
    FnameIn.Value = "Marjorie";

    PubsConn.Open();
    intRowAffected = testCMD.ExecuteNonQuery();

    Console.WriteLine("Liczba wierszy, które uległy zmianie: " + intRowAffected);
    Console.WriteLine(RetVal.Value);
  5. Zmodyfikuj ciąg połączenia dla obiektu
    Connection, tak aby wskazywał serwer z programem SQL Server.
  6. Uruchom kod. W oknie Output jest wyświetlana liczba zmienionych wierszy (intRowAffected) oraz wartość parametru zwracanego.
powrót do początku

Materiały referencyjne

Aby uzyskać dodatkowe informacje, odwiedź następujące witryny MSDN w sieci Web:
powrót do początku
Właściwości

Identyfikator artykułu: 310070 — ostatni przegląd: 07.04.2003 — zmiana: 1

Opinia