none
Insertion in SQL Compact with NText RRS feed

  • Question

  • The Subject :

    Using NTEXT for insertion of Large data with parameterized statement to be used with SqlCeDataAdapter, and bulk operations such as Linq select.

    Can't create commands to use them with flexibility of adding unknown values to our NText field or make it ready for Linq select and adapter's update.

    Tried this at first :

    ... cmdIns.Parameters.Add("@story", SqlDbType.NText, 16, "Story") ...

    Adapter.InsertCommand = cmdIns;

    DataTable dtNew = Ds1Words.Tables["TblProducts"];

    ListProducts.Select(p => dtNew.Rows.Add (p.Product, p.Story)).ToArray();

    Adapter.Update(Ds1Prod, "TblProducts");




    But it's no use, because of the limitation of 16 here.(16 is chose because in SSMS it took it as the default value)

    At first were using EF and it was dealing well with the database, But I am in a situation that couldn't use EF anymore on this task.

    Tried different strategies to define it, but seems better to get an advice about.

    I prefer to have something similar to the piece of code I posted here, cause this way don't need any iteration.

    Other pieces of the code are correct, and just looking to make this piece of code work.

    Any Ideas ?

    thanks in advance



    • Edited by MHM-Mz Monday, July 9, 2012 2:11 PM
    • Moved by Val MazurModerator Friday, July 20, 2012 3:46 PM (From:ADO.NET DataSet)
    Monday, July 9, 2012 9:11 AM

Answers

  • I know that SqlCE doesn't have everything that a full-blown SQL Server does, but I didn't know which things are missing. Anyway, ok then, you can't use varchar(max). =0(

    The following works (I haven't tested with text or ntext, but it worked fine with a varchar(50)):

    // this way, you skip having to use the size parameter
    cmdIns.Parameters.Add("@story", SqlDbType.NText);
    cmdIns.Parameters["@story"].SourceColumn = "Story";


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    • Marked as answer by MHM-Mz Monday, July 9, 2012 7:34 PM
    Monday, July 9, 2012 3:48 PM

All replies

  • The text and ntext datatypes are being deprecated in SQL Server (probably in SqlCE too, but I'm not 100% sure of that). What is taking it's place is varchar(max), nvarchar(max), and varbinary(max). I don't know if those are available in SqlCE, but probably. If you can change your database tables to use one of these, it would be better. (I realize that we don't always have control of the database we have to work with).

    At any rate, I believe that you can make the parameter size a -1 and that should then allow any size. I'm pretty sure that works with the varchar(max) types of data, and I think it also works with text and ntext.

    cmdIns.Parameters.Add("@story", SqlDbType.NText, -1, "Story");


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Monday, July 9, 2012 2:45 PM
  • Hi Ms Bonnie,

    Thanks involving in this,

    In reality no SQL Server CE lacks lots of valuable pieces such as all you have mentioned,

    It doesn't support nvarchar(max) and it is told to use ntext instead,

    And the parameter can't get -1, and doesn't accept not specified size parameter creation !

    for me, In my opinion I have some little options here, just hope may be someone could have known a shorthand on this.

    Monday, July 9, 2012 2:53 PM
  • I know that SqlCE doesn't have everything that a full-blown SQL Server does, but I didn't know which things are missing. Anyway, ok then, you can't use varchar(max). =0(

    The following works (I haven't tested with text or ntext, but it worked fine with a varchar(50)):

    // this way, you skip having to use the size parameter
    cmdIns.Parameters.Add("@story", SqlDbType.NText);
    cmdIns.Parameters["@story"].SourceColumn = "Story";


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    • Marked as answer by MHM-Mz Monday, July 9, 2012 7:34 PM
    Monday, July 9, 2012 3:48 PM
  • Thanks you very much,

    You don't know how much the trick could help me.

    • Edited by MHM-Mz Monday, July 9, 2012 7:35 PM
    Monday, July 9, 2012 6:44 PM
  • You're welcome! I'm glad I could help! =0)

    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Monday, July 9, 2012 7:49 PM