1050 answers in forums like Experts-Exchange.com and Forums.Asp.Net and 225 articles in SQLHub.com

Well It’s been almost six and a half months I am helping the community via my own blog SQLHub.com and few forums, mainly Experts-Exchange.com and sometime Forums.Asp.Net. Today I thought to calculate about how many people I have helped in forums so just went to statistics and found that I have answered 936 questions in the area of Microsoft technology in Experts-Exchange.com and in forums.asp.net, I have answered approx 114 questions so far, so the total is 1050. In my blog SQLHub.com, I have written 225 articles till date and have received so many comments and 100s of emails from readers about appreciating my work.

I am really pleased to have really warm welcome and co-operation from the community. It is really encouraging me to work more for the community as this is the great way to share and learn new from other experts and colleague in community.

I hope to have same co-operation and encouraging comment from all of you in the future also and I promise today that I will work more and more in community and try to help each and every single individual who will come to me.

BTW, you can refer my Experts-Exchange.com profile and Asp.Net profile too, for more information about me and my community activity.

Thanks & Regards.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Advertisements

Started contributing in ASP.NET also

My regular blog reader knows that I used to help community by my blog www.SQLHub.com and by Experts-Exchange.com. Due to high response from the community and few suggestions from my friends I started participating in www.Asp.Net forum also. Actually my account in Asp.net forum is few year old but I have started contributing since last three days only. You know that I am working with Microsoft technology and how is it possible that I don’t contribute anything in Microsoft’s own website? J



Since my prime area of working is SQL Server, I would love to write blog my website and give solution to the people in Experts-Exchange.com; I will share my knowledge of .NET framework and SQL Server both in Asp.Net for a change now onwards.
So from now, I will be contributing to community from three ways.
Happy Helping!!!!
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

httpRuntime – Increase file size limit from 4 MB to bigger size in ASP.NET

I wrote an article on how to upload image to SQL Server database BLOB fileld and retrieve it back. You can refer that article at:

http://www.sqlhub.com/2009/03/image-store-in-sql-server-2005-database.html

After trying to implement this functionality, one of my readers has observed that he can’t upload the file big file. He came to me for solution. Actually in ASP.NET you can upload file with maximum size of 4MB. That is the default settings. Than how could you upload bigger file than 4096 kb? Isn’t there any way around? Because this is somehow very common need.

Well, they is a will, there is way!!! You can set maximum file size with httpRuntime tag. You can set this at machine.config or web.config but I insist, not to touch machine.config file as it has web server wide effect, you should set it in web.config so that it affect one website or one subdirectory in which web.config reside.

You have to add one line under <system.web> element of your web.config. The line is

<httpRuntime maxRequestLength=”20480″ />

This will increase file limit from 4 MB to 20MB.

Enjoy!!!!

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Stored Procedure return value handle and record set handle in C# ASP.NET

If you are SQL Server geek, you must be aware with Stored Procedure. While question comes to implement stored procedure in front end language, many developers makes mistake or feel confusion as per my recent observation so I am tempted to write one article which can show how to handle stored procedure in C# when it return some value and when it return some record sets.

–CREATE TABLE

CREATE TABLE empDemo

(

ID INT NOT NULL,

NAME VARCHAR(50)

)

GO

–INSERT DATA

INSERT INTO empDemo

SELECT 1,‘Ritesh’ UNION ALL

SELECT 2,‘Rajan’

–create SP which will return list of employee

–NOTE: this is just basic SP, you can create

–your own for your custom need.

CREATE PROC ListEmp (@id INT)

AS

BEGIN

SELECT * FROM empDemo WHERE ID=@id

END

–check SP whether it works.

EXEC ListEmp 2

Now, we will see C# code (in asp.net code behind) to return the list of employee:

protected void Button1_Click(object sender, EventArgs e)

