Paramaterized Queries.

Parameterized queries prohibit the sql injection in your web application.

It allows you to safely write code like the following:

Let say, I have a SqlDataReader function to get the details of the particular ID:

public static SqlDataReader GetDetail(int id)

{

string sql = "select * from Raj_table where id = @id ";

SqlParameter paramUserId = new SqlParameter("id", SqlDbType.Int);

paramUserId.Value = id;

// I like to use the SqlHelper class

return SqlHelper.ExecuteReader(GetConnectionString(), CommandType.Text, sql, paramUserId);

}

Firstly, this parameter ensures that the paramater is an INT so a string value would throw an exception here. Also, if we were using a VARCHAR parameter, the SqlParameter value assignment automatically escapes the string for us.

If you run SQL Profiler and observe the queries, you'll notice that they are actually execute via a Stored Procedure (sp_executesql)

Happy Programming!!

Sending Email with ASP.NET

Email consists:

-From

-To

-CC

-BCC

-Subect

-Body

depend on these fields, below is the ASP.NET code to send the message:

First, add this namespace 'using System.Web.Mail' to your aspx.cs page.

then inside the button_click event, add this code:

MailMessage mail = new MailMessage();

mail.From = "someone@google.com";

mail.To = "somebody@google.com";

mail.Cc = "badgirl@google.com";

mail.Subject = "Just to say hi";

mail.Bcc = "badboy@google.com";

mail.Body = "Happy B'day to you!!";

//If you are using different mail server, please replace this one by your own...

SmtpMail.SmtpServer = "smtp.gmail.com";

SmtpMail.Send(mail);

Sending Attachments:

mail.From = "someone@google.com";

mail.To = "somebody@google.com";

mail.Cc = "badgirl@google.com";

mail.Subject = "Just to say hi";

mail.Bcc = "badboy@google.com";

mail.Body = "Happy B'day to you!!";

mail.BodyFormat = MailFormat.Text;

mail.Attachments.Add(new MailAttachment("c:\\temp\\test.pdf"));

SmtpMail.SmtpServer = "smtp.gmail.com";

SmtpMail.Send(mail);

Happy Coding!!!

Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool.

I keep getting the following errors on one of my application sites after clicking for many times:

"Timeout expired: The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached."


Below was my code:

SqlConnection conn = new SqlConnection(DataAccess.GetConnectionString());

conn.Open();

SqlDataSource1.SelectCommand = sql;

SqlDataSource1.SelectParameters.Clear();

Repeater1.DataSource = SqlDataSource1;

Repeater1.DataSourceID = string.Empty;

Repeater1.DataBind();

conn.Close();

I found later, this typically happens when connections are not closed after they are used. You should put everything in a try/catch/finally, with the connection being closed in the Finally to ensure that it always gets closed no mater what.

Hence the solution is:

SqlConnection conn = new SqlConnection(DataAccess.GetConnectionString());

conn.Open();

try

{

SqlDataSource1.SelectCommand = sql;

SqlDataSource1.SelectParameters.Clear();

Repeater1.DataSource = SqlDataSource1;

Repeater1.DataSourceID = string.Empty;

Repeater1.DataBind();

}

finally

{

SqlConnection.ClearPool(conn);

SqlConnection.ClearAllPools();

}

Note: If there is any return statement before you close the connection, there might be a connection leak ; be careful on that.

Return value from stored procedure to c#

Suppose you have a stored procedure which returns a bit value 0 or 1. If you want to call that procedure from c# and get that bit value, do like this below:

let say, the stored procedure has:

Name: dbo.sp_dateCheck

Variables:

@laptop varchar(50),

@startdate DateTime,

@enddate DateTime

and return bit variable: @CondFlag

SqlCommand com = new SqlCommand("dbo.sp_dateCheck", conn);

com.CommandType = CommandType.StoredProcedure;

try

{

conn.Open();

com.Parameters.Add(new SqlParameter("@laptop", Laptop.Text));

com.Parameters.Add(new SqlParameter("@startdate", startDate. DateTime));

com.Parameters.Add(new SqlParameter("@enddate", endDate. DateTime));

//read the returned value from stored procedure

com.Parameters.Add("@CondFlag", SqlDbType.Bit);

com.Parameters["@CondFlag"].Direction = ParameterDirection.ReturnValue;

com.ExecuteNonQuery();

int ReturnedVal = (int)com.Parameters["@CondFlag"].Value;

if (ReturnedVal == 0)

{ // do this

}

else

{//do this

}

}

catch

{

}

Happy Programming!!!