CODEWITHSUNDEEP
c#sqlsql-server
Dwane Marsh
Dwane Marsh

Reputation: 75

How to set database table values in a string?

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"]);

Upvotes: 0

Views: 896

Answers (3)

Salem Ahmed
Salem Ahmed

Reputation: 1096

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"];
    }
}

Upvotes: 0

Nyandika
Nyandika

Reputation: 121

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

Upvotes: 1

Andrei dela Cruz
Andrei dela Cruz

Reputation: 628

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!

Upvotes: 0

Related Questions