c#oracle 访问

来源：互联网发布：c语言温度转换编辑：程序博客网时间：2024/06/11 18:21

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;
}

}

0 0