Populer le tableau de données à partir du lecteur de données

Question

Je fais une chose de base en C# (MS VS2008) et j'ai une question plus sur le bon design que sur le code spécifique.

Je crée un datatable puis j'essaie de charger le datatable à partir d'un datareader (basé sur une procédure stockée SQL). Ce que je me demande, c'est si la manière la plus efficace de charger le datatable est d'utiliser une instruction while, ou s'il y a une meilleure façon de procéder.

À mon avis, le seul inconvénient est que je dois taper manuellement les champs que je veux ajouter dans mon instruction while, mais je ne connais pas non plus de moyen de les automatiser de toute façon, car je ne veux pas tous les champs de la procédure stockée, juste ceux que je sélectionne, mais ce n'est pas un gros problème à mes yeux.

J'ai inclus des extraits de code ci-dessous pour montrer tout ce que je fais, bien que pour moi le code lui-même n'est pas remarquable ni même ce dont je parle. Plus précisément, je me demande si ma méthodologie est bonne/efficace ; je demanderai de l'aide sur le code plus tard si ma stratégie est mauvaise/inefficace.

var dtWriteoffUpload = new DataTable();
dtWriteoffUpload.Columns.Add("Unit");
dtWriteoffUpload.Columns.Add("Year");
dtWriteoffUpload.Columns.Add("Period");
dtWriteoffUpload.Columns.Add("Acct");
dtWriteoffUpload.Columns.Add("Descr");
dtWriteoffUpload.Columns.Add("DEFERRAL_TYPE");
dtWriteoffUpload.Columns.Add("NDC_Indicator");
dtWriteoffUpload.Columns.Add("Mgmt Cd");
dtWriteoffUpload.Columns.Add("Prod");
dtWriteoffUpload.Columns.Add("Node");
dtWriteoffUpload.Columns.Add("Curve_Family");
dtWriteoffUpload.Columns.Add("Sum Amount");
dtWriteoffUpload.Columns.Add("Base Curr");
dtWriteoffUpload.Columns.Add("Ledger");  
cmd = util.SqlConn.CreateCommand();
cmd.CommandTimeout = 1000;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "proc_writeoff_data_details";
cmd.Parameters.Add("@whoAmI", SqlDbType.VarChar).Value = 

WindowsIdentity.GetCurrent().Name;
cmd.Parameters.Add("@parmEndDateKey", SqlDbType.VarChar).Value = myMostRecentActualDate;
cmd.Parameters.Add("@countrykeys", SqlDbType.VarChar).Value = myCountryKey;
cmd.Parameters.Add("@nodekeys", SqlDbType.VarChar).Value = "1,2";
break;
dr = cmd.ExecuteReader();
while (dr.Read())                    
{
    dtWriteoffUpload.Rows.Add(dr["country name"].ToString(), dr["country key"].ToString());
}

Answer 1

Vous pouvez charger un DataTable directement à partir d'un lecteur de données en utilisant la méthode Load() qui accepte un IDataReader.

var dataReader = cmd.ExecuteReader();
var dataTable = new DataTable();
dataTable.Load(dataReader);

Answer 2

Si vous essayez de charger un DataTable, alors exploitez plutôt le SqlDataAdapter :

DataTable dt = new DataTable();

using (SqlConnection c = new SqlConnection(cString))
using (SqlDataAdapter sda = new SqlDataAdapter(sql, c))
{
    sda.SelectCommand.CommandType = CommandType.StoredProcedure;
    sda.SelectCommand.Parameters.AddWithValue("@parm1", val1);
    ...

    sda.Fill(dt);
}

Vous n'avez même pas besoin de définir les colonnes. Créez simplement le DataTable et Fill le.

Ici, cString est votre chaîne de connexion et sql est la commande de procédure stockée.

Answer 3

Comme l'a indiqué Sagi dans sa réponse, DataTable.Load est une bonne solution. Si vous essayez de charger plusieurs tables à partir d'un seul lecteur, vous n'avez pas besoin d'appeler DataReader.NextResult. La méthode DataTable.Load fait également avancer le lecteur vers le jeu de résultats suivant (le cas échéant).

