将2个SQL查询合并为一个查询并转换为c#
本文关键字:查询 一个 转换 2个 SQL 合并 | 更新日期: 2023-09-27 18:02:22
我需要将这两个单独的查询合并为一个,因为它效率不高,并将其转换为c# ASP。. NET from Classic ASP
SQL = "SELECT MemID FROM network WHERE userid='"&strTemp&"'"
Set rsNet = Conn.Execute(SQL)
if NOT rsNet.eof then
strList = rsNet("memID")
end if
rsNet.close
set rsNet = Nothing
SQL = "SELECT Distinct userid, username, img1, birthday, gendid, city, state, country, title FROM UserInfo WHERE (userinfo.userid IN (" & strList & ")) "
Set rsView = Server.CreateObject("ADODB.Recordset")
rsView.Open SQL, Conn, adOpenKeyset, adLockReadOnly
if NOT rsView.EOF then
arrN = rsView.getrows()
end if
rsView.close
set rsview = nothing
我不知道c#,但SQL去一个经典的连接…
SELECT userid, username, img1, birthday, gendid, city, state, country, title
FROM UserInfo
JOIN network
ON network.MemID = userinfo.userid
AND network.userid = :inputUserId
您应该只需要DISTINCT
,如果由于某种原因,您有非唯一的行。
DataTable dtTable = null;
using (SqlConnection oConn = new SqlConnection("Your connection string"))
{
string strQuery = @"SELECT Distinct userid, username, img1, birthday, gendid, city, state, country, title
FROM UserInfo
WHERE userinfo.userid IN
(
SELECT MemID
FROM network
WHERE userid= @userid
)";
SqlDataAdapter oDataAdapter = new SqlDataAdapter(strQuery, oConn);
oDataAdapter.Fill(dtTable, "TableName");
}
@X-Zero的select很好——如果您使用microsoft (sql-server),则参数将是@uinputUserID,而不是:inputUserId。
否则,将其转换为asp.net(使用c#)取决于您想使用的控件—dataset, datatable, datasource, datareader等…@Mr。的数据表是一个很好的方法,但是你需要将它绑定到任何正在使用它的控件。
试试这个(我包括using语句,以防您不熟悉ADO.NET使用的权利语句):
using System.Data;
using System.Data.SqlClient
public DataTable GetUserDetails(int userID)
{
DataTable dTable = new DataTable();
using (SqlConnection con = new SqlConnection(<your connection string>))
{
con.Open();
SqlCommand cmd = new SqlCommand();
cmd.Parameters.Add(new SqlParameter("@userid", userID);
cmd.Text = "SELECT DISTINCT u.userid, u.username, u.img1, u.birthday, u.genid, u.city, u.state, u.country, u.title FROM UserInfo u JOIN Network n ON u.userid = n.userid WHERE n.userid = ?";
cmd.CommandType = CommandType.Text;
cmd.Connection = con;
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dTable);
}
return dTable;
}
这并不像其他答案想象的那么简单,因为它看起来像MemId
是一个逗号分隔的字符串字段(糟糕的数据库设计)。因此,你必须使用动态SQL在…动态SQL .
using (SqlConnection conn = new SqlConnection(@"ConnectionStringHere"))
{
conn.Open();
IDbCommand cmd = conn.CreateCommand();
cmd.CommandText = @"
DECLARE @sql nvarchar(MAX)
SET @sql = '
SELECT DISTINCT
userid, username, img1, birthday,
gendid, city, state, country, title
FROM UserInfo
WHERE UserId IN
(' + (SELECT MemID FROM network WHERE UserId = @userId) + ')'
EXEC(@sql)";
IDbDataParameter param = cmd.CreateParameter();
param.DbType = DbType.String;
param.Value = "12345"; // TODO
param.ParameterName = "@userId";
cmd.Parameters.Add(param);
IDataReader dr = null;
try
{
dr = cmd.ExecuteReader();
// TODO: Process result set
}
finally
{
if (dr != null)
dr.Close();
}
}