Procedura: Chiamare SQL Server Stored procedure in ASP.NET utilizzando Visual C# .NET

Per una versione di Microsoft Visual Basic .NET di questo articolo, vedere 306574 .

Per una versione di Microsoft Visual j# .NET di questo articolo, vedere 320622 .

IN QUESTA ATTIVITÀ

Riepilogo

In questo articolo viene illustrato come utilizzare ASP.NET e ADO.NET con Visual C# .NET per creare e per chiamare un Microsoft SQL Server stored procedure con un parametro di input e un parametro di output.

Nell'esempio di codice in questo articolo verifica innanzitutto se la stored procedure che verrà creata esiste nel database Pubs , incluso in un'installazione standard di Microsoft SQL Server. Se la stored procedure non esiste, il codice crea una stored procedure che accetta un parametro per la ricerca della tabella Authors in base al cognome e restituisce le righe corrispondenti e il numero di righe restituite nel parametro di output.

In questo articolo viene inoltre illustrato come creare un modulo Web che fornisce una semplice interfaccia utente. Il Web Form contiene i seguenti elementi:

  • Una casella di testo in cui l'utente digita la condizione di ricerca.
  • Un controllo DataGrid che visualizza i risultati della ricerca.
  • Un controllo Label che visualizza il numero di record restituiti.
  • Un controllo pulsante che chiama la stored procedure quando viene fatto clic sul pulsante.

Requisiti

Nell'elenco seguente vengono indicati l'hardware consigliato, software, infrastruttura di rete e i service pack necessari:

  • Microsoft SQL Server 7.0 o versione successiva
  • Microsoft Visual Studio .NET
  • Microsoft Visual C# .NET
  • Autorizzazioni per creare la stored procedure nel database
In questo articolo si presuppone che si abbia familiarità con i seguenti argomenti:

  • ASP.NET
  • Procedure di SQL Server archiviati

Creare un progetto ASP.NET e aggiungere i controlli

In questa sezione, si crea un progetto ASP.NET e creazione l'interfaccia utente di base. Si noti che questa procedura utilizza codice Microsoft Visual C# .NET. Per creare il progetto, attenersi alla seguente procedura:

  1. Fare clic su Start, scegliere programmi, scegliere Microsoft Visual Studio.NETe quindi fare clic su Microsoft Visual Studio.NET.
  2. Nella pagina di avvio di Visual Studio .NET, fare clic su Nuovo progetto.
  3. Nella finestra di dialogo Nuovo progetto scegliere Progetti di Visual C# in Tipi progettoe quindi fare clic su Applicazione Web ASP.NET in modelli.
  4. Nella casella nome digitare un nome per l'applicazione Web e quindi fare clic su OK.
  5. Aggiungere i seguenti controlli server Web Form e impostare le proprietà in cui sono elencati nella tabella:
    ControlloProprietà IDProprietà Text
    EtichettalblLastNameDigitare il cognome dell'autore:
    Casella di testotxtLastName%
    PulsantebtnGetAuthorsOttieni autori
    EtichettalblRowCount(Conteggio)

  6. Trascinare un controllo server DataGrid dalla casella degli strumenti per il Web Form e quindi impostare la proprietà Name su GrdAuthors.
  7. Destro della griglia e quindi fare clic su formattazione automatica.
  8. Fare clic su Professional 1 per lo schema e quindi fare clic su OK.

Creare il GetAuthorsByLastName Stored Procedure

Utilizzare il seguente codice Transact-SQL per creare la stored procedure GetAuthorsByLastName:

Use PubsGo
Create Procedure GetAuthorsByLastName (@au_lname varchar(40), @RowCount int output)
as

select * from authors where au_lname like @au_lname;

/* @@ROWCOUNT returns the number of rows that are affected by the last statement. */
select @RowCount=@@ROWCOUNT

Questo codice include due parametri: @au_lname e @RowCount. Il parametro @au_lname è un parametro di input che ottiene la stringa di ricerca per eseguire una ricerca "like" nella tabella Authors . Il parametro @RowCount è un parametro di output che utilizza la variabile @@ROWCOUNT per ottenere le righe.

Creare ed eseguire la Stored Procedure

Per accedere ai database di SQL Server, è necessario importare lo spazio dei nomi System.Data.SqlClient , che fornisce oggetti SqlDataAdapter e nuovi oggetti, ad esempio SqlDataReader . È possibile utilizzare l'oggetto SqlDataReader per leggere un flusso di righe di tipo forward-only da un database di SQL Server. DataAdapter rappresenta un set di comandi dati e una connessione di database che è possibile utilizzare per compilare l'oggetto DataSet e aggiornare un database di SQL Server.

