Clone wiki

simpledataextension / Home

Getting Started

At first, you have to provide the connection string in your we.config/app.config file as so:

    <add name="Membership" connectionString="server=;database=Test;user id=kamyar;password=secret;" providerName="System.Data.SqlClient" />

Do not forget to add the 'ProviderName', for that the library can work with any of them! Not limited to MS SQL Server. You can use MySQl, my favorite PostGresql, Oracle, SQLite or any other database supporting Stored Procedures.

Afterwards, you must create an interface containing the stored procedure signatures:

public interface IMembershipDatabase
	int GetUsersCount();
	bool CreateUser(string username, string password);
	IEnumerable<User> GetUserList();
	void GetUserBalance(string username, out decimal balance, out decimal discount);

As you can see you can even use 'out' parameters and IEnumerable for stored procedures returning tables. You dant want to define POCO classes? no problem, This library creates one on-the-fly for you, just replace the Class name with object keyword:

IEnumerable<object> GetUserList();

Wiki pages are normal files, with the .wiki extension. You can edit them locally, as well as creating new ones.

This makes you confortable when for example serializing the class as JSON.

The last part is to write system wirings: for example, if you are making a web project, you can write these two lines in Global.asax file:


Ready! Now we can use the soterd procedures like any normal C# method:

	var db = Setup.GetInstance<IMembershipDatabase>();
	var count = db.getUserCount();
	decimal balance, discount;
	db.GetUserBalance("John", out balance, out discount);

Good luck!

Edit: Added return value feature

You can now use stored procedure 'Return' parameter in two ways:

1. You can ask the library to suppose return value of the stored procedure as the methods return value. For this to take effect, you just need to decorate the method inside the interface with ReturnValueAsResultAttribute:

   bool InsertUser();

2. You want to get the 'Return Value' as one of your out parameters. There is another Attribute:

   bool GetUserCount([ReturnValue] out int count)

This example supposes the target stored procedure returns the bool value using select statement and also the resulting integer as 'Return Value';

Update: You can now use a parameter name other than what you defined in the database:

bool CreateUser([DBName("username")] string uname, string password);

Edit: Added Dynamic Database feature!

In this version we have a new feature: You can use C# language dynamic feature to call your stored procedures instead of declaring an interface! You can omit interface declaration step as the following:

dynamic db = Setup.GetInstance("Financial");
int retval = db.TestRetVal<int>();

Note that in the above example, generic parameter is intended to clarify the return type of the method/stored procedure for the underlying library code. Alternatively, if you have a stored procedure in which the select clause returns a table, you can do same as 'Interface' mode:

IEnumerable<object> ret = db.TestSP2<IEnumerable<object>>(1);

You now, by default, the return values is supposed as a 'select' clause within the stored procedure. If you want to use a 'return' clause instead, you can add an extra underscore character (_) to the method name. In fact this is replacement of ReturnValueAsResult attribute since we can not use attributes in dynamic methods in C# language.

int RetVal = sb.TestRetVal_()

As you can see, you do not need to provide generic parameter any more! But only if you have a scalar return value.

As you can guess, the parameters order can be naturally changed using named parameter feature of C# language.

int RetVal = sb.TestRetVal_(p2: 5, p1: 7)

Edit: Out parameter Size feature added by lucaverga75

public GetNextValue(string SequenceID, [Size(50)] out string SequenceValue); }

NOTE: Currently we do not support table return values. please use normal select for table result.