Update a certain part of a long string stored in MSSQL database ntext or nvarchar column

Question

3.00/5 (1 vote)

See more:

VS2012

This question maybe related to my previous question
How to I update a certain part of a long string using a tag within that string[^]
which was brilliantly solved by OriginalGriff, though this one I would be placing the same format of string in a table within a MsSql Database.

So here's my case, I have this server-client application that I need to make CRUD utility for, it uses MSSQL database and has a main table that contains a ntext column, now this application reads the ntext column most of the time and barely uses the other fields, but the contents of the ntext column came from the fields next to it. For instance a table that has six fields pkfield, field1, field2, field3, field4, and field5 where the contents of field5 will be something like:

[pkfield:"value",field1:"value",field3:null,fied4:"value"]

now if i need to update either field1,2,or 3 i also need to update their value in field5

i can do this with the RegEx solution, also via raw script in mssql that looks like this

SQL

update table1 set
field5 = replace( convert(varchar(5000), field5), 'field5:"value"', 'field5:"newvalue that can be very long string"' )
where pkfield='sdfajklfsdjfl'

what i need to know is if there are other simpler method for an update, by the way I'm using entity framework to connect to my database.

thanks in advance

Posted 27-Apr-14 15:49pm

phyxian

Add a Solution

Comments

Nayan Ambaliya 27-Apr-14 22:30pm

I would recommend to update the required field (regular SQL update statement) and at the same time, construct the field 5 and update it as well. All other methods like string replace, etc on either the front end side or SQL side would consume more resources than a regular update. Its just my opinion.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

DamithSL · Accepted Answer · 2014-04-27T16:39:00

Solution 1

since field5 value can generate from the values of other column values, I think you don't need to store field5 in your database.

for example

C#

string field5 = string.Format("[pkfield:'{0}',field1:'{1}',field3:'{2}',fied4:'{3}']", pkfield,field1,field3, fied4);

will generate the string you need as field5.

Then you don't need to to complex regex or string manipulation for updating. You only need to set value of one of the column with where condition.

Posted 27-Apr-14 16:39pm

DamithSL

Comments

phyxian 27-Apr-14 23:48pm

will that method not consume so much time to run? because what you suggest is just like what @nayan ambaliya said in his comment, update a field reconstruct field5 then update field field5

DamithSL 28-Apr-14 0:30am

run what? insert and update will definitely fast with this way. select also will not be much different. You can build field 5 value on display purpose when you want in UI side.

phyxian 28-Apr-14 0:43am

i see, but i really can't remove the text field from database since that's where the main application read its data, the utility i'm making is just for updating records that are found to have clerical errors. I'm trying out your solution now :)

phyxian 28-Apr-14 1:05am

i wonder, on this part of your code
string.Format("[pkfield:'{0}',field1:'{1}',field3:'{2}',fied4:'{3}']", pkfield,field1,field3, fied4)

is it possible for me to use the xml schema of the table? since the real table has around 65 columnms