110,534
社区成员
发帖
与我相关
我的任务
分享
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;
}
}
}
}
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;
dataAdapter.Update(i_Data, "TB_USER");//数据更新
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;
//关键
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;
}
}
}
oraParameter = new OracleParameter("I_LOGINID",OracleDbType.Varchar2);
oraParameter.SourceColumn = "LOGINID";//检索字段值
oraParameter.SourceVersion = DataRowVersion.Original;
cmdUpdate.Parameters.Add(oraParameter);
//更新用的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");//数据更新
}