// Lire chaque jeu de résultats dans le lecteur de données.
while (!reader.IsClosed)
{
    DataTable dt = new DataTable();
    // DataTable.Load fait automatiquement avancer le lecteur vers le jeu de résultats suivant
    dt.Load(reader);
    items.Add(dt);
}

Answer 4

J'ai également examiné cela, et après avoir comparé la méthode SqlDataAdapter.Fill avec les fonctions SqlDataReader.Load, j'ai constaté que la méthode SqlDataAdapter.Fill est plus de deux fois plus rapide avec les ensembles de résultats que j'ai utilisés

Code utilisé:

    [TestMethod]
    public void SQLCommandVsAddaptor()
    {
        long AdapterFillLargeTableTime, readerLoadLargeTableTime, AdapterFillMediumTableTime, readerLoadMediumTableTime, AdapterFillSmallTableTime, readerLoadSmallTableTime, AdapterFillTinyTableTime, readerLoadTinyTableTime;

        string LargeTableToFill = "select top 10000 * from FooBar";
        string MediumTableToFill = "select top 1000 * from FooBar";
        string SmallTableToFill = "select top 100 * from FooBar";
        string TinyTableToFill = "select top 10 * from FooBar";

        using (SqlConnection sconn = new SqlConnection("Data Source=.;initial catalog=Foo;persist security info=True; user id=bar;password=foobar;"))
        {
            // mesures du jeu de données volumineux
            AdapterFillLargeTableTime = MesurerTempsExecutionMéthode(sconn, LargeTableToFill, ExecuteDataAdapterFillStep);
            readerLoadLargeTableTime = MesurerTempsExecutionMéthode(sconn, LargeTableToFill, ExecuteSqlReaderLoadStep);
            // mesures du jeu de données moyen
            AdapterFillMediumTableTime = MesurerTempsExecutionMéthode(sconn, MediumTableToFill, ExecuteDataAdapterFillStep);
            readerLoadMediumTableTime = MesurerTempsExecutionMéthode(sconn, MediumTableToFill, ExecuteSqlReaderLoadStep);
            // mesures du jeu de données petit
            AdapterFillSmallTableTime = MesurerTempsExecutionMéthode(sconn, SmallTableToFill, ExecuteDataAdapterFillStep);
            readerLoadSmallTableTime = MesurerTempsExecutionMéthode(sconn, SmallTableToFill, ExecuteSqlReaderLoadStep);
            // mesures du jeu de données minuscule
            AdapterFillTinyTableTime = MesurerTempsExecutionMéthode(sconn, TinyTableToFill, ExecuteDataAdapterFillStep);
            readerLoadTinyTableTime = MesurerTempsExecutionMéthode(sconn, TinyTableToFill, ExecuteSqlReaderLoadStep);
        }
        using (StreamWriter writer = new StreamWriter("result_sql_compare.txt"))
        {
            writer.WriteLine("10000 lignes");
            writer.WriteLine("Adaptateur de données SQL 100 fois la vitesse de remplissage du tableau 10000 lignes : {0} millisecondes", AdapterFillLargeTableTime);
            writer.WriteLine("Lecteur de données SQL 100 fois la vitesse de chargement du tableau 10000 lignes : {0} millisecondes", readerLoadLargeTableTime);
            writer.WriteLine("1000 lignes");
            writer.WriteLine("Adaptateur de données SQL 100 fois la vitesse de remplissage du tableau 1000 lignes : {0} millisecondes", AdapterFillMediumTableTime);
            writer.WriteLine("Lecteur de données SQL 100 fois la vitesse de chargement du tableau 1000 lignes : {0} millisecondes", readerLoadMediumTableTime);
            writer.WriteLine("100 lignes");
            writer.WriteLine("Adaptateur de données SQL 100 fois la vitesse de remplissage du tableau 100 lignes : {0} millisecondes", AdapterFillSmallTableTime);
            writer.WriteLine("Lecteur de données SQL 100 fois la vitesse de chargement du tableau 100 lignes : {0} millisecondes", readerLoadSmallTableTime);
            writer.WriteLine("10 lignes");
            writer.WriteLine("Adaptateur de données SQL 100 fois la vitesse de remplissage du tableau 10 lignes : {0} millisecondes", AdapterFillTinyTableTime);
            writer.WriteLine("Lecteur de données SQL 100 fois la vitesse de chargement du tableau 10 lignes : {0} millisecondes", readerLoadTinyTableTime);

        }
        Process.Start("result_sql_compare.txt");
    }

    private long MesurerTempsExecutionMéthode(SqlConnection conn, string query, Action Méthode)
    {
        long time; // savoir C#
        // exécuter une seule étape de lecture hors du temps de mesure, pour chauffer le cache ou autre
        Méthode(conn, query);
        // démarrer le chronométrage
        time = Environment.TickCount;
        for (int i = 0; i < 100; i++)
        {
            Méthode(conn, query);
        }
        // retourner le temps en millisecondes
        return Environment.TickCount - time;
    }

    private void ExecuteDataAdapterFillStep(SqlConnection conn, string query)
    {
        DataTable tab = new DataTable();
        conn.Open();
        using (SqlDataAdapter comm = new SqlDataAdapter(query, conn))
        {
            // Fonction de remplissage du tableau de l'adaptateur
            comm.Fill(tab);
        }
        conn.Close();
    }

    private void ExecuteSqlReaderLoadStep(SqlConnection conn, string query)
    {
        DataTable tab = new DataTable();
        conn.Open();
        using (SqlCommand comm = new SqlCommand(query, conn))
        {
            using (SqlDataReader reader = comm.ExecuteReader())
            {
                // Fonction de chargement de l'IDataReader
                tab.Load(reader);
            }
        }
        conn.Close();
    }

