[krista80]

Hi. I'm creating a program that allows a user to browse for a file using a FileUpload control. The file type is either csv, xls, or xlsx. The information in that file is then processed and saved to an SQL Server 2005 database temporary table where it is then tested against other tables in the database to see if the information in the file is in one of the other tables. If it is, the program does nothing with that information. If it is not found, the information is then posted to a gridview on another page for the user to see. The project is being made using Visual Studio 2010, ASP.NET, C#, and SQL Server 2005.

My issue is I have searched all over, and one forum told me what I wanted to do couldn't be done without a third party program, while other forums just don't answer my questions. So I am hoping I can get some help here. I had no problem working with and processing the CSV file. There was lots of help for that one. And I was able to find out how to connect to the excel file using OleDbConnection. The coding I have done so far looks like this:

 protected void bttnValidate_Click(object sender, EventArgs e)
       {
         if (docCSV.HasFile)
         {
           if (CheckFileType(docCSV.FileName))
           {
             string fileType = Path.GetExtension(docCSV.FileName);
                    
             if (fileType == ".csv")
             { 
               Stream theFile = docCSV.PostedFile.InputStream;
                        
               using (StreamReader sr = new StreamReader(theFile))
               {
                 string line;
                            
                 while ((line = sr.ReadLine()) != null)
                 {
                   string[] tempArray = line.Split(Convert.ToChar(","));
                   LoadDataToDatabase(tempArray);
                 }
                 InsertData();
                 DeleteData();
               }
               Response.Redirect("~/Results.aspx");
             }
             else if (fileType == ".xls")
             {
               string strConnXLS;

               strConnXLS = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;" +
                 "Data Source={0};Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\";", docCSV);

               OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [Template$A10:P51]", strConnXLS);

               DataSet myDS = new DataSet();
               myCommand.Fill(myDS, "ExcelInfo");
                        
             }
             else if (fileType == ".xlsx")
             {
               string strConnXLSX;

               strConnXLSX = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;" +
                 "Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";", docCSV);

               OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [Template$A10:P51]", strConnXLSX);
               DataSet myDS = new DataSet();
               myCommand.Fill(myDS, "ExcelInfo");
             }
             else
             {
               lblInvalidFile.Text = "Invalid file! Please only upload files with extension csv, xls, or xlsx.";
             }
           }
           else
           {
             lblInvalidFile.Text = "Invalid file! Please only upload files with extension csv, xls, or xlsx.";
           }
         }
         else
         {
           lblInvalidFile.Text = "Please choose a csv, xls, or xlsx file to upload before clicking on validate. Thank you.";
         }
       }

The following shows one of the sqlQueries done for the CSV file type.

 private void LoadDataToDatabase(string[] tempArray)
        {
            string sqlTempQuery = string.Empty;
            StringBuilder sbTemp = new StringBuilder();

            sbTemp.AppendFormat(string.Format(" INSERT INTO TempNumHolder "));
            sbTemp.AppendFormat(string.Format(" (SessionID, Temp_FirstNumSet, Temp_SecondNumSet, Temp_ThirdNumSet, Temp_FourthNumSet, Temp_FifthNumSet) "));
            sbTemp.AppendFormat(string.Format(" VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}') ", Session.SessionID, 
                tempArray[0], tempArray[1], tempArray[2], tempArray[3], tempArray[4]));

            sqlTempQuery = sbTemp.ToString();

            
            using (SqlConnection sqlConn = new SqlConnection(connectionString))
            {
                sqlConn.Open();

                SqlCommand sqlCmd = new SqlCommand(sqlTempQuery, sqlConn);
                sqlCmd.ExecuteNonQuery();
                sqlConn.Close();
            }
        }

So how different is the OleDbCommand to the SqlQuery? Can I just simply modify what I already have to make it work for an excel file, as long as I know where the information I need is? If you would like to see the whole code I will attach the .txt file. Any help would be greatly appreciated. And if there are any sites or questions you would recommend, please include them.

