CODEWITHSUNDEEP
c#asp.netsql-serveroledb
HARISH SEKAR
HARISH SEKAR

Reputation: 35

In OLEDB Connection string ,data source is DATASOURCE = {0} What Does it Mean?

I have a aspx page,which allows you to upload an excel template and content from that template are fetched to a datatable and then updated in SQL Server Database. OLEDB data provider is used.

Conn =new OleDbConnection(string.Format("Provider=Microsoft.ACE.OLEDB.12.0; Data Source={0};Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1;'", filename))

In above connection string, data source is given as Data Source ={0} what does it mean?

Here is the code behind for UPLOAD button.

     protected void Button1_Click(object sender, EventArgs e)
    {
        if (FileUpload1.FileName.ToString() == "")
        {
            lblMessage.Text = "Please select the File to Upload";
            lblMessage.ForeColor = System.Drawing.Color.Red;
            return;
        }

        string filename = Server.MapPath("Temp/" + FileUpload1.FileName.ToString());
        FileUpload1.SaveAs(filename);
        HttpPostedFile postedFile = this.FileUpload1.PostedFile;
        OleDbConnection Conn = null;
        OleDbDataAdapter DA;
        DataTable DT = new DataTable();
        DataTable tempDT = new DataTable();
        if (Path.GetExtension(postedFile.FileName) == ".xls")
        {
            Conn = new OleDbConnection(string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1;'", filename));
            OleDbCommand excelCmd = new OleDbCommand();
            string sheet_Name = "";
            excelCmd.Connection = Conn;
            excelCmd.Connection.Open();
            DT = excelCmd.Connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

            sheet_Name = DT.Rows[0]["TABLE_NAME"].ToString();
            excelCmd.Connection.Close();                

            if (sheet_Name == "")
            {
                //Throw Excption if Sheet is not present in file
            }

            DA = new OleDbDataAdapter(string.Format("select * from [{0}]", sheet_Name), Conn);

            DA.Fill(DT);              

            string Qno = string.Empty;
            string Qdesc = string.Empty;
            string Header = string.Empty;
            int Dorder;
            string eAlert = string.Empty;



            foreach (DataRow dr in DT.Rows)
            {
                if (!DT.Columns.Contains("QuestionNo"))
                {
                    lblMessage.Text = "QuestionNo ColumnName Mismatching";
                    lblMessage.ForeColor = System.Drawing.Color.Red;
                    grd_Questions.DataBind();
                    return;
                }
                else if (!DT.Columns.Contains("QuestionDesc"))
                {
                    lblMessage.Text = "QuestionDesc ColumnName Mismatching";
                    lblMessage.ForeColor = System.Drawing.Color.Red;
                    grd_Questions.DataBind();
                    return;
                }
                else if (!DT.Columns.Contains("Type"))
                {
                    lblMessage.Text = "Type ColumnName Mismatching";
                    lblMessage.ForeColor = System.Drawing.Color.Red;
                    grd_Questions.DataBind();
                    return;
                }
                else if (!DT.Columns.Contains("e-Alert"))
                {
                    lblMessage.Text = "e-Alert ColumnName Mismatching";
                    lblMessage.ForeColor = System.Drawing.Color.Red;
                    grd_Questions.DataBind();
                    return;
                }
                else if (!DT.Columns.Contains("DisplayOrder"))
                {
                    lblMessage.Text = "DisplayOrder ColumnName Mismatching";
                    lblMessage.ForeColor = System.Drawing.Color.Red;
                    grd_Questions.DataBind();
                    return;
                }
                else
                {
                    PQ.updateQuestion();
                }

            }

Upvotes: 0

Views: 2100

Answers (1)

Tushit Nagda
Tushit Nagda

Reputation: 118

It means that your data source will be your excel file. The '{0}' part will be replaced by the 'filename' (after Comma part) during execution. So that your excel file will act as a database.

("Provider={0};Data Source={1};Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1;'", Microsoft.ACE.OLEDB.12.0, filename)

This would also work.

Upvotes: 1

Related Questions