How can I save the results of an SQL query into an array?
I want to use the values (located in col1 and col2) in an IF statement, leading to my thinking of saving 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();
}
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.
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
{
}
}
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();
}
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
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();
}
}
}
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; }
}
Global.arrayTest defined? why Global?