protected int BaseTransaction(DbTransaction transaction)
{
try
{
connection = DBConnectionFactory.Instance(contextName);
DBConnectionOperation.ConnectionOpen(connection);
command = connection.CreateCommand();
command.Transaction = transaction;
command.CommandText = runQuery;
if (parameterList != null && parameterList.Count > 0)
{
foreach (var loopParameter in parameterList)
{
DbParameter parameter = command.CreateParameter();
parameter.ParameterName = loopParameter.Key.ToString();
parameter.Value = loopParameter.Value;
command.Parameters.Add(parameter);
}
}
return(command.ExecuteNonQuery());
}
catch (Exception ex)
{
DBConnectionOperation.ConnectionClose(connection);
throw ex;
}
}
public DbDataReader ExecutePureSQL(string query, Dictionary <string, object> parameters = null)
{
try
{
using (connection = DBConnectionFactory.Instance(contextName))
{
DBConnectionOperation.ConnectionOpen(connection);
command = connection.CreateCommand();
command.CommandText = query;
if (parameters != null)
{
foreach (var loopParameter in parameters)
{
DbParameter parameter = command.CreateParameter();
parameter.ParameterName = loopParameter.Key.ToString();
parameter.Value = loopParameter.Value;
command.Parameters.Add(parameter);
}
}
return(command.ExecuteReader());
}
}
catch (Exception ex)
{
DBConnectionOperation.ConnectionClose(connection);
throw ex;
}
}
public int LatestVersionDatabase()
{
int version = 0;
try
{
using (connection = DBConnectionFactory.Instance(this.ContextName))
{
DBConnectionOperation.ConnectionOpen(connection);
MySqlCommand command = new MySqlCommand($"SELECT Version FROM __WORM__Configuration ORDER BY Version DESC LIMIT 1", (MySqlConnection)connection);
DbDataReader reader = command.ExecuteReader();
while (reader.Read())
{
version = reader.GetInt32(0);
}
reader.Close();
}
}
catch (Exception ex)
{
DBConnectionOperation.ConnectionClose(connection);
throw ex;
}
return(version);
}
public string ColumnInformationFromDB(string tableName, string columnName)
{
string columnInformation = string.Empty;
try
{
using (connection = DBConnectionFactory.Instance(this.ContextName))
{
DBConnectionOperation.ConnectionOpen(connection);
MySqlCommand command = new MySqlCommand($"SELECT DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS " +
$"WHERE TABLE_NAME = @TableName AND COLUMN_NAME = @ColumnName", (MySqlConnection)connection);
command.Parameters.AddWithValue("@TableName", tableName);
command.Parameters.AddWithValue("@ColumnName", columnName);
DbDataReader reader = command.ExecuteReader();
while (reader.Read())
{
columnInformation = $"{reader.GetString(0)}{(reader.IsDBNull(1) ? "" : $"({reader.GetString(1)})")} {(reader.GetString(2) == "YES" ? "NULL" : "NOT NULL")} ";
}
reader.Close();
}
}
catch (Exception ex)
{
DBConnectionOperation.ConnectionClose(connection);
throw ex;
}
return(columnInformation);
}
public bool IsTableHasPrimaryKey(string schemaName, string tableName)
{
bool tablehasPrimaryKey = false;
try
{
using (connection = DBConnectionFactory.Instance(this.contextName))
{
DBConnectionOperation.ConnectionOpen(connection);
MySqlCommand command = new MySqlCommand($"SELECT CASE WHEN EXISTS((SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = @TableName " +
"AND TABLE_SCHEMA = @SchemaName" +
"AND CONSTRAINT_TYPE = 'PRIMARY KEY')) THEN 1 ELSE 0 END",
(MySqlConnection)connection);
command.Parameters.AddWithValue("@SchemaName", schemaName);
command.Parameters.AddWithValue("@TableName", tableName);
tablehasPrimaryKey = (int)command.ExecuteScalar() == 1 ? true : false;
}
}
catch (Exception ex)
{
DBConnectionOperation.ConnectionClose(connection);
throw ex;
}
return(tablehasPrimaryKey);
}
// <summary>
/// Tablo ve Sütun adı verilerek ilgili Constraint ismini döner
/// </summary>
/// <param name="schemaName">Tablo Şema Adı</param>
/// <param name="tableName">Tablo Adı</param>
/// <param name="columnName">Sütun Adı</param>
/// <returns></returns>
public Tuple <string, string> ConstraintNameByTableAndColumnName(string tableName, string columnName, string schemaName = "")
{
string constraintName = string.Empty;
string constraintType = string.Empty;
try
{
using (connection = DBConnectionFactory.Instance(this.ContextName))
{
DBConnectionOperation.ConnectionOpen(connection);
MySqlCommand command = new MySqlCommand($"SELECT Col.COLUMN_NAME,Col.CONSTRAINT_NAME,Tbl.CONSTRAINT_TYPE " +
$"FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tbl,INFORMATION_SCHEMA.KEY_COLUMN_USAGE Col " +
$"WHERE Col.CONSTRAINT_NAME = Tbl.CONSTRAINT_NAME " +
$"AND Col.TABLE_NAME = Tbl.TABLE_NAME " +
$"AND Col.TABLE_NAME = @TableName " +
$"AND Col.COLUMN_NAME = @ColumnName", (MySqlConnection)connection);
command.Parameters.AddWithValue("@TableName", tableName);
command.Parameters.AddWithValue("@ColumnName", columnName);
DbDataReader reader = command.ExecuteReader();
while (reader.Read())
{
constraintName = reader.GetString(1);
constraintType = reader.GetString(2);
}
reader.Close();
}
}
catch (Exception ex)
{
DBConnectionOperation.ConnectionClose(connection);
throw ex;
}
return(Tuple.Create(constraintName, constraintType));
}
/// <summary>
/// Tablo üzerindeki sütunların isimlerini listeler
/// </summary>
/// <param name="tableName">Sütunları listelenecek tablo adı</param>
/// <returns></returns>
public List <string> ColumnListOnTable(string tableName)
{
List <string> columnList = new List <string>();
try
{
using (connection = DBConnectionFactory.Instance(this.ContextName))
{
DBConnectionOperation.ConnectionOpen(connection);
MySqlCommand command = new MySqlCommand($"SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName", (MySqlConnection)connection);
command.Parameters.AddWithValue("@TableName", tableName);
DbDataReader reader = command.ExecuteReader();
while (reader.Read())
{
columnList.Add(reader.GetString(3));
}
reader.Close();
}
}
catch (Exception)
{
DBConnectionOperation.ConnectionClose(connection);
}
return(columnList);
}
/// <summary>
/// Veritabanı üzerindeki tabloları isim ve şema birlikte listeler
/// </summary>
/// <returns></returns>
public List <DBTableModel> TableListOnDB()
{
List <DBTableModel> tableList = new List <DBTableModel>();
try
{
using (connection = DBConnectionFactory.Instance(this.contextName))
{
DBConnectionOperation.ConnectionOpen(connection);
DataTable tables = connection.GetSchema("Tables");
foreach (DataRow table in tables.Rows)
{
DBTableModel dBTableModel = new DBTableModel
{
TableName = table[2].ToString()
};
tableList.Add(dBTableModel);
}
}
}
catch (Exception)
{
DBConnectionOperation.ConnectionClose(connection);
}
return(tableList);
}
/// <summary>
/// TR : CRUD işlemlerinden sonra çalıştırılacak method
/// EN :
/// </summary>
/// <returns></returns>
/// <summary>
public virtual int PushToDB()
{
try
{
using (connection = DBConnectionFactory.Instance(contextName))
{
DBConnectionOperation.ConnectionOpen(connection);
command = connection.CreateCommand();
command.CommandText = runVersionQuery;
//Development ortamı isteyen geliştiriciler override edip bu satırları aktif hale getirecekler
//if (DBConnectionFactory.LatestDatabaseVersionFromXML(contextName) != Convert.ToInt32(command.ExecuteScalar()))
// throw new Exception("Veritabanı üzerinde bir güncelleme olduğu için bu işlem gerçekleştirilemez.Lütfen öncelikle Veritabanı versiyonunuzu eşitleyiniz.");
command.CommandText = runQuery;
if (parameterList != null && parameterList.Count > 0)
{
foreach (var loopParameter in parameterList)
{
DbParameter parameter = command.CreateParameter();
parameter.ParameterName = loopParameter.Key.ToString();
parameter.Value = loopParameter.Value;
command.Parameters.Add(parameter);
}
}
return(command.ExecuteNonQuery());
}
}
catch (Exception ex)
{
DBConnectionOperation.ConnectionClose(connection);
throw ex;
}
}
public bool ContextGenerateFromDB(int dbVersion, string contextPath = "", string namespaceName = "", string contextName = "")
{
if (string.IsNullOrEmpty(contextPath))
{
contextPath = projectPath + "\\WORM_Context\\";
}
if (string.IsNullOrEmpty(contextName))
{
contextName = "WORM_Context";
}
bool dbCreatedSuccess = true;
try
{
using (connection = new NpgsqlConnection(this.connectionString))
{
DBConnectionOperation.ConnectionOpen(connection);
NpgsqlCommand command = new NpgsqlCommand($"SELECT * FROM [dbo].[__WORM__Configuration] WHERE Version=@Version", (NpgsqlConnection)connection);
command.Parameters.AddWithValue("@Version", dbVersion);
DbDataReader reader = command.ExecuteReader();
while (reader.Read())
{
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.LoadXml(reader.GetString(3));
XmlNodeList xmlTableForm = xmlDoc.GetElementsByTagName("Classes");
ContextGenerate contextGenerate = new ContextGenerate();
foreach (XmlNode pocoClasses in xmlTableForm)
{
foreach (XmlNode pocoProperty in pocoClasses.ChildNodes)
{
contextGenerate.CreateContextEntity(pocoProperty.Name, contextName);
foreach (XmlNode pocoColumn in pocoProperty.ChildNodes)
{
List <string> customAttributes = new List <string>();
foreach (XmlAttribute pocoColumnAttribute in pocoColumn.Attributes)
{
customAttributes.Add(pocoColumnAttribute.Value);
}
contextGenerate.AddProperties(pocoColumn.Name, new POSTGRESQL_To_CSHARP().XML_To_CSHARP(pocoColumn.Attributes.GetNamedItem("type").Value), customAttributes);
}
contextGenerate.GenerateCSharpCode(contextPath, $"{pocoProperty.Name}.cs");
}
}
}
reader.Close();
}
}
catch (Exception ex)
{
dbCreatedSuccess = false;
throw ex;
}
DBConnectionOperation.ConnectionClose(connection);
return(dbCreatedSuccess);
}
public Tuple <bool, int> CreateORAlterDatabaseAndTables(string tablesXMLForm, string createTableSQLQuery)
{
bool dbCreatedSuccess = true;
int version = 0;
try
{
using (connection = DBConnectionFactory.CreateDatabaseInstance(this.contextName))
{
DBConnectionOperation.ConnectionOpen(connection);
#region Veritabanı var olup olmadığı konrtrol edilerek oluşturulur
MySqlCommand command = new MySqlCommand(CreateDatabaseQuery(), (MySqlConnection)connection);
command.ExecuteNonQuery();
#endregion
#region Oluşturulan veritabanına geçiş yapılır
connection.ChangeDatabase(this.contextName.ToLower());
#endregion
#region __WORM__Configuration Tablosu var olup olmadığı kontrol edilerek oluşturulur
Tuple <string, MySqlCommand> worm_Table = Create__WORM__Configuration_Table(tablesXMLForm);
command = new MySqlCommand(worm_Table.Item1, (MySqlConnection)connection);
command.ExecuteNonQuery();
command = worm_Table.Item2;
command.ExecuteNonQuery();
#endregion
#region __WORM__Configuration tablosundan veritabanı son versiyonu çekilir
command = new MySqlCommand($"SELECT Version FROM __worm__configuration ORDER BY Version DESC LIMIT 1", (MySqlConnection)connection);
DbDataReader reader = command.ExecuteReader();
while (reader.Read())
{
version = reader.GetInt32(0);
}
reader.Close();
#endregion
#region Tablolar oluşturulur
command = new MySqlCommand(createTableSQLQuery, (MySqlConnection)connection);
command.ExecuteNonQuery();
#endregion
}
}
catch (Exception ex)
{
DBConnectionOperation.ConnectionClose(connection);
throw ex;
}
return(Tuple.Create(dbCreatedSuccess, version));
}
public void TransactionCommit(DbTransaction transaction)
{
try
{
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
DBConnectionOperation.ConnectionClose(connection);
throw new Exception("Transaction çalıştırılırken hata oluştu!.RollBack çalıştırıldı.", ex);
}
}
public DbTransaction BeginTransaction()
{
try
{
connection = DBConnectionFactory.Instance(contextName);
DBConnectionOperation.ConnectionOpen(connection);
return(connection.BeginTransaction());
}
catch (Exception ex)
{
DBConnectionOperation.ConnectionClose(connection);
throw ex;
}
}
public bool CreateORAlterDatabaseAndTables(string tablesXMLForm, string createTableSQLQuery)
{
bool dbCreatedSuccess = true;
try
{
using (connection = new NpgsqlConnection(this.connectionString))
{
DBConnectionOperation.ConnectionOpen(connection);
#region Veritabanı var olup olmadığı konrtrol edilerek oluşturulur
NpgsqlCommand command = new NpgsqlCommand(CreateDatabaseQuery(), (NpgsqlConnection)connection);
command.ExecuteNonQuery();
#endregion
#region Oluşturulan veritabanına geçiş yapılır
connection.ChangeDatabase(this.contextName);
#endregion
#region __WORM__Configuration Tablosu var olup olmadığı kontrol edilerek oluşturulur
Tuple <string, NpgsqlCommand> worm_Table = Create__WORM__Configuration_Table(tablesXMLForm);
command = new NpgsqlCommand(worm_Table.Item1, (NpgsqlConnection)connection);
command.ExecuteNonQuery();
command = worm_Table.Item2;
command.ExecuteNonQuery();
#endregion
#region Tablolar oluşturulur
command = new NpgsqlCommand(createTableSQLQuery, (NpgsqlConnection)connection);
command.ExecuteNonQuery();
#endregion
}
}
catch (Exception ex)
{
DBConnectionOperation.ConnectionClose(connection);
dbCreatedSuccess = false;
throw ex;
}
return(dbCreatedSuccess);
}
/// <summary>
/// Veritabanın var olup olmadığı kontrolünü sağlar
/// </summary>
/// <returns></returns>
public bool DatabaseExistControl()
{
bool dbExist = false;
try
{
using (connection = DBConnectionFactory.Instance(this.contextName))
{
DBConnectionOperation.ConnectionOpen(connection);
MySqlCommand command = new MySqlCommand($"SELECT CASE WHEN EXISTS((SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = @DbName)) THEN 1 ELSE 0 END",
(MySqlConnection)connection);
command.Parameters.AddWithValue("@DbName", this.contextName);
dbExist = (int)command.ExecuteScalar() == 1 ? true : false;
}
}
catch (Exception ex)
{
DBConnectionOperation.ConnectionClose(connection);
throw ex;
}
return(dbExist);
}
/// <summary>
/// TR : Verilen Entity Class(POCO) yani Tabloya ait tüm kayıtları listelemek için kullanılır
/// EN :
/// </summary>
/// <typeparam name="TEntity">TR : Entity Class(POCO)/Tablo EN : Entity Class(POCO)/Table </typeparam>
/// <returns></returns>
protected List <TEntity> GetListFromDB <TEntity>()
{
List <TEntity> entities = new List <TEntity>();
try
{
using (connection = DBConnectionFactory.Instance(contextName))
{
DBConnectionOperation.ConnectionOpen(connection);
command = connection.CreateCommand();
command.CommandText = runQuery;
DbDataReader reader = command.ExecuteReader();
while (reader.Read())
{
TEntity entity = Activator.CreateInstance <TEntity>();
foreach (PropertyInfo property in typeof(TEntity).GetProperties())
{
var propertyValue = reader[property.Name];
if (propertyValue == DBNull.Value)
{
propertyValue = null;
}
property.SetValue(entity, propertyValue);
}
entities.Add(entity);
}
reader.Close();
}
}
catch (Exception ex)
{
DBConnectionOperation.ConnectionClose(connection);
throw ex;
}
return(entities);
}
public bool ContextGenerateFromDB(int dbVersion, string contextPath = "", string namespaceName = "")
{
if (string.IsNullOrEmpty(contextPath))
{
contextPath = projectPath + "\\WORM_Context\\";
}
if (string.IsNullOrEmpty(namespaceName))
{
namespaceName = projectNamespace + ".WORM_Context";
}
bool dbCreatedSuccess = true;
List <string> POCOClasses = new List <string>();
try
{
using (connection = DBConnectionFactory.Instance(this.ContextName))
{
DBConnectionOperation.ConnectionOpen(connection);
MySqlCommand command = new MySqlCommand($"SELECT * FROM __WORM__Configuration WHERE Version=@Version", (MySqlConnection)connection);
command.Parameters.AddWithValue("@Version", dbVersion);
DbDataReader reader = command.ExecuteReader();
#region XML verisinden POCO Entity Class ları oluşturulur
while (reader.Read())
{
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.LoadXml(reader.GetString(3));
XmlNodeList xmlTableForm = xmlDoc.GetElementsByTagName("Classes");
ContextGenerate POCOClassGenerate = new ContextGenerate();
foreach (XmlNode pocoClasses in xmlTableForm)
{
foreach (XmlNode pocoClass in pocoClasses.ChildNodes)
{
List <string> pocoClasscustomAttributes = new List <string>();
foreach (XmlAttribute pocoClassAttribute in pocoClass.Attributes)
{
pocoClasscustomAttributes.Add(pocoClassAttribute.Value);
}
POCOClassGenerate.CreateContextEntity(pocoClass.Name, namespaceName + ".Entities", pocoClasscustomAttributes);
POCOClasses.Add(pocoClass.Name);
foreach (XmlNode pocoColumn in pocoClass.ChildNodes)
{
Dictionary <string, string> pocoColumncustomAttributes = new Dictionary <string, string>();
foreach (XmlAttribute pocoColumnAttribute in pocoColumn.Attributes)
{
pocoColumncustomAttributes.Add(pocoColumnAttribute.Name, pocoColumnAttribute.Value);
}
POCOClassGenerate.AddProperties(pocoColumn.Name, new MYSQL_To_CSHARP().XML_To_CSHARP(pocoColumn.Attributes.GetNamedItem("Type").Value), pocoColumncustomAttributes);
}
POCOClassGenerate.GenerateCSharpCode(contextPath + "Entities\\", $"{pocoClass.Name}.cs");
}
}
}
reader.Close();
#endregion
#region XML verisinden Context Class oluşturulur
// TODO : Otomatik oluşturulan Entity Class lar exclude halinde geliyor
ContextGenerate contextGenerate = new ContextGenerate();
contextGenerate.CreateContextEntity(contextName, namespaceName);
//foreach (string pocoClass in POCOClasses)
//{
// contextGenerate.AddProperties(pocoClass, "MYSQLProviderContext", contextName);
//}
contextGenerate.GenerateCSharpCode(contextPath, $"{contextName}.cs");
#endregion
}
}
catch (Exception ex)
{
DBConnectionOperation.ConnectionClose(connection);
dbCreatedSuccess = false;
throw ex;
}
return(dbCreatedSuccess);
}
