private void GetData(string selectCommand)
{
try
{
this.Open();
OracleDataAdapter dataAdapter = new OracleDataAdapter(selectCommand, MyConn);
OracleCommandBuilder commandBuilder = new OracleCommandBuilder(dataAdapter);
ds = new DataSet();
DataTable table = new DataTable();
dataAdapter.Fill(table);
bindingSource1.DataSource = table;
this.dataGridView1.DataSource = bindingSource1;
dataGridView1.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader);
this.dataGridView1.Columns["SPXXID"].HeaderText = "商品ID";
this.dataGridView1.Columns["SPBH"].HeaderText = "商品编号";
this.dataGridView1.Columns["PM"].HeaderText = "商品名称";
this.dataGridView1.Columns["DJ"].HeaderText = "商品定价";
this.dataGridView1.Columns["DWMC"].HeaderText = "出版社名称";
this.dataGridView1.Columns["ZZ"].HeaderText = "作者";
this.dataGridView1.Columns["CBNY"].HeaderText = "出版年月";
this.dataGridView1.Columns["BC"].HeaderText = "版次";
this.dataGridView1.Columns["YSSJ"].HeaderText = "印刷时间";
}
catch(OracleException ex)
{
throw ex;
}
finally
{
// this.sClose();
}
}
private void button2_Click(object sender, EventArgs e)
{
oracle.LinkToOracle("orcl", "hui", "oracle");
OracleCommand cmd = oracle.getConnection().CreateCommand();
if (radioButton1.Checked)
{
OracleDataAdapter sda = new OracleDataAdapter("select * from department", oracle.getConnection());
DataTable query = new DataTable();
query = (DataTable)dataGridView1.DataSource;
OracleCommandBuilder sb = new OracleCommandBuilder(sda);
sda.Update(query);
query.Clear();
sda.Fill(query);
}
if (radioButton2.Checked)
{
OracleDataAdapter sda = new OracleDataAdapter("select * from teacher", oracle.getConnection());
DataTable query = new DataTable();
query = (DataTable)dataGridView1.DataSource;
OracleCommandBuilder sb = new OracleCommandBuilder(sda);
sda.Update(query);
query.Clear();
sda.Fill(query);
}
oracle.LinkClose();
cmd.Dispose();
}
private DbParameter[] DiscoverSpParameterSet(string strProcedureName, bool includeReturnValueParameter)
{
DbCommand dbCommand = new DbCommand();
dbCommand.Connection = mConn;
dbCommand.CommandText = strProcedureName;
dbCommand.CommandType = CommandType.StoredProcedure;
try
{
if (mConn.State == ConnectionState.Closed)
{
mConn.Open();
}
// 查找存储过程的参数
DbCommandBuilder.DeriveParameters(dbCommand);
// 如果包含返回Result value参数（自动生成的，不需要传进去）,如果有，则删除
// Oracle中，function产生此参数，procedure则不产生
// SQL SERVER中，procedure 产生此参数,function 未测试，应该也会有
if (includeReturnValueParameter)
{
dbCommand.Parameters.RemoveAt(0);
}
// 返回找到的参数的副本
DbParameter[] discoveredParameters = new DbParameter[dbCommand.Parameters.Count];
dbCommand.Parameters.CopyTo(discoveredParameters, 0);
// 参数一刀两断,没有关系了.
dbCommand.Parameters.Clear();
// 初始化各参数为DBNull
foreach (DbParameter discoveredParameter in discoveredParameters)
{
discoveredParameter.Value = DBNull.Value;
}
return(discoveredParameters);
}
catch (Exception e)
{
WriteLogInfo("error on " + this.GetType().Name + " GetStoredProcedureParamters:" + strProcedureName + "\r\n" + e.Message);
return(null);
}
}
private void CheckServis_Click(object sender, EventArgs e)
{
ClearBtn.Visible = true;
string oradb = "Data Source=(DESCRIPTION="
+ "(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))"
+ "(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORCL)));"
+ "User Id=DBA_20122013L_GRP_028;Password=8244442;";
OracleConnection conn = new OracleConnection(oradb);
conn.Open();
string selectStmt = string.Format("select s.IMENASERVIS as Servis, l.ime, l.prezime, S.CENANASERVIS as Cena, m.IMENAMARKA as Marka, mod.IMENAMODEL as Model FROM LUGE L, KLIENTI K, odinaservis odi, SERVIS s, vozila car, marka m, modeli mod where l.embg=k.embg and k.EMBG=odi.KEMBG and odi.servisid=s.servisid and s.VOZILOID=car.VOZILOID and car.IMENAMODEL=mod.IMENAMODEL and m.IMENAMARKA=mod.IMENAMARKA ");
OracleDataAdapter dataAdapter = new OracleDataAdapter(selectStmt, conn);
OracleCommandBuilder cmb = new OracleCommandBuilder(dataAdapter);
DataTable table = new DataTable();
dataAdapter.Fill(table);
TABLE.DataSource = table;
TABLE.Visible = true;
conn.Close();
}
public int UpdateDataTable1(string sql, System.Data.DataTable dt, System.Data.OracleClient.OracleDataAdapter sda)
{
int i = 0;
try
{
System.Data.OracleClient.OracleCommandBuilder builder = new System.Data.OracleClient.OracleCommandBuilder(sda);
sda.UpdateCommand = builder.GetUpdateCommand();
if (dt.GetChanges() != null)
{
i = sda.Update(dt);
dt = GetDataTable(sql);
}
}
catch (Exception ex)
{
MessageBox.Show("Error:" + ex.Message.ToString());
return(-1);
}
return(i);
}
/// <summary>
/// ���ݰ�
/// </summary>
/// <param name="sqlstr"></param>
private void DataBind(string sqlstr)
{
try
{
OracleConnection con = new OracleConnection(DataAccess.OIDSConnStr);
con.Open();
oda = new OracleDataAdapter(sqlstr, con);
OracleCommandBuilder builder = new OracleCommandBuilder(oda);
DataSet ds = new DataSet();
oda.Fill(ds);
bs.DataSource = ds.Tables[0];
this.EditDgv.DataSource = bs;
this.bindingNavigator1.BindingSource = bs;
con.Close();
ds.Dispose();
}
catch (OracleException ex)
{
MessageBox.Show(ex.Message.ToString());
return;
}
}
/// <summary>
/// 动态创建TreeView
/// </summary>
/// <param name="sqlText">传入的SQL语句</param>
/// <param name="nodes">TreeView节点集</param>
public void CreateTreeView(string sqlText, System.Windows.Forms.TreeNodeCollection nodes)
{
DataTable dbTable = new DataTable();//实例化一个DataTable数据表对象
try
{
OracleConnection con = new OracleConnection(DataAccess.OIDSConnStr);
con.Open();
OracleDataAdapter oda = new OracleDataAdapter(sqlText, con);
OracleCommandBuilder builder = new OracleCommandBuilder(oda);
DataSet ds = new DataSet();
oda.Fill(ds);
dbTable = ds.Tables[0];
con.Close();
ds.Dispose();
}
catch (OracleException ex)
{
MessageBox.Show(ex.Message.ToString());
return;
}
//将第一级菜单取出生成TreeView的节点,作为递归运算的入口递归查询出TreeView的所有节点数据
CreateTreeViewRecursive(nodes, dbTable, 0);
}
private void GetData(string selectCommand)
{
try
{
this.Open();
OracleDataAdapter dataAdapter = new OracleDataAdapter(selectCommand, MyConn);
OracleCommandBuilder commandBuilder = new OracleCommandBuilder(dataAdapter);
ds = new DataSet();
dtInfo.Locale = System.Globalization.CultureInfo.InvariantCulture;
dataAdapter.Fill(ds, "JT_J_KXNR");
dtInfo = ds.Tables["JT_J_KXNR"];
InitDataSet();
}
catch (OracleException)
{
MessageBox.Show("To run this example, replace the value of the " +
"connectionString variable with a connection string that is " +
"valid for your system.");
}
}
public static void DataAdapterTest2_Update (OracleConnection con)
{
Console.WriteLine("================================");
Console.WriteLine("=== Adapter Update =============");
Console.WriteLine("================================");
OracleTransaction transaction = con.BeginTransaction ();
Console.WriteLine(" Create adapter...");
OracleCommand selectCmd = con.CreateCommand ();
selectCmd.Transaction = transaction;
selectCmd.CommandText = "SELECT * FROM mono_adapter_test";
OracleDataAdapter da = new OracleDataAdapter(selectCmd);
Console.WriteLine(" Create command builder...");
OracleCommandBuilder mycb = new OracleCommandBuilder(da);
Console.WriteLine(" Create data set ...");
DataSet ds = new DataSet();
Console.WriteLine(" Set missing schema action...");
Console.WriteLine(" Fill data set via adapter...");
da.Fill(ds, "mono_adapter_test");
DataRow myRow;
Console.WriteLine(" New Row...");
myRow = ds.Tables["mono_adapter_test"].Rows[0];
Console.WriteLine("Tables Count: " + ds.Tables.Count.ToString());
DataTable table = ds.Tables["mono_adapter_test"];
DataRowCollection rows;
rows = table.Rows;
Console.WriteLine(" Row Count: " + rows.Count.ToString());
myRow = rows[0];
byte[] bytes = new byte[] { 0x62,0x63,0x64,0x65,0x66,0x67 };
Console.WriteLine(" Set values in the new DataRow...");
myRow["varchar2_value"] = "Super Power!";
myRow["number_scaled_value"] = 12.35;
myRow["number_integer_value"] = 457;
myRow["float_value"] = 198.76;
myRow["date_value"] = new DateTime(2002,08,09);
//myRow["char_value"] = "Juliet";
myRow["clob_value"] = "this is a clob";
myRow["blob_value"] = bytes;
Console.WriteLine("da.Update(ds...");
da.Update(ds, "mono_adapter_test");
transaction.Commit();
mycb.Dispose();
mycb = null;
}
public static void DataAdapterTest2_Delete (OracleConnection con)
{
Console.WriteLine("================================");
Console.WriteLine("=== Adapter Delete =============");
Console.WriteLine("================================");
OracleTransaction transaction = con.BeginTransaction ();
Console.WriteLine(" Create adapter...");
OracleDataAdapter da = new OracleDataAdapter("SELECT * FROM mono_adapter_test", con);
Console.WriteLine(" Create command builder...");
OracleCommandBuilder mycb = new OracleCommandBuilder(da);
Console.WriteLine(" set transr...");
da.SelectCommand.Transaction = transaction;
Console.WriteLine(" Create data set ...");
DataSet ds = new DataSet();
Console.WriteLine("Fill data set via adapter...");
da.Fill(ds, "mono_adapter_test");
Console.WriteLine("delete row...");
ds.Tables["mono_adapter_test"].Rows[0].Delete();
Console.WriteLine("da.Update(table...");
da.Update(ds, "mono_adapter_test");
Console.WriteLine("Commit...");
transaction.Commit();
mycb.Dispose();
mycb = null;
}
public virtual void SelectAll(string orderBy)
{
//check Oracle Connection
if (myOracleConnection == null)
throw new Exception("No connection established.");
try
{
//no need to open the connection manually, DataAdapter will open
//and close it automatically, in fact it is recommended not to
//explicity open the connection: in case of an error, raised by
//the DataAdapter.Fill() or DataAdapter.Update() method, the connection
//will stay open
//myOracleConnection.Open();
//read all datasets (rows) from table
OracleCommand myOracleCommand = new OracleCommand("SELECT * FROM " + table + " ORDER BY " + orderBy, myOracleConnection);
myOracleDataAdapter = new OracleDataAdapter();
myOracleDataAdapter.SelectCommand = myOracleCommand;
//Create CommandBuilder to automatically generate Update/Delete/Insert-Commands
//for DataAdapter used by method writeToDb()
OracleCommandBuilder myOracleCommandBuilder = new OracleCommandBuilder(myOracleDataAdapter);
//Add Tablenames to Dataset
myOracleDataAdapter.TableMappings.Clear();
myOracleDataAdapter.TableMappings.Add("Table", table);
//fill DataSet
myOracleDataAdapter.Fill(dataSet, table);
//connection established
isConnected = true;
}
catch (OracleException e)
{
Trace.WriteLine("Oracle-Error: ", "Error");
Trace.WriteLine("Code: " + e.Code, "Error");
Trace.WriteLine("Message: " + e.Message, "Error");
Trace.WriteLine("Source: " + e.Source, "Error");
//connection not established
isConnected = false;
}
finally
{
//myOracleConnection.Close();
}
}
public static void DataAdapterTest2_Insert (OracleConnection con)
{
Console.WriteLine("================================");
Console.WriteLine("=== Adapter Insert =============");
Console.WriteLine("================================");
OracleTransaction transaction = con.BeginTransaction ();
Console.WriteLine(" Create adapter...");
OracleDataAdapter da = new OracleDataAdapter("select * from mono_adapter_test", con);
da.SelectCommand.Transaction = transaction;
Console.WriteLine(" Create command builder...");
OracleCommandBuilder mycb = new OracleCommandBuilder(da);
Console.WriteLine(" Create data set ...");
DataSet ds = new DataSet();
Console.WriteLine(" Fill data set via adapter...");
da.Fill(ds, "mono_adapter_test");
Console.WriteLine(" New Row...");
DataRow myRow;
myRow = ds.Tables["mono_adapter_test"].NewRow();
byte[] bytes = new byte[] { 0x45,0x46,0x47,0x48,0x49,0x50 };
Console.WriteLine(" Set values in the new DataRow...");
myRow["varchar2_value"] = "OracleClient";
myRow["number_whole_value"] = 22;
myRow["number_scaled_value"] = 12.34;
myRow["number_integer_value"] = 456;
myRow["float_value"] = 98.76;
myRow["date_value"] = new DateTime(2001,07,09);
Console.WriteLine(" *** FIXME; char value not working");
//myRow["char_value"] = "Romeo";
myRow["clob_value"] = "clobtest";
myRow["blob_value"] = bytes;
Console.WriteLine(" Add DataRow to DataTable...");
ds.Tables["mono_adapter_test"].Rows.Add(myRow);
Console.WriteLine("da.Update(ds...");
da.Update(ds, "mono_adapter_test");
transaction.Commit();
mycb.Dispose();
mycb = null;
}
/// <summary>
/// ���ݰ�
/// </summary>
/// <param name="sqlstr"></param>
private void DataBind(string sqlstr)
{
try
{
OracleConnection con = new OracleConnection(DataAccess.OIDSConnStr);
con.Open();
OracleDataAdapter oda = new OracleDataAdapter(sqlstr, con);
OracleCommandBuilder builder = new OracleCommandBuilder(oda);
DataSet ds = new DataSet();
oda.Fill(ds);
checkedListBox1.DataSource = ds.Tables[0];
checkedListBox1.DisplayMember = ds.Tables[0].Columns[1].ToString(); //Ҫ��ʾ��������
checkedListBox1.ValueMember = ds.Tables[0].Columns[0].ToString(); //�洢��������
con.Close();
ds.Dispose();
}
catch (OracleException ex)
{
MessageBox.Show(ex.Message.ToString());
return;
}
}
public virtual void SelectAll()
{
//Connection string for Oracle 3.51
/*myOracleConnectionString = "DRIVER={MySQL Oracle 3.51 Driver};" +
"SERVER=localhost;" +
"DATABASE=" + database + ";" +
"UID=" + user + ";" +
"PASSWORD="******";" +
"OPTION=3";
*/
//check Oracle Connection
if (myOracleConnection == null)
throw new Exception("No connection established.");
try
{
//no need to open the connection manually, DataAdapter will open
//and close it automatically, in fact it is recommended not to
//explicity open the connection: in case of an error, raised by
//the DataAdapter.Fill() or DataAdapter.Update() method, the connection
//will stay open
//myOracleConnection.Open();
//read all Datasets from table
OracleCommand myOracleCommand = new OracleCommand("SELECT * FROM " + table, myOracleConnection);
myOracleDataAdapter = new OracleDataAdapter();
myOracleDataAdapter.SelectCommand = myOracleCommand;
//Create CommandBuilder to generate Update/Delete/Insert-Commands for writeToDb()
OracleCommandBuilder myOracleCommandBuilder = new OracleCommandBuilder(myOracleDataAdapter);
//Add Tablenames to Dataset
myOracleDataAdapter.TableMappings.Clear();
myOracleDataAdapter.TableMappings.Add("Table", table);
//fill DataSet
myOracleDataAdapter.Fill(dataSet, table);
//connection established
isConnected = true;
}
catch (OracleException e)
{
Trace.WriteLine("Oracle-Error: ", "Error");
Trace.WriteLine("Code: " + e.Code, "Error");
Trace.WriteLine("Message: " + e.Message, "Error");
Trace.WriteLine("Source: " + e.Source, "Error");
//return with error
isConnected=false;
}
finally
{
//myOracleConnection.Close();
}
}
//----------------------------------------------------------------------------------------------------
/// <summary>
/// 아답터 생성
/// </summary>
/// <param name="table_name">테이블 이름</param>
/// <returns>데이터어답터 객체</returns>
//----------------------------------------------------------------------------------------------------
public DbDataAdapter CreateAdapter( string table_name )
{
if( !Connect() ) return null;
// Select 커멘더 생성
DbCommand command = m_connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = "SELECT * FROM " + table_name;
// 어답터 생성
DbDataAdapter adapter=null;
switch(m_type)
{
case eType.SQLite: adapter = new SqliteDataAdapter((SqliteCommand)command); break;
case eType.MySQL: adapter = new MySqlDataAdapter((MySqlCommand)command); break;
case eType.MSSQL: adapter = new SqlDataAdapter((SqlCommand)command); break;
case eType.Oracle: adapter = new OracleDataAdapter((OracleCommand)command); break;
}
// Insert,Update,Delete 컴멘더 생성
DbCommandBuilder builder = null;
switch(m_type)
{
case eType.SQLite: builder = new SqliteCommandBuilder(); break;
case eType.MySQL: builder = new MySqlCommandBuilder(); break;
case eType.MSSQL: builder = new SqlCommandBuilder(); break;
case eType.Oracle: builder = new OracleCommandBuilder(); break;
}
builder.DataAdapter = adapter;
adapter.InsertCommand = builder.GetInsertCommand();
adapter.UpdateCommand = builder.GetUpdateCommand();
adapter.DeleteCommand = builder.GetDeleteCommand();
return adapter;
}
public static void GetProjectPlan(string queryString, string frmtext, OracleCommandBuilder builder, DataSet myDS, DataGridView dgv, OracleDataAdapter adapter, BindingSource bdsource, BindingNavigator bdn)
{
//OracleConnection conn = new OracleConnection(DataAccess.OIDSConnStr);//���conn����
//conn.Open();
//OracleCommand cmd = new OracleCommand(queryString, conn);
//cmd.CommandType = System.Data.CommandType.StoredProcedure;
//OracleParameter pram = new OracleParameter("V_CS", OracleType.Cursor);
//pram.Direction = System.Data.ParameterDirection.Output;
//cmd.Parameters.Add(pram);
//cmd.Parameters.Add("formtext_in", OracleType.VarChar).Value = frmtext;
//cmd.Parameters["formtext_in"].Direction = System.Data.ParameterDirection.Input;
////OracleDataAdapter adapter = new OracleDataAdapter(cmd);
//myDS = new DataSet();
//adapter = new OracleDataAdapter(cmd);
//builder = new OracleCommandBuilder(adapter);
////adapter.FillSchema(
//adapter.Fill(myDS);
////BindingSource bds = new BindingSource();
//bdsource.DataSource = myDS.Tables[0];
//dgv.DataSource = bdsource;
//bdn.BindingSource = bdsource;
////dgv.DataSource = myDS.Tables["plantable"];
//myDS.Dispose();
}
public void RetrieveOracleData(string sql)
{
this.oracleadapter = new OracleDataAdapter(sql, (OracleConnection)this.conn);
this.oraclebuilder = new OracleCommandBuilder(this.oracleadapter);
ds = new DataSet();
this.oracleadapter.Fill(ds, tablename);
}
private void GetData_nothing(string selectCommand)
{
try
{
// Specify a connection string. Replace the given value with a valid connection string for a Northwind SQL Server sample database accessible to your system.
this.Open();
// Create a new data adapter based on the specified query.
OracleDataAdapter dataAdapter = new OracleDataAdapter(selectCommand, MyConn);
// Create a command builder to generate SQL update, insert, and delete commands based on selectCommand. These are used to update the database.
OracleCommandBuilder commandBuilder = new OracleCommandBuilder(dataAdapter);
// Populate a new data table and bind it to the BindingSource.
ds = new DataSet();
DataTable table = new DataTable();
table.Locale = System.Globalization.CultureInfo.InvariantCulture;
dataAdapter.Fill(ds, "JT_J_KXNR");
table = ds.Tables["JT_J_KXNR"];
bindingSource1.DataSource = table;
// bindingSource1.DataMember = "SYS_ROLE";
dataGridView1.DataSource = bindingSource1;
this.dataGridView1.Columns[0].HeaderText = "ID";
this.dataGridView1.Columns[1].HeaderText = "内容编号";
this.dataGridView1.Columns[2].HeaderText = "款项内容";
this.dataGridView1.Columns[3].HeaderText = "助记码";
this.dataGridView1.Columns[4].HeaderText = "内容简称";
}
catch (OracleException)
{
MessageBox.Show("To run this example, replace the value of the " +
"connectionString variable with a connection string that is " +
"valid for your system.");
}
}
//要在C#中使用引用参数 ，必须在函数声明以及函数调用中都明确地使用关键字ref
public static void createDbConnection(DbName dbName, DbType dbType, ref IDbDataAdapter ada, ref DbCommandBuilder cb, string sql)
{
String connStr = getConnStr(dbName, dbType);
Dictionary<string, string> ht = splitConnectionString(connStr);
if (dbName == DbName.Oracle)
{
string cnStr = "password="******"password"] + ";data source=" + ht["data source"] + ";user id=" + ht["user id"];
ada = new OracleDataAdapter(sql, cnStr);//构造查询(select)适配器
cb = new OracleCommandBuilder((OracleDataAdapter)ada);//该对象在adapter执行update的时候，自动生成insert，update，delete语句。
}
else if (dbName == DbName.MSSql)
{
string cnStr = "Server=" + ht["data source"] + ";DataBase=" + ht["initial catalog"] + ";Uid=" + ht["user id"] + ";pwd=" + ht["password"];
ada = new SqlDataAdapter(sql, cnStr);
cb = new SqlCommandBuilder((SqlDataAdapter)ada);
}
else if (dbName == DbName.MySql)
{
string cnStr = "Database=" + ht["initial catalog"] + ";Data Source=" + ht["ip"] + ";User Id=" + ht["user id"] + ";Password="******"password"];
ada = new MySqlDataAdapter(sql, cnStr);
cb = new MySqlCommandBuilder((MySqlDataAdapter)ada);
}
else if (dbName == DbName.WinCE)
{
ada = new SqlCeDataAdapter(sql, connStr);
cb = new SqlCeCommandBuilder((SqlCeDataAdapter)ada);
}
}
//批量更新
public static bool MultiUpdateData(DataTable data, string Columns, string tableName)
{
//string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["constr"].ToString();
string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["conn"].ToString();
using (OracleConnection connection = new OracleConnection(connectionString))
{
string SQLString = string.Format("select {0} from {1} where rownum=0", Columns, tableName);
using (OracleCommand cmd = new OracleCommand(SQLString, connection))
{
try
{
connection.Open();
OracleDataAdapter myDataAdapter = new OracleDataAdapter();
myDataAdapter.SelectCommand = new OracleCommand(SQLString, connection);
OracleCommandBuilder custCB = new OracleCommandBuilder(myDataAdapter);
custCB.ConflictOption = ConflictOption.OverwriteChanges;
custCB.SetAllValues = true;
foreach (DataRow dr in data.Rows)
{
if (dr.RowState == DataRowState.Unchanged)
{
dr.SetModified();
}
}
myDataAdapter.Update(data);
data.AcceptChanges();
myDataAdapter.Dispose();
return true;
}
catch (System.Data.OracleClient.OracleException E)
{
connection.Close();
return false;
}
}
}
}
//批量插入
public static bool MultiInsertData(DataSet ds, string Columns, string tableName)
{
//string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["constr"].ToString();
string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["conn"].ToString();
using (OracleConnection connection = new OracleConnection(connectionString))
{
string SQLString = string.Format("select {0} from {1} where rownum=0", Columns, tableName);
using (OracleCommand cmd = new OracleCommand(SQLString, connection))
{
try
{
connection.Open();
OracleDataAdapter myDataAdapter = new OracleDataAdapter();
myDataAdapter.SelectCommand = new OracleCommand(SQLString, connection);
myDataAdapter.UpdateBatchSize = 0;
OracleCommandBuilder custCB = new OracleCommandBuilder(myDataAdapter);
DataTable dt = ds.Tables[0].Copy();
DataTable dtTemp = dt.Clone();
int times = 0;
for (int count = 0; count < dt.Rows.Count; times++)
{
for (int i = 0; i < 400 && 400 * times + i < dt.Rows.Count; i++, count++)
{
dtTemp.Rows.Add(dt.Rows[count].ItemArray);
}
myDataAdapter.Update(dtTemp);
dtTemp.Rows.Clear();
}
dt.Dispose();
dtTemp.Dispose();
myDataAdapter.Dispose();
return true;
}
catch (System.Data.OracleClient.OracleException E)
{
connection.Close();
return false;
}
}
}
}
/// <summary>
/// 功能：把dt表中的数据全部追加到Oracle数据中指定的表
/// 注意：要保证dt表的列与数据库中指定表默认检索的列的次序相同
/// </summary>
/// <param name="strconn">链接字符串</param>
/// <param name="dt">要追加的数据</param>
/// <param name="tb_name">数据库中的表名</param>
public void insertDB(DataTable dt, string tb_name)
{
try
{
conn = new OracleConnection(strconn);
conn.Open();
//获取表结构，构造new_dt
com = conn.CreateCommand();
com.CommandText = "select * from " + tb_name + " where 0=1"; ;
com.CommandType = CommandType.Text;
da = new OracleDataAdapter(com);
DataTable new_dt = new DataTable();
da.Fill(new_dt);
//复制dt的数据到new_dt中
DataRow new_dr = new_dt.NewRow();
foreach (DataRow dr in dt.Rows)
{
for (int i = 0; i < dt.Columns.Count; ++i)
{
new_dr[i] = dr[i];
}
new_dt.Rows.Add(new_dr);
}
//自动构造InsertCommand命令
ocb = new OracleCommandBuilder(da);
da.InsertCommand = ocb.GetInsertCommand();
da.UpdateBatchSize = 5000;
da.Update(new_dt);
}
catch (Exception ex)
{
//暂不处理，抛给上层做处理
throw (ex);
}
finally
{
conn.Close();
ocb.Dispose();
da.Dispose();
com.Dispose();
conn.Dispose();
}
}
private void GetSelectData(string selectCommand)
{
try
{
this.Open();
OracleDataAdapter dataAdapter = new OracleDataAdapter(selectCommand, MyConn);
OracleCommandBuilder commandBuilder = new OracleCommandBuilder(dataAdapter);
ds = new DataSet();
dtInfo.Locale = System.Globalization.CultureInfo.InvariantCulture;
dataAdapter.Fill(ds, "base_operator");
dtInfo = ds.Tables["base_operator"];
bindingSource1.DataSource = dtInfo;
dataGridView1.DataSource = bindingSource1;
this.dataGridView1.Columns["OPERATORID"].HeaderText = "ID";
this.dataGridView1.Columns["operatorno"].HeaderText = "员工编号";
this.dataGridView1.Columns["operatorname"].HeaderText = "员工姓名";
this.dataGridView1.Columns["fastcode"].HeaderText = "助记符";
}
catch (OracleException)
{
MessageBox.Show("To run this example, replace the value of the " +
"connectionString variable with a connection string that is " +
"valid for your system.");
}
}
private void GetSelectData(string selectCommand)
{
try
{
this.Open();
OracleDataAdapter dataAdapter = new OracleDataAdapter(selectCommand, MyConn);
OracleCommandBuilder commandBuilder = new OracleCommandBuilder(dataAdapter);
ds = new DataSet();
dtInfo.Locale = System.Globalization.CultureInfo.InvariantCulture;
dataAdapter.Fill(ds, "JT_J_KXNR");
dtInfo = ds.Tables["JT_J_KXNR"];
bindingSource1.DataSource = dtInfo;
dataGridView1.DataSource = bindingSource1;
this.dataGridView1.Columns[0].HeaderText = "ID";
this.dataGridView1.Columns[1].HeaderText = "内容编号";
this.dataGridView1.Columns[2].HeaderText = "款项内容";
this.dataGridView1.Columns[3].HeaderText = "助记码";
this.dataGridView1.Columns[4].HeaderText = "内容简称";
}
catch (OracleException ex)
{
MessageBox.Show(ex.Message);
}
}
/// <summary>
/// Updates the underlying data of the <see cref="DataTable"/> using <see cref="OracleConnection"/>, and
/// returns the number of rows successfully updated.
/// </summary>
/// <param name="sourceData">The <see cref="DataTable"/> used to update the underlying data source.</param>
/// <param name="sourceSql">The SQL statement used initially to populate the <see cref="DataTable"/>.</param>
/// <param name="connection">The <see cref="OracleConnection"/> to use for updating the underlying data source.</param>
/// <returns>The number of rows successfully updated from the <see cref="DataTable"/>.</returns>
public static int UpdateData(this OracleConnection connection, DataTable sourceData, string sourceSql)
{
OracleDataAdapter dataAdapter = new OracleDataAdapter(sourceSql, connection);
OracleCommandBuilder commandBuilder = new OracleCommandBuilder(dataAdapter);
return dataAdapter.Update(sourceData);
}
//----------------------------------------------------------------------------------------------------
/// <summary>
/// 테이블 읽기
/// </summary>
/// <param name="table">테이블 객체</param>
/// <param name="sql">SQL문</param>
/// <returns>데이터어답터 객체</returns>
//----------------------------------------------------------------------------------------------------
public DbDataAdapter ReadTable( DataTable table, string sql )
{
if( !Connect() ) return null;
DbDataAdapter adapter=null;
try
{
// Select 커멘더 생성
m_command = m_connection.CreateCommand();
m_command.CommandType = CommandType.Text;
m_command.CommandText = sql;
// 어답터 생성
switch(m_type)
{
case eType.SQLite: adapter = new SqliteDataAdapter((SqliteCommand)m_command); break;
case eType.MySQL: adapter = new MySqlDataAdapter((MySqlCommand)m_command); break;
case eType.MSSQL: adapter = new SqlDataAdapter((SqlCommand)m_command); break;
case eType.Oracle: adapter = new OracleDataAdapter((OracleCommand)m_command); break;
}
// Insert,Update,Delete 컴멘더 생성
DbCommandBuilder builder = null;
switch(m_type)
{
case eType.SQLite: builder = new SqliteCommandBuilder(); break;
case eType.MySQL: builder = new MySqlCommandBuilder(); break;
case eType.MSSQL: builder = new SqlCommandBuilder(); break;
case eType.Oracle: builder = new OracleCommandBuilder(); break;
}
builder.DataAdapter = adapter;
adapter.InsertCommand = builder.GetInsertCommand();
adapter.UpdateCommand = builder.GetUpdateCommand();
adapter.DeleteCommand = builder.GetDeleteCommand();
// 테이블 채워 넣기
adapter.Fill(table);
return adapter;
}
catch( Exception ex )
{
Log( ex.ToString() );
m_error = ex.Message;
CloseQuery();
return null;
}
}
private void buttonWriteDB_Click(object sender, EventArgs e)
{
if (adapter is SqlDataAdapter)
{
SqlCommandBuilder builder = new SqlCommandBuilder((SqlDataAdapter)adapter);
}
else if (adapter is OracleDataAdapter)
{
OracleCommandBuilder builder = new OracleCommandBuilder((OracleDataAdapter)adapter);
}
else if (adapter is OleDbDataAdapter)
{
OleDbCommandBuilder builder = new OleDbCommandBuilder((OleDbDataAdapter)adapter);
}
else if (adapter is OdbcDataAdapter)
{
OdbcCommandBuilder builder = new OdbcCommandBuilder((OdbcDataAdapter)adapter);
}
#if MySql
else if (adapter.GetType().Name == "MySqlDataAdapter")
{
MySqlCommandBuilder builder = new MySqlCommandBuilder((MySqlDataAdapter)adapter);
}
#endif
try
{
adapter.Update(dataSet);
MessageBox.Show(this, "Write to Database successfully", "Info", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(this, ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void Sales_Click(object sender, EventArgs e)
{
ClearBtn.Visible = true;
TABLE.DataSource = null;
string oradb = "Data Source=(DESCRIPTION="
+ "(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))"
+ "(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORCL)));"
+ "User Id=DBA_20122013L_GRP_028;Password=8244442;";
OracleConnection conn = new OracleConnection(oradb);
conn.Open();
string selectStmt = string.Format("select p.brdogovor as dogovor , p.DATANADOGOVOR as Data , p.CENANADOGOVOR as Cena, m.IMENAMARKA as Marka, mod.Imenamodel as Model , l.ime , l.prezime , s.ime, s.prezime from Prodazba p , Klienti k, luge l,luge s, vraboteni v, vozila c ,Marka m , modeli mod where s.embg=k.embg and k.embg=p.kembg and l.embg=v.embg and v.embg=p.sembg and p.BRDOGOVOR=c.BRDOGOVOR and mod.IMENAMODEL=c.IMENAMODEL and mod.imenamarka=m.imenamarka");
OracleDataAdapter dataAdapter = new OracleDataAdapter(selectStmt, conn);
OracleCommandBuilder cmb = new OracleCommandBuilder(dataAdapter);
DataTable table = new DataTable();
dataAdapter.Fill(table);
TABLE.DataSource = table;
TABLE.Visible = true;
conn.Close();
}
/// <summary>
/// 将DataTable插入数据库中
/// </summary>
/// <param name="dbtablename">数据库中要插入的表名</param>
/// <param name="dt">DataTable</param>
/// <param name="con">数据库连接字符串</param>
/// <returns></returns>
public bool InsertDatatable(string queryString, DataTable dt, string con)
{
try
{
using (OracleConnection connection = new OracleConnection(con))
{
OracleDataAdapter adapter = new OracleDataAdapter();
adapter.SelectCommand = new OracleCommand(queryString, connection);
OracleCommandBuilder builder = new OracleCommandBuilder(adapter);
connection.Open();
foreach (DataRow dr in dt.Rows)
{
dr.AcceptChanges();
dr.SetAdded();
}
adapter.Update(dt);
connection.Close();
}
return true;
}
catch (Exception e)
{
Log.Error(String.Format("检疫监管{0}表统计数据保存时", queryString.Replace("select * from", "")), e);
return false;
}
}
//通过DATASET更新数据库
public int UpdateFromDataSet(string strSql, string strTabName, string strList, string strPath)
{
try
{
DataSet changeDat = GetExcelData(strPath, strList, strTabName);
if (changeDat == null)
{
return 0;
}
string strConnection = GetString();
OracleConnection conn = new OracleConnection(strConnection);
OracleDataAdapter oda = new OracleDataAdapter(strSql, conn);
OracleCommandBuilder oraCmd = new OracleCommandBuilder(oda);
DataSet ds = new DataSet();
oda.Fill(ds);
DataTable tabSou = ds.Tables[0];
DataTable tabAid = changeDat.Tables[strTabName];
for (int i = 0; i < tabAid.Rows.Count; i++)
{
DataRow row1 = tabAid.Rows[i];
DataRow row = tabSou.NewRow();
for (int j = 0; j < tabAid.Columns.Count; j++)
{
row[j] = row1[j];
}
tabSou.Rows.Add(row);
}
int intFlag = oda.Update(tabSou);
return intFlag;
}
catch (Exception err)
{
return 0;
}
}
public override DbCommandBuilder MakeCommandBuilder()
{
OracleCommandBuilder oraclebuilder = new OracleCommandBuilder();
return oraclebuilder;
}
