Friday, June 17, 2011

How to convert date time format based on the languages in C#.NET


How to convert date time format based on the languages in C#.NET

If you want to convert date into different languages date, you can use .NET globalization library for this purpose, see the example below

Namespace

using System.Globalization;

Source Code

//Type the ISO code based on languages you want like English: en-us, Genman: de, French (Canada): fr-ca 
CultureInfo culInfo = new CultureInfo(textBox1.Text);
//I am converting current date into desired language datetime format
DateTime dtUs = Convert.ToDateTime(DateTime.Now, culInfo.DateTimeFormat);
//this will give Typed language date format
label1.Content = string.Format("{0} {1}",culInfo.DateTimeFormat.ShortDatePattern,culInfo.DateTimeFormat.LongTimePattern);
//this will give Typed language date and time
label2.Content = dtUs.ToString(culInfo.DateTimeFormat.ShortDatePattern + " " + culInfo.DateTimeFormat.LongTimePattern);

Check code like

textBox1.Text  = “fr-ca”  // for Canada (French)

How to Read/Write Data from DBF file using C#.net application.

How to Read/Write Data from DBF file using C#.net application.

Overview:

This document is useful to create import export utility for DBF file. Some time it does require import/export data from DBF file from your DOTNET application. Currently I am working on same kind of requirement where I have developed Export Import Utility it's include export/import data from various file type. One of toughest type is DBF file import export, I did some research on that and got one reboots solution that I want to share with you all.

This document covers the following

1) How to create DBF file database table
2) How to Insert data in created DBF file
3) How to Read data from DBF file.

Introduction:

DBF is a FoxPro database file, this we can create from C#.net application even we can use basic DDL and DML statement on this like 'Create table' , 'Insert table' etc.
If our requirement is to export your data which in is the Dataset (in C#.net) in DBF file this document will help you. You can also read data from any DBF file using this document. Using the same function with different connection string you can use to read / write excel file too.

How function works.

1) I have created one function for Export DBF file; this will takes one dataset as an input parameter
2) Based on input dataset parameter I am creating the database table using 'create table' statement.
3) Then I open the connection using 'Microsoft.Jet.OLEDB.4.0' provider.
4) Using OleDbDataAdapter object I am filling the dataset (dsFill) this will create one DBF table structure with given table name.
5) Now using the Same connection you can fire the next DML statement 'Insert into ..' to insert the data into the DBF file.
6) And same way you can read the data using 'Select..' statement. Fro Import.

Some points are important here

1) DBF file creates with Table name (TEMP.dbf) given in the 'Create table TEMP...' statement.
2) If you are reading the DBF file using 'Select ...' statement the select statement should be like 'Select * from TEMP'.
3) Key words are not acceptable in the DDL or DML statement. Like table, date etc.
4) This will take data source as folder name where DBF file exist like If you want to export / Import DBF file from "C:\Temp\country.dbf." then your Data source will be "C:\Temp" and Table name will be "country".

Source Code:

Follow the simple steps to create application. You can simply copy and past code from here in your application

1) Create the function which will give you the folder path and table name

private void GetFileNameAndPath(string completePath, ref string fileName, ref string folderPath)
{
string[] fileSep = completePath.Split('\\');
for (int iCount = 0; iCount < fileSep.Length; iCount++)
{
if (iCount == fileSep.Length - 2)
{
if (fileSep.Length == 2)
{
folderPath += fileSep[iCount] + "\\";
}
else
{
folderPath += fileSep[iCount];
}
}
else
{
if (fileSep[iCount].IndexOf(".") > 0)
{
fileName = fileSep[iCount];
fileName = fileName.Substring(0, fileName.IndexOf("."));
}
else
{
folderPath += fileSep[iCount] + "\\";
}
}
}
}
Note: Use the folderPath and DataSource in the connection string and fileName in the 'Create tabel' statement.

1) Create ExportDBF function.

