What is the best approach to Update SQL Server 2005 with large dataset using dataadapter  
Author Message
TallMike





PostPosted: Visual C# General, What is the best approach to Update SQL Server 2005 with large dataset using dataadapter 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#17  
 
 
cverdon





PostPosted: Visual C# General, What is the best approach to Update SQL Server 2005 with large dataset using dataadapter 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, What is the best approach to Update SQL Server 2005 with large dataset using dataadapter Top

Thanks Charles, I try it
 
 
Mr Surendra Holambe





PostPosted: Visual C# General, What is the best approach to Update SQL Server 2005 with large dataset using dataadapter 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....



 
 
SteveDrake





PostPosted: Visual C# General, What is the best approach to Update SQL Server 2005 with large dataset using dataadapter 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, What is the best approach to Update SQL Server 2005 with large dataset using dataadapter 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...



 
 
SteveDrake





PostPosted: Visual C# General, What is the best approach to Update SQL Server 2005 with large dataset using dataadapter 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();
            }
        }
    }