Resource Application block and Transact-Sql

Topics: Development Team Discussion, User Discussion
Nov 18, 2009 at 10:03 PM

Hi,

Do you know like I can extract and insert values to resources table by transact sql? I know do it with c#, providers etc but i want do it using transact sql sometimes.I tried several ways (convert to binary the image fields etc) but I couldnt yet.

Could you help me?

 

Thanks, regards

Coordinator
Nov 20, 2009 at 8:37 PM

Hi,

For BLOB data I now use the new NVARCHAR(MAX) and VARBINARY(MAX) data types with the new .WRITE extension to the UPDATE statement. For new records I use INSERT statements to create a new record without the BLOB data and then use UPDATE statements with the .WRITE extension to write the BLOB data using a buffer. That way you can write as much as you want without breaking any limits.

Cheers...

Stephen Phillips

http://sp.ewdev.com

From: silverius [mailto:notifications@codeplex.com]
Sent: 18 November 2009 22:26
To: Stephen J. Phillips
Subject: Resource Application block and Transact-Sql [entlibcontrib:75531]

From: silverius

Hi,

Do you know like I can extract and insert values to resources table by transact sql? I know do it with c#, providers etc but i want do it using transact sql sometimes.I tried several ways (convert to binary the image fields etc) but I couldnt yet.

Could you help me?

Thanks, regards

Read the full discussion online.

To add a post to this discussion, reply to this email (entlibcontrib@discussions.codeplex.com)

To start a new discussion for this project, email entlibcontrib@discussions.codeplex.com

You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe or change your settings on codePlex.com.

Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at codeplex.com

No virus found in this incoming message.
Checked by AVG - www.avg.com
Version: 9.0.707 / Virus Database: 270.14.71/2510 - Release Date: 11/18/09 07:50:00



This message is intended only for the use of the Addressee and may contain information that is PRIVILEGED and CONFIDENTIAL. If you are not the intended recipient, you are hereby notified that any dissemination of this communication is strictly prohibited. If you have received this communication in error, please erase all copies of the message and its attachments and notify us immediately. Whilst attachments are checked, neither the sender nor e-World Developments Ltd accept any liability in respect of any virus that has not been detected.
Nov 20, 2009 at 9:37 PM

Thanks Stephen. Could you put a only one example please?

 

Thanks again.

Coordinator
Nov 23, 2009 at 8:06 PM

Firstly the data type (New from SQL Server 2005 onwards) is VARBINARY(MAX).  If you are passing this type as a SqlParameter in ADO.NET you would need to declare a SqlDbType.Varbinary with a size of -1

Reading is nothing special a simple SELECT statement will do the trick:

SELECT [Dbo].[File] FROM [Dbo].[Files] WHERE [Guid] = @guid;

For writing, this is where the new syntax comes in.  If you are storing more than 100K then I recommend you buffer the writes. Don't forget that for a new record you have to insert a new record first before you can write the BLOB data. This example uses a Guid (@guid) as a primary key and (@bytes) is your buffer byte[] array:

INSERT INTO [Dbo].[Files] ([Guid]) VALUES (@guid);

UPDATE [Dbo].[Files] SET [File].Write(@bytes, null, 0) WHERE [Guid] = @guid;

The "null" offset parameter tells SQL Server to append the contents of your buffer to the end of the [File] field and thus the length parameter (0 in this case) is ignored.

Cheers...      Steve

Nov 23, 2009 at 8:39 PM

Sorry, I dont understand anything...."file" field..."files" table?

I speak about read and write the table "resources", this table is created from from resource block to store the strings in bdd.This table is named "resources" and the it has a field named "Name" and its type is "Image".

do you understand me?

 

Coordinator
Nov 23, 2009 at 9:24 PM

Sorry, I am with you now.

You are quite right, the Value field is still of type Image and should be Varbinary(MAX), I must change this before releasing the v4.1 version. However, the above example should still work with an Image type. Serialization and Deserialization of objects is done using the SerializationUtility class found in the Resource project. The @bytes data below will be a byte[] array taken from SerializationUtility.ToBytes(object) and broken up into multiples of 8040 byte (recommended size) chunks.

So to write to this Table you would need the following ("Type" is the value object's AssemblyQualifiedName):

INSERT INTO [Dbo].[Resources] (BaseName, Culture, [Name], Type, MimeType, Value, Comment) VALUES (@BaseName, @Culture, @Name, @Type, @MimeType, @value, @Comment);

Now call the following for each buffer of data:

UPDATE [Dbo].[Resources] SET [Value].Write(@bytes, null, 0) WHERE [Name] = @Name AND BaseName = @BaseName AND Culture = @Culture;

Now to extract this value you would use a SELECT statement like:

SELECT [Value] FROM [Dbo].[Resources] WHERE [Name] = @Name AND BaseName = @BaseName AND Culture = @Culture;

then the resulting byte[] array you get from the Value field is passed into SerializationUtility.ToObject(bytes) to convert back to an object.

I hope this helps?

Cheers...   Steve

Nov 23, 2009 at 9:42 PM

Sorry ewdev, I know like I can get the values from my application.........But I would like know How can I read and write it from Transact-Sql.

We have a resouce editor etc etc but sometimes its a lot more pragmatic insert,delete and modify from the Sql console.So, you can do comparations, inserts etc etc.

Then I would like know how can I read and write the table only using SqlConsole.

Now ok?

 

Coordinator
Nov 23, 2009 at 10:26 PM

In that case I don't think you can unless you can type the binary itself in the SqlConsole.  All values have to stored as serialized objects in order for the RAB to be flexible enough to store any type of object.

Cheers...  Steve