转换持续ADO 2.8 COM记录到ADO.Net数据集 [英] Convert persisted ADO 2.8 COM recordset to ADO.Net DataSet

问题描述

我有我转换到.NET VB6的应用程序。我分阶段做这使客户端将同时拥有VB6，并在同一.NET应用程序。该应用程序缓存的一部分ADO在SQL Server 2.8 COM的记录到一个表，并根据需要检索它们。在.NET应用程序使用同一个持续的记录。我有一个检索持久记录并将其转换为一个数据集的C＃代码。我的问题是 - ？我做的最有效的方式。

这是我的代码是从数据库检索记录 -

 流adoStream = NULL; 
的SqlParameter cmdParameter; 
的SqlCommand CMD = NULL; 
 SqlDataReader的博士= NULL; 
 
串声明cmdtext; 
 INT bytesReturned; 
 INT CHUNKSIZE = 65536; 
 INT偏移= 0; 
 
 UnicodeEncoding的ReadBytes; 
 
试
 {
 cmdParameter =新的SqlParameter（参数名称，idParamter）; 
 
声明cmdtext =的SqlString; 
 
 CMD =新的SqlCommand（）; 
 cmd.CommandType = CommandType.Text; 
 cmd.CommandTimeout = 0; 
 cmd.CommandText =声明cmdtext; 
 cmd.Connection = this.pbiSQLConnection; 
 cmd.Parameters.Add（cmdParameter）; 
博士= cmd.ExecuteReader（CommandBehavior.SequentialAccess）; 
 dr.Read（）; 
 
如果（dr.HasRows）
 {
的ReadBytes =新UnicodeEncoding（）; 
字节[] = byteChunk新的字节[CHUNKSIZE] 
 
 adoStream =新流（）; 
 adoStream.Type = StreamTypeEnum.adTypeText; 
 adoStream.Open（Type.Missing，ConnectModeEnum.adModeUnknown，
 StreamOpenOptionsEnum.adOpenStreamUnspecified，，）; 
 
做
 {
 bytesReturned =（INT）dr.GetBytes（0，胶印，byteChunk，0，
 CHUNKSIZE）; 
尺寸+ = bytesReturned; 
如果（bytesReturned大于0）
 {
如果（bytesReturned< CHUNKSIZE）
 {
 Array.Resize（REF byteChunk，bytesReturned）; 
} 
 
 adoStream.WriteText（readBytes.GetString（byteChunk），
 StreamWriteEnum.stWriteChar）; 
 adoStream.Flush（）; 
} 
 
偏差+ = bytesReturned; 
}，而（bytesReturned == CHUNKSIZE）; 
} 
} 
赶上（例外exLoadResultsFromDB）
 {
掷（exLoadResultsFromDB）; 
} 
终于
 {
如果（DR！= NULL）
 {
如果（！dr.IsClosed）
 {
 dr.Close（）; 
} 
 
 dr.Dispose（）; 
} 
 
如果（CMD！= NULL）
 {
 cmd.Dispose（）; 
} 
} 
  

这就是结结实实的流转换为代码数据集 -

  adoStream = LoadTextFromDBToADODBStream（resultID，@result_id，
一些SQL语句，楼盘的大小）; 
如果（adoStream.Size == 0）
 {
成功= FALSE; 
} 
，否则
 {
 adoStream.Position = 0; 
 
 DataTable的表=新的DataTable（）; 
记录RS =新的Recordset（）; 
 rs.Open（adoStream，Type.Missing，CursorTypeEnum.adOpenStatic，
 LockTypeEnum.adLockBatchOptimistic，-1）; 
 
如果（adoStream！= NULL）
 {
 adoStream.Close（）; 
 adoStream = NULL; 
} 
 
 source.SourceRows = rs.RecordCount; 
 table.TableName =源; 
 source.Dataset =新的DataSet（）; 
 source.Dataset.Tables.Add（表）; 
 
 OleDbDataAdapter的适配器=新OleDbDataAdapter的（）; 
 adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey; 
 adapter.Fill（source.Dataset.Tables [0]，RS）; 
 
如果（适配器！= NULL）
 {
 adapter.Dispose（）; 
适配器= NULL; 
} 
 
如果（adoStream！= NULL）
 {
 adoStream.Close（）; 
 adoStream = NULL; 
} 
 
如果（RS！= NULL）
 {
如果（rs.State == 1）
 {
 rs.Close （）; 
} 
 
 RS = NULL; 
} 
} 
  

