LiteSqlHelper - yet another .Net data access helper
3 reasons why I made my own data access helper:
- Simplicity
- No unwanted dependencies
- Extensibility
Sample Usage
Configuration
1
2 <configuration>
3 <connectionStrings>
4 <add
5 name="MsSqlConnectionString"
6 connectionString="Your connection string..."
7 />
8 </connectionStrings>
9 </configuration>
10
Using Directives
1
2 using CAO.LiteSqlHelper;
3 using CAO.LiteSqlHelper.DataProvider;
4
Initialize Database
1
2 LiteSqlDb db = LiteSqlDb.CreateFromConfig(
3 new MsSqlProvider(),
4 "MsSqlConnectionString"
5 );
6
ExecuteNonQuery
1
2 LiteSqlCommand cmd = db.GetStoredProcCommand("sp_Name");
3 cmd.AddOutParameter("ID", DbType.Int32);
4 cmd.AddInParameter("FirstName", DbType.String, "Chris");
5 cmd.AddInParameter("LastName", DbType.String, "Ongsuco");
6
7 db.ExecuteNonQuery(cmd);
8
9 // Get the output parameter
10 int outputID = cmd.GetParameterValue<int>("ID");
11
ExecuteScalar
1
2 string sql = "SELECT COUNT(*) FROM [Customer] WHERE [ID] = @ID";
3
4 LiteSqlCommand cmd = db.GetSqlStringCommand(sql);
5 cmd.AddInParameter("ID", DbType.Int32, 210);
6
7 int totalCustomers = db.ExecuteScalar<int>(cmd);
8
ExecuteReader
1
2 DbDataReader reader;
3
4 try {
5 // Initialize your command...
6 reader = db.ExecuteReader(cmd);
7
8 // Populating collections and entities
9 List<Customer> customers = new List<Customer>();
10
11 while (reader.Read()) {
12 // Fill your collection or something...
13 Customer customer = new Customer();
14
15 // SqlValue is part of the helper
16 customer.ID =
17 SqlValue.Parse<int>(reader["ID"]);
18 customer.FirstName =
19 SqlValue.Parse<string>(reader["FirstName"]);
20
21 // and so on...
22
23 customers.Add(customer);
24 }
25 }
26 finally {
27 // Close the data reader
28 db.CloseReader(reader);
29 }
30
Transactions
1
2 try {
3 db.BeginTransaction();
4
5 string sql = "SELECT MAX([ID]) FROM [Customer]";
6
7 LiteSqlCommand cmd1 = db.GetSqlStringCommand(sql);
8
9 // Execute command 1
10 int maxID = db.ExecuteScalar<int>(cmd1);
11
12 LiteSqlCommand cmd2 = db.GetStoredProcCommand("sp_Name");
13
14 cmd2.AddInParameter(
15 "FirstName",
16 DbType.String,
17 "Any first name"
18 );
19 cmd2.AddInParameter(
20 "LastName",
21 DbType.String,
22 "Any last name"
23 );
24
25 // Execute command 2
26 db.ExecuteNonQuery(cmd2);
27
28 // Do some more work here...
29
30 db.Commit();
31 }
32 catch {
33 db.Rollback();
34 }
35 finally {
36 db.Dispose();
37 }
38
Database Support
The default database providers are:
-
MsSqlProvider - for SQL Server
-
OleDbProvider - for MS Access and etc.
You can also extend LiteSqlHelper to
support other databases. For example, to
use MySql as
database you need to:
-
Create a new class library project
-
Download and install the .Net data provider
of your favorite database (in this case we
will use MySql .Net Connector)
-
Add MySql.Data assembly as reference
-
Create a class called MySqlProvider that
inherits from the LiteSqlDbProvider
base class
MySqlProvider code:
1
2 using System;
3 using System.Data.Common;
4 using MySql.Data.MySqlClient;
5
6 namespace CAO.LiteSqlHelper.DataProvider.MySQL {
7 public class MySqlProvider : LiteSqlDbProvider {
8 public override DbConnection GetConnection() {
9 return new MySqlConnection();
10 }
11
12 public override DbDataAdapter GetDataAdapter() {
13 return new MySqlDataAdapter();
14 }
15
16 public override DbCommand GetCommand() {
17 return new MySqlCommand();
18 }
19
20 public override DbParameter GetParameter() {
21 return new MySqlParameter();
22 }
23
24 public override string CreateParameterName(
25 string parameterName
26 ) {
27 return string.Concat("?", parameterName);
28 }
29 }
30 }
31
Then you will initialize your LiteSqlDb
class like this:
1
2 LiteSqlDb db = LiteSqlDb.CreateFromConfig(
3 new MySqlProvider(),
4 "MySqlConnectionString"
5 );
6
That's it! You're good to go.
Source Code
The source code is completely written in C# 3.5
and is available for download.
License
It's completely free without any limitations.