ADO.NET viene inoltre presentato l'oggetto DataSet , che è una rappresentazione residenti in memoria dei dati che fornisce un modello di programmazione relazionale coerenza, indipendentemente dall'origine dati. Il codice in questa sezione utilizza tutti questi oggetti.

  1. Fare doppio clic su Web Form.
  2. Aggiungere il codice seguente (le direttive using ) alla sezione Declaration del Web Form, che viene visualizzata nella parte superiore della finestra del codice:
    using System.Data;using System.Data.SqlClient;

  3. Per assicurarsi che la stored procedure esiste e creare una nuova stored procedure, utilizzare un oggetto SqlCommand con un oggetto SqlDataReader . È possibile utilizzare SqlCommand per eseguire i comandi SQL nel database. Quindi chiamare il metodo ExecuteReader di SqlCommand per restituire l' oggetto SqlDataReader, che contiene righe corrispondenti per la query.

    Aggiungere il codice seguente nell'evento Page_Load del Web Form:
    private void Page_Load(object sender, System.EventArgs e){
    // Only run this code the first time the page is loaded.
    // The code inside the IF statement is skipped when you resubmit the page.
    if (!IsPostBack)
    {
    //Create a connection to the SQL Server; modify the connection string for your environment
    //SqlConnection MyConnection = new SqlConnection("server=(local);database=pubs;Trusted_Connection=yes");
    SqlConnection MyConnection = new SqlConnection("server=(local);database=pubs;UID=myUser;PWD=myPassword;");

    // Create a Command object, and then set the connection.
    // The following SQL statements check whether a GetAuthorsByLastName
    // stored procedure already exists.
    SqlCommand MyCommand = new SqlCommand("select * from sysobjects where id = object_id(N'GetAuthorsByLastName')" +
    " and OBJECTPROPERTY(id, N'IsProcedure') = 1", MyConnection);

    // Set the command type that you will run.
    MyCommand.CommandType = CommandType.Text;

    // Open the connection.
    MyCommand.Connection.Open();

    // Run the SQL statement, and then get the returned rows to the DataReader.
    SqlDataReader MyDataReader = MyCommand.ExecuteReader();

    // If any rows are returned, the stored procedure that you are trying
    // to create already exists. Therefore, try to create the stored procedure
    // only if it does not exist.
    if(!MyDataReader.Read())
    {
    MyCommand.CommandText = "create procedure GetAuthorsByLastName" +
    " (@au_lname varchar(40), select * from authors where" +
    " au_lname like @au_lname; select @RowCount=@@ROWCOUNT";
    MyDataReader.Close();
    MyCommand.ExecuteNonQuery();
    }
    else
    {
    MyDataReader.Close();
    }

    MyCommand.Dispose(); //Dispose of the Command object.
    MyConnection.Close(); //Close the connection.
    }

    // Add the event handler to the Button_Click event.
    this.btnGetAuthors.Click += new System.EventHandler(this.btnGetAuthors_Click);
    }

  4. Chiamare la stored procedure nell'evento Click del pulsante btnGetAuthors e quindi utilizzare l'oggetto SqlDataAdapter per eseguire la stored procedure. È necessario creare i parametri della stored procedure e accodarla all'insieme Parameters dell'oggetto SqlDataAdapter .

    Aggiungere il codice seguente dopo l'evento Page_Load :
    private void btnGetAuthors_Click(object sender, System.EventArgs e){
    //Create a connection to the SQL Server; modify the connection string for your environment.
    //SqlConnection MyConnection = new SqlConnection("server=(local);database=pubs;Trusted_Connection=yes");
    SqlConnection MyConnection = new SqlConnection("server=(local);database=pubs;UID=myUser;PWD=myPassword;");

    //Create a DataAdapter, and then provide the name of the stored procedure.
    SqlDataAdapter MyDataAdapter = new SqlDataAdapter("GetAuthorsByLastName", MyConnection);

    //Set the command type as StoredProcedure.
    MyDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;

    //Create and add a parameter to Parameters collection for the stored procedure.
    MyDataAdapter.SelectCommand.Parameters.Add(new SqlParameter("@au_lname", SqlDbType.VarChar, 40));

    //Assign the search value to the parameter.
    MyDataAdapter.SelectCommand.Parameters["@au_lname"].Value = (txtLastName.Text).Trim();

    //Create and add an output parameter to the Parameters collection.
    MyDataAdapter.SelectCommand.Parameters.Add(new SqlParameter("@RowCount", SqlDbType.Int, 4));

    //Set the direction for the parameter. This parameter returns the Rows that are returned.
    MyDataAdapter.SelectCommand.Parameters["@RowCount"].Direction = ParameterDirection.Output;

    //Create a new DataSet to hold the records.
    DataSet DS = new DataSet();

    //Fill the DataSet with the rows that are returned.
    MyDataAdapter.Fill(DS, "AuthorsByLastName");

    //Get the number of rows returned, and assign it to the Label control.
    //lblRowCount.Text = DS.Tables(0).Rows.Count().ToString() & " Rows Found!"
    lblRowCount.Text = MyDataAdapter.SelectCommand.Parameters[1].Value + " Rows Found!";

    //Set the data source for the DataGrid as the DataSet that holds the rows.
    GrdAuthors.DataSource = DS.Tables["AuthorsByLastName"].DefaultView;

    //NOTE: If you do not call this method, the DataGrid is not displayed!
    GrdAuthors.DataBind();

    MyDataAdapter.Dispose(); //Dispose the DataAdapter.
    MyConnection.Close(); //Close the connection.
    }

  5. In Esplora soluzioni destro della pagina aspx e quindi fare clic su Imposta come pagina iniziale.
  6. Salvare il progetto e quindi fare clic su Start in Visual Studio .NET. Si noti che il progetto viene compilato e che viene eseguita la pagina predefinita.
  7. Digitare il cognome dell'autore nella casella di testo e quindi fare clic su Ottieni autori. Si noti che viene chiamata la stored procedure e che le righe restituite compilano il DataGrid.

    È possibile utilizzare stringhe di ricerca di tipo SQL Server ad esempio G %, che restituisce tutti gli autori per i cognomi che iniziano con la lettera "G".

Risoluzione dei problemi

  • Se è possibile connettersi al database, assicurarsi che la proprietà ConnectionString punti correttamente al server che esegue SQL Server.
  • Se è possibile connettersi al database, ma se si verificano problemi quando si tenta di creare la stored procedure, assicurarsi che si dispone delle autorizzazioni corrette per creare stored procedure nel database al quale desidera connettersi.
Proprietà

ID articolo: 320916 - Ultima revisione: 30 gen 2017 - Revisione: 1

Feedback