Details of the C Oracle database operation class instance
- 2020-06-23 01:47:27
- OfStack
C # described in this article so as to realize the Oracle database operations, can perform more than common Oracle database operations, contains the basic database connection, close the connection, the output record sets, execute Sql statement, with the paging function returns dataset, take exterior-interior field type and length, etc., as well as the thin table automatically inserted into the database and other advanced tasks. It should be noted that before executing SQL statement and returning DataReader, 1 must be opened with.read () before reading the data, and then use hashTable to perform insert,update,del operations on the database. Note that only the default database connection "connstr" can be used at this time.
The complete C# Oracle database class instance code is as follows:
using System;
using System.Data;
using System.Data.OracleClient;
using System.Collections;
using System.Reflection;
namespace MyOraComm
{
/// ConnDbForOracle The summary description of.
public class ConnForOracle
{
protected OracleConnection Connection;
private string connectionString;
public ConnForOracle()
{
string connStr;
connStr = System.Configuration.ConfigurationSettings.AppSettings["connStr"].ToString();
connectionString = connStr;
Connection = new OracleConnection(connectionString);
}
#region A constructor with parameters
/// A constructor with parameters
/// Database join string
public ConnForOracle(string ConnString)
{
string connStr;
connStr = System.Configuration.ConfigurationSettings.AppSettings[ConnString].ToString();
Connection = new OracleConnection(connStr);
}
#endregion
#region Open the database
/// Open the database
public void OpenConn()
{
if(this.Connection.State!=ConnectionState.Open)
this.Connection.Open();
}
#endregion
#region Close the database connection
/// Close the database connection
public void CloseConn()
{
if(Connection.State==ConnectionState.Open)
Connection.Close();
}
#endregion
#region perform SQL Statement that returns data to DataSet In the
/// perform SQL Statement that returns data to DataSet In the
/// sql statements
/// Custom returned DataSet The name of the table
/// return DataSet
public DataSet ReturnDataSet(string sql,string DataSetName)
{
DataSet dataSet=new DataSet();
OpenConn();
OracleDataAdapter OraDA=new OracleDataAdapter(sql,Connection);
OraDA.Fill(dataSet,DataSetName);
// CloseConn();
return dataSet;
}
#endregion
#region perform Sql statements , Return to pagination dataset
/// perform Sql statements , Return to pagination dataset
/// Sql statements
/// Number of records per page
/// < The current page /param>
/// return dataset The name of the table
/// return DataSet
public DataSet ReturnDataSet(string sql,int PageSize,int CurrPageIndex,string DataSetName)
{
DataSet dataSet=new DataSet();
OpenConn();
OracleDataAdapter OraDA=new OracleDataAdapter(sql,Connection);
OraDA.Fill(dataSet,PageSize * (CurrPageIndex - 1), PageSize,DataSetName);
// CloseConn();
return dataSet;
}
#endregion
#region perform SQL Statement, return DataReader, Before using 1 Must first .read() Open the , And then you can read the data
/// perform SQL Statement, return DataReader, Before using 1 Must first .read() Open the , And then you can read the data
/// sql statements
/// return 1 a OracleDataReader
public OracleDataReader ReturnDataReader(String sql)
{
OpenConn();
OracleCommand command = new OracleCommand(sql,Connection);
return command.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
}
#endregion
#region perform SQL Statement that returns the total number of records
/// perform SQL Statement that returns the total number of records
/// sql statements
/// Returns the total number of records
public int GetRecordCount(string sql)
{
int recordCount = 0;
OpenConn();
OracleCommand command = new OracleCommand(sql,Connection);
OracleDataReader dataReader = command.ExecuteReader();
while(dataReader.Read())
{
recordCount++;
}
dataReader.Close();
//CloseConn();
return recordCount;
}
#endregion
#region Take the current sequence , Conditions for seq.nextval or seq.currval
///
/// Take the current sequence
public decimal GetSeq(string seqstr)
{
decimal seqnum = 0;
string sql="select "+seqstr+" from dual";
OpenConn();
OracleCommand command = new OracleCommand(sql,Connection);
OracleDataReader dataReader = command.ExecuteReader();
if(dataReader.Read())
{
seqnum=decimal.Parse(dataReader[0].ToString());
}
dataReader.Close();
// CloseConn();
return seqnum;
}
#endregion
#region perform SQL statements , Returns the number of rows affected
/// perform SQL statements , Returns the number of rows affected
public int ExecuteSQL(string sql)
{
int Cmd=0;
OpenConn();
OracleCommand command = new OracleCommand(sql,Connection);
try
{
Cmd =command.ExecuteNonQuery();
}
catch
{
}
finally
{
//CloseConn();
}
return Cmd;
}
#endregion
// = = use hashTable Perform against the database insert,update,del operation , Note that you can only use the default database connection at this point "connstr"
#region Automatically insert the database based on the table name and the hashtable Usage: Insert("test",ht)
public int Insert(string TableName,Hashtable ht)
{
OracleParameter[] Parms=new OracleParameter[ht.Count];
IDictionaryEnumerator et = ht.GetEnumerator();
DataTable dt=GetTabType(TableName);
System.Data.OracleClient.OracleType otype;
int size=0;
int i=0;
while ( et.MoveNext() ) // Hash table loop
{
GetoType(et.Key.ToString().ToUpper(),dt,out otype,out size);
System.Data.OracleClient.OracleParameter op=MakeParam(":"+et.Key.ToString(),otype,size,et.Value.ToString());
Parms[i]=op; // add SqlParameter object
i=i+1;
}
string str_Sql=GetInsertSqlbyHt(TableName,ht); // For insert sql statements
int val=ExecuteNonQuery(str_Sql,Parms);
return val;
}
#endregion
#region Update the database according to the relevant conditions Usage: Update("test","Id=:Id",ht);
public int Update(string TableName,string ht_Where, Hashtable ht)
{
OracleParameter[] Parms=new OracleParameter[ht.Count];
IDictionaryEnumerator et = ht.GetEnumerator();
DataTable dt=GetTabType(TableName);
System.Data.OracleClient.OracleType otype;
int size=0;
int i=0;
// Hash table loop
while ( et.MoveNext() )
{
GetoType(et.Key.ToString().ToUpper(),dt,out otype,out size);
System.Data.OracleClient.OracleParameter op=MakeParam(":"+et.Key.ToString(),otype,size,et.Value.ToString());
Parms[i]=op; // add SqlParameter object
i=i+1;
}
string str_Sql=GetUpdateSqlbyHt(TableName,ht_Where,ht); // For insert sql statements
int val=ExecuteNonQuery(str_Sql,Parms);
return val;
}
#endregion
#region del operation , Notice the number of conditions and hash The number of arguments in should be 1 to Usage: Del("test","Id=:Id",ht)
public int Del(string TableName,string ht_Where,Hashtable ht)
{
OracleParameter[] Parms=new OracleParameter[ht.Count];
IDictionaryEnumerator et = ht.GetEnumerator();
DataTable dt=GetTabType(TableName);
System.Data.OracleClient.OracleType otype;
int i=0;
int size=0;
// Hash table loop
while ( et.MoveNext() )
{
GetoType(et.Key.ToString().ToUpper(),dt,out otype,out size);
System.Data.OracleClient.OracleParameter op=MakeParam(":"+et.Key.ToString(),et.Value.ToString());
Parms[i]=op; // add SqlParameter object
i=i+1;
}
string str_Sql=GetDelSqlbyHt(TableName,ht_Where,ht); // Get deleted sql statements
int val=ExecuteNonQuery(str_Sql,Parms);
return val;
}
#endregion
// = = = = = = = up 3 Operation = = = = = = = = = = = =
#region According to the hashtable and table name automatically generate corresponding insert statements ( Parameter type )
/// According to the hashtable and table name automatically generate corresponding insert statements
/// The name of the table to insert
/// The thin sheet
/// return sql statements
public static string GetInsertSqlbyHt(string TableName,Hashtable ht)
{
string str_Sql="";
int i=0;
int ht_Count=ht.Count; // Number of hash tables
IDictionaryEnumerator myEnumerator = ht.GetEnumerator();
string before="";
string behide="";
while ( myEnumerator.MoveNext() )
{
if (i==0)
{
before="("+myEnumerator.Key;
}
else if (i+1==ht_Count)
{
before=before+","+myEnumerator.Key+")";
}
else
{
before=before+","+myEnumerator.Key;
}
i=i+1;
}
behide=" Values"+before.Replace(",",",:").Replace("(","(:");
str_Sql="Insert into "+TableName+before+behide;
return str_Sql;
}
#endregion
#region Based on the table name, where Condition that the hash table automatically generates an update statement ( Parameter type )
public static string GetUpdateSqlbyHt(string Table,string ht_Where,Hashtable ht)
{
string str_Sql="";
int i=0;
int ht_Count=ht.Count; // Number of hash tables
IDictionaryEnumerator myEnumerator = ht.GetEnumerator();
while ( myEnumerator.MoveNext() )
{
if (i==0)
{
if (ht_Where.ToString().ToLower().IndexOf((myEnumerator.Key+"=:"+myEnumerator.Key).ToLower())==-1)
{
str_Sql=myEnumerator.Key+"=:"+myEnumerator.Key;
}
}
else
{
if (ht_Where.ToString().ToLower().IndexOf((":"+myEnumerator.Key+" ").ToLower())==-1)
{
str_Sql=str_Sql+","+myEnumerator.Key+"=:"+myEnumerator.Key;
}
}
i=i+1;
}
if (ht_Where==null || ht_Where.Replace(" ","")=="") // There are no conditions when updating
{
str_Sql="update "+Table+" set "+str_Sql;
}
else
{
str_Sql="update "+Table+" set "+str_Sql+" where "+ht_Where;
}
str_Sql=str_Sql.Replace("set ,","set ").Replace("update ,","update ");
return str_Sql;
}
#endregion
#region Based on the table name, where Condition, hashtable automatically generated del statements ( Parameter type )
public static string GetDelSqlbyHt(string Table,string ht_Where,Hashtable ht)
{
string str_Sql="";
int i=0;
int ht_Count=ht.Count; // Number of hash tables
IDictionaryEnumerator myEnumerator = ht.GetEnumerator();
while ( myEnumerator.MoveNext() )
{
if (i==0)
{
if (ht_Where.ToString().ToLower().IndexOf((myEnumerator.Key+"=:"+myEnumerator.Key).ToLower())==-1)
{
str_Sql=myEnumerator.Key+"=:"+myEnumerator.Key;
}
}
else
{
if (ht_Where.ToString().ToLower().IndexOf((":"+myEnumerator.Key+" ").ToLower())==-1)
{
str_Sql=str_Sql+","+myEnumerator.Key+"=:"+myEnumerator.Key;
}
}
i=i+1;
}
if (ht_Where==null || ht_Where.Replace(" ","")=="") // There are no conditions when updating
{
str_Sql="Delete "+Table;
}
else
{
str_Sql="Delete "+Table+" where "+ht_Where;
}
return str_Sql;
}
#endregion
#region generate oracle parameter
///
/// generate oracle parameter
/// The field name
/// The data type
/// Data size
/// value
public static OracleParameter MakeParam(string ParamName,System.Data.OracleClient.OracleType otype,int size,Object Value)
{
OracleParameter para=new OracleParameter(ParamName,Value);
para.OracleType=otype;
para.Size=size;
return para;
}
#endregion
#region generate oracle parameter
public static OracleParameter MakeParam(string ParamName,string Value)
{
return new OracleParameter(ParamName, Value);
}
#endregion
#region Assembly is based on the type and length of the table structure field oracle sql Statement parameters
public static void GetoType(string key,DataTable dt,out System.Data.OracleClient.OracleType otype,out int size)
{
DataView dv=dt.DefaultView;
dv.RowFilter="column_name='"+key+"'";
string fType=dv[0]["data_type"].ToString().ToUpper();
switch (fType)
{
case "DATE":
otype= OracleType.DateTime;
size=int.Parse(dv[0]["data_length"].ToString());
break;
case "CHAR":
otype= OracleType.Char;
size=int.Parse(dv[0]["data_length"].ToString());
break;
case "LONG":
otype= OracleType.Double;
size=int.Parse(dv[0]["data_length"].ToString());
break;
case "NVARCHAR2":
otype= OracleType.NVarChar;
size=int.Parse(dv[0]["data_length"].ToString());
break;
case "VARCHAR2":
otype= OracleType.NVarChar;
size=int.Parse(dv[0]["data_length"].ToString());
break;
default:
otype= OracleType.NVarChar;
size=100;
break;
}
}
#endregion
#region dynamic Takes the type and length of a table field , There is no dynamic use here connstr, It's the default! by/ Wen less
public System.Data.DataTable GetTabType(string tabnale)
{
string sql="select column_name,data_type,data_length from all_tab_columns where table_name='"+tabnale.ToUpper()+"'";
OpenConn();
return (ReturnDataSet(sql,"dv")).Tables[0];
}
#endregion
#region perform sql statements
public int ExecuteNonQuery(string cmdText, params OracleParameter[] cmdParms)
{
OracleCommand cmd = new OracleCommand();
OpenConn();
cmd.Connection=Connection;
cmd.CommandText = cmdText;
if (cmdParms != null)
{
foreach (OracleParameter parm in cmdParms)
cmd.Parameters.Add(parm);
}
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
//conn.CloseConn();
return val;
}
#endregion
}
}
When in use, the above code can be saved as es21EN_dbconn.cs file and then called.