Question

以下是伪代码：

SqlCeResultSet myResultSet = cmd.ExecuteResultSet(Options...etc);
bool found = myResultSet.Seek();
if found {
    //do an Update
    myResultSet.Read() //make current

    //At this point we have a cursor positioned at a row to be edited
    myResultSet.SetString(1, "value for col 1");
    myResultSet.SetString(2, "value for col 2");
    //... etc... 
    myResultSet.SetString(100, "value for col 100");
    //i want to replace above with: CommonMethodToFillRowData(someRow)

    //finally update
    myResultSet.Update();

} else {
    //do an insert
    SqlCeUpdatableRecord myRec = myResultSet.CreateRecord();
    //set primaryKey
    myRec.SetInt32(0, pkValue);

    //At this point we have a cursor positioned at a row to be edited   
    myRec.SetString(1, "value for col 1");
    myRec.SetString(2, "value for col 2");
    //... etc...
    myRec.SetString(100, "value for col 100");
    //i want to replace above with: CommonMethodToFillRowData(someRow)

    //finally insert
    myResultSet.Insert(myRec);
}

根据上面的内容，如果我有100列要准备，它必须重复两次；我想要的是一些CommonMethodToFillRowData（）；但是，对于这样的方法，我应该使用什么类型的参数？

CommonMethodToFillRowData(SqlCeResultSet or SqlCeUpdatableRecord ? parmRow) {
    parmRow.SetInt32(col1, value1)
    parmRow.SetString(col2, value2)
    ...etc.
    parmRow.SetString(100, "value for col 100");
}

Directly quoting from MSDN doco on SqlCeUpdatableRecord Class: --> Represents a row of updatable values from the data source. A SqlCeResultSet object contains one or more UpdatableRecords.

如果是这样的话，为什么一旦我通过Seek（）定位光标，我就不能直接访问SqlCeResultSet中的单个UpdatableRecord？

如果可能的话，我可以使用：

CommonMethodToFillRowData(SqlCeUpdatableRecord parmRow) {
   //end of story
}

Answer 1

创建一个包装对象，该包装对象可以表示SqlCeResultSet或SqlCeUpdatableRecord（视情况而定）。然后写一次“保存”代码，并将其作为更新或插入应用，具体取决于记录是否已经存在。

警告：这是未经测试的代码

public void SavingMyData()
{
    SqlCeResultSet resultSet = cmd.ExecuteResultSet(Options...etc);
    SqlCeWrapper wrapper = new SqlCeWrapper(resultSet);

    wrapper.SetInt32(0, pkValue, true); // Primary Key = true
    wrapper.SetString(1, "value for col 1");
    wrapper.SetString(2, "value for col 2");
    wrapper.SetString(100, "value for col 100");
    wrapper.Commit();
}

...

public class SqlCeWrapper
{
    private readonly bool _found;
    private readonly SqlCeResultSet _resultSet;
    private readonly SqlCeUpdatableRecord _newRecord;

    public SqlCeWrapper(SqlCeResultSet resultSet)
    {
        _resultSet = resultSet;

        _found = resultSet.Seek();
        if (_found)
            resultSet.Read();
        else
            _newRecord = resultSet.CreateRecord();
    }

    public void SetInt32(int ordinal, int value, bool isPrimary = false)
    {
        if (_found && !isPrimary)
            _resultSet.SetInt32(ordinal, value);
        else if (!_found)
            _newRecord.SetInt32(ordinal, value);
    }

    public void SetString(int ordinal, string value, bool isPrimary = false)
    {
        if (_found && !isPrimary)
             _resultSet.SetString(ordinal, value);
        else if (!_found)
            _newRecord.SetString(ordinal, value);
    }

    public void Commit()
    {
        if (_found)
            _resultSet.Update();
        else
            _resultSet.Insert(_newRecord);
    }
}

注意：如果您没有使用.NET4，则必须删除可选参数。您还可以根据需要向SqlCeWrapper添加其他SetX（）方法

Answer 2

怎么样：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlServerCe;
using System.Collections;
using System.Data;

