CODEWITHSUNDEEP
databasevb.netclassado.net
gingo
gingo

Reputation: 488

Adding a row to a table from the properties of a class

I have a class that represents the table of a db-row. Its properties are the columns of the table. I add a new row to the table with the following code:

Public Sub AddRow(oTestRow As TestRow)
    Dim sql As String
    With oTestRow
        sql = String.Format("INSERT INTO TestTable " &
                             "(ArtNr, ArtName, ArtName2, IsVal, CLenght)  " &
                             "Values ('{0}', '{1}', '{2}', {3}, {4})",
                              .ArtNr, .ArtName, .ArtName2, .IsVal, .CLenght)
    End With
    Using oConn As New OleDbConnection(m_ConnString)
        oConn.Open()
        Using oInsertCmd As New OleDbCommand(sql, oConn)
            oInsertCmd.ExecuteNonQuery()
        End Using
    End Using
End Sub

That is just an example, but my classes have around 30-40 properties and this brings a very large and complex sql string. Creating, editing or maintaining these sql strings for many classes could generate errors. I am wondering if any compact way or method exists in order to add the whole object's istance (the properties of course) to the table "TestTable" without writing such a large sql string. I created the TestRow in the way that its properties are exactly the columns of the table "TestTable" (with the same name). But I did not found in the ADO.NET anything that could be used.

Upvotes: 0

Views: 80

Answers (2)

Krishna
Krishna

Reputation: 2481

for starters anything with inline queries is a bad practice (unless the need demands for e.g. you have tables defined in the db, and dont have access to the db to deploy procedures)

you have few options - for e.g. instead of handwriting the classes , use Entitiy framework a better alternative to Linq2Sql

if you want to stick with the tags in this question I would design this making the most of OO concepts. (this is a rough sketch, but I hope this helps)

public class dbObject
   protected <type> ID --- This is important. if this has value, commit will assume update, otherwise an update will be performed

   public property DBTableName // set the table name 

   public property CommitStoredprocedure // the procedure on the database that can do commit work
   public property SelectStoredProcedure // the procedure used to retrieve the i

   public dbObject construcor (connection string or dbcontext etc)
     set dbConnection here
   end constructor

   public method commit

     reflect on this.properties available and prepare your commit string.

     if you are using storedproc ensure that you prepare named parameters and that the stored proc is defined with the same property names as your class property names. also ensure that storedproc will update if there is an ID value or insert and return a ID when the id value is not available

     Create ADO.net command and execute. (this is said easy here but you need to perfect this method)
   End method

end class

public class employee inherits dbObject
   // employee properties here
   public string name;
end employee

public class another inherits dbObject
   //another properties
   public bool isValid;
end department

usage:

employee e = new employee;
e.name = "John Smith";
e.commit(); 
console.WriteLine(e.id); // will be the id set by the commit method from the db

If you make baseclass correct (well tested) here, this is automated and you shouldnt see errors.

you will need to extend the base class to Retrieve records from the db based on an id (if you want to instantiate objects from db)

Upvotes: 0

nik
nik

Reputation: 1479

If changing DB system is an option, you may wanna take a look at some document based no sql solution like MongoDB, CouchDB or especially for .Net RavenDB, db4o or Eloquera.

Here is a list of some of them.

Upvotes: 1

Related Questions