对 int 字段的查询查找行，对字符字段的查询不查找行，c# oledb

Question

使用 C# 和 OleDB 连接到 Access 数据库。

我有三个几乎相同的查询字符串：

"select * from stock_head where sh_id = 19;"
"select * from stock_head where sh_lineno = 32059";
"select * from stock_head where sh_ref='#007705';";

第一个和第二个检索行，每种情况下的字段都是整数，最后一个不是，它是一个 char 字段，并且该行确实存在：

ExecuteQuery - select * from stock_head where sh_lineno = 32059
ID 7705, #007705, 32059, NS, 'NO SALE', 04/02/2017 14:29:00
1 row(s) found
ExecuteQuery - select * from stock_head where sh_ref='#007705';
0 row(s) found

通过 C# 和 OleDB 查询字符字段有什么奇怪的吗？ 我尝试使用"like"而不是"="，以及单引号和双引号来分隔值，但都无济于事。

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;
namespace OleDbTest
{
class Program
{
static void Main( string[] args )
{
// Create Profile File object
ProcessEJFile EJP = new ProcessEJFile(
"Provider=Microsoft.ACE.OLEDB.12.0;" +
@"Data Source=E:UserssallywDocumentsBar.accdb;" +
"Persist Security Info=False;");
//// Get last reference from the Stock Header file
//object retVal = EJP.ExecuteScalar(
//    @"SELECT max(stock_head.[sh_ref]) FROM stock_head where sh_ref like ""[#]%"";", null);
//string maxRef = retVal.ToString();
//Console.WriteLine( "maxRef = {0}", maxRef );
// Get the actual row
string query =
// @"select * from stock_head where sh_ref = '{0}';";
//"select * from stock_head where sh_id = 19;";
"select * from stock_head where sh_lineno = 32059";
List<StockHead> shlist = EJP.GetStockHead(query, null );
if ( shlist == null )
{
Console.WriteLine( "shlist is null" );
}
else
{
foreach (StockHead sh in shlist )
{
Console.WriteLine( sh.ToString() );
}
Console.WriteLine( "{0} row(s) found", shlist.Count());
}
query =
// @"select * from stock_head where sh_ref = '{0}';";
"select * from stock_head where sh_ref='#007705';";
List<StockHead> shlist1 = EJP.GetStockHead(query, null );
if ( shlist1 == null )
{
Console.WriteLine( "shlist1 is null" );
}
else
{
foreach ( StockHead sh in shlist1 )
{
Console.WriteLine( sh.ToString() );
}
Console.WriteLine( "{0} row(s) found", shlist1.Count() );
}
Console.ReadLine();
}
}
class ProcessEJFile
{
AccessDatabase Accdb = null;
public ProcessEJFile( string connectionString )
{
Accdb = new AccessDatabase( connectionString );
}
public List<StockHead> GetStockHead( string sql, params object[] args )
{
DataTable t;
Accdb.ExecuteQuery( out t, sql, args );
if ( t != null )
{
List<StockHead> shlist = new List<StockHead>();
foreach ( DataRow r in t.Rows )
{
StockHead sh = new StockHead( r);
shlist.Add( sh );
}
return shlist;
}
else
{
return null;
}
}
// Get a single value - MAX, COUNT etc.
public Object ExecuteScalar( string sql, params object[] args )
{
return Accdb.ExecuteScalar( sql, args );
}
}

class AccessDatabase
{
public OleDbConnection conn = new OleDbConnection();
public AccessDatabase( string connection )
{
conn.ConnectionString = connection;
}
public bool OpenDatabase()
{
try
{
conn.Open();
}
catch ( Exception ex )
{
return false;
}
return true;
}
public void CloseDatabase()
{
if ( conn == null )
return;
conn.Close();
}
public void ExecuteQuery( out DataTable dataTable, string sql, params object[] args )
{
dataTable = new DataTable();
string query;
// Simplified version not validating or cleaning arguments in any way
if ( args == null )
{
query = sql;
}
else
{
query = string.Format( sql, args );
}
Console.WriteLine( "nExecuteQuery - {0}", query );
if ( OpenDatabase() )
{
OleDbCommand command = new OleDbCommand( query, conn );
OleDbDataAdapter adapter = new OleDbDataAdapter( command );
adapter.Fill( dataTable );
}
}
public object ExecuteScalar( string sql, params object[] args )
{
Object returnValue = null;
string query = sql;
if ( OpenDatabase() )
{
OleDbCommand cmd = new OleDbCommand( query, (OleDbConnection)conn);
returnValue = cmd.ExecuteScalar();
}
return returnValue;
}
}
class StockHead
{
public int sh_id;
public string sh_ref;
public int sh_lineno = 0;
public string sh_type;
public string sh_supplier = "";
public DateTime sh_datetime;
public StockHead( DataRow row )
{
this.sh_id = (int)row[ "sh_id" ];
this.sh_ref = (string)row[ "sh_ref" ];
if ( !string.IsNullOrEmpty( row[ "sh_lineno" ].ToString() ) )
{
this.sh_lineno = (int)row[ "sh_lineno" ];
}
this.sh_type = (string)row[ "sh_type" ];
if ( !string.IsNullOrEmpty( row[ "sh_lineno" ].ToString() ) )
{
this.sh_supplier = (string)row[ "sh_supplier" ];
}
this.sh_datetime = (DateTime)row[ "sh_datetime" ];
}
public override string ToString()
{
return String.Format( "ID {0}, {1}, {2}, {3}, '{4}', {5}",
this.sh_id, this.sh_ref, this.sh_lineno, this.sh_type, this.sh_supplier, this.sh_datetime );
}
}

}'

Answer 1

您的查询没有错。此操作失败，因为您尝试在每次执行查询时通过在执行查询中调用 OpenDatabase 来打开与数据库的连接。第二次 OpenDatabase 失败，因为数据库已打开且查询未执行。

您正在捕获错误，但对它无所作为。给出的消息是...

The connection was not closed. The connection's current state is open.

这可以通过单步执行代码轻松找到。你没有在调试中运行它吗？

您可以将其更改为..

public bool OpenDatabase()
{
try
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return false;
}
return true;
}

如果您捕获了一个错误，则对它执行除返回 False 之外的操作