Create Auto Implemented Public Properties To Be Used in C# Code From SQL Server

This script will help you to create Auto Implemented public properties.
For this you just need to pass the table name and the Script will create properties for each column available in that Table.

Copy the following code to generate the properties:

DECLARE @TableName VARCHAR(100)
SET @TableName = 'tblUsers'
--String
SELECT 'public string ' + name + ' { get; set; }'
FROM Sys.COLUMNS WHERE Object_ID = OBJECT_ID(@TableName)
AND System_Type_ID IN (35, 36, 98, 99,165,167,173,175,231,239,241,231,189)
UNION
-- Int64
SELECT 'public Int64 ' + name + ' { get; set; }'
FROM Sys.COLUMNS WHERE Object_ID = OBJECT_ID(@TableName)
AND System_Type_ID IN (127)
UNION
-- Int32
SELECT 'public Int32 ' + name + ' { get; set; }'
FROM Sys.COLUMNS WHERE Object_ID = OBJECT_ID(@TableName)
AND System_Type_ID IN (56)
UNION
-- Int16
SELECT 'public Int16 ' + name + ' { get; set; }'
FROM Sys.COLUMNS WHERE Object_ID = OBJECT_ID(@TableName)
AND System_Type_ID IN (48, 52)
UNION
--Decimal
SELECT 'public Decimal ' + name + ' { get; set; }'
FROM Sys.COLUMNS WHERE Object_ID = OBJECT_ID(@TableName)
AND System_Type_ID IN (108, 62, 106, 60,122)
UNION
--Boolean
SELECT 'public Boolean ' + name + ' { get; set; }'
FROM Sys.COLUMNS WHERE Object_ID = OBJECT_ID(@TableName)
AND System_Type_ID IN (104)
UNION
-- DateTime
SELECT 'public DateTime ' + name + ' { get; set; }'
FROM Sys.COLUMNS WHERE Object_ID = OBJECT_ID(@TableName)
AND System_Type_ID IN (58,61)

Code to Get Twitter Follower's Count

When we send a request on the following url http://twitter.com/statuses/user_timeline/lakhangarg.xml?count=1 then the XML data will be returned as a XML.

This is sample XML retured from twitter:

<statuses type="array">
<status>
<created_at>Tue Oct 27 16:44:10 +0000 2009</created_at>
<id>5204855334</id>
<text>
http://bit.ly/oUB92 Reading: Create Script File For Each Store Procedure And Save into Seperate SQL File For Each Store Procedure
</text>
<source>web</source>
<truncated>false</truncated>
<in_reply_to_status_id/>
<in_reply_to_user_id/>
<favorited>false</favorited>
<in_reply_to_screen_name/>
<user>
<id>28538625</id>
<name>Lakhan Pal Garg</name>
<screen_name>lakhangarg</screen_name>
<location/>
<description/>
<profile_image_url>
http://a1.twimg.com/profile_images/319559736/PhotoFunia-19dab_normal.jpg
</profile_image_url>
<url>http://lakhangarg.blogspot.com/</url>
<protected>false</protected>
<followers_count>16</followers_count>
<profile_background_color>9ae4e8</profile_background_color>
<profile_text_color>000000</profile_text_color>
<profile_link_color>0000ff</profile_link_color>
<profile_sidebar_fill_color>e0ff92</profile_sidebar_fill_color>
<profile_sidebar_border_color>87bc44</profile_sidebar_border_color>
<friends_count>15</friends_count>
<created_at>Fri Apr 03 10:39:09 +0000 2009</created_at>
<favourites_count>0</favourites_count>
<utc_offset>-36000</utc_offset>
<time_zone>Hawaii</time_zone>
<profile_background_image_url>
http://s.twimg.com/a/1258070043/images/themes/theme1/bg.png
</profile_background_image_url>
<profile_background_tile>false</profile_background_tile>
<statuses_count>12</statuses_count>
<notifications/>
<geo_enabled>false</geo_enabled>
<verified>false</verified>
<following/>
</user>
<geo/>
</status>
</statuses>

Sample code to get the follower count from the XML data is:

