我编写了一些代码来建立与 SQL Server 的连接,然后执行 select 过程以从 SQL Server 中的数据表中获取所有数据,但它在声明新的 SqlDataAdapter 的命令中抛出 InvalidOperationException,请帮助我解决此问题错误。
public class dBConnect
{
//create SQLconnection variable
private SqlConnection con;
//create default constructor that passing a string to con
public dBConnect()
{
try
{
con = new SqlConnection(@"Server=Trump\SQLEXPRESS;
Database=Demo;User Id=sa;Password = stevejobs;");
}
catch(Exception exCon)
{
Console.WriteLine("Unable to connect to database: {0}", exCon);
}
}
//create Select method to Pour the data into the DataTable
public DataTable SelectAll(string procName, SqlParameter[] para = null)
{
//create a DataTable to store Data from DB
DataTable dt = new DataTable();
//create SQLCommand
SqlCommand cmd = new SqlCommand(procName, con);
//declare that cmdType is sp
cmd.CommandType = CommandType.StoredProcedure;
//input parameter of cmd
if (para != null)
cmd.Parameters.AddRange(para);
//create dataAdapter object
//InvalidOperationException was thrown at here
SqlDataAdapter da = new SqlDataAdapter(cmd);
//declare that cmd is select command of da
da.SelectCommand = cmd;
//use try/catch/finally to establish a connection
try
{
con.Open();
da.Fill(dt);
}
catch(Exception sqlEx)
{
Console.WriteLine(@":Unable to establish a connection: {0}", sqlEx);
}
finally
{
con.Close();
con.Dispose();
}
return dt;
}
}
}
你应该:
cmd.CommandText = "your Stored Procedure here.";
cmd.CommandType = CommandType.StoredProcedure;
//input parameter of cmd
if (para != null)
cmd.Parameters.AddRange(para);
//create dataAdapter object
把你的con.Open();
above
or
看看这个步骤
DataTable dt = new DataTable();
using (SqlConnection con = new SqlConnection(connectionString))
{
con.Open();
using (SqlCommand cmd = con.CreateCommand())
{
//sample stored procedure with parameter:
// "exec yourstoredProcedureName '" + param1+ "','" + param2+ "'";
cmd.CommandText = "Your Stored Procedure Here";
cmd.CommandType =CommandType.StoredProcedure;
using (SqlDataAdapter adp = new SqlDataAdapter(cmd))
{
adp.Fill(dt);
return dt;
}
}
}
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)