Trouble filling a DataTable from MS SQL table

Question

This is what I have so far:

static void Main(string[] args)
        {
            DataTable t = new DataTable();

            string connetionString = null;
            SqlConnection cnn ;
            connetionString = "Data Source=local.url;Initial Catalog=databasename;User ID=username;Password=password";

            cnn = new SqlConnection(connetionString);

            string sql = "SELECT * FROM shiplabels";
            SqlDataAdapter a = new SqlDataAdapter(sql, cnn);

            try
            {
                cnn.Open();
                a.Fill(t);
                cnn.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine ("Can not open connection ! ");
            }
        }

I want to connect to this Microsoft DB and pull data from it. I am having trouble just getting this to work! When I use this code datatable t has 0 rows where it should come back with a few hundred. I'm clearly missing something simple here?

This result would strongly suggest that there are indeed zero rows in the `shiplabels` table. Perhaps you've overlooked something in debugging? — David, Dec 15 '16 at 18:37
http://stackoverflow.com/questions/16958155/fill-datatable-from-sql-server-database... http://stackoverflow.com/questions/11993211/how-to-fill-datatable-with-sql-table... http://stackoverflow.com/questions/6073382/read-sql-table-into-c-sharp-datatable... take your pick. An external one: https://social.msdn.microsoft.com/Forums/en-US/88acfb48-b5b2-42d2-881d-5cd14eac4790/what-is-the-fastest-way-to-fill-datatable-from-sql-server?forum=csharpgeneral — Marc L., Jan 20 '17 at 19:22
Possible duplicate of [Fill DataTable from SQL Server database](http://stackoverflow.com/questions/16958155/fill-datatable-from-sql-server-database) — Marc L., Jan 20 '17 at 19:23

Zath · Answer 1 · 2016-12-15T18:53:02.493

        DataTable dt = new DataTable();
        SqlDataAdapter sqlAdtp = new SqlDataAdapter();
        string connectionString = "Data Source=local.url;Initial Catalog=databasename;User ID=username;Password=password";
        string sql = "SELECT * FROM shiplabels";

        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            using (SqlCommand cmd = new SqlCommand(sql, conn))
            {
                cmd.CommandType = CommandType.Text;                   

                try
                {
                    sqlAdtp.SelectCommand = cmd;
                    sqlAdtp.Fill(dt);
                }
                catch (Exception ex)
                {

                }
            }
        }

First, you don't need to open the connection when using SqlDataAdapter. Also, you forgot the CommandType.

score 0 · Answer 2 · answered Jan 20 '17 at 18:33

This should work fine for you.

using System;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient; 

namespace WindowsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            string connetionString = null;
            SqlConnection sqlCnn ;
            SqlCommand sqlCmd ;
            SqlDataAdapter adapter = new SqlDataAdapter();
            DataSet ds = new DataSet();
            int i = 0;
            string sql = null;

            connetionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password";
            sql = "Select * from product";

            sqlCnn = new SqlConnection(connetionString);
            try
            {
                sqlCnn.Open();
                sqlCmd = new SqlCommand(sql, sqlCnn);
                adapter.SelectCommand = sqlCmd;
                adapter.Fill(ds);
                for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
                {
                    MessageBox.Show(ds.Tables[0].Rows[i].ItemArray[0] + " -- " + ds.Tables[0].Rows[i].ItemArray[1]);
                }
                adapter.Dispose();
                sqlCmd.Dispose();
                sqlCnn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Can not open connection ! ");
            }
        }
    }
}

Trouble filling a DataTable from MS SQL table

2 Answers2