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); }