private static string GetTwitterFollowers(string TwitterURL)
{
try
{
if (TwitterURL != "")
{
TwitterURL = TwitterURL.Substring(TwitterURL.LastIndexOf('/') + 1);
Uri uri = new Uri("http://twitter.com/statuses/user_timeline/" + TwitterURL + ".xml?count=1");
HttpWebRequest req = (HttpWebRequest)WebRequest.Create(uri);
req.UserAgent = "Get Content";
WebResponse resp = req.GetResponse();
Stream stream = resp.GetResponseStream();
StreamReader sr = new StreamReader(stream);
string s = sr.ReadToEnd();
System.Xml.XmlDocument xDoc = new System.Xml.XmlDocument();
xDoc.LoadXml(s);
string FollowerCount = xDoc.GetElementsByTagName("followers_count").Item(0).InnerText;
if (FollowerCount == "")
FollowerCount = "0";
return FollowerCount;
}
else
return "0";
}
catch
{
return "0";
}
}

Decrypt String Data

To decrypt data we need to pass the encrypted string data.
To see the code to Encrypt string data click here

public static string Decrypt(string cipherString)
{
byte[] keyArray;
//get the byte code of the string
byte[] toEncryptArray = Convert.FromBase64String(cipherString);
string key = ")(*&";
//if hashing was used get the hash code with regards to your key
MD5CryptoServiceProvider hashmd5 = new MD5CryptoServiceProvider();
keyArray = hashmd5.ComputeHash(UTF8Encoding.UTF8.GetBytes(key));
//release any resource held by the MD5CryptoServiceProvider
hashmd5.Clear();

TripleDESCryptoServiceProvider tdes = new TripleDESCryptoServiceProvider();
//set the secret key for the tripleDES algorithm
tdes.Key = keyArray;
//mode of operation. there are other 4 modes. We choose ECB(Electronic code Book)

tdes.Mode = CipherMode.ECB;
//padding mode(if any extra byte added)
tdes.Padding = PaddingMode.PKCS7;

ICryptoTransform cTransform = tdes.CreateDecryptor();
byte[] resultArray = cTransform.TransformFinalBlock(toEncryptArray, 0, toEncryptArray.Length);
//Release resources held by TripleDes Encryptor
tdes.Clear();
//return the Clear decrypted TEXT
return UTF8Encoding.UTF8.GetString(resultArray);
}

Encrypt a string Data

Pass the string data to this function and this function will return the encrypted string. To see the code to decrypt string data click here

public static string Encrypt(string toEncrypt)
{
byte[] keyArray;
byte[] toEncryptArray = UTF8Encoding.UTF8.GetBytes(toEncrypt);
string key = ")(*&";
MD5CryptoServiceProvider hashmd5 = new MD5CryptoServiceProvider();
keyArray = hashmd5.ComputeHash(UTF8Encoding.UTF8.GetBytes(key));
//Always release the resources and flush data of the Cryptographic service provide. Best Practice
hashmd5.Clear();
TripleDESCryptoServiceProvider tdes = new TripleDESCryptoServiceProvider();
//set the secret key for the tripleDES algorithm
tdes.Key = keyArray;
//mode of operation. there are other 4 modes. We choose ECB(Electronic code Book)
tdes.Mode = CipherMode.ECB;
//padding mode(if any extra byte added)

tdes.Padding = PaddingMode.PKCS7;

ICryptoTransform cTransform = tdes.CreateEncryptor();
//transform the specified region of bytes array to resultArray
byte[] resultArray = cTransform.TransformFinalBlock(toEncryptArray, 0, toEncryptArray.Length);
//Release resources held by TripleDes Encryptor
tdes.Clear();
//Return the encrypted data into unreadable string format
return Convert.ToBase64String(resultArray, 0, resultArray.Length);
}

Method to Get Time Elapsed With Refernce to Current Date Time

With the help of following code we can get the time elapsed with refernce to current date time.

Pass the Previous Activities Date Time and the following function will return time elapsed in the format:
{Number} hour/hours ago,{Number} Day/Days ago,{Number} week/weeks ago,{Number} month/months ago