Thanks again!

Attached File(s)

•  excelValidation.txt (7.16K)
  Number of downloads: 168

[Ionut]

The main difference between those two is in the matter of performance. SqlClient is optimized to work with SQL Server as is part of it and oledb is generic for all databases. So, if you work with MSSQL, use SQLClient.

Now, about your code.
1. use parameterized queries (Example here). This way you don't make your application vulnerable to sql injections.
The example shows only the usage of a sql command with a select. But an insert statement is the same

INSERT INTO Table(column1, column2) VALUES (@Column1, @column2)
//the rest of the code

2. for xls and xlsx, I find this way easier for file manipulation.
3. use try catch blocks to avoid an ugly crush of the application.

If you have more question, shoot them.

[keakTheGEEK]

One thing that you may consider is that you can actually use the same data provider that you are using for your .xls files to read from your .csv files as well:

//This here
string strConnXLS;
strConnXLS = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source={0};Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\";", docCSV);
OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [Template$A10:P51]", strConnXLS);

Just change the Data Source attribute to point to the directory where the .csv file is located on your server, then set the Extended Properties attribute to "text;HDR=Yes;FMT=Delimited"; So essentially you can set the connection string for the .csv file in your code in this fashion:

strConn = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source={0};Extended Properties=\"{1};HDR=YES;{2}\";", Path.GetDirectoryName(docCSV), "text", "FMT=Delimited");

Then you can select from the .csv file just like you do with an excel spreadsheet, only instead of using the sheet name you use the file name:

 OleDbDataAdapter myCommand = new OleDbDataAdapter(string.Format("SELECT * FROM {0}", Path.GetFileName(docCsv)), strConn);

The benefits of doing it this way will allow you to reuse the same block of code and just pass in variables to fill in the place holders. Not only that, but these lines of code that fill your dataset:

DataSet myDS = new DataSet();
myCommand.Fill(myDS, "ExcelInfo");

This will only need to be called once, since the same dataset can be used for any of the file types.

Another benefit of having the same dataset is that you can also use the same method to upload the data into the table in your database (instead of having a different method for each).

You can use either OLEDB or SQL Data Provider to insert the excel data in your dataset into your SQL Database. Being that you are including this functionality in an ASP .NET web application and inserting the data into an SQL Server 2005 database, using SQL Data Provider would be ideal (as @Ionut points out)

There are actually a few different ways that you could insert the data into your SQL Server 2005 database. One approach is to create a stored procedure in your db that takes an XML data type as a parameter. Then you could call that stored procedure in .NET code and create a parameter of SqlDataType.XML and set that parameter to the dataset as XML using the myDS.GetXML() method.

protected int insertData(string xml)
{
    int rows = 0;
    using (SqlConnection sqlConn = new SqlConnection(connectionString))
    {
               sqlConn.Open();
               SqlCommand sqlCmd = new SqlCommand("stored procedure name", sqlConn);
               sqlCmd.CommandType = CommandType.StoredProcedure;
               sqlCmd.Parameters.Add("@Arg", SqlDbType.Xml).Value = xml;
               rows = sqlCmd.ExecuteNonQuery();
    }
    return rows;
}

//Call this function like so
int affectedRows = insertData(myDS.GetXML());

(*Code not tested)

Another, common approach to upload the data is to create a function that takes a data table as a parameter. Then iterate through each row and insert the records row by row.

private int insertData(DataTable t)
{
    string sql = @"INSERT INTO TempNumHolder 
                   (SessionID,  Temp_FirstNumSet, Temp_SecondNumSet, Temp_ThirdNumSet, Temp_FourthNumSet, Temp_FifthNumSet)
                   VALUES(@SessionID, @Temp_First, @Temp_SecondNumSet, @Temp_ThirdNumSet, @Temp_FourthNumSet, @Temp_FifthNumSet)";
   
    int rows = 0;
     using (SqlConnection sqlConn = new SqlConnection(connectionString))
    {
       sqlConn.Open();
       SqlCommand sqlCmd = new SqlCommand(sql, sqlConn);
        foreach(DataRow r in t.Rows)
        {        
           //Create your parameters and add them to sqlCmd

           //Execute the insert
            sqlCmd.ExecuteNonQuery();
            
           //Then clear all parameters after insert
           sqlCmd.Parameters.Clear();

          rows++;
        }
    }
    return rows;
}