{

//create connection string and assign it to SqlConnection object

string strConn = “Data Source=.;Initial Catalog=AdventureWorks;Persist Security Info=True;User ID=sa;Password=sa”;

System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(strConn);

conn.Open();

//setup SqlCommand and assign SP name along with input parameter variable name and value

System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand();

command.CommandType = System.Data.CommandType.StoredProcedure;

command.Connection = conn;

command.CommandText = “ListEmp”;

command.Parameters.AddWithValue(“@id”, “2”);

//create one data adapter which will execute the command and fill the data into data set

System.Data.SqlClient.SqlDataAdapter recordAdp = new System.Data.SqlClient.SqlDataAdapter();

recordAdp.SelectCommand = command;

System.Data.DataSet recordSet = new System.Data.DataSet();

recordAdp.Fill(recordSet);

command.Dispose();

conn.Close();

//bind gridview1 with our dataset

GridView1.DataSource = recordSet.Tables[0].DefaultView;

GridView1.DataBind();

}

This seems basic but really useful for newbie, now what if we have some other DML command and we want to return value from SP and handle it in our C# ASP.NET.

–create SP to insert record and retun value

–we will handle return value in C# code behind

CREATE PROC InsertEmp(@ID INT, @Name VARCHAR(20))

AS

BEGIN

BEGIN TRY

INSERT INTO empDemo(ID,Name) VALUES(@ID,@Name)

return 1

END TRY

BEGIN CATCH

return 0

END CATCH

END

–check SP works or not

EXEC InsertEmp 3,‘Alka’

Now let us move to ASP.NET C# code behind.

protected void Button1_Click(object sender, EventArgs e)

{

//create connection string and assign it to SqlConnection object

string strConn = “Data Source=.;Initial Catalog=AdventureWorks;Persist Security Info=True;User ID=sa;Password=sa”;

System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(strConn);

conn.Open();

//setup SqlCommand and assign SP name along with input parameter variable name and value

System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand();

command.CommandType = System.Data.CommandType.StoredProcedure;

command.Connection = conn;

command.CommandText = “InsertEmp”;

command.Parameters.Add(“@ReturnValue”,System.Data.SqlDbType.Int);

command.Parameters[“@ReturnValue”].Direction = System.Data.ParameterDirection.ReturnValue;

command.Parameters.AddWithValue(“@id”, “4”);

command.Parameters.AddWithValue(“@Name”, “Bihag”);

command.ExecuteScalar();

int i = Convert.ToInt32(command.Parameters[“@ReturnValue”].Value);

if (i == 1)

{

Response.Write(“Successfull!!!!”);

}

else

{

Response.Write(“Not Successfull!!!!”);

}

}

Hope you have enjoyed it!!!!

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Image store in SQL-Server 2005 database and retrieve it in asp.net application with C#:

