How to set database table values in a string?

Question

I have a SQL Server database table EmpDetails and it contains two row values. I want to save these values into another table. but as per my code I can get only first row value. How to get all rows values from my EmpDetails table.

My sample code is:

SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString);
SqlCommand cmd  = new SqlCommand ("select e.MachID,e.Name,e.EmpCode,T.TypeName from EmpDetails e,EmpType t where e.EtypeID=t.EtypeID  and e.Deptid='" + dddep.SelectedItem.Value + "'",conn);
SqlDataAdapter sda = new SqlDataAdapter(cmd);

DataSet ds = new DataSet();
sda.Fill(ds);

string name = Convert.ToString(ds.Tables[0].Rows[0]["Name"]);
string code = Convert.ToString(ds.Tables[0].Rows[0]["EmpCode"]);
string type = Convert.ToString(ds.Tables[0].Rows[0]["TypeName"]);

By using ds.Tables[0].Rows[1]["XYZ"] and storing those into different variables? — User
– User, Commented Nov 6, 2014 at 5:51

Nyandika · Accepted Answer · 2014-11-06 05:56:29Z

1

You could try to use a loop because currently you are getting values from the first row when you say index [0].

Example:

//let n be the total rows

 for(int i = 0; i < n; i++)
{
     string name = Convert.ToString(ds.Tables[0].Rows[i]["Name"]);
     string code = Convert.ToString(ds.Tables[0].Rows[i]["EmpCode"]);
     string type = Convert.ToString(ds.Tables[0].Rows[i]["TypeName"]);
}

Hope it helps

answered Nov 6, 2014 at 5:56

Nyandika

1212 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dwane Marsh Over a year ago

Yup this also helpful

Andrei dela Cruz · Accepted Answer · 2014-11-06 05:52:14Z

0

What you can do is iterate the Rows in each Table using foreach. Since Tables[0].Rows return a DataRowCollection, you can directly put the rows in a loop:

var rows = ds.Tables[0].Rows;
foreach (DataRow row in rows)
{
   var name = row["Name"];
   var code= row["EmpCode"];
   var type= row["TypeName"];
}

And you can use a collection to store the values for each column for each row, maybe like a list that uses a simple data model.

Hope that works!

answered Nov 6, 2014 at 5:52

Andrei dela Cruz

6284 silver badges11 bronze badges

Comments

Salem Ahmed · Accepted Answer · 2014-11-06 06:28:12Z

0

if (ds !=null && ds.Tables.count > 0 )  {
   foreach (row in ds.Tables["EmpDetails"].Rows)
    {
       var name = row["Name"];
       var EmpCode= row["EmpCode"];
       var TypeName= row["TypeName"];
    }
}

answered Nov 6, 2014 at 6:28

Salem Ahmed

1,0962 gold badges12 silver badges18 bronze badges

Collectives™ on Stack Overflow

How to set database table values in a string?

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related