There are few other ways to do this also. Such as using Data Adapters.

Also, I often refer to connectionstrings.com for information on different connection strings.

Hope this helps. Good Luck

[krista80]

Thank you, Ionut and KeakTheGEEK. I'll take a look at the links you posted and let you know how it goes!

[krista80]

Ok. First error. I tried changing this:

 private void LoadDataToDatabase(string[] tempArray)
        {
            string sqlTempQuery = string.Empty;
            StringBuilder sbTemp = new StringBuilder();

            sbTemp.AppendFormat(string.Format(" INSERT INTO TempNumHolder "));
            sbTemp.AppendFormat(string.Format(" (SessionID, Temp_FirstNumSet, Temp_SecondNumSet, Temp_ThirdNumSet, Temp_FourthNumSet, Temp_FifthNumSet) "));
            sbTemp.AppendFormat(string.Format(" VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}') ", Session.SessionID, 
                tempArray[0], tempArray[1], tempArray[2], tempArray[3], tempArray[4]));

            sqlTempQuery = sbTemp.ToString();

            
            using (SqlConnection sqlConn = new SqlConnection(connectionString))
            {
                sqlConn.Open();

                SqlCommand sqlCmd = new SqlCommand(sqlTempQuery, sqlConn);
                sqlCmd.ExecuteNonQuery();
                sqlConn.Close();
            }//end of the using block
        }//end of the LoadDataToDatabase method 

to this:

 private void LoadDataToDatabase(string[] tempArray)
        {
            string sqlTempQuery = string.Empty;
            StringBuilder sbTemp = new StringBuilder();

            SqlParameter par = new SqlParameter();
            par.ParameterName = "@Sess";
            par.Value = Session.SessionID;
            par.ParameterName = "@First";
            par.Value = tempArray[0];
            par.ParameterName = "@Second";
            par.Value = tempArray[1];
            par.ParameterName = "@Third";
            par.Value = tempArray[2];
            par.ParameterName = "@Fourth";
            par.Value = tempArray[3];
            par.ParameterName = "@Fifth";
            par.Value = tempArray[4];

            sbTemp.AppendFormat(string.Format(" INSERT INTO TempNumHolder "));
            sbTemp.AppendFormat(string.Format(" (SessionID, Temp_FirstNumSet, Temp_SecondNumSet, Temp_ThirdNumSet, Temp_FourthNumSet, Temp_FifthNumSet) "));
            sbTemp.AppendFormat(string.Format(" VALUES (@Sess, @First, @Second, @Third, @Fourth, @Fifth) "));

            sqlTempQuery = sbTemp.ToString();

            
            using (SqlConnection sqlConn = new SqlConnection(connectionString))
            {
                sqlConn.Open();

                SqlCommand sqlCmd = new SqlCommand(sqlTempQuery, sqlConn);
                sqlCmd.Parameters.Add(par);
                sqlCmd.ExecuteNonQuery();
                sqlConn.Close();
            }//end of the using block
        }//end of the LoadDataToDatabase method 

I am getting an error on the sqlCmd.ExecuteNonQuery(); line that says "Must declare the scalar variable '@Sess'. I tried changing the @Sess back to how it was before, and the @First threw up the same error. If I put single quotes around the @Sess and the others in the string builder, it of course simply saved the @Sess and the others to the database. How can I fix this?

This post has been edited by krista80: 23 February 2011 - 07:43 AM

[krista80]

Nevermind this one. I fixed it. Ok. Now onto the next query.

