List<WaitingEmaliList> weList = new List<WaitingEmaliList>();
SqlConnection connection = new SqlConnection(cnStr);
SqlCommand cmd = connection.CreateCommand();
cmd.CommandText = "SELECT [emailID] ,[sentTime],[subject],[body] FROM [MailManDB].[dbo].[WaitingEmaliLists] a where DateDiff(DD,sentTime,GetDate())=0";
connection.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
weList.Add(new WaitingEmaliList { emailID = Convert.ToInt32(dr["emailID"].ToString()), body = dr["body"].ToString(), subject = dr["subject"].ToString() });
}
dr.Dispose();
connection.Close();
foreach (WaitingEmaliList we in weList)
{
cmd = connection.CreateCommand();
connection.Open();
cmd.CommandText = " SELECT e.addressId ,e.toAddress,e.toDisplayName FROM EmailAddressLists e WHERE e.emailID=@id";
SqlParameter parameters = new SqlParameter("@id", we.emailID);
cmd.Parameters.Add(parameters);
dr = cmd.ExecuteReader();
while (dr.Read())
{
Console.WriteLine(dr["toAddress"].ToString());
}
Console.WriteLine("---------------");
dr.Dispose();
connection.Close();
}
目的是达到了,想请问下有没有更好的写法
是两个连接的表 根据第一个select 的查询 得到的结果的ID去找第二个查询的结果
SqlConnection connection = new SqlConnection(cnStr);
SqlCommand cmd = connection.CreateCommand();
cmd.CommandText = "SELECT [emailID] ,[sentTime],[subject],[body] FROM [MailManDB].[dbo].[WaitingEmaliLists] a where DateDiff(DD,sentTime,GetDate())=0";
connection.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
weList.Add(new WaitingEmaliList { emailID = Convert.ToInt32(dr["emailID"].ToString()), body = dr["body"].ToString(), subject = dr["subject"].ToString() });
}
dr.Dispose();
connection.Close();
foreach (WaitingEmaliList we in weList)
{
cmd = connection.CreateCommand();
connection.Open();
cmd.CommandText = " SELECT e.addressId ,e.toAddress,e.toDisplayName FROM EmailAddressLists e WHERE e.emailID=@id";
SqlParameter parameters = new SqlParameter("@id", we.emailID);
cmd.Parameters.Add(parameters);
dr = cmd.ExecuteReader();
while (dr.Read())
{
Console.WriteLine(dr["toAddress"].ToString());
}
Console.WriteLine("---------------");
dr.Dispose();
connection.Close();
}
目的是达到了,想请问下有没有更好的写法
是两个连接的表 根据第一个select 的查询 得到的结果的ID去找第二个查询的结果
还有最好不要使用DataReader,直接读取数据库的话会占用连接资源。最好用DataSet。
将第一个的结果集放到record中,
再讲结果集的结果作为参数循环插入到第二个SQL语句中。直接利用SqlCommand去执行存储过程即可~~
无需开关两次~