我需要在visualstudio中使用C#代码从表值函数的SQL中获取数据集

Question

1. 我在SQL中有一个表。

2. 然后我创建了一个名为GetTaxDataSet的表值函数。因此，当我使用Where条件执行该函数时，我得到了所需的数据集值。

3. 现在，我正在尝试使用C#代码使用SQL表值函数来获取数据集。

   DataSet ds = new DataSet();
   SqlDataAdapter da = new SqlDataAdapter();
   string sqlSelect = "Select * From [dbo].[fn_GetTaxRuleDataSet] (@TaxID, @TaxRulesID)";
   SqlCommand cmd = new SqlCommand(sqlSelect, Sqlconn);
   SqlParameter param1 = new SqlParameter();
   param1.ParameterName = "@ID";
   param1.SqlDbType = SqlDbType.Int;
   param1.ParameterName = "@TaxID";
   param1.SqlDbType = SqlDbType.Int;
   param1.ParameterName = "TaxRulesID";
   param1.SqlDbType = SqlDbType.Int;
   param1.ParameterName = "@EffectiveDate";
   param1.SqlDbType = SqlDbType.DateTime;
   cmd.Parameters.Add(param1);
   Sqlconn.Open();
   //Executing The SqlCommand.
   SqlDataReader dr = cmd.ExecuteReader();
   Console.ReadLine();    
   

你们能给我推荐正确的代码吗？感谢

Answer 1

希望这个答案能帮助你。请记住，当创建返回类型为DataSet的方法时，还应将其设置为接收DataSet。

public DataSet GetData()
{
DataSet output = new DataSet();
try
{
SqlConnection sqlConn = new SqlConnection(connectionString);
if (sqlConn.State == ConnectionState.Closed)
{
sqlConn.Open();
}
String sqlSelect = "Select * From [dbo].[fn_GetTaxRuleDataSet] (@TaxID, @TaxRulesID)";
SqlCommand cmd = new SqlCommand(sqlSelect, sqlConn);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);

adapter.Fill(output);
sqlConn.Close();
}
catch (Exception ex)
{
ScriptManager.RegisterStartupScript(this, GetType(),
"ServerControlScript", ex.Message, true);
return (null);
}
return output;
}

Answer 2

下面的代码工作正常。这是针对表值函数的。正在尝试从c#检索数据。

public void fn_GetTaxCalculationDataSet(int cur_tax_calc_ruleid({Sqlconn.Open((//连接打开

//Variables to Store Result from DataSet
int TaxCalcRuleID              = cur_tax_calc_ruleid;
int ScheduleTypeID             = 0;
int FilingStatusID             = 0;
int bottomorder                = 0;
decimal bottom                 = 0;
decimal Amount                 = 0;
decimal plusAmountOfPercentage = 0;
try
{
//Defining The Query.
string sqlSelect = "Select * From dbo.[fn_GetTaxCalculationDataSet] (@TaxCalcRuleID)";
SqlCommand cmd = new SqlCommand(sqlSelect, Sqlconn);
cmd.Parameters.Add("@TaxCalcRuleID", SqlDbType.Int).Value = cur_tax_calc_ruleid;
//Executing The SqlCommand
SqlDataReader dr = cmd.ExecuteReader();
Console.WriteLine(Environment.NewLine + "Retrieving CalcData based on TaxRuleID From database..!" + Environment.NewLine);
Console.WriteLine("Retriving The Records");
Console.WriteLine("Press Any Key To Continue.....!");
Console.ReadLine();
//Check if have any records..!
if (dr.HasRows)
{
while (dr.Read())
{
TaxCalcRuleID  = dr.GetInt32(0);
ScheduleTypeID = dr.GetInt32(1);
FilingStatusID = dr.GetInt32(2);
bottomorder    = dr.GetInt32(3);
bottom         = dr.GetDecimal(4);
Amount         = dr.GetDecimal(5);
plusAmountOfPercentage = dr.GetDecimal(6);
//Displaying The Retrieved Records or DataSet..!
Console.WriteLine("{0},{1},{2},{3},{4},{5},{6}", TaxCalcRuleID.ToString(), ScheduleTypeID, FilingStatusID, bottomorder, bottom, Amount, plusAmountOfPercentage);
Console.WriteLine("Press Enter To Close");
Console.ReadLine();
}
}
else
{
Console.WriteLine("No Data Found");
}
//Data ReaderClosed.
dr.Close();
//SQL Connection Closed. 
Sqlconn.Close();
}
catch (Exception ex)
{
Console.WriteLine("Exception: " + ex.Message);
}
}