using System; using System.Collections.Generic; using System.Text; using System.Data.OleDb; using System.Data; namespace ExcelDataAdapter { class Program { static void Main(string[] args) { string xlsFile = @"C:\Temp\AdapterTest.xls"; string xlsSheet = @"Sheet1$"; // HDR=Yes means that the first row in the range is the header row (or field names) by default. // If the first range does not contain headers, you can specify HDR=No in the extended properties in your connection string. string connectionstring = string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes;'", xlsFile); // Create connection OleDbConnection oleDBConnection = new OleDbConnection(connectionstring); // Create the dataadapter with the select to get all rows in in the xls OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT FirstName, LastName, Age FROM [" + xlsSheet + "]", oleDBConnection); // Since there is no pk in Excel, using a command builder will not help here. //OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter); // Create the dataset and fill it by using the adapter. DataSet dataset = new DataSet(); adapter.Fill(dataset);//, "PersonTable"); // Time to create the INSERT/UPDATE commands for the Adapter, // the way to do this is to use parameterized commands. // *** INSERT COMMAND *** adapter.InsertCommand = new OleDbCommand("INSERT INTO [" + xlsSheet + "] (FirstName, LastName, Age) VALUES (?, ?, ?)", oleDBConnection); adapter.InsertCommand.Parameters.Add("@FirstName", OleDbType.VarChar, 255).SourceColumn = "FirstName"; adapter.InsertCommand.Parameters.Add("@LastName", OleDbType.Char, 255).SourceColumn = "LastName"; adapter.InsertCommand.Parameters.Add("@Age", OleDbType.Char, 255).SourceColumn = "Age"; // *** UPDATE COMMAND *** adapter.UpdateCommand = new OleDbCommand("UPDATE [" + xlsSheet + "] SET FirstName = ?, LastName = ?, Age = ?" + " WHERE FirstName = ? AND LastName = ? AND Age = ?", oleDBConnection); adapter.UpdateCommand.Parameters.Add("@FirstName", OleDbType.Char, 255).SourceColumn = "FirstName"; adapter.UpdateCommand.Parameters.Add("@LastName", OleDbType.Char, 255).SourceColumn = "LastName"; adapter.UpdateCommand.Parameters.Add("@Age", OleDbType.Char, 255).SourceColumn = "Age"; // For Updates, we need to provide the old values so that we only update the corresponding row. adapter.UpdateCommand.Parameters.Add("@OldFirstName", OleDbType.Char, 255, "FirstName").SourceVersion = DataRowVersion.Original; adapter.UpdateCommand.Parameters.Add("@OldLastName", OleDbType.Char, 255, "LastName").SourceVersion = DataRowVersion.Original; adapter.UpdateCommand.Parameters.Add("@OldAge", OleDbType.Char, 255, "Age").SourceVersion = DataRowVersion.Original; // Insert a new row DataRow newPersonRow = dataset.Tables[0].NewRow(); newPersonRow["FirstName"] = "New"; newPersonRow["LastName"] = "Person"; newPersonRow["Age"] = "100"; dataset.Tables[0].Rows.Add(newPersonRow); // Updates the first row dataset.Tables[0].Rows[0]["FirstName"] = "Updated"; dataset.Tables[0].Rows[0]["LastName"] = "Person"; dataset.Tables[0].Rows[0]["Age"] = "55"; // Call update on the adapter to save all the changes to the dataset adapter.Update(dataset); } } }