如何从SQL命令中获取多个变量,在c#中使用ExecuteScalar将它们设置为本地变量

本文关键字:变量 ExecuteScalar 设置 命令 SQL 获取 | 更新日期: 2023-09-27 18:04:32

使用此代码,我从MS Access 2013数据库中获取变量并将其存储到本地变量中:

public static int UpdateDBtCowsCalculatedVariable()
{
    string StrCon = System.Configuration.ConfigurationManager.ConnectionStrings["FeedLibraryConnectionString"].ConnectionString;
    OleDbConnection Connection = new OleDbConnection(StrCon);
    OleDbCommand Cmd = new OleDbCommand();
    Cmd.Connection = Connection;
    Cmd.CommandText = "Select BW from tCows";
    Connection.Open();
    int BW = (int)Cmd.ExecuteScalar();
    Connection.Close();
        return BW;         
}

但是多变量呢?我该怎么做呢?

我的意思是这样的:

public static void UpdateDBtCowsCalculatedVariable()
{
    //int BW, DaysInMilk, AnimalType;
    string StrCon = System.Configuration.ConfigurationManager.ConnectionStrings["FeedLibraryConnectionString"].ConnectionString;
    OleDbConnection Connection = new OleDbConnection(StrCon);
    OleDbCommand Cmd = new OleDbCommand();
    Cmd.Connection = Connection;
    Cmd.CommandText = "Select BW, DaysInMilk, AnimalType from tCows";
    Connection.Open();
    int BW = (int)Cmd.ExecuteScalar();
    // int DaysInMilk = (int)Cmd.ExecuteScalar();
    // int AnimalType = (int)Cmd.ExecuteScalar();
    Connection.Close();
}

如何从SQL命令中获取多个变量,在c#中使用ExecuteScalar将它们设置为本地变量

为什么不是ExecuteReader() ?即使从技术上讲,您只有一条记录:

public static void UpdateDBtCowsCalculatedVariable()
{
    string StrCon = ...
    int BW = -1; 
    int DaysInMilk = -1;
    int AnimalType = -1;
    using (OleDbConnection Connection = new OleDbConnection(StrCon)) 
    {
        Connection.Open();
        using (OleDbCommand Cmd = new OleDbCommand())
        {
            Cmd.Connection = Connection;
            Cmd.CommandText = 
              @"select BW, 
                       DaysInMilk, 
                       AnimalType 
                  from tCows";
            using (var reader = Cmd.ExecuteReader())
            {
                if (reader.Read()) 
                {
                    BW =         Convert.ToInt32(reader.GetValue(0));
                    DaysInMilk = Convert.ToInt32(reader.GetValue(1));
                    AnimalType = Convert.ToInt32(reader.GetValue(2));   
                }
            }
        }
    }
}

试试这个:

public static void UpdateDBtCowsCalculatedVariable() {
//int BW, DaysInMilk, AnimalType;
string StrCon = System.Configuration.ConfigurationManager.ConnectionStrings["FeedLibraryConnectionString"].ConnectionString;
OleDbConnection Connection = new OleDbConnection(StrCon);
OleDbCommand Cmd = new OleDbCommand();
Cmd.Connection = Connection;
Cmd.CommandText = "Select BW, DaysInMilk, AnimalType from tCows";
Connection.Open();
reader = Cmd.ExecuteReader();
while (reader.Read())
{
  int BW = reader.GetValue(0);
  int DaysInMilk =  reader.GetValue(1);
  int AnimalType =  reader.GetValue(2);
}
reader.Close();
Connection.Close();
}