110,566
社区成员
发帖
与我相关
我的任务
分享
string sqlStr = "select * from XM where ISPOST=0";
OleDbCommand Dbcom = new OleDbCommand(sqlStr, connMdb);
OleDbDataReader dbDR = Dbcom.ExecuteReader();
//怎么样将上面查询到的结果上传到SQL中?
// 下面是SQL的查询.
comStr = "select * from XM1 ";
SqlCommand Dbcom = new SqlCommand(comStr, connSQL);
insert into xxoo
select * from
openrowset(
'Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;IMEX=1;Database=D:\6总装任务.xlsx',
'select * from [0ETC100295$]')
这个是查询xls的 自己百度下 稍微修改下 就直接查询access了
private void upload()
{
if (connMdb == null)
{
openDBC();
}
if (connMdb.State != ConnectionState.Open)
{
openDBC();
}
//================================
string sqlStr = "select * from XM where ISPOST=0";
OleDbCommand Dbcom = new OleDbCommand(sqlStr, connMdb);
OleDbDataReader dbDR = Dbcom.ExecuteReader();
if (int.Parse(textBox3.Text) <= 0)
{
MessageBox.Show(this, "没有可被上传的数据!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
int iCount = 0;
if (is_connection)
{
string sqlStr1="select * from "+Program.sqlConn.Table1;
SqlCommand DbcomSQL = new SqlCommand(sqlStr1, conn);
while (dbDR.Read())
{
sqlStr1 = "INSERT INTO " + Program.sqlConn.Table1 + "(BarCode,FDate,T01,T02,T03,T04,T05,T06,T07,T08,T09,T10,T11,T12,T13,T14,T15,T16,T17,T18,Status,ISPOST)";
sqlStr1 += " VALUES('"+dbDR["BarCode"].ToString()+"','";
sqlStr1 += dbDR["postdate"].ToString() + "',";
sqlStr1 += dbDR["T01"].ToString() + ",";
sqlStr1 += dbDR["T02"].ToString() + ",";
sqlStr1 += dbDR["T03"].ToString() + ",";
sqlStr1 += dbDR["T04"].ToString() + ",";
sqlStr1 += dbDR["T05"].ToString() + ",";
sqlStr1 += dbDR["T06"].ToString() + ",";
sqlStr1 += dbDR["T07"].ToString() + ",";
sqlStr1 += dbDR["T08"].ToString() + ",";
sqlStr1 += dbDR["T09"].ToString() + ",";
sqlStr1 += dbDR["T10"].ToString() + ",";
sqlStr1 += dbDR["T11"].ToString() + ",";
sqlStr1 += dbDR["T12"].ToString() + ",";
sqlStr1 += dbDR["T13"].ToString() + ",";
sqlStr1 += dbDR["T14"].ToString() + ",";
sqlStr1 += dbDR["T15"].ToString() + ",";
sqlStr1 += dbDR["T16"].ToString() + ",";
sqlStr1 += dbDR["T17"].ToString() + ",";
sqlStr1 += dbDR["T18"].ToString() + ",";
sqlStr1 += dbDR["Status"].ToString() + ",";
sqlStr1 += "1)";
DbcomSQL.CommandText = sqlStr1 ;
DbcomSQL.ExecuteNonQuery();
iCount++;
}
dbDR.Close();
sqlStr = "UPDATE XM SET ISPOST=1 where ISPOST=0";
Dbcom.CommandText = sqlStr;
int iRet = Dbcom.ExecuteNonQuery();
sqlStr = "SELECT * FROM XM where ISPOST=0";
Dbcom.CommandText = sqlStr;
textBox3.Text = Dbcom.ExecuteNonQuery().ToString();
if (int.Parse(textBox3.Text) > 0)
{
btnUpload.Enabled = true;
}
else
{
btnUpload.Enabled = false;
}
MessageBox.Show(this, "数据上传完成!共:" + iCount.ToString() + "条记录被上传!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
textBox1.Text = "0";
btnUpload.Enabled = false;
}
else
{
MessageBox.Show(this, "远程数据库连接失败,不能将本地数据上传到服务器!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
这是我的代码,要用每一句循环来插入这样效率可能会低很多,有没有一次性将OleDbDataReader dbDR的结果传到SQL中呢?