krista80, on 23 February 2011 - 07:35 AM, said:

Ok. First error. I tried changing this:

 private void LoadDataToDatabase(string[] tempArray)
        {
            string sqlTempQuery = string.Empty;
            StringBuilder sbTemp = new StringBuilder();

            sbTemp.AppendFormat(string.Format(" INSERT INTO TempNumHolder "));
            sbTemp.AppendFormat(string.Format(" (SessionID, Temp_FirstNumSet, Temp_SecondNumSet, Temp_ThirdNumSet, Temp_FourthNumSet, Temp_FifthNumSet) "));
            sbTemp.AppendFormat(string.Format(" VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}') ", Session.SessionID, 
                tempArray[0], tempArray[1], tempArray[2], tempArray[3], tempArray[4]));

            sqlTempQuery = sbTemp.ToString();

            
            using (SqlConnection sqlConn = new SqlConnection(connectionString))
            {
                sqlConn.Open();

                SqlCommand sqlCmd = new SqlCommand(sqlTempQuery, sqlConn);
                sqlCmd.ExecuteNonQuery();
                sqlConn.Close();
            }//end of the using block
        }//end of the LoadDataToDatabase method 

to this:

 private void LoadDataToDatabase(string[] tempArray)
        {
            string sqlTempQuery = string.Empty;
            StringBuilder sbTemp = new StringBuilder();

            SqlParameter par = new SqlParameter();
            par.ParameterName = "@Sess";
            par.Value = Session.SessionID;
            par.ParameterName = "@First";
            par.Value = tempArray[0];
            par.ParameterName = "@Second";
            par.Value = tempArray[1];
            par.ParameterName = "@Third";
            par.Value = tempArray[2];
            par.ParameterName = "@Fourth";
            par.Value = tempArray[3];
            par.ParameterName = "@Fifth";
            par.Value = tempArray[4];

            sbTemp.AppendFormat(string.Format(" INSERT INTO TempNumHolder "));
            sbTemp.AppendFormat(string.Format(" (SessionID, Temp_FirstNumSet, Temp_SecondNumSet, Temp_ThirdNumSet, Temp_FourthNumSet, Temp_FifthNumSet) "));
            sbTemp.AppendFormat(string.Format(" VALUES (@Sess, @First, @Second, @Third, @Fourth, @Fifth) "));

            sqlTempQuery = sbTemp.ToString();

            
            using (SqlConnection sqlConn = new SqlConnection(connectionString))
            {
                sqlConn.Open();

                SqlCommand sqlCmd = new SqlCommand(sqlTempQuery, sqlConn);
                sqlCmd.Parameters.Add(par);
                sqlCmd.ExecuteNonQuery();
                sqlConn.Close();
            }//end of the using block
        }//end of the LoadDataToDatabase method 

I am getting an error on the sqlCmd.ExecuteNonQuery(); line that says "Must declare the scalar variable '@Sess'. I tried changing the @Sess back to how it was before, and the @First threw up the same error. If I put single quotes around the @Sess and the others in the string builder, it of course simply saved the @Sess and the others to the database. How can I fix this?

[krista80]

Ok next question. How do I get this line:

strConnXLS = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;" +
                            "Data Source={0};Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\";", docCSV);

to work with my FileUpload control?

I have it like that now, but it keeps saying that Microsoft Jet database engine can't find the object. Although it is giving me the error on the "myCommand.Fill(myDs, "ExcelInfo");" line.

The whole error message: The Microsoft Jet database engine could not find the object 'C:\Program Files (x86)\Common Files\Microsoft Shared\DevServer\10.0\System.Web.UI.WebControls.FileUpload'. Make sure the object exists and that you spell its name and the path name correctly.

Do I need to download something to get it to work or is there something else I can do?

[Ionut]

Add a reference to "Microsoft Excel Object 8.0". If you have MsExcel installed it should appear under COM tab in Add reference.

[krista80]

