Ado.net - the Size property has an invalid size of 0

I'm trying to get output value from DB via ADO.net. There's a client code:

    using (var connection = new SqlConnection(ConnectionString))
    {
        connection.Open();
        SqlCommand command = new SqlCommand("pDoSomethingParamsRes", connection);
        command.CommandType = CommandType.StoredProcedure;
        command.Parameters.Add("@i", 1);
        var outParam = new SqlParameter("@out", SqlDbType.VarChar);
        outParam.Direction = ParameterDirection.Output;
        command.Parameters.Add(outParam);
        command.ExecuteNonQuery();
        Console.WriteLine(command.Parameters["@out"].Value.ToString());
    }

When I run this I get the following exception:

the Size property has an invalid size of 0

Solutions :-

VarChar and NVarChar are variable width character fields (thus var+char). You have to set the length, otherwise the default is zero.

outParam.Size= int.MaxValue

to define maximum value of integer.

Read More

Views

Views are known as logical tables. They represent the data of one of more tables. A view derives its data from the tables on which it is based. These tables are called base tables. Views can be based on actual tables or another view also.

Whatever DML operations you performed on a view they actually affect the base table of the view. You can treat views same as any other table. You can Query, Insert, Update and delete from views, just as any other table.

Views are very powerful and handy since they can be treated just like any other table but do not occupy the space of a table.

The following sections explain how to create, replace, and drop views using SQL commands.

Creating Views

Suppose we have EMP and DEPT table. To see the empno, ename, sal, deptno, department name and location we have to give a join query like this.

select e.empno,e.ename,e.sal,e.deptno,d.dname,d.loc

        From emp e, dept d where e.deptno=d.deptno;

So everytime we want to see emp details and department names where they are working we have to give a long join query. Instead of giving this join query again and again, we can create a view on these table by using a CREATE VIEW command given below

create view emp_det as select e.empno,

e.ename,e.sal,e.deptno,d.dname,d.loc

        from emp e, dept d where e.deptno=d.deptno;

Now to see the employee details and department names we don’t have to give a join query, we can just type the following simple query.

select * from emp_det;

This will show same result as you have type the long join query.  Now you can treat this EMP_DET view same as  any other table.

For example, suppose all the employee working in Department No. 10 belongs to accounts department and most of the time you deal with these people. So every time you  have to give a DML or Select statement you have to give a WHERE condition like .....WHERE DEPTNO=10. To avoid this, you can create a view as given below

CREATE VIEW accounts_staff AS

    SELECT Empno, Ename, Deptno

    FROM Emp

    WHERE Deptno = 10

    WITH CHECK OPTION CONSTRAINT ica_Accounts_cnst;

Now to see the account people you don’t have to give a query with where condition you can just type the following query.

Select * from accounts_staff;

Select sum(sal) from accounst_staff;

Select max(sal) from accounts_staff; 

As you can see how views make  things easier.

The query that defines the ACCOUNTS_STAFF view references only rows in department 10. Furthermore, WITH CHECK OPTION creates the view with the constraint that INSERT and UPDATEstatements issued against the view are not allowed to create or result in rows that the view cannot select.

Considering the example above, the following INSERT statement successfully inserts a row into the EMP table through the ACCOUNTS_STAFF view:

INSERT INTO Accounts_staff VALUES (110, 'ASHI', 10);

However, the following INSERT statement is rolled back and returns an error because it attempts to insert a row for department number 30, which could not be selected using theACCOUNTS_STAFF view:

INSERT INTO Accounts_staff VALUES (111, 'SAMI', 30);

Read More

Nth Highest Salary Query in Oracle 9i/10g/11g/11i

Problem: Show the list of all employees from the employee table having second highest Salary or Nth highest salary in the organization.

Solution: Well we can achieve the result by so many ways but in this post I would like to do it by using co-related queries.
Query to get the list of employees getting 2nd highest salary (Set the value of @SalaryPosition to get Nth highest salary):