// This function takes Dataset (to be exported) and filePath as input parameter and return // bool status as output parameter
// comments are written inside the function to describe the functionality
public bool EportDBF(DataSet dsExport, string filePath)
{
string tableName = string.Empty;
string folderPath = string.Empty;
bool returnStatus = false;
// This function give the Folder name and table name to use in
// the connection string and create table statement.
GetFileNameAndPath(filePath, ref tableName, ref folderPath);
// here you can use DBASE IV also
string connString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + folderPath + "; Extended Properties=DBASE III;";
string createStatement = "Create Table " + tableName + " ( ";
string insertStatement = "Insert Into " + tableName + " Values ( ";
string insertTemp = string.Empty;
OleDbCommand cmd = new OleDbCommand();
OleDbConnection conn = new OleDbConnection(connString);
if (dsExport.Tables[0].Columns.Count <= 0) { throw new Exception(); }
// This for loop to create "Create table statement" for DBF
// Here I am creating varchar(250) datatype for all column.
// for formatting If you don't have to format data before
// export then you can make a clone of dsExport data and transfer // data in to that no need to add datatable, datarow and
// datacolumn in the code.
for (int iCol = 0; iCol < dsExport.Tables[0].Columns.Count; iCol++)
{
createStatement += dsExport.Tables[0].Columns[iCol].ColumnName.ToString();
if (iCol == dsExport.Tables[0].Columns.Count - 1)
{
createStatement += " varchar(250) )";
}
else
{
createStatement += " varchar(250), ";
}
}
//Create Temp Dateset
DataSet dsCreateTable = new DataSet();
//Open the connection
conn.Open();
//Create the DBF table
DataSet dsFill = new DataSet();
OleDbDataAdapter daInsertTable = new OleDbDataAdapter(createStatement, conn);
daInsertTable.Fill(dsFill);
//Adding One DataTable into the dsCreatedTable dataset
DataTable dt = new DataTable();
dsCreateTable.Tables.Add(dt);
for (int row = 0; row < dsExport.Tables[0].Rows.Count; row++)
{
insertTemp = insertStatement;
//Adding Rows to the dsCreatedTable dataset
DataRow dr = dsCreateTable.Tables[0].NewRow();
dsCreateTable.Tables[0].Rows.Add(dr);
for (int col = 0; col < dsExport.Tables[0].Columns.Count; col++)
{
if (row == 0)
{
//Adding Columns to the dsCreatedTable dataset
DataColumn dc = new DataColumn();
dsCreateTable.Tables[0].Columns.Add(dc);
}
// Remove Special character if any like dot,semicolon,colon,comma // etc
dsExport.Tables[0].Rows[row][col].ToString().Replace("LF", "");
// do the formating if you want like modify the Date symbol , //thousand saperator etc.
dsCreateTable.Tables[0].Rows[row][col] = dsExport.Tables[0].Rows[row][col].ToString().Trim();
} // inner for loop close
// Create Insert Statement
if (col == dsExport.Tables[0].Columns.Count - 1)
{
insertTemp += "'" + dsCreateTable.Tables[0].Rows[row][col] + "' ) ;";
}
else
{
insertTemp += "'" + dsCreateTable.Tables[0].Rows[row][col] + "' , ";
}
// This lines of code insert Row One by one to above created
// datatable.
daInsertTable = new OleDbDataAdapter(insertTemp, conn);
daInsertTable.Fill(dsFill);
} // close outer for loop
MessageBox.Show("Exported done Successfully to DBF File.");
returnStatus = true;
} // close function
Note: for formatting like date and thousand separators add below lines of code while transfer data from one dataset to another.
1) Char decimalSymbol = '/'; /. Or "."
dsCreateTable.Tables[0].Rows[row][col] = Convert.ToString(Convert.ToDecimal(dsExport.Tables[0].Rows[row][col].ToString().Trim())).Replace('.', decimalSymbol);
2) string thousandSeparator = "#" + "" + "###"; // or ","
dsCreateTable.Tables[0].Rows[row][col] = Convert.ToString(Convert.ToDouble(dsExport.Tables[0].Rows[row][col].ToString().Trim()).ToString(thousandSeparator));
3) string dateFormat ="MM/dd/yyyy";
dsCreateTable.Tables[0].Rows[row][col] = Convert.ToString(Convert.ToDateTime(dsExport.Tables[0].Rows[row][col].ToString().Trim()).ToString(dateFormat));

2) Create ImportDBF function.

// This function takes filePath as input parameter and return DataSet as output parameter
// comments are written inside the function to describe the functionality
public DataSet ImportDBF(string filePath)
{
string ImportDirPath = string.Empty;
string tableName = string.Empty;
// This function give the Folder name and table name to use in
// the connection string and create table statement.
GetFileNameAndPath(filePath, ref tableName, ref ImportDirPath);
DataSet dsImport = new DataSet();
string thousandSep = thousandSeparator;
string connString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + ImportDirPath + "; Extended Properties=DBASE IV;";
OleDbConnection conn = new OleDbConnection(connString);
DataSet dsGetData = new DataSet();
OleDbDataAdapter daGetTableData = new OleDbDataAdapter("Select * from " + tableName , conn);
// fill all the data in to dataset
daGetTableData.Fill(dsGetData);
DataTable dt = new DataTable(dsGetData.Tables[0].TableName.ToString());
dsImport.Tables.Add(dt);
// here I am copying get Dataset into another dataset because //before return the dataset I want to format the data like change //"datesymbol","thousand symbol" and date format as did while
// exporting. If you do not want to format the data then you can // directly return the dsGetData
for (int row = 0; row < dsGetData.Tables[0].Rows.Count; row++)
{
DataRow dr = dsImport.Tables[0].NewRow();
dsImport.Tables[0].Rows.Add(dr);
for (int col = 0; col < dsGetData.Tables[0].Columns.Count; col++)
{
if (row == 0)
{
DataColumn dc = new DataColumn(dsGetData.Tables[0].Columns[col].ColumnName.ToString());
dsImport.Tables[0].Columns.Add(dc);
}
if (!String.IsNullOrEmpty(dsGetData.Tables[0].Rows[row][col].
ToString()))
{
dsImport.Tables[0].Rows[row][col] = Convert.ToString(dsGetData.Tables[0].Rows[row][col].ToString().Trim());
}
} // close inner for loop
}// close ouer for loop
MessageBox.Show("Import done Successfully to DBF File.");
Return dsImport;
} // close function
Note: Same function you can use to Export Import from Excel file also
Use
string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0";
// filePath = complete path like "C:\Temp\country.xls"

