Now, I will show a example of connection in C# + SQLServer, C# + MySQL and C# + Oracle.
All the three are much alike (it will just change de library that you'll work with).
Here's a Class wich a created for connect with the three Data Bases:
Download Class
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using System.Data.OracleClient;
using System.Data.SqlClient;
using System.Data;
namespace Connection
{
public class Conexao
{
#region globals
private bool _State; // shows connections last state
private string _ErrorMessage; // shows last error message
private int _ErrorNumber; // shows last connections last error
private bool _CompleteCommand; // shows last command completed with success
// constantes das strings de conexão do SqlServer, Oracle e MySQL
public const string SQL_STR_CON = "yourConnectionString";
private const string ORA_STR_CON = "yourConnectionString";
private const string MYSQL_STR_CON = "yourConnectionString";
#endregion
#region properties
public int ErrorNumber
{
get { return _ErrorNumber; }
}
public string ErrorDescription
{
get { return _ErrorMessage; }
}
public bool ConnectionState
{
get { return _State; }
}
public bool CompleteCommand
{
get { return _CompleteCommand; }
}
#endregion
///
/// Executes a sql command with no return. Uses Transaction.
///
/// Query a ser executada. (INSERT, UPDATE, DELETE, TRUNCATE, DROP)
/// SQLSERVER, ORACLE, MYSQL
public void ExecutaNonQuery(string myQuery, string banco)
{
// Definir valores padrões das variaveis
_ErrorMessage = "";
_State = false;
// Se não for informado comando T-SQL retorno error
if (myQuery.Length > 0) // Se realmente foi passado um comando a ser executado
{
switch (banco)
{
case "SQLSERVER":
SqlConnection sqlCon = new SqlConnection(SQL_STR_CON);
SqlTransaction sqlTransaction;
sqlCon.Open();
sqlTransaction = sqlCon.BeginTransaction();
SqlCommand sqlCommand = new SqlCommand(myQuery, sqlCon, sqlTransaction);
try
{
sqlCommand.ExecuteNonQuery();
sqlTransaction.Commit();
_State = true;
_CompleteCommand = true;
}
catch (SqlException sqlEx)
{
sqlTransaction.Rollback();
_ErrorMessage = sqlEx.Message.ToString();
_State = false;
_ErrorNumber = sqlEx.GetHashCode();
_CompleteCommand = false;
}
sqlCon.Close();
sqlCommand.Dispose();
break;
case "ORACLE":
OracleConnection oraCon = new OracleConnection(ORA_STR_CON);
OracleTransaction oraTransaction;
oraCon.Open();
oraTransaction = oraCon.BeginTransaction();
OracleCommand oraCommand = new OracleCommand(myQuery, oraCon, oraTransaction);
try
{
oraCommand.ExecuteNonQuery();
oraTransaction.Commit();
_State = true;
_CompleteCommand = true;
}
catch (OracleException oraEx)
{
oraTransaction.Rollback();
_ErrorMessage = oraEx.Message.ToString();
_State = false;
_ErrorNumber = oraEx.GetHashCode();
_CompleteCommand = false;
}
oraCon.Close();
oraCommand.Dispose();
break;
case "MYSQL":
MySqlConnection mySqlCon = new MySqlConnection(MYSQL_STR_CON);
MySqlTransaction mySqlTransaction;
mySqlCon.Open();
mySqlTransaction = mySqlCon.BeginTransaction();
MySqlCommand mySqlCommand = new MySqlCommand(myQuery, mySqlCon, mySqlTransaction);
try
{
mySqlCommand.ExecuteNonQuery();
mySqlTransaction.Commit();
_State = true;
_CompleteCommand = true;
}
catch (MySqlException myEx)
{
mySqlTransaction.Rollback();
_ErrorMessage = myEx.Message.ToString();
_State = false;
_ErrorNumber = myEx.GetHashCode();
_CompleteCommand = false;
}
mySqlCon.Close();
mySqlCommand.Dispose();
break;
default:
_ErrorMessage = "BANCO INFORMADO INVÁLIDO";
break;
}
}
}
///
/// Executes a sql query.
///
/// Query.
/// SQLSERVER, ORACLE, MYSQL
/// DataTable
public DataTable ExecutaReader(string myQuery, string banco)
{
DataTable dtResult = new DataTable();
_ErrorMessage = "";
_State = false;
if (myQuery.Length > 0)
{
switch (banco)
{
case "SQLSERVER":
SqlConnection sqlCon = new SqlConnection(SQL_STR_CON);
sqlCon.Open();
SqlCommand sqlCommand = new SqlCommand(myQuery, sqlCon);
try
{
SqlServerReader = sqlCommand.ExecuteReader();
dtResult.Load(SqlServerReader);
_State = true;
_CompleteCommand = true;
}
catch (SqlException sqlEx)
{
_ErrorMessage = sqlEx.Message.ToString();
_State = false;
_ErrorNumber = sqlEx.GetHashCode();
_CompleteCommand = false;
}
sqlCon.Close();
sqlCommand.Dispose();
break;
case "ORACLE":
OracleConnection oraCon = new OracleConnection(ORA_STR_CON);
oraCon.Open();
OracleCommand oraCommand = new OracleCommand(myQuery, oraCon);
try
{
OraReader = oraCommand.ExecuteReader();
dtResult.Load(OraReader);
_State = true;
_CompleteCommand = true;
}
catch (OracleException oraEx)
{
_ErrorMessage = oraEx.Message.ToString();
_State = false;
_ErrorNumber = oraEx.GetHashCode();
_CompleteCommand = false;
}
oraCon.Close();
oraCommand.Dispose();
break;
case "MYSQL":
MySqlConnection mySqlCon = new MySqlConnection(MYSQL_STR_CON);
mySqlCon.Open();
MySqlCommand mySqlCommand = new MySqlCommand(myQuery, mySqlCon);
try
{
MysqlReader = mySqlCommand.ExecuteReader();
dtResult.Load(MysqlReader);
_State = true;
_CompleteCommand = true;
}
catch (MySqlException myEx)
{
_ErrorMessage = myEx.Message.ToString();
_State = false;
_ErrorNumber = myEx.GetHashCode();
_CompleteCommand = false;
}
mySqlCon.Close();
mySqlCommand.Dispose();
break;
default:
_ErrorMessage = "BANCO INFORMADO INVÁLIDO";
break;
}
}
return dtResult;
}
///
/// Sql query with a single return.
///
/// Query
/// SQLSERVER, ORACLE, MYSQL
/// Object
public Object ExecutaScalar(string myQuery, string banco)
{
Object objResult = new Object();
_ErrorMessage = "";
_State = false;
if (myQuery.Length > 0)
{
switch (banco)
{
case "SQLSERVER":
SqlConnection sqlCon = new SqlConnection(SQL_STR_CON);
sqlCon.Open();
SqlCommand sqlCommand = new SqlCommand(myQuery, sqlCon);
try
{
objResult = sqlCommand.ExecuteScalar();
_State = true;
_CompleteCommand = true;
}
catch (SqlException sqlEx)
{
_ErrorMessage = sqlEx.Message.ToString();
_State = false;
_ErrorNumber = sqlEx.GetHashCode();
_CompleteCommand = false;
}
sqlCon.Close();
sqlCommand.Dispose();
break;
case "ORACLE":
OracleConnection oraCon = new OracleConnection(ORA_STR_CON);
oraCon.Open();
OracleCommand oraCommand = new OracleCommand(myQuery, oraCon);
try
{
objResult = oraCommand.ExecuteScalar();
_State = true;
_CompleteCommand = true;
}
catch (OracleException oraEx)
{
_ErrorMessage = oraEx.Message.ToString();
_State = false;
_ErrorNumber = oraEx.GetHashCode();
_CompleteCommand = false;
}
oraCon.Close();
oraCommand.Dispose();
break;
case "MYSQL":
MySqlConnection mySqlCon = new MySqlConnection(MYSQL_STR_CON);
mySqlCon.Open();
MySqlCommand mySqlCommand = new MySqlCommand(myQuery, mySqlCon);
try
{
objResult = mySqlCommand.ExecuteScalar();
_State = true;
_CompleteCommand = true;
}
catch (MySqlException myEx)
{
_ErrorMessage = myEx.Message.ToString();
_State = false;
_ErrorNumber = myEx.GetHashCode();
_CompleteCommand = false;
}
mySqlCon.Close();
mySqlCommand.Dispose();
break;
default:
_ErrorMessage = "BANCO INFORMADO INVÁLIDO";
break;
}
}
return objResult;
}
}
}
Nenhum comentário:
Postar um comentário