Résultats:

10000 lignes :
Adaptateur de données SQL 100 fois la vitesse de remplissage du tableau 10000 lignes : 11782 millisecondes
Lecteur de données SQL  100 fois la vitesse de chargement du tableau 10000 lignes : 26047 millisecondes
1000 lignes :
Adaptateur de données SQL 100 fois la vitesse de remplissage du tableau 1000 lignes: 984  millisecondes
Lecteur de données SQL  100 fois la vitesse de chargement du tableau 1000 lignes: 2031 millisecondes
100 lignes :
Adaptateur de données SQL 100 fois la vitesse de remplissage du tableau 100 lignes : 125 millisecondes
Lecteur de données SQL  100 fois la vitesse de chargement du tableau 100 lignes : 235 millisecondes
10 lignes :
Adaptateur de données SQL 100 fois la vitesse de remplissage du tableau 10 lignes : 32 millisecondes
Lecteur de données SQL  100 fois la vitesse de chargement du tableau 10 lignes: 93 millisecondes

Pour des problèmes de performances, utiliser la méthode SqlDataAdapter.Fill est bien plus efficace. Donc sauf si vous voulez vous tirer une balle dans le pied, utilisez cela. Cela fonctionne plus rapidement pour les petits et grands ensembles de données.

Answer 5

Sans doute la solution la plus élégante est :

DataTable dt = reader.GetSchemaTable();

C’est une seule ligne, alors allez-y et essayez! Une fois que vous avez ouvert un lecteur, vous appelez simplement GetSchemaTable. La beauté de cette méthode est qu'elle fonctionne même lorsque la requête ne renvoie pas de lignes, et pourtant vous pouvez obtenir des informations sur les colonnes. Il y a 31 attributs en tout, y compris si une colonne est NOT NULL ou non au serveur SQL. Elle obtient même toutes les propriétés étendues que vous avez définies sur la colonne au serveur SQL. En prime, vous pouvez facilement voir tout ce qu’elle obtient via le Visualiseur de données dans Visual Studio en survolant simplement "DataTable dt". capture d'écran du Visualiseur