Insert/upadte thousands of records in the table from an excel file depending on some business logic.  
Author Message
TallMike





PostPosted: Visual C# General, Insert/upadte thousands of records in the table from an excel file depending on some business logic. Top

OK, I used a dataadapter to load a large dataset with Excel data (I will eventually do some data validation on it).

Now I want to insert this data into a SQL server 2005 database table.

I see lots of examples loading single records, but what about a complete datatable

Is it possible to use a stored procedure to load hundreds of records

Any links, samples or general advice appreciated

Thanks,

Mike



Visual C#19  
 
 
David Hayden





PostPosted: Visual C# General, Insert/upadte thousands of records in the table from an excel file depending on some business logic. Top

I would read the excel spreadsheet in using an ADO.NET DataReader and upload it via SqlBulkCopy:

Check out this tutorial:

Import Excel Spreadsheet Data into SQL Server Database Table Using SqlBulkCopy

Here is some sample code:

// Connection String to Excel Workbook

// Create Connection to Excel Workbook
using (OleDbConnection connection = new OleDbConnection(excelConnectionString))
{
OleDbCommand command = new OleDbCommand ("Select ID,Data FROM [Data$]", connection);

connection.Open();

// Create DbDataReader to Data Worksheet
using (DbDataReader dr = command.ExecuteReader())
{
// SQL Server Connection String
string sqlConnectionString = "Data Source=.;Initial Catalog=Test;Integrated Security=True";

// Bulk Copy to SQL Server
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
{
bulkCopy.DestinationTableName = "ExcelData";
bulkCopy.WriteToServer(dr);
}
}
}

Here are some other tutorials that may be of use:

SqlBulkCopy - Copy Table Data Between SQL Servers at High Speeds - ADO.NET 2.0 New Feature

Reading Excel Worksheet and Column Schema Information Using ADO.NET 2.0 and GetSchema

Reading and Writing Excel Spreadsheets / Worksheets Using ADO.NET C# DbProviderFactory

Regards,

Dave



 
 
cverdon





PostPosted: Visual C# General, Insert/upadte thousands of records in the table from an excel file depending on some business logic. Top

Hi,

If it's only hundreds of records calling Update on the table adapter should be performant enough. You can set the update method of the dataset to be a stored procedure if you want - records will still be processed one after the other.