namespace SqlCeRecord_Test
{
    class Program
    {
        static void Main(string[] args)
        {
            // Arguments for update
            int lookFor = 1;
            string value = "AC/DC";

            // Arguments for insert
            lookFor = Int16.MaxValue;
            value = "joedotnet";

            using (SqlCeConnection conn = new SqlCeConnection(@"Data Source=C:UsersxeejDownloadsChinookPart2Chinook.sdf"))
            {
                conn.Open();

                using (SqlCeCommand cmd = new SqlCeCommand("Artist"))
                {
                    SqlCeUpdatableRecord myRec = null;
                    cmd.Connection = conn;
                    cmd.CommandType = System.Data.CommandType.TableDirect;
                    cmd.IndexName = "PK_Artist";
                    SqlCeResultSet myResultSet = cmd.ExecuteResultSet(ResultSetOptions.Updatable | ResultSetOptions.Scrollable);
                    bool found = myResultSet.Seek(DbSeekOptions.FirstEqual, new object[] { lookFor });

                    if (found)
                    {
                        myResultSet.Read();
                    }
                    else
                    {
                        myRec = myResultSet.CreateRecord();
                    }
                    foreach (KeyValuePair<int, object> item in CommonMethodToFillRowData(value))
                    {
                        if (found)
                        {
                            myResultSet.SetValue(item.Key, item.Value);
                        }
                        else
                        {
                            myRec.SetValue(item.Key, item.Value);
                        }
                    }
                    if (found)
                    {
                        myResultSet.Update();
                    }
                    else
                    {
                        myResultSet.Insert(myRec);
                    }
                }
            }
        }

        private static Dictionary<int, object> CommonMethodToFillRowData(string value1) //TODO add more values
        {
            var dict = new Dictionary<int, object>();
            dict.Add(1, value1);
            return dict;
        } 
    }
}

Answer 3

不如在SqlCeResultSet上使用枚举器，而不是像这样的Read方法

IEnumerator enumerator = myResultSet.GetEnumerator();
bool found = enumerator.MoveNext();
SqlCeUpdatableRecord record;
if (found) {
    record = (SqlCeUpdatableRecord)enumerator.Current;
    MethodToFill(record);
    myResultSet.Update();
} else {
    record = myResultSet.CreateRecord();
    MethodToFill(record);
    myResultSet.Insert(record);
}

private void MethodToFill(SqlCeUpdatableRecord recordToFill) {
    recordToFill.SetString(0, "Hello");
    recordToFill.SetString(1, "World");
    // etc
}

Answer 4

我不确定你想做什么，也不确定这些组件的具体情况，所以可能有一种更有效的方法。但据MSDN报道，SqlCeResultSet是SqlCeDataReader，它是DbDataReader，实现IDataRecord…和SqlCeUpdateableRecord还实现了ID数据记录。

那么，这行得通吗？

public static void DoStuff()
{
    SqlCeCommand cmd = new SqlCeCommand();
    SqlCeResultSet myResultSet = cmd.ExecuteResultSet(ResultSetOptions.None);
    var reader = myResultSet.Read();

    bool found = myResultSet.Seek(DbSeekOptions.After);
    if (found)
    {
        myResultSet.Read();
        CommonMethodToFillRowData(myResultSet);
        myResultSet.Update();
    }
    else 
    {
        SqlCeUpdatableRecord myRec = myResultSet.CreateRecord();
        CommonMethodToFillRowData(myRec);
        myResultSet.Insert(myRec);
    }
}

// All the messy Type-wrangling is hidden behind the scenes
public static void CommonMethodToFillRowData(this IDataRecord RowToFill)
{
    RowToFill.SetInt32(1, 42);
    RowToFill.SetString(2, "Foo");
    // etc...
}

// Since SetInt32 seems to do the same thing in either inherited Type
public static void SetInt32(this IDataRecord RowToFill, int Ordinal, int Value)
{
    Type rowType = RowToFill.GetType();
    if (rowType == typeof(SqlCeResultSet))
        ((SqlCeResultSet)RowToFill).SetInt32(Ordinal, Value);
    else if (rowType == typeof(SqlCeUpdatableRecord))
        ((SqlCeUpdatableRecord)RowToFill).SetInt32(Ordinal, Value);
    else
        throw new ArgumentException("Method does not know what to do with Type " + rowType.ToString());
}

// Since SetString seems to do the same thing in either inherited Type
public static void SetString(this IDataRecord RowToFill, int Ordinal, string Value)
{
    Type rowType = RowToFill.GetType();
    if (rowType == typeof(SqlCeResultSet))
        ((SqlCeResultSet)RowToFill).SetString(Ordinal, Value);
    else if (rowType == typeof(SqlCeUpdatableRecord))
        ((SqlCeUpdatableRecord)RowToFill).SetString(Ordinal, Value);
    else
        throw new ArgumentException("Method does not know what to do with Type " + rowType.ToString());
}

这很糟糕，性能也不会很好，但如果你只想把行填充到一段代码中，它可能会达到你想要的效果。

友情链接