using System;
using System.Linq;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
namespace Access
{
public static class DbAccess
{
//const string CONN_STR = "Provider=OraOLEDB.Oracle.1;User ID={0};Password={1};Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST={2})(PORT={3})))(CONNECT_DATA=(SERVICE_NAME={4})))";
public static Dictionary<string, string> DataSource { get; set; }
private static string GetConnStr(string connStr)
{
if (DataSource != null)
{
string outstr;
if (string.IsNullOrEmpty(connStr))
connStr = DataSource.First().Value;
else if (DataSource.TryGetValue(connStr, out outstr))
connStr = outstr;
}
return connStr;
}
public static void ExecuteSingle(string connStr, string sql, params OleDbParameter[] cmdParms)
{
using (OleDbConnection connection = new OleDbConnection(GetConnStr(connStr)))
{
using (OleDbCommand command = connection.CreateCommand())
{
connection.Open();
if (!object.Equals(cmdParms, null))
command.Parameters.AddRange(cmdParms);
command.CommandText = sql;
command.ExecuteNonQuery();
}
}
}
public static int ExecuteMultiple(string connStr, string[] sqlArr, CommandType cmdType = CommandType.Text, params OleDbParameter[] cmdParms)
{
int rowCount = 0;
using (OleDbConnection connection = new OleDbConnection(GetConnStr(connStr)))
{
using (OleDbCommand command = connection.CreateCommand())
{
connection.Open();
using (OleDbTransaction transaction = connection.BeginTransaction())
{
try
{
command.Transaction = transaction;
command.CommandType = cmdType;
if (!object.Equals(cmdParms, null))
command.Parameters.AddRange(cmdParms);
foreach (var sql in sqlArr)
{
command.CommandText = sql;
rowCount += command.ExecuteNonQuery();
}
transaction.Commit();
return rowCount;
}
catch(Exception ex)
{
transaction.Rollback();
throw ex;
}
}
}
}
}
public static OleDbDataReader ExecuteReader(string connStr, string sql, params OleDbParameter[] cmdParms)
{
using (OleDbConnection conn = new OleDbConnection(connStr))
{
using (OleDbCommand cmd = conn.CreateCommand())
{
conn.Open();
cmd.CommandText = sql;
if (!object.Equals(cmdParms, null))
cmd.Parameters.AddRange(cmdParms);
return cmd.ExecuteReader();
}
}
}
public static object ExecuteScalar(string connStr, string sql, params OleDbParameter[] cmdParms)
{
using (OleDbConnection conn = new OleDbConnection(GetConnStr(connStr)))
{
using (OleDbCommand cmd = conn.CreateCommand())
{
conn.Open();
cmd.CommandText = sql;
if (!object.Equals(cmdParms, null))
cmd.Parameters.AddRange(cmdParms);
var val = cmd.ExecuteScalar();
return val;
}
}
}
public static DataSet GetDataset(string connStr, string sql, params OleDbParameter[] cmdParms)
{
using (OleDbConnection conn = new OleDbConnection(GetConnStr(connStr)))
{
using (OleDbCommand cmd = conn.CreateCommand())
{
using (OleDbDataAdapter adp = new OleDbDataAdapter(cmd))
{
conn.Open();
cmd.CommandText = sql;
if (!object.Equals(cmdParms, null))
cmd.Parameters.AddRange(cmdParms);
DataSet ds = new DataSet();
adp.Fill(ds);
return ds;
}
}
}
}
public static DataTable GetTable(string connStr, string sql, params OleDbParameter[] cmdParms)
{
using (DataSet ds = GetDataset(connStr, sql, cmdParms))
{
if (ds != null)
return ds.Tables[0];
else
return null;
}
}
public const string SQL_QUERY_ROWNUM = "SELECT * FROM ({0}) WHERE ROWNUM <= {1}";
public const string SQL_QUERY_AND_WHERE = "SELECT * FROM ({0}) WHERE {1}";
public const string SQL_QUERY_TABLE_STRUCT = "SELECT * FROM ({0}) WHERE 1<>1";
public const string SQL_QUERY_TOTAL_COUNT = "SELECT COUNT(*) FROM ({0})";
//const string Page_Query_SQL = "SELECT * FROM (SELECT src.*, ROWNUM AS rowno FROM ( # source # ) src WHERE ROWNUM <= 20) part WHERE part.rowno >= 10";
/*
DDL is short name of Data Definition Language, which deals with database schemas and descriptions, of how the data should reside in the database.
CREATE – to create database and its objects like (table, index, views, store procedure, function and triggers)
ALTER – alters the structure of the existing database
DROP – delete objects from the database
TRUNCATE – remove all records from a table, including all spaces allocated for the records are removed
COMMENT – add comments to the data dictionary
RENAME – rename an object
DML is short name of Data Manipulation Language which deals with data manipulation, and includes most common SQL statements such SELECT, INSERT, UPDATE, DELETE etc, and it is used to store, modify, retrieve, delete and update data in database.
SELECT – retrieve data from the a database
INSERT – insert data into a table
UPDATE – updates existing data within a table
DELETE – Delete all records from a database table
MERGE – UPSERT operation (insert or update)
CALL – call a PL/SQL or Java subprogram
EXPLAIN PLAN – interpretation of the data access path
LOCK TABLE – concurrency Control
DCL is short name of Data Control Language which includes commands such as GRANT, and mostly concerned with rights, permissions and other controls of the database system.
GRANT – allow users access privileges to database
REVOKE – withdraw users access privileges given by using the GRANT command
TCL is short name of Transaction Control Language which deals with transaction within a database.
COMMIT – commits a Transaction
ROLLBACK – rollback a transaction in case of any error occurs
SAVEPOINT – to rollback the transaction making points within groups
SET TRANSACTION – specify characteristics for the transaction
*/
private static readonly string[] DDL_SQL_HEADS = { "CREATE", "ALTER", "DROP", "TRUNCATE", "COMMENT", "RENAME" };
private static readonly string[] DML_SQL_HEADS = { "SELECT", "INSERT", "UPDATE", "DELETE", "MERGE", "CALL", "EXPLAIN PLAN", "LOCK TABLE" };
private static readonly string[] DCL_SQL_HEADS = { "GRANT", "REVOKE" };
private static readonly string[] TCL_SQL_HEADS = { "COMMIT", "ROLLBACK", "SAVEPOINT", "SET TRANSACTION" };
private static bool WhatSqlHead(string sql, params string[] heads)
{
if (string.IsNullOrEmpty(sql))
return false;
else
{
foreach (var head in heads)
{
if (sql.StartsWith(head, true, null))
return true;
}
return false;
}
}
public static bool IsSelectSql(string sql)
{
return WhatSqlHead(sql, "SELECT");
}
public static bool IsDDLSql(string sql)
{
return WhatSqlHead(sql, DDL_SQL_HEADS);
}
public static bool IsDMLSql(string sql)
{
return WhatSqlHead(sql, DML_SQL_HEADS);
}
public static bool IsDCLSql(string sql)
{
return WhatSqlHead(sql, DCL_SQL_HEADS);
}
public static bool IsTCLSql(string sql)
{
return WhatSqlHead(sql, TCL_SQL_HEADS);
}
}
}
