Using View with Linked Server (Oracle) on Entity Framework (.NET)

One of the issue I came across on Entity Framework was pulling a view that consist of a linked server table (Oracle). When I added the view, I was getting the following error. "The table/view LINKED_SERVER_VIEW does not have a primary key defined and no valid primary key could be inferred. This table/view has been excluded. To use the entity, you will need to review your schema, add the correct keys, and uncomment it." I needed this view as readonly. Only solution that worked was using ISNULL to define a primary key on the view. You will have to create a view like this for Entity Framework to understand the primary key. CREATE VIEW LINKED_SERVER_VIEW AS SELECT ISNULL(PRIMAYKEYCOLUMN, 0) as COL1, OTHERCOLUMN from LINKEDSERVER..SCHEMA.TABLENAME I believe the best approach is to actually fix the table or the model itself and add an actual primary key. However, sometime you will have to work on a database that a client does not want to change and you will have to deal with issues like this. Hope this solution works for you. If you have better solution, feel free to comment.
FacebookTwitterGoogle+Share

Protected Internal access modifiers on C#

Most of us have question on what is a protected internal modifiers. In short, it is a access modifier that can be accessed within the same assembly or by a class that inherits in a different assembly. For testing purposes, we will create an C# solution that has two projects. One of the project is a web application/console application and another is a class library. Instead of application you can create a class library if you wish. We will use the protected, internal and protected internal access modifiers to test the results. On the class library project, I have the following classes Class1 which contains 3 methods with protected, internal and protected internal access modifiers. Class2 does not inherit Class1 but it instantiates Class1 object "b" Class3 inherits Class1 and instantiates object "c" hence inheriting Class1 methods. "b" and "c" will try to access the methods on Class1. On the web application project, I will be using the namespace of class library project above to inherit Class1. Before I do this, I will need to compile class library project above and add the dll as a reference into the web application project. On this web application project, I created the fourth class called Class4. This class will inherit Class1 from the dll. I will try to access the methods on Class1 by creating an object "d". Below is the code Class Library
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
 
namespace ClassLibrary1
{
    public class Class1 //super class that contains the access modifiers and methods
    {
        protected void testprotected() { }
        internal void testinternal() { }
        protected internal void testprotectedinternal() { }
    }
 
    public class Class2 //different class on same assembly
    {
        public void test2()
        {
            Class1 b = new Class1();
            b.testprotected(); //Error 'ClassLibrary1.Class1.testprotected()' is inaccessible due to its protection level
            b.testinternal();
            b.testprotectedinternal();
        }
    }
 
    public class Class3 : Class1 // different class on same assembly that inherits class on same assembly
    {
        public void test3()
        {
            Class3 c = new Class3();
            c.testprotected();
            c.testinternal();
            c.testprotectedinternal();
        }
    }
}
Web Application
using System.Linq;
using System.Web;
using ClassLibrary1;
 
namespace protectedinternaltest1
{
    public class Class4 : Class1 // different class that inherits class from different assembly
    {
        public void test4()
        {
            Class4 d = new Class4();
            d.testprotected();
            d.testinternal(); //Error 'protectedinternaltest1.Class4' does not contain a definition for 'testinternal' and no extension method 'testinternal' accepting a first argument of type 'protectedinternaltest1.Class3' could be found (are you missing a using directive or an assembly reference?)
            d.testprotectedinternal();
        }
    }
}
You will receive the errors(commented above) on the code because of encapsulation. Same Assembly, different class protected: not accessible because protected modifiers allows only sub class to access the protected method internal: accessible because it is within the same assembly protected internal: accessible because it is within the same assembly Same Assembly, sub class (inheritance) protected: accessible because it is a sub class of Class1 internal: accessible protected internal: accessible Different assembly, sub class (inheritance) protected: accessible because protected allows sub class to access the method. internal: inaccessible because it is not within the same class protected internal: accessible because method is called from sub class.

Convert Membership GUID to User Information

Here is an example of converting Membership userid to First Name and Last Name on Membership Profile. The way you will call this function ...
Response.write(convertGuidtoName(Guid "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"));
public string convertGuidtoName(Guid g)
    {
        MembershipUser user = Membership.GetUser(g) ; //
        string name = user.UserName; // get username from membership
 
        ProfileCommon userProfile = (ProfileCommon)
        ProfileCommon.Create(name, true);
        string x = string.Concat(userProfile.FirstName, " ", userProfile.LastName);
        return x;
    }

