How to handle queries for each form

[ADQUSIT]

Hi. I'm using Insert, Update and delete queries on each form, according to the need of form. But is it a right way or I should adapt some other way? Please guide me. I want to improve my development strategies.

[MattP]

You'll need to elaborate a little bit before we can give you an answer that will be of value to you.

[ADQUSIT]

I mean that all the queries are working on same form, not in a separate module. Like programmers use a module and put all the queries there and just send them parameters from all forms. So which way is gonna helping. please guide me.

[make me rain]

you mean, just like an N tier application

[ADQUSIT]

This time i mean for a 1 PC Stand Alone application.

[CoachBarker]

You can still program it as an N tier application, 1 PC or many.

[ADQUSIT]

N tier means?

[CoachBarker]

multi tier application for example Data Layer, Data Access Class, Entity Class , GUI Class

[ADQUSIT]

Didn't pick it still?

[make me rain]

I mean that all the queries are working on same form, not in a separate module. Like programmers use a module and put all the queries there and just send them parameters from all forms. So which way is gonna helping. please guide me

do you mean to say that you will save the SQL statements in a module and pass on the parameters to the command object of some function / sub routine in a module ?
are you looking for one such ?

[techgnome]

multi tier application for example Data Layer, Data Access Class, Entity Class , GUI Class

oi.... no it doesn't... that's a multi-layered application... tiering the system means moving things to physically separated areas... such as having a database server, and application server, client, business server and so on... tiers and layers are not the same thing. Although you can't really have tiers w/o layers as a result they often go hand in hand... but you can layer an app, and still have only 1-tier... which is what it sounds like ADQUSIT is looking for. What you can't have is multiple tiers with a single layer... well, you could in theory, but something would have to be on crack in order to make it work.

ADQUSIT - in the end, it's all about maintainability... can you, six months from now make changes w/o affecting a whole string of code? If you're going to be the only one ever touching it, and you understand it, then go with what you want. If someone else is going to be going into it after you, then look at it form the 6-months ... If you want some kind of advice on how it's typically done in the field... then yes, things are broken out by functionality or some other arbitrary means and put into classes of some kind.

-tg

[make me rain]

tech
i would like to take some of your time here
i am not a professional programmer and am not in that field , i am a indian railways train driver
how exactly the IT companies doe's,
i mean for example, if i am developing a desk top application and a professional developer developing
which way it makes a difference. ( just out of curiosity ), i believe you are professional !

why the software piece costs so much costly, is this

maintainability
quality of code
supply of source code
annual maintenance contract
application of mind

what it is ?

[techgnome]

Why?
Short answer: there's a lot of overhead... marketing, packaging, talent, taxes, etc... all that stuff adds up.

The longer answer is that to do things right, it takes time. Pure and simple. And it takes talent and knowledge. Neither comes cheap. It's the same reason people pay others to do their taxes for them. They (hopefully) have the knowledge to fill out all the forms right. I am good at what I do, and as a result I can charge a premium for my time. Just about anyone can crank out code. Cranking out the right kind of code is another matter.

there's a number of things that determine the price of a piece of software... it's value plays a big part in that... if I create something no one's seen, but everyone wants it... I can charge quite a bit for it... if there's competitors, then I'd better be doing something better, or (most likely "and" rather than "or") I need to lower the price to be competitive... free market... supply & demand...


-tg

[CoachBarker]

My apologies read it wrong.

[techgnome]

My apologies read it wrong.

Pffft! I wouldn't worry about it... it's a common misconception... heck for years I operated under the idea that they were the same... finally took someone to step me through it before I really understood the differences... and of course the pedantic nature of me (and quite possibly the CDO side of me) wants to educate people when I see the terms being misapplied.

-tg

[ADQUSIT]

do you mean to say that you will save the SQL statements in a module and pass on the parameters to the command object of some function / sub routine in a module ?
are you looking for one such ?

