using System.Collections;
using System;
using System.Data;
using System.Data.Odbc;
using System.Data.OleDb;
using System.ComponentModel;//这行和下一行都要先在引用中填加system.data.oracleclient
using System.Data.OracleClient;
public class OracleData
{
public DataTable dtYourData;
private static string datapath = "C:\\ap4.mdb";
private string _name = CMSS_SVR.Properties.Settings.Default.orcal_name;
private string _pass = CMSS_SVR.Properties.Settings.Default.orcal_pass;//"admin#ccrbcn";
private string _ip = CMSS_SVR.Properties.Settings.Default.orcal_ip;//"114.255.75.23";
private string _port = CMSS_SVR.Properties.Settings.Default.orcal_port;//"1521";
private string _con = "";
private string con;
// private string con = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" + datapath;
private OracleConnection oCon;
public IList mycopy_colunm_name = new ArrayList();
private IList mycopy_colunm_type = new ArrayList();
/// <summary>
/// 初始化数据库
/// </summary>
/// <param name="name"></param>
/// <param name="pass"></param>
/// <param name="datasource"></param>
public void Start (string name,string pass,string ip,string port) {
_con = "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=" + ip + ") (PORT=" + port + ")))(CONNECT_DATA=(SERVICE_NAME=orcl)));Persist Security Info=True"
+ ";user=" + name + ";password=" + pass + ";";//定义连接数据库的字符串
}
public OracleData(string name, string pass, string ip, string port)
{
Start(name,pass,ip,port);
}
public OracleData()
{
Start(_name, _pass, _ip, _port);
}
void UpDate()
{
//Debug.Log(dtYourData.Rows[0][dtYourData.Columns[1].ColumnName].ToString());
}
public Boolean MyUpdatasql(string strsql)///跟新数据库
{
oCon = new OracleConnection (_con);
OracleCommand oCmd = new OracleCommand(strsql, oCon);
try
{
oCon.Open();
// lets use a datareader to fill that table!
oCmd.ExecuteNonQuery();
oCon.Close();
}
catch (Exception ex)
{
Console.WriteLine("错误的字符串:"+strsql+" --->"+ex.ToString());
if (oCon.State != ConnectionState.Closed)
oCon.Close();
oCon.Dispose();
return false;
}
return true;
//Debug.Log(dtYourData.Rows[0]["work_1"].ToString());
}
public void myselect(string strsql, string tableName)
{
oCon = new OracleConnection (_con);
OracleCommand oCmd = new OracleCommand(strsql, oCon);
dtYourData = null;
dtYourData = new DataTable(tableName);
try
{
// open the connection
oCon.Open();
// lets use a datareader to fill that table!
OracleDataReader rData = oCmd.ExecuteReader();
// now lets blast that into the table by sheer man power!
dtYourData.Load(rData);
// close that reader!
rData.Close();
// close your connection to the spreadsheet!
oCon.Close();
}
catch (Exception e)
{
Console.WriteLine("错误的字符串:" + strsql + " --->" + e.ToString());
}
finally
{
if (oCon.State != ConnectionState.Closed)
oCon.Close();
oCon.Dispose();
}
}
public void inser_or_delete(string strsql)
{
oCon = new OracleConnection (_con);
OracleCommand oCmd = new OracleCommand(strsql, oCon);
try
{
// open the connection
oCon.Open();
// lets use a datareader to fill that table!
oCmd.ExecuteNonQuery();
// now lets blast that into the table by sheer man power!
// close your connection to the spreadsheet!
oCon.Close();
}
catch (Exception ex)
{
Console.WriteLine("错误的字符串:" + strsql + " --->" + ex.ToString());
}
finally
{
if (oCon.State != ConnectionState.Closed)
oCon.Close();
oCon.Dispose();
}
}
public void copytable(string oldname,string newname)复制数据库表
{
string strsql = "select * from "+oldname;
mycopy_colunm_name =new ArrayList();
myselect(strsql,oldname);
int n =dtYourData.Columns.Count;
for(int i=0;i<n;i++)
{
mycopy_colunm_name.Add(dtYourData.Columns[i].ColumnName);//得到名字列表
}
if(newname==""){
return;
}
IList rows = new ArrayList();
if(dtYourData.Rows.Count!=0)
{
for(int i=0;i<dtYourData.Rows.Count;i++)
{
rows.Add(dtYourData.Rows[i]["层名"].ToString());
}
}
///创建
strsql = "create table "+newname+" ( ID int";
for(int i=1;i<n;i++)
{
strsql +=","+ mycopy_colunm_name[i]+" string";
}
strsql +=")";
oCon = new OracleConnection (_con);
OracleCommand oCmd = new OracleCommand(strsql, oCon);
try
{
// open the connection
oCon.Open();
// lets use a datareader to fill that table!
oCmd.ExecuteNonQuery();
// now lets blast that into the table by sheer man power!
// close your connection to the spreadsheet!
oCon.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
if (oCon.State != ConnectionState.Closed)
oCon.Close();
oCon.Dispose();
}
if(rows.Count!=0)
{
for(int i=0;i<rows.Count;i++)
{
strsql ="insert into "+newname+"(层名) "+" values('" +
rows[i] +"')";
oCon = new OracleConnection (_con);
oCmd = new OracleCommand(strsql, oCon);
try
{
// open the connection
oCon.Open();
// lets use a datareader to fill that table!
oCmd.ExecuteNonQuery();
// now lets blast that into the table by sheer man power!
// close your connection to the spreadsheet!
oCon.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
if (oCon.State != ConnectionState.Closed)
oCon.Close();
oCon.Dispose();
}
}
}
}
public bool isexist(string tablename)表是否存在 存在 true,不存在 false
{
string strsql = "select * from "+tablename;
try
{
oCon = new OracleConnection (_con);
OracleCommand oCmd = new OracleCommand(strsql, oCon);
dtYourData = null;
dtYourData = new DataTable(tablename);
// open the connection
oCon.Open();
// lets use a datareader to fill that table!
OracleDataReader rData = oCmd.ExecuteReader();
// now lets blast that into the table by sheer man power!
dtYourData.Load(rData);
// close that reader!
rData.Close();
// close your connection to the spreadsheet!
oCon.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
if (oCon.State != ConnectionState.Closed)
oCon.Close();
oCon.Dispose();
return false;
}
return true;
}
public DataSet select(string sql)
{
DataSet dsReturn = new DataSet();
try
{
oCon = new OracleConnection(_con);
oCon.Open();
OracleDataAdapter comm = new OracleDataAdapter(sql, oCon);
comm.Fill(dsReturn);
oCon.Close();
}
catch (Exception ex)
{
Console.WriteLine("错误的字符串:" + sql + " --->" + ex.ToString());
}
{
return dsReturn;
}
}
public Boolean deleteTable(string tablename)
{
oCon = new OracleConnection(con);
OracleCommand oCmd = new OracleCommand("drop table " + tablename, oCon);
try
{
oCon.Open();
// lets use a datareader to fill that table!
oCmd.ExecuteNonQuery();
oCon.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
if (oCon.State != ConnectionState.Closed)
oCon.Close();
oCon.Dispose();
return false;
}
if (oCon.State != ConnectionState.Closed)
oCon.Close();
oCon.Dispose();
return true;
}
}