Rapid solution in Programming and Related Bug

Tuesday, November 1, 2011

Programing Solutions on OOPS-Concept: Asp.net:Multiple insertion of record into a SQL Se...

Programing Solutions on OOPS-Concept: Asp.net:Multiple insertion of record into a SQL Se...: Every time we face problem bulk insertion through store procedure to perform the following task 1) Multiple insert in table 2) ...

Monday, September 12, 2011

Common Problems while Consuming Web Services over SSL in .NET-Resolved

To consume https enabled web service on test environment, a self-signed certificate (test certificate) is being used.

Check this link on this blog to install test certificate on your web server (IIS).

http://rash-solution.blogspot.com/2011/09/installing-ssl-on-localhostsetting-up.html?spref=bl

When we try to access an https/SSL enabled web service from our C# code, the following error comes.

The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel

This unhandled exception basically generated by the 'System.Net.WebException' as because of that we do not any pop dialog box in code asking to trust the certificate like following.

To resolve the above said problem, we have to write the following code above the method call of web service to Trust the certificate programmatically.

using System.Net;

using System.Security.Cryptography.X509Certificates;

using System.Net.Security;

ServicePointManager.ServerCertificateValidationCallback =

delegate(Object obj, X509Certificate certificate, X509Chain chain, SslPolicyErrors errors)

{

return (true);

};

Here your method call goes on

Happy programming

Installing SSL on localhost/Setting up SSL with a SelfSSL certificate on Windows Xp/2003 IIS

This article will demonstrate how to install SelfSSL from the IIS Resource Kit. You can download the Internet Information Services (IIS) 6.0 Resource Kit Tools from the following link.

http://www.microsoft.com/downloads/details.aspx?FamilyID=56fc92ee-a71a-4c73-b628-ade629c89499&displaylang=en

Run iis60rkt.exe. You will see the welcome screen - click Next

In the next dialog, read over the EULA and select "I agree" and press Next. In the next dialog, you can usually just press "Next" because your information is usually entered already.
Now in this next dialog, select "Complete" and press "Next"

Now you will be presented with an overview, you can click "Next" and the install will copy the selected files. When that's done, click "Finish"

Now we will create a certificate. Click Start -> All Programs -> IIS Resources -> SelfSSL -> SelfSSL

Type "selfssl /T", without the quotes and press "y" when prompted.

If you type "selfssl /T /N:CN=<computer or domain name>" then you will only be prompted once in Internet Explorer to accept the certificate

Now open this link "https://localhost" in Internet Explorer, and click "Yes" to view a secure site.

Hope you find this article useful to you

You can Create The Self-Signed Certificate Using OpenSSL too

Note:- this is for IIS6.0 for IIS 7.0 and above follow these links

http://weblogs.asp.net/scottgu/archive/2007/04/06/tip-trick-enabling-ssl-on-iis7-using-self-signed-certificates.aspx

http://learn.iis.net/page.aspx/144/how-to-set-up-ssl-on-iis-7/

http://www.globalsign.com/support/install/install_iis7.php

Wednesday, September 7, 2011

Rapid solution in Programming and Related Bug: How to pass XML/Datatable/Dataset in Store Procedu...

Rapid solution in Programming and Related Bug: How to pass XML/Datatable/Dataset in Store Procedu...: Every time we face problem of passing multiple value in store procedure to perform the following task 1) Multiple insert in table 2)...

How to pass XML/Datatable/Dataset in Store Procedure (SP) from C# from code behind in ASP.net

Every time we face problem of passing multiple value in store procedure to perform the following task

1) Multiple insert in table

2) Select multiple rows using in-clause at where condition

3) Delete multiple rows

Here I am writing some very good code to pass multiple value in store procedure using xml string which is much faster and we can pass a large value having multiple rows and column some time using Gridview and other similar control.

Convert Datatable/Dataset to XML string

public string DataTableToXML(DataTable table)

{

StringWriter xml = new StringWriter();

string xmldata = string.Empty;

table.TableName = "journal";

table.WriteXml(xml,XmlWriteMode.IgnoreSchema);

xmldata = xml.ToString();

return xmldata;

}

Handling of XML string inside Store procedure/Query

Pass this xml string in your store procedure and handle this string in your SP by as following code given

Create procedure handlexml(@journalstring varchar(1000))

Begin

/*declare @journalstring varchar(2000)='<DocumentElement>

</journal>

</journal>

</journal>

</DocumentElement>' */

Suppose your string is xml element/Node and coming as above format

------------------------------------------

DECLARE @hDoc int

EXEC sp_xml_preparedocument @hDoc OUTPUT,

@journalstring

---------------------------------------------For XML Element--------------------------

SELECT jrnlfk,orderfk

FROM OPENXML(@hdoc, 'DocumentElement/journal',3)

WITH (jrnlfk int,

orderfk int)

-----------------------------------For XML Attribute--------------------------

declare @journalstring varchar(2000)='<Journal>

</Journal>'

---------------------------------------------------XXX-----------------------

SELECT *

FROM OPENXML(@hdoc, 'Journal/remit', 2)

WITH (journal int '@journalfk',

orderno int '@orderno')

end

Hope this help you

Happy Programming

Wednesday, August 10, 2011

How to use Merge T-SQL Command

The MERGE statement performs INSERT/UPDATE/DELETE operations on a target table based on the results of a join with a source table.

DECLARE @Code VARCHAR(3);

DECLARE @Description VARCHAR(15);

SET @Code = 'ABC';

SET @Description ='Merge';

MERGE INTO tblData T

USING (SELECT @Code AS code, @Description AS desc) S

ON T.Code = S.code

WHEN MATCHED THEN

UPDATE SET Description = S.desc

WHEN NOT MATCHED

THEN INSERT VALUES (code, desc);

Here’s a more detailed example to demonstrate how to use the MERGE statement. This script updates the Stock table based on daily trades tracked in the Trades table.

CREATE TABLE Stock (Stock VARCHAR(10) PRIMARY KEY, Qty INT CHECK(Qty > 0));

CREATE TABLE Trades (Stock VARCHAR(10) PRIMARY KEY, Delta INT);

INSERT Stock VALUES ('MSFT', 10);

INSERT Stock VALUES ('TXN', 5);

INSERT Trades VALUES ('MSFT', 5);

INSERT Trades VALUES ('TXN', -5);

INSERT Trades VALUES ('SBUX ', 3);

-- Apply changes to the Stock table based on daily trades

-- tracked in the Trades table. Delete a row from the Stock table

-- if all the stock has been sold. Update the quantity in the Stock

-- table if you still hold some stock after the daily trades. Insert

-- a new row if you acquired a new Stock.

-- As a result, TXN is deleted, SBUX inserted, MSFT updated

MERGE Stock S -- target table

USING Trades T -- source table

ON S.Stock = T.Stock

WHEN MATCHED AND (Qty + Delta = 0) THEN

DELETE -- delete stock if entirely sold

WHEN MATCHED THEN

-- update stock if you still hold some stock

UPDATE SET Qty = Qty + Delta

WHEN NOT MATCHED THEN

-- insert a row if the stock is newly acquired

INSERT VALUES (Stock, Delta)

-- output details of INSERT/UPDATE/DELETE operations

-- made on the target table

OUTPUT $action, inserted.Stock, deleted.Stock;

SELECT * FROM Stock;