EDN Admin
Well-known member
Hello
I have below DAL codes. It give me timeout and pooling error sometime. I have to close and open computer again when it give me that error.
My DAL codes have a problem ? Can you fix it ?
Thank You.
<pre class="prettyprint winform.cs
//I use my DAL at winform like below. sorgu means SQL query. and dic means dictionary like paremeters. You should fix my DAL codes due to MsDataAccess.Connect() and MsDataAccess.ExecuteQuery codes is important. I used them at huge project. There are a lot of some codes. If you change it, i should change a lot of codes at winforms.cs
//1-without Transaction
string sorgu1 = @"SELECT * FROM table";
Dictionary<string, object> dic = new Dictionary<string, object>();
dic.Add("x_ceksenetid", Convert.ToInt32(idL.Text));
MsDataAccess.Connect(genel.kullanici.ConnectionString);
DataTable dt = MsDataAccess.ExecuteQuery(sorgu1, dic);
//2-With Transaction
MsDataAccess.Connect(genel.kullanici.ConnectionString);
MsDataAccess.BeginTransaction();
bool success = true;
success = success && MsDataAccess.ExecuteNonQueryMY(sorguckupdatecarikarti, dicckupdatecarikarti);
success = success && MsDataAccess.ExecuteNonQueryMY(sorgu, dic);
success = success && MsDataAccess.ExecuteNonQueryMY(sorguceksenetupdate, dicceksenetupdate);
if (success)
{
MsDataAccess.CommitTransaction();
}
else
{
MsDataAccess.RollBackTransaction();
MessageBox.Show("İşlem Geri Alınırken bir hata oluştu. Lütfen işlemi tekrarlayınız.", "Hata", MessageBoxButtons.OK, MessageBoxIcon.None);
return;
}
///////////////////////////////
MsDataAccess.cs
//////////////////////////////
#define MSSQL
//#define NPGQL
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Text.RegularExpressions;
using System.Windows.Forms;
//using FastReport;
#if MSSQL
using System.Data.SqlClient;
using PROJECTNAMEEEEEEEEEEEEEEE;
#endif
#if NPGQL
using Npgsql;
using NpgsqlTypes;
#endif
#if MSSQL
public static class MsDataAccess
{
#region Variables
private static string iConnectionString;
public static string ConnectionString
{
get { return iConnectionString; }
}
public static bool SetConnectionString(string value)
{
if (TryConnect(value))
{
iConnectionString = value;
return true;
}
return false;
}
private static SqlTransaction iTransaction;
private static SqlConnection iConnection;
#endregion
#region Connect
public static bool Connect(string server, string database, string username, string password)
{
string connStr = string.Format("Server={0};Database={1};User Id={2};Password={3}", server, database, username, password);
try
{
return TryConnect(connStr);
}
catch
{
SqlConnection.ClearAllPools();
return TryConnect(connStr);
}
}
public static bool Connect(string connStr)
{
try
{
return TryConnect(connStr);
}
catch
{
SqlConnection.ClearAllPools();
return TryConnect(connStr);
}
}
private static bool TryConnect(string connStr)
{
//if (iConnection != null)
//{
// if (iConnection.State == ConnectionState.Open)
// {
// iConnection.Close();
// }
//}
connStr += ";Connection Timeout=500;";
//if (iTransaction != null)
//{
// RollBackTransaction();
// iTransaction = null;
//}
if (iTransaction != null) RollBackTransaction();
if (iConnection != null) iConnection = null;
// SqlConnection.ClearAllPools();
iConnection = new SqlConnection(connStr);
//if (iConnection == null)
// iConnection = new SqlConnection(connStr);
//{
// try
// {
// if (iConnection.State == ConnectionState.Closed)
// {
// iConnection.Open(); iConnectionString = connStr;
// }
// }
// catch (Exception ex) { ErrorLogger.Log(iConnection, ex); return false; }
// return true;
//}
// iConnection = new SqlConnection(connStr);
{
try { iConnection.Open(); iConnectionString = connStr; }
catch (Exception ex) { ErrorLogger.Log(iConnection, ex); return false; }
return true;
}
}
#endregion
#region iTransaction
public static bool BeginTransaction()
{
if (iConnection.State != ConnectionState.Open) iConnection.Open();
if (iTransaction != null) iTransaction.Rollback();
try { iTransaction = iConnection.BeginTransaction(); }
catch (Exception ex) { ErrorLogger.Log(iConnection, ex); return false; }
return true;
}
public static bool CommitTransaction()
{
if (iConnection.State != ConnectionState.Open) iConnection.Open();
try { iTransaction.Commit(); iTransaction = null; }
catch (Exception ex)
{
if (iTransaction != null)
{
iTransaction.Rollback();
ErrorLogger.Log(iTransaction, ex);
}
iTransaction = null;
return false;
}
return true;
}
public static bool RollBackTransaction()
{
if (iConnection.State != ConnectionState.Open) iConnection.Open();
try { iTransaction.Rollback(); iTransaction = null; }
catch (Exception ex)
{
ErrorLogger.Log(iTransaction, ex); iTransaction = null; return false;
}
return true;
}
#endregion
#region Public Methods
public static bool ExecuteNonQuery(string query, params object[] pObjects)
{
if (iConnection.State != ConnectionState.Open) iConnection.Open();
return ExecuteNonQuery(iTransaction == null ? iConnection : iTransaction.Connection, query, pObjects);
}
public static bool ExecuteNonQueryMY(string query, params object[] pObjects)
{
if (iConnection.State != ConnectionState.Open) iConnection.Open();
return ExecuteNonQueryMY(iTransaction == null ? iConnection : iTransaction.Connection, query, pObjects);
}
public static DataTable ExecuteQuery(string query, params object[] pObjects)
{
if (iConnection.State != ConnectionState.Open) iConnection.Open();
return ExecuteQuery(iTransaction == null ? iConnection : iTransaction.Connection, query, pObjects);
}
//public static FrxDataTable FrxExecuteQuery(string query, params object[] pObjects)
//{
// if (iConnection.State != ConnectionState.Open) iConnection.Open();
// return FrxExecuteQuery(iTransaction == null ? iConnection : iTransaction.Connection, query, pObjects);
//}
public static DataSet ExecuteMultiQuery(string query, params object[] pObjects)
{
if (iConnection.State != ConnectionState.Open) iConnection.Open();
return ExecuteMultiQuery(iTransaction == null ? iConnection : iTransaction.Connection, query, pObjects);
}
public static object ExecuteScalarQuery(string query, params object[] pObjects)
{
if (iConnection.State != ConnectionState.Open) iConnection.Open();
return ExecuteScalarQuery(iTransaction == null ? iConnection : iTransaction.Connection, query, pObjects);
}
public static SqlDataReader ExecuteReaderQuery(string query, params object[] pObjects)
{
if (iConnection.State != ConnectionState.Open) iConnection.Open();
return ExecuteReaderQuery(iTransaction == null ? iConnection : iTransaction.Connection, query, pObjects);
}
public static int ExecuteIdentityQuery(string query, params object[] pObjects)
{
if (iConnection.State != ConnectionState.Open) iConnection.Open();
return ExecuteIdentityQuery(iTransaction == null ? iConnection : iTransaction.Connection, query, pObjects);
}
public static Object[] ExecuteOutputQuery(string query, params object[] pObjects)
{
if (iConnection.State != ConnectionState.Open) iConnection.Open();
return ExecuteOutputQuery(iTransaction == null ? iConnection : iTransaction.Connection, query, pObjects);
}
#endregion
#region Private Methods
private static bool ExecuteNonQuery(SqlConnection connection, string query, params object[] pObjects)
{
using (SqlCommand command = connection.CreateCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = query;
command.Transaction = iTransaction;
try { DeriveParameters(command, pObjects); }
catch (Exception ex) { ErrorLogger.Log(command, ex); return false; }
try { command.ExecuteNonQuery();
if (query.StartsWith("i") || query.StartsWith("I")) MessageBox.Show("Kayıt Eklendi.", "Bildiri", MessageBoxButtons.OK, MessageBoxIcon.None);
if (query.StartsWith("u") || query.StartsWith("U")) MessageBox.Show("Kayıt Güncellendi.", "Bildiri", MessageBoxButtons.OK, MessageBoxIcon.None);
if (query.StartsWith("d") || query.StartsWith("D")) MessageBox.Show("Kayıt Silindi.", "Bildiri", MessageBoxButtons.OK, MessageBoxIcon.None);
return true; }
catch (Exception ex) { ErrorLogger.Log(command, ex); return false; }
}
}
private static bool ExecuteNonQueryMY(SqlConnection connection, string query, params object[] pObjects)
{
using (SqlCommand command = connection.CreateCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = query;
command.Transaction = iTransaction;
try { DeriveParameters(command, pObjects); }
catch (Exception ex) { ErrorLogger.Log(command, ex); return false; }
try
{
command.ExecuteNonQuery();
return true;
}
catch (Exception ex) { ErrorLogger.Log(command, ex); return false; }
}
}
private static DataTable ExecuteQuery(SqlConnection connection, string query, params object[] pObjects)
{
using (SqlCommand command = connection.CreateCommand())
{
DataTable returnTable = new DataTable("newTable");
command.CommandType = CommandType.Text;
command.CommandText = query;
command.Transaction = iTransaction;
try { DeriveParameters(command, pObjects); }
catch (Exception ex) { ErrorLogger.Log(command, ex); return null; }
SqlDataAdapter adapter = new SqlDataAdapter(command);
try { adapter.Fill(returnTable); }
catch (Exception ex) { ErrorLogger.Log(command, ex); return null; }
return returnTable;
}
}
//private static FrxDataTable FrxExecuteQuery(SqlConnection connection, string query, params object[] pObjects)
//{
// using (SqlCommand command = connection.CreateCommand())
// {
// FrxDataTable returnTable = new FrxDataTable("newTable");
// command.CommandType = CommandType.Text;
// command.CommandText = query;
// command.Transaction = iTransaction;
// try { DeriveParameters(command, pObjects); }
// catch (Exception ex) { ErrorLogger.Log(command, ex); return null; }
// SqlDataAdapter adapter = new SqlDataAdapter(command);
// try { adapter.Fill(returnTable); }
// catch (Exception ex) { ErrorLogger.Log(command, ex); return null; }
// return returnTable;
// }
//}
private static DataSet ExecuteMultiQuery(SqlConnection connection, string query, params object[] pObjects)
{
using (SqlCommand command = connection.CreateCommand())
{
DataSet ds = new DataSet();
command.CommandType = CommandType.Text;
command.CommandText = query;
command.Transaction = iTransaction;
try { DeriveParameters(command, pObjects); }
catch (Exception ex) { ErrorLogger.Log(command, ex); return null; }
SqlDataAdapter adapter = new SqlDataAdapter(command);
try { adapter.Fill(ds); }
catch (Exception ex) { ErrorLogger.Log(command, ex); return null; }
return ds;
}
}
private static object ExecuteScalarQuery(SqlConnection connection, string query, params object[] pObjects)
{
using (SqlCommand command = connection.CreateCommand())
{
object returnObject;
command.CommandType = CommandType.Text;
command.CommandText = query;
command.Transaction = iTransaction;
try { DeriveParameters(command, pObjects); }
catch (Exception ex) { ErrorLogger.Log(command, ex); return null; }
try { returnObject = command.ExecuteScalar(); }
catch (Exception ex) { ErrorLogger.Log(command, ex); return null; }
if (returnObject == DBNull.Value) returnObject = null;
return returnObject;
}
}
private static SqlDataReader ExecuteReaderQuery(SqlConnection connection, string query, params object[] pObjects)
{
SqlDataReader returnReader = null;
SqlCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = query;
command.Transaction = iTransaction;
try { DeriveParameters(command, pObjects); }
catch (Exception ex) { ErrorLogger.Log(command, ex); return null; }
try { returnReader = command.ExecuteReader(CommandBehavior.Default); }
catch (Exception ex) { ErrorLogger.Log(command, ex); }
return returnReader;
}
private static int ExecuteIdentityQuery(SqlConnection connection, string query, params object[] pObjects)
{
using (SqlCommand command = connection.CreateCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = query;
command.Transaction = iTransaction;
try { DeriveParameters(command, pObjects); }
catch (Exception ex) { ErrorLogger.Log(command, ex); return -1; }
command.CommandText += ";Set @RetId = @@Identity;";
command.Parameters.Add("@RetId", SqlDbType.Int);
command.Parameters["@RetId"].Direction = ParameterDirection.Output;
try { command.ExecuteNonQuery(); return Convert.ToInt32(command.Parameters["@RetId"].Value); }
catch (Exception ex) { ErrorLogger.Log(command, ex); return -1; }
}
}
private static Object[] ExecuteOutputQuery(SqlConnection connection, string query, params object[] pObjects)
{
using (SqlCommand command = connection.CreateCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = query;
command.Transaction = iTransaction;
try { DeriveParameters(command, pObjects); }
catch (Exception ex) { ErrorLogger.Log(command, ex); return null; }
try
{
command.ExecuteNonQuery();
var retVal = new List<object>();
for (int i = 1; i < command.Parameters.Count; i++)
if (command.Parameters.Direction == ParameterDirection.Output || command.Parameters.Direction == ParameterDirection.InputOutput)
retVal.Add(command.Parameters.Value);
return retVal.ToArray();
}
catch (Exception ex) { ErrorLogger.Log(command, ex); return null; }
}
}
private static void DeriveParameters(SqlCommand command, params object[] pObjects)
{
if (pObjects.Length > 0 && pObjects[0] is Dictionary<string, object>.ValueCollection)
{
Dictionary<string, object>.ValueCollection vc = (Dictionary<string, object>.ValueCollection)pObjects[0];
object[] pObjects1 = new object[vc.Count + pObjects.Length - 1];
vc.CopyTo(pObjects1, 0);
for (int i = 1; i < pObjects.Length; i++) pObjects1[vc.Count + i] = pObjects1;
pObjects = pObjects1;
}
else if (pObjects.Length > 0 && pObjects[0] is IList)
{
IList vc = (IList)pObjects[0];
for (int i = 1; i < pObjects.Length; i++) vc.Add(pObjects);
pObjects = new object[vc.Count];
vc.CopyTo(pObjects, 0);
}
int j = 0;
string commandText = command.CommandText;// = command.CommandText.Replace
, @);
if (string.IsNullOrEmpty(commandText)) return;
MatchCollection matched = Regex.Matches(commandText, @"@(w+)");// @"@(?<=@)(w+)(.w+)*");
for (int i = 0; i < matched.Count; i++)
{
if (!command.Parameters.Contains(matched.Value))
{
string pName = matched.Value;
object value;
if (pObjects.Length == 1 && pObjects[0] is Dictionary<string, object>)
{
string dpName = pName.Trim(@);
Dictionary<string, object> dpObjects = (Dictionary<string, object>)pObjects[0];
value = dpObjects[dpName] ?? DBNull.Value;
}
else if (j < pObjects.Length)
{
value = pObjects[j] ?? DBNull.Value;
}
else
{
continue;
}
command.Parameters.AddWithValue(pName, value);
command.Parameters[command.Parameters.Count - 1].Direction = ParameterDirection.Input;
if (pName == "@Resim")
command.Parameters[command.Parameters.Count - 1].SqlDbType = SqlDbType.Image;
j++;
}
}
}
#endregion
#region Public Methods
public static bool ExecuteNonProc(string query, params object[] pObjects)
{
if (iConnection.State != ConnectionState.Open) iConnection.Open();
return ExecuteNonProc(iTransaction == null ? iConnection : iTransaction.Connection, query, pObjects);
}
public static DataTable ExecuteProc(string query, params object[] pObjects)
{
if (iConnection.State != ConnectionState.Open) iConnection.Open();
return ExecuteProc(iTransaction == null ? iConnection : iTransaction.Connection, query, pObjects);
}
public static DataSet ExecuteMultiProc(string query, params object[] pObjects)
{
if (iConnection.State != ConnectionState.Open) iConnection.Open();
return ExecuteMultiProc(iTransaction == null ? iConnection : iTransaction.Connection, query, pObjects);
}
public static object ExecuteScalarProc(string query, params object[] pObjects)
{
if (iConnection.State != ConnectionState.Open) iConnection.Open();
return ExecuteScalarProc(iTransaction == null ? iConnection : iTransaction.Connection, query, pObjects);
}
public static SqlDataReader ExecuteReaderProc(string query, params object[] pObjects)
{
if (iConnection.State != ConnectionState.Open) iConnection.Open();
return ExecuteReaderProc(iTransaction == null ? iConnection : iTransaction.Connection, query, pObjects);
}
public static int ExecuteIdentityProc(string query, params object[] pObjects)
{
if (iConnection.State != ConnectionState.Open) iConnection.Open();
return ExecuteIdentityProc(iTransaction == null ? iConnection : iTransaction.Connection, query, pObjects);
}
public static Object[] ExecuteOutputProc(string query, params object[] pObjects)
{
if (iConnection.State != ConnectionState.Open) iConnection.Open();
return ExecuteOutputProc(iTransaction == null ? iConnection : iTransaction.Connection, query, pObjects);
}
#endregion
#region Private Methods
private static bool ExecuteNonProc(SqlConnection connection, string query, params object[] pObjects)
{
using (SqlCommand command = connection.CreateCommand())
{
command.CommandType = CommandType.StoredProcedure;
command.CommandText = query;
command.Transaction = iTransaction;
try { DeriveParametersProc(command, pObjects); }
catch (Exception ex) { ErrorLogger.Log(command, ex); return false; }
try { command.ExecuteNonQuery(); return true; }
catch (Exception ex) { ErrorLogger.Log(command, ex); return false; }
}
}
private static DataTable ExecuteProc(SqlConnection connection, string query, params object[] pObjects)
{
using (SqlCommand command = connection.CreateCommand())
{
DataTable returnTable = new DataTable("newTable");
command.CommandType = CommandType.StoredProcedure;
command.CommandText = query;
command.Transaction = iTransaction;
try { DeriveParametersProc(command, pObjects); }
catch (Exception ex) { ErrorLogger.Log(command, ex); return null; }
SqlDataAdapter adapter = new SqlDataAdapter(command);
try { adapter.Fill(returnTable); }
catch (Exception ex) { ErrorLogger.Log(command, ex); return null; }
return returnTable;
}
}
private static DataSet ExecuteMultiProc(SqlConnection connection, string query, params object[] pObjects)
{
using (SqlCommand command = connection.CreateCommand())
{
DataSet ds = new DataSet();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = query;
command.Transaction = iTransaction;
try { DeriveParametersProc(command, pObjects); }
catch (Exception ex) { ErrorLogger.Log(command, ex); return null; }
SqlDataAdapter adapter = new SqlDataAdapter(command);
try { adapter.Fill(ds); }
catch (Exception ex) { ErrorLogger.Log(command, ex); return null; }
return ds;
}
}
private static object ExecuteScalarProc(SqlConnection connection, string query, params object[] pObjects)
{
using (SqlCommand command = connection.CreateCommand())
{
object returnObject;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = query;
command.Transaction = iTransaction;
try { DeriveParametersProc(command, pObjects); }
catch (Exception ex) { ErrorLogger.Log(command, ex); return null; }
try { returnObject = command.ExecuteScalar(); }
catch (Exception ex) { ErrorLogger.Log(command, ex); return null; }
if (returnObject == DBNull.Value) returnObject = null;
return returnObject;
}
}
private static SqlDataReader ExecuteReaderProc(SqlConnection connection, string query, params object[] pObjects)
{
SqlDataReader returnReader = null;
SqlCommand command = connection.CreateCommand();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = query;
command.Transaction = iTransaction;
try { DeriveParametersProc(command, pObjects); }
catch (Exception ex) { ErrorLogger.Log(command, ex); return null; }
try { returnReader = command.ExecuteReader(CommandBehavior.Default); }
catch (Exception ex) { ErrorLogger.Log(command, ex); }
return returnReader;
}
private static int ExecuteIdentityProc(SqlConnection connection, string query, params object[] pObjects)
{
using (SqlCommand command = connection.CreateCommand())
{
command.CommandType = CommandType.StoredProcedure;
command.CommandText = query;
command.Transaction = iTransaction;
try { DeriveParametersProc(command, pObjects); }
catch (Exception ex) { ErrorLogger.Log(command, ex); return -1; }
command.CommandText += ";Set @RetId = @@Identity;";
command.Parameters.Add("@RetId", SqlDbType.Int);
command.Parameters["@RetId"].Direction = ParameterDirection.Output;
try { command.ExecuteNonQuery(); return Convert.ToInt32(command.Parameters["@RetId"].Value); }
catch (Exception ex) { ErrorLogger.Log(command, ex); return -1; }
}
}
private static Object[] ExecuteOutputProc(SqlConnection connection, string query, params object[] pObjects)
{
using (SqlCommand command = connection.CreateCommand())
{
command.CommandType = CommandType.StoredProcedure;
command.CommandText = query;
command.Transaction = iTransaction;
try { DeriveParametersProc(command, pObjects); }
catch (Exception ex) { ErrorLogger.Log(command, ex); return null; }
try
{
command.ExecuteNonQuery();
var retVal = new List<object>();
for (int i = 1; i < command.Parameters.Count; i++)
if (command.Parameters.Direction == ParameterDirection.Output || command.Parameters.Direction == ParameterDirection.InputOutput)
retVal.Add(command.Parameters.Value);
return retVal.ToArray();
}
catch (Exception ex) { ErrorLogger.Log(command, ex); return null; }
}
}
private static void DeriveParametersProc(SqlCommand command, params object[] pObjects)
{
var con = command.Connection;
if (con.State == ConnectionState.Closed || con.State == ConnectionState.Broken)
con.Open();
while (con.State != ConnectionState.Open)
Application.DoEvents();
SqlCommandBuilder.DeriveParameters(command);
if (pObjects.Length > 0 && pObjects[0] is Dictionary<string, object>.ValueCollection)
{
Dictionary<string, object>.ValueCollection vc = (Dictionary<string, object>.ValueCollection)pObjects[0];
object[] pObjects1 = new object[vc.Count + pObjects.Length - 1];
vc.CopyTo(pObjects1, 0);
for (int i = 1; i < pObjects.Length; i++) pObjects1[vc.Count + i] = pObjects1;
pObjects = pObjects1;
}
//else if (pObjects.Length > 0 && pObjects[0] is Dictionary<string, object>)
//{
// Dictionary<string, object> vc = (Dictionary<string, object>)pObjects[0];
// object[] pObjects1 = new object[vc.Count + pObjects.Length - 1];
// vc.Values.CopyTo(pObjects1, 0);
// for (int i = 1; i < pObjects.Length; i++) pObjects1[vc.Count + i] = pObjects1;
// pObjects = pObjects1;
//}
else if (pObjects.Length > 0 && pObjects[0] is IList)
{
IList vc = (IList)pObjects[0];
for (int i = 1; i < pObjects.Length; i++) vc.Add(pObjects);
pObjects = new object[vc.Count];
vc.CopyTo(pObjects, 0);
}
if (pObjects.Length > 0 && pObjects[0] is Dictionary<string, object>)
{
Dictionary<string, object> pobj = (Dictionary<string, object>)pObjects[0];
for (int i = 1; i < command.Parameters.Count; i++)
{
command.Parameters.Value =
pobj.ContainsKey(command.Parameters.ParameterName)
? pobj[command.Parameters.ParameterName] ?? DBNull.Value
: DBNull.Value;
}
}
else
{
for (int i = 1; i < command.Parameters.Count; i++)
{
command.Parameters.Value = (pObjects.Length >= i ? pObjects[i - 1] : null) ?? DBNull.Value;
}
}
}
#endregion
#region Public Methods
public static bool ExecuteNonPureQuery(string query)
{
if (iConnection.State != ConnectionState.Open) iConnection.Open();
return ExecuteNonPureQuery(iTransaction == null ? iConnection : iTransaction.Connection, query);
}
public static DataTable ExecutePureQuery(string query)
{
if (iConnection.State != ConnectionState.Open) iConnection.Open();
return ExecutePureQuery(iTransaction == null ? iConnection : iTransaction.Connection, query);
}
#endregion
#region Private Methods
private static DataTable ExecutePureQuery(SqlConnection connection, string query)
{
using (SqlCommand command = connection.CreateCommand())
{
DataTable returnTable = new DataTable("newTable");
command.CommandType = CommandType.Text;
command.CommandText = query;
command.Transaction = iTransaction;
SqlDataAdapter adapter = new SqlDataAdapter(command);
try { adapter.Fill(returnTable); }
catch (Exception ex) { ErrorLogger.Log(command, ex); return null; }
return returnTable;
}
}
private static bool ExecuteNonPureQuery(SqlConnection connection, string query)
{
using (SqlCommand command = connection.CreateCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = query;
command.Transaction = iTransaction;
try { command.ExecuteNonQuery(); return true; }
catch (Exception ex) { ErrorLogger.Log(command, ex); return false; }
}
}
#endregion
}
#endif
#if NPGQL
public static class PgDataAccess
{
#region Variables
private static string connectionString;
public static string ConnectionString
{
get { return connectionString; }
}
public static bool SetConnectionString(string value)
{
if (TryConnect(value))
{
connectionString = value;
return true;
}
return false;
}
private static NpgsqlTransaction transaction;
private static NpgsqlConnection pgConnection;
#endregion
#region Connect
public static bool Disconnect()
{
if (transaction != null) transaction.Rollback();
if (pgConnection.State != ConnectionState.Broken || pgConnection.State != ConnectionState.Closed) pgConnection.Close();
pgConnection = null;
return true;
}
public static bool Connect(string server, string database, string username, string password)
{
string connStr = string.Format("Server={0};Database={1};User Id={2};Password={3}", server, database, username, password);
return TryConnect(connStr);
}
public static bool Connect(string connStr)
{
return TryConnect(connStr);
}
private static bool TryConnect(string connStr)
{
if (transaction != null) RollBackTransaction();
if (pgConnection != null) pgConnection = null;
pgConnection = new NpgsqlConnection(connStr);
{
try { pgConnection.Open(); connectionString = connStr; }
catch (Exception ex) { ErrorLogger.Log(pgConnection, ex); return false; }
return true;
}
}
#endregion
#region transaction
public static bool BeginTransaction()
{
if (pgConnection.State != ConnectionState.Open) pgConnection.Open();
if (transaction != null) transaction.Rollback();
try { transaction = pgConnection.BeginTransaction(); }
catch (Exception ex) { ErrorLogger.Log(pgConnection, ex); return false; }
return true;
}
public static bool CommitTransaction()
{
if (pgConnection.State != ConnectionState.Open) pgConnection.Open();
try { transaction.Commit(); transaction = null; }
catch (Exception ex)
{
if (transaction != null)
{
transaction.Rollback();
ErrorLogger.Log(transaction, ex);
}
transaction = null;
return false;
}
return true;
}
public static bool RollBackTransaction()
{
if (pgConnection.State != ConnectionState.Open) pgConnection.Open();
try { transaction.Rollback(); transaction = null; }
catch (Exception ex)
{
ErrorLogger.Log(transaction, ex); transaction = null; return false;
}
return true;
}
#endregion
#region Public Methods
public static bool ExecuteNonQuery(string query, params object[] pObjects)
{
if (pgConnection.State != ConnectionState.Open) pgConnection.Open();
return ExecuteNonQuery(transaction == null ? pgConnection : transaction.Connection, query, pObjects);
}
public static DataTable ExecuteQuery(string query, params object[] pObjects)
{
if (pgConnection.State != ConnectionState.Open) pgConnection.Open();
return ExecuteQuery(transaction == null ? pgConnection : transaction.Connection, query, pObjects);
}
public static DataSet ExecuteMultiQuery(string query, params object[] pObjects)
{
if (pgConnection.State != ConnectionState.Open) pgConnection.Open();
return ExecuteMultiQuery(transaction == null ? pgConnection : transaction.Connection, query, pObjects);
}
public static object ExecuteScalarQuery(string query, params object[] pObjects)
{
if (pgConnection.State != ConnectionState.Open) pgConnection.Open();
return ExecuteScalarQuery(transaction == null ? pgConnection : transaction.Connection, query, pObjects);
}
public static NpgsqlDataReader ExecuteReaderQuery(string query, params object[] pObjects)
{
if (pgConnection.State != ConnectionState.Open) pgConnection.Open();
return ExecuteReaderQuery(transaction == null ? pgConnection : transaction.Connection, query, pObjects);
}
public static int ExecuteIdentityQuery(string query, string sequence, params object[] pObjects)
{
if (pgConnection.State != ConnectionState.Open) pgConnection.Open();
return ExecuteIdentityQuery(transaction == null ? pgConnection : transaction.Connection, query, sequence, pObjects);
}
public static Object[] ExecuteOutputQuery(string query, params object[] pObjects)
{
if (pgConnection.State != ConnectionState.Open) pgConnection.Open();
return ExecuteOutputQuery(transaction == null ? pgConnection : transaction.Connection, query, pObjects);
}
#endregion
#region Private Methods
private static bool ExecuteNonQuery(NpgsqlConnection connection, string query, params object[] pObjects)
{
using (NpgsqlCommand command = connection.CreateCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = query;
command.Transaction = transaction;
try { DeriveParametersQuery(command, pObjects); }
catch (Exception ex) { ErrorLogger.Log(command, ex); return false; }
try { command.ExecuteNonQuery(); return true; }
catch (Exception ex) { ErrorLogger.Log(command, ex); return false; }
}
}
private static DataTable ExecuteQuery(NpgsqlConnection connection, string query, params object[] pObjects)
{
using (NpgsqlCommand command = connection.CreateCommand())
{
DataTable returnTable = new DataTable("newTable");
command.CommandType = CommandType.Text;
command.CommandText = query;
command.Transaction = transaction;
try { DeriveParametersQuery(command, pObjects); }
catch (Exception ex) { ErrorLogger.Log(command, ex); return null; }
NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(command);
try { adapter.Fill(returnTable); }
catch (Exception ex) { ErrorLogger.Log(command, ex); return null; }
return returnTable;
}
}
private static DataSet ExecuteMultiQuery(NpgsqlConnection connection, string query, params object[] pObjects)
{
using (NpgsqlCommand command = connection.CreateCommand())
{
DataSet ds = new DataSet();
command.CommandType = CommandType.Text;
command.CommandText = query;
command.Transaction = transaction;
try { DeriveParametersQuery(command, pObjects); }
catch (Exception ex) { ErrorLogger.Log(command, ex); return null; }
NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(command);
try { adapter.Fill(ds); }
catch (Exception ex) { ErrorLogger.Log(command, ex); return null; }
return ds;
}
}
private static object ExecuteScalarQuery(NpgsqlConnection connection, string query, params object[] pObjects)
{
using (NpgsqlCommand command = connection.CreateCommand())
{
object returnObject;
command.CommandType = CommandType.Text;
command.CommandText = query;
command.Transaction = transaction;
try { DeriveParametersQuery(command, pObjects); }
catch (Exception ex) { ErrorLogger.Log(command, ex); return null; }
try { returnObject = command.ExecuteScalar(); }
catch (Exception ex) { ErrorLogger.Log(command, ex); return null; }
if (returnObject == DBNull.Value) returnObject = null;
return returnObject;
}
}
private static NpgsqlDataReader ExecuteReaderQuery(NpgsqlConnection connection, string query, params object[] pObjects)
{
NpgsqlDataReader returnReader = null;
NpgsqlCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = query;
command.Transaction = transaction;
try { DeriveParametersQuery(command, pObjects); }
catch (Exception ex) { ErrorLogger.Log(command, ex); return null; }
try { returnReader = command.ExecuteReader(CommandBehavior.Default); }
catch (Exception ex) { ErrorLogger.Log(command, ex); }
return returnReader;
}
private static int ExecuteIdentityQuery(NpgsqlConnection connection, string query, string sequence, params object[] pObjects)
{
using (NpgsqlCommand command = connection.CreateCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = query;
command.Transaction = transaction;
try { DeriveParametersQuery(command, pObjects); }
catch (Exception ex) { ErrorLogger.Log(command, ex); return -1; }
try { command.ExecuteNonQuery(); return Convert.ToInt32(new NpgsqlCommand("Select last_value From " + sequence, connection).ExecuteScalar()); }
catch (Exception ex) { ErrorLogger.Log(command, ex); return -1; }
}
}
private static Object[] ExecuteOutputQuery(NpgsqlConnection connection, string query, params object[] pObjects)
{
using (NpgsqlCommand command = connection.CreateCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = query;
command.Transaction = transaction;
try { DeriveParametersQuery(command, pObjects); }
catch (Exception ex) { ErrorLogger.Log(command, ex); return null; }
try
{
command.ExecuteNonQuery();
for (int i = 1; i < command.Parameters.Count; i++)
if (command.Parameters.Direction == ParameterDirection.Output || command.Parameters.Direction == ParameterDirection.InputOutput)
pObjects[i - 1] = command.Parameters;
return pObjects;
}
catch (Exception ex) { ErrorLogger.Log(command, ex); return null; }
}
}
private static void DeriveParametersQuery(NpgsqlCommand command, params object[] pObjects)
{
if (pObjects.Length > 0 && pObjects[0] is Dictionary<string, object>.ValueCollection)
{
Dictionary<string, object>.ValueCollection vc = (Dictionary<string, object>.ValueCollection)pObjects[0];
object[] pObjects1 = new object[vc.Count + pObjects.Length - 1];
vc.CopyTo(pObjects1, 0);
for (int i = 1; i < pObjects.Length; i++) pObjects1[vc.Count + i] = pObjects1;
pObjects = pObjects1;
}
else if (pObjects.Length > 0 && pObjects[0] is IList && pObjects[0].GetType() != typeof(byte[]))
{
IList vc = (IList)pObjects[0];
for (int i = 1; i < pObjects.Length; i++) vc.Add(pObjects);
pObjects = new object[vc.Count];
vc.CopyTo(pObjects, 0);
}
int j = 0;
string commandText = command.CommandText;//= command.CommandText.Replace, @);
if (string.IsNullOrEmpty(commandText)) return;
MatchCollection matched = Regex.Matches(commandText, @"@(w+)");
for (int i = 0; i < matched.Count; i++)
{
if (!command.Parameters.Contains(matched.Value))
{
string pName = matched.Value;
NpgsqlDbType npgType = NpgsqlDbType.Integer;
DbType dbTYpe = DbType.Int32;
object value = DBNull.Value;
if (pObjects.Length == 1 && pObjects[0] is Dictionary<string, object>)
{
string dpName = pName.Trim(@);
Dictionary<string, object> dpObjects = (Dictionary<string, object>)pObjects[0];
npgType = dpObjects[dpName] == null ? NpgsqlDbType.Integer : dpObjects[dpName].GetType() == typeof(DateTime) ? NpgsqlDbType.Date : dpObjects[dpName].GetType() == typeof(string) ? NpgsqlDbType.Varchar : dpObjects[dpName].GetType() == typeof(int) ? NpgsqlDbType.Integer : dpObjects[dpName].GetType() == typeof(byte[]) ? NpgsqlDbType.Bytea : NpgsqlDbType.Varchar;
dbTYpe = dpObjects[dpName] == null ? DbType.Int32 : dpObjects[dpName].GetType() == typeof(DateTime) ? DbType.DateTime : dpObjects[dpName].GetType() == typeof(string) ? DbType.String : dpObjects[dpName].GetType() == typeof(int) ? DbType.Int32 : dpObjects[dpName].GetType() == typeof(byte[]) ? DbType.Binary : DbType.String;
value = dpObjects[dpName] ?? DBNull.Value;
}
else if (j < pObjects.Length)
{
npgType = pObjects[j] == null ? NpgsqlDbType.Integer : pObjects[j].GetType() == typeof(DateTime) ? NpgsqlDbType.Date : pObjects[j].GetType() == typeof(string) ? NpgsqlDbType.Varchar : pObjects[j].GetType() == typeof(int) ? NpgsqlDbType.Integer : pObjects[j].GetType() == typeof(byte[]) ? NpgsqlDbType.Bytea : NpgsqlDbType.Varchar;
dbTYpe = pObjects[j] == null ? DbType.Int32 : pObjects[j].GetType() == typeof(DateTime) ? DbType.DateTime : pObjects[j].GetType() == typeof(string) ? DbType.String : pObjects[j].GetType() == typeof(int) ? DbType.Int32 : pObjects[j].GetType() == typeof(byte[]) ? DbType.Binary : DbType.String;
value = pObjects[j] ?? DBNull.Value;
}
else
{
continue;
}
NpgsqlParameter p = new NpgsqlParameter();
p.ParameterName = pName;
p.Value = value ?? DBNull.Value;
p.NpgsqlDbType = npgType;
p.DbType = dbTYpe;
command.Parameters.Add(p);
j++;
}
}
}
#endregion
}
#endif
////////////////////////////
Errorlogger.cs
/////////////////////////////
#define MSSQL
//#define NPGQL
using System;
using System.Windows.Forms;
#if MSSQL
using System.Data.SqlClient;
#endif
#if NPGQL
using Npgsql;
#endif
public static class ErrorLogger
{
#if MSSQL
public static void Log(SqlConnection connection, Exception exception)
{
MessageBox.Show(exception.Message+exception.StackTrace);
}
public static void Log(SqlCommand command, Exception exception)
{
MessageBox.Show(exception.Message + exception.StackTrace + "nAtn" + command.CommandText);
}
public static void Log(SqlTransaction transaction, Exception exception)
{
MessageBox.Show(exception.Message + exception.StackTrace);
}
#endif
#if NPGQL
public static void Log(NpgsqlTransaction transaction, Exception exception)
{
MessageBox.Show(exception.Message);
}
public static void Log(NpgsqlCommand command, Exception exception)
{
MessageBox.Show(exception.Message);
}
public static void Log(NpgsqlConnection connection, Exception exception)
{
MessageBox.Show(exception.Message);
}
#endif
}
[/code]
<br/>
View the full article
I have below DAL codes. It give me timeout and pooling error sometime. I have to close and open computer again when it give me that error.
My DAL codes have a problem ? Can you fix it ?
Thank You.
<pre class="prettyprint winform.cs
//I use my DAL at winform like below. sorgu means SQL query. and dic means dictionary like paremeters. You should fix my DAL codes due to MsDataAccess.Connect() and MsDataAccess.ExecuteQuery codes is important. I used them at huge project. There are a lot of some codes. If you change it, i should change a lot of codes at winforms.cs
//1-without Transaction
string sorgu1 = @"SELECT * FROM table";
Dictionary<string, object> dic = new Dictionary<string, object>();
dic.Add("x_ceksenetid", Convert.ToInt32(idL.Text));
MsDataAccess.Connect(genel.kullanici.ConnectionString);
DataTable dt = MsDataAccess.ExecuteQuery(sorgu1, dic);
//2-With Transaction
MsDataAccess.Connect(genel.kullanici.ConnectionString);
MsDataAccess.BeginTransaction();
bool success = true;
success = success && MsDataAccess.ExecuteNonQueryMY(sorguckupdatecarikarti, dicckupdatecarikarti);
success = success && MsDataAccess.ExecuteNonQueryMY(sorgu, dic);
success = success && MsDataAccess.ExecuteNonQueryMY(sorguceksenetupdate, dicceksenetupdate);
if (success)
{
MsDataAccess.CommitTransaction();
}
else
{
MsDataAccess.RollBackTransaction();
MessageBox.Show("İşlem Geri Alınırken bir hata oluştu. Lütfen işlemi tekrarlayınız.", "Hata", MessageBoxButtons.OK, MessageBoxIcon.None);
return;
}
///////////////////////////////
MsDataAccess.cs
//////////////////////////////
#define MSSQL
//#define NPGQL
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Text.RegularExpressions;
using System.Windows.Forms;
//using FastReport;
#if MSSQL
using System.Data.SqlClient;
using PROJECTNAMEEEEEEEEEEEEEEE;
#endif
#if NPGQL
using Npgsql;
using NpgsqlTypes;
#endif
#if MSSQL
public static class MsDataAccess
{
#region Variables
private static string iConnectionString;
public static string ConnectionString
{
get { return iConnectionString; }
}
public static bool SetConnectionString(string value)
{
if (TryConnect(value))
{
iConnectionString = value;
return true;
}
return false;
}
private static SqlTransaction iTransaction;
private static SqlConnection iConnection;
#endregion
#region Connect
public static bool Connect(string server, string database, string username, string password)
{
string connStr = string.Format("Server={0};Database={1};User Id={2};Password={3}", server, database, username, password);
try
{
return TryConnect(connStr);
}
catch
{
SqlConnection.ClearAllPools();
return TryConnect(connStr);
}
}
public static bool Connect(string connStr)
{
try
{
return TryConnect(connStr);
}
catch
{
SqlConnection.ClearAllPools();
return TryConnect(connStr);
}
}
private static bool TryConnect(string connStr)
{
//if (iConnection != null)
//{
// if (iConnection.State == ConnectionState.Open)
// {
// iConnection.Close();
// }
//}
connStr += ";Connection Timeout=500;";
//if (iTransaction != null)
//{
// RollBackTransaction();
// iTransaction = null;
//}
if (iTransaction != null) RollBackTransaction();
if (iConnection != null) iConnection = null;
// SqlConnection.ClearAllPools();
iConnection = new SqlConnection(connStr);
//if (iConnection == null)
// iConnection = new SqlConnection(connStr);
//{
// try
// {
// if (iConnection.State == ConnectionState.Closed)
// {
// iConnection.Open(); iConnectionString = connStr;
// }
// }
// catch (Exception ex) { ErrorLogger.Log(iConnection, ex); return false; }
// return true;
//}
// iConnection = new SqlConnection(connStr);
{
try { iConnection.Open(); iConnectionString = connStr; }
catch (Exception ex) { ErrorLogger.Log(iConnection, ex); return false; }
return true;
}
}
#endregion
#region iTransaction
public static bool BeginTransaction()
{
if (iConnection.State != ConnectionState.Open) iConnection.Open();
if (iTransaction != null) iTransaction.Rollback();
try { iTransaction = iConnection.BeginTransaction(); }
catch (Exception ex) { ErrorLogger.Log(iConnection, ex); return false; }
return true;
}
public static bool CommitTransaction()
{
if (iConnection.State != ConnectionState.Open) iConnection.Open();
try { iTransaction.Commit(); iTransaction = null; }
catch (Exception ex)
{
if (iTransaction != null)
{
iTransaction.Rollback();
ErrorLogger.Log(iTransaction, ex);
}
iTransaction = null;
return false;
}
return true;
}
public static bool RollBackTransaction()
{
if (iConnection.State != ConnectionState.Open) iConnection.Open();
try { iTransaction.Rollback(); iTransaction = null; }
catch (Exception ex)
{
ErrorLogger.Log(iTransaction, ex); iTransaction = null; return false;
}
return true;
}
#endregion
#region Public Methods
public static bool ExecuteNonQuery(string query, params object[] pObjects)
{
if (iConnection.State != ConnectionState.Open) iConnection.Open();
return ExecuteNonQuery(iTransaction == null ? iConnection : iTransaction.Connection, query, pObjects);
}
public static bool ExecuteNonQueryMY(string query, params object[] pObjects)
{
if (iConnection.State != ConnectionState.Open) iConnection.Open();
return ExecuteNonQueryMY(iTransaction == null ? iConnection : iTransaction.Connection, query, pObjects);
}
public static DataTable ExecuteQuery(string query, params object[] pObjects)
{
if (iConnection.State != ConnectionState.Open) iConnection.Open();
return ExecuteQuery(iTransaction == null ? iConnection : iTransaction.Connection, query, pObjects);
}
//public static FrxDataTable FrxExecuteQuery(string query, params object[] pObjects)
//{
// if (iConnection.State != ConnectionState.Open) iConnection.Open();
// return FrxExecuteQuery(iTransaction == null ? iConnection : iTransaction.Connection, query, pObjects);
//}
public static DataSet ExecuteMultiQuery(string query, params object[] pObjects)
{
if (iConnection.State != ConnectionState.Open) iConnection.Open();
return ExecuteMultiQuery(iTransaction == null ? iConnection : iTransaction.Connection, query, pObjects);
}
public static object ExecuteScalarQuery(string query, params object[] pObjects)
{
if (iConnection.State != ConnectionState.Open) iConnection.Open();
return ExecuteScalarQuery(iTransaction == null ? iConnection : iTransaction.Connection, query, pObjects);
}
public static SqlDataReader ExecuteReaderQuery(string query, params object[] pObjects)
{
if (iConnection.State != ConnectionState.Open) iConnection.Open();
return ExecuteReaderQuery(iTransaction == null ? iConnection : iTransaction.Connection, query, pObjects);
}
public static int ExecuteIdentityQuery(string query, params object[] pObjects)
{
if (iConnection.State != ConnectionState.Open) iConnection.Open();
return ExecuteIdentityQuery(iTransaction == null ? iConnection : iTransaction.Connection, query, pObjects);
}
public static Object[] ExecuteOutputQuery(string query, params object[] pObjects)
{
if (iConnection.State != ConnectionState.Open) iConnection.Open();
return ExecuteOutputQuery(iTransaction == null ? iConnection : iTransaction.Connection, query, pObjects);
}
#endregion
#region Private Methods
private static bool ExecuteNonQuery(SqlConnection connection, string query, params object[] pObjects)
{
using (SqlCommand command = connection.CreateCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = query;
command.Transaction = iTransaction;
try { DeriveParameters(command, pObjects); }
catch (Exception ex) { ErrorLogger.Log(command, ex); return false; }
try { command.ExecuteNonQuery();
if (query.StartsWith("i") || query.StartsWith("I")) MessageBox.Show("Kayıt Eklendi.", "Bildiri", MessageBoxButtons.OK, MessageBoxIcon.None);
if (query.StartsWith("u") || query.StartsWith("U")) MessageBox.Show("Kayıt Güncellendi.", "Bildiri", MessageBoxButtons.OK, MessageBoxIcon.None);
if (query.StartsWith("d") || query.StartsWith("D")) MessageBox.Show("Kayıt Silindi.", "Bildiri", MessageBoxButtons.OK, MessageBoxIcon.None);
return true; }
catch (Exception ex) { ErrorLogger.Log(command, ex); return false; }
}
}
private static bool ExecuteNonQueryMY(SqlConnection connection, string query, params object[] pObjects)
{
using (SqlCommand command = connection.CreateCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = query;
command.Transaction = iTransaction;
try { DeriveParameters(command, pObjects); }
catch (Exception ex) { ErrorLogger.Log(command, ex); return false; }
try
{
command.ExecuteNonQuery();
return true;
}
catch (Exception ex) { ErrorLogger.Log(command, ex); return false; }
}
}
private static DataTable ExecuteQuery(SqlConnection connection, string query, params object[] pObjects)
{
using (SqlCommand command = connection.CreateCommand())
{
DataTable returnTable = new DataTable("newTable");
command.CommandType = CommandType.Text;
command.CommandText = query;
command.Transaction = iTransaction;
try { DeriveParameters(command, pObjects); }
catch (Exception ex) { ErrorLogger.Log(command, ex); return null; }
SqlDataAdapter adapter = new SqlDataAdapter(command);
try { adapter.Fill(returnTable); }
catch (Exception ex) { ErrorLogger.Log(command, ex); return null; }
return returnTable;
}
}
//private static FrxDataTable FrxExecuteQuery(SqlConnection connection, string query, params object[] pObjects)
//{
// using (SqlCommand command = connection.CreateCommand())
// {
// FrxDataTable returnTable = new FrxDataTable("newTable");
// command.CommandType = CommandType.Text;
// command.CommandText = query;
// command.Transaction = iTransaction;
// try { DeriveParameters(command, pObjects); }
// catch (Exception ex) { ErrorLogger.Log(command, ex); return null; }
// SqlDataAdapter adapter = new SqlDataAdapter(command);
// try { adapter.Fill(returnTable); }
// catch (Exception ex) { ErrorLogger.Log(command, ex); return null; }
// return returnTable;
// }
//}
private static DataSet ExecuteMultiQuery(SqlConnection connection, string query, params object[] pObjects)
{
using (SqlCommand command = connection.CreateCommand())
{
DataSet ds = new DataSet();
command.CommandType = CommandType.Text;
command.CommandText = query;
command.Transaction = iTransaction;
try { DeriveParameters(command, pObjects); }
catch (Exception ex) { ErrorLogger.Log(command, ex); return null; }
SqlDataAdapter adapter = new SqlDataAdapter(command);
try { adapter.Fill(ds); }
catch (Exception ex) { ErrorLogger.Log(command, ex); return null; }
return ds;
}
}
private static object ExecuteScalarQuery(SqlConnection connection, string query, params object[] pObjects)
{
using (SqlCommand command = connection.CreateCommand())
{
object returnObject;
command.CommandType = CommandType.Text;
command.CommandText = query;
command.Transaction = iTransaction;
try { DeriveParameters(command, pObjects); }
catch (Exception ex) { ErrorLogger.Log(command, ex); return null; }
try { returnObject = command.ExecuteScalar(); }
catch (Exception ex) { ErrorLogger.Log(command, ex); return null; }
if (returnObject == DBNull.Value) returnObject = null;
return returnObject;
}
}
private static SqlDataReader ExecuteReaderQuery(SqlConnection connection, string query, params object[] pObjects)
{
SqlDataReader returnReader = null;
SqlCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = query;
command.Transaction = iTransaction;
try { DeriveParameters(command, pObjects); }
catch (Exception ex) { ErrorLogger.Log(command, ex); return null; }
try { returnReader = command.ExecuteReader(CommandBehavior.Default); }
catch (Exception ex) { ErrorLogger.Log(command, ex); }
return returnReader;
}
private static int ExecuteIdentityQuery(SqlConnection connection, string query, params object[] pObjects)
{
using (SqlCommand command = connection.CreateCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = query;
command.Transaction = iTransaction;
try { DeriveParameters(command, pObjects); }
catch (Exception ex) { ErrorLogger.Log(command, ex); return -1; }
command.CommandText += ";Set @RetId = @@Identity;";
command.Parameters.Add("@RetId", SqlDbType.Int);
command.Parameters["@RetId"].Direction = ParameterDirection.Output;
try { command.ExecuteNonQuery(); return Convert.ToInt32(command.Parameters["@RetId"].Value); }
catch (Exception ex) { ErrorLogger.Log(command, ex); return -1; }
}
}
private static Object[] ExecuteOutputQuery(SqlConnection connection, string query, params object[] pObjects)
{
using (SqlCommand command = connection.CreateCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = query;
command.Transaction = iTransaction;
try { DeriveParameters(command, pObjects); }
catch (Exception ex) { ErrorLogger.Log(command, ex); return null; }
try
{
command.ExecuteNonQuery();
var retVal = new List<object>();
for (int i = 1; i < command.Parameters.Count; i++)
if (command.Parameters.Direction == ParameterDirection.Output || command.Parameters.Direction == ParameterDirection.InputOutput)
retVal.Add(command.Parameters.Value);
return retVal.ToArray();
}
catch (Exception ex) { ErrorLogger.Log(command, ex); return null; }
}
}
private static void DeriveParameters(SqlCommand command, params object[] pObjects)
{
if (pObjects.Length > 0 && pObjects[0] is Dictionary<string, object>.ValueCollection)
{
Dictionary<string, object>.ValueCollection vc = (Dictionary<string, object>.ValueCollection)pObjects[0];
object[] pObjects1 = new object[vc.Count + pObjects.Length - 1];
vc.CopyTo(pObjects1, 0);
for (int i = 1; i < pObjects.Length; i++) pObjects1[vc.Count + i] = pObjects1;
pObjects = pObjects1;
}
else if (pObjects.Length > 0 && pObjects[0] is IList)
{
IList vc = (IList)pObjects[0];
for (int i = 1; i < pObjects.Length; i++) vc.Add(pObjects);
pObjects = new object[vc.Count];
vc.CopyTo(pObjects, 0);
}
int j = 0;
string commandText = command.CommandText;// = command.CommandText.Replace
, @);if (string.IsNullOrEmpty(commandText)) return;
MatchCollection matched = Regex.Matches(commandText, @"@(w+)");// @"@(?<=@)(w+)(.w+)*");
for (int i = 0; i < matched.Count; i++)
{
if (!command.Parameters.Contains(matched.Value))
{
string pName = matched.Value;
object value;
if (pObjects.Length == 1 && pObjects[0] is Dictionary<string, object>)
{
string dpName = pName.Trim(@);
Dictionary<string, object> dpObjects = (Dictionary<string, object>)pObjects[0];
value = dpObjects[dpName] ?? DBNull.Value;
}
else if (j < pObjects.Length)
{
value = pObjects[j] ?? DBNull.Value;
}
else
{
continue;
}
command.Parameters.AddWithValue(pName, value);
command.Parameters[command.Parameters.Count - 1].Direction = ParameterDirection.Input;
if (pName == "@Resim")
command.Parameters[command.Parameters.Count - 1].SqlDbType = SqlDbType.Image;
j++;
}
}
}
#endregion
#region Public Methods
public static bool ExecuteNonProc(string query, params object[] pObjects)
{
if (iConnection.State != ConnectionState.Open) iConnection.Open();
return ExecuteNonProc(iTransaction == null ? iConnection : iTransaction.Connection, query, pObjects);
}
public static DataTable ExecuteProc(string query, params object[] pObjects)
{
if (iConnection.State != ConnectionState.Open) iConnection.Open();
return ExecuteProc(iTransaction == null ? iConnection : iTransaction.Connection, query, pObjects);
}
public static DataSet ExecuteMultiProc(string query, params object[] pObjects)
{
if (iConnection.State != ConnectionState.Open) iConnection.Open();
return ExecuteMultiProc(iTransaction == null ? iConnection : iTransaction.Connection, query, pObjects);
}
public static object ExecuteScalarProc(string query, params object[] pObjects)
{
if (iConnection.State != ConnectionState.Open) iConnection.Open();
return ExecuteScalarProc(iTransaction == null ? iConnection : iTransaction.Connection, query, pObjects);
}
public static SqlDataReader ExecuteReaderProc(string query, params object[] pObjects)
{
if (iConnection.State != ConnectionState.Open) iConnection.Open();
return ExecuteReaderProc(iTransaction == null ? iConnection : iTransaction.Connection, query, pObjects);
}
public static int ExecuteIdentityProc(string query, params object[] pObjects)
{
if (iConnection.State != ConnectionState.Open) iConnection.Open();
return ExecuteIdentityProc(iTransaction == null ? iConnection : iTransaction.Connection, query, pObjects);
}
public static Object[] ExecuteOutputProc(string query, params object[] pObjects)
{
if (iConnection.State != ConnectionState.Open) iConnection.Open();
return ExecuteOutputProc(iTransaction == null ? iConnection : iTransaction.Connection, query, pObjects);
}
#endregion
#region Private Methods
private static bool ExecuteNonProc(SqlConnection connection, string query, params object[] pObjects)
{
using (SqlCommand command = connection.CreateCommand())
{
command.CommandType = CommandType.StoredProcedure;
command.CommandText = query;
command.Transaction = iTransaction;
try { DeriveParametersProc(command, pObjects); }
catch (Exception ex) { ErrorLogger.Log(command, ex); return false; }
try { command.ExecuteNonQuery(); return true; }
catch (Exception ex) { ErrorLogger.Log(command, ex); return false; }
}
}
private static DataTable ExecuteProc(SqlConnection connection, string query, params object[] pObjects)
{
using (SqlCommand command = connection.CreateCommand())
{
DataTable returnTable = new DataTable("newTable");
command.CommandType = CommandType.StoredProcedure;
command.CommandText = query;
command.Transaction = iTransaction;
try { DeriveParametersProc(command, pObjects); }
catch (Exception ex) { ErrorLogger.Log(command, ex); return null; }
SqlDataAdapter adapter = new SqlDataAdapter(command);
try { adapter.Fill(returnTable); }
catch (Exception ex) { ErrorLogger.Log(command, ex); return null; }
return returnTable;
}
}
private static DataSet ExecuteMultiProc(SqlConnection connection, string query, params object[] pObjects)
{
using (SqlCommand command = connection.CreateCommand())
{
DataSet ds = new DataSet();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = query;
command.Transaction = iTransaction;
try { DeriveParametersProc(command, pObjects); }
catch (Exception ex) { ErrorLogger.Log(command, ex); return null; }
SqlDataAdapter adapter = new SqlDataAdapter(command);
try { adapter.Fill(ds); }
catch (Exception ex) { ErrorLogger.Log(command, ex); return null; }
return ds;
}
}
private static object ExecuteScalarProc(SqlConnection connection, string query, params object[] pObjects)
{
using (SqlCommand command = connection.CreateCommand())
{
object returnObject;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = query;
command.Transaction = iTransaction;
try { DeriveParametersProc(command, pObjects); }
catch (Exception ex) { ErrorLogger.Log(command, ex); return null; }
try { returnObject = command.ExecuteScalar(); }
catch (Exception ex) { ErrorLogger.Log(command, ex); return null; }
if (returnObject == DBNull.Value) returnObject = null;
return returnObject;
}
}
private static SqlDataReader ExecuteReaderProc(SqlConnection connection, string query, params object[] pObjects)
{
SqlDataReader returnReader = null;
SqlCommand command = connection.CreateCommand();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = query;
command.Transaction = iTransaction;
try { DeriveParametersProc(command, pObjects); }
catch (Exception ex) { ErrorLogger.Log(command, ex); return null; }
try { returnReader = command.ExecuteReader(CommandBehavior.Default); }
catch (Exception ex) { ErrorLogger.Log(command, ex); }
return returnReader;
}
private static int ExecuteIdentityProc(SqlConnection connection, string query, params object[] pObjects)
{
using (SqlCommand command = connection.CreateCommand())
{
command.CommandType = CommandType.StoredProcedure;
command.CommandText = query;
command.Transaction = iTransaction;
try { DeriveParametersProc(command, pObjects); }
catch (Exception ex) { ErrorLogger.Log(command, ex); return -1; }
command.CommandText += ";Set @RetId = @@Identity;";
command.Parameters.Add("@RetId", SqlDbType.Int);
command.Parameters["@RetId"].Direction = ParameterDirection.Output;
try { command.ExecuteNonQuery(); return Convert.ToInt32(command.Parameters["@RetId"].Value); }
catch (Exception ex) { ErrorLogger.Log(command, ex); return -1; }
}
}
private static Object[] ExecuteOutputProc(SqlConnection connection, string query, params object[] pObjects)
{
using (SqlCommand command = connection.CreateCommand())
{
command.CommandType = CommandType.StoredProcedure;
command.CommandText = query;
command.Transaction = iTransaction;
try { DeriveParametersProc(command, pObjects); }
catch (Exception ex) { ErrorLogger.Log(command, ex); return null; }
try
{
command.ExecuteNonQuery();
var retVal = new List<object>();
for (int i = 1; i < command.Parameters.Count; i++)
if (command.Parameters.Direction == ParameterDirection.Output || command.Parameters.Direction == ParameterDirection.InputOutput)
retVal.Add(command.Parameters.Value);
return retVal.ToArray();
}
catch (Exception ex) { ErrorLogger.Log(command, ex); return null; }
}
}
private static void DeriveParametersProc(SqlCommand command, params object[] pObjects)
{
var con = command.Connection;
if (con.State == ConnectionState.Closed || con.State == ConnectionState.Broken)
con.Open();
while (con.State != ConnectionState.Open)
Application.DoEvents();
SqlCommandBuilder.DeriveParameters(command);
if (pObjects.Length > 0 && pObjects[0] is Dictionary<string, object>.ValueCollection)
{
Dictionary<string, object>.ValueCollection vc = (Dictionary<string, object>.ValueCollection)pObjects[0];
object[] pObjects1 = new object[vc.Count + pObjects.Length - 1];
vc.CopyTo(pObjects1, 0);
for (int i = 1; i < pObjects.Length; i++) pObjects1[vc.Count + i] = pObjects1;
pObjects = pObjects1;
}
//else if (pObjects.Length > 0 && pObjects[0] is Dictionary<string, object>)
//{
// Dictionary<string, object> vc = (Dictionary<string, object>)pObjects[0];
// object[] pObjects1 = new object[vc.Count + pObjects.Length - 1];
// vc.Values.CopyTo(pObjects1, 0);
// for (int i = 1; i < pObjects.Length; i++) pObjects1[vc.Count + i] = pObjects1;
// pObjects = pObjects1;
//}
else if (pObjects.Length > 0 && pObjects[0] is IList)
{
IList vc = (IList)pObjects[0];
for (int i = 1; i < pObjects.Length; i++) vc.Add(pObjects);
pObjects = new object[vc.Count];
vc.CopyTo(pObjects, 0);
}
if (pObjects.Length > 0 && pObjects[0] is Dictionary<string, object>)
{
Dictionary<string, object> pobj = (Dictionary<string, object>)pObjects[0];
for (int i = 1; i < command.Parameters.Count; i++)
{
command.Parameters.Value =
pobj.ContainsKey(command.Parameters.ParameterName)
? pobj[command.Parameters.ParameterName] ?? DBNull.Value
: DBNull.Value;
}
}
else
{
for (int i = 1; i < command.Parameters.Count; i++)
{
command.Parameters.Value = (pObjects.Length >= i ? pObjects[i - 1] : null) ?? DBNull.Value;
}
}
}
#endregion
#region Public Methods
public static bool ExecuteNonPureQuery(string query)
{
if (iConnection.State != ConnectionState.Open) iConnection.Open();
return ExecuteNonPureQuery(iTransaction == null ? iConnection : iTransaction.Connection, query);
}
public static DataTable ExecutePureQuery(string query)
{
if (iConnection.State != ConnectionState.Open) iConnection.Open();
return ExecutePureQuery(iTransaction == null ? iConnection : iTransaction.Connection, query);
}
#endregion
#region Private Methods
private static DataTable ExecutePureQuery(SqlConnection connection, string query)
{
using (SqlCommand command = connection.CreateCommand())
{
DataTable returnTable = new DataTable("newTable");
command.CommandType = CommandType.Text;
command.CommandText = query;
command.Transaction = iTransaction;
SqlDataAdapter adapter = new SqlDataAdapter(command);
try { adapter.Fill(returnTable); }
catch (Exception ex) { ErrorLogger.Log(command, ex); return null; }
return returnTable;
}
}
private static bool ExecuteNonPureQuery(SqlConnection connection, string query)
{
using (SqlCommand command = connection.CreateCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = query;
command.Transaction = iTransaction;
try { command.ExecuteNonQuery(); return true; }
catch (Exception ex) { ErrorLogger.Log(command, ex); return false; }
}
}
#endregion
}
#endif
#if NPGQL
public static class PgDataAccess
{
#region Variables
private static string connectionString;
public static string ConnectionString
{
get { return connectionString; }
}
public static bool SetConnectionString(string value)
{
if (TryConnect(value))
{
connectionString = value;
return true;
}
return false;
}
private static NpgsqlTransaction transaction;
private static NpgsqlConnection pgConnection;
#endregion
#region Connect
public static bool Disconnect()
{
if (transaction != null) transaction.Rollback();
if (pgConnection.State != ConnectionState.Broken || pgConnection.State != ConnectionState.Closed) pgConnection.Close();
pgConnection = null;
return true;
}
public static bool Connect(string server, string database, string username, string password)
{
string connStr = string.Format("Server={0};Database={1};User Id={2};Password={3}", server, database, username, password);
return TryConnect(connStr);
}
public static bool Connect(string connStr)
{
return TryConnect(connStr);
}
private static bool TryConnect(string connStr)
{
if (transaction != null) RollBackTransaction();
if (pgConnection != null) pgConnection = null;
pgConnection = new NpgsqlConnection(connStr);
{
try { pgConnection.Open(); connectionString = connStr; }
catch (Exception ex) { ErrorLogger.Log(pgConnection, ex); return false; }
return true;
}
}
#endregion
#region transaction
public static bool BeginTransaction()
{
if (pgConnection.State != ConnectionState.Open) pgConnection.Open();
if (transaction != null) transaction.Rollback();
try { transaction = pgConnection.BeginTransaction(); }
catch (Exception ex) { ErrorLogger.Log(pgConnection, ex); return false; }
return true;
}
public static bool CommitTransaction()
{
if (pgConnection.State != ConnectionState.Open) pgConnection.Open();
try { transaction.Commit(); transaction = null; }
catch (Exception ex)
{
if (transaction != null)
{
transaction.Rollback();
ErrorLogger.Log(transaction, ex);
}
transaction = null;
return false;
}
return true;
}
public static bool RollBackTransaction()
{
if (pgConnection.State != ConnectionState.Open) pgConnection.Open();
try { transaction.Rollback(); transaction = null; }
catch (Exception ex)
{
ErrorLogger.Log(transaction, ex); transaction = null; return false;
}
return true;
}
#endregion
#region Public Methods
public static bool ExecuteNonQuery(string query, params object[] pObjects)
{
if (pgConnection.State != ConnectionState.Open) pgConnection.Open();
return ExecuteNonQuery(transaction == null ? pgConnection : transaction.Connection, query, pObjects);
}
public static DataTable ExecuteQuery(string query, params object[] pObjects)
{
if (pgConnection.State != ConnectionState.Open) pgConnection.Open();
return ExecuteQuery(transaction == null ? pgConnection : transaction.Connection, query, pObjects);
}
public static DataSet ExecuteMultiQuery(string query, params object[] pObjects)
{
if (pgConnection.State != ConnectionState.Open) pgConnection.Open();
return ExecuteMultiQuery(transaction == null ? pgConnection : transaction.Connection, query, pObjects);
}
public static object ExecuteScalarQuery(string query, params object[] pObjects)
{
if (pgConnection.State != ConnectionState.Open) pgConnection.Open();
return ExecuteScalarQuery(transaction == null ? pgConnection : transaction.Connection, query, pObjects);
}
public static NpgsqlDataReader ExecuteReaderQuery(string query, params object[] pObjects)
{
if (pgConnection.State != ConnectionState.Open) pgConnection.Open();
return ExecuteReaderQuery(transaction == null ? pgConnection : transaction.Connection, query, pObjects);
}
public static int ExecuteIdentityQuery(string query, string sequence, params object[] pObjects)
{
if (pgConnection.State != ConnectionState.Open) pgConnection.Open();
return ExecuteIdentityQuery(transaction == null ? pgConnection : transaction.Connection, query, sequence, pObjects);
}
public static Object[] ExecuteOutputQuery(string query, params object[] pObjects)
{
if (pgConnection.State != ConnectionState.Open) pgConnection.Open();
return ExecuteOutputQuery(transaction == null ? pgConnection : transaction.Connection, query, pObjects);
}
#endregion
#region Private Methods
private static bool ExecuteNonQuery(NpgsqlConnection connection, string query, params object[] pObjects)
{
using (NpgsqlCommand command = connection.CreateCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = query;
command.Transaction = transaction;
try { DeriveParametersQuery(command, pObjects); }
catch (Exception ex) { ErrorLogger.Log(command, ex); return false; }
try { command.ExecuteNonQuery(); return true; }
catch (Exception ex) { ErrorLogger.Log(command, ex); return false; }
}
}
private static DataTable ExecuteQuery(NpgsqlConnection connection, string query, params object[] pObjects)
{
using (NpgsqlCommand command = connection.CreateCommand())
{
DataTable returnTable = new DataTable("newTable");
command.CommandType = CommandType.Text;
command.CommandText = query;
command.Transaction = transaction;
try { DeriveParametersQuery(command, pObjects); }
catch (Exception ex) { ErrorLogger.Log(command, ex); return null; }
NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(command);
try { adapter.Fill(returnTable); }
catch (Exception ex) { ErrorLogger.Log(command, ex); return null; }
return returnTable;
}
}
private static DataSet ExecuteMultiQuery(NpgsqlConnection connection, string query, params object[] pObjects)
{
using (NpgsqlCommand command = connection.CreateCommand())
{
DataSet ds = new DataSet();
command.CommandType = CommandType.Text;
command.CommandText = query;
command.Transaction = transaction;
try { DeriveParametersQuery(command, pObjects); }
catch (Exception ex) { ErrorLogger.Log(command, ex); return null; }
NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(command);
try { adapter.Fill(ds); }
catch (Exception ex) { ErrorLogger.Log(command, ex); return null; }
return ds;
}
}
private static object ExecuteScalarQuery(NpgsqlConnection connection, string query, params object[] pObjects)
{
using (NpgsqlCommand command = connection.CreateCommand())
{
object returnObject;
command.CommandType = CommandType.Text;
command.CommandText = query;
command.Transaction = transaction;
try { DeriveParametersQuery(command, pObjects); }
catch (Exception ex) { ErrorLogger.Log(command, ex); return null; }
try { returnObject = command.ExecuteScalar(); }
catch (Exception ex) { ErrorLogger.Log(command, ex); return null; }
if (returnObject == DBNull.Value) returnObject = null;
return returnObject;
}
}
private static NpgsqlDataReader ExecuteReaderQuery(NpgsqlConnection connection, string query, params object[] pObjects)
{
NpgsqlDataReader returnReader = null;
NpgsqlCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = query;
command.Transaction = transaction;
try { DeriveParametersQuery(command, pObjects); }
catch (Exception ex) { ErrorLogger.Log(command, ex); return null; }
try { returnReader = command.ExecuteReader(CommandBehavior.Default); }
catch (Exception ex) { ErrorLogger.Log(command, ex); }
return returnReader;
}
private static int ExecuteIdentityQuery(NpgsqlConnection connection, string query, string sequence, params object[] pObjects)
{
using (NpgsqlCommand command = connection.CreateCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = query;
command.Transaction = transaction;
try { DeriveParametersQuery(command, pObjects); }
catch (Exception ex) { ErrorLogger.Log(command, ex); return -1; }
try { command.ExecuteNonQuery(); return Convert.ToInt32(new NpgsqlCommand("Select last_value From " + sequence, connection).ExecuteScalar()); }
catch (Exception ex) { ErrorLogger.Log(command, ex); return -1; }
}
}
private static Object[] ExecuteOutputQuery(NpgsqlConnection connection, string query, params object[] pObjects)
{
using (NpgsqlCommand command = connection.CreateCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = query;
command.Transaction = transaction;
try { DeriveParametersQuery(command, pObjects); }
catch (Exception ex) { ErrorLogger.Log(command, ex); return null; }
try
{
command.ExecuteNonQuery();
for (int i = 1; i < command.Parameters.Count; i++)
if (command.Parameters.Direction == ParameterDirection.Output || command.Parameters.Direction == ParameterDirection.InputOutput)
pObjects[i - 1] = command.Parameters;
return pObjects;
}
catch (Exception ex) { ErrorLogger.Log(command, ex); return null; }
}
}
private static void DeriveParametersQuery(NpgsqlCommand command, params object[] pObjects)
{
if (pObjects.Length > 0 && pObjects[0] is Dictionary<string, object>.ValueCollection)
{
Dictionary<string, object>.ValueCollection vc = (Dictionary<string, object>.ValueCollection)pObjects[0];
object[] pObjects1 = new object[vc.Count + pObjects.Length - 1];
vc.CopyTo(pObjects1, 0);
for (int i = 1; i < pObjects.Length; i++) pObjects1[vc.Count + i] = pObjects1;
pObjects = pObjects1;
}
else if (pObjects.Length > 0 && pObjects[0] is IList && pObjects[0].GetType() != typeof(byte[]))
{
IList vc = (IList)pObjects[0];
for (int i = 1; i < pObjects.Length; i++) vc.Add(pObjects);
pObjects = new object[vc.Count];
vc.CopyTo(pObjects, 0);
}
int j = 0;
string commandText = command.CommandText;//= command.CommandText.Replace
, @);if (string.IsNullOrEmpty(commandText)) return;
MatchCollection matched = Regex.Matches(commandText, @"@(w+)");
for (int i = 0; i < matched.Count; i++)
{
if (!command.Parameters.Contains(matched.Value))
{
string pName = matched.Value;
NpgsqlDbType npgType = NpgsqlDbType.Integer;
DbType dbTYpe = DbType.Int32;
object value = DBNull.Value;
if (pObjects.Length == 1 && pObjects[0] is Dictionary<string, object>)
{
string dpName = pName.Trim(@);
Dictionary<string, object> dpObjects = (Dictionary<string, object>)pObjects[0];
npgType = dpObjects[dpName] == null ? NpgsqlDbType.Integer : dpObjects[dpName].GetType() == typeof(DateTime) ? NpgsqlDbType.Date : dpObjects[dpName].GetType() == typeof(string) ? NpgsqlDbType.Varchar : dpObjects[dpName].GetType() == typeof(int) ? NpgsqlDbType.Integer : dpObjects[dpName].GetType() == typeof(byte[]) ? NpgsqlDbType.Bytea : NpgsqlDbType.Varchar;
dbTYpe = dpObjects[dpName] == null ? DbType.Int32 : dpObjects[dpName].GetType() == typeof(DateTime) ? DbType.DateTime : dpObjects[dpName].GetType() == typeof(string) ? DbType.String : dpObjects[dpName].GetType() == typeof(int) ? DbType.Int32 : dpObjects[dpName].GetType() == typeof(byte[]) ? DbType.Binary : DbType.String;
value = dpObjects[dpName] ?? DBNull.Value;
}
else if (j < pObjects.Length)
{
npgType = pObjects[j] == null ? NpgsqlDbType.Integer : pObjects[j].GetType() == typeof(DateTime) ? NpgsqlDbType.Date : pObjects[j].GetType() == typeof(string) ? NpgsqlDbType.Varchar : pObjects[j].GetType() == typeof(int) ? NpgsqlDbType.Integer : pObjects[j].GetType() == typeof(byte[]) ? NpgsqlDbType.Bytea : NpgsqlDbType.Varchar;
dbTYpe = pObjects[j] == null ? DbType.Int32 : pObjects[j].GetType() == typeof(DateTime) ? DbType.DateTime : pObjects[j].GetType() == typeof(string) ? DbType.String : pObjects[j].GetType() == typeof(int) ? DbType.Int32 : pObjects[j].GetType() == typeof(byte[]) ? DbType.Binary : DbType.String;
value = pObjects[j] ?? DBNull.Value;
}
else
{
continue;
}
NpgsqlParameter p = new NpgsqlParameter();
p.ParameterName = pName;
p.Value = value ?? DBNull.Value;
p.NpgsqlDbType = npgType;
p.DbType = dbTYpe;
command.Parameters.Add(p);
j++;
}
}
}
#endregion
}
#endif
////////////////////////////
Errorlogger.cs
/////////////////////////////
#define MSSQL
//#define NPGQL
using System;
using System.Windows.Forms;
#if MSSQL
using System.Data.SqlClient;
#endif
#if NPGQL
using Npgsql;
#endif
public static class ErrorLogger
{
#if MSSQL
public static void Log(SqlConnection connection, Exception exception)
{
MessageBox.Show(exception.Message+exception.StackTrace);
}
public static void Log(SqlCommand command, Exception exception)
{
MessageBox.Show(exception.Message + exception.StackTrace + "nAtn" + command.CommandText);
}
public static void Log(SqlTransaction transaction, Exception exception)
{
MessageBox.Show(exception.Message + exception.StackTrace);
}
#endif
#if NPGQL
public static void Log(NpgsqlTransaction transaction, Exception exception)
{
MessageBox.Show(exception.Message);
}
public static void Log(NpgsqlCommand command, Exception exception)
{
MessageBox.Show(exception.Message);
}
public static void Log(NpgsqlConnection connection, Exception exception)
{
MessageBox.Show(exception.Message);
}
#endif
}
[/code]
<br/>
View the full article