Archive for the ‘Database’ Category.

Entity Framework Cascading Deletes; Set it from the database.

To achieve cascading deletes, one must specify the cascading deletes on the FK relationships from the top level table in the database. The default is not to cascade.

Here is the visual Process in SQL Server Management Studio.

  1. Select the top level table which will handle the delete and right click.
  2. Select design mode.
  3. Right click any row in the design mode.
  4. Select Relationships.
  5. Find all the FK relationships and set them to cascade.

Then in Entity Framework update the edmx file after these changes are made so entity framework knows about the cascading constraint.  Once all this is done a cascaded delete is possible using Entity Framework.




C#: Reflect and Load Entities From a DataSet using a Generic Method and Attributed Entities

iStock_000011856107XSmall If one is not using Linq-To-Sql and the entities are not being generated and loaded, one has to create their own entities and manually load the data into the it. Sure the process for doing one or two entities is ok when done by hand; but it becomes very repetitive grunt work for two or more. This article shows how to decorate an entity with attributes and then using reflection in a generic method load the data from a dataset in C#. This can be done with any version of .Net from 3.5 or greater.


To make this magic happen we need to decorate our entity with a C# attribute which describes the target column name in the database. Thanks to the System.Xml.Serialization namespace we can use the XmlElement attribute. It has an attribute within itself the ElementName attribute which is an excellent vehicle for what we need to do.

In the class below we have two strings, a DateTime and a decimal. Using the XmlElement we define what all of the target database column names are for each individual mapping of the property to hold the data.

public class Site

    [XmlElement(ElementName = "ID")]
    public decimal ID { get; set; }

    [XmlElement(ElementName = "Site_Code")]
    public string Code { get; set; }

    public string Description { get; set; }

    [XmlElement(ElementName = "REQ_PROP_ORIG")]
    public DateTime? Origination { get; set; }


Here are the usings if you don’t want to do <CTRL><.> on each error in the file to resolve the namespace for the next sections code.

using System.Reflection;
using System.Xml.Serialization;
using System.ComponentModel;
using System.Globalization;
Generic Method and Reflection

Here is the generic method in a static class. In the next section I describe what is going on when this class is called with a data row and the target entity instance is returned. (See the section Example Usage below to see it in action.)

public static class EntityHelper

    public static TEntity CreateEntityFromDataRow<TEntity>(DataRow drData)
        where TEntity : class, new()

        Attribute aTargetAttribute;
        Type tColumnDataType;

        TEntity targetClass = new TEntity();

        Type targetType = targetClass.GetType(); // The target object's type

        PropertyInfo[] properties = targetType.GetProperties(BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.Public);

        // Enumerate the properties and each property which has the XmlElementAttribute defined match the column name
        // and set the new objects value..
        foreach (PropertyInfo piTargetProperty in properties)
            aTargetAttribute = Attribute.GetCustomAttribute(piTargetProperty, typeof(XmlElementAttribute));

            if (aTargetAttribute != null)
                    foreach (DataColumn column in drData.Table.Columns)

                        if (((XmlElementAttribute)aTargetAttribute).ElementName.ToUpper() == column.ColumnName.ToUpper())

                            if (drData[column.ToString()] != DBNull.Value) // Only pull over actual values
                                tColumnDataType = drData[column.ToString()].GetType();

                                // Is the data in the database  a string format and do we
                                // want a DateTime? Do the below checks and if so covert to datetime.
                                if ((tColumnDataType != null) &&
                                    (tColumnDataType == typeof(System.String)) &&
                                    (piTargetProperty.PropertyType.IsGenericType) &&
                                    (piTargetProperty.PropertyType.GetGenericTypeDefinition().Equals(typeof(Nullable<>))) &&
                                    ((new NullableConverter(piTargetProperty.PropertyType)).UnderlyingType == typeof(System.DateTime)))
                                    // The below pattern dd-MMM-YY is for an Oracle date target. You may need to change this depending
                                    // on the database being used.
                                    DateTime dt = DateTime.ParseExact(drData[column.ToString()].ToString(), "dd-MMM-YY", CultureInfo.CurrentCulture);
                                    piTargetProperty.SetValue(targetClass, dt, null);
                                else // Set the value which matches the property type.
                                    piTargetProperty.SetValue(targetClass, drData[column.ToString()], null);
                            break; // Column name and data associated, no need to look at the rest of the columns.
                catch (Exception ex)
                    throw new ApplicationException(string.Format("Load Failure of the Attribute ({0}) for {1}. Exception:{2}",
                        ((XmlElementAttribute)aTargetAttribute).ElementName, targetType.Name, ex.Message));

        return targetClass;
  • Line 4: The generic method is defined to accept a generic type definition (the entity we are working with) but accept a DataRow as the actual data and return the generic type as a valid instance.
  • Line 5: This line requires that the generic type is a class and can be new’ed up. New because we will creating new entity instance at run time. See line 10.
  • Line 11: Simply new up a new entity to return.
  • Line 13/15: We begin to examine the generic type which will lead to the discovery of the properties we are looking at. Note we want all properties regardless of their access level. You can specify just public or private by removing the flags setup on line 14. (For more on this see my blog article .Net Reflection 101: Accessing The String Properties of an Active Object).
  • Line 19: We will enumerate upon the reflected properties of the type and match by reflected Element Name to Column Name.
  • Line 21: Here we reflect and get the custom attributes of the type for usage later.
  • Line 27: Now we begin to find the associated column by enumerating over each column found in the row.
  • Line 30: Does the reflected name match the current column name?
  • Line 35: What is the reported data type from the current row column? This is usede in the if of 39-43.
  • Line 39-43: Some databases return a string value for specific dates/times. If that is the case and we are looking at a string held date value do specialized processing to extract the date/time. If your database returns a DateTime object you can remove this if check.
  • Line 51: Assign the data in the column to the instances property via reflection in SetValue.
