如何为特定的 sql 查询执行 C# 解决方法

Question

我正在用 C# 构建一个分层树列表，我正在使用 sql 查询来获取我的结果。但是，我似乎无法让这个查询工作。它给了我在sql-server中所需要的，但它在C#中不起作用。此查询是否有可能的解决方法？ .HTML

<div class="Demo">
<h1>Tree Demo</h1>
<div class="tree well">
<ul>
<li><span>ServerName</span>
<ul>
<asp:Repeater ID="rpDatabasesParent" runat="server" OnItemDataBound="loadTables">
<ItemTemplate>
<li><span><%# Eval("Name") %></span>
<ul>
<h3>Tables</h3>
<asp:Repeater ID="rpTablesChild" runat="server" OnItemDataBound="loadTables">
<ItemTemplate>
<li><span><%# Eval("Tables") %></span></li>
</ItemTemplate>
</asp:Repeater>
</ul>
</li>
</ItemTemplate>
</asp:Repeater>
</ul>
</li>
</ul>
</div>
</div>

有问题的查询是我的代码中的第二个查询。它是动态的，它准确地给了我所需要的，但我无法让它工作。我需要一种解决方法(如果有的话(才能使此查询正常工作

C#

protected void Page_Load(object sender, EventArgs e)
{
testConnection();
}
public void testConnection()
{
using (SqlConnection connection = new SqlConnection(masterConnectionString))
{
try
{
connection.Open();
SqlCommand cmd = new SqlCommand("SELECT Name From Sys.Databases", connection);
cmd.CommandType = CommandType.Text;
SqlDataReader rdr = cmd.ExecuteReader();
rpDatabasesParent.DataSource = rdr;
rpDatabasesParent.DataBind();
}
catch (Exception err)
{
//log something
}
finally
{
connection.Close();
}
}
}
protected void loadTables(object sender, RepeaterItemEventArgs e)
{
RepeaterItem item = e.Item;
Repeater rpTablesChild = (Repeater)item.FindControl("rpTablesChild");
using (SqlConnection connection = new SqlConnection(masterConnectionString))
{
try
{
connection.Open();
SqlCommand cmd = new SqlCommand("DECLARE @SERVERNAME SYSNAME = @@SERVERNAME, @DB SYSNAME, @Object SYSNAME, @Str NVARCHAR(4000); DECLARE DBList CURSOR LOCAL FAST_FORWARD FOR SELECT name FROM sys.databases; OPEN DBList; FETCH NEXT FROM DBList INTO @DB; WHILE @@FETCH_STATUS = 0 BEGIN SELECT @Str = 'SELECT name AS Tables FROM '+@DB+'.sys.tables; SELECT TABLE_NAME AS Views FROM '+@DB+'.INFORMATION_SCHEMA.VIEWS; SELECT ROUTINE_NAME AS StoredProcedures FROM '+@DB+'.INFORMATION_SCHEMA.ROUTINES;' EXEC sp_executesql @Str FETCH NEXT FROM DBList INTO @DB; END CLOSE DBList; DEALLOCATE DBList;", connection);
cmd.CommandType = CommandType.Text;
SqlDataReader rdr = cmd.ExecuteReader();
rpTablesChild.DataSource = rdr;
rpTablesChild.DataBind();
}
catch (Exception err)
{
//log something
}
finally
{
connection.Close();
}
}
}

Answer 1

除此之外，我鼓励您考虑将这段代码转换为存储过程，看起来在少数地方缺少空格字符。

f.ex：

@StrFETCH

应该是

@Str FETCH

所以整个固定字符串将是：

DECLARE @SERVERNAME SYSNAME= @@SERVERNAME, @DB SYSNAME, @Object SYSNAME, @Str NVARCHAR(4000); DECLARE DBList CURSOR LOCAL FAST_FORWARD FOR SELECT name FROM sys.databases; OPEN DBList; FETCH NEXT FROM DBList INTO @DB; WHILE @@FETCH_STATUS = 0 BEGIN SELECT @Str = 'SELECT name AS Tables FROM '+@DB+'.sys.tables; SELECT TABLE_NAME AS Views FROM '+@DB+'.INFORMATION_SCHEMA.VIEWS; SELECT ROUTINE_NAME AS StoredProcedures FROM '+@DB+'.INFORMATION_SCHEMA.ROUTINES;' EXEC sp_executesql @Str FETCH NEXT FROM DBList INTO @DB; END CLOSE DBList; DEALLOCATE DBList;

此外，您还需要考虑到此查询将返回多个结果集，因此我不确定使用 DataRead 是否可以帮助您获得结果。

您可以将所有行合并为 1 个结果，如下所示：

CREATE PROCEDURE dbo.GetDbsObjects AS
BEGIN
CREATE TABLE #output (
[Db] SYSNAME,
[Type] VARCHAR(20),
[Name] SYSNAME
)
DECLARE @SERVERNAME SYSNAME= @@SERVERNAME, @DB SYSNAME, @Object SYSNAME, @Str NVARCHAR(4000);
DECLARE DBList CURSOR LOCAL FAST_FORWARD
FOR SELECT name
FROM sys.databases;
OPEN DBList;
FETCH NEXT FROM DBList INTO @DB;
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @Str = 'INSERT INTO #output([Db], [Type], [Name]) SELECT '''+@DB+''', ''table'', name FROM '+@DB+'.sys.tables;'
EXEC sp_executesql @Str
SELECT @Str = 'INSERT INTO #output([Db], [Type], [Name]) SELECT '''+@DB+''', ''view'', TABLE_NAME FROM '+@DB+'.INFORMATION_SCHEMA.VIEWS;'
EXEC sp_executesql @Str
SELECT @Str = 'INSERT INTO #output([Db], [Type], [Name]) SELECT '''+@DB+''', ''stored procedure'', ROUTINE_NAME FROM '+@DB+'.INFORMATION_SCHEMA.ROUTINES;'
EXEC sp_executesql @Str
FETCH NEXT FROM DBList INTO @DB;
END
CLOSE DBList;
DEALLOCATE DBList;
SELECT * FROM #output
DROP TABLE #output
END

然后你应该能够从 c# 运行它

...
connection.Open();
SqlCommand cmd = new SqlCommand("dbo.GetDbsObjects", connection);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataReader rdr = cmd.ExecuteReader();
rpTablesChild.DataSource = rdr;
...

Answer 2

首先，我相信您在查询中有一些错误，您的 BEGINSELECT 应该是 BEGIN SELECT，并且在 WHILE 开始后您不会以 END 关闭。

如果这不能解决您的问题，也许您可以尝试以下解决方法：

使用输入参数在 SQL 服务器管理中创建存储过程。 然后，只需在 C# 代码中使用参数执行该过程。

下面是一个示例：

public DataSet RelaEspec(DateTime dtInicio, DateTime dtFim, string CodNo)
{
string sql = string.Empty;
try
{
sql = "exec store_procedure @dtInicio, @dtFim, @CodNo ";
SqlParameterCollection pCol = new SqlParameterCollection();
pCol.Add("@dtInicio", SqlDbType.DateTime, dtInicio);
pCol.Add("@dtFim", SqlDbType.DateTime, dtFim);
pCol.Add("@CodNo", SqlDbType.VarChar, -1, CodNo);
return ExecuteDataSet(sql, pCol, SessionID, connectionTimeout);
}
catch (Exception ex)
{
logErro(ex, "DataSet RelatorioEspecializacao");
return null;
}
}

创建存储过程：

USE [YOUR DATABASE]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[store_procedure]
@SERVERNAME SYSNAME = @@SERVERNAME
, @DB SYSNAME
, @Object SYSNAME
, @Str NVARCHAR(4000);
AS
BEGIN
DECLARE 
DBList CURSOR LOCAL FAST_FORWARDFOR 
SELECT name FROM sys.databases; 
OPEN DBList; 
FETCH NEXT FROM DBList INTO @DB;
WHILE @@FETCH_STATUS = 0 
BEGIN 
SELECT @Str = 'SELECT name AS Tables FROM '+@DB+'.sys.tables; SELECT 
TABLE_NAME AS Views FROM '+@DB+'.INFORMATION_SCHEMA.VIEWS; SELECT 
ROUTINE_NAME AS StoredProcedures FROM '+@DB+'.INFORMATION_SCHEMA.ROUTINES;' 
EXEC sp_executesql @StrFETCH NEXT FROM DBList INTO @DB; ENDCLOSE DBList; 
END
DEALLOCATE DBList
return 
END
GO