C＃问题：我最简单的方法是加载.MDB文件，对其进行更改，并将更改保存回原始文件？ [英] C# Issue: What is the simplest way for me to load a .MDB file, make changes to it, and save the changes back to the original file?
问题描述
我已经广泛研究过Google，没有我的问题的答案。我认为自己是这个特定主题的初学者，所以请不要让答案太复杂 - 我需要最简单的方式来编辑.MDB文件！请提供编程示例。
- 假设我已经连接到DataGrid。如何获取Datagrid所做的更改？我确定这个很简单，可以回答。
- 然后，我需要知道如何使用这个Datatable，将其插入到数据集中，然后取得该数据集并重写.MDB文件。 （如果有一种方式只插入已更改的表格，我更喜欢）。
提前谢谢，让我知道你是否需要更多的信息。这是最后一件事我可能不得不问这个话题...感谢上帝。
编辑：
我正在使用的.mdb是一个 Microsoft Access数据库。（我甚至不知道有多个.mdb文件）
我知道我无法直接通过流程写入.MDB文件或任何东西，但有一种方法，我可以生成一个.MDB文件与DataSet信息已经在其中？或者有一种方法可以将表添加到已经加载到DataGrid中的.MDB文件中。有一种方法！
同样，我需要一种方法来在C＃中进行 / p>
编辑：
好的，我的项目相当大，但我使用一个单独的类文件来处理所有数据库连接。我知道我的设计和来源真的很草率，但它完成了工作。我只是在互联网上找到的例子一样好。
记住，我只是连接到另一种形式的DataGrid。让我知道，如果你想要我的代码从Datagrid的形式（我不知道为什么你会需要它）。 DatabaseHandling.cs处理2 .MDB文件。所以你会看到两个数据集在那里。我将最终使用它来从一个数据集获取表，并将它们放入另一个数据集。我只需要弄清楚如何将这些值保存回到.MDB文件中。
有没有办法这样做？必须有一种方式...
编辑：
从什么我已经研究和阅读了...我认为答案是在我的鼻子下。使用Update（）命令。现在，虽然这样做确实有一个简单的方法，但我仍然没有问题，我没有friggin-clue如何使用这个更新命令。
也许我可以这样设置：
Oledb。 OledbConnection cn = new Oledb.OledbConnection（）;
cn.ConnectionString =Provider = Microsoft.ACE.OLEDB.12.0; Data Source = C：\Staff.mdb;
Oledb.OledbCommand cmd = new Oledb.OledbCommand（cn）;
cmd.CommandText =INSERT INTO Customers（FirstName，LastName）VALUES（@FirstName，@LastName）;
我认为可以这样做，但我不想手动插入任何东西。我想要同时进行这两个操作：
- 获取Datagrid上更改的信息，并更新Access数据库文件（.mdb）我从
- 创建一个函数，允许我从另一个Access数据库文件（.mdb）中获取表，并将其替换为辅助Access数据库文件（.mdb）。这两个文件将使用完全相同的结构，但会有不同的信息。
我希望有人提出一个答案。 ..my项目完成所有等待是一个简单的答案。
提前再次感谢。
编辑：
好的好消息。我已经弄清楚如何查询.mdb文件本身（我想）。这是代码，它不起作用，因为我正在尝试使用的sql命令，因为运行时错误。这将带给我下一个问题。
添加到DatabaseHandling.cs的新功能代码：
$ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $
$ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ FROM [MS Access;+ tableTwoLocation +;]。[+ table +]）; //此查询生成运行时错误
cmdCopyTables.Connection = dataconnectionA;
dataconnectionA.Open（）;
cmdCopyTables.ExecuteNonQuery（）;
dataconnectionA.Close（）;
}
正如你所看到的，我实际上设法执行一个查询连接本身，我相信是实际的Access .MDB文件。正如我所说，我在文件中执行的SQL查询不起作用，并在使用时生成运行时错误。
我正在尝试的命令执行应该是从.MDB文件中取出一个表，并覆盖与不同的.MDB文件相同类型的表。我上面尝试的SQL命令尝试直接从.mdb文件中获取一个表，并直接将其放在另一个 - 这不是我想要做的。我想从.MDB文件中获取所有信息 - 将表放入Datatable，然后将所有的Datatables添加到数据集（我已经完成了）。我想为两个.MDB文件执行此操作。一旦我有两个数据集，我想从每个数据集中采取特定的表，并将它们添加到每个文件，如下所示：
- DataSetA >>> > ----- [添加表
（覆盖他们）] ----- >>>> DataSetB - DataSetB >>>> ----- [添加表
（覆盖他们）] ----- >>>> DataSetA
我想把这些那些数据集，然后将它们放回到他们来自的每个Access .MDB文件中。基本上保持两个数据库的同步化。
所以我修改的问题是：
- 如何创建一个SQL查询，将通过覆盖现有的同一个名称来将表添加到.MDB文件。该查询应该能够在运行时动态地使用一个数组替换要添加的表名的变量。
- 如何获取Datagrid所做的更改到DataTable并将它们放回到DataTable（或DataSet）中，以便我们可以将它们发送到.MDB文件？
我试图尽可能详细阐述，因为我相信我没有很好地解释我的问题。现在这个问题已经越来越长了。我只是希望我可以更好地解释一下。 ：[
编辑：
感谢下面的用户，几乎找到了一个修正 - 关键字几乎。
这是我下面更新的DatabaseHandling.cs代码。我收到运行时错误数据类型不匹配。我不知道这可能是可能的，考虑到我正在尝试将这些表复制到具有完全相同设置的另一个数据库中。
using系统;
使用System.Collections.Generic;
使用System.Linq;
使用System.Text;
使用System.Data.OleDb;
使用System.Data;
使用System.IO;
命名空间LCR_ShepherdStaffupdater_1._0
{
public class DatabaseHandling
{
static DataTable datatableB = new DataTable（）;
static DataTable datatableA = new DataTable（）;
public static DataSet datasetA = new DataSet（）;
public static DataSet datasetB = new DataSet（）;
static OleDbDataAdapter adapterA = new OleDbDataAdapter（）;
static OleDbDataAdapter adapterB = new OleDbDataAdapter（）;
static string connectionstringA =Provider = Microsoft.Jet.OLEDB.4.0; +Data Source =+ Settings.getfilelocationA（）;
static string connectionstringB =Provider = Microsoft.Jet.OLEDB.4.0; +Data Source =+ Settings.getfilelocationB（）;
static OleDbConnection dataconnectionB = new OleDbConnection（connectionstringB）;
static OleDbConnection dataconnectionA = new OleDbConnection（connectionstringA）;
static DataTable tableListA;
static DataTable tableListB;
static public void addTableA（string table，bool addtoDataSet）
{
dataconnectionA.Open（）;
datatableA = new DataTable（table）;
try
{
OleDbCommand commandselectA = new OleDbCommand（SELECT * FROM [+ table +]，dataconnectionA）;
adapterA.SelectCommand = commandselectA;
adapterA.Fill（datatableA）;
}
catch
{
Logging.updateLog（Error：试图从DataSetA获取+表+表不存在！
}
if（addtoDataSet == true）
{
datasetA.Tables.Add（datatableA）;
Logging.updateLog（Added DataTableA：+ datatableA.TableName.ToString（）+Successfully！）;
}
dataconnectionA.Close（）;
}
static public void addTableB（string table，bool addtoDataSet）
{
dataconnectionB.Open（）;
datatableB = new DataTable（table）;
try
{
OleDbCommand commandselectB = new OleDbCommand（SELECT * FROM [+ table +]，dataconnectionB）;
adapterB.SelectCommand = commandselectB;
adapterB.Fill（datatableB）;
}
catch
{
Logging.updateLog（错误：试图从DataSetB获取+表+表不存在！
}
if（addtoDataSet == true）
{
datasetB.Tables.Add（datatableB）;
Logging.updateLog（添加DataTableB：+ datatableB.TableName.ToString（）+成功！）;
}
dataconnectionB.Close（）;
}
static public string [] getTablesA（string connectionString）
{
dataconnectionA.Open（）;
tableListA = dataconnectionA.GetOleDbSchemaTable（OleDbSchemaGuid.Tables，new Object [] {null，null，null，TABLE}）;
string [] stringTableListA = new string [tableListA.Rows.Count];
for（int i = 0; i< tableListA.Rows.Count; i ++）
{
stringTableListA [i] = tableListA.Rows [i] .ItemArray [2 ] .ToString（）;
}
dataconnectionA.Close（）;
return stringTableListA;
}
static public string [] getTablesB（string connectionString）
{
dataconnectionB.Open（）;
tableListB = dataconnectionB.GetOleDbSchemaTable（OleDbSchemaGuid.Tables，new Object [] {null，null，null，TABLE}）;
string [] stringTableListB = new string [tableListB.Rows.Count];
for（int i = 0; i< tableListB.Rows.Count; i ++）
{
stringTableListB [i] = tableListB.Rows [i] .ItemArray [2 ] .ToString（）;
}
dataconnectionB.Close（）;
return stringTableListB;
}
static public void createDataSet（）
{
string [] tempA = getTablesA（connectionstringA）;
string [] tempB = getTablesB（connectionstringB）;
int percentage = 0;
int maximum =（tempA.Length + tempB.Length）;
Logging.updateNotice（加载表...）; （int i = 0; i< tempA.Length; i ++）
{
if（！datasetA.Tables.Contains（tempA [i]））
{
addTableA（tempA [i]，true）;
百分比++;
Logging.loadStatus（百分比，最大）;
}
else
{
datasetA.Tables.Remove（tempA [i]）;
addTableA（tempA [i]，true）;
百分比++;
Logging.loadStatus（百分比，最大）; （int i = 0; i< tempB.Length; i ++）
{
如果（！datasetB.Tables），则
$
包含（tempB [i]））
{
addTableB（tempB [i]，true）;
百分比++;
Logging.loadStatus（百分比，最大）;
}
else
{
datasetB.Tables.Remove（tempB [i]）;
addTableB（tempB [i]，true）;
百分比++;
Logging.loadStatus（百分比，最大）;
}
}
}
static public DataTable getDataTableA（）
{
datatableA = datasetA。表[Settings.textA];
return datatableA;
}
static public DataTable getDataTableB（）
{
datatableB = datasetB.Tables [Settings.textB];
return datatableB;
}
static public DataSet getDataSetA（）
{
return datasetA;
}
static public DataSet getDataSetB（）
{
return datasetB;
}
static public void InitiateCopyProcessA（）
{
DataSet tablesA;
tablesA = DatabaseHandling.getDataSetA（）;
foreach（tableA.Tables中的DataTable表）
{
CopyTable（table，connectionstringB）;
}
}
public static void CopyTable（DataTable表，string connectionStringB）
{
var connectionB = new OleDbConnection（connectionStringB）;
foreach（table.Rows中的DataRow行）
{
InsertRow（row，table.Columns，table.TableName，connectionB）;
}
}
public static void InsertRow（DataRow row，DataColumnCollection columns，string table，OleDbConnection connection）
{
var columnNames = new List& string>（）;
var values = new List< string>（）;
for（int i = 0; i< columns.Count; i ++）
{
columnNames.Add（[+ columns [i] .ColumnName +] ）;
values.Add（'+ row [i] .ToString（）。Replace（'，''）+'）;
}
string sql = string.Format（INSERT INTO {0}（{1}）VALUES（{2}），
表，
string .Join（，，columnNames.ToArray（）），
string.Join（，，values.ToArray（））
）;
ExecuteNonQuery（sql，connection）;
}
public static void ExecuteNonQuery（string sql，OleDbConnection conn）
{
if（conn == null）
throw new ArgumentNullException（conn ）;
ConnectionState prevState = ConnectionState.Closed;
var command = new OleDbCommand（sql，conn）;
try
{
prevState = conn.State;
if（prevState！= ConnectionState.Open）
conn.Open（）;
command.ExecuteNonQuery（）; // !!!运行时错误：条件表达式中的数据类型不匹配。 !!!
}
finally
{
if（conn.State！= ConnectionState.Closed
&& prevState！= ConnectionState.Open）
conn。关（）;
}
}
}
}
为什么我得到这个错误？两张表完全一样。我究竟做错了什么？
最差的情况下，如何在其他Access .MDB文件中删除表，然后再插入具有不同值的完全相同的结构表？
希望我能想出来...
编辑：
好的我来了一段距离我的问题已经变成了一个新的问题，因此值得一提。我已经回答了我的问题，现在我知道如何直接执行查询到我打开的连接。谢谢大家！
我不知道你有多远，但是如果你寻找一个快速的拖动和drop操作你可能想看看创建一个强类型数据集连接，并使用Visual Studio中DataSources工具窗口的拖放功能。
有
- 创建一个新的DataSet
- 从服务器资源管理器中的DataConnection树中取出n-drop
- 创建新表单
- 从DataSources Tool拖动表
- 窗口到窗体
- voila
更新：
首先，我不是100％我明白你的问题。如果您可以在最佳访问文件之间创建一些LinkTable，则可以使用sql语句（如INSERT INTO Customers SELECT FirstName，LastName FROM File2.Customers）复制文件之间的数据。如果没有和选项，我认为您将不得不循环使用DataTable并使用类似于上一次编辑的INSERT语句手动插入记录。对于datagrid，您可能必须通过监视RowChanged事件来跟踪更改的内容（不确定是否是确切的事件）甚至在行更改时执行insert / update语句。
更新：
循环数据表，你会做这样的事情。没有测试。我刚刚更新了这个包含MakeValueDbReady函数。这还没有测试，我不知道我是否正确处理所有的情况，甚至所有的情况。你真的必须调试sql语句，并确保它生成正确的值。每个数据库句柄是不同的值。至少这样一来，价值解析就被提取出来了。我也意识到，而不是硬编码TableName，你应该可以从DataTable中的一个属性获取它
void CopyTable（ DataTable表，string connectionStringB）
{
var connectionB = new OleDbConnection（connectionStringB）;
foreach（table.Rows中的DataRow行）
{
InsertRow（row，table.Columns，table.TableName，connectionB）;
}
}
public static void InsertRow（DataRow row，DataColumnCollection columns，string table，OleDbConnection connection）
{
var columnNames = new List& string>（）;
var values = new List< string>（）;
//从数据列中生成列和值名称
（int i = 0; i< columns.Count; i ++）
{
columnNames.Add （[+ columns [i] .ColumnName +]）;
//数据类型不匹配应该由此函数修正
values.Add（MakeValueDbReady（row [i]，columns [i] .DataType））;
}
//创建sql
string sql = string.Format（INSERT INTO {0}（{1}）VALUES（{2}），
表，
string.Join（，，columnNames.ToArray（）），
string.Join（，，values.ToArray（））
）;
//调试sql的准确性，甚至复制到
//在Access中测试一个新的Query
ExecuteNonQuery（sql，connection）;
}
//作为名称，我们将根据数据库期望的类型在sql字符串中检查数据类型并格式化
//值b $ b public string MakeValueDbReady（object value，Type dataType）
{
if（value == null）
return null;
if（dataType == typeof（string））
{
return'+ value.ToString（）替换（'，''）+ $
else if（dataType == typeof（DateTime））
{
返回＃+（（DateTime）值）.ToString +＃
}
else if（dataType == typeof（bool））
{
return（（bool）value）？ 1：0;
}
返回值.ToString（）;
}
public static void ExecuteNonQuery（string sql，OleDbConnection conn）
{
if（conn == null）
throw new ArgumentNullException（conn ）;
ConnectionState prevState = ConnectionState.Closed;
var command = new OleDbCommand（sql，conn）;
尝试
{
//我们检查prev状态的原因是出于性能原因
//稍后您可能想要为一个批次打开一次连接
//说500行，甚至在事务中包装你的连接。
//我们不想打开和关闭500个连接
prevState = conn.State;
if（prevState！= ConnectionState.Open）
conn.Open（）;
command.ExecuteNonQuery（）;
}
finally
{
if（conn.State！= ConnectionState.Closed
&& prevState！= ConnectionState.Open）
conn。关（）;
}
}
My project that I am working on is almost finished. I am loading a .MDB file, displaying the contents on a DataGrid and attempting to get those changes on the DataGrid and save them back into the .MDB file. I am also going to create a function that allows me to take the tables from one .MDB file and save it to another .MDB file. Of course, I cannot do any of this if I cannot figure out how to save the changes back to the .MDB file.
I have researched Google extensively and there are no answers to my question. I consider myself a beginner at this specific topic so please don't make the answers too complicated -- I need the simplest way to edit a .MDB file! Please provide programming examples.
- Assume that I've already made a connection to a DataGrid. How do I get the changes made by the Datagrid? Im sure this one is simple enough to answer.
- I then need to know how to take this Datatable, insert it into Dataset it came from then take that Dataset and rewrite the .MDB file. (If there is a way of only inserting the tables that were changed I would prefer that.)
Thank you in advance, let me know if you need more information. This is the last thing I am probably going to have to ask about this topic...thank god.
EDIT:
The .mdb I am working with is a Microsoft Access Database. ( I didnt even know there were multiple .mdb files)
I know I cannot write directly to the .MDB file via a streamwriter or anything but is there a way I can possibly generated a .MDB File with the DataSet information already in it? OR is there just a way that I can add tables to a .MDB file that i've already loaded into the DataGrid. There HAS to be a way!
Again, I need a way to do this PROGRAMMATICALLY in C#.
EDIT:
Okay, my project is fairly large but I use a seperate class file to handle all Database connections. I know my design and source is really sloppy, but it gets the job done. I am only as good as the examples I find on the internet.
Remember, I am simply connecting to a DataGrid in another form. Let me know if you want my code from the Datagrid form (I dont know why you would need it though). DatabaseHandling.cs handles 2 .MDB files. So you will see two datasets in there. I will use this eventually to take tables from one Dataset and put them into another Dataset. I just need to figure out how to save these values BACK into a .MDB file.
Is there anyway to do this? There has to be a way...
EDIT:
From what i've researched and read...I think the answer is right under my nose. Using the "Update()" command. Now while this is re-assuring that there is infact a simple way of doing this, I am still left with the problem that I have no-friggin-clue how to use this update command.
Perhaps I can set it up like this:
Oledb.OledbConnection cn = new Oledb.OledbConnection();
cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Staff.mdb";
Oledb.OledbCommand cmd = new Oledb.OledbCommand(cn);
cmd.CommandText = "INSERT INTO Customers (FirstName, LastName) VALUES (@FirstName, @LastName)";
I think that may do it, but I dont want to manually insert anything. I want to do both of these instead:
- Take information that is changed on the Datagrid and update the Access Database File (.mdb) that I got it from
- Create a function that allows me to take tables from another Access Database File (.mdb) and replace them in a secondary Access Database file (.mdb). Both files will use the exact same structure but will have different information in them.
I hope someone comes up with a answer for this...my project is done all that awaits is one simple answer.
Thank you again in advance.
EDIT:
Okay...good news. I have figured out how to query the .mdb file itself (I think). Here is the code, which doesn't work because I get a runtime error due to the sql command i'm attempting to use. Which will bring me to my next question.
New function code added to DatabaseHandling.cs:
static public void performSynchronization(string table, string tableTwoLocation)
{
OleDbCommand cmdCopyTables = new OleDbCommand("INSERT INTO" + table + "SELECT * FROM [MS Access;" + tableTwoLocation + ";].[" + table + "]"); // This query generates runtime error
cmdCopyTables.Connection = dataconnectionA;
dataconnectionA.Open();
cmdCopyTables.ExecuteNonQuery();
dataconnectionA.Close();
}
As you can see, I've actually managed to execute a query on the connection itself, which I believe to be the actual Access .MDB file. As I said though, the SQL query I've executed on the file doesn't work and generated a run-time error when used.
The command I am attempting to execute is supposed to take a table from a .MDB file and overwrite a table of the same type of a different .MDB file. The SQL command I attempted above tried to directly take a table from a .mdb file, and directly put it in another -- this isn't what I want to do. I want to take all the information from the .MDB file -- put the tables into a Datatable and then add all the Datatables to a Dataset (which i've done.) I want to do this for two .MDB files. Once I have two Datasets I want to take specific tables out of each Dataset and add them to each file like this:
- DataSetA >>>>----- [Add Tables (Overwrite Them)] ----->>>> DataSetB
- DataSetB >>>>----- [Add Tables (Overwrite Them)] ----->>>> DataSetA
I want to take those each those Datasets and then put them BACK into each Access .MDB file they came from. Essentially keeping both databases synchronized.
So my questions, revised, is:
- How do I create a SQL query that will add a table to the .MDB file by overwriting the existing one of the same name. The query should be able to be created dynamically during runtime with an array that replaces a variable with the table name I want to add.
- How do I get the changes that were made by the Datagrid to the DataTable and put them back into a DataTable (or DataSet) so I can send them to the .MDB file?
I've tried to elaborate as much as possible...because I believe I am not explaing my issue very well. Now this question has grown wayyy too long. I just wish I could explain this better. :[
EDIT:
Thanks to a user below I think I've almost found a fix -- the keyword almost. Here is my updated DatabaseHandling.cs code below. I get a runtime error "Datatype Mismatch." I dont know how that could be possible considering I am trying to copy these tables into another database with the exact same setup.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;
using System.IO;
namespace LCR_ShepherdStaffupdater_1._0
{
public class DatabaseHandling
{
static DataTable datatableB = new DataTable();
static DataTable datatableA = new DataTable();
public static DataSet datasetA = new DataSet();
public static DataSet datasetB = new DataSet();
static OleDbDataAdapter adapterA = new OleDbDataAdapter();
static OleDbDataAdapter adapterB = new OleDbDataAdapter();
static string connectionstringA = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Settings.getfilelocationA();
static string connectionstringB = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Settings.getfilelocationB();
static OleDbConnection dataconnectionB = new OleDbConnection(connectionstringB);
static OleDbConnection dataconnectionA = new OleDbConnection(connectionstringA);
static DataTable tableListA;
static DataTable tableListB;
static public void addTableA(string table, bool addtoDataSet)
{
dataconnectionA.Open();
datatableA = new DataTable(table);
try
{
OleDbCommand commandselectA = new OleDbCommand("SELECT * FROM [" + table + "]", dataconnectionA);
adapterA.SelectCommand = commandselectA;
adapterA.Fill(datatableA);
}
catch
{
Logging.updateLog("Error: Tried to get " + table + " from DataSetA. Table doesn't exist!");
}
if (addtoDataSet == true)
{
datasetA.Tables.Add(datatableA);
Logging.updateLog("Added DataTableA: " + datatableA.TableName.ToString() + " Successfully!");
}
dataconnectionA.Close();
}
static public void addTableB(string table, bool addtoDataSet)
{
dataconnectionB.Open();
datatableB = new DataTable(table);
try
{
OleDbCommand commandselectB = new OleDbCommand("SELECT * FROM [" + table + "]", dataconnectionB);
adapterB.SelectCommand = commandselectB;
adapterB.Fill(datatableB);
}
catch
{
Logging.updateLog("Error: Tried to get " + table + " from DataSetB. Table doesn't exist!");
}
if (addtoDataSet == true)
{
datasetB.Tables.Add(datatableB);
Logging.updateLog("Added DataTableB: " + datatableB.TableName.ToString() + " Successfully!");
}
dataconnectionB.Close();
}
static public string[] getTablesA(string connectionString)
{
dataconnectionA.Open();
tableListA = dataconnectionA.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new Object[] { null, null, null, "TABLE" });
string[] stringTableListA = new string[tableListA.Rows.Count];
for (int i = 0; i < tableListA.Rows.Count; i++)
{
stringTableListA[i] = tableListA.Rows[i].ItemArray[2].ToString();
}
dataconnectionA.Close();
return stringTableListA;
}
static public string[] getTablesB(string connectionString)
{
dataconnectionB.Open();
tableListB = dataconnectionB.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new Object[] { null, null, null, "TABLE" });
string[] stringTableListB = new string[tableListB.Rows.Count];
for (int i = 0; i < tableListB.Rows.Count; i++)
{
stringTableListB[i] = tableListB.Rows[i].ItemArray[2].ToString();
}
dataconnectionB.Close();
return stringTableListB;
}
static public void createDataSet()
{
string[] tempA = getTablesA(connectionstringA);
string[] tempB = getTablesB(connectionstringB);
int percentage = 0;
int maximum = (tempA.Length + tempB.Length);
Logging.updateNotice("Loading Tables...");
for (int i = 0; i < tempA.Length ; i++)
{
if (!datasetA.Tables.Contains(tempA[i]))
{
addTableA(tempA[i], true);
percentage++;
Logging.loadStatus(percentage, maximum);
}
else
{
datasetA.Tables.Remove(tempA[i]);
addTableA(tempA[i], true);
percentage++;
Logging.loadStatus(percentage, maximum);
}
}
for (int i = 0; i < tempB.Length ; i++)
{
if (!datasetB.Tables.Contains(tempB[i]))
{
addTableB(tempB[i], true);
percentage++;
Logging.loadStatus(percentage, maximum);
}
else
{
datasetB.Tables.Remove(tempB[i]);
addTableB(tempB[i], true);
percentage++;
Logging.loadStatus(percentage, maximum);
}
}
}
static public DataTable getDataTableA()
{
datatableA = datasetA.Tables[Settings.textA];
return datatableA;
}
static public DataTable getDataTableB()
{
datatableB = datasetB.Tables[Settings.textB];
return datatableB;
}
static public DataSet getDataSetA()
{
return datasetA;
}
static public DataSet getDataSetB()
{
return datasetB;
}
static public void InitiateCopyProcessA()
{
DataSet tablesA;
tablesA = DatabaseHandling.getDataSetA();
foreach (DataTable table in tablesA.Tables)
{
CopyTable(table, connectionstringB);
}
}
public static void CopyTable(DataTable table, string connectionStringB)
{
var connectionB = new OleDbConnection(connectionStringB);
foreach (DataRow row in table.Rows)
{
InsertRow(row, table.Columns, table.TableName, connectionB);
}
}
public static void InsertRow(DataRow row, DataColumnCollection columns, string table, OleDbConnection connection)
{
var columnNames = new List<string>();
var values = new List<string>();
for (int i = 0; i < columns.Count; i++)
{
columnNames.Add("[" + columns[i].ColumnName + "]");
values.Add("'" + row[i].ToString().Replace("'", "''") + "'");
}
string sql = string.Format("INSERT INTO {0} ({1}) VALUES ({2})",
table,
string.Join(", ", columnNames.ToArray()),
string.Join(", ", values.ToArray())
);
ExecuteNonQuery(sql, connection);
}
public static void ExecuteNonQuery(string sql, OleDbConnection conn)
{
if (conn == null)
throw new ArgumentNullException("conn");
ConnectionState prevState = ConnectionState.Closed;
var command = new OleDbCommand(sql, conn);
try
{
prevState = conn.State;
if (prevState != ConnectionState.Open)
conn.Open();
command.ExecuteNonQuery(); // !!! Runtime-Error: Data type mismatch in criteria expression. !!!
}
finally
{
if (conn.State != ConnectionState.Closed
&& prevState != ConnectionState.Open)
conn.Close();
}
}
}
}
Why am I getting this error? Both tables are exactly the same. What am I doing wrong? Worst case, how do I delete the table in the other Access .MDB file before inserting the exact same structure table with different values in it?
Man I wish I could just figure this out...
EDIT:
Okay, I've come some distance. My question has morphed into a new one, and thus deserves being asked seperately. I have had my question answered as now I know how to execute queries directly to the connection that I have opened. Thank you all!
I'm not sure how far you've gotten, but if you looking for a quick drag and drop operation you might want to look at creating a strongly-typed dataset that connects, and using the drag-drop features of the DataSources Tool Window in Visual Studio.
There are definately samples out there, but you will want to.
- Create a new DataSet
- Drag-n-Drop from your DataConnection Tree in Server Explorer
- Create a new form
- Drag the table from the DataSources Tool
- Window on to the form.
- voila
Update:
First off, I'm not 100% that I understand your issue. If you can create some LinkTables between the access files that would be best, then you can copy the data between files using a sql statement like 'INSERT INTO Customers SELECT FirstName, LastName FROM File2.Customers'. If thats not and option I think your going to have to loop the DataTables and insert the records manually using INSERT statements similar to your last edit. As for the datagrid, you will probably have to keep track of whats changed by monitoring the RowChanged Event (not sure if thats the exact event) of even do the insert/update statements when the row changes.
Update:
to loop the datatable you would do something like this. not tested. I just updated this again to include the MakeValueDbReady function. This is not tested either and I'm not sure if I've handle all the cases or even all the cases correctly. You'll really have to debug the sql statement and make sure its generating the right value. Each database handles is values differently. Atleast this way the value parse is extracted away. I also realized that instead of hard coding the TableName you should be able to get it from a property on the DataTable
void CopyTable(DataTable table, string connectionStringB)
{
var connectionB = new OleDbConnection(connectionStringB);
foreach(DataRow row in table.Rows)
{
InsertRow(row, table.Columns, table.TableName, connectionB);
}
}
public static void InsertRow(DataRow row, DataColumnCollection columns, string table, OleDbConnection connection)
{
var columnNames = new List<string>();
var values = new List<string>();
// generate the column and value names from the datacolumns
for(int i =0;i<columns.Count; i++)
{
columnNames.Add("[" + columns[i].ColumnName + "]");
// datatype mismatch should be fixed by this function
values.Add(MakeValueDbReady(row[i], columns[i].DataType));
}
// create the sql
string sql = string.Format("INSERT INTO {0} ({1}) VALUES ({2})",
table,
string.Join(", ", columnNames.ToArray()),
string.Join(", ", values.ToArray())
);
// debug the accuracy of the sql here and even copy into
// a new Query in Access to test
ExecuteNonQuery(sql, connection);
}
// as the name says we are going to check the datatype and format the value
// in the sql string based on the type that the database is expecting
public string MakeValueDbReady(object value, Type dataType)
{
if (value == null)
return null;
if (dataType == typeof(string))
{
return "'" + value.ToString().Replace("'", "''") + "'"
}
else if (dataType == typeof(DateTime))
{
return "#" + ((DateTime)value).ToString + "#"
}
else if (dataType == typeof(bool))
{
return ((bool)value) ? "1" : "0";
}
return value.ToString();
}
public static void ExecuteNonQuery(string sql, OleDbConnection conn)
{
if (conn == null)
throw new ArgumentNullException("conn");
ConnectionState prevState = ConnectionState.Closed;
var command = new OleDbCommand(sql, conn);
try
{
// the reason we are checking the prev state is for performance reasons
// later you might want to open the connection once for the a batch
// of say 500 rows or even wrap your connection in a transaction.
// we don't want to open and close 500 connections
prevState = conn.State;
if (prevState != ConnectionState.Open)
conn.Open();
command.ExecuteNonQuery();
}
finally
{
if (conn.State != ConnectionState.Closed
&& prevState != ConnectionState.Open)
conn.Close();
}
}
这篇关于C＃问题：我最简单的方法是加载.MDB文件，对其进行更改，并将更改保存回原始文件？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