Otherwise you can look into the SQL Server Bulk insert (http://www.sqlteam.com/item.asp ItemID=3207) or sql bulk copy (http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx).

Good luck,
Charles


 
 
TallMike





PostPosted: Visual C# General, Insert/upadte thousands of records in the table from an excel file depending on some business logic. Top

Thanks Charles, I try it
 
 
VMazur





PostPosted: Visual C# General, Insert/upadte thousands of records in the table from an excel file depending on some business logic. Top

Do you need to update Excel file or move data from Excel into SQL Server table If you need to move data into SQL Server, you could use SQL DTS, which is really fast.

 
 
Suren...





PostPosted: Visual C# General, Insert/upadte thousands of records in the table from an excel file depending on some business logic. Top

Hi,

Thanks for reply.

But prior to loading the data want to apply some business logic.

I want to check for the duplicate records in the excel file as well in the database table for the same.

If the records are duplicate I dont want to insert that record again.

Regards,

Suren...



 
 
Mr Surendra Holambe





PostPosted: Visual C# General, Insert/upadte thousands of records in the table from an excel file depending on some business logic. Top

Hi,

I have an excel file with thousands of records which contains Employee data.

Depending on some business logic I want to upadate/insert the records in a table "Employees".

Is there any way to do this with little performance impact

Please reply...

Thanks in advance,

Suren....



 
 
VMazur





PostPosted: Visual C# General, Insert/upadte thousands of records in the table from an excel file depending on some business logic. Top

You can put logic into DTS packages as well.

 
 
SteveDrake





PostPosted: Visual C# General, Insert/upadte thousands of records in the table from an excel file depending on some business logic. Top

Our installer loads and entire database from datasets that are stored as XML.

It works really well, we don’t use stored procs as its a one off for our installer.

But, if you do use stored procs then it’s going to be even faster.

If you loading 'loads' of data, do you want one transaction Or lots of mini ones if you application is going to be in use then your 'load' may block the usage unless data is read with (NOLOCK).

If you want to example from our installer, just ask.


 
 
Mr Surendra Holambe





PostPosted: Visual C# General, Insert/upadte thousands of records in the table from an excel file depending on some business logic. Top

Hi Steve,

Thanks for your reply.

I am doing it in one transaction.

It would be glad if you provide the sample example.

Cheers,

Suren...



 
 
Suren...





PostPosted: Visual C# General, Insert/upadte thousands of records in the table from an excel file depending on some business logic. Top

How will I do it using SQL Server 2005

 
 
SteveDrake





PostPosted: Visual C# General, Insert/upadte thousands of records in the table from an excel file depending on some business logic. Top

This is snipped from our installer code, I have not tested it on its own.

This is used to load an entire database from XML, so it deletes the rows first, so you dont trash your database i have commented this part of the code out.

It gets the table list from the database schema not the dataset.

 

    public void LoadXMLIntoDataBase(string xmlFile, string databaseConnection)
    {
        using (SqlConnection connection = new SqlConnection(databaseConnection))
        {
            connection.Open();
            // First things first let's make sure we have the xml file...
            if (!File.Exists(xmlFile))
            {
                throw new ApplicationException("No valid xml file found!");
            }
            // Load in our xml file into a dataset...
            DataSet ds = new DataSet();
            ds.ReadXml(xmlFile);

            // Holds the names of all the tables in the database...
            ArrayList tables = new ArrayList();

            // Get a list of tables in the database...
            using (SqlCommand cmd = new SqlCommand("SELECT name FROM sys.objects WHERE type = 'U'", connection))
            {
                using (SqlDataReader rdr = cmd.ExecuteReader())
                {
                    while (rdr.Read())
                    {
                        tables.Add(rdr.GetString(0));
                    }
                    rdr.Close();
                }
                cmd.Dispose();
            }

            // Remove check constraints from and allow identity inserts into all tables...
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.Connection = connection;
                foreach (string table in tables)
                {
                    cmd.CommandText = string.Format("ALTER TABLE [{0}] NOCHECK CONSTRAINT ALL", table);
                    cmd.ExecuteScalar();
                    try
                    {
                        cmd.CommandText = string.Format("SET IDENTITY_INSERT [{0}] ON ", table);
                        cmd.ExecuteScalar();
                    }
                    catch { /* IGNORED! */ }
                }
                cmd.Dispose();
            }

            foreach (DataTable dt in ds.Tables)
            {
                // Tables that are to be ignored...
                if (dt.TableName.ToLower() == "dtproperties")
                {
                    continue;
                }

                // Right we've got a table to do something with...                   

                // Delete all contents...
/* PUT ME BACK IN IF YOU WISH

                using (SqlCommand cmd = new SqlCommand(string.Format("DELETE FROM {0}", dt.TableName), connection))
                {
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();
                }

*/

                using (SqlDataAdapter dataAdapter = new SqlDataAdapter(new SqlCommand(string.Format("SELECT * FROM {0}", dt.TableName), connection)))
                {
                    using (SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(dataAdapter))
                    {
                        cmdBuilder.QuotePrefix = "[";
                        cmdBuilder.QuoteSuffix = "]";

                        dataAdapter.InsertCommand = cmdBuilder.GetInsertCommand();

                        // Check that our columns match up...
                        DataColumnCollection columns = dt.Columns;
                        if (columns.Count != dataAdapter.InsertCommand.Parameters.Count)
                        {
                            int z = 0;
                            foreach (DataColumn column in columns)
                            {
                                // Does this exist
                                bool found = false;
                                foreach (SqlParameter param in dataAdapter.InsertCommand.Parameters)
                                {
                                    if (string.Compare(param.SourceColumn, column.ColumnName, true) == 0)
                                    {
                                        found = true;
                                        break;
                                    }
                                }

                                if (!found)
                                {
                                    // Lets add this
                                    string cmd = dataAdapter.InsertCommand.CommandText;
                                    cmd = cmd.Replace(string.Format("[{0}](", dt.TableName), string.Format("[{0}]({1}, ", dt.TableName, column.ColumnName));

                                    SqlParameter param = new SqlParameter();

                                    param.SourceColumn = column.ColumnName;

                                    dataAdapter.InsertCommand.CommandText = cmd;
                                    dataAdapter.InsertCommand.Parameters.Add(param);

                                    z++;
                                }
                            }

                            foreach (SqlParameter param in dataAdapter.InsertCommand.Parameters)
                            {
                                if (param.SourceColumn == null || param.SourceColumn == "")
                                {
                                    throw new ApplicationException(string.Format("Parameter {0} has not been assigned", param.ParameterName));
                                }

                                if (!columns.Contains(param.SourceColumn))
                                {
                                    columns.Add(param.SourceColumn);
                                }
                            }
                        }

                        dataAdapter.TableMappings.Clear();
                        dataAdapter.TableMappings.Add("Table", dt.TableName);

                        try
                        {
                            dataAdapter.Update(ds);
                        }
                        catch (SqlException e)
                        {
                            throw new
                                ApplicationException
                                (
                                    string.Format
                                    (
                                        "SQL Exception populating table {0}\n\nCommand:\n'{2}'\n\n{1}",
                                        dt.TableName,
                                        e.Message,
                                        dataAdapter.InsertCommand.CommandText
                                    )
                                );
                        }
                    }
                }
            }
            // Ensure check constraints and identity insert off for all tables...
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.Connection = connection;

                foreach (string table in tables)
                {
                    cmd.CommandText = string.Format("ALTER TABLE [{0}] CHECK CONSTRAINT ALL", table);
                    cmd.ExecuteScalar();

                    try
                    {
                        cmd.CommandText = string.Format("SET IDENTITY_INSERT [{0}] OFF", table);
                        cmd.ExecuteScalar();
                    }
                    catch { /* IGNORED! */ }
                }
                cmd.Dispose();
            }
        }
    }