C#转移MYSQL数据问题 本帖最后由 haolaile 于 2013-03-06 11:32:24 编辑 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 第二层循环,最后是j++,而不是i++for(int j=0;j<dataSet2.Tables[i].Rows.Count;j++) 你还是直接用MySQL的导入导出命令吧,写个C#程序毫无意义,何况你这里没有做任何数据筛选。 谢谢gxingmin的回复,不过在执行adapter1.Update(dataSet1, tabStr);还是报错,这个怎么处理 在Update前加下面三句MySqlCommandBuilder cb = new MySqlCommandBuilder (adapter1);adapter1.SelectCommand.CommandText = "SELECT * FROM " + tabStr";cb.RefreshSchema();adapter1.Update(dataSet1,tabStr); 谢谢5楼gxingmin的回复,问题已经解决,不过有个额外的问题,如果数据库中某个字段为空或是中文字符的时候怎么处理插入。中文字符报错了。Duplidate entry '??????' for key 2 问题完美解决,现讲代码贴出来//数据库2的数据插入到数据库1中//参数1:目标数据库;参数2:源数据库;参数3:表名private void InsertTableData(string conString1, string conString2, string tabStr){ MySqlConnection conn1 = new MySqlConnection(); conn1.ConnectionString = conString1; conn1.Open(); MySqlConnection conn2 = new MySqlConnection(); conn2.ConnectionString = conString2; conn2.Open(); MySqlDataAdapter adapter1 = new MySqlDataAdapter("select * from '" + tabStr + "'", conn1); DataSet dataSet1 = new DataSet(); if (dataSet1 != null) { adapter1.Fill(dataSet1, tabStr); } MySqlDataAdapter adapter2 = new MySqlDataAdapter("select * from '" + tabStr + "'", conn2); DataSet dataSet2 = new DataSet(); MySqlCommand cmd2 = new MySqlCommand("select count(*) from " + tabStr, conn2); Object res2 = cmd2.ExecuteScalar(); if (res2 != null) { int nCount = Convert.ToInt32(res2.ToString()); //没有数据,不用插入了 if (nCount == 0) { conn1.Close(); conn2.Close(); return; } } if (dataSet2 != null) { adapter2.Fill(dataSet2, tabStr); } for (int i = 0; i < dataSet2.Tables.Count;i++ ) { for (int j = 0; j < dataSet2.Tables[i].Rows.Count;i++ ) { dataSet1.Tables[i].LoadDataRow(dataSet2.Tables[i].Rows[j].ItemArray, false); } } MySqlCommandBuilder cb = new MySqlCommandBuilder (adapter1); adapter1.SelectCommand.CommandText = "SELECT * FROM " + tabStr"; cb.RefreshSchema(); //判断是否有主键 MySqlCommand cmd11 = new MySqlCommand("SELECT COLUMN_NAME FROM information_schema.`COLUMNS` WHERE TABLE_NAME='" + tabStr + "' and COLUMN_KEY='PRI'", conn1); Object res1 = cmd11.ExecuteScalar(); string reStr = res1.ToString(); if (res1 != null) { //删除主键 MySqlCommand cmd12 = new MySqlCommand("ALTER table " + tabStr + " modify " + reStr + " int(4) not null default 0", conn1); int nRes12 = cmd12.ExecuteNonQuery(); MySqlCommand cmd13 = new MySqlCommand("ALTER table " + tabStr + " drop primary key", conn1); int nRes13 = cmd13.ExecuteNonQuery(); } adapter1.Update(dataSet1, tabStr); //添加主键 if (res1 != null) { MySqlCommand cmd14 = new MySqlCommand("ALTER table " + tabStr + " add primary key(" + reStr + ")", conn1); int nRes4 = cmd14.ExecuteNonQuery(); MySqlCommand cmd15 = new MySqlCommand("ALTER table " + tabStr + " modify " + reStr + " int(4) not null auto_increment", conn1); int nRes15 = cmd15.ExecuteNonQuery(); } conn1.Close(); conn2.Close();} c#未处理异常问题 C# ado insert access,求帮助 快捷键问题!!! ※关于可扩展应用程序的一些问题※ 你们都是怎样推广自己的软件的! windows窗体中数据传递问题,急!!! VSTS2005设置问题! 急求一段简单绘制坐标图形的代码参考一下 高手请看这样的DataGrid如何实现? 求一给C#程序加密的软件,大家推荐下,谢谢了 求助,webbrowser 自动填表,找不到文本框 长时间动态调用WebService出错,郁闷
for(int j=0;j<dataSet2.Tables[i].Rows.Count;j++)
MySqlCommandBuilder cb = new MySqlCommandBuilder (adapter1);
adapter1.SelectCommand.CommandText = "SELECT * FROM " + tabStr";
cb.RefreshSchema();
adapter1.Update(dataSet1,tabStr);
Duplidate entry '??????' for key 2
//参数1:目标数据库;参数2:源数据库;参数3:表名
private void InsertTableData(string conString1, string conString2, string tabStr)
{
MySqlConnection conn1 = new MySqlConnection();
conn1.ConnectionString = conString1;
conn1.Open(); MySqlConnection conn2 = new MySqlConnection();
conn2.ConnectionString = conString2;
conn2.Open(); MySqlDataAdapter adapter1 = new MySqlDataAdapter("select * from '" + tabStr + "'", conn1);
DataSet dataSet1 = new DataSet(); if (dataSet1 != null)
{
adapter1.Fill(dataSet1, tabStr);
} MySqlDataAdapter adapter2 = new MySqlDataAdapter("select * from '" + tabStr + "'", conn2);
DataSet dataSet2 = new DataSet(); MySqlCommand cmd2 = new MySqlCommand("select count(*) from " + tabStr, conn2);
Object res2 = cmd2.ExecuteScalar(); if (res2 != null)
{
int nCount = Convert.ToInt32(res2.ToString());
//没有数据,不用插入了
if (nCount == 0)
{
conn1.Close();
conn2.Close();
return;
}
} if (dataSet2 != null)
{
adapter2.Fill(dataSet2, tabStr);
} for (int i = 0; i < dataSet2.Tables.Count;i++ )
{
for (int j = 0; j < dataSet2.Tables[i].Rows.Count;i++ )
{
dataSet1.Tables[i].LoadDataRow(dataSet2.Tables[i].Rows[j].ItemArray, false);
}
} MySqlCommandBuilder cb = new MySqlCommandBuilder (adapter1);
adapter1.SelectCommand.CommandText = "SELECT * FROM " + tabStr";
cb.RefreshSchema(); //判断是否有主键
MySqlCommand cmd11 = new MySqlCommand("SELECT COLUMN_NAME FROM information_schema.`COLUMNS` WHERE TABLE_NAME='" + tabStr + "' and COLUMN_KEY='PRI'", conn1);
Object res1 = cmd11.ExecuteScalar();
string reStr = res1.ToString(); if (res1 != null)
{
//删除主键
MySqlCommand cmd12 = new MySqlCommand("ALTER table " + tabStr + " modify " + reStr + " int(4) not null default 0", conn1);
int nRes12 = cmd12.ExecuteNonQuery(); MySqlCommand cmd13 = new MySqlCommand("ALTER table " + tabStr + " drop primary key", conn1);
int nRes13 = cmd13.ExecuteNonQuery();
} adapter1.Update(dataSet1, tabStr); //添加主键
if (res1 != null)
{
MySqlCommand cmd14 = new MySqlCommand("ALTER table " + tabStr + " add primary key(" + reStr + ")", conn1);
int nRes4 = cmd14.ExecuteNonQuery(); MySqlCommand cmd15 = new MySqlCommand("ALTER table " + tabStr + " modify " + reStr + " int(4) not null auto_increment", conn1);
int nRes15 = cmd15.ExecuteNonQuery();
} conn1.Close();
conn2.Close();
}