Posts Tagged DataGridView

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.

Steps

  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.
    OracleFolder2
  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:
    try
    {
        using ( OracleConnection connection = new OracleConnection( GenerateConnectionString() ) )
        {
            connection.Open();
            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

Share

Tags: , ,

C# Linq How To Load a Winform Control Using Dynamic Linq entitites (Or any other Control)

This has been requested in the forums, for most examples in the Linq world are of console applications. One can use Linq to dynamically load winform (or asp.net) controls very easily with the dynamic entities which are created on the fly thanks to Linq. This article shows how to load a gridview control, or any other, with dynamic items of linq.

Steps

  1. Create a winform project/solution.
  2. Drag two controls to the surface of the form datagridview and bindingsource. We will accept the default names of dataGridView1 and bindingSource1.
  3. For demonstration purposes of this article we will create a struct for the simulated data which the Linq-to-Object operation will query off of:
    struct Data
    {
        public string Name { get; set; }
        public string Operation { get; set; }
        public string Description { get; set; }
        public DateTime DateStart { get; set; }
        public DateTime DateEnd { get; set; }
    
    }

    Note if you don’t create a new file for the above struct, place it below the Form1 partial class. Placing it above will screw up the design view when you try to edit.

  4. Here is the code to load the GridView using Linq which can be called from the constructor of the form after the InitializeCoponent call.
    BindingSource bindingSource1= new BindingSource();
    private void LoadGrid()
    {
        List<Data> dataListing = new List<Data>()
        {
            new Data() { Name = "Jabberwocky", Operation="Read", DateStart= DateTime.Now.AddDays(-2), DateEnd = DateTime.Now.AddDays(-2), Description="Process Started No errors"},
            new Data() { Name = "Space", Operation="Write", DateStart= DateTime.Now.AddDays(-2), DateEnd = DateTime.Now.AddDays(-1), Description="Final process remote allocation of 3000 items to main buffer."},
            new Data() { Name = "Stock Purchase", Operation="DataWarehousing", DateStart= DateTime.Now, DateEnd = DateTime.Now, Description="Shared data transport."}
        };
    
        var items = from dta in dataListing
            select new
            {
               OperationName = dta.Name,
               Start         = dta.DateStart.ToShortDateString(),
               End           = dta.DateEnd.ToShortDateString(),
               Operation     = dta.Operation,
               Description   = dta.Description
             };
    
        bindingSource1.DataSource = items;
        dataGridView1.DataSource  = bindingSource1;
    
        // Grid attributes
        dataGridView1.BorderStyle         = BorderStyle.Fixed3D;
        dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
    
    }

Result

GridView

Share

Tags: , , ,