how to save database data using foreach loop in console

In table record as follows

Select * from Employee

Cityid AssName ProjectName Mobile Source Destination
1 Rakesh Java 987445 TK BLR
1 Suresh Dotnet 884554 RM BTP
1 Vignesh Testing 451211 RP KOL
1 Suresh Mainframe 457845 RF KOL

2 Ramesh Animation 454542 JS KOC
2 Magesh Warehouse 211455 WH KOC
2 Santhosh Database 445545 RO CHN
2 Vignesh ETLTool 154555 VJ CHN

console application code as follows

i am displaying the above data from table Employee(Database) in to excel file using console application.

My console application code as follows

string connectionstring = "Server=(local);initial catalog=OneC;Trusted_Connection=True";
SqlConnection con = new SqlConnection(connectionstring);
SqlCommand command= new SqlCommand();
SqlDataReader dr;
DataSet ds= new DataSet();
command.CommandText = "Select * from Employee";
command.CommandType = CommandType.Text;
command.Connection = con;
con.Open();
dr= cmd.ExecuteReader();
if (dr.HasRows)
{

using (System.IO.StreamWriter sw = new System.IO.StreamWriter(@"C:\Details\Excel.xls"))
{
while (dr.Read())
{
for (int i = 0; i < dr.FieldCount; i++)
{
sw.Autoflush = true;
sw.write(dr[i].Tostring() + "\t");
}
sw,writeline("\n");
}
}
con.Close();
}

When i exeucte above query in my system in c folder under the folder details excel file is created as follows

in my system C Folder created as details in that details folder excel file is created a
1 Rakesh Java 987445 TK BLR
1 Suresh Dotnet 884554 RM BTP
1 Vignesh Testing 451211 RP KOL
1 Suresh Mainframe 457845 RF KOL

2 Ramesh Animation 454542 JS KOC
2 Magesh Warehouse 211455 WH KOC
2 Santhosh Database 445545 RO CHN
2 Vignesh ETLTool 154555 VJ CHN

But i want excel file to be saved based on city id. The city id 1 details to be saved in one excel

And another city id 2 details to be saved in another excel

for that how can i do in asp.net using my above code in console application

I want City id 1 details to be saved in one excel as follows

1 Rakesh Java 987445 TK BLR
1 Suresh Dotnet 884554 RM BTP
1 Vignesh Testing 451211 RP KOL
1 Suresh Mainframe 457845 RF KOL

I want City id 2 details to be saved in another excel as follows

2 Ramesh Animation 454542 JS KOC
2 Magesh Warehouse 211455 WH KOC
2 Santhosh Database 445545 RO CHN
2 Vignesh ETLTool 154555 VJ CHN

First create a List of Employee

Class Employee
{
public int Cityid {get; set;}
public string AssName {get; set;}
public string ProjectName {get; set;}
public string Mobile {get; set;}
public string Source {get; set;}
public string Destination {get; set;}
}
// Create employee list
List empList = new List();

And then assign value based on your while.Read(). Once you have complete list write Linq to get results based on City id and then loop over Linq result to create excel sheets.

var results = from p in empList
group p by p.Cityid into g
select new { CityId = g.Key, Emps = g.ToList() };
foreach(var rec in results)
{
// Create excel sheet
int cityId = rec.CityId;
foreach(var emp in rec.Emps)
{
// Add all employees to sheet
}
}

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Manas Mohapatra10y ago
First create a List of Employee
Class Employee
{
    public int Cityid {get; set;}
    public string AssName {get; set;}
    public string ProjectName {get; set;}
    public string Mobile {get; set;}
    public string Source {get; set;}
    public string Destination {get; set;}
}

// Create employee list
List empList = new List();
And then assign value based on your while.Read(). Once you have complete list write Linq to get results based on City id and then loop over Linq result to create excel sheets.
var results = from p in empList
              group p by p.Cityid into g
              select new { CityId = g.Key, Emps = g.ToList() };
foreach(var rec in results)
{
    // Create excel sheet
    int cityId = rec.CityId;

    foreach(var emp in rec.Emps)
    {
        // Add all employees to sheet
    }
}
0