Monday, 28 January 2013

SQL : Inserting multiple rows using onq sql query

Following SQL query is used to insert multiple record into table

INSERT INTO tmpTable (field1, field2,field3) VALUES
 ('74251', 'Black', '511black')
INSERT INTO tmpTable  (field1, field2,field3) VALUES
 ('74251', 'CharcoalF', '5145')
INSERT INTO tmpTable  (field1, field2,field3) VALUES
 ('74251', 'KhakiQ', '5165')
INSERT INTO tmpTable  (field1, field2,field3) VALUES
 ('74251', 'NavyA', '515')
INSERT INTO tmpTable  (field1, field2,field3) VALUES
 ('74251', 'OD', '511')

Converting Stream to String and String to Bytes

C# : Converting Stream to String and  String to Bytes

Following code can be sued for converting stream to string and then stream to bytes.

Stream Stream1 = bodyPart.GetOriginalDataStream();
                            String test = new StreamReader(Stream1).ReadToEnd();                            System.Text.ASCIIEncoding encoding = new System.Text.ASCIIEncoding();
                            Byte[] bytes = encoding.GetBytes(test);

Convert BizTalk MessageBodyPart to String

Get MessageBodyPart as string in Custom Pipeline componenet execute method.

In the follwoing code pInMsg.BodyPart is converted to String using stream. You can edit this messsage steam as per your requirement then assign to new message traceDataRequest.Message. Following code can be used for tracing the request message recived by BizTalk pipeline or to modifiy the request received.

We have used eventlog entry to log the message for tracing.

                           int bufferSize = 0x280; //  TODO move to external config
                            int thresholdSize = 0x100000;   //  TODO move to external config
                            // Stream inboundStream = bodyPart.GetOriginalDataStream();
                            var virtualStream = new VirtualStream(bufferSize, thresholdSize);
                            var readOnlySeekableStream = new ReadOnlySeekableStream(pInMsg.BodyPart.GetOriginalDataStream(), virtualStream, bufferSize);
                            var memoryStream = new MemoryStream();
                            memoryStream.Flush();
                            IBaseMessagePart bodyPart = pInMsg.BodyPart;

                            virtualStream.CopyTo(memoryStream);
                            Stream Stream1 = bodyPart.GetOriginalDataStream();
                            String test = new StreamReader(Stream1).ReadToEnd();                            System.Text.ASCIIEncoding encoding = new System.Text.ASCIIEncoding();
                            Byte[] bytes = encoding.GetBytes(test);
                            Stream1.CopyTo(memoryStream);
                            traceDataRequest.Message = bytes;//memoryStream.ToArray();                            System.Diagnostics.EventLog.WriteEntry("Request message", test);
                            readOnlySeekableStream.Seek(0, SeekOrigin.Begin);


Following are the method which you can directly use in your pipeline component for Getting String data from message and copying the BodyPart to other stream.

Return string message from IBaseMessage

 public string ReadEntireBody(IBaseMessage msg)
        {
            int BufferSize = 0x280; //  TODO move to external config
            int ThresholdSize = 0x100000;   //  TODO move to external config
            string body = null;
            IBaseMessagePart bodyPart = msg.BodyPart;
            Stream inboundStream = bodyPart.GetOriginalDataStream();
            var virtualStream = new VirtualStream(BufferSize, ThresholdSize);
            var readOnlySeekableStream = new ReadOnlySeekableStream(inboundStream, virtualStream,
                                                                    BufferSize);
            var sr = new StreamReader(readOnlySeekableStream);
            body = sr.ReadToEnd();
            return body;
        }


To copy one stream to other Stream

 internal static void CopyDataStream(Stream source, Stream destination)
        {
            byte[] buffer = new byte[2048];
            int length;
            while ((length = source.Read(buffer, 0, 2048)) > 0)
            {
                destination.Write(buffer, 0, length);
            }
            if (!source.CanSeek)
            {
                ReadOnlySeekableStream seekableStream = new ReadOnlySeekableStream(source);
            }
            else
            {
                source.Seek(0, SeekOrigin.Begin);
            }
            destination.Seek(0, SeekOrigin.Begin);
        }

Sunday, 27 January 2013

SQL Query to get random records from table

SQL Query to get random records from table

Following query is used to get random number of records from SQL table


SELECT TOP 5 *
  FROM [JukeBox].[dbo].[tbl_Debate]
 ORDER BY NEWID()


Following query is update with were clause


SELECT TOP 5 *
  FROM [JukeBox].[dbo].[tbl_Debate]
  where ID not in (21,5,6,20,11,12)
 ORDER BY NEWID()

Saturday, 5 January 2013

SQL LEN function and IF ELSE

SQL Query to check length of the field and using IF ELSE

IF
LEN(@UserLike) >0

BEGIN

UPDATE tbl_Kukoo

SET UserLike = @UserLike

where ID=@ID

END


LEN(@UserLike) >0   - to get length of SQL field.

Regular Expression to validate URL


ASP.NET  : Regular expression to validate URL.


Regex reg = new Regex(@"@"^(?:ftp|http|https):\/\/(?:[\w\.\-\+]+:{0,1}[\w\.\-\+]*@)?(?:[a-z0-9\-\.]+)(?::[0-9]+)?(?:\/|\/(?:[\w#!:\.\?\+=&%@!\-\/\(\)]+)|\?(?:[\w#!:\.\?\+=&%@!\-\/\(\)]+))?$");
Response.Write(reg.IsMatch(txtwebsite.Text));

string

regURL = @"^(?:ftp|http|https):\/\/(?:[\w\.\-\+]+:{0,1}[\w\.\-\+]*@)?(?:[a-z0-9\-\.]+)(?::[0-9]+)?(?:\/|\/(?:[\w#!:\.\?\+=&%@!\-\/\(\)]+)|\?(?:[\w#!:\.\?\+=&%@!\-\/\(\)]+))?$";




if (Regex.IsMatch(txtwebsite.Text, regURL))
{
// string match with regExpression.
}
Else
{
//String does not validate
}
 

SQL TABLE ALTER SCRIPT

SQL TABLE ALTER SCRIPT

Following script is used to alter sql table with default value.

-- Adding Value with Default ValueALTER TABLE TableNameADD ColumnName INT NOT NULL DEFAULT(0)GO

Script alter Column type

ALTER TABLE mytable ALTER COLUMN mycolumn newtype

Thursday, 3 January 2013

BizTalk XSD validation error

BizTalk XSD Validation Error

Error :   List of possible element expected : FieldName1
Solution : Cause for this error is that you are not passing FieldName1 value in input instance and it's min Occur is not set to ZERO.

Set the (minOccur =0),  for this field (FieldName1), Now the schema will validate the input instance with no value for this field.