Dailycode.info

Short solution for short problems

MSSQL: convert datetime to time

I needed to show the time between a start and end datetime in one record in the DB. This could be done using the CONVERT function:

CONVERT(char(10),  EndTime - StartTime, 114) as TotalTime

Then the result looked like this:



Oracle: Concatinate columns in a group by function

I wanted to concatinate information of a column into a ; seperated column in a group by.

eg

Projectmanagerdepartmentcustomer
1MarkABDO
2PietBBDO
3StevenAMicrosoft
I want to count the number of project for each customer and see which managers and departments there are.

With a LISTAGG function this works

SELECT

count(Project) as nr, LISTAGG(manager, ' ') WITHIN GROUP (order by manager) as managers 

FROM table 

GROUP BY customer

ORDER BY customer


result will be

2            Mark;Piet            BDO

1            Steven                Microsoft


Drop tables procedure for .Net and MS SQL

I wrote a simple procedure that will drop tables in a database that have a certain prefix. 

First I initiate the process layer:

MESCockpitProcessLayer.DirectDataAccess pl = new MESCockpitProcessLayer.DirectDataAccess();

Then I will fill a dataset with the following statement:

//This script will get all tables with the selected prefix and generates a drop statement. I load it in a dataset, each drop statement

//is a row

string DropTableString = "SELECT 'DROP TABLE ' + TABLE_NAME + ' ' FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE 'GP_MES%'";

DataSet ds = pl.GetDataSet(new StringBuilder(DropTableString), serverName, DBName, DBUserName, DBUserPWD);

Next I simply go over each row in the dataset table and execute the statement:

if (ds != null)

{

    foreach (DataRow table in ds.Tables[0].Rows)

    {

        //Drop the table using the generated script

        pl.ExecuteQueryDirect(new StringBuilder(table[0].ToString()), serverName, DBName, DBUserName, DBUserPWD);

    }

}


The generated statement look like this:

DROP TABLE EventLog 

The data class that gets the data and executes the SQL statements is just the very simplest using the Data.SqlClient:

using System;

using System.Collections.Generic;

using System.Data;

using System.Data.SqlClient;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

 

namespace MESCockpitDataLayer.SqlClient

{

    publicclassMESDataAccess

    {

        publicstaticint ExecuteNonQuery(StringBuilder sb, string connectionString)

        {

            SqlConnection conn = null;

            SqlCommand executeCommand = null;

            //SqlTransaction MyTrans = null;

            try

            {

                conn = newSqlConnection(connectionString);

                conn.Open();

 

                executeCommand = newSqlCommand(sb.ToString(), conn);

                return executeCommand.ExecuteNonQuery();

            }

            catch (Exception er)

            {

                //Handle exception.

                throw er;

            }

        }

 

        publicstaticobject ExecuteScalar(StringBuilder sb, string connectionString)

        {

            SqlConnection conn = null;

            SqlCommand executeCommand = null;

            //SqlTransaction MyTrans = null;

            try

            {

                conn = newSqlConnection(connectionString);

                conn.Open();

 

                executeCommand = newSqlCommand(sb.ToString(), conn);

                return executeCommand.ExecuteScalar();

            }

            catch (Exception er)

            {

                //Handle exception.

                throw er;

            }

        }

 

        publicstaticDataSet GetDataSet(StringBuilder sb, string connectionString)

        {

            SqlConnection conn = null;

            SqlCommand executeCommand = null;

            SqlDataAdapter MyDataAdapter = null;

            DataSet returnDS = newDataSet();

            try

            {

                MyDataAdapter = newSqlDataAdapter();

               

                conn = newSqlConnection(connectionString);

                conn.Open();

 

                executeCommand = newSqlCommand(sb.ToString(), conn);

                MyDataAdapter.SelectCommand = executeCommand;

               

                MyDataAdapter.Fill(returnDS);

                return returnDS;

            }

            catch (Exception er)

            {

                //Handle exception.

                throw er;

            }

        }

    }

}

 


How to get a dataset with variable SQL from the entity framework

When you are working with the EF, you will finally get to the point that you need some joined data from the database that could be based on variable SQL queries. In our case we can define SQL queries in the database that will decide what to show in grids. Since this data is combining several tables from a non relational DB, its not returning entity data. So I found a good way to get a dataset and still use the entity framework connection. So no new connection logic to be instantiated:

 

public DataSet ExecuteStoreQuery(string commandText)

{

    DataSet retVal = new DataSet();

    EntityConnection entityConn = (EntityConnection)MyContext.Connection;

    SqlConnection sqlConn = (SqlConnection)entityConn.StoreConnection;

    SqlCommand cmdReport = new SqlCommand(commandText, sqlConn);

    SqlDataAdapter daReport = new SqlDataAdapter(cmdReport);

    using (cmdReport)

    {

        cmdReport.CommandType = CommandType.Text;

        daReport.Fill(retVal);

    }                  



    return retVal;

}

 

You should only use this for view purposes only!!!


Entity Framework and There is already an open DataReader associated with this Command which must be closed first.