感谢所有

编辑：我添加了一个赏金，看是否有人能够使代码更高效

解决方案

一般来说。 ，你不采取using语句足够的优势，自己处理这一切。不幸的是，你在做了错误的方式，因为如果你有IDisposable接口的实现会抛出呼叫上的异常处置，其他调用处置不占地方。如果使用using语句，IDisposable.Dispose的所有实现将被调用，不管他们是如何嵌套的。

让我们通过LoadTextFromDBToADODBStream第一。在庞大这里的问题是，您共享的时候，你不应该是一个连接。你应该创建为您的操作的连接，使用它，然后关闭它。 ，这是不是这里的情况。

因此，让我们假设你创建一个单独的方法，您的连接，如：

 的SqlConnection创建连接（）
 {
 //这里创建连接并返回它。 
返回...; 
} 
  

您也将需要以下结构妥善管理您的COM引用：

 结构ComReference< T> ：IDisposable的其中T：类，新的（）
 {
私人T参考; 
 
公共T来源{{返回参考; }} 
 
公共静态ComReference< T>创建（）
 {
 //创建实例。 
 ComReference< T> retVal的=新ComReference< T>（）; 
 
 //设置参考。 
 retVal.reference =新T（）; 
 
 //返回。 
返回retVal的; 
} 
 
公共ComReference< T>发行（）
 {
 //退货创建一个副本。 
 //注意，这是复制的堆栈。 
 ComReference< T> retVal的这一=; 
 
 //设置这个基准为null; 
 this.reference = NULL; 
 
 //返回引用。 
返回retVal的; 
} 
 
公共无效的Dispose（）
 {
 //如果有一个参照，然后松开。 
对Marshal.ReleaseComObject（参考）; 
} 
} 
  

您想这一点，以便管理您的COM引用你释放他们，当你与他们做的，而不是通过垃圾收集。 COM依赖于确定性终止了，你不要不理，只是因为你是在.NET。上述结构利用了IDisposable（而事实上它是一个结构和随之而来的细微差别），以帮助确定的方式这样做。

类型参数 T 将是为COM互操作创建，在数据流的情况下，类的类型，这将是ADODB.StreamClass。

您LoadTextFromDBToADODBStream则是这样的：

  ComReference< StreamClass> LoadTextFromDBToADODBStream（INT idParameter，
