Question

c#操作oracle做insert

一个button1向往linedate表插入我想要的数据。sql语句我在pl/sql里执行成功的。现在就是button1点击后，库里查看linedate表，并没insert进去，就像pl/sql里完成了insert 但是没有commit；一样。也没报错。求找问题，谢谢了！

private void button1_Click(object sender, EventArgs e)
{
string ConnectionString = "Data Source=orcl; User Id=" + u + "; Password=" + p;
OracleConnection conn1 = new OracleConnection(ConnectionString);
try
{
conn1.Open();
OracleTransaction myTrans;
OracleCommand cmd1 = null;
myTrans = null;
cmd1 = conn1.CreateCommand();
myTrans = conn1.BeginTransaction();
cmd1.Transaction = myTrans;
cmd1.CommandText = "insert into linedate select * from bsvcbusarrlftld5 where routeid = '" + comboBox1.SelectedItem.ToString() +"' and to_char(actdatetime,'yyyy-mm-dd') = '" + dateTimePicker1.Value.ToString()+"'";
cmd1.ExecuteNonQuery();
myTrans.Commit();
}
catch (Exception ee)
{
MessageBox.Show(ee.Message);

}
finally
{
conn1.Close();
}

Answer 1

应该是写法有问题，给你找了段

#region //操作Oracle数据库
public class OracleDBService
{
public OracleDBService(string ConnectionString)
{
OracleConStr = ConnectionString;
}
#region//连接数据库所需变量及方法
private string OracleConStr = "";
private OracleConnection conn;//创建sql连接
private OracleCommand com;//创建sql命令对象
private OracleDataReader dr;//创建sql数据阅读器
private OracleDataAdapter sdr;//创建sql适配器
private DataSet ds;//创建数据集

/// <summary>
/// 创建数据库连接并打开
/// </summary>
public void open()
{
//创建连接
conn = new OracleConnection(OracleConStr);
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
else if (conn.State == ConnectionState.Broken)
{
conn.Close();
conn.Open();
}
}
#region//事务操作数据库
/// <summary>
/// 提交一组（多条）SQL语句操作数据库
/// </summary>
/// <param name="commandStringList">SQL列表</param>
/// <returns>执行结果</returns>
public int UpdateBatchCommand(ArrayList commandStringList)
{
open();
OracleTransaction m_OraTrans = conn.BeginTransaction();//创建事务对象
com = new OracleCommand();
com.Connection = conn;
string tmpStr = "";
int influenceRowCount = 0;
try
{
foreach (string commandString in commandStringList)
{
tmpStr = commandString;
com.CommandText = tmpStr;
influenceRowCount += com.ExecuteNonQuery();
}
m_OraTrans.Commit();
return influenceRowCount;
}
catch (OracleException ex)
{
m_OraTrans.Rollback();
throw ex;
}
}
#endregion

追问

我写的没差哪里吧..就一个insert的操作，能帮我那段看看哪里出问题了么?

追答

给你改了下，你试试
private void button1_Click(object sender, EventArgs e)
{
string ConnectionString = "Data Source=orcl; User Id=" + u + "; Password=" + p;
OracleConnection conn1 = new OracleConnection(ConnectionString);
try
{
conn1.Open();
OracleTransaction myTrans = conn1.BeginTransaction();//创建事务对象
OracleCommand cmd1 = new OracleCommand();
cmd1.Connection = conn1;
cmd1.CommandText = "insert into linedate select * from bsvcbusarrlftld5 where routeid = '" + comboBox1.SelectedItem.ToString() + "' and to_char(actdatetime,'yyyy-mm-dd') = '" + dateTimePicker1.Value.ToString() + "'";
cmd1.ExecuteNonQuery();
myTrans.Commit();
}
catch (Exception ee)
{
MessageBox.Show(ee.Message);
}
finally
{
conn1.Close();
}
}

追问

显示cmd的Transaction属性没初始化。我加了句cmd.Transaction=mytrans；
可是还是不行么没有数据..你主要的改动是OracleCommand cmd1 = new OracleCommand();
？这里我觉得原来那句createcommand()也行的。。还有什么想法么兄台？