how to get all the column value in C# from database table

Question

public void doTry()
        {
            db.ConnectionCheck();
            string sqlUname = "";
            string oracleUname = "";
            string usertesting = "select * from nayatable";
            db.cmd = new SqlCommand(usertesting, db.DBconnect);
            SqlDataReader myReader = db.cmd.ExecuteReader();
            if (myReader.Read())
            {
                sqlUname = myReader["USERNAME"].ToString();
                //MessageBox.Show(uname);
                textBox1.Text = sqlUname;
            }
    }

It only gives first row second value but I need all the column in available in database.

this should help: https://stackoverflow.com/questions/6073382/read-sql-table-into-c-sharp-datatable — Ehsan Sajjad, Jan 14 '19 at 06:28
`SqlConnection` and `SqlCommand` are for **Microsoft SQL Server** - yet the question has a tag `oracle11g` - now what are you **really** using?? — marc_s, Jan 14 '19 at 06:59
thank you but can you can tell how can I store it in a variable so that I can check further for existing user. I am trying to clone two database oracle and sql server and check the existing username and if no any matches then insert them into sql server. — Learner, Jan 14 '19 at 07:13

score 0 · Answer 1 · answered Jan 14 '19 at 06:56

There are numerous options here either you can go for DataTable as Ehsan said or you can use List to collect all the data. Something Like this:

public class dbLogin
{
   public string userName{get;set;}
}

public void doTry()
    {
        db.ConnectionCheck();
        string sqlUname = "";
        string oracleUname = "";
        string usertesting = "select * from nayatable";
        db.cmd = new SqlCommand(usertesting, db.DBconnect);
        SqlDataReader myReader = db.cmd.ExecuteReader();

List<dbLogin> dbData=new List<dbLogin>();


 if (myReader.Read())
        {
            dbLogin _dbLogin=new dbLogin();
            _dbLogin.userName = myReader["USERNAME"].ToString();
            textBox1.Text = sqlUname;
            dbData.Add(_dbLogin);
        }
}

Dmitry Bychenko · Answer 2 · 2019-01-14T08:01:39.733

If you want to get all the values, you have to loop:

        ...
        // Keep Sql Readable
        // Do not fetch unwanted columns - * 
        string userTesting = 
          @"select UserName 
              from NayaTable";

        // Wrap IDisposable into using
        using (db.cmd = new SqlCommand(userTesting, db.DBconnect)) {
          // Wrap IDisposable into using
          using(SqlDataReader myReader = db.cmd.ExecuteReader()) {
            // we are going to build one string from many records
            StringBuilder sb = new StringBuilder();

            // Here, we have to loop and aggregate / collect all the records
            while (myReader.Read()) {
              if (sb.Length > 0)
                sb.Append(", "); // delimiter 

              sb.Append(Convert.ToString(myReader["USERNAME"])); 
            }

            // Here you'll get usernames like "Sam, John, Mary" 
            textBox1.Text = sb.ToString(); 
          }
        }

how to get all the column value in C# from database table

2 Answers2