yes its a way, about which i want to ask opinion, that which way is better to use?

@ TechGnome
I didn't understand it:

ADQUSIT - in the end, it's all about maintainability... can you, six months from now make changes w/o affecting a whole string of code? If you're going to be the only one ever touching it, and you understand it, then go with what you want. If someone else is going to be going into it after you, then look at it form the 6-months

[make me rain]

my straight answer is don't do it
because it serves no purpose because,
the SQL statement which you passes on to the command object includes the parameters often, and which must be in the same routine on the command,connection,adapter etc resides.

if you store it in other place alone , & as the application grows the confusion creates the havoc
secondly the SQL statements are 99 % unique ( for example inserting,editing,deleting into different tables )

How ever you can build a class which can build a SQL string and then execute the statement , of course it saves some lines of code and development time but it's nasty ( i don't know about the security concerns )

Public Class Generals
  
    Public Sub SaveMethod(ByVal TableName As String, _
                          ByVal FieldNames() As String, _
                          ByVal Params() As String, _
                          ByVal Field_Values() As String, ByVal Cnn As MySqlConnection)
 
        '' saves the new values
 
        Dim Str_Sql As New StringBuilder
        With Str_Sql
            .Append("INSERT INTO ")
            .Append(TableName)
            .Append("( ")
        End With
 
        '' append field names
        For Each Sx As String In FieldNames
            With Str_Sql
                .Append(Sx.ToString & ",")
            End With
        Next
 
        '' remove extra coma
        With Str_Sql
            .Append(".").Replace(",.", ")")
            .Append(" VALUES ( ")
        End With
 
        '' add params
        For Each Rx As String In Params
            With Str_Sql
                .Append(Rx.ToString & ",")
            End With
        Next
 
        '' Remove extra coma
        With Str_Sql
            .Append(".").Replace(",.", ")")
            .Append(" ;")
        End With
 
        Dim SQL_save As String = Str_Sql.ToString
        Dim Cmd_Insert As New MySqlCommand(SQL_save, Cnn)
        Dim Connection As New Connector
 
        '' Add parameters
        Dim I As Byte = 0
        For Each FV As String In Field_Values
 
            With Cmd_Insert
                .Parameters.AddWithValue(Params(I).ToString, FV)
            End With
 
            I += 1
        Next
 
        With Cmd_Insert
 
            Connection.Cobs_Connect(True, Cnn)
            .ExecuteNonQuery()
            Connection.Cobs_Connect(False, Cnn)
 
            .Dispose()
        End With
 
        TableName = Nothing
        FieldNames = Nothing
        Params = Nothing
        Field_Values = Nothing
 
    End Sub
 
    Public Sub EditMethod(ByVal TableName As String, _
                          ByVal FieldNames() As String, _
                          ByVal Params() As String, _
                          ByVal Field_Values() As Object, _
                          ByVal KeyField As String, _
                          ByVal KeyParam As String, ByVal KeyValue As String, _
                          ByVal Cnn As MySqlConnection)
 
        '' saves the new values
 
        Dim Str_Sql As New StringBuilder
 
        With Str_Sql
            .Append("UPDATE ")
            .Append(TableName)
            .Append(" SET ")
        End With
 
        Dim iX As Byte = 0
        For Each sX As String In FieldNames
 
            With Str_Sql
                .Append(String.Concat(sX, " = ", Params(iX).ToString, ","))
            End With
 
            iX += 1
        Next
 
        '' remove extra coma
        With Str_Sql
            .Append(".").Replace(",.", "")
        End With
 
        With Str_Sql
            .Append(" WHERE ")
            .Append(String.Concat(KeyField, " = ", KeyParam, " ;"))
        End With
 
 
        Dim Cmd_Edit As New MySqlCommand(Str_Sql.ToString, Cnn)
        Dim Connection As New Connector
 
        '' Add parameters
        Dim I As Byte = 0
        For Each FV As Object In Field_Values
 
            With Cmd_Edit
                .Parameters.AddWithValue(Params(I).ToString, FV)
            End With
 
            I += 1
        Next
 
        '' add where clause
 
        With Cmd_Edit
            .Parameters.AddWithValue(KeyParam, KeyValue)
        End With
 
        With Cmd_Edit
 
            Connection.Cobs_Connect(True, Cnn)
            .ExecuteNonQuery()
            Connection.Cobs_Connect(False, Cnn)
 
            .Dispose()
        End With
 
        TableName = Nothing
        FieldNames = Nothing
        Params = Nothing
        Field_Values = Nothing
 
    End Sub
    Public Sub DeleteMethod(ByVal TableName As String, _
                          ByVal FieldNames As String, _
                          ByVal Params As String, _
                          ByVal Field_Values As String, ByVal Cnn As MySqlConnection)
 
        '' saves the new values
 
        Dim Str_Sql As New StringBuilder
 
        With Str_Sql
            .Append(" DELETE FROM ")
            .Append(TableName)
            .Append(" WHERE ")
            .Append(String.Concat(TableName, ".", FieldNames, " = ", Params, " ;"))
        End With
 
        Dim S As String = Str_Sql.ToString
 
        Dim Cmd_Drop As New MySqlCommand(S, Cnn)
        Dim Connection As New Connector
 
        '' Add parameters
        With Cmd_Drop
            .Parameters.AddWithValue(Params, Field_Values)
        End With
 
        With Cmd_Drop
 
            Connection.Cobs_Connect(True, Cnn)
            .ExecuteNonQuery()
            Connection.Cobs_Connect(False, Cnn)
 
            .Dispose()
        End With
 
        TableName = Nothing
        FieldNames = Nothing
        Params = Nothing
        Field_Values = Nothing
 
    End Sub

