paulovich Wiki & Documentation Rss Feed

Updated Wiki: Home

paulovich — Tue, 13 Oct 2009 13:49:03 GMT

Project Description
Paulovich.Data is an light weight data access layer.

There is a bigger wiki at http://www.100loop.com/wikis/paulovich-data/default.aspx in portuguese.

Paulovich.Data has the following key features:

Multi-database support
- SQL Server (2000, 2005 and 2008)
- Oracle
- Access
- Odbc
Easy-to-use
- Stored Procedures
- Parametrized Queries
- Custom select return types (DataTable, DataSet, DataReader, Scalar, Xml)
Just Plug-and-Play
- No complicated configuration
ER Mapping

Updated Wiki: Generating classes

LucianoLima — Tue, 10 Feb 2009 12:33:49 GMT

Download the current project in http://www.codeplex.com/paulovich open the solution in VisualStudio 2008 and if not configured, select the project Paulovich.Data.Generator as "StartUpProject" of the solution. The VisualStudio will be bold the name of project in your Solution Explorer.

Now edit the App.config:

<add name="dbPortal" connectionstring="your connection string here" 
    providername="System.Data.SqlClient"></add>

Then, press F5 and run the application we create classes!

When the application starts, click the yellow folder at the top left. Then you will see the tables of the bank that you configured in App.config. Select the tables you want to map to classes and click "Generate".

Now just copy the classes to your project. Enjoy!

Remember that your classes are generated with the name standards that are being used in your database. To understand how to edit your classes without losing its features read this article Editing Classes.

Updated Wiki: Generating classes

paulovich — Tue, 10 Feb 2009 11:52:56 GMT

<add name="dbPortal" connectionstring="your connection string here" 
    providername="System.Data.SqlClient"></add>

Updated Wiki: Home

paulovich — Tue, 10 Feb 2009 11:52:12 GMT

Multi-database support

SQL Server (2000 and 2005)
Oracle
Access
Odbc

Easy-to-use

Stored Procedures
Parametrized Queries
Custom select return types (DataTable, DataSet, DataReader, Scalar, Xml)

Just Plug-and-Play

No complicated configuration

ER Mapping

Updated Wiki: Generating yours classes

paulovich — Tue, 10 Feb 2009 11:50:09 GMT

<add name="dbPortal" connectionstring="your connection string here" 
    providername="System.Data.SqlClient"></add>

Updated Wiki: Generating yours classes

paulovich — Tue, 10 Feb 2009 11:46:50 GMT

<add name="dbPortal" connectionstring="your connection string here" 
    providername="System.Data.SqlClient"></add>

Then, press F5 and run the application we create classes!

When the application starts, click the yellow folder at the top left. Then you will see the tables of the bank that you configured in App.config. Select the tables you want to map to classes and click "Generate".

Now just copy the classes to your project. Enjoy!

Remember that your classes are generated with the standards of name that are being used in your database. To understand how to edit your classes without losing its features read this article (in portuguese, that will be translated) http://www.100loop.com/wikis/paulovich-data/editando-suas-classes.aspx.

Updated Wiki: Generating yours classes

paulovich — Tue, 10 Feb 2009 11:45:58 GMT

<add name="dbPortal" connectionstring="your connection string here" 
    providername="System.Data.SqlClient"></add>

Then, press F5 and run the application we create classes!

When the application starts, click the yellow folder at the top left. Then you will see the tables of the bank that you configured in App.config. Select the tables you want to map to classes and click "Generate".

Now just copy the classes to your project. Enjoy!

Remember that your classes are generated with the standards of name that are being used in your database. To understand how to edit your classes without losing its features http://www.100loop.com/wikis/paulovich-data/editando-suas-classes.aspxread this article.

Updated Wiki: Generating yours classes

paulovich — Tue, 10 Feb 2009 11:42:58 GMT

<add name="dbPortal" connectionstring="your connection string here" 
    providername="System.Data.SqlClient"></add>

Updated Wiki: Generating yours classes