public static string GetDaysAgo(string strCreatedDateTime)
{
try
{
DateTime CreatedDateTime = Convert.ToDateTime(strCreatedDateTime);
string StrReturn = null;
TimeSpan TimeDiff = DateTime.Now - CreatedDateTime;
double MinDiff = Convert.ToDouble(TimeDiff.TotalMinutes.ToString());
if (MinDiff < 0) MinDiff = 0;
if (MinDiff < 60) StrReturn = Math.Floor(Convert.ToDecimal(MinDiff)).ToString() + " minutes ago";
else { MinDiff = MinDiff / 60;
if (MinDiff < 24) if (Math.Floor(Convert.ToDecimal(MinDiff)) == 1) StrReturn = Math.Floor(Convert.ToDecimal(MinDiff)).ToString() + " hour ago";
else StrReturn = Math.Floor(Convert.ToDecimal(MinDiff)).ToString() + " hours ago";
else { MinDiff = MinDiff / 24;
if (MinDiff < 7) if (Math.Floor(Convert.ToDecimal(MinDiff)) == 1) StrReturn = Math.Floor(Convert.ToDecimal(MinDiff)).ToString() + " day ago";
else StrReturn = Math.Floor(Convert.ToDecimal(MinDiff)).ToString() + " days ago";
else if (MinDiff < 30) { MinDiff = MinDiff / 7;
if (Math.Floor(Convert.ToDecimal(MinDiff)) == 1) StrReturn = Math.Floor(Convert.ToDecimal(MinDiff)).ToString() + " week ago";
else StrReturn = Math.Floor(Convert.ToDecimal(MinDiff)).ToString() + " weeks ago";
} else { MinDiff = MinDiff / 30;
if (Math.Floor(Convert.ToDecimal(MinDiff)) == 1) StrReturn = Math.Floor(Convert.ToDecimal(MinDiff)).ToString() + " month ago";
else StrReturn = Math.Floor(Convert.ToDecimal(MinDiff)).ToString() + " months ago";
} } } return StrReturn;
} catch (Exception ex) { return "1 months ago";
} }

Call this function like this:

GetDaysAgo("30/8/2009");

Create XML Doc in C#

This Sample code will show you how to create a XML document using C#.

Suppose we have Data like:

PublisherName: Wrox

Book Subject: ASP.NET

Book Title:

(1) Beginning ASP.NET MVC 1.0

(2) Silverlight 3 Programmer's Reference

(3) Professional Refactoring in C# & ASP.NET

Book Subject: SQL Server

Book Title:

(1) Beginning Microsoft SQL Server 2008 Administration

(2) Professional LINQ

And we want to show this data in XML format:

Sample Code to show above Data into XMl Format:

XmlDocument xDoc = new XmlDocument();

XmlNode ndRootNode = xDoc.CreateElement("BookData");

xDoc.AppendChild(ndRootNode);

XmlNode ndPublisher = xDoc.CreateElement("Publisher");

XmlAttribute atName = xDoc.CreateAttribute("Name");

atName.Value = "Wrox";

ndPublisher.Attributes.Append(atName);

ndRootNode.AppendChild(ndPublisher);

XmlNode ndBookSubject1 = xDoc.CreateElement("Subject");

XmlAttribute atSubject1 = xDoc.CreateAttribute("Name");

atSubject1.Value = "ASP.NET";

ndBookSubject1.Attributes.Append(atSubject1) ;

ndPublisher.AppendChild(ndBookSubject1);

XmlNode ndBookTitle1 = xDoc.CreateElement("Title");

ndBookTitle1.AppendChild(xDoc.CreateCDataSection("Beginning ASP.NET MVC 1.0"));

ndBookSubject1.AppendChild(ndBookTitle1);

XmlNode ndBookTitle2 = xDoc.CreateElement("Title");

ndBookTitle2.AppendChild(xDoc.CreateCDataSection("Silverlight 3 Programmer's Reference"));

ndBookSubject1.AppendChild(ndBookTitle2);

XmlNode ndBookTitle3 = xDoc.CreateElement("Title");

ndBookTitle3.AppendChild(xDoc.CreateCDataSection("Professional Refactoring in C# & ASP.NET"));

ndBookSubject1.AppendChild(ndBookTitle3);

XmlNode ndBookSubject2 = xDoc.CreateElement("Subject");

XmlAttribute atSubject2 = xDoc.CreateAttribute("Name");

atSubject2.Value = "SQL Server";

ndBookSubject1.Attributes.Append(atSubject2);