Image store in SQL-Server 2005 database and retrieve it in asp.net application with C#:
Storing image in sql-server 2005 database is quiet simple task. All you need to deal with binary datatype. One more benefit you will get by storing image in database is that you are free from burden of managing lots of image folder and its path consistency.
Herewith, I am describing one simple web application in ASP.NET which will store the image in database and will retrieve it right from there.
Let us start our journey to this interesting topic.
First we will create one table in AdventureWorks database or in any other DB you have.
use adventureworks
GO
CREATE TABLE [dbo].[ImageStore] (
[ID] [int] NOT NULL ,
[ImageContent] [image] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Note: You can create couple more field in above table in order to get file name and its extension. I will be using “.JPG” extension in my code while retrieving the image but you can make it more dynamic.
Now, we have done with SQL-Server and let us move to our Visual Studio 2008. We will be creating one new web application of C#.
As soon as you will get your web form, draw one button on that with text property to “Upload Image”.
After setting text property of button, double click on that so it will open code behind of C# with click event button. Before we go further, uses following namespaces in your code behind as those are mandatory.
using System.Drawing;
using System.Data.SqlClient;
using System.IO;
Now, let us move to uploading the file.
Note: I will be going to upload one file from my “C” drive, you can make it more dynamic by using open file dialog box and select file from users computer.
Once you set the reference of above given name spaces, you have to write the following code in button1’s click event.
protected void Button1_Click(object sender, EventArgs e)
{
string strImageName= @”C:\Conversion.jpg”;
Bitmap bNewImage = new Bitmap(strImageName);

FileStream fs = new FileStream(strImageName, FileMode.Open, FileAccess.Read);

//creating byte array to read image
byte[] bImage = new byte[fs.Length];

//this will store conversion.jp in bImage byte array
fs.Read(bImage, 0, Convert.ToInt32(fs.Length));
fs.Close();
fs = null;

//open the database using odp.net and insert the data
string connstr = @”Data Source=.;Initial Catalog=AdventureWorks;
Persist Security Info=True;User ID=sa;Password=sa”;

SqlConnection conn = new SqlConnection(connstr);
conn.Open();

string strQuery;
strQuery = “insert into [dbo].[ImageStore](id,[ImageContent]) values(“ + “1,” + ” @pic)”;

SqlParameter ImageParameter= new SqlParameter();
ImageParameter.SqlDbType = SqlDbType.Image;
ImageParameter.ParameterName = “pic”;
ImageParameter.Value = bImage;

SqlCommand cmd = new SqlCommand(strQuery, conn);
cmd.Parameters.Add(ImageParameter);
cmd.ExecuteNonQuery();

Response.Write(“Image has been added to database successfully”);
cmd.Dispose();
conn.Close();
conn.Dispose();
}
So far, you have finished half of the journey. You have select the image from your hard drive, convert it in byte and insert it in SQL-Server table, now we are going to do reverse procedure to get image back.
Now, create another button on the same web form and set the text property with “Retrieve Image” value and also put one image box or grid or whatever tool in which you want to retrieve the image or else, you can simply save it at your hard drive. I am saving it in my hard drive with .JPG extension, you can make more dynamic as I specified in starting of this article.
Finally, write down the image retrieval code in button2’s click event as follows.
protected void Button2_Click(object sender, EventArgs e)
{
string connstr = @”Data Source=.;Initial Catalog=AdventureWorks;
Persist Security Info=True;User ID=sa;Password=sa”;

SqlConnection conn = new SqlConnection(connstr);
conn.Open();

//selecting image from sqldataadapter to dataset.
SqlDataAdapter sdImageSource = new SqlDataAdapter();
sdImageSource.SelectCommand = new SqlCommand(“SELECT * FROM [dbo].[ImageStore]”, conn);

DataSet dsImage = new DataSet();
sdImageSource.Fill(dsImage);

string strfn = Convert.ToString(DateTime.Now.ToFileTime());
strfn = @”D:\StoreIMG\” + strfn + “.jpg”;
FileStream fs = new FileStream(strfn, FileMode.CreateNew, FileAccess.Write);

//retrieving binary data of image from dataset to byte array
byte[] blob = (byte[])dsImage.Tables[0].Rows[0][“imageContent”];
//saving back our image to D:\StoreIMG folder
fs.Write(blob, 0, blob.Length);
fs.Close();
fs = null;
}
Note: You have to have write permission in D:\StoreIMG for ASP.Net user or else it will show you an error of permission.
Hope you have enjoyed it!!!!
Reference: Ritesh Shah

Image store in SQL-Server 2005 database and retrieve it in asp.net application with C#:

Image store in SQL-Server 2005 database and retrieve it in asp.net application with C#:

Storing image in sql-server 2005 database is quiet simple task. All you need to deal with binary datatype. One more benefit you will get by storing image in database is that you are free from burden of managing lots of image folder and its path consistency.

Herewith, I am describing one simple web application in ASP.NET which will store the image in database and will retrieve it right from there.

This article is move to following link:

http://blog.extreme-advice.com/2009/03/07/image-store-in-sql-server-2005-database-and-retrieve-it-in-asp-net-application-with-c/