Creating web service in VB.NET that outputs to JSON raw data

Here is a quick tutorial on creating a web service in VB.NET that output the data in JSON.
Imports System
Imports System.Web
Imports System.Collections
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.Script.Serialization
Imports System.Web.Script.Services
Imports System.ComponentModel
 
<System.Web.Services.WebService(Namespace:="http://tempuri.org/")> _
<System.Web.Services.WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<ToolboxItem(False)> _
Public Class config
    Inherits System.Web.Services.WebService
 
    <WebMethod()> _
    <ScriptMethod(ResponseFormat:=ResponseFormat.Json)> _
    Public Function getConfigurationValueByKey(ByVal strKeyName As String) As String
        Dim sqlConnection As SqlConnection = New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("ConnStr").ConnectionString)
        sqlConnection.Open()
        Dim sqlCommand As SqlCommand = New SqlCommand("SELECT * FROM table1 WHERE column1 ='" + strKeyName + "';", sqlConnection)
        Dim sqlDataset As DataSet = New DataSet()
        Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdapter(sqlCommand)
 
        sqlDataAdapter.Fill(sqlDataset)
        sqlConnection.Close()
 
        Dim MArray()() As String = New String(sqlDataset.Tables(0).Rows.Count)() {}
        Dim i As Integer = 0
 
        For Each rs As DataRow In sqlDataset.Tables(0).Rows
            MArray(i) = New String() {rs("KeyName").ToString(), rs("ValueName").ToString()}
            i = i + 1
        Next
 
        Dim js As JavaScriptSerializer = New JavaScriptSerializer()
        Dim sJSON As String = js.Serialize(MArray)
        Return sJSON
    End Function
 
End Class
Output will be something like this.
  <?xml version="1.0" encoding="utf-8" ?> 
  <string xmlns="http://tempuri.org/">[["key1","value1"],["key2","value2"]]</string>

Display SQL result horizontally (comma separated) in MSSQL 2005

Here is a sql statement to display a column result horizontally. e.g. record1, record2, record3, record4, ... recordx
DECLARE @id_list VARCHAR(MAX) --varchar max works for SQL 2005 or above
 
SELECT @id_list = CASE WHEN @id_list IS NULL THEN CONVERT(VARCHAR,column1) 
		ELSE @id_list + ',' + CONVERT(VARCHAR,column1) END
		FROM table1		
SELECT @id_list

Visual Source Safe 2005 issue – opening VS solution redirects to My Computer

I have been using Visual Source Safe 2005 for quite sometime. I came to an unusual problem. I tried opening a project solution created under Visual Studio 2010. When I double clicked the solution, it redirected me to "My Computer" list which contained Physical and Network directories. After quite a bit of searching for answer, I found a solution to this issue. There is a Microsoft patch (KB943847) for VSS 2005 that needs to be applied to the VSS 2005. This solves the issue of redirecting to My computer when trying to open solution on VSS 2005. http://www.microsoft.com/downloads/details.aspx?FamilyID=8a1a68d8-db11-417c-91ad-02aab484776b&displaylang=en

Max pool .NET issue on ADO.NET and a quick solution

This is the error message you will get when pool is maxed out. Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached. Please try closing reader, command, connection by using close() and dispose() before using the method below. If the pooling is enabled in the application, sometime closing the connection by using Dispose() or Close() is just going to put the connection on the sleep mode (awaiting command). Even though there is connection closing statements on the application, on the database side, it was putting those connections on the sleep mode. As it built up, there were no more pool left, therefore it timed out. Solution is to add Pooling = False on the connection string of the application. This closes the connection as soon as dispose() is introduced. Hence no timeouts waiting for open pool.
Database=testdb; Server=testserver; uid=test; PWD=test; Pooling=False; Connect Timeout=30
I am not responsible if you use my solution and mess up something. Do at your own risk. Works for me here. Checking number of connections on database to see if max pool has reached …
--Total number of connections
SELECT DB_NAME(dbid) AS 'Database Name',
COUNT(dbid) AS 'Total Connections'
FROM sys.sysprocesses WITH (nolock)
WHERE dbid > 0
GROUP BY dbid;
 
--Connections
SELECT hostname, program_name, STATUS, cmd FROM sys.sysprocesses
WHERE DB_NAME(dbid) = 'Database Name goes here';