There is already an open DataReader associated with this Command which must be closed first.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: System.InvalidOperationException: There is already an open DataReader associated with this Command which must be closed first.

Source Error: 

When using the entity Framework I got that error. Got it when I was looping within a loop. Since there was no association between the objects, I had to do a loop en inner loop, it looks like this:

foreach (GP_PROJ_ProjectSteps step in Ents.GP_PROJ_ProjectSteps.Where(s=>s.ProjectID == project.ProjectID))

{

    foreach (GP_PROJ_ProjectTasks task in Ents.GP_PROJ_ProjectTasks.Where(d => d.StepID == step.StepID))

    {

        Ents.GP_PROJ_ProjectTasks.DeleteObject(task);

    }

    Ents.GP_PROJ_ProjectSteps.DeleteObject(step);

}

Ents.GP_PROJ_Projects.DeleteObject(project);

Ents.SaveChanges();

 

The solution to this problem was to allow or enable Multiple Active Result Sets (MARS). This is done in your connection string:

Data Source=DBServer;Initial Catalog=MyDB;Persist Security Info=True;User ID=DBUSER; Password=PWD;MultipleActiveResultSets=true;


Fill Entity with custom query code. (Inner join etc)

 

Just a quick example of an ExecuteStoreQuery. This is a really nice gift of the entity framework. If you need fill an entity with some dynamic sql, you can use the ExecuteStoreQuery. Really easy to use!

return ents.ExecuteStoreQuery<GP_PROJ_Projects>("Select P.* From GP_PROJ_Projects P left join GP_PROJ_Codes C on C.CodeValue = P.ProjectStatus and C.CodeType = 'Project_Status' where C.CodeValue1 < {0} ", 1000).ToList();

Same result but can help with passing dates and other values:

return ents.ExecuteStoreQuery<GP_PROJ_Projects>("Select P.* From GP_PROJ_Projects P left join GP_PROJ_Codes C on C.CodeValue = P.ProjectStatus and C.CodeType = 'Project_Status' where C.CodeValue1< @p0",new SqlParameter { ParameterName = "p0", Value = 1000 }).ToList();

 

Source: http://msdn.microsoft.com/en-us/library/ee358769.aspx


Entity framework and MS SQL database (identity insert)

Using the entity framework in combination with an existing MS SQL database, comes with some restrictions and sometimes you need to change the DB structure.

For example, when you start from an existing database and you use identities as primary keys, the entity framework won't like it. There are several things you can do, you can let the entity framework create the database, or you can switch for example to guids and insrt the guid yourself. Because MSSQL doesn't allow changing the primary keys, I just generated a table creation script, changed the primary keys to guids, dropped all tables and ran the scripts.

To erase tables quickly in MS SQL, use:

(Warning, the following script will delete all tables!!!!)

EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"

This will delete all tables, then you can recreate the tables from a script you earlier created and alter the script as needed.


Outer join in Oracle SQL

I'm by origin a MS SQL  writer. This is the SQL I grew up with, although at school I learned Oracle, at my first job it was MS SQL and since then 90 %  of the SQL I use was MQ SQL. But at my current employer, it the other way around. It's 90% Oracle. Working more then 1 year with Oracle, I'm becoming more and more aware of the differences between MS SQL en Oracle SQL. I'm will discuss one of these differences.

When you need information from 2 tables, in MS SQL we use the Join clause, inner or outer. When it's possible that the child table has no related data for the record of the master table in the query, and still you want to see the record from the master table, you will need to use outer join. Now I tried this in Oracle SQL, there was no syntax error, but also no data. I could figure out what I was doing wrong.

This syntax returned nothing:

SELECT a.GUID, a.ROLE_NAME, b.APP_NAME, b.guid as APPGUID 
FROM CIG_MGMT.ROLE a
LEFT OUTER JOIN CIG_MGMT.ROLE_APPS b ON b.ROLE_GUID = a.GUID 
WHERE a.IS_VALID = 1 AND b.IS_VALID = 1 
ORDER BY a.ROLE_NAME,b.APP_NAME

So I started looking for an answer. I came up with this site: http://www.adp-gmbh.ch/ora/sql/outer_join.html

I changed the code into this and it worked!

SELECT a.GUID, a.ROLE_NAME, b.APP_NAME, b.guid as APPGUID 
FROM CIG_MGMT.ROLE a, CIG_MGMT.ROLE_APPS b 
WHERE b.ROLE_GUID(+) = a.GUID and 
a.IS_VALID = 1 AND b.IS_VALID(+) = 1 
ORDER BY a.ROLE_NAME,b.APP_NAME
 

Just use the (+) syntax for every field of the (child) table in the where clause. 

A solution was found, but not a reason why the left outer join wouldn't work and the (+) worked fine. The first reason could be that I was using Oracle 8i wich only supports the (+). But this is not the case. We are using 9i and the compiler doesn't complain to the syntax.

So I'm still looking for an explanation, because I have very little time, I cannot investigate this and I will just implement the (+) solution. I hope somebody who knows the answer takes the time to post it here... Or maybe in the future when I will have some more time, I will remember this and look for an answer. But this first 2 years will be hectic ;-)