See System.Data.Entity.Validation.DbEntityValidationException Details

Today i got an error on saving data to db using EF.

An exception of type ‘System.Data.Entity.Validation.DbEntityValidationException’ occurred in EntityFramework.dll but was not handled in user code

Additional information: Validation failed for one or more entities. See ‘EntityValidationErrors’ property for more details.

System.Data.Entity.Validation.DbEntityValidationException

but I can’t find out what are the failed validations.

System.Data.Entity.Validation.DbEntityValidationException2

No more details in quick watch. I stucked a little. At this time i find out the use of $exception.That is a special debugger variable. You cannot access it via code.

https://msdn.microsoft.com/en-us/library/ms164891.aspx

https://blogs.msdn.microsoft.com/shaykatc/2004/02/20/vs-2003-tip-3-view-exception-information-with-exception/

look  $exception in quickwatch.

System.Data.Entity.Validation.DbEntityValidationException3

 

CREATE DATABASE PERMISSION DENIED IN DATABASE ‘master’

I have installed SQL SERVER 2008  on my system with Windows 7 operating system installed.
Whenever I try to create a new database it displays an error:
CREATE DATABASE PERMISSION DENIED IN DATABASE 'master'.(Microsoft SQL Server, Error:262).

After a lot of search i am found a solution.

When i start SQL server management studio use ‘sa’ user and not Windows-User. Looks like Windows-user does not have permission.

First, open SQl in administrator mode.
In Security node ,open logins and select users node. In server role tab, tick sysadmin and User Map tab tick master database and select owner in below section.

Click Ok in all windows. Close SQL Management Studio and open it in normal mode.
Now i can create database. No Issues..

 

How I find a particular column name within all tables of SQL server datbase.

For example, I have a database named Organisation. I have more than one table where tax_id column is present.

Most of the time, we have to find such a column from the whole database. The solution is provided below:
select table_name,column_name from information_schema.columns where column_name like ‘%tax%’

 
Here I am searching for column_name which contains tax within its name. It will return all the tables and respective columns containing tax.

Example:

Table_Name Column_name
t_Customer tax_id
t_Employee tax_number
t_Balance_sheet tax_percentage

 

Get Full Month

ALTER FUNCTION [dbo].[getFullmonth]
(
@date1 date
)
RETURNS @dates TABLE
(
date date not null
)
AS
BEGIN
-- Set first day in month
DECLARE @month int;

SET @month = datepart(MM, @date1);
SET @date1 = convert(datetime, convert(varchar,datepart(yy,@date1)) + ‘.’ + convert(varchar,@month) + ‘.01 00:00:00’);
WHILE datepart(MM,@date1) = @month
BEGIN
INSERT INTO @dates VALUES (@date1);
SET @date1 = dateadd(dd, 1, @date1);
END
RETURN;
END

OVER and PARTITION BY

OVER

OVER allows you to get aggregate information without using a GROUP BY. In other words, you can retrieve detail rows, and get aggregate data alongside it. For example, this query:

SELECT SUM(Cost) OVER () AS Cost
, OrderNum
FROM Orders

Will return something like this:

Cost  OrderNum
10.00 345
10.00 346
10.00 347
10.00 348

Quick translation:

  • SUM(cost) – get me the sum of the COST column
  • OVER – for the set of rows….
  • () – …that encompasses the entire result set.

OVER(PARTITION BY)

OVER, as used in our previous example, exposes the entire resultset to the aggregation…”Cost” was the sum of all [Cost]  in the resultset.  We can break upthat resultset into partitions with the use of PARTITION BY:

SELECT SUM(Cost) OVER (PARTITION BY CustomerNo) AS Cost
, OrderNum
, CustomerNo

FROM Orders

My partition is by CustomerNo – each “window” of a single customer’s orders will be treated separately from each other “window”….I’ll get the sum of cost for Customer 1, and then the sum for Customer 2:

Cost  OrderNum   CustomerNo
8.00 345        1
8.00 346        1
8.00 347        1
2.00 348        2

The translation here is:

  • SUM(cost) – get me the sum of the COST column
  • OVER – for the set of rows….
  • (PARTITION BY CustomerNo) – …that have the same CustomerNo.

Ref: http://www.midnightdba.com/Jen

ADO Helper

 

#Region “- Includes”
Imports System.Text
Imports System.Data
Imports System.Xml
Imports System.Data.Common
Imports System.Data.SqlClient
Imports System.Collections
#End Region

Public Class clsDBA
Implements IDisposable
#Region “Local Variable and Objects”
Private strConnectionString As String
Private objConnection As DbConnection
Private objCommand As DbCommand
Private objFactory As DbProviderFactory = Nothing

#End Region

#Region ” – Constructors”

Public Sub New(ByVal connectionstring As String)
strConnectionString = connectionstring

objFactory = SqlClientFactory.Instance
objConnection = objFactory.CreateConnection()
objCommand = objFactory.CreateCommand()

objConnection.ConnectionString = strConnectionString
objCommand.Connection = objConnection
End Sub

#End Region

#Region ” – Command”

Public ReadOnly Property Command() As DbCommand
Get
Return objCommand
End Get
End Property