I did not have the Microsoft Excel Object 8.0 in the COM list. I only had 12.0 and 5.0. I've added both of them, but still get the same error. I have Microsoft Office Excel 2007 installed on my system. Do I need to install something else?

[Ionut]

Can you show me the snippet where you use FileUpload? I miss it from the above posts.

[krista80]

No problem. Here is the ASP.NET code:

<%@ Page Title="" Language="C#" MasterPageFile="~/ValidationSite.Master" AutoEventWireup="true" CodeBehind="ValidateCCComboForm.aspx.cs" Inherits="ValidationProject.ValidateCCComboForm" %>
<asp:Content ID="Content1" ContentPlaceHolderID="HeadContent" runat="server">
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" runat="server">
  <h2>
    Please choose a csv, xls, or xlsx file to upload for validation:
  </h2>
  <br />
  <asp:FileUpload ID="docCSV" runat="server" />
  <br /><br />
  <asp:Button ID="bttnValidate" runat="server" Text="Validate" Width="200px" 
        onclick="bttnValidate_Click" />
  <asp:Label ID="lblInvalidFile" runat="server" Text="" Font-Size="XX-Large" ForeColor="Red"></asp:Label>
</asp:Content>

and the code where docCSV is used:

protected void bttnValidate_Click(object sender, EventArgs e)
        {
            if (docCSV.HasFile)
            {
                if (CheckFileType(docCSV.FileName))
                {
                    string fileType = Path.GetExtension(docCSV.FileName);

                    
                    if (fileType == ".csv")
                    { 
                        Stream theFile = docCSV.PostedFile.InputStream;
                        
                        using (StreamReader sr = new StreamReader(theFile))
                        {
                            string line;
                            
                            while ((line = sr.ReadLine()) != null)
                            {
                                string[] tempArray = line.Split(Convert.ToChar(","));
                                LoadDataToDatabase(tempArray);
                            }//end of while loop
                            InsertData();
                            DeleteData();
                        }//end of using
                        Response.Redirect("~/Results.aspx");
                    }//end of if (fileType == ".csv")
                    else if (fileType == ".xls")
                    {
                        string strConnXLS;

                        strConnXLS = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;" +
                            "Data Source={0};Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\";", docCSV);

                        OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [Template$A10:P51]", strConnXLS);

                        DataSet myDS = new DataSet();
                        myCommand.Fill(myDS, "ExcelInfo");
                        
                    }//end of else if (fileType == ".xls")
                    else if (fileType == ".xlsx")
                    {
                        string strConnXLSX;

                        strConnXLSX = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;" +
                            "Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";", docCSV);

                        OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [Template$A10:P51]", strConnXLSX);
                        DataSet myDS = new DataSet();
                        myCommand.Fill(myDS, "ExcelInfo");
                    }//end of else if (fileType == ".xlsx")

From what I know, the lines that use the FileUpload are for csv:

if (docCSV.HasFile)
            {
                if (CheckFileType(docCSV.FileName))
                {
                    string fileType = Path.GetExtension(docCSV.FileName);

                    
                    if (fileType == ".csv")
                    { 
                        Stream theFile = docCSV.PostedFile.InputStream;
                        
                        using (StreamReader sr = new StreamReader(theFile))

For xls:

strConnXLS = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;" +
                            "Data Source={0};Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\";", docCSV);

and for xlsx:

strConnXLSX = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;" +
                            "Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";", docCSV);

I'm guessing I'm probably doing it all wrong for the excel file extensions. I have never worked with excel files before so this really is all new to me.

[krista80]

Ok. I figured out why it was giving me that error. I had to change this"

strConnXLSX = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;" +
	                   "Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";", docCSV);
strConnXLS = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;" +
                           "Data Source={0};Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\";", docCSV);

to this:

strConnXLSX = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;" +
             "Data Source={0};Extended Properties=Excel 12.0 Xml;", docCSV);
strConnXLS = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;" +
                            "Data Source={0};Extended Properties=Excel 8.0;", docCSV);