now to use the calss

Dim Gen As New Generals
        Dim Cnn29 As New MySqlConnection
 
        Dim FieldNames() As String = {"cugno", "perno", "pmedue", "grs_due", "tech_due", "nliid"}
        Dim Params() As String = {"@cugno", "@perno", "@pmedue", "@grs_due", "@tech_due", "@nliid"}
        Dim FieldValues() As Object = {CUGnumber, PersNumber, PmeDue, GrsDue, TechDue, Liid}
 
        Gen.EditMethod("crewdetail", FieldNames, Params, FieldValues, "Detailid", "@Detailid", detailId, Cnn29)

[ADQUSIT]

I'm using parametrized queries on my forms and have all common objects like sqlconnection, dataAdapter and DataSet.

What i want now is that i pass the parameters to module, where all the process on query is done and just give me the end result on my form.

[ADQUSIT]

This is my module code:

This is my connection code:

 Public Function Conn()
        Try
            If cnSql.State = ConnectionState.Closed Then
                cnSql.Open()
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

        Return cnSql
    End Function

This is my insert sub routine:

#Region "Insertion"

    Public Sub Insertion(ByVal tblName As String, ByVal columns As String, ByVal Parameters As String)

        Try
            cmdsql1.CommandText = "insert into " & tblName & " ( " & columns & ")  values  ( " & Parameters & ")"
            cmdsql1.Connection = Conn()
            cmdsql1.ExecuteNonQuery()

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub

#End Region

And this is my save button code on my main form:

Try
            Insertion("ProductBasicInfo", "ProdId, ProdName, Description, Manufacturer", " " & txtProdID.Text.Trim & ", '" & txtProdName.Text.Trim & "', '" & txtProdDesc.Text.Trim & "', '" & txtProdManuf.Text.Trim & "'")
            MessageBox.Show("Record Inserted Successfully")
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

From my form, i pass the parameters to module, but i'm not satisfied with this way of code, because this query may have sql injection.

Please guide me that how to use parametrized query in module level. Though I always deal queries on single form in parametrized way, but can't use on module level.