ndPublisher.AppendChild(ndBookSubject2);

XmlNode ndBookTitle4 = xDoc.CreateElement("Title");

ndBookTitle4.AppendChild(xDoc.CreateCDataSection("Beginning Microsoft SQL Server 2008 Administration"));

ndBookSubject2.AppendChild(ndBookTitle4);

XmlNode ndBookTitle5 = xDoc.CreateElement("Title");

ndBookTitle5.AppendChild(xDoc.CreateCDataSection("Professional LINQ"));

ndBookSubject2.AppendChild(ndBookTitle5);

Output:

<BookData>

<Publisher Name="Wrox">

<Subject Name="SQL Server">

<Title>Beginning ASP.NET MVC 1.0</Title>

<Title>Silverlight 3 Programmer's Reference</Title>

<Title>Professional Refactoring in C# & ASP.NET</Title>

</Subject>

<Subject>

<Title>Beginning Microsoft SQL Server 2008 Administration</Title>

<Title>Professional LINQ</Title>

</Subject>

</Publisher>

</BookData>

The CreateCDataSection

xDoc.CreateCDataSection("Beginning Microsoft SQL Server 2008 Administration")

is used to write the data in CData Section

Implement Multilingual in ASP.NET Web Site using globalization.

Before going to implement we need to know the answer of few question:

What is globalization?
Globalization is the process of making an application that supports multiple cultures without mixing up the business logic and the culture related information of that application.

What is localization?
In Localization you customize the application for new locales. This consists of translating resources that you identified during the globalization phase.

What is Local Resource?
File which stores resources with local scope into App_LocalResource directory. We can not access one pages local resource from another page.
They can be generated by ASP.Net automatically. For it go to design view of aspx page and then Tools > Generate

What is Global Resource?
File used to store resources globally, means we can access same resource from different pages. It store in App_GlobalResource folder. we can't generate global resource automatically.

Difference between Local Resource and Global Resource:

1. Performance: Global Resources are faster than Local Resource- because Global Resources are Strongly Typed.
2. Consistency. Explanation: In your application you have Label Text as Category and ten pages. Now you want to change it to CATEGORY then if you using Local Resource concept then you have to change 10 resource files, where as in Global Resource you need to change only one.
3. Difference as per Project Type:
   • Local Resource is treated as content based resource, so we can change it and it affects to website.
   • Global Resource is treated as embedded resource, So they are compiled into specific language dlls into bin folder.

Code to implement multilingual using global resource:

Add This in global.asax file:

private void Application_BeginRequest(Object source, EventArgs e)
{
string[] languages = HttpContext.Current.Request.UserLanguages;
if (languages[0].ToLower() != null && languages[0].ToLower()!="")
{
System.Threading.Thread.CurrentThread.CurrentUICulture = new System.Globalization.CultureInfo(languages[0].ToLower());
System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture(languages[0].ToLower());

}
}

now we can use resource file on page like this:
suppose we have three resource file in app_globalresource folder:
TestResource.resx (Default)
TestResource.hi.resx (Hindi)
TestResource.zn-ch.resx (Chinese)
<:Label ID="lblText" runat="server" Text="<%$ Resources:TestResource, LabelText %&>">

now as we set the language from browser, the text for label will be read from respected files.

Read, Insert and Update Data into Excel Sheet

This application will tell you about the operation on excel sheet (like Insert, Update and Read) with the help of simple queries.
i am using OLEDB provider for these operations.
Connection String for OLEDB is:

string file = Server.MapPath("UserData.xls"); string constr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + file + ";
Extended Properties=Excel 8.0;";

Read data and display the result in repeater control:
First we'll read the data from the Excel sheet and display the result into a repeater control.
first query to fetch the records:

string query = "Select UserID,UserName,Country,State,City from [Sheet1$]";

[Sheet1$] name of the sheet in the Excel workbook.And UserID,UserName,Country,State,City are the name of the column in this sheet.
this query will return all the records from Excel Sheet [Sheet1$].
Code to execute the above query is:

DataSet dsUserData = new DataSet();
using (OleDbConnection Connection = new OleDbConnection(constr))
{
using (OleDbDataAdapter DataAdapter = new OleDbDataAdapter(query, Connection))
{
DataAdapter.Fill(dsUserData, "UserData");
DataAdapter.AcceptChangesDuringFill = false;
DataAdapter.Dispose();
Connection.Close();
}
}