字符串参数名称，字符串的SqlString，文献INT大小）
 {
 INT bytesReturned; 
 INT CHUNKSIZE = 65536; 
 INT偏移= 0; 
 
 //创建命令。使用
（CMD的SqlCommand =新的SqlCommand（））
 {
 //设置参数。 
 cmd.CommandType = CommandType.Text; 
 cmd.CommandTimeout = 0; 
 cmd.CommandText =的SqlString; 
 
 //见（1）。 
使用（SqlConnection的连接=创建连接（））
 {
 //设置命令连接。 
 cmd.Connection =连接; 
 
 //创建参数和添加的参数。 
的SqlParameter cmdParameter =新的SqlParameter（
参数名称，idParameter）; 
 cmd.Parameters.Add（cmdParameter）; 
 
 //创建读者。 
使用（SqlDataReader的博士= cmd.ExecuteReader（
 CommandBehavior.SequentialAccess））
 {
 dr.Read（）; 
 
 //见（2）
如果（！dr.HasRows）
 {
 //返回一个空的实例。 
返回新ComReference< StreamClass>（）; 
} 
 
 //这里创建流。请参阅使用（3）
（ComReference其中p StreamClass> adoStreamClass = 
 ComReference< StreamClass> .Create（））
 {
 //获取流。 
 StreamClass adoStream = adoStreamClass.Reference; 
 
 //打开流。 
 adoStream.Type = StreamTypeEnum.adTypeText; 
 adoStream.Open（Type.Missing，
 ConnectModeEnum.adModeUnknown，
 StreamOpenOptionsEnum.adOpenStreamUnspecified，
，）; 
 
 //创建字节数组。 
字节[] = byteChunk新的字节[CHUNKSIZE] 
 
 //见（4）
编码的ReadBytes = Encoding.Unicode; 
 
 //循环。 
做
 {
 bytesReturned =（INT）dr.GetBytes（0，胶印，
 byteChunk，0，CHUNKSIZE）; 
尺寸+ = bytesReturned; 
如果（bytesReturned大于0）
 {
如果（bytesReturned&下; CHUNKSIZE）
 {
 Array.Resize（参考byteChunk，
 bytesReturned）; 
} 
 
 adoStream.WriteText（
 readBytes.GetString（byteChunk），
 StreamWriteEnum.stWriteChar）; 
 adoStream.Flush（）; 
} 
 
偏差+ = bytesReturned; 
}，而（bytesReturned == CHUNKSIZE）; 
 
 //释放参考，并返回。 
 //见（5）。 
返回adoStreamClass.Release（）; 
} 
} 
} 
} 
} 
  

注：

1. 这是你要创建的连接。你想，因为你要放心，您的资源被清理，成功或失败，使用它在using语句。


2. 它更容易短路代码在这里，并返回一个StreamClass> ComReference<的新实例。当你创建这个，它返回一个没有参考的结构（这是你想要的，而不是调用静态创建方法）的效果。


3. 在调用静态创建方法，正在创建的ADODB.StreamClass的新实例。你要确保，如果出了问题，这是一经推出处置（因为它是一个COM接口的实现，并依赖于确定性终止）。


4. 有没有必要建立新UnicodeEncoding。你可以只使用Unicode财产上的编码类使用预制的实例。


5. 在调用版本中，设置参考字段设置为null上是当前栈上的实例，它转移到 ComReference< StreamClass> 时返回。通过这种方式，StreamClass参考仍然活着，并且当处置上调用堆栈变量，它不会传递参照ReleaseComObject的





移动到该呼叫LoadTextFromDBToADODBStream代码：

  //使用（ComReference<见（1）
; StreamClass> adoStreamClass = 
 LoadTextFromDBToADODBStream（resultID，@result_id，
一些SQL语句，楼盘的大小））
 {
 //设置为类实例。见（2）
 StreamClass adoStream = adoStreamClass.Reference; 
 
如果（adoStream.Size == 0）
 {
成功= FALSE; 
} 
，否则
 {
 adoStream.Position = 0; 
 
 DataTable的表=新的DataTable（）;使用
 
 //见（3）
（; RecordsetClass> ComReference< rsClass = 
 ComReference< RecordsetClass> .Create（））
 {
记录RS = rsClass.Reference; 
 rs.Open（adoStream，Type.Missing，CursorTypeEnum.adOpenStatic，
 LockTypeEnum.adLockBatchOptimistic，-1）; 
 
如果（adoStream！= NULL）
 {
 adoStream.Close（）; 
 adoStream = NULL; 
} 
 
 source.SourceRows = rs.RecordCount; 
 table.TableName =源; 
 source.Dataset =新的DataSet（）; 
 source.Dataset.Tables.Add（表）; 
 
 //使用（OleDbDataAdapter的适配器=新OleDbDataAdapter的（））
 {
 adapter.MissingSchemaAction = 
 MissingSchemaAction.AddWithKey见（4）
; 
 adapter.Fill（source.Dataset.Tables [0]，RS）; 
} 
} 
} 
} 
  







1. 这是要接收呼叫LoadTextFromDBToADODBStream到发布的返回值。它将包含活参考创建那里的ADODB.Stream和using语句将保证它清理时，范围离开了。