#End Region

#Region ” – AddParameter”

Public Function AddParameter(ByVal name As String, ByVal value As Object) As Integer
Dim p As DbParameter = objFactory.CreateParameter()
Try
p.ParameterName = name
p.Value = value
Catch ex As Exception

End Try

Return objCommand.Parameters.Add(p)
End Function

#End Region

#Region ” – AddParameter”

Public Function AddParameter(ByVal name As String, ByVal value As Object, ByVal direction As String) As Integer
Dim p As DbParameter = objFactory.CreateParameter()
Try
p.ParameterName = name
p.Value = value

If direction.ToUpper() = “output”.ToUpper() Then
p.Direction = ParameterDirection.Output
End If

Catch ex As Exception

End Try

Return objCommand.Parameters.Add(p)
End Function

#End Region

#Region ” – Open/CloseConnection”

Public Sub OpenConnection()
‘ Clear DB Connection Pooling
ClearDBConnectionPool(objConnection)

‘ Forcing the connection to close
objConnection.Close()

If objConnection.State = System.Data.ConnectionState.Closed Then
objConnection.Open()
End If
End Sub

Public Sub CloseConnection()
‘ Clear DB Connection Pooling
ClearDBConnectionPool(objConnection)

‘ Forcing the connection to close
objConnection.Close()
End Sub

#End Region

#Region ” – ClearDBConnectionPool”
”’
”’ To clear the DB connection pooling
”’

”’Connection Object Private Sub ClearDBConnectionPool(ByVal objConnection As DbConnection)
Try
SqlConnection.ClearPool(DirectCast(objConnection, SqlConnection))
Catch ex As Exception

End Try
End Sub
#End Region

#Region ” – Dispose”

Public Sub Dispose() Implements IDisposable.Dispose
ClearDBConnectionPool(objConnection)
If objConnection.State = System.Data.ConnectionState.Open Then
objConnection.Close()
End If
objConnection.Dispose()
objCommand.Dispose()
End Sub

#End Region

#Region ” – ExecuteNonQuery”

Public Function ExecuteNonQuery(ByVal query As String) As Integer
Dim intIndex As Integer = -1

Try
objCommand.CommandText = query
objCommand.CommandType = CommandType.StoredProcedure

If objConnection.State = System.Data.ConnectionState.Closed Then
objConnection.Open()
End If

objCommand.ExecuteNonQuery()

For intjIndex As Integer = 0 To objCommand.Parameters.Count – 1
If objCommand.Parameters(intjIndex).Direction.ToString().ToUpper() = “output”.ToUpper() Then
intIndex = Convert.ToInt32(objCommand.Parameters(intjIndex).Value)
End If
Next
Catch ex As Exception
Throw ex
Finally
objCommand.Parameters.Clear()
End Try
Return intIndex
End Function

#End Region

#Region ” – ExecuteScalar”

Public Function ExecuteScalar(ByVal query As String) As Object
Dim o As Object = Nothing
Try
objCommand.CommandText = query
objCommand.CommandType = CommandType.StoredProcedure

If objConnection.State = System.Data.ConnectionState.Closed Then
objConnection.Open()
End If
o = objCommand.ExecuteScalar()
Catch ex As Exception
Throw ex
Finally
objCommand.Parameters.Clear()
End Try

Return o
End Function

#End Region

#Region ” – ExecuteDataSet”

Public Function ExecuteDataSet(ByVal query As String) As DataSet

Dim adapter As DbDataAdapter = objFactory.CreateDataAdapter()
Dim dsResult As New DataSet()
Try
objCommand.CommandText = query
objCommand.CommandType = CommandType.StoredProcedure
adapter.SelectCommand = objCommand

adapter.Fill(dsResult)
Catch ex As Exception
Throw ex
Finally
objCommand.Parameters.Clear()
End Try
Return dsResult
End Function

#End Region

#Region ” – ExecuteDataTable”

Public Function ExecuteDataTable(ByVal query As String) As DataTable
Dim adapter As DbDataAdapter = objFactory.CreateDataAdapter()
Dim dsResult As New DataSet()
Try
objCommand.CommandText = query
objCommand.CommandType = CommandType.StoredProcedure
adapter.SelectCommand = objCommand

adapter.Fill(dsResult)
Catch ex As Exception
Throw ex
Finally
objCommand.Parameters.Clear()
End Try
Return dsResult.Tables(0)
End Function

#End Region

End Class

 

Remove Duplicate Entry from Comma Delimited String

This is a very simple script which will remove duplicate entry from comma delimited string. User Defined Function has very simple logic behind it. It takes comma delimited string and then converts it to table and runs DISTINCT operation on the table. DISTINCT operation removes duplicate value. After that it converts the table again into the string and it can be used.

I have modified original contribution from Ashish so now it completely covers the subject intended to cover. I would suggest that this UDF should be kept handy to perform this tedious task easily.