Update Data:
we can click on the Edit Data link for the corresponding row to edit the record.
on click of Edit Data button the corresponding records data will be shown in textboxes. user can change the text and click on the "Update Excel Data" to reflect changes is Excel Sheet.
Code to Update Record:

string file = Server.MapPath("UserData.xls");
string constr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + file + ";Extended Properties=Excel 8.0;";
using (OleDbConnection Connection = new OleDbConnection(constr))
{
Connection.Open();
string query = "UPDATE [Sheet1$] SET UserName=\"" + txtUserName.Text.Trim() + "\",Country=\"" + txtCountry.Text.Trim() + "\",State=\"" + txtState.Text.Trim() + "\",City=\"" + txtCity.Text.Trim() + "\" WHERE UserID="+ btnUpdate.CommandArgument.ToString();
using (OleDbCommand objCmd = new OleDbCommand(query, Connection))
{
objCmd.ExecuteNonQuery();
objCmd.Dispose();
Connection.Close();
}
}

Similar way we can add the new record into the Excel Sheet. first we will get the UserId of the Last record and add one to that record to get the next UserID as:

Int32 LastUserID = Convert.ToInt32(((Label)rptUserData.Items[rptUserData.Items.Count - 1].FindControl("lblID")).Text);
LastUserID += 1;
Click Here To Download Source Code

Note: Please contact me in case you have any error in download the code: lakhangarg@gmail.com

Note: User the Following Connection String for xlsx file:
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + file + ";Extended Properties=Excel 12.0;"

Create Thumbnail

This Code will help the users to create thumbnail of an image. for this user need to pass three parameters as:
(1) Image To Scale
(2) Desired height of the output image
(3) Desired width of output image

First we will get the image in a Bitmap Object and then we will scale the image.

Here is the Code to create thumbnail:

/// Scales an image as per the ratio of dimensions of the image to hight and width specified in the
/// parameters

public static System.Drawing.Image ScaleByPercent(string strImage, double dblImgHt, double dblImgWd)
{
Bitmap imgRetPhoto = null;
double dblWdRatio, dblHtRatio;

try
{
imgRetPhoto = new Bitmap(strImage);
if (imgRetPhoto.Height > Convert.ToInt32(dblImgHt) || imgRetPhoto.Width > Convert.ToInt32(dblImgWd))
{
if (imgRetPhoto.Height > dblImgHt)
{
dblHtRatio = dblImgHt / Convert.ToDouble(imgRetPhoto.Height);
dblWdRatio = Convert.ToDouble(imgRetPhoto.Width) * dblHtRatio;
imgRetPhoto = new Bitmap(imgRetPhoto, Convert.ToInt32(dblWdRatio), Convert.ToInt32(dblImgHt));
imgRetPhoto.SetResolution(imgRetPhoto.HorizontalResolution, imgRetPhoto.VerticalResolution);
}

if (imgRetPhoto.Width > dblImgWd)
{
dblWdRatio = dblImgWd / Convert.ToDouble(imgRetPhoto.Width);
dblHtRatio = Convert.ToDouble(imgRetPhoto.Height) * dblWdRatio;
imgRetPhoto = new Bitmap(imgRetPhoto, Convert.ToInt32(dblImgWd), Convert.ToInt32(dblHtRatio));
imgRetPhoto.SetResolution(imgRetPhoto.HorizontalResolution, imgRetPhoto.VerticalResolution);
}
return imgRetPhoto;
}
else
return imgRetPhoto;
}
catch (Exception ex)
{
throw ex;
}
}

Save Image For given URL

Set Url of the Image you want to save on your system.
DestinationPath is the path where you want to save the image.
in this case it is Products folder on the root of the application folder.
strImage = url.Substring(url.LastIndexOf('/') + 1);
above line of code will give us the name of the image.
GetBytesFromUrl(url); method will return the Byte array of the image
for the corrsponding url of the image.
Once we got the Byte image data in the form of Byte array then
we can save the image using WriteBytesToFile method.

