CODEWITHSUNDEEP
c#sqlsql-servert-sqllistview
user222427
user222427

Reputation:

SQL export to ListView using C#

I have a query that selects 3 columns from a SQL table, I need to export the resulting query into a listview. I don't need any help with the SQL setup, just how to format it into the listview. There is where I am stuck. Any help is appreciated. Thanks!

string sql = "select distinct pro,ds,date from dbo.Sge where date IN (select max(date) from dbo.Storage) order by project";
SqlConnection con = new SqlConnection("Data Source= ;Initial Catalog= ;Integrated Security= SSPI");
con.Open();
DataSet ds = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter(sql,con);
adapter.Fill(ds);
foreach (DataRow row in ds.Tables[0].Rows)
{
    for(int i=0; i< ds.Tables[0].Columns.Count; i++))
}

Edit: Sorry guys, i mentioned ListBox originally and i meant ListView. My ListView is detailed and had a column for each of my columns in my select, i just simply need the SQL export to go to the right column.

This is what I have come up with, but it doesn't add to each column, just to the first column

foreach (DataRow row in poplb6ds.Tables[0].Rows)
{
    listView1.Items.Add(row["Pro"].ToString());
    listView1.Items.Add(row["size"].ToString());
    listView1.Items.Add(row["Date"].ToString());
}

Upvotes: 1

Views: 11967

Answers (4)

abatishchev
abatishchev

Reputation: 100258

  1. Use using blocks:

    using (SqlConnection con = new SqlConnection("Data Source=;Initial Catalog= ;Integrated Security= SSPI"))
{
    con.Open();
    using (DataSet ds = new DataSet())
    {
        using (SqlDataAdapter adapter = new SqlDataAdapter(sql, con))
        {
            adapter.Fill(ds);
        }    
    }
}

  2. Fill single table:

    DataTable dt = new DataTable(); // new DataTable("Sge"); name it if will be required
adapter.Fill(dt);

  3. Your loop has makes no sense because columns are the same for each row in the table:

    foreach (DataColumn column in dt.Columns)
{
}

  4. Or you want to iterate thru each row and each column?

    foreach (DataColumn column in dt.Columns)
{
    foreach (DataRow row in dt)
    {
        object value = row[column];
    }
}

Upvotes: 0

franklins
franklins

Reputation: 3738

Check this out:

C# listView, how do I add items to columns 2, 3 and 4 etc?

Upvotes: 0

bitxwise
bitxwise

Reputation: 3594

The following assumes that your ListView has columns in the order |Pro|Size|Date|

foreach (DataRow row in poplb6ds.Tables[0].Rows) {
    ListViewItem item = new ListViewItem(row["Pro"].ToString());
    item.SubItems.Add(row["size"].ToString());
    item.SubItems.Add(row["Date"].ToString());
    listView1.Items.Add(item);
}

Upvotes: 3

MrFox
MrFox

Reputation: 5116

The standaard solution would be: listbox1.DataSource = poplb6ds; If you need any specific formatting, please elaborate on that.

Upvotes: 0

Related Questions