Import Data to Sql From Excel Using C#

Question

0.00/5 (No votes)

See more:

SQL-Server-2014

Hi

I want to Import data From Excel To Sql Server , The Problem I face that I dont Want to Insert Id Column From Excel I use the OrderBy Stored Procedure To Insert Id But I Unbale to do that

Here The Code

C#

//Upload and save the file
string excelPath = Server.MapPath("~/") + Path.GetFileName(FileUpload1.PostedFile.FileName);
FileUpload1.SaveAs(excelPath);

string conString = string.Empty;
string extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
switch (extension)
{
    case ".xls": //Excel 97-03
        conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
        break;
    case ".xlsx": //Excel 07 or higher
        conString = ConfigurationManager.ConnectionStrings["Excel07+ConString"].ConnectionString;
        break;

}
conString = string.Format(conString, excelPath);
using (OleDbConnection excel_con = new OleDbConnection(conString))
{
    excel_con.Open();
    string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();
    DataTable dtExcelData = new DataTable();

    //[OPTIONAL]: It is recommended as otherwise the data will be considered as String by default.
    dtExcelData.Columns.AddRange(new DataColumn[3] { new DataColumn("Id", typeof(Int64)),
        new DataColumn("Name", typeof(string)),
        new DataColumn("Summary",typeof(string)) });

    using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT * FROM [" + sheet1 + "]", excel_con))
    {
        oda.Fill(dtExcelData);
    }
    excel_con.Close();

    string consString = ConfigurationManager.ConnectionStrings["Practice_One"].ConnectionString;
    using (SqlConnection con = new SqlConnection(consString))
    {
        using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
        {
            //Set the database table name
            sqlBulkCopy.DestinationTableName = "dbo.Practice_One";

            //[OPTIONAL]: Map the Excel columns with that of the database table
            sqlBulkCopy.ColumnMappings.Add("Id", "Id");
            Practice_One One = new Practice_One();
            One.Order_By_Practice_One(ref consString, out Id);
            Id = Convert.ToInt64(Id + 1);
            sqlBulkCopy.ColumnMappings.Add("Name", "Name");
            sqlBulkCopy.ColumnMappings.Add("Summary", "Summary");
            con.Open();
            sqlBulkCopy.WriteToServer(dtExcelData);
            con.Close();
        }
    }
}

}

I want To use this Class to Check last Id and Increment according to that

C#

Practice_One One = new Practice_One();
One.Order_By_Practice_One(ref consString, out Id);
Id = Convert.ToInt64(Id + 1);

Posted 4-Jun-15 19:45pm

Gurpreet Arora Malhotra

Updated 4-Jun-15 20:49pm

Tomas Takac

v3

Add a Solution

Comments

Sinisa Hajnal 5-Jun-15 6:16am

Since you are using bulk insert, your approach is wrong. To be able to do max +1 logic you would have to go row by row insert. Instead see if you can make calculated column in dtExcelData with starting number you get from the database.

Gurpreet Arora Malhotra 5-Jun-15 8:31am

How I insert data row by row

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

RDBurmon · Accepted Answer · 2015-06-05T17:52:00

Here is the code -

C#

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.IO;
using System.Data;

namespace _997998_Import_Data_to_Sql_Using_Excel
{
    class Program
    {
        static void Main(string[] args)
        {
            //Upload and save the file
            string ExcelPath = "test.xls";
            string conString = string.Empty;
            //string extension = Path.GetExtension(ExcelPath);

            conString = String.Concat("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" , ExcelPath.ToString() , ";Extended Properties='Excel 12.0 Xml;HDR=YES';");
            using (OleDbConnection excel_con = new OleDbConnection(conString))
            {
                excel_con.Open();
                string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();
                DataTable dtExcelData = new DataTable();

                using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT * FROM [" + sheet1 + "]", excel_con))
                {
                    oda.Fill(dtExcelData);
                }
                excel_con.Close();

                Connect();

                string sql1;
                    foreach (DataRow row in dtExcelData.Rows)
                    {
                        sql1 = String.Concat("INSERT INTO Practice_one SELECT (SELECT Isnull(MAX(Id),0) FROM Practice_one) + 1 ,'", row[1].ToString(), "','", row[2].ToString() , "'" );
                        OleDbCommand insertCommand1 = new OleDbCommand(sql1, Conn);
                        int count = insertCommand1.ExecuteNonQuery();
                    }
                
            }


        }
        public static OleDbConnection Conn = new OleDbConnection();

        public static bool Connect()
        {

            string conn = "PROVIDER=SQLOLEDB;Data Source=RAHUL-PC;Initial Catalog=Test;UserId=sa;Password=sa123;Integrated Security=SSPI";
            
            if (Conn.State == System.Data.ConnectionState.Open)
                Conn.Close();
            Conn.ConnectionString = conn;
            Conn.Open();
            return true;

        }
    }

}