将存储过程的结果显示到datagridview中 [英] Displaying results from a stored procedure into datagridview
问题描述
有没有可能的方法在不知道参数名称或属性的情况下显示存储过程结果？我正在做一个包含这样一个问题的项目。我需要在datagridview中显示过程的结果。首先，用户选择一个数据库名称，然后从该数据库中选择一个程序并单击一个按钮，说明查看结果我已完成除显示结果的部分之外的所有内容。有没有可能的方法呢？...
如果您需要更多关于我的问题的信息，请询问，我会提供更多信息。
提前致谢...
我使用的是SQL Server 2008，Visual Studio 2010
is there any possible way of displaying a stored procedures results without knowing the parameters names or properties? Im doing a project which contains such a problem. i need to display the results of the procedure in a datagridview. First the user selects a database name, then selects a procedure from that database and clicks a button saying 'view results' i've done all that except the part about displaying the results. is there any possible way to so?...
if you need anymore information about my question, please ask and i will provide more information.
Thanks in advance...
I am using SQL Server 2008, Visual Studio 2010
推荐答案
好吧，如果你使用 SqlCommand [ ^ 等只是绑定生成的 DataTable [ ^ ]到 DataGridView [ ^ ]（将其分配给DataGridView.DataSource属性 [ ^ ]）。
DataGridView应该现在自动填充其列并用结果填充行。
它基本上看起来像是使用SQL Server运行SP。
示例：
Well, if you run the SP using a SqlCommand[^] etc. just bind the resulting DataTable[^] to the DataGridView[^] (assigning it to DataGridView.DataSource Property[^]).
TheDataGridViewshould now automatically populate its columns and fill the rows with the results.
It will basically look like you ran the SP using SQL Server.
Example:
// I am assuming you already got a DataTable, because you said you got the results.
DataTable dt = GetStoredProcResults();
DataGridView1.DataSource = dt;这就是它的全部内容:)
编辑：
以下是如何从存储过程中获取数据的粗略示例：
And that's all there is to it :)
Here is a rough example of how to get the data from a stored procedure:
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
builder.DataSource = "(local)";
builder.InitialCatalog = "Northwind";
builder.IntegratedSecurity = true;
using (SqlConnection conn = new SqlConnection(builder.ToString()))
{
using (SqlCommand cmd = new SqlCommand("Ten Most Expensive Products", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
// Think of a smart way to use any parameters.
// For example create a dictionary of name/value
// pairs and create parameters like this:
// foreach (var pair in dict) {
// cmd.Parameters.AddWithValue(pair.Key, pair.Value); }
using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
{
DataTable dt = new DataTable();
adapter.Fill(dt);
dataGridView1.DataSource = dt;
}
}
}有关详细信息，请参阅以下文章：
SqlConnection [ ^ ]
SqlCommand [ ^ ]
SqlDataAdapter [ ^ ]
请注意，以上所有类都是从接口和基类继承的。 OleDb，Oracle和任何其他数据库的东西都使用这些接口和基类。如果你知道Sql的这些东西，你就知道Oracle，Access等。:)
For more information see the following articles:
SqlConnection[^]
SqlCommand[^]
SqlDataAdapter[^]
Note that all of the above Classes Inherit from Interfaces and base classes. OleDb, Oracle and any other database stuff works using these Interfaces and baseclasses. If you know this stuff for Sql you know it for Oracle, Access, etc. :)
public DataTable Search()
{
DataTable lRowsAffected;
_customQuery = new CustomQuery();
SqlCommand lobjSqlCmd = new SqlCommand();
SqlParameter lobjSqlParam = null;
lobjSqlCmd.CommandText = "USP_TMSSearchStudio";
lobjSqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
lobjSqlParam = new SqlParameter("@Studio",System.Data.SqlDbType.VarChar);
lobjSqlParam.Value = _strStudio;
lobjSqlCmd.Parameters.Add(lobjSqlParam);
lobjSqlParam = new SqlParameter("@Studio2",System.Data.SqlDbType.VarChar);
lobjSqlParam.Value = _strStudio2;
lobjSqlCmd.Parameters.Add(lobjSqlParam);
lobjSqlParam = new SqlParameter("@ErrorDescription",System.Data.SqlDbType.VarChar);
lobjSqlParam.Value = _strErrorDescription;
lobjSqlCmd.Parameters.Add(lobjSqlParam);
lobjSqlParam.Direction = ParameterDirection.Output;
lRowsAffected = _customQuery.ExecuteQuery(lobjSqlCmd);
if (lobjSqlCmd.Parameters["@ErrorDescription"].Value is DBNull)
Error = "";
else
Error = (string)(lobjSqlCmd.Parameters["@ErrorDescription"].Value);
return lRowsAffected;
}
IN CustomQuery.cs你需要写的
IN CustomQuery.cs yOU HAVE TO WRITE
private DataTable _ExecuteQuery_SQL(SqlCommand pSQLCommand)
{
DataTable lDataTable = null;
lDataTable = new DataTable();
pSQLCommand.CommandTimeout = 1200;
SqlDataAdapter lSqlDataAdopter = new SqlDataAdapter(pSQLCommand);
lSqlDataAdopter.Fill(lDataTable);
return lDataTable;
}
在Gridview表单中你必须编写此呼叫和呼叫SP功能
In Gridview Form You Have to Write This Call and Call SP function
DataTable dtsearch = new DataTable();
dtsearch =Search();
if (dtsearch.Rows.Count > 0)
{
pnlStudioInfo.Visible = true;
gdvStudio.DataSource = dtsearch;
ViewState["file"] = dtsearch;
gdvStudio.DataBind();
}
私有DataTable TestingSP（）{
DataTable dt = new DataTable（）;
SqlCommand comando = new SqlCommand（USE DB_NAME EXEC dbo.SP_NAME @ parameter1 = @ par1，@ parameter2 = @ par2，U_STRING_CONNECTION）;
comando.Parameters.Clear（）;
comando.Parameters.AddWithValue（@ par1，TextBox1.Text）;
comando.Parameters.AddWithValue（@ par2，TextBox2.Text）;
SqlDataAdapter adapter = new SqlDataAdapter（comando）;
使用（适配器）;
adapter.Fill（dt）;
结束使用;
返回dt;
}
Private DataTable TestingSP() {
DataTable dt = new DataTable();
SqlCommand comando = new SqlCommand("USE DB_NAME EXEC dbo.SP_NAME @parameter1= @par1 , @parameter2 = @par2", U_STRING_CONNECTION);
comando.Parameters.Clear();
comando.Parameters.AddWithValue("@par1 ", TextBox1.Text);
comando.Parameters.AddWithValue("@par2", TextBox2.Text);
SqlDataAdapter adapter = new SqlDataAdapter(comando);
using (adapter);
adapter.Fill(dt);
End using;
return dt;
}
这篇关于将存储过程的结果显示到datagridview中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
