(2)OLEDB数据库操作
1、首先要引入 System.Data.OracleClient.dll
2、引入命名空间 using System.Data.OleDb;
OleDb类 https://msdn.microsoft.com/zh-cn/library/system.data.oledb(v=vs.110).aspx
一、连接数据库
连接字符串
string str = "Provider=OraOLEDB.Oracle.1;User ID=xxx;Password=xxx;Data Source=(DESCRIPTION = (ADDRESS_LIST= (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ORCL)))"; OleDbConnection conn = new OleDbConnection(str);
开关数据库
conn.Open();//打开数据库连接 conn.Close();//关闭数据库连接
打开的连接,再打开会报错。关闭的连接,再关闭不会报错。
测试连接数据库
无需配置tnsnames.ora
string str = "Provider=OraOLEDB.Oracle.1;User ID=xxx;Password=xxx;Data Source=(DESCRIPTION = (ADDRESS_LIST= (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ORCL)))"; OleDbConnection conn = new OleDbConnection(str); conn.Open(); if (conn.State == ConnectionState.Open) { MessageBox.Show("连接成功"); } conn.Close();
ConnectionState枚举类,用来判断数据库当前状态,结果为true或false
bool status = ConnectionState.Open;//数据库是否已打开 bool status = ConnectionState.Closed;//数据库是否已关闭
if (conn.State == ConnectionState.Open) { MessageBox.Show("连接成功"); }
预定义连接字符串
1.创建一个配置文件,app.config
<?xml version="1.0" encoding="utf-8" ?> <configuration> <appSettings> <add key="con_MES" value="Provider=OraOLEDB.Oracle.1;User ID=MES;Password=zidonghua;Data Source=(DESCRIPTION = (ADDRESS_LIST= (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ORCL)))"></add> </appSettings> </configuration>
2.添加 System.Configuration 程序集
3.代码增加using System.Configuration;
4.获取连接串
string str = ConfigurationManager.AppSettings["con_MES"];
为了确保使用数据操作后不占用资源应该立即关闭连接,有两种高效使用连接的方式
1. try
string str = ConfigurationManager.AppSettings["con_MES"]; try { //外部以定义 OleDbConnection conn; conn = new OleDbConnection(str); conn.Open(); if (conn.State == ConnectionState.Open) { MessageBox.Show("连接成功"); } } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { conn.Close(); }
2.using
string str = ConfigurationManager.AppSettings["con_MES"]; using (OleDbConnection conn = new OleDbConnection(str)) { conn.Open(); if (conn.State == ConnectionState.Open) { MessageBox.Show("连接成功"); } }
最是两者结合在一起
string str = ConfigurationManager.AppSettings["con_MES"]; try { using (OleDbConnection conn = new OleDbConnection(str)) { conn.Open(); if (conn.State == ConnectionState.Open) { MessageBox.Show("连接成功"); } } } catch (Exception ex) { MessageBox.Show(ex.Message); }
二、事物
ADO.NET分类里找有单独几篇博客
三、赋予命令
OleDbCommand类
表示要对数据源执行的 SQL 语句或存储过程,把SQL语句赋给Command对象
构造方法:
有四种
1、不带参
OleDbCommand()
OleDbCommand dbsql =new OleDbCommand();
初始化时如果没带参数,可以通过属性单独添加
(1)CommandText属性:获取或设置 SQL 语句或存储的过程以对数据库执行
command.CommandText = "update pingtable set targetip='192.168.55.55' where id=1102";//添加SQL
(2)Connection属性:获取或设置 OracleCommand 的此实例使用的 OracleConnection
command.Connection = conn;//添加连接 OleDbConnection conn
(3)Transaction属性:获取或设置要在其中执行 OracleTransaction 的 OracleCommand
command.Transaction = OT; //添加事务 OleDbTransaction OT
(4)CommandType属性:获取或设置一个值,该值指示如何 CommandText 属性将被解释
4.1 Text 表示Command对象用于执行SQL
4.2 StoredProcedure表示Command对象用于执行存储过程
4.3 TableDirect表示Command
CommandType属性的默认值为Text
command.CommandType = CommandType.Text;
2、传一个参数。(一个SQL语句 )
OleDbCommand(sql)
string sql="select * from tab"; OleDbCommand command=new OleDbCommand(sql);
3、传两个参数(一个sql语句和一个OleDbConnection对象)
OleDbCommand(sql,conn)
string str = "Provider=OraOLEDB.Oracle.1;User ID=xxx;Password=xxx;Data Source=(DESCRIPTION = (ADDRESS_LIST= (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ORCL)))"; OleDbConnection conn = new OleDbConnection(str); conn.Open(); string sql = "select * from tab"; OleDbCommand command = new OleDbCommand(sql, conn); //MessageBox.Show("成功");
4、传三个参数(一个sql语句和一个OleDbConnection对象,一个事务)
string str = "Provider=OraOLEDB.Oracle.1;User ID=xxx;Password=xxx;Data Source=(DESCRIPTION = (ADDRESS_LIST= (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ORCL)))"; OleDbConnection conn = new OleDbConnection(str); conn.Open(); string sql = "select * from T_PI_ORDER"; OleDbTransaction transaction = conn.BeginTransaction(); OleDbCommand command = new OleDbCommand(sql, conn, transaction); transaction.Commit(); MessageBox.Show("成功");
如果command构造函数时没带sql的参数,就要用CommandText属性来赋值。
OleDbCommand command = new OleDbCommand(); command.CommandText = "select * from tab";
如果command构造函数时没带OleDbConnection时,就给command.Connection赋值一个OleDbConnection对象。
string str = "Provider=OraOLEDB.Oracle.1;User ID=xxx;Password=xxx;Data Source=(DESCRIPTION = (ADDRESS_LIST= (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ORCL)))"; OleDbConnection conn = new OleDbConnection(str); conn.Open(); OleDbCommand command = new OleDbCommand(); command.Connection = conn; conn.Close();
等待命令执行所需的时间(以秒为单位)。 默认值为 30 秒
OleDbCommand command = new OleDbCommand(); command.CommandTimeout = 1;
四、执行命令(增删改查)
1.ExecuteNonQuery()
执行 SQL 语句针并返回受影响的行数,一般用于update、insert、delete 语句的执行
string str = "Provider=OraOLEDB.Oracle.1;User ID=xxx;Password=xxx;Data Source=(DESCRIPTION = (ADDRESS_LIST= (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ORCL)))"; string sql = "update tab set score='90' where name='tom'"; using (OleDbConnection conn = new OleDbConnection(str)) { connection.Open(); OleDbCommand command = new OleDbCommand(sql, conn); command.ExecuteNonQuery(); }
2.ExecuteReader()
获得结果集,返回一个DataReader对象,一般用于select
string str = "Provider=OraOLEDB.Oracle.1;User ID=xxx;Password=xxx;Data Source=(DESCRIPTION = (ADDRESS_LIST= (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ORCL)))"; string sql = "select * from tab"; using (OleDbConnection conn = new OleDbConnection(str)) { connection.Open(); OleDbCommand command = new OleDbCommand(sql, conn); OleDbDataReader reader = command.ExecuteReader();//把执行结果传给OleDbDataReader ....... reader.Close(); }
3.ExecuteScalar()
返回的数据是查询结果的第一行第一列,并且返回的结果是一个object类型,之后可以把该对象强制转换为合适的类型
string str = "Provider=OraOLEDB.Oracle.1;User ID=xxx;Password=xxx;Data Source=(DESCRIPTION = (ADDRESS_LIST= (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ORCL)))"; string sql = "select count(*) from table"; using (OleDbConnection conn = new OleDbConnection(str)) { connection.Open(); OleDbCommand command = new OleDbCommand(sql, conn); object o = command.ExecuteScalar(); }
五、调用存储过程
...
六、快速数据访问-数据读取器
OleDbDataReader类
虽然数据读取器是从数据库中选择某些数据的最简单快捷的方法。
但这也是功能最弱的方法,不能直接实例化数据阅读器,需要调用ExecuteReader()方法后从相应数据库的命令对象(如OleDbCommand)中返回的实例。
Read()方法 指针移动到下一行数据之前。
while(dr.Read())
{
Console.WriteLine(dr[0]);//循环输出当前行的第一列
}
string str = "Provider=OraOLEDB.Oracle.1;User ID=xxx;Password=xxx;Data Source=(DESCRIPTION = (ADDRESS_LIST= (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ORCL)))"; string sql = "select * from tab"; using (OleDbConnection conn = new OleDbConnection(str)) { connection.Open(); OleDbCommand command = new OleDbCommand(sql, conn); OleDbDataReader reader = command.ExecuteReader();//把执行结果传给OleDbDataReader while(reader.Read()) { Console.WriteLine("'{0} from {1}',reader.GetString(0),reader.GetString(1)") } reader.Close(); }
OleDbDataReader是一个只向前的连接读取器,即只能沿着一个方向遍历记录,而使用的数据库一直打开,直到关闭该数据读取器为止。
OleDbDataReader类不能直接实例化,需要通过调用OleDbCommand类的ExecuteReader()方法来。
OleDbDataReader索引器取数数据
获取指定列的值,返回值是object类型
object id = dr[0];//数字索引器
object id = dr["ID"];//字符索引器
OleDbDataReader方法取数数据
reader.GetString(0)
while(reader.Read()) { Console.WriteLine("'{0} from {1}',reader.GetString(0),reader.GetString(1)") } reader.Close();
*用完read()方法记得Close()掉
七、填充DataSet类
从数据源读取数据并插入到DataSet中有两种方式:使用数据适配器、把XML读入DataSet
1.用数据适配器读入DataSet
这里要用到OleDbDataAdapter类,表示用于填充 DataSet 和更新数据源的一组数据命令和一个数据库连接
带sql语句和连接对象的初始化
OleDbDataAdapter(String, OleDbConnection)
DataSet ds; string str = ConfigurationManager.AppSettings["con_MES"]; try { using (OleDbConnection conn = new OleDbConnection(str)) { conn.Open(); if (conn.State == ConnectionState.Open) { string select = "select * from PINGTABLE where rownum<50"; OleDbDataAdapter da = new OleDbDataAdapter(select, conn);//连接对象 ds = new DataSet(); da.Fill(ds, "Costomers"); } } } catch (Exception ex) { MessageBox.Show(ex.Message); }
带sql语句和连接字符串的初始化
OleDbDataAdapter(String, String)
DataSet ds; string str = ConfigurationManager.AppSettings["con_MES"]; try { using (OleDbConnection conn = new OleDbConnection(str)) { conn.Open(); if (conn.State == ConnectionState.Open) { string select = "select * from PINGTABLE where rownum<50"; OleDbDataAdapter da = new OleDbDataAdapter(select, str);//连接字符串 ds = new DataSet(); da.Fill(ds, "Costomers"); } } } catch (Exception ex) { MessageBox.Show(ex.Message); }
OleDbParameter 类