Sunday, July 22, 2012

Stored Procedure and Transactions

Stored Procedure and Transactions

I simply overheard the subsequent statement – “I don't use Transactions in SQL as i exploit Stored Procedure“.

I simply realized that there are numerous misconceptions regarding this subject. Transactions has nothing to try and do with Stored Procedures. Let me demonstrate that with a straightforward example.

USE tempdb
GO
-- produce three check Tables
CREATE TABLE TABLE_1 (ID INT);
CREATE TABLE TABLE_2 (ID INT);
CREATE TABLE TABLE_3 (ID INT);
GO
-- produce SP
CREATE PROCEDURE TestSP
AS
INSERT INTO TABLE_1 (ID)
VALUES (1)
INSERT INTO TABLE_2 (ID)
VALUES ('a')
INSERT INTO TABLE_3 (ID)
VALUES (3)
GO
-- Execute SP
-- SP can error out
EXEC TestSP
GO
-- Check the Values in Table
SELECT *
FROM TABLE_1;
SELECT *
FROM TABLE_2;
SELECT *
FROM TABLE_3;
GO

Now, the most purpose is: If Stored Procedure is transactional then, it ought to roll back complete transactions when it encounters any errors. Well, that doesn't happen during this case, that proves that Stored Procedure doesn't solely give simply the transactional feature to a batch of T-SQL.

Send Asynchronous Email in Asp.net

Send Asynchronous Email in Asp.net?

when you are sending bulk email using Asp.net then sending email using traditional parameter would be terribly time consuming as a result of we'll ought to expect the execution of smtp.send().but in Async methodology Email are going to be delivered in background and that we also can manage the output of email by Async handler.

Here i'm providing a code snippet to send email in Async manner.

using System.Web.Util;

     static void SendAsyncEmail()
        {
            
            MailMessage cnetcode_mail = new MailMessage();            
            cnetcode_mail.From = new MailAddress("RD@cnetcode.com");
            cnetcode_mail.To.Add("you@cnetcode.com");            
            cnetcode_mail.Subject = "your subject goes here";
            cnetcode_mail.Body = "Body content of the email.";
            //send the message
            SmtpClient smtp = new SmtpClient("Outgoing SMTP IP"); //Enter mail server address           
            object userState = cnetcode_mail;            
            smtp.SendCompleted += new SendCompletedEventHandler(SmtpClient_OnCompleted) 
            smtp.SendAsync( cnetcode_mail, userState );
        }

Thursday, July 19, 2012

Unable to connect to Sql Server

If you are unable to connect your SQL Server from outside or from Asp.net web.config
then  you must check this setting.

open your Sql Server and right click on your connected server and go to properties.
click security from left hand side and ensure
Sql server and windows authentication mode is enable.

:))

Tips to Speed up SQL Query

Almost Every website data is stored in a database and served to visitors upon request. Databases are very fast, but there is lots of things that we need to  enhance  speed and make sure not to waste any server resources. In this article, I am suggesting you 10  tips to optimize and speed up your Sql query.

1. Do not Select column that we do not need.

    A very common practice is to use Select * from tablename.
    It's better to select column which you need in output.

2. Avoid using Cursor , use while loop instead of Cursor.

3. Avoid using Sql statement in a loop.It's takes a lot of resource.

4.Use Join instead of Subqueries.
   
SELECT a.id,
    (SELECT MAX(created)
    FROM posts
    WHERE book_id = a.id)
AS latest_post FROM books a

However subqueries are useful, they often can be replaced by a join, which is definitely faster to execute.
SELECT a.id, MAX(p.created) AS latest_post
FROM books 
a
INNER JOIN posts p
    ON (a.id = p.book_id)
GROUP BY a.id

Enable IIS 7.0 Compression

To enable Compression at IIS 7.0

Enabling IIS Compression can improve performance of a web site.
you can enable both
A) Static Compression
B) Dynamic Compression

Follow these steps to enable it:-
Goto run and type intemgr => it will open up your IIS => then select your website from left hand side pane.
Now at right side you will see an option Compression just click it twice and here we go.
Check Static and dynamic compression.
[note: By default dynamic compression is disabled you need to enable dynamic compression from server manager in administrative panel]

:))