CREATE FUNCTION dbo.DistinctList
(
@List VARCHAR(MAX),
@Delim CHAR
)
RETURNS
VARCHAR(MAX)
AS
BEGIN
DECLARE @ParsedList TABLE
(
Item VARCHAR(MAX)
)
DECLARE @list1 VARCHAR(MAX), @Pos INT, @rList VARCHAR(MAX)
SET @list = LTRIM(RTRIM(@list)) + @Delim
SET @pos = CHARINDEX(@delim, @list, 1)
WHILE @pos > 0
BEGIN
SET @list1 = LTRIM(RTRIM(LEFT(@list, @pos - 1)))
IF @list1 <> ''
INSERT INTO @ParsedList VALUES (CAST(@list1 AS VARCHAR(MAX)))
SET @list = SUBSTRING(@list, @pos+1, LEN(@list))
SET @pos = CHARINDEX(@delim, @list, 1)
END
SELECT @rlist = COALESCE(@rlist+',','') + item
FROM (SELECT DISTINCT Item FROM @ParsedList) t
RETURN @rlist
END
GO
SELECT dbo.DistinctList('342,34,456,34,3454,456,aa,bb,cc,aa',',')DistinctList
GO

I encourage my readers to send their contribution as well so I can include their contribution as well.

AdoHelper Class

using System;

using System.Data;

using System.Data.SqlClient;

namespace BlackBeltCoder

{
class AdoHelper : IDisposable
{

protected string _connString = null;

protected SqlConnection _conn = null;
protected SqlTransaction _trans = null;
protected bool _disposed = false;
public static string ConnectionString { get; set; }
public SqlTransaction Transaction { get { return _trans; } }

public AdoHelper()
{
_connString = ConnectionString;

Connect();

}

public AdoHelper(string connString)

{

_connString = connString;

Connect();

}

protected void Connect()

{

_conn = new SqlConnection(_connString);

_conn.Open();

}

public SqlCommand CreateCommand(string qry, CommandType type, paramsobject[]

args)
{
SqlCommand cmd = new SqlCommand(qry, _conn);
if (_trans !=  null)
cmd.Transaction = _trans;
cmd.CommandType = type;
for (int i = 0; i < args.Length; i++)
{

if (args[i] is string && i < (args.Length – 1))

{
SqlParameter parm = new SqlParameter();
parm.ParameterName = (string)args[i];
parm.Value = args[++i];
cmd.Parameters.Add(parm);
}
else if
(args[i] is SqlParameter)
{
cmd.Parameters.Add((SqlParameter)args[i]);
}
else throw
new
ArgumentException
(“Invalid number or type of
arguments supplied”
);
}
return cmd;
}

#region
Exec Members
public int ExecNonQuery(string qry, params object[] args)
{
using (SqlCommand cmd =CreateCommand(qry, CommandType.Text, args))
{
return cmd.ExecuteNonQuery();
}
}

public int ExecNonQueryProc(string proc, params object[] args)

{

using (SqlCommand cmd =
CreateCommand(proc, CommandType.StoredProcedure, args))

{

return cmd.ExecuteNonQuery();

}

}

public object ExecScalar(string qry,  params object[] args)

{

using (SqlCommand cmd =
CreateCommand(qry, CommandType.Text, args))

{

return cmd.ExecuteScalar();

}

}

publicobject ExecScalarProc(string qry,paramsobject[] args)

{

using (SqlCommand cmd =
CreateCommand(qry, CommandType.StoredProcedure, args))

{

return cmd.ExecuteScalar();

}

}

public SqlDataReader ExecDataReader(string qry, params

object[] args)

{

using (SqlCommand cmd =
CreateCommand(qry, CommandType.Text, args))

{

return cmd.ExecuteReader();

}

}

public SqlDataReader ExecDataReaderProc(string qry, params

object[] args)

{

using (SqlCommand cmd =
CreateCommand(qry, CommandType.StoredProcedure, args))

{

return cmd.ExecuteReader();

}

}

 public DataSet ExecDataSet(string qry, params

object[] args)

{

using (SqlCommand cmd =
CreateCommand(qry, CommandType.Text, args))

{

SqlDataAdapter adapt = new SqlDataAdapter(cmd);

DataSet ds = new DataSet();

adapt.Fill(ds);

return ds;

}

}

public DataSet ExecDataSetProc(string qry, params

object[] args)

{

using (SqlCommand cmd = CreateCommand(qry, CommandType.StoredProcedure, args))

{

SqlDataAdapter adapt = new SqlDataAdapter(cmd);

DataSet ds = new DataSet();

adapt.Fill(ds);

return ds;

}

}

#endregion   

#region Transaction Members

public SqlTransaction BeginTransaction()

{

Rollback();

_trans = _conn.BeginTransaction();

return Transaction;

}

public void

Commit()

{

if (_trans !=
null
)

{

_trans.Commit();

_trans = null;

}

}

 public void Rollback()

{


if (_trans != null)

{

_trans.Rollback();

_trans = null;

}

}      

#endregion      

#region IDisposable Members

 public void Dispose()

{

Dispose(true);

GC.SuppressFinalize(this);

}

protected virtual void Dispose(bool disposing)

{

if (!_disposed)

{

if (disposing)

{

if (_conn != null)

{

Rollback();

_conn.Dispose();

_conn = null;

}

}

_disposed = true;

}

}    

#endregion

}

}