Example Usage
List<Site> SiteList = new List<Site>();

DataSet ds = new DataSet();

{Data Adaptor}.Fill( ds ); // Fill the dataset from a data adapter. 

// Convert the data in the dataset into a list of the target types.
foreach (DataRow dr in ds.Tables[0].Rows)

C#: Connect To Oracle Database With No Oracle Client Install Needed (Winform DataGridView Loading Example)

Oracle_smallUpdate 1/14/2014 : Updated Oracle Instant Client Link

This article demonstrates in a step by step fashion the easiest, and frankly fastest way to connect to an Oracle database using C#. The goal is to not have to install the huge Oracle Client either on the development machine nor the target machine the code will run on. This example creates a winform and inserts the content into a DataGridView for quick viewing. The code below is base on .Net 3.5.


  1. Oracle has an Oracle Database Instant Client (download link)which is a set of Dlls which can be XCopy installed onto the development and target PC to allow Oracle database access without installing the full Oracle client. In future steps we will include those target Dlls to be copied to the target output folder with the executable. Download the appropriate package and add Dlls to a folder of your choice on the PC.
  2. In Visual Studio create a Winform Project. From the Solution Explorer and within the project create a subfolder named Oracle Dlls. Update: This step should not be done, for the dlls will end up being copied into a subfolder of the same name and when running the client an error may come up stating “System.Data.OracleClient requires Oracle client software
    version 8.1.7 or greater”.
  3. Add the reference to the project of System.Data.OracleClient to the project..
  4. In Studio again select the folder created in step 2 (highlight the project root) and from the right click menu select Add->Existing Item, and insert all the top level Oracle Dlls from step one into the directory into the folder project. Note the below picture shows into a folder, they do not go into a folder but at the root of the project.
  5. Highlight all the inserted DLLs and select Properties to bring up the properties window. Change the Build Action to be Content and the Copy To Output Directory to be Copy If Newer. This allows the dlls to reside with the created executable program. By doing this it allows the program to run on another computer, as well as this one, that does not have the Oracle Client installed because all the Oracle specific dlls reside with the output executable.
  6. On the design view of the form add a button, label, binding source and a DataGridView. The names used for each in the example (Label as lbState, Binding Source as bsOracle and DataGridView as gvOracle).
  7. Create in the forms code a method to handle the connection string and add the target Oracle db/instance items (Note: Replace { xxx } including the curly braces the specifics to your db) :
    private string GenerateConnectionString()
       return "Data Source=( DESCRIPTION = ( ADDRESS_LIST = ( ADDRESS = ( PROTOCOL = TCP )( HOST = {Insert Host Here} )( PORT = {Insert Port Here} ) ) )( CONNECT_DATA = ( SERVER = DEDICATED )( SERVICE_NAME = {Service Name Here } ) ) ); User Id= {DB ID Here}; Password = {Password Here};";
  8. In the button’s onclick event wire up the controls and access the Oracle database as such:
        using ( OracleConnection connection = new OracleConnection( GenerateConnectionString() ) )
            lblState.Text = connection.State.ToString();
            OracleCommand oc = connection.CreateCommand();
            oc.CommandText = "SELECT * FROM {Your Table Here}";
            OracleDataReader reader = oc.ExecuteReader();
            bsOracle.DataSource = reader;
            gvOracle.DataSource = bsOracle;
            gvOracle.BorderStyle = BorderStyle.Fixed3D;
            gvOracle.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
    catch ( Exception ex )
    //  MessageBox.Show( "Exception: " + ex.Message );
        lblState.Text = ex.Message;
  9. Run and compile the program and if all goes well it should attach…but there are other possible failure points see the next section as to a couple of them.

Note for a more involved explanation of this process check out the article Instant Oracle Using C# which has a console example and does not provide the advice of using the build options in Studio for the Oracle Dlls. That will be our little secret. HTH

Possible Errors Encountered

System.Data.OracleClient requires Oracle client software version 8.1.7 or greater

If this is encountered it could be a permission problem accessing the location where the oracle dlls are…but most likely the dlls are not in the same directory as the executable or found within the environment path of the system.

ORA-12541: TNS:no listener


This one could mean that one of the connection settings is incorrect and the database could not be connected to and this generic error comes back. Try tweaking the settings, port, instance