SELECT * FROM Employee E1  WHERE @SalaryPosition = ( SELECT COUNT(DISTINCT E2.Salary) FROM Employee E2 WHERE E2.Salary >= E1.Salary )



Co-Related Query:  In short, it’s a type of nested sub query where the inner query is referenced to some value of the outer query. And the inner query is executed for each record in the outer query making it slowL.
     In our query, to get the 2nd highest salary, works in the same way too. Let’s dive a little deeper.

Sample data used in employee table:


slNo           empname               salary


101            Ram                      5000
102            Abhishek              7500
101            kumar                   5000
104            prasad                  6570
102            Jumla                    7500
101           Harkesh                12000
101           John                      4000

So we have taken two aliases for the employee table i.e. E1 and E2.(Here N=2)

Corollary: Let’s take the first record in the table E1 where the salary is 5000 and assume it as the second highest salary. But how will you know if it is actually the second highest salary or not. If 5000 is the second highest salary, then the distinct count of all the salaries from table E2 which are greater than or equal to 5000 must be 2.

The first record from E1 (salary = 5000) will be compared to all the records in E2 (salary).


Table E1                                         Table E2                                                  Distinct count where
(considering the first record only)   (distinct salary)                                           salary is >= 5000

5000                                               4000                                                                   4
                                                       5000
                                                       6570
                                                       7500
                                                      12000

Hence 5000 is not the highest salary as we have 4 more salaries greater than equal to 5000. But it is concluded that 5000 is at the fourth position.

     Now let’s take the second salary from table E1 and do the same comparison as in the earlier case.

The second salary is 7500 and as per rule the count of distinct salaries greater than equal to 7500 must be 2.

   Table E1                                         Table E2                                                Distinct count where
(considering the second record only)  (distinct salary)                                       salary is >= 5000


7500                                                  4000                                                                 2
                                                          5000
                                                         6570
                                                         7500
                                                         12000


Here in this case our condition in the where clause of outer query is satisfying.

Note:-Please comment and reply me.

Read More

A CYCLE sequence

 A CYCLE sequence

SQL> -- A CYCLE sequence
SQL>
SQL>
SQL> CREATE SEQUENCE StudentNumSeq
  2 INCREMENT BY 50000
  3 START WITH 50000
  4 MAXVALUE 99999
  5 NOCACHE
  6 CYCLE;

Sequence created.

SQL>
SQL>
SQL> select studentNumSeq.nextVal from dual;

  NEXTVAL
----------
  50000

SQL> select studentNumSeq.nextVal from dual;

  NEXTVAL
----------
  1

SQL> select studentNumSeq.nextVal from dual;

  NEXTVAL
----------
  50001

SQL>
SQL>
SQL> drop sequence studentNumSeq;



Note:-Please comment and reply me.

Read More

SQL Statements

SQL Statements

Oracle SQL complies with industry-accepted standards. Oracle Corporation ensures future
compliance with evolving standards by actively involving key personnel in SQL standards
committees. Industry-accepted committees are the American National Standards Institute (ANSI)and the International Standards Organization (ISO). Both ANSI and ISO have accepted SQL as the standard language for relational databases.

Statement Description

• SELECT Retrieves data from the database
  
• INSERT,UPDATE,DELETE,MERGE

Enters new rows, changes existing rows, and removes unwanted rows

from tables in the database, respectively. Collectively known as data

manipulation language (DML).

• CREATE,ALTER,DROP,RENAME,TRUNCATE

Sets up, changes, and removes data structures from tables. Collectively

known as data definition language (DDL).

• COMMIT,ROLLBACK,SAVEPOINT

Manages the changes made by DML statements. Changes to the data can

be grouped together into logical transactions.

• GRANT,REVOKE

Gives or removes access rights to both the Oracle database and the

structures within it. Collectively known as data control language

(DCL).

Read More