SQL object mapper - delete a record from table

How to delete records from Person table

This sample codes will show how easy using SQL objects.

string connectionString = "...";

// define Person object.
Person person = new Person();

// using SQL-object to delete Person table
SqlDeleteFrom delete = new SqlDeleteFrom(person);
// set connection string
delete.ConnectionString = connectionString;

// set Where conditional to delete a record with PersonId = 3
delete.Where.Compare(person.PersonId, CompareOperator.Equal, 3);

// execute delete Person table
int r = delete.Execute();


For more information click here.

SQL object mapper - update a record in table

How to update records in Person table

This sample codes will show how easy using SQL objects.

string connectionString = "...";

// define Person object.
Person person = new Person();

// set data to be updated - Firstname = "New Name"
person.FirstName.Value = "New Name"

// using SQL-object to update Person table
SqlUpdate update = new SqlUpdate(person);
// set connection string
update.ConnectionString = connectionString;

// select FirstName column to update
update.Columns.Add(person.FirstName);

// set Where conditional to update a record with personid = 2
update.Where.Compare(person.PersonId, CompareOperator.Equal, 2);

// execute select Person table
int r = update.Execute();

For more information click here.

SQL object mapper - insert record to table

How to insert a record into Person table

This sample codes will show how easy using SQL objects.

string connectionString = "...";

// define Person object.
Person person = new Person();

// set all data properties to insert
person.PersonId.Value = 10;
person.FirstName.Value = "Michael Johnson";
person.Status.Value = true;

// using SQL-object to insert Person object into Person table
SqlInsertInto insert = new SqlInsertInto(person);
// set connection string
insert.ConnectionString = connectionString;

// select all columns to insert
insert.Columns.Add(person.PersonId);
insert.Columns.Add(person.FirstName);
insert.Columns.Add(person.Status);

// execute insert Person table
int r = insert.Execute();

For more information click here.

SQL object mapper - Where conditional

How to select table with WHERE codnitional

This sample codes will show how easy using SQL objects.

string connectionString = "...";

// define Person object.
Person person = new Person();

// using SQL-object to select Person table
SqlSelectFrom select = new SqlSelectFrom(person);
// set connection string
select.ConnectionString = connectionString;

// select all columns from Person table
select.Columns.All(person);

// set Where conditional FirstName = 'Andy'
select.Where.Compare(person.FirstName, CompareOperator.Equal, "Andy");

// execute select Person table
Irwsoft.Data.DataView dv = select.Execute();

// display record from DataView object
string personID_Row_1 = dv.Rows[0][person.PersonId];
string firstName_Row_1 = dv.Rows[0][person.FirstName];


Conditional :

• // conditional personid between 1 and 5
  select.Where.Between(person.PersonId, 3, 7);
• // conditional personid in ( 1, 2, 3, 4, 5 )
  select.Where.In(person.PersonId, 1, 2, 3, 4, 5);
• // conditional firstname like 'Andy%'
  select.Where.Like(person.FirstName, "Andy%");

For more information click here .

SQL object mapper - select table

How to select or display a Person table

First we need to design blueprint of table schema as a class,
Example : Person table with columns : PersonID (PK), FirstName, Status, LeaderID (FK)

[Table("Person")]
public class Person
{

[Column("PersonId", DbType.Int32, false, PrimaryKey = true)]
private Column _personId;

[Column("FirstName", DbType.String, true)]
private Column _firstName;

[Column("Status", DbType.Boolean, true)]
private Column _status;

[Column("LeaderId", DbType.Int32, false, PrimaryKey = true)]
private Column _leaderId;

public Column PersonId { get { return _personId; } }
public Column FirstName { get { return _firstName; } }
public Column LeaderId { get { return _leaderId; } }
public Column Status { get { return _status; } }
public PersonTable()
{ // initialize all fields with ColumnAttribute
Irwsoft.Data.DataFactories.InitTableSchema(this);
}
} 

For more information click here.

This sample codes will show how easy using SQL objects to select Person table :

string connectionString = "...";

// define Person object.
Person person = new Person();

// using SQL-object to select Person table
SqlSelectFrom select = new SqlSelectFrom(person);
// set connection string
select.ConnectionString = connectionString;

// select all columns from Person table
select.Columns.All(person);

// execute select Person table
Irwsoft.Data.DataView dv = select.Execute();

// display record from DataView object on 1st row
string personID_Row_1 = dv.Rows[0][person.PersonId];
string firstName_Row_1 = dv.Rows[0][person.FirstName];

// display record from DataView object on 2nd row
string personID_Row_2 = dv.Rows[1][person.PersonId];
string firstName_Row_2 = dv.Rows[1][person.FirstName];

For more information click here .

•  Previous chapter : About Irwsoft.Data Framework
•  Next chapter : How to use WHERE conditional

Irwsoft Data Framework

As developer we always deal with SQL database and SQL command text. Ofcourse it's not a problem if we just select records from a table. We need more than that like passing the data as an object to a method. To doing that it must create object to encapsulate the properties and that's extra job for us. Then the object relational mapper come with the idea a record is a class with columns as its properties. Yes it help us more to work easily.

Now , I have come with the idea in the fact that some of developers do not remember of all SQL expression and syntax rule for each provider. Because there are so much different in many providers. The idea is to make object relation mapper framework that work or design like SQL command. And I call it SQL object mapper.

Ok... I don't want to make my writing to long and boring, so let's continue on next chapter ...

Starting using Irwsoft.Data SQL object mapper - How to select a table