我有一个要转换为 .net 的 VB6 应用程序。我分阶段执行此操作,因此客户端将同时拥有 VB6 和 .net 应用程序。应用程序的一部分将 ADO 2.8 COM 记录集缓存到 SQL Server 中的一个表中,并根据需要检索它们。 .net 应用程序使用相同的持久化记录集。我有 C# 代码,用于检索持久化记录集并将其转换为数据集。我的问题是——我是否以最有效的方式做这件事?
这是我从数据库中检索记录集的代码——
Stream adoStream = null;
SqlParameter cmdParameter;
SqlCommand cmd = null;
SqlDataReader dr = null;
string cmdText;
int bytesReturned;
int chunkSize = 65536;
int offSet = 0;
UnicodeEncoding readBytes;
try
{
cmdParameter = new SqlParameter(parameterName, idParamter);
cmdText = sqlString;
cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandTimeout = 0;
cmd.CommandText = cmdText;
cmd.Connection = this.pbiSQLConnection;
cmd.Parameters.Add(cmdParameter);
dr = cmd.ExecuteReader(CommandBehavior.SequentialAccess);
dr.Read();
if (dr.HasRows)
{
readBytes = new UnicodeEncoding();
byte[] byteChunk = new byte[chunkSize];
adoStream = new Stream();
adoStream.Type = StreamTypeEnum.adTypeText;
adoStream.Open(Type.Missing, ConnectModeEnum.adModeUnknown,
StreamOpenOptionsEnum.adOpenStreamUnspecified, "", "");
do
{
bytesReturned = (int)dr.GetBytes(0, offSet, byteChunk, 0,
chunkSize);
size += bytesReturned;
if (bytesReturned > 0)
{
if (bytesReturned < chunkSize)
{
Array.Resize(ref byteChunk, bytesReturned);
}
adoStream.WriteText(readBytes.GetString(byteChunk),
StreamWriteEnum.stWriteChar);
adoStream.Flush();
}
offSet += bytesReturned;
} while (bytesReturned == chunkSize);
}
}
catch (Exception exLoadResultsFromDB)
{
throw (exLoadResultsFromDB);
}
finally
{
if (dr != null)
{
if (!dr.IsClosed)
{
dr.Close();
}
dr.Dispose();
}
if (cmd != null)
{
cmd.Dispose();
}
}
这是将 ado 流转换为数据集的代码——
adoStream = LoadTextFromDBToADODBStream(resultID, "@result_id",
"some sql statement", ref size);
if (adoStream.Size == 0)
{
success = false;
}
else
{
adoStream.Position = 0;
DataTable table = new DataTable();
Recordset rs = new Recordset();
rs.Open(adoStream, Type.Missing, CursorTypeEnum.adOpenStatic,
LockTypeEnum.adLockBatchOptimistic, -1);
if (adoStream != null)
{
adoStream.Close();
adoStream = null;
}
source.SourceRows = rs.RecordCount;
table.TableName = "Source";
source.Dataset = new DataSet();
source.Dataset.Tables.Add(table);
OleDbDataAdapter adapter = new OleDbDataAdapter();
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
adapter.Fill(source.Dataset.Tables[0], rs);
if (adapter != null)
{
adapter.Dispose();
adapter = null;
}
if (adoStream != null)
{
adoStream.Close();
adoStream = null;
}
if (rs != null)
{
if (rs.State == 1)
{
rs.Close();
}
rs = null;
}
}
谢谢大家
编辑:我添加了一个赏金,看看是否有人可以使代码更高效。
最佳答案
一般来说,您没有充分利用 using 语句并自己处理它。不幸的是,您这样做是错误的,因为如果您有一个 IDisposable 实现,它在调用 Dispose 时抛出异常,则不会发生对 Dispose 的其他调用。如果使用 using 语句,则将调用 IDisposable.Dispose 的所有实现,无论它们如何嵌套。
我们先来看看 LoadTextFromDBToADODBStream。这里的 大规模 问题是您在不应该共享连接时共享连接。您应该为您的操作创建连接,使用它,然后关闭它。这里情况不同。
因此,让我们假设您使用单独的方法创建连接,如下所示:
SqlConnection CreateConnection()
{
// Create the connection here and return it.
return ...;
}
您还需要以下结构来正确管理您的 COM 引用:
struct ComReference<T> : IDisposable where T : class, new()
{
private T reference;
public T Reference { get { return reference; } }
public static ComReference<T> Create()
{
// Create the instance.
ComReference<T> retVal = new ComReference<T>();
// Set the reference.
retVal.reference = new T();
// Return.
return retVal;
}
public ComReference<T> Release()
{
// Create a copy for return.
// Note, this is copied on the stack.
ComReference<T> retVal = this;
// Set this reference to null;
this.reference = null;
// Return the reference.
return retVal;
}
public void Dispose()
{
// If there is a reference, then release.
Marshal.ReleaseComObject(reference);
}
}
你想用它来管理你的 COM 引用,以便在你完成它们时释放它们,而不是通过垃圾收集。 COM 依赖于确定性终结,您不能仅仅因为您在 .NET 中就忽略这一点。上面的结构利用 IDisposable(事实上它是一个结构和随之而来的细微差别)以一种确定性的方式帮助做到这一点。
类型参数
T
将是为 COM 互操作创建的类类型,在流的情况下,它将是 ADODB.StreamClass。你的 LoadTextFromDBToADODBStream 然后看起来像这样:
ComReference<StreamClass> LoadTextFromDBToADODBStream(int idParameter,
string parameterName, string sqlString, ref int size)
{
int bytesReturned;
int chunkSize = 65536;
int offSet = 0;
// Create the command.
using (SqlCommand cmd = new SqlCommand())
{
// Set the parameters.
cmd.CommandType = CommandType.Text;
cmd.CommandTimeout = 0;
cmd.CommandText = sqlString;
// See (1).
using (SqlConnection connection = CreateConnection())
{
// Set the connection on the command.
cmd.Connection = connection;
// Create the parameter and add to the parameters.
SqlParameter cmdParameter = new SqlParameter(
parameterName, idParameter);
cmd.Parameters.Add(cmdParameter);
// Create the reader.
using (SqlDataReader dr = cmd.ExecuteReader(
CommandBehavior.SequentialAccess))
{
dr.Read();
// See (2)
if (!dr.HasRows)
{
// Return an empty instance.
return new ComReference<StreamClass>();
}
// Create the stream here. See (3)
using (ComReference<StreamClass> adoStreamClass =
ComReference<StreamClass>.Create())
{
// Get the stream.
StreamClass adoStream = adoStreamClass.Reference;
// Open the stream.
adoStream.Type = StreamTypeEnum.adTypeText;
adoStream.Open(Type.Missing,
ConnectModeEnum.adModeUnknown,
StreamOpenOptionsEnum.adOpenStreamUnspecified,
"", "");
// Create the byte array.
byte[] byteChunk = new byte[chunkSize];
// See (4)
Encoding readBytes = Encoding.Unicode;
// Cycle.
do
{
bytesReturned = (int)dr.GetBytes(0, offSet,
byteChunk, 0, chunkSize);
size += bytesReturned;
if (bytesReturned > 0)
{
if (bytesReturned < chunkSize)
{
Array.Resize(ref byteChunk,
bytesReturned);
}
adoStream.WriteText(
readBytes.GetString(byteChunk),
StreamWriteEnum.stWriteChar);
adoStream.Flush();
}
offSet += bytesReturned;
} while (bytesReturned == chunkSize);
// Release the reference and return it.
// See (5).
return adoStreamClass.Release();
}
}
}
}
}
笔记:
ComReference<StreamClass>
的新实例。当您创建它时,它会返回一个没有引用的结构(这是您想要的,而不是调用静态 Create 方法)。 ComReference<StreamClass>
。这样,StreamClass 引用仍然有效,并且当在堆栈变量上调用 Dispose 时,它不会将该引用传递给 ReleaseComObject。 继续调用 LoadTextFromDBToADODBStream 的代码:
// See (1)
using (ComReference<StreamClass> adoStreamClass =
LoadTextFromDBToADODBStream(resultID, "@result_id",
"some sql statement", ref size))
{
// Set to the class instance. See (2)
StreamClass adoStream = adoStreamClass.Reference;
if (adoStream.Size == 0)
{
success = false;
}
else
{
adoStream.Position = 0;
DataTable table = new DataTable();
// See (3)
using (ComReference<RecordsetClass> rsClass =
ComReference<RecordsetClass>.Create())
{
Recordset rs = rsClass.Reference;
rs.Open(adoStream, Type.Missing, CursorTypeEnum.adOpenStatic,
LockTypeEnum.adLockBatchOptimistic, -1);
if (adoStream != null)
{
adoStream.Close();
adoStream = null;
}
source.SourceRows = rs.RecordCount;
table.TableName = "Source";
source.Dataset = new DataSet();
source.Dataset.Tables.Add(table);
// See (4)
using (OleDbDataAdapter adapter = new OleDbDataAdapter())
{
adapter.MissingSchemaAction =
MissingSchemaAction.AddWithKey;
adapter.Fill(source.Dataset.Tables[0], rs);
}
}
}
}
adoStreamClass.Reference.<method>
ComReference<RecordsetClass>
。 在更多地使用 using 语句时,您可以清理大量难以阅读的代码。此外,一般来说,您正在清理一些在遇到异常时会出现的资源问题,以及未正确处理的已处理 COM 实现。
关于c# - 将持久的 ADO 2.8 COM 记录集转换为 ADO.Net 数据集,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/409385/