string url = "http://3.bp.blogspot.com/_Kp--fzJWFmc/
SNH1SzkEZoI/AAAAAAAAADU/W0bjZKqem6s/S220/lakhan.jpg";
strImage = url.Substring(url.LastIndexOf('/') + 1);
string DestinationPath = Server.MapPath("~/Products");
byte[] bytes = GetBytesFromUrl(url);
WriteBytesToFile(DestinationPath + "/" + strImage, bytes);

First we make the request for the given image url and server
will send us a response for that request in the form of stream of data.

With the help of BinaryReader system will read the content of image
and add that into byte array.

static public byte[] GetBytesFromUrl(string url)
{
byte[] b;
HttpWebRequest myReq =
(HttpWebRequest)WebRequest.Create(url);
WebResponse myResp = myReq.GetResponse();

Stream stream = myResp.GetResponseStream();
//int i;
using (BinaryReader br = new BinaryReader(stream))
{
//i = (int)(stream.Length);
b = br.ReadBytes(500000);
br.Close();
}
myResp.Close();
return b;
}

Here we'll write the Byte array data with the help of
BinaryWriter on the given destinaion path.

static public void WriteBytesToFile(string fileName, byte[] content)
{
FileStream fs = new FileStream(fileName, FileMode.Create);
BinaryWriter w = new BinaryWriter(fs);
try
{
w.Write(content);
}
finally
{
fs.Close();
w.Close();
}
}

Import Data to CSV File FROM DataTable

In th First step we will Clear the Response Object and then we'll attach a csv file with the Response Object. that will be popup once we'll write the Data into it and End the Response Object.

After Attaching the file we'll write the column Name into the CSV File. and then iterate for all the Records to write them into the CSV file.

"" is used to protect the Data if the data contain ',' (Comma) .

Sample Code:

DataTable dtProducts=GetProductsFromDB();
string attachment = "attachment; filename=products.csv";
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.ClearHeaders();
HttpContext.Current.Response.ClearContent();
HttpContext.Current.Response.AddHeader("content-disposition", attachment);
HttpContext.Current.Response.ContentType = "application/octet-stream";

