You'd better use a code generator for your data access layer. Writing it is quite easy. It could generate even CRUD PL/SQL.
Here you have a small example:
Private Sub GeneraCapaAccesoDato(ByVal tipo As Integer, ByVal modo As String)
Dim sb As New StringBuilder()
Dim Tabla As String = lbTabla.SelectedValue
Dim dt As DataTable = RecuperarDatosTabla(Tabla)
sb.Append(String.Format("public int {1}(OracleConnection con, BE{0} oBE{0})", Tabla, modo))
sb.AppendLine("{")
sb.AppendLine("int Resultado;")
sb.AppendLine(String.Format("OracleCommand cmd = new OracleCommand(""Pa_{0}_{1}"", con);", Tabla, modo))
sb.AppendLine("cmd.CommandType = CommandType.StoredProcedure;")
sb.AppendLine("")
Dim i As Integer
Row = dt.Select()
Dim NomTabla As String
Dim Tamaño As Integer
Dim scala As Integer
Dim TipoDato As String = "Ninguno"
Dim precision As Integer
Dim aux As Object
Dim aux1 As Object
Dim llave As Integer
For i = tipo To Row.Count() - 1
llave = Int32.Parse(Row(i).Item(7))
NomTabla = Row(i).Item(0).ToString()
Tamaño = Integer.Parse(Row(i).Item(2).ToString())
aux = Row(i).Item(4).ToString()
scala = Integer.Parse(If(aux = "", 0, aux))
aux1 = Row(i).Item(3).ToString()
precision = Integer.Parse(If(aux1 = "", 0, aux1))
If scala > 0 Then
If scala >= 0 And scala <= 15 Then
TipoDato = "OracleDbType.Double"
End If
ElseIf Row(i).Item(1).ToString() = "NUMBER" Then
If precision < 2 Then
TipoDato = "OracleDbType.Int16"
ElseIf precision >= 2 And precision <= 9 Then
TipoDato = "OracleDbType.Int32"
ElseIf precision >= 10 And precision <= 18 Then
TipoDato = "OracleDbType.Int64"
End If
Else
If Row(i).Item(1).ToString() = "DATE" Then
TipoDato = "OracleDbType.Date "
End If
If Row(i).Item(1).ToString() = "VARCHAR2" Then
TipoDato = "OracleDbType.Varchar2 "
End If
If Row(i).Item(1).ToString() = "CHAR" Then
TipoDato = "OracleDbType.Char "
End If
End If
sb.AppendLine(String.Format("OracleParameter Par{1} = cmd.Parameters.Add(""P_{0}"",{2});", _
NomTabla, If(tipo = 0, i + 1, i), TipoDato))
If Row(i).Item(1).ToString() = "VARCHAR2" Or Row(i).Item(1).ToString() = "CHAR" Then
sb.AppendLine(String.Format("Par{1}.Size = {0};", Tamaño, If(tipo = 0, i + 1, i)))
End If
sb.AppendLine(String.Format("Par{0}.Direction = ParameterDirection.Input;", If(tipo = 0, i + 1, i)))
sb.AppendLine(String.Format("Par{0}.Value = oBE{2}.{1};", If(tipo = 0, i + 1, i), NomTabla, Tabla))
sb.AppendLine("")
TipoDato = ""
Next
sb.AppendLine("Resultado = cmd.ExecuteNonQuery();")
sb.AppendLine("return Resultado;")
sb.AppendLine("}")
rtbVisor.Text = sb.ToString()
End Sub
Private Function RecuperarDatosTabla(ByVal NombreTabla As String) As DataTable
sb = New StringBuilder
sb.Append(" select COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE, NULLABLE, DATA_DEFAULT ,column_id")
sb.Append(" from(USER_TAB_COLUMNS)")
sb.Append(String.Format(" where TABLE_NAME = '{0}' order by table_name,column_id ", NombreTabla))
Using con As New OracleConnection(strConexion)
con.Open()
dt = New DataTable
Dim da As OracleDataAdapter = New OracleDataAdapter(sb.ToString(), con)
da.Fill(dt)
End Using
Return dt
End Function