Import Excel

string strconn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + filePath + ";Extended Properties=Excel 8.0";
OleDbDataAdapter da = new OleDbDataAdapter("select * from [" + fileName + "$]", strconn);
DataSet dsExcel = new DataSet();
da.Fill(dsExcel);

Export Excel

Same as Export DBF file with different connection string.
string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0";
// filePath = complete path like "C:\Temp\country.xls"

Windows Requirements
Windows 2000, XP and above
Language
C#.NET and ASP.NET, Visual Studio 2003 and above

Thursday, June 02, 2011

How to read Excel XML file from .NET (C#.NET) application using System.Xml.Linq

How to read Excel XML file from .NET (C#.NET) application using System.Xml.Linq
If client machine does not have Excel installed then you have to use Excel XML file for any read/write operation. You can create XML file from excel (save as "XML Spreadsheet 2003") and read from .NET application using System.Xml.Linq library.

Let say you have some Excel XML template on the server location where MS office not installed and you have to read data from Excel XML template then you can do it by using System.Xml.Linq library, actually this is another way to read Excel file
Below source code will give demo to read data from Excel XML by using XDocument
NameSpace
using System.Xml.Linq;
using System.IO;

Source Code
private void button1_Click(object sender, RoutedEventArgs e)
{
  //for silverlight application File operation not allow from client side
  // so we have to give option to select Excel xml file from client
  // you can use file path if it is fixed file from server location
  
  OpenFileDialog dlg = new OpenFileDialog();
  dlg.Multiselect = false;
  dlg.Filter = "Excel XML Files (*.xml)|*.xml";

  //Open saved excel file into XML format (select Save as
  //Type "XML Spreadsheet 2003")
  bool? userClickedOK = dlg.ShowDialog();
  string xml = string.Empty;

  //If user select correct file then procced
  if (userClickedOK == true)
  {
     FileInfo info = dlg.File;
     Stream s = info.OpenRead();
     StreamReader reader = new StreamReader(s);
     xml = reader.ReadToEnd();
     reader.Close();
 }
 else
 {
    //log error or display message for user to select file
    return;
 }

 // load xml into XDocumemt object
 XDocument doc = XDocument.Parse(xml);

 //Query for Sheet1 from selected Worksheet
 var worksheet = doc.Descendants().Where(x => (x.Name.LocalName == "Worksheet" && x.FirstAttribute.Value == "Sheet1"));
 //Query for Filled rows
 var rows = worksheet.Descendants().Where(x => x.Name.LocalName == "Row");

 int rowCount = 0;

 foreach (var row in rows)
 {
    if (String.IsNullOrEmpty(row.Value))
    {
       continue;
    }
   else
   {
     //I am allowing max 100 rows to read from sheet1
      if (rowCount < 100)
      {
         //validating the header row, I am assuming very
       // First row will be the HEADER
       if (rowCount == 0)
       {
        //Read first row first cell value
       if (string.Compare(row.Descendants().Elements().ElementAt(0).Value,
"Emp Number", StringComparison.CurrentCultureIgnoreCase) != 0
                        && string.Compare(row.Descendants().Elements().ElementAt(1).Value,
"Emp Salary", StringComparison.CurrentCultureIgnoreCase) != 0
                        && string.Compare(row.Descendants().Elements().ElementAt(2).Value,
"Date of Birth", StringComparison.CurrentCultureIgnoreCase) != 0)
 {
   //return error for Invalid excel
   break;
 }
}
else
{
    //validating records
    var cell = row.Descendants().Where(y => y.Name.LocalName == "Cell");

//Here you will get all value from excel,you need to check for Empty or null value
//NOTE: If cell is empty then you will not get cell number here like you do not have any value filled on cell 2 then you will not get cell.ElementAt(2), this will be NULL to overcome this you can fill empty string (blank value) into the cell

  string empNo = cell.ElementAt(0).Value; //Result will be 100, 200
  string empSal = cell.ElementAt(1).Value;//Result will be 2000, 3000
  string empDOB = cell.ElementAt(2).Value;//Result will be 10/10/1987, 1/1/1999, convert into desired date    format
}
 rowCount++;
}
else
{
    //return error for Invalid excel
    break;
}
}
} 
}

Assume you have data into Excel XML like below and Saved as "XML Spreadsheet 2003" format "Book1.xml"

Emp Number                     Emp Salary          Date of Birth
100                                         2000                       10/10/1987
200                                         3000                       1/1/1999