从存储过程获取记录时出错

Question

我有代码从存储过程中获取数据。

我只想从存储过程中记录。但是现在我在带有 DataAccess 代码的课堂上遇到此代码错误：

SelectCommand.Connection property has not been initialized. fill data adapter

我只想用分页跟踪所有行：

namespace SpidiWeb.App_Code.Helper
{
public class DataAccess
{
public string mstr_ConnectionString;
public SqlConnection mobj_SqlConnection;
public SqlCommand mobj_SqlCommand;
public int mint_CommandTimeout = 30;
public void GetConnection(string cnn_type, string dbname)
{
try
{
if (cnn_type == "W")
{
string Cnn_Str = "";
//string ServerName = "SHREE-PC";
//string DBUserName = string.Empty;
//string DBPassword = string.Empty;
string ServerName = ConfigurationManager.AppSettings["SERVER"];
string DBUserName = ConfigurationManager.AppSettings["UID"] ?? string.Empty;
string DBPassword = ConfigurationManager.AppSettings["PASSWORD"] ?? string.Empty;
string Database = dbname;
DBPassword += "c#" + Convert.ToChar(49);
//Cnn_Str = "Server=" + ServerName + ";User Id=" + DBUserName + ";Password=" + DBPassword + ";Initial Catalog=" + Database;
Cnn_Str = "Server=" + ServerName + ";Initial Catalog=" + Database +";Integrated Security=True";
mstr_ConnectionString = Cnn_Str;
}
else if (cnn_type == "S")
{
string Cnn_Str = "";
//string ServerName = "SHREE-PC";
//string DBUserName = string.Empty;
//string DBPassword = string.Empty;
string ServerName = ConfigurationManager.AppSettings["SERVER"];
string DBUserName = ConfigurationManager.AppSettings["UID"] ?? string.Empty;
string DBPassword = ConfigurationManager.AppSettings["PASSWORD"] ?? string.Empty;
string Database = "NEWSMSLOG_" + dbname;
DBPassword += "c#" + Convert.ToChar(49);
//Cnn_Str = "Server=" + ServerName + ";User Id=" + DBUserName + ";Password=" + DBPassword + ";Initial Catalog=" + Database;
Cnn_Str = "Server=" + ServerName + ";Initial Catalog=" + Database + ";Integrated Security=True";
mstr_ConnectionString = Cnn_Str;
}
mobj_SqlConnection = new SqlConnection(mstr_ConnectionString);
mobj_SqlCommand = new SqlCommand();
mobj_SqlCommand.CommandTimeout = mint_CommandTimeout;
mobj_SqlCommand.CommandType = CommandType.StoredProcedure;
mobj_SqlCommand.Connection = mobj_SqlConnection;
mobj_SqlConnection.Open();
}
catch (Exception ex)
{
throw new Exception("Error initializing data class." + Environment.NewLine + ex.Message);
}

这段代码来自我的页面：

DataAccess obj_con = new DataAccess();    
protected void Get_Data()
{
try
{
DataTable dt = new DataTable();
int intUserId = ddl_users.SelectedItem.Text.ToString() == "All" ? 0 : Convert.ToInt32(ddl_users.SelectedValue.ToString());
string strCampaignName = string.IsNullOrEmpty(txt_CompaignName.Text.Trim()) ? string.Empty : txt_CompaignName.Text.Trim();
string mobileNo = string.IsNullOrEmpty(txt_mobileno.Text.Trim()) ? string.Empty : txt_mobileno.Text.Trim();
int intSenderId = ddl_sender.SelectedItem.Text.ToString() == "All" ? 0 : Convert.ToInt32(ddl_sender.SelectedValue.ToString());
string strDeliveryId = ddl_delevery.SelectedItem.Text.ToString() == "All" ? "-1" : ddl_delevery.SelectedItem.Text.ToString();
using (var con = obj_con.mobj_SqlConnection)
{
//if (con.State == ConnectionState.Closed)
//    con.Open();
var cmd = new SqlCommand();
cmd.Parameters.AddWithValue("intUserID", intUserId);
cmd.Parameters.AddWithValue("strCampaignName", strCampaignName);
cmd.Parameters.AddWithValue("strMobileNo", mobileNo);
cmd.Parameters.AddWithValue("intSenderId", intSenderId);
cmd.Parameters.AddWithValue("strDeliveryId", strDeliveryId);
if (ddl_account.SelectedItem.Text.ToString() == "All")
{
cmd.Parameters.AddWithValue("intAccType", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("intAccType", Convert.ToInt32(ddl_account.SelectedValue.ToString()));
}
if (chk_date.Checked == true)
{
DateTime dateValue = Convert.ToDateTime(CommonLogic.Get_Date_From_String(txt_date_from.Text.Trim(), 1));
cmd.Parameters.AddWithValue("strdbNM ", dateValue.ToString("yyyy_MM"));
}
else
{
cmd.Parameters.AddWithValue("strdbNM ", DateTime.Now.Date.ToString("yyyy_MM"));
}
if (!string.IsNullOrEmpty(txt_date_from.Text.Trim()))
{
DateTime date = Convert.ToDateTime(txt_date_from.Text.Trim());
cmd.Parameters.AddWithValue("strTBNM", date.ToString("ddMMyy"));
}
else
{
cmd.Parameters.AddWithValue("strTBNM", DateTime.Now.Date.ToString("ddMMyy"));
}
cmd.Parameters.AddWithValue("PageIndex", 1);
cmd.Parameters.AddWithValue("PageSize", 1000);
cmd.Parameters.Add("RecordCount", SqlDbType.Int).Direction = ParameterDirection.Output;
cmd.Connection = con;
cmd.CommandText = "dbo.Report_ViewCombineReport";
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter ad = new SqlDataAdapter(cmd);
ad.SelectCommand = cmd;
ad.Fill(dt); // Error Occurs here Fill: SelectCommand.Connection property has not been initialized.
egrd.DataSource = dt;
egrd.DataBind();
}
}
catch (Exception ex)
{
CommonLogic.SendMailOnError(ex);
}
}

Answer 1

您没有将连接传递给 SqlCommand 对象，而是在参数中传递它，如下所示

var cmd = new SqlCommand("Your query", con);

或者您也可以将其设置为

cmd.Connection = con;