paulovich — Tue, 10 Feb 2009 11:39:32 GMT

<add name="dbPortal" connectionstring="sua string de conexão" 
    providername="System.Data.SqlClient"></add>

Updated Wiki: Home

paulovich — Tue, 10 Feb 2009 11:36:51 GMT

Multi-database support

SQL Server (2000 and 2005)
Oracle
Access
Odbc

Easy-to-use

Stored Procedures
Parametrized Queries
Custom select return types (DataTable, DataSet, DataReader, Scalar, Xml)

Just Plug-and-Play

No complicated configuration

ER Mapping

Updated Wiki: Using our Stored Procedures

paulovich — Tue, 10 Feb 2009 11:25:26 GMT

Using our Stored Procedures

Lets execute the procedure spExecuteProcedure like that:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE spExecuteProcedure
     @param1 int,
     @param2 varchar(50),
     @param3 smalldatetime
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO TABLE (field1,field2 ,field3) VALUES(@param1 ,@param2 ,@param3)
END
GO

imports Paulovich.Data;
imports System.Data;
create a method to execute your stored

public void ExecuteProcedures(int param1, string param2, DateTime param3) {
//declare a Paulovich Command object

Command command = new Command();
    try {
        //define a CommandType on Enumerator

        command.CommandType = CommandType.StoredProcedure;
        //add values to object

        command.AddWithValue("param1", param1);
        command.AddWithValue("param2", param2);
        command.AddWithValue("param3", param3);
        //execute the procedure easily

        command.ExecuteNonQuery("spExecuteProcedure"); 
    } catch (Exception) { 
        throw;
   }
}

You can implement a transaction too.

Updated Wiki: implement a transaction

paulovich — Tue, 10 Feb 2009 11:24:09 GMT

How to implement a Transaction

Declare a Paulovich Command object

            Command command = new Command();
            try {
                //define a CommandType on Enumerator

                command.CommandType = CommandType.StoredProcedure;
                //add values to object

                command.AddWithValue("param1", param1);
                command.AddWithValue("param2", param2);
                command.AddWithValue("param3", param3);
                //start a transaction

                command.BeginTransaction();
                
                command.ExecuteNonQuery("spExecuteProcedure");
                
                //if no error was fire execute Commit();

                command.Commit();
            } catch (Exception) {
                //otherwise RollBack() your Command

                command.RollBack();
                throw;
            }

Updated Wiki: Using our Stored Procedures

paulovich — Tue, 10 Feb 2009 11:23:27 GMT

Using our Stored Procedures

Lets execute the procedure spExecuteProcedure like that:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE spExecuteProcedure
     @param1 int,
     @param2 varchar(50),
     @param3 smalldatetime
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO TABLE (field1,field2 ,field3) VALUES(@param1 ,@param2 ,@param3)
END
GO

imports Paulovich.Data;
imports System.Data;
create a method to execute your stored

public void ExecuteProcedures(int param1, string param2, DateTime param3) {
//declare a Paulovich Command object

Command command = new Command();
try {
//define a CommandType on Enumerator

command.CommandType = CommandType.StoredProcedure;
//add values to object

command.AddWithValue("param1", param1);
command.AddWithValue("param2", param2);
command.AddWithValue("param3", param3);
//execute the procedure easily

command.ExecuteNonQuery("spExecuteProcedure"); 
} catch (Exception) { 
throw;
}
}

You can implement a transaction too.

Updated Wiki: Scalar

paulovich — Tue, 10 Feb 2009 11:22:42 GMT

Return Scalar

Lets return the value of spCountCities:

CREATE PROCEDURE spCountCities	
AS
BEGIN
	SET NOCOUNT ON;
	SELECT Count(*) FROM CITIES
END
GO

Like other types, you need to create a Paulovich Command.

 (int)(command.ExecuteQuery("spCountCities", ReturnType.Scalar));

Updated Wiki: Custom select return types

paulovich — Tue, 10 Feb 2009 11:21:24 GMT

Custom select return types

