CODEWITHSUNDEEP
c#asp.net
nshull16
nshull16

Reputation: 1

How do I use a filepath in my connection string?

I'm currently creating a web application to take in an excel file and import the data into a database. My current connection string is as follows

string connStringExcel = @"Provider=Microsoft.ACE.OLEDB.12.0;Data 
Source=C:\users\myname\downloads\example.xlsx;Extended 
Properties=""Excel 
12.0;HDR=YES;""";

I've tried to insert a fileupload control, and then find the path of the file using:

string filePath = 
System.IO.Path.GetFullPath(fileUpload.PostedFile.FileName);

and then replacing the full file path in my connection string with just the filePath variable. What can I do to choose my own file, so that it doesn't have to be hard coded in?

Errors

Upvotes: 0

Views: 1247

Answers (3)

Владимир Мардеев
Владимир Мардеев

Reputation: 11

That was helpful for me:

Private Sub Workbook_Open()    
    Dim oCN As WorkbookConnection
    
    'Loop through all the Workbook Connections
    For Each oCN In ThisWorkbook.Connections
        oCN.OLEDBConnection.Connection = "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=" + ThisWorkbook.Path + "\Database10.accdb;Mode=ReadWrite;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=6;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False;Jet OLEDB:Limited DB Caching=False;Jet OLEDB:Bypass ChoiceField Validation=False"
    Next 'Connections
    
    ThisWorkbook.RefreshAll
End Sub

In the string oCN.OLEDBConnection.Connection = ... I took original connection string and just replaced path with that construction Data Source=" + ThisWorkbook.Path + "\Database10.accdb

Upvotes: 1

Trey
Trey

Reputation: 413

string filePath = 
System.IO.Path.GetFullPath(fileUpload.PostedFile.FileName);
string connStringExcel = $"Provider=Microsoft.ACE.OLEDB.12.0;Data 
Source={filePath};Extended 
Properties='Excel 
12.0;HDR=YES;'";

Upvotes: 2

Frost_Mourne
Frost_Mourne

Reputation: 342

 <asp:FileUpload ID="fuManual" Enabled="false" runat="server"  />
  <asp:Button ID="btnUploadDoc" Text="Upload" runat="server" OnClick="UploadDocument"  />



     public void UploadDocument(object sender, EventArgs e)
        {
        try
        {

            if (fuManual.HasFile)
            {

                string manual_filename = ddlStore.SelectedItem.Text + "_" + "Manual.xlsx";

                string extension = Path.GetExtension(fuManual.PostedFile.FileName);

                // Import Excel Code for Manual Excel Data import....
                string FilePath = Server.MapPath("~/Temp/" + manual_filename);
                fuManual.SaveAs(FilePath);
                System.Data.OleDb.OleDbConnection myConnection = new System.Data.OleDb.OleDbConnection(
                            "Provider=Microsoft.ACE.OLEDB.12.0; " +
                             "data source='" + FilePath + "';" +
                                "Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\" ");
                myConnection.Open();

                DataTable mySheets = myConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                DataSet ds_manualData = new DataSet();
                DataTable dt_manualData;

                for (int i = 0; i < mySheets.Rows.Count; i++)
                {
                    dt_manualData = makeDataTableFromSheetName(FilePath, mySheets.Rows[i]["TABLE_NAME"].ToString());
                    ds_manualData.Tables.Add(dt_manualData);
                }


                ViewState["ManualData"] = ds_manualData;
                DataTable dts = new DataTable();
                dts = ds_manualData.Tables[0];
                //here u have the whole excel file in the data table now send it to the database  
               //remember to send table u need SqlDbType-structured

               myConnection.Close();
            }
            else
            {
                lblError.Text = "please Select the file to be uploaded";
            }
        }
        catch (Exception ex)
        {
        }                                    
    }
    private static DataTable makeDataTableFromSheetName(string FilePath, string sheetName)
    {
        System.Data.OleDb.OleDbConnection myConnection = new System.Data.OleDb.OleDbConnection(
        "Provider=Microsoft.ACE.OLEDB.12.0; " +
        "data source='" + FilePath + "';" +
        "Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\" ");

        DataTable dtImport = new DataTable();
        System.Data.OleDb.OleDbDataAdapter myImportCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [" + sheetName + "]", myConnection);
        myImportCommand.Fill(dtImport);
        return dtImport;

    }

Upvotes: 0

Related Questions