Oracle:
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using Devart.Data;
using Devart.Data.Oracle;
using System.Web.Configuration;
using System.Configuration;
/// <summary>
/// 对oracle数据库的相关操作
/// 注意命名空间: Devart.Data.Oracle;
/// </summary>public class OracleHelper
{ /// <summary>
/// 数据库连接字符串
/// </summary>
public static readonly string ConnString = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
#region 执行sql之增,删,改操作
/// <summary>
/// 执行ExecuteNonQuery sql之增,删,改操作
/// </summary>
/// <param name="connection">数据库连接</param>
/// <param name="commandType">操作类型:
/// CommandType.StoredProcedure为存储过程;
/// CommandType.Text为普通sql语句;
/// </param>
/// <param name="commandText">sql语句或存储过程名称</param>
/// <returns></returns>
public static int ExecuteNonQuery(OracleConnection connection, CommandType commandType, string commandText)
{ return ExecuteNonQuery(connection, commandType, commandText, (OracleParameter[])null);
}
/// <summary>
/// 执行ExecuteNonQuery sql之增,删,改操作
/// </summary>
/// <param name="connection">数据库连接</param>
/// <param name="commandType">操作类型:
/// CommandType.StoredProcedure为存储过程;
/// CommandType.Text为普通sql语句;
/// </param>
/// <param name="commandText">sql语句或存储过程名称</param>
/// <param name="commandParameters">参数</param>
/// <returns></returns>
public static int ExecuteNonQuery(OracleConnection connection, CommandType commandType, string commandText, params OracleParameter[] commandParameters)
{ if (connection == null)
throw new ArgumentNullException("connection对象为null");
int result = -1;
OracleCommand cmd = new OracleCommand();
try
{
connection.Open();
cmd.Connection = connection;
cmd.CommandText = commandText;
cmd.CommandType = commandType;
if (commandParameters != null)//参数不为空
cmd.Parameters.AddRange(commandParameters);
//对于 UPDATE、INSERT 和 DELETE 语句,返回值为该命令所影响的行数。
//对于 CREATE TABLE 和 DROP TABLE 语句,返回值为 0。对于所有其他类型的语句,返回值为 -1。如果发生回滚,返回值同样为 -1
result = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
finally
{
cmd.Dispose();
connection.Close();
}
return result;
} #endregion #region 执行查询,返回DataSet
/// <summary>
/// 执行查询,返回DataSet
/// </summary>
/// <param name="connection">数据库连接</param>
/// <param name="commandType">操作类型:
/// CommandType.StoredProcedure为存储过程;
/// CommandType.Text为普通sql语句;
/// </param>
/// <param name="commandText">sql语句或存储过程名称</param>
/// <returns>结果集,DataSet</returns>
public static DataSet ExecuteDataset(OracleConnection connection, CommandType commandType, string commandText)
{
return ExecuteDataset(connection, commandType, commandText, (OracleParameter[])null);
}
/// <summary>
/// 执行查询,返回DataSet
/// </summary>
/// <param name="connection">数据库连接</param>
/// <param name="commandType">操作类型:
/// CommandType.StoredProcedure为存储过程;
/// CommandType.Text为普通sql语句;
/// </param>
/// <param name="commandText">sql语句或存储过程名称</param>
/// <param name="commandParameters">参数</param>
/// <returns>结果集,DataSet</returns>
public static DataSet ExecuteDataset(OracleConnection connection, CommandType commandType, string commandText, params OracleParameter[] commandParameters)
{
if (connection == null)
throw new ArgumentNullException("connection对象为null");
DataSet ds = new DataSet();
OracleCommand cmd = new OracleCommand();
try
{
connection.Open();
cmd.Connection = connection;
cmd.CommandText = commandText;
cmd.CommandType = commandType;
if (commandParameters != null)
cmd.Parameters.AddRange(commandParameters);
OracleDataAdapter da = new OracleDataAdapter(cmd);
da.Fill(ds, "table"); }
finally
{
cmd.Dispose();
connection.Close();
}
return ds;
} #endregion #region 执行查询,返回DataReader ( 注意:调用该方法后,一定要对SqlDataReader进行Close )
/// <summary>
/// 执行查询,返回DataReader ( 注意:调用该方法后,一定要对SqlDataReader进行Close )
/// </summary>
/// <param name="connection">数据库连接</param>
/// <param name="commandType">操作类型:
/// CommandType.StoredProcedure为存储过程;
/// CommandType.Text为普通sql语句;
/// </param>
/// <param name="commandText">sql语句或存储过程名称</param>
/// <returns></returns>
public static OracleDataReader ExecuteReader(OracleConnection connection, CommandType commandType, string commandText)
{
return ExecuteReader(connection, commandType, commandText, (OracleParameter[])null);
}
/// <summary>
/// 执行查询,返回DataReader ( 注意:调用该方法后,一定要对SqlDataReader进行Close )
/// </summary>
/// <param name="connection">数据库连接</param>
/// <param name="commandType">操作类型:
/// CommandType.StoredProcedure为存储过程;
/// CommandType.Text为普通sql语句;
/// </param>
/// <param name="commandText">sql语句或存储过程名称</param>
/// <param name="commandParameters">参数</param>
/// <returns></returns>
public static OracleDataReader ExecuteReader(OracleConnection connection, CommandType commandType, string commandText, params OracleParameter[] commandParameters)
{
if (connection == null)
throw new ArgumentNullException("connection对象为null"); connection.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = connection;
cmd.CommandText = commandText;
cmd.CommandType = commandType; if (commandParameters != null)
cmd.Parameters.AddRange(commandParameters); return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
#endregion #region 执行多条SQL语句,实现数据库事务
/// <summary>
/// 执行多条SQL语句,实现数据库事务
/// </summary>
/// <param name="connection">数据库连接</param>
/// <param name="sqls">多条SQL语句</param>
public static void ExecuteBatchBySqls(OracleConnection connection, List<string> sqls)
{
if (connection == null)
throw new ArgumentNullException("connection对象为null");
OracleTransaction tx = null;
OracleCommand cmd = new OracleCommand();
try
{
connection.Open();
cmd.Connection = connection;
//开始事务
tx = connection.BeginTransaction();
cmd.Transaction = tx;
foreach (string sql in sqls)
{
if (sql.Trim().Length > 1)
{
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
}
}
//提交事务
tx.Commit();
}
catch (Exception ex)
{
//事务回滚
tx.Rollback();
throw new Exception(ex.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
#endregion}
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using Devart.Data;
using Devart.Data.Oracle;
using System.Web.Configuration;
using System.Configuration;
/// <summary>
/// 对oracle数据库的相关操作
/// 注意命名空间: Devart.Data.Oracle;
/// </summary>public class OracleHelper
{ /// <summary>
/// 数据库连接字符串
/// </summary>
public static readonly string ConnString = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
#region 执行sql之增,删,改操作
/// <summary>
/// 执行ExecuteNonQuery sql之增,删,改操作
/// </summary>
/// <param name="connection">数据库连接</param>
/// <param name="commandType">操作类型:
/// CommandType.StoredProcedure为存储过程;
/// CommandType.Text为普通sql语句;
/// </param>
/// <param name="commandText">sql语句或存储过程名称</param>
/// <returns></returns>
public static int ExecuteNonQuery(OracleConnection connection, CommandType commandType, string commandText)
{ return ExecuteNonQuery(connection, commandType, commandText, (OracleParameter[])null);
}
/// <summary>
/// 执行ExecuteNonQuery sql之增,删,改操作
/// </summary>
/// <param name="connection">数据库连接</param>
/// <param name="commandType">操作类型:
/// CommandType.StoredProcedure为存储过程;
/// CommandType.Text为普通sql语句;
/// </param>
/// <param name="commandText">sql语句或存储过程名称</param>
/// <param name="commandParameters">参数</param>
/// <returns></returns>
public static int ExecuteNonQuery(OracleConnection connection, CommandType commandType, string commandText, params OracleParameter[] commandParameters)
{ if (connection == null)
throw new ArgumentNullException("connection对象为null");
int result = -1;
OracleCommand cmd = new OracleCommand();
try
{
connection.Open();
cmd.Connection = connection;
cmd.CommandText = commandText;
cmd.CommandType = commandType;
if (commandParameters != null)//参数不为空
cmd.Parameters.AddRange(commandParameters);
//对于 UPDATE、INSERT 和 DELETE 语句,返回值为该命令所影响的行数。
//对于 CREATE TABLE 和 DROP TABLE 语句,返回值为 0。对于所有其他类型的语句,返回值为 -1。如果发生回滚,返回值同样为 -1
result = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
finally
{
cmd.Dispose();
connection.Close();
}
return result;
} #endregion #region 执行查询,返回DataSet
/// <summary>
/// 执行查询,返回DataSet
/// </summary>
/// <param name="connection">数据库连接</param>
/// <param name="commandType">操作类型:
/// CommandType.StoredProcedure为存储过程;
/// CommandType.Text为普通sql语句;
/// </param>
/// <param name="commandText">sql语句或存储过程名称</param>
/// <returns>结果集,DataSet</returns>
public static DataSet ExecuteDataset(OracleConnection connection, CommandType commandType, string commandText)
{
return ExecuteDataset(connection, commandType, commandText, (OracleParameter[])null);
}
/// <summary>
/// 执行查询,返回DataSet
/// </summary>
/// <param name="connection">数据库连接</param>
/// <param name="commandType">操作类型:
/// CommandType.StoredProcedure为存储过程;
/// CommandType.Text为普通sql语句;
/// </param>
/// <param name="commandText">sql语句或存储过程名称</param>
/// <param name="commandParameters">参数</param>
/// <returns>结果集,DataSet</returns>
public static DataSet ExecuteDataset(OracleConnection connection, CommandType commandType, string commandText, params OracleParameter[] commandParameters)
{
if (connection == null)
throw new ArgumentNullException("connection对象为null");
DataSet ds = new DataSet();
OracleCommand cmd = new OracleCommand();
try
{
connection.Open();
cmd.Connection = connection;
cmd.CommandText = commandText;
cmd.CommandType = commandType;
if (commandParameters != null)
cmd.Parameters.AddRange(commandParameters);
OracleDataAdapter da = new OracleDataAdapter(cmd);
da.Fill(ds, "table"); }
finally
{
cmd.Dispose();
connection.Close();
}
return ds;
} #endregion #region 执行查询,返回DataReader ( 注意:调用该方法后,一定要对SqlDataReader进行Close )
/// <summary>
/// 执行查询,返回DataReader ( 注意:调用该方法后,一定要对SqlDataReader进行Close )
/// </summary>
/// <param name="connection">数据库连接</param>
/// <param name="commandType">操作类型:
/// CommandType.StoredProcedure为存储过程;
/// CommandType.Text为普通sql语句;
/// </param>
/// <param name="commandText">sql语句或存储过程名称</param>
/// <returns></returns>
public static OracleDataReader ExecuteReader(OracleConnection connection, CommandType commandType, string commandText)
{
return ExecuteReader(connection, commandType, commandText, (OracleParameter[])null);
}
/// <summary>
/// 执行查询,返回DataReader ( 注意:调用该方法后,一定要对SqlDataReader进行Close )
/// </summary>
/// <param name="connection">数据库连接</param>
/// <param name="commandType">操作类型:
/// CommandType.StoredProcedure为存储过程;
/// CommandType.Text为普通sql语句;
/// </param>
/// <param name="commandText">sql语句或存储过程名称</param>
/// <param name="commandParameters">参数</param>
/// <returns></returns>
public static OracleDataReader ExecuteReader(OracleConnection connection, CommandType commandType, string commandText, params OracleParameter[] commandParameters)
{
if (connection == null)
throw new ArgumentNullException("connection对象为null"); connection.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = connection;
cmd.CommandText = commandText;
cmd.CommandType = commandType; if (commandParameters != null)
cmd.Parameters.AddRange(commandParameters); return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
#endregion #region 执行多条SQL语句,实现数据库事务
/// <summary>
/// 执行多条SQL语句,实现数据库事务
/// </summary>
/// <param name="connection">数据库连接</param>
/// <param name="sqls">多条SQL语句</param>
public static void ExecuteBatchBySqls(OracleConnection connection, List<string> sqls)
{
if (connection == null)
throw new ArgumentNullException("connection对象为null");
OracleTransaction tx = null;
OracleCommand cmd = new OracleCommand();
try
{
connection.Open();
cmd.Connection = connection;
//开始事务
tx = connection.BeginTransaction();
cmd.Transaction = tx;
foreach (string sql in sqls)
{
if (sql.Trim().Length > 1)
{
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
}
}
//提交事务
tx.Commit();
}
catch (Exception ex)
{
//事务回滚
tx.Rollback();
throw new Exception(ex.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
#endregion}
难道设置timeout大点?这种方式很多人说,但一直超时应该如何知道?
总之就是一直连不上,使程序死掉,像进入死循环一样,刚刚遇到这个问题,现在在找方法,但也没找到,
如果能判断10秒钟内连不上就好了,像找开网页一样,有连接超时的页面。但并不是说像网络不稳定、账号密码错误等,这样的错误try ... catch 就能捕获了
超时,检查SQL
http://topic.csdn.net/u/20091019/10/4eb4f2d4-0cb6-49ce-abc5-a135ac6b733d.html