Search This Blog

Wednesday, February 26, 2014

Read XMLTYPE Oracle C#

C# > Oracle.DataAccess > Read XMLTYPE 

An OracleXmlType object represents an Oracle XMLType instance.

Example:


OracleConnection con = new OracleConnection() ;
con.ConnectionString = "Data Source=sid; User ID=xx; Password=xx; enlist=false; ";
con.Open();
OracleCommand xmlCmd = new OracleCommand();
xmlCmd.CommandText = "select xml from table ";
xmlCmd.Connection = con;
OracleDataReader poReader = xmlCmd.ExecuteReader();
OracleXmlType poXml;
while (poReader.Read())
{
     poXml = poReader.GetOracleXmlType(0);    
}

con.Close();




Tuesday, February 25, 2014

c# GuidAttribute Example

C# > InteropServices >  GuidAttribute 

Supplies an explicit Guid when an automatic GUID is undesirable.

When you write an application to be used as COM you have 
to give a unique name. In this case you need to apply GUID attribute.

Example

Class that is exported to COM with a fixed GUID


[GuidAttribute("bd2d60fa-8e40-4d06-ad6f-8760983efe33")]
public class ExposedToComClass
{
          
}






Friday, February 21, 2014

Get all services C#

C# > ServiceProcess > Service Controller

It is a Windows service that allows you to connect to a service.
You can manipulate it or get information about it.

Example:
Get list of all services

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.ServiceProcess;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace WindowsFormsApplication1
{
    public class clsService
    {
        public string Name { get; set; }
        public string Type { get; set; }
        public string Status { get; set; }
    }
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            ServiceController[] scServices;
            scServices = ServiceController.GetServices();
            List<clsService> lcs = new List<clsService>();
            foreach (ServiceController sc in scServices)
            {
                clsService cs = new clsService();
                cs.Name = sc.ServiceName;
                cs.Type = sc.ServiceType.ToString();
                cs.Status = sc.Status.ToString();
               
                lcs.Add(cs);
            }
            dgv.DataSource =lcs ;
        }
    }
}





Linq to SQL equivalent to TOP

C# > LINQ > Take

Take returns the first specified number of elements.

Example
Linq to SQL equivalent to TOP

var query = from q in dc.Queries.OrderBy(f => f.name).Take(10)






Thursday, February 20, 2014

Oracle DBMS_SCHEDULER

Oracle >  SYS.DBMS_SCHEDULER 

The DBMS_SCHEDULER package provides a collection of scheduling functions and procedures.







Wednesday, February 19, 2014

Oracle create job example

Oracle SYS.DBMS_SCHEDULER > CREATE_JOB

The CREATE_JOB procedure creates a single job. You can create multiple jobs using the CREATE_JOBS procedure.


Example:

Oracle create job example


BEGIN
    SYS.DBMS_SCHEDULER.CREATE_JOB (
            job_name => '"db"."job"',
            job_type => 'PLSQL_BLOCK',
            job_action => 'delete from item',
            number_of_arguments => 0,
            start_date => TO_TIMESTAMP('19-FEB-14 04.51.50.316000000 PM', 'DD-MON-RR HH.MI.SS.FF AM'),
            repeat_interval => 'FREQ=DAILY;BYHOUR=3',
            end_date => NULL,
            job_class => 'DEFAULT_JOB_CLASS',
            enabled => false,
            auto_drop => true,
            comments => NULL);


END;






sys.schemas SQL Server example

SQL Server > Catalog Views > sys.schemas

Gets information about database schema.

Example:

select * from sys.schemas

Result






Tuesday, February 18, 2014

Timestamp to date Oracle

Oracle > Timestamp > To Date

select * from your_table
where
TO_DATE (TO_CHAR (timestamp_date, 'YYYY-MON-DD'),'YYYY-MON-DD') = '18-FEB-2014'
 











Thursday, February 13, 2014

sys.data_spaces SQL Server

SQL Server > Catalog Views > sys.data_spaces

Return info for each data space.

Example

select * from sys.data_spaces






Add Image Column to Telerik Radgrid

Telerik > RadGridView > GridViewImageColumn

GridViewImageColumn displays read-only images for database columns of image data





GridViewImageColumn imageColumn = new GridViewImageColumn();
imageColumn.Name = "imgCol";
imageColumn.FieldName = "dbPhoto";
imageColumn.HeaderText = "Picture";
imageColumn.ImageLayout = ImageLayout.Zoom; // Zoom - Image is zoomed but the aspect ratio is preserved:
radGridView1.MasterTemplate.Columns.Insert(3, imageColumn);




Using Joins SQL Server Examples

SQL SERVER > QUERY > JOIN

Using joins you can retrieve data from two or more tables based on logical relationships between the tables.

create table #category(name varchar(20), description  varchar(20))

insert into #category(name,description)
values('cat 1','cat 1 description')
insert into #category(name,description)
values('cat 2','cat 2 description')
insert into #category(name,description)
values('cat 3','cat 3 description')

create table #product(id int, name varchar(20), category varchar(20))

insert into #product(id,name,category)
values(1,'product 1','cat 1')
insert into #product(id,name,category)
values(2,'product 2','cat 2')
insert into #product(id,name,category)
values(2,'product 3',null)






INNER JOIN  returns all the columns in both tables, and returns only the rows for which there is an equal value in the join column. 

select p.id, p.name, c.description as category
from #product p
inner join #category c
       on p.category = c.name


LEFT OUTER JOIN includes all rows in the a table  whether or not there is a match on the another table.

select p.id, p.name, c.description as category
from #product p
left  join #category c
       on p.category = c.name







RIGHT OUTER JOIN indicates all rows in the second table are to be included in the results, regardless of whether there is matching data in the first table.


select p.id, p.name, c.description as category
from #product p
right  join #category c
       on p.category = c.name




FULL OUTER JOIN includes all rows from both tables, regardless of whether or not the other table has a matching value.
select p.id, p.name, c.description as category
from #product p
full  join #category c
       on p.category = c.name







CROSS JOIN produces the Cartesian product of the tables involved in the join.
 
select p.id, p.name, c.description as category
from #product p
cross join #category c

      


drop table #product
drop table #category