using System;
using System.Data;
using Oracle.DataAccess.Client;
using System.Web.Configuration;namespace oraModify
{
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
try
{
String connectionString = WebConfigurationManager.ConnectionStrings["ConnectionString1"].ConnectionString;
OracleConnection con = new OracleConnection(connectionString);
con.Open(); //// Create the command
OracleCommand cmdSelect = new OracleCommand("", con); // sql文
OracleCommand cmdUpdate = new OracleCommand("", con); // sql文
OracleCommand cmdDelete = new OracleCommand("", con); // sql文
OracleCommand cmdInsert = new OracleCommand("", con); //select statement
string strSelectSql = "SELECT * FROM TBUSER WHERE LOGINID = :I_LOGINID ";
string strUpdateSql = "UPDATE TBUSER SET EFFECTSTRT = :I_EFFECTSTRT WHERE LOGINID = :I_LOGINID ";
//string strDeleteSql = "DELETE FROM TBUSER WHERE LOGINID = :I_LOGINID";
//string strInsertSql = "INSERT TB_USER(LOGINID,EFFECTSTRT,EFFECTEND) VALUES (:I_LOGINID, :I_EFFECTSTRT,:I_EFFECTEND )"; //command和检索sql文 关联
cmdSelect.CommandText = strSelectSql;
cmdUpdate.CommandText = strUpdateSql;
//cmdDelete.CommandText = strDeleteSql;
//cmdInsert.CommandText = strInsertSql; //sql文中变量通过oracle参数传递
OracleParameter oraParameter; //当设置参数的时候
//oracle参数值可通过SourceColumn属性与更新数据行的字段绑定
//取得数据值的版本可通过SourceVersion来设定。
//DataRowVersion.Current 取得数据行中修改后的数据值
//DataRowVersion.Original 取得数据行中原始的数据值
//Where条件里面的参数值往往设置为取DataRowVersion.Original版本
//更新或者插入的参数值往往设置为取DataRowVersion.Current版本 //--------------------------------------------------
//取得用的oracle command
oraParameter = new OracleParameter("I_LOGINID", OracleDbType.Varchar2);
oraParameter.SourceColumn = "LOGINID";//检索字段值
oraParameter.SourceVersion = DataRowVersion.Current;
oraParameter.Value = "60";
cmdSelect.Parameters.Add(oraParameter);//字符型
//--------------------------------------------------
//更新用的oracle command
oraParameter = new OracleParameter("I_EFFECTSTRT", OracleDbType.Date);
oraParameter.SourceColumn = "EFFECTSTRT";//更新字段值
oraParameter.SourceVersion = DataRowVersion.Current;
oraParameter.Value = Convert.ToDateTime("2010/02/14").Date;
cmdUpdate.Parameters.Add(oraParameter); oraParameter = new OracleParameter("I_LOGINID", OracleDbType.Varchar2);
oraParameter.SourceColumn = "LOGINID";//检索字段值
oraParameter.SourceVersion = DataRowVersion.Original;
oraParameter.Value = "60";
cmdUpdate.Parameters.Add(oraParameter);
////--------------------------------------------------
////删除用的oracle command
//oraParameter = new OracleParameter("I_LOGINID", OracleDbType.Varchar2);
//oraParameter.SourceColumn = "LOGINID";//检索字段值
//oraParameter.SourceVersion = DataRowVersion.Original;
//oraParameter.Value = "50";
//cmdDelete.Parameters.Add(oraParameter); ////--------------------------------------------------
////追加用的oracle command
//oraParameter = new OracleParameter("I_LOGINID", OracleDbType.Varchar2);
//oraParameter.SourceColumn = "LOGINID";//追加字段值
//oraParameter.SourceVersion = DataRowVersion.Current;
//oraParameter.Value = "70";
//cmdInsert.Parameters.Add(oraParameter); //oraParameter = new OracleParameter("I_EFFECTSTRT", OracleDbType.Varchar2);
//oraParameter.SourceColumn = "EFFECTSTRT";//追加字段值
//oraParameter.Value = "2010/02/14";
//oraParameter.SourceVersion = DataRowVersion.Current;
//cmdInsert.Parameters.Add(oraParameter); //oraParameter = new OracleParameter("I_EFFECTEND", OracleDbType.Varchar2);
//oraParameter.SourceColumn = "EFFECTEND";//追加字段值
//oraParameter.Value = "2010/02/14";
//oraParameter.SourceVersion = DataRowVersion.Current;
//cmdInsert.Parameters.Add(oraParameter); using (OracleDataAdapter dataAdapter = new OracleDataAdapter())
{
int nRecCount = 0;
DataSet i_Data = new DataSet(); dataAdapter.SelectCommand = cmdSelect;
dataAdapter.Fill(i_Data, "TB_USER");
//dataAdapter.InsertCommand = cmdInsert;//追加command设置
dataAdapter.UpdateCommand = cmdUpdate;//更新command设置
//dataAdapter.DeleteCommand = cmdDelete;//删除command设置
nRecCount = dataAdapter.Update(i_Data, "TB_USER");//数据更新
//this.GridView1.DataSource = i_Data;
//GridView1.DataBind();//将控件及其所有子控件绑定到指定的数据源
}
// Close and Dispose OracleConnection object
con.Close();
con.Dispose(); }
catch (Exception ex)
{ throw ex;
}
}
}
}
using System.Data;
using Oracle.DataAccess.Client;
using System.Web.Configuration;namespace oraModify
{
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
try
{
String connectionString = WebConfigurationManager.ConnectionStrings["ConnectionString1"].ConnectionString;
OracleConnection con = new OracleConnection(connectionString);
con.Open(); //// Create the command
OracleCommand cmdSelect = new OracleCommand("", con); // sql文
OracleCommand cmdUpdate = new OracleCommand("", con); // sql文
OracleCommand cmdDelete = new OracleCommand("", con); // sql文
OracleCommand cmdInsert = new OracleCommand("", con); //select statement
string strSelectSql = "SELECT * FROM TBUSER WHERE LOGINID = :I_LOGINID ";
string strUpdateSql = "UPDATE TBUSER SET EFFECTSTRT = :I_EFFECTSTRT WHERE LOGINID = :I_LOGINID ";
//string strDeleteSql = "DELETE FROM TBUSER WHERE LOGINID = :I_LOGINID";
//string strInsertSql = "INSERT TB_USER(LOGINID,EFFECTSTRT,EFFECTEND) VALUES (:I_LOGINID, :I_EFFECTSTRT,:I_EFFECTEND )"; //command和检索sql文 关联
cmdSelect.CommandText = strSelectSql;
cmdUpdate.CommandText = strUpdateSql;
//cmdDelete.CommandText = strDeleteSql;
//cmdInsert.CommandText = strInsertSql; //sql文中变量通过oracle参数传递
OracleParameter oraParameter; //当设置参数的时候
//oracle参数值可通过SourceColumn属性与更新数据行的字段绑定
//取得数据值的版本可通过SourceVersion来设定。
//DataRowVersion.Current 取得数据行中修改后的数据值
//DataRowVersion.Original 取得数据行中原始的数据值
//Where条件里面的参数值往往设置为取DataRowVersion.Original版本
//更新或者插入的参数值往往设置为取DataRowVersion.Current版本 //--------------------------------------------------
//取得用的oracle command
oraParameter = new OracleParameter("I_LOGINID", OracleDbType.Varchar2);
oraParameter.SourceColumn = "LOGINID";//检索字段值
oraParameter.SourceVersion = DataRowVersion.Current;
oraParameter.Value = "60";
cmdSelect.Parameters.Add(oraParameter);//字符型
//--------------------------------------------------
//更新用的oracle command
oraParameter = new OracleParameter("I_EFFECTSTRT", OracleDbType.Date);
oraParameter.SourceColumn = "EFFECTSTRT";//更新字段值
oraParameter.SourceVersion = DataRowVersion.Current;
oraParameter.Value = Convert.ToDateTime("2010/02/14").Date;
cmdUpdate.Parameters.Add(oraParameter); oraParameter = new OracleParameter("I_LOGINID", OracleDbType.Varchar2);
oraParameter.SourceColumn = "LOGINID";//检索字段值
oraParameter.SourceVersion = DataRowVersion.Original;
oraParameter.Value = "60";
cmdUpdate.Parameters.Add(oraParameter);
////--------------------------------------------------
////删除用的oracle command
//oraParameter = new OracleParameter("I_LOGINID", OracleDbType.Varchar2);
//oraParameter.SourceColumn = "LOGINID";//检索字段值
//oraParameter.SourceVersion = DataRowVersion.Original;
//oraParameter.Value = "50";
//cmdDelete.Parameters.Add(oraParameter); ////--------------------------------------------------
////追加用的oracle command
//oraParameter = new OracleParameter("I_LOGINID", OracleDbType.Varchar2);
//oraParameter.SourceColumn = "LOGINID";//追加字段值
//oraParameter.SourceVersion = DataRowVersion.Current;
//oraParameter.Value = "70";
//cmdInsert.Parameters.Add(oraParameter); //oraParameter = new OracleParameter("I_EFFECTSTRT", OracleDbType.Varchar2);
//oraParameter.SourceColumn = "EFFECTSTRT";//追加字段值
//oraParameter.Value = "2010/02/14";
//oraParameter.SourceVersion = DataRowVersion.Current;
//cmdInsert.Parameters.Add(oraParameter); //oraParameter = new OracleParameter("I_EFFECTEND", OracleDbType.Varchar2);
//oraParameter.SourceColumn = "EFFECTEND";//追加字段值
//oraParameter.Value = "2010/02/14";
//oraParameter.SourceVersion = DataRowVersion.Current;
//cmdInsert.Parameters.Add(oraParameter); using (OracleDataAdapter dataAdapter = new OracleDataAdapter())
{
int nRecCount = 0;
DataSet i_Data = new DataSet(); dataAdapter.SelectCommand = cmdSelect;
dataAdapter.Fill(i_Data, "TB_USER");
//dataAdapter.InsertCommand = cmdInsert;//追加command设置
dataAdapter.UpdateCommand = cmdUpdate;//更新command设置
//dataAdapter.DeleteCommand = cmdDelete;//删除command设置
nRecCount = dataAdapter.Update(i_Data, "TB_USER");//数据更新
//this.GridView1.DataSource = i_Data;
//GridView1.DataBind();//将控件及其所有子控件绑定到指定的数据源
}
// Close and Dispose OracleConnection object
con.Close();
con.Dispose(); }
catch (Exception ex)
{ throw ex;
}
}
}
}
command = new SqlCommand(
"UPDATE Customers SET CustomerID = @CustomerID, CompanyName = @CompanyName " +
"WHERE CustomerID = @oldCustomerID", connection); // Add the parameters for the UpdateCommand.
command.Parameters.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID");
command.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 40, "CompanyName");
SqlParameter parameter = command.Parameters.Add(
"@oldCustomerID", SqlDbType.NChar, 5, "CustomerID");
parameter.SourceVersion = DataRowVersion.Original; adapter.UpdateCommand = command;
看过了,没看到有什么区别。单用dataAdapter.selectcommand 是可以通过的。
有两个注意的事情:),我不了解的。
1.这个是oracle的,并且是oracle provider。是不是与sqlclient/oracleclient有不同
2.单独写 .updatecommand会因为
dataAdapter.Update(i_Data, "TB_USER");//数据更新
中没有表名出错。
3.没用到的代码很多,简单建个user表就看到现象了。多谢!
"UPDATE Customers SET CustomerID = @CustomerID, CompanyName = @CompanyName " +
"WHERE CustomerID = @oldCustomerID", connection); // Add the parameters for the UpdateCommand.
command.Parameters.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID");
command.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 40, "CompanyName");
SqlParameter parameter = command.Parameters.Add(
"@oldCustomerID", SqlDbType.NChar, 5, "CustomerID");
parameter.SourceVersion = DataRowVersion.Original; adapter.UpdateCommand = command;
OracleDataAdapter da = new OracleDataAdapter();
OracleCommand cmd;
OracleParameter parm; // Create the SelectCommand. cmd = new OracleCommand("SELECT * FROM Dept " +
"WHERE DName = pDName AND Loc = pLoc", conn); cmd.Parameters.Add("pDName", OracleType.NVarChar, 14);
cmd.Parameters.Add("pLoc", OracleType.NVarChar, 13); da.SelectCommand = cmd; // Create the UpdateCommand. cmd = new OracleCommand("UPDATE Dept SET DeptNo = pDeptNo, DName = pDName " +
"WHERE DeptNo = poldDeptNo", conn); cmd.Parameters.Add("pDeptNo", OracleType.Number, 2, "DeptNo");
cmd.Parameters.Add("pDName", OracleType.NVarChar, 14, "DName"); parm = cmd.Parameters.Add("poldDeptNo", OracleType.Number, 2, "DeptNo");
parm.SourceVersion = DataRowVersion.Original; da.UpdateCommand = cmd;
using System;
using System.Data;
using Oracle.DataAccess.Client;
using System.Web.Configuration; namespace oraModify
{
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
try
{
String connectionString = WebConfigurationManager.ConnectionStrings["ConnectionString1"].ConnectionString;
OracleConnection con = new OracleConnection(connectionString);
con.Open(); //// Create the command
OracleCommand cmdSelect = new OracleCommand("", con);
// sql文
OracleCommand cmdUpdate = new OracleCommand("", con); //select statement
string strSelectSql = "SELECT * FROM TBUSER WHERE LOGINID = :I_LOGINID ";
string strUpdateSql = "UPDATE TBUSER SET EFFECTSTRT = :I_EFFECTSTRT WHERE LOGINID = :I_LOGINID "; //command和检索sql文 关联
cmdSelect.CommandText = strSelectSql;
cmdUpdate.CommandText = strUpdateSql; //sql文中变量通过oracle参数传递
OracleParameter oraParameter; //--------------------------------------------------
//取得用的oracle command
oraParameter = new OracleParameter("I_LOGINID", OracleDbType.Varchar2);
oraParameter.SourceColumn = "LOGINID";//检索字段值
oraParameter.SourceVersion = DataRowVersion.Current;
oraParameter.Value = "60";
cmdSelect.Parameters.Add(oraParameter);//字符型
//--------------------------------------------------
//更新用的oracle command
oraParameter = new OracleParameter("I_EFFECTSTRT", OracleDbType.Date);
oraParameter.SourceColumn = "EFFECTSTRT";//更新字段值
oraParameter.SourceVersion = DataRowVersion.Current;
oraParameter.Value = Convert.ToDateTime("2010/02/14").Date;
cmdUpdate.Parameters.Add(oraParameter); oraParameter = new OracleParameter("I_LOGINID", OracleDbType.Varchar2);
oraParameter.SourceColumn = "LOGINID";//检索字段值
oraParameter.SourceVersion = DataRowVersion.Original;
oraParameter.Value = "60";
cmdUpdate.Parameters.Add(oraParameter); using (OracleDataAdapter dataAdapter = new OracleDataAdapter())
{
int nRecCount = 0;
DataSet i_Data = new DataSet(); dataAdapter.SelectCommand = cmdSelect;
dataAdapter.Fill(i_Data, "TB_USER");
//dataAdapter.InsertCommand = cmdInsert;//追加command设置
dataAdapter.UpdateCommand = cmdUpdate;//更新command设置
nRecCount = dataAdapter.Update(i_Data, "TB_USER");//数据更新
}
// Close and Dispose OracleConnection object
con.Close();
con.Dispose();
}
catch (Exception ex)
{
throw ex;
}
}
}
} 整理了一下,方便看。:)
oraParameter.SourceColumn = "LOGINID";//检索字段值
oraParameter.SourceVersion = DataRowVersion.Original;
oraParameter.Value = "60";
cmdUpdate.Parameters.Add(oraParameter);
--------------------------------------------------------oraParameter.Value = "60";
区别是没有设置value
UPDATE TBUSER SET EFFECTSTRT = :I_EFFECTSTRT WHERE LOGINID = :I_LOGINID
这里的
oraParameter.Value = "60";
是上面的
I_LOGINID !!!
不用oraParameter.Value = "60";是错误的。
60时update语句的值。几楼都是错的。:)
parameter.ParameterName = configInfo.Fields[i].FieldName;
parameter.OracleType = (OracleType)configInfo.Fields[i].DataType;
parameter.SourceColumn = configInfo.Fields[i].FieldName;
parameter.Size = configInfo.Fields[i].Size;
parameter.Value = fldInfo.GetValue(o);直接这样声明参数,然后使用cmdUpdate.ExecuteNoQuery();试试
using (OracleConnection connection = new OracleConnection(connectionString))
{
OracleDataAdapter dataAdpater = new OracleDataAdapter(
"SELECT * FROM TBUSER", connection); dataAdpater.UpdateCommand = new SqlCommand(
"UPDATE TBUSER SET EFFECTSTRT = I_EFFECTSTRT WHERE LOGINID = I_LOGINID ", connection);
OracleParameter parameter = new OracleParameter("I_EFFECTSTRT", SqlDbType.Date); parameter.SourceColumn = "EFFECTSTRT";//更新字段值 dataAdpater.UpdateCommand.Parameters.Add(parameter); parameter = dataAdpater.UpdateCommand.Parameters.Add(
"I_LOGINID", SqlDbType.Varchar2); //看看这里 I_LOGINID 的长度,需要的话设置一下长度 parameter.SourceColumn = "LOGINID";
parameter.SourceVersion = DataRowVersion.Original; //关键
DataSet dataSet = new DataSet();
dataAdpater.Fill(dataSet, "TBUSER"); //这里只是更新了第一行,如果你想更新LOGINID 为60的所有记录,遍历DataSet 分别赋值即可
DataRow row = dataSet.Tables["TBUSER"].Rows[0];
row["EFFECTSTRT"] = Convert.ToDateTime("2010/02/14").Date; dataAdpater.Update(dataSet, "TBUSER");
}
{
int nRecCount = 0;
DataSet i_Data = new DataSet(); dataAdapter.SelectCommand = cmdSelect;
dataAdapter.Fill(i_Data, "TB_USER");
//dataAdapter.InsertCommand = cmdInsert;//追加command设置
dataAdapter.UpdateCommand = cmdUpdate;//更新command设置
nRecCount = dataAdapter.Update(i_Data, "TB_USER");//数据更新
} 显然你的i_Data 未做过任何改动,更新自然不会有效。dataAdapter.Update是针对DataTable中有改动的数据行,有一行被改动了,就更新那一行数据到数据库一次。再看这里:oraParameter = new OracleParameter("I_LOGINID", OracleDbType.Varchar2);
oraParameter.SourceColumn = "LOGINID";//检索字段值
oraParameter.SourceVersion = DataRowVersion.Original;
oraParameter.Value = "60"; 你既然指定了参数的具体值,也就是并非从DataTable里传递改动映射来更新,那么你根本不需要那个OracleDataAdapter,直接使用OracleCommand 调用它的ExecuteNonQuery()方法即可实现更新效果了。
需要如何改动i_Data,这个dataset;请详细些。
ExecuteNonQuery是可以的。
//更新用的oracle command
oraParameter = new OracleParameter("I_EFFECTSTRT", OracleDbType.Date);
oraParameter.SourceColumn = "EFFECTSTRT";//更新字段值
oraParameter.SourceVersion = DataRowVersion.Current;
cmdUpdate.Parameters.Add(oraParameter); using (OracleDataAdapter dataAdapter = new OracleDataAdapter())
{
int nRecCount = 0;
DataSet i_Data = new DataSet(); DataRow dr = i_Data.Tables[0].Rows[0];
dr["EFFECTSTRT"] = Convert.ToDateTime("2010/02/14").Date;//这里改动了下第一行数据。 dataAdapter.SelectCommand = cmdSelect;
dataAdapter.Fill(i_Data, "TB_USER");
//dataAdapter.InsertCommand = cmdInsert;//追加command设置
dataAdapter.UpdateCommand = cmdUpdate;//更新command设置
nRecCount = dataAdapter.Update(i_Data, "TB_USER");//数据更新
}
因为是通过表映射来改动数据库,所以必须要DataSet本身有所改变才行。
同时也因为这样,更新命令UpdateCommand的参数不要给任何值,为空即可,它会自动到DataTable中取相应的值,通过你设置的oraParameter.SourceColumn来关联。
oraParameter = new OracleParameter("I_LOGINID",OracleDbType.Varchar2);
oraParameter.SourceColumn = "LOGINID";//检索字段值
oraParameter.SourceVersion = DataRowVersion.Original;
cmdUpdate.Parameters.Add(oraParameter); 另外Sandy945的代码一直犯了一个相同的错误,Oracle的参数前缀你没有添加。
-------------------------?
DataSet dataSet = new DataSet();
dataAdpater.Fill(dataSet, "TBUSER"); //这里只是更新了第一行,如果你想更新LOGINID 为60的所有记录,遍历DataSet 分别赋值即可
//DataRow row = dataSet.Tables["TBUSER"].Rows[0];
//row["EFFECTSTRT"] = Convert.ToDateTime("2010/02/14").Date;
if(dataSet.Tables.Contains("TBUSER"))
{
for(int i=0;i<dataSet.Tables["TBUSER"].Rows.Count;i++)
{
object obj = dataSet.Tables["TBUSER"].Rows[i]["LOGINID"]??"0";
int id;
int.TryParse(obj.ToString(),out id);
if(id==60)
{
dataSet.Tables["TBUSER"].Rows[i]["EFFECTSTRT"] = DateTime.Now;
}
}
}
更新 LOGINID 字段 值为 60 的所有记录 的 EFFECTSTRT 字段 为更新时的时间