Click here to Skip to main content
15,937,779 members
Please Sign up or sign in to vote.
3.00/5 (1 vote)
See more:
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
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

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.
 
Share this answer
 
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

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900