[c#] How to save SELECT sql query results in an array in C# Asp.net

I have wrote this query to get some results, if I want to save the results in an array what I have to do? I want to use the values which are in col1 and col2 in an IF statement, that's why I am thinking to save them in an array.

var con = new SqlConnection("Data Source=local;Initial Catalog=Test;Integrated Security=True");

using (con)
using (var command = new SqlCommand("SELECT col1,col2 FROM  some table", con))
{
   con.Open();
   command.ExecuteNonQuery();
}

This question is related to c# asp.net sql

The answer is


Normally i use a class for this:

public class ClassName
{
    public string Col1 { get; set; }
    public int Col2 { get; set; }
}

Now you can use a loop to fill a list and ToArray if you really need an array:

ClassName[] allRecords = null;
string sql = @"SELECT col1,col2
               FROM  some table";
using (var command = new SqlCommand(sql, con))
{
    con.Open();
    using (var reader = command.ExecuteReader())
    {
        var list = new List<ClassName>();
        while (reader.Read())
            list.Add(new ClassName { Col1 = reader.GetString(0), Col2 = reader.GetInt32(1) });
        allRecords = list.ToArray();
    }
}

Note that i've presumed that the first column is a string and the second an integer. Just to demonstrate that C# is typesafe and how you use the DataReader.GetXY methods.


Pretty easy:

 public void PrintSql_Array()
    {
        int[] numbers = new int[4];
        string[] names = new string[4];
        string[] secondNames = new string[4];
        int[] ages = new int[4];

        int cont = 0;

        string cs = @"Server=ADMIN\SQLEXPRESS; Database=dbYourBase; User id=sa; password=youpass";
        using (SqlConnection con = new SqlConnection(cs))
        {
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.Connection = con;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "SELECT * FROM tbl_Datos";
                con.Open();

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                da.Fill(dt);

                foreach (DataRow row in dt.Rows)
                {
                    numbers[cont] = row.Field<int>(0);
                    names[cont] = row.Field<string>(1);
                    secondNames[cont] = row.Field<string>(2);
                    ages[cont] = row.Field<int>(3);

                    cont++;
                }

                for (int i = 0; i < numbers.Length; i++)
                {
                    Console.WriteLine("{0} | {1} {2} {3}", numbers[i], names[i], secondNames[i], ages[i]);
                }

                con.Close();
            }
        }
    }

Instead of any Array you can load your data in DataTable like:

using System.Data;

DataTable dt = new DataTable();
using (var con = new SqlConnection("Data Source=local;Initial Catalog=Test;Integrated Security=True"))
{
    using (var command = new SqlCommand("SELECT col1,col2" +
    {
        con.Open();
        using (SqlDataReader dr = command.ExecuteReader())
        {
            dt.Load(dr);
        }
    }
}

You can also use SqlDataAdapater to fill your DataTable like

SqlDataAdapter da = new SqlDataAdapter(command);
da.Fill(dt);

Later you can iterate each row and compare like:

foreach (DataRow dr in dt.Rows)
{
    if (dr.Field<string>("col1") == "yourvalue") //your condition
    {
    }
}

A great alternative that hasn't been mentioned is to use the entity framework, which uses an object that is the table - to get data into an array you can do things like:

var rows = db.someTable.SqlQuery("SELECT col1,col2 FROM  someTable").ToList().ToArray();

for info on getting started with Entity Framework see https://msdn.microsoft.com/en-us/library/aa937723(v=vs.113).aspx


Use a SQL DATA READER:

In this example i use a List instead an array.

try
{
    SqlCommand comm = new SqlCommand("SELECT CategoryID, CategoryName FROM Categories;",connection);
    connection.Open();

    SqlDataReader reader = comm.ExecuteReader();
    List<string> str = new List<string>();
    int i=0;
    while (reader.Read())
    {
        str.Add( reader.GetValue(i).ToString() );
        i++;
    }
    reader.Close();
}
catch (Exception)
{
    throw;
}
finally
{
    connection.Close();
}

    public void ChargingArraySelect()
    {
        int loop = 0;
        int registros = 0;

        OdbcConnection conn = WebApiConfig.conn();
        OdbcCommand query = conn.CreateCommand();

        query.CommandText = "select dataA, DataB, dataC, DataD FROM table  where dataA = 'xpto'";

        try
        {
            conn.Open();
            OdbcDataReader dr = query.ExecuteReader();

            //take the number the registers, to use into next step
            registros = dr.RecordsAffected;

            //calls an array to be populated
            Global.arrayTest = new string[registros, 4];

            while (dr.Read())
            {
                if (loop < registros)
                {
                    Global.arrayTest[i, 0] = Convert.ToString(dr["dataA"]);
                    Global.arrayTest[i, 1] = Convert.ToString(dr["dataB"]);
                    Global.arrayTest[i, 2] = Convert.ToString(dr["dataC"]);
                    Global.arrayTest[i, 3] = Convert.ToString(dr["dataD"]);
                }
                loop++;
            }
        }
    }


    //Declaration the Globais Array in Global Classs
    private static string[] uso_internoArray1;
    public static string[] arrayTest
    {
        get { return uso_internoArray1; }
        set { uso_internoArray1 = value; }
    }

Examples related to c#

How can I convert this one line of ActionScript to C#? Microsoft Advertising SDK doesn't deliverer ads How to use a global array in C#? How to correctly write async method? C# - insert values from file into two arrays Uploading into folder in FTP? Are these methods thread safe? dotnet ef not found in .NET Core 3 HTTP Error 500.30 - ANCM In-Process Start Failure Best way to "push" into C# array

Examples related to asp.net

RegisterStartupScript from code behind not working when Update Panel is used You must add a reference to assembly 'netstandard, Version=2.0.0.0 No authenticationScheme was specified, and there was no DefaultChallengeScheme found with default authentification and custom authorization How to use log4net in Asp.net core 2.0 Visual Studio 2017 error: Unable to start program, An operation is not legal in the current state How to create roles in ASP.NET Core and assign them to users? How to handle Uncaught (in promise) DOMException: The play() request was interrupted by a call to pause() ASP.NET Core Web API Authentication Could not load file or assembly 'CrystalDecisions.ReportAppServer.CommLayer, Version=13.0.2000.0 WebForms UnobtrusiveValidationMode requires a ScriptResourceMapping for jquery

Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database