想用C#动态修改ACCESS,完成批量数据的添加
刚学.net,这两天一直在查资料,现在做到能创建access及连接,
以及能简单的添加数据,这段代码如下:
OleDbConnection aConnection = new OleDbConnection(
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dbName);
try
{
aConnection.Open();
OleDbCommand cmd = new OleDbCommand();//
cmd.Connection = aConnection;
cmd.CommandText = "insert into MyTable (id, Description, volt) values('123','43','4');";
cmd.Parameters["@fi"].Value = "4";
cmd.ExecuteNonQuery(); cmd.CommandText = "insert into MyTable (id, Description, volt) values('34','4','2');";
cmd.ExecuteNonQuery(); aConnection.Close();
}有两个问题想请达人帮忙解答:
1、values('123','43','4') 括号里的数能否用参数传递,如何实现?比如int a = 123,然后我用a替换掉括号里的123这形 式,查了一些资料都没有找到。
2、能否还有其他形式实现数据的批量添加?
谢谢!
刚学.net,这两天一直在查资料,现在做到能创建access及连接,
以及能简单的添加数据,这段代码如下:
OleDbConnection aConnection = new OleDbConnection(
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dbName);
try
{
aConnection.Open();
OleDbCommand cmd = new OleDbCommand();//
cmd.Connection = aConnection;
cmd.CommandText = "insert into MyTable (id, Description, volt) values('123','43','4');";
cmd.Parameters["@fi"].Value = "4";
cmd.ExecuteNonQuery(); cmd.CommandText = "insert into MyTable (id, Description, volt) values('34','4','2');";
cmd.ExecuteNonQuery(); aConnection.Close();
}有两个问题想请达人帮忙解答:
1、values('123','43','4') 括号里的数能否用参数传递,如何实现?比如int a = 123,然后我用a替换掉括号里的123这形 式,查了一些资料都没有找到。
2、能否还有其他形式实现数据的批量添加?
谢谢!
比如string sql = "insert into MyTable (id, Description, volt) values('+id+','"+description+"','"+volt+"')";
这样就可以了。
数据批量添加可以考虑用存储过程,也可以用循环
OleDbConnection aConnection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dbName);
try
{
aConnection.Open();
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = aConnection;
cmd.CommandText = "insert into MyTable (id, Description, volt) values(?, ?, ?);";
//下面?号值的顺序要和上面的sql语句中?号的顺序要完全一致, oledb的参数只认顺序,不认名字
cmd.Parameters.AddWithValue("?", "123");
cmd.Parameters.AddWithValue("?", "43");
cmd.Parameters.AddWithValue("?", "4");
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
if(aConnection.State == ConnectionState.Open)
aConnection.Close();
}
OleDbConnection aConnection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dbName);
try
{
aConnection.Open();
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = aConnection;
cmd.CommandText = "insert into MyTable (id, Description, volt) values(?, ?, ?);"; for (int i = 0; i < 10; i++)
{
cmd.Parameters.AddWithValue("?", i.ToString());
cmd.Parameters.AddWithValue("?", i.ToString());
cmd.Parameters.AddWithValue("?", i.ToString());
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
if(aConnection.State == ConnectionState.Open)
aConnection.Close();
}