//Write Column Names
string str = "ProductNo,Product,SKU,ProductType,Price";
HttpContext.Current.Response.Write(str);
HttpContext.Current.Response.Write(Environment.NewLine);
for(int i =0; i
{
string strRowData="";
for(int jColumns=0; jColumns
{
if(strRowData=="")
strRowData='"'+dtProducts.Rows[i][jColumns].ToString()+'"';
else
{
strRowData=","+'"'+dtProducts.Rows[i][jColumns].ToString()+'"';
}
}
HttpContext.Current.Response.Write(strRowData);
HttpContext.Current.Response.Write(Environment.NewLine);
strRowData="";
}
HttpContext.Current.Response.End();

string v/s StringBuilder

System.Text.StringBuilder strTest=new System.Text.StringBuilder();
Strings are immutable. immutable means every time we alter the string a new object is created. hence lower the performance. while stringBuilder are mutable. so it increase the performance where we need to perform altered, insert and remove operations. But it is not recommended to use StringBuilder always. for small string where you need to perform less operation then use string
and in case of large string and more operation use StringBuilder.

Read the CSV Data and Save the Data in DataSet.

In this first we will read the data from csv file using the select query and load the data into a dataset.

Query To read data from CSV file: 
SELECT * FROM [test.csv]

OLEDB Connectionstring:
 @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\Lakhan\Projects\Testweb\Test\;Extended
Properties=""text;HDR=Yes;FMT=Delimited"""

if you want to consider first row as column then mention HDR=Yes otherwise no.
using System.Data.OleDb;
for(Int32 i=0; i
   {
       Response.Write(dataSetFromCSV.Tables[0].Columns[i].
       ColumnName + "");
   }
the above code is used to print all the column names

Sample Code:
using System.Data.OleDb;
private void ReadCSVFile()
{
 string cnStr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data
 Source=E:\Lakhan\Projects\Testweb\Test\;Extended
 Properties=""text;HDR=Yes;FMT=Delimited""";
 OleDbConnection ExcelConnection =
 new OleDbConnection(cnStr);
 OleDbCommand ExcelCommand = new OleDbCommand
 (@"SELECT * FROM [test.csv]",
 ExcelConnection);
 OleDbDataAdapter ExcelAdapter =
 new OleDbDataAdapter(ExcelCommand);
 ExcelConnection.Open();
      
 DataSet dataSetFromCSV = new DataSet();
 ExcelAdapter.Fill(dataSetFromCSV);
 ExcelConnection.Close();
 for(Int32 i=0; i
 {
     Response.Write(dataSetFromCSV.Tables[0].
     Columns[i].ColumnName + "");
 }
}

CSV File's Content:
Customer Number,Last Name,First Name,Address,City,Province,
Postal Code,Balance10001,Smith,Dave,123 Parkside Ave.,London,
ON,N6J 4G6,125.3510002,Pearson,Anne,44 Northside Road,Toronto,
ON,N0M 5L8,38.1210003,Carson,Ronald,12 Talbot Road,London,
ON,N6U 3G8,1024.5610006,Davis,Albert,19 Southam Road,Ajax,
ON,N7J 5H7,-8.5510007,Anderson,Theresa,118 Sarnia Road,
London,ON,N6G 5C6,1181.1210009,Jones,Jason,1008,
Carver Place,Ottawa,ON,N8K 8H4,0.00

ref v/s out Parametes in C#

out and ref looks quite similar in nature.Both parameters are used to return back some value to the caller of the function. But still there is a important difference between them. these two types are used for specific purpose.
When we use the out parameter, The program calling the function need not assign a value to the out parameter before making the call to the function. The value of the out parameter has to be set by the function before returning the value.
For a ref type parameter, the value to the parameter has to be assigned before calling the function. If we do not assign the value before calling the function we will get a compiler error.
Another important thing to note here is that in case of ref parameter, the value passed by the caller function can be very well used by the called function. The called function does not have the compulsion to assign the value to a ref type parameter. But in case of the out parameter, the called function has to assign a value.

Export Data From Excel Sheet into Dataset- C#

Procedure To Export Excel Sheet Data into Dataset With the help of OLEDB
Connection String For Excel & Query to Fetch The Records:

string constr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + file + ";Extended Properties=Excel 8.0;";

Select Column1,Column2,Column3,Column4 from [Sheet1$]
Sheet1 is the name of the sheet in the Excel file from where you want to get the records.

Complete Code to Get the Excel Sheet Data
string file = "Physical path of the File"; (Like: D:\\Amt.xls)
string constr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + file + ";Extended Properties=Excel 8.0;";

string query = "Select Column1,Column2,Column3,Column4 from [Sheet1$]";
DataSet dataSet = new DataSet();
using (OleDbConnection Connection = new OleDbConnection(constr))
{
using (OleDbDataAdapter DataAdapter = new OleDbDataAdapter(query, Connection))
{
DataAdapter.Fill(dataSet, "DataSetName");
DataAdapter.AcceptChangesDuringFill = false;

Function To Send Email - ASP.NET (C#)

User needs to pass few parameter to this method to send the mail.
body - Content of email that you want to send.
toadd - Address of the user to whom you want to send the Email.
ccAdd - Address of the user to whom you want to send Email in CC.
bccadd -Address of the user to whom you want to send Email in BCC.
fromaddm - Address of the user that want to send the Email.
subject - Subject of the Email.
attachment - Attachment any if you want to send to user otherwise set the value as blank if you don't want to send any email.

Address of SMTP Server - Replace this text with the address of your SMTP Server.
Code To Send Email:
public static bool SendMail(string body, string toadd, string fromadd, string subject, string attachment, string ccAdd, string bccadd)
{
string mailServerName = "Address of SMTP Server ";
try
{
//MailMessage represents the e-mail being sent
using (MailMessage message = new MailMessage(fromadd, toadd, subject, body))
{
if (attachment != "")
{
message.Attachments.Add(new Attachment(attachment));
}
if (bccadd != "")
{
message.Bcc.Add(bccadd);
}
message.IsBodyHtml = true;
message.Priority = MailPriority.Normal;
SmtpClient mailClient = new SmtpClient();
mailClient.Host = mailServerName;
mailClient.UseDefaultCredentials = true;
mailClient.Send(message);

}
return true;
}
catch (SmtpException ex)
{

return false;
}
catch (Exception ex)
{

return false;
}
}