SQL Queries + DataTable + GridView
本文关键字:GridView DataTable SQL Queries | 更新日期: 2023-09-27 18:15:00
我需要从SQL数据库中读取数据,并在表中创建以下结果:
DATABASE
=========
Server, Site Name, Status
001, Site 1, Down
002, Site 1, Up
003, Site 2, Up
004, Site 2, Down
001, Site 3, Up
005, Site 4, Down
- 站点1:它存在于2个服务器(001,002)
- 站点2:它存在于2个服务器(003,004)
- 站点3:它只存在于1台服务器(001)
- 站点4:它只存在于1台服务器(005)
@@@@@@@@@@@@@@@@@@@@@@
SITE NAME 1 = 1 long column
@@@@@@@@@@@@@@@@@@@@@@
SERVER 1 =列1 || SERVER 2 =列2
@@@@@@@@@@@@@@@@@@@@@@
SITE NAME 2 = 1 long column
@@@@@@@@@@@@@@@@@@@@@@
SERVER 1 =列1 || SERVER 2 =列2
@@@@@@@@@@@@@@@@@@@@@@
这是我的c#代码SqlConnection myConnection = new SqlConnection("my connection");
string SqlCon = "SELECT distinct(Site_Name) as SN FROM TABLE;";
using (myConnection)
{
myConnection.Open();
SqlDataAdapter da1 = new SqlDataAdapter(SqlCon, myConnection);
DataTable dt = new DataTable();
da1.Fill(dt);
foreach (DataRow dr in dt.Rows) //IS THIS CORRECT?
{
string SqlCon2 = "SELECT * FROM LIVEWEBSITES WHERE Site_Name='" + dr["SN"].ToString() + "';";
//Crete the table and populate it here
if Status==Down then bgcolor of the cell = RED
else bgcolor = GREEN
}
MyGridView.DataSource = dt;
MyGridView.DataBind();
}
第一个SQL查询是只选择明显的站点
然后使用该信息执行另一个SQL查询以选择所有信息并相应地填充表
我该怎么做?
您是否考虑过使用DataContext
或DbContext
?基本上就是将数据库映射到代码。然后你可以利用LINQ。
例如,如果你有一个学生表和一个学生信息表,你可以这样做:
public class MyContext : DataContext
{
public Table<Student> Students { get { return GetTable<Student>(); } }
public Table<StudentInfo> StudentInfo { get { return GetTable<StudentInfo>(); } }
public MyContext(string ConnString) : base(ConnString) {}
}
[Table(Name = "dbo.Students")]
public class Student
{
[Column(IsPrimary = true, Name = "StudentId", IsDbGenerated = true, DbType = "int Not Null IDENTITY")]
public int StudentId { get; set; }
[Column(Name = "Name")]
public string Name { get; set; }
}
[Table(Name = "dbo.StudentInfo")]
public class StudentInfo
{
[Column(IsPrimary = true, Name = "StudentInfoId", IsDbGenerated = true, DbType = "int Not Null IDENTITY")]
public int StudentInfoId { get; set; }
[Column(Name = "StudentId")]
public int StudentId { get; set; } // FK
[Column(Name = "Address")]
public string Address{ get; set; }
}
// a method somewhere
public void StudentLookup(string name)
{
var MyContext context = new MyContext(connString);
var student = context.Students.Single(x => x.Name == name)
var StudentInfo = context.StudentInfo.Where(x => x.StudentId == student.StudentId);
}
这是一个选择,但我建议使用实体框架与代码优先迁移