Archive for April, 2011

April 26, 2011

Dev Express v10.1 Compilation Error Message: CS0012

CS0012: The type ‘DevExpress.Utils.IAssignableCollection’ is defined in an assembly that is not referenced. You must add a reference to assembly ‘DevExpress.Data.v10.1, Version=10.1.6.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a’.

check your web.conf file whether the said “DevExpress.Data.v10.1, Version=10.1.6.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a” entry exists or not. if not just add and recompile. Usually when you add any DevExpress controls to your form Visual Stuido automatically adds the entries as above. if you replaced the web.conf file with another due to so many reasons this used to happen.

April 25, 2011

Microsoft SQL Server Sample Databases

you can download the sample databases for SQL Server from codeplex.

http://sqlserversamples.codeplex.com

April 6, 2011

Import data from Excel (.xls/xlsx)

If you need to read data from Microsoft Excel file you can use the following code snippet.

 private void LoadExcelData()
 {
 try
 {
 string strPath = lblExcelPath.Text.Trim();

 if (strPath != "")
 {

 string strConn = GetConnString(strPath);
 string strExcelSheetName = ddlTemplates.SelectedItem.ToString();
 OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [" + GetExcelSheetNames(strPath)[0] + "]", strConn);
 DataSet myDataSet = new DataSet();
 myCommand.Fill(myDataSet, "ExcelInfo");
 Session["myDataSet"] = myDataSet;
 GridView1.DataSource = myDataSet.Tables["ExcelInfo"].DefaultView;
 GridView1.DataBind();
 }
 else
 {
DisplayUIMessage("Select a excel file to Upload", MessageType.Information);
 }
 }
 catch (Exception ee)
 {
 LoggingProvider.Log(ee.Message, TraceEventType.Error, ApplicationLayer.UserInterface, LoggingMode.Debug);
 return;
 }
 }

the way to read data from a excel sheet.

 private static String[] GetExcelSheetNames(string excelFile)
 {
 OleDbConnection objConn = null; System.Data.DataTable dtExcel = null;
 try
 {
 String connString = GetConnString(excelFile);
 objConn = new OleDbConnection(connString);
 objConn.Open();
 dtExcel = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
 if (dtExcel == null)
 {
 return null;
 }
 String[] excelSheets = new String[dtExcel.Rows.Count];
 int i = 0;
 foreach (DataRow row in dtExcel.Rows)
 {
 excelSheets[i] = row["TABLE_NAME"].ToString();
 i++;
 }
 return excelSheets;
 }
 catch (Exception ex)
 {
 return null;
 }
 finally
 {
 if (objConn != null)
 {
 objConn.Close();
 objConn.Dispose();
 }
 if (dtExcel != null)
 {
 dtExcel.Dispose();
 }
 }
 }

As well as as i had to deal with both MS Excel 2003 and 2010 formats file and was using the following method to pick the proper connectionstring.


private static String GetConnString(string excelFilePath)
 {
 String connString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelFilePath + ";Extended Properties=Excel 8.0;";

 if (excelFilePath.Contains("xlsx"))
 {
 connString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + excelFilePath + ";Extended Properties='Excel 12.0 Xml; HDR=YES; IMEX=1';";
 }
 return connString;
 }

Usually when you specify connectionstring to load a excel file, the header columns in the excel file will be the Columns Names in your Dataset. But when you used the following connectionstring the header columns will be returned as the first data row of the dataset. for that you have to set the HDR value to NO.


private static String GetHeaderConnString(string excelFilePath)
 {
 String connString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + excelFilePath + ";Extended Properties='Excel 12.0 Xml; HDR=NO; IMEX=1';";
 return connString;
 }