2. 和以前一样，这使得它更容易引用直接引用，而不是总是要打电话， adoStreamClass.Reference<方法>


3. 使用另一个ComReference，此时 ComReference< RecordsetClass方式>


4. 让编译器做肮脏的工作，为您






在使用using语句越多，你可以清理大量被使它非常难读的代码。另外，在一般情况下，你被清理，将在面部异常的，以及处理COM实现了未布置的正确也出现了一些资源问题。




I have a VB6 application that I am converting to .net. I am doing this in phases so clients will have both VB6 and .net applications at the same. Part of the application caches ADO 2.8 COM recordsets to a table in SQL Server and retrieves them as needed. The .net application uses that same persisted recordsets. I have c# code that retrieves the persisted recordset and converts it to a dataset. My question is -- Am I doing it in the most efficient manner?

This is my code that retrieves the recordset from the database --

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();
    }
}

This is the code that converts the ado stream to a datasets --

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;
    }
}

Thanks all

EDIT: I added a bounty to see if anyone can make the code more efficient.

解决方案

Generally speaking, you aren't taking enough advantage of the using statement and handling it all yourself. Unfortunately, you are doing it the wrong way, in that if you have an implementation of IDisposable which throws an exception on the call to Dispose, the other calls to Dispose do not take place. If you use the using statement, all implementations of IDisposable.Dispose will be called, no matter how nested they are.

Let's go through the LoadTextFromDBToADODBStream first. The massive issue here is that you are sharing a connection when you shouldn't be. You should be creating the connection for your operation, using it, then closing it down. That is not the case here.

So let's assume you create your connection in a separate method, like this:

SqlConnection CreateConnection()
{
    // Create the connection here and return it.
    return ...;
}

You are also going to need the following structure to properly manage your COM references:

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);
    }
}

You want to manage your COM references with this so that you release them when you are done with them, not through garbage collection. COM relies on deterministic finalization, and you don't get to ignore that just because you are in .NET. The structure above leverages IDisposable (and the fact it is a structure and the nuances that come with it) to help do so in a deterministic way.

The type parameter T will be the class type that is created for COM interop, in the case of the stream, it will be ADODB.StreamClass.

Your LoadTextFromDBToADODBStream then looks like this:

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();
                }
            }
        }
    }
}

Notes:

1. This is where you want to create your connection. You want to use it in a using statement because you want to be assured that your resources are cleaned up, on success or failure.
2. It's easier to short-circuit the code here and return a new instance of ComReference<StreamClass>. When you create this, it has the effect of returning a structure which has no reference (which is what you want, as opposed to calling the static Create method).
3. In calling the static Create method, you are creating the new instance of the ADODB.StreamClass. You want to make sure that if something goes wrong, this is disposed of upon release (since it is a COM interface implementation, and dependent on deterministic finalization).
4. There is no need to create a new UnicodeEncoding. You can just use the Unicode property on the Encoding class to use a premade instance.
5. In calling release, you set the reference field to null on the instance that is on the current stack, and transfer it to the ComReference<StreamClass> that is returned. This way, the StreamClass reference is still alive, and when Dispose is called on the stack variable, it doesn't pass that reference to ReleaseComObject.

Moving on to the code that calls 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);
            }
        }
    }
}

1. This is going to receive the return value of the call to Release in LoadTextFromDBToADODBStream. It will contain the live reference to the ADODB.Stream created there, and the using statement will guarantee it is cleaned up when the scope is left.
2. As before, this makes it easier to reference the direct reference, instead of always having to call adoStreamClass.Reference.<method>
3. Using another ComReference, this time ComReference<RecordsetClass>.
4. Let the compiler do the dirty work for you.

In using the using statement more, you can clean up a lot of the code that was making it very difficult to read. Also, in general, you were cleaning up some resource issues that would have cropped up in the face of exception, as well as handled COM implementations that were not being disposed of correctly.

这篇关于转换持续ADO 2.8 COM记录到ADO.Net数据集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！