The procedure is:

CREATE PROCEDURE spGetCities
	@fkState int
AS
BEGIN
	SET NOCOUNT ON;
	SELECT CityName FROM CITIES where pkState = @pkState
END
GO

And method

public DataTable getDataTable (int pkState)  {
           Command command = new Command();
           command.CommandType = CommandType.StoredProcedure;
           //add values to object

           command.AddWithValue("pkState", pkState);
            
           return (DataTable)(command.ExecuteQuery("spGetCities", ReturnType.DataTable));
}

You can return other types:

DataReader

 (DataReader)(command.ExecuteQuery("spGetCities", ReturnType.DataReader));

DataSet

 (DataSet)(command.ExecuteQuery("spGetCities", ReturnType.DataSet));

DataView

 (DataView)(command.ExecuteQuery("spGetCities", ReturnType.DataView));

Read more about types:

Return Scalar.
Return XmlDocument.

Updated Wiki: How To Configure Paulovich.Data

paulovich — Tue, 10 Feb 2009 11:18:12 GMT

How to configure Paulovich.Data

Add Paulovich.Data.dll reference to your project
Set connectionString section like this:

//Connection Strings Section  

  <connectionStrings>
    <remove name="dbPortal" />
    <add name="dbPortal" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true" providerName="System.Data.SqlClient"/>
  </connectionStrings>

Updated Wiki: How To Configure Paulovich.Data

paulovich — Tue, 10 Feb 2009 11:17:35 GMT

How to configure Paulovich.Data

Add Paulovich.Data.dll reference to your project
Set connectionString section like this:

//

// Connection Strings Section 

// 

  <connectionStrings>
    <remove name="dbPortal" />
    <add name="dbPortal" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true" providerName="System.Data.SqlClient"/>
  </connectionStrings>

Updated Wiki: How To Configure Paulovich.Data

paulovich — Tue, 10 Feb 2009 11:17:18 GMT

How to configure Paulovich.Data

Add Paulovich.Data.dll reference to your project
Set connectionString section like this:

//
// Connection Strings Section
//

  <connectionStrings>
    <remove name="dbPortal" />
    <add name="dbPortal" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true" providerName="System.Data.SqlClient"/>
  </connectionStrings>

Updated Wiki: Multiplies Database

LucianoLima — Tue, 10 Feb 2009 11:11:52 GMT

The default connection string is in Web.config file.
Sometimes we need to work with more than one database in the same application, so how can we do that?

First, we need to use the SetConnection() method.

SetConnection method is responsible to define the current connection. We will use ConnectionString sentence and the Database Type as parameters for the SetConnection method.

Command.SetConnection({DataBaseType}, {ConnectionString});

DataBaseType (Enum): this Enum defines which database type will be set to Command instance. It has the following data types: DataBaseType.Sql, DataBaseType.Oracle, DataBaseType.Oledbc and DataBaseType.OleDb.

ConnectionString (String): the connection string sentence.

But, attention!! These changes are permanent. Every command will be executed using this connection.

To recover the old information about the connection we will use the GetDefaultConnectionStrings method and we must set the new parameters (Database Type and ConnectionString) again.

var settings = Command.GetDefaultConnectionSettings();
Command.SetConnection((DataBaseType) Command.GetDataBaseTypeByName(settings.ProviderName), settings.ConnectionString);

That´s it!

Updated Wiki: Multiplies Database

LucianoLima — Tue, 10 Feb 2009 11:11:24 GMT

Command.SetConnection({DataBaseType}, {ConnectionString});

DataBaseType (Enum): this Enum defines which database type will be set to Command instance. It has the following data types: DataBaseType.Sql, DataBaseType.Oracle, DataBaseType.Oledbc and DataBaseType.OleDb.

ConnectionString (String): the connection string sentence.

var settings = Command.GetDefaultConnectionSettings();
Command.SetConnection((DataBaseType) Command.GetDataBaseTypeByName(settings.ProviderName), settings.ConnectionString);

That´s it!