Posts By Brandon Avant

SQLite Implementation

We’ve all been in situations where we needed to store data on a device within a specific application. Not only should the database reside on the machine where the application is running, but it also shouldn’t matter if we have multiple instances connecting to the database. In other words, we only need to provide data to one specific application. For a desktop solution, we could easily setup MSSQL or MySQL; but what if the aforementioned application is running on a mobile device? Similarly, what if we need our database to be portable between devices? SQLite solves all of these requirements. SQLite is a light-weight and self-contained SQL database engine that enables us to put a database on just about any type of device. Also, since SQLite uses a flat-file for the actual database, there is no worry of a complicated setup. Typically, a SQLite installation consists of a flat-file, the SQLite library (in a format such as a .dll file), and the actual application that will be using the database; That’s it! Perhaps now you’re thinking, “That’s all good, but how complicated is SQLite to code for?” The answer to that question is, “Not complicated at all!” To demonstrate the simplicity of implementing SQLite, consider the following C# code snippet: string connectionString = "@"Data Source="C:exampledb.sqlite3" SQLiteConnection db = new SQLiteConnection(connectionString); db.Open(); SQLiteCommand query = db.CreateCommand(); query.CommandText = "SELECT * FROM tblExampleTable"; DataTable dtExampleTable = new DataTable(); SQLiteDataReader dr; dr = query.ExecuteReader(CommandBehavior.CloseConnection); dtExampleTable.Load(drSqliteReader); Now, let’s discuss what this code actually does. In the first line, we’re instantiating a new instance of SQLiteConnection. The argument passed into the constructor provides the location of the flat-file that we will be using for the database. In the case of this example, I used the extension .sqlite3 for my flat-file. Because of the way the file is parsed, the actual extension that you use is irrelevant. Nonetheless, providing a meaningful extension (such as .sqlite3) can give a good indication to others of what the file is as well as the version of SQLite being used. From here, we call the Open() method on our newly-created connection object. This method simply tells SQLite to go ahead and connect to our database, whose connection string was passed to the SQLiteConnection its constructor. Next, using our connection object, we create a SQLiteCommand that will be used to provide the engine with our actual query that we will be running. After assigning said query, using query.CommandText, we instantiate an instance of a standard .NET DataTable. After that, we simply execute our query that we just defined and load the results into the DataTable object that we created. Voila! We now have an object that contains results that we queried straight from a SQLite flat-file. We didn’t have to perform any complicated code (like serialization between .NET and SQLite) to get this going. You would of course want to continue beyond our short code example (e.g. displaying the data in a GridView in ASP.NET), but for the purpose of this blog, that’s all we need.

N-Tier Software Architecture

As many developers probably realize, software development is a very detailed process. Not only must a requirements gathering and specifications documentation process be established, but there is also a need for a pre-development architectural phase. What do I mean by this? Well, you can’t exactly instruct builders to build a house without an architect and electrician laying out the blueprints. Likewise, “Good Software” is usually built on top of pre-planned “blueprints”. As an example, consider a game development studio. Before game developers can implement code written for a game environment, there must first be a game engine. The game engine would provide the developers a set of tools (e.g. lighting, sound, and physics) for developing a game. Programmers don’t necessarily care about the actual environment or what was involved in creating the game engine; they only care about the availability of the tools. In this posting, we will explore one of the most integral methodologies in today’s world of software development. That methodology is known as multi-tier software architecture. N-Tier Architecture (or multi-tier architecture) is an approach to software development in which code and underlying data structures are split into multiple independent layers. This structure is not only a logical separation of layers, but often times a physical separation as well (i.e. different layers on different machines). The first question that may come to mind when considering this approach might be, “Why design software this way? Wouldn’t such a separation introduce unnecessary complexity?” The answer is quite the contrary. By introducing this separation, we are allowing software to be written in a way that enables better extensibility, better usability, and more portability. During my years as an undergraduate C.S. student, we were introduced to programming in several steps. The first step (Structured Programming) was to gain an understanding of basic programming fundamentals such as loops, if-conditions, and methods/functions. The second step was an introduction to one of the most important programming fundamentals to date: object-oriented programming. Beyond this, further instruction was in the way of implementation. This included network programming and UNIX systems programming. These approaches to the art of programming (yes, programming is an art) are vital when learning the fundamentals. However, they don’t exactly teach true business-level software development. We not only want to stick to the laws of object-oriented programming and all of those things we were taught from a beginner perspective, but we also need to provide, again, extensibility, usability, and portability. To gain a true understanding of how n-tier architecture can help us achieve this, we first take a look directly at the multi-layer aspect of this development approach. The standard approach is to split development into three main layers; presentation layer, logic layer, and data access layer. The first layer, the Presentation Tier is the portion of the software that is what the user actually sees, more specifically, the UI. This layer (or tier) is the visual representation of the data that is gathered in the other, not-so-visible layers. The presentation is often in the form of a Graphical User Interface; such as the website that the user actually sees. Not only does this layer typically provide the visual representation of our data, it also provides a means of user-intervention; thus allowing the user to interact with the software environment. This layer should not perform complex logic (e.g. data processing and calculations), as it is the next layer’s job to implement such a task. The second layer in our example is the Logic Tier. This layer is hidden from the front-end user and is in charge of performing calculations, business logic, and/or transferring data between the surrounding layers. This layer is sometimes considered the middleware layer (e.g. ASP.NET). The final layer in our example (keeping in mind that there could be more layers; I’ve chosen two for this example) is the Data Tier. This layer is in charge of moving data between the logic tier and the database. No complex logic or calculations should be performed at this layer. Simple logic, such as determining how to place data in an object for the other layers to utilize, is usually okay. Complex logic is the responsibility of the aforementioned logic tier. By separating our software into the various layers described above, we can save ourselves a lot of development time in the future. Why would we save time? Here’s a scenario that I’ll use to help explain. Let’s say we’re developing complex software that needs to access the database in several different places. Let’s also say that the project architect decides to establish a connection to said database in each portion of the application that needs to access the database. Now, finally, let’s say that the means of connection was specific to the type of database (e.g. SQLServer, MySQL, etc) that we’re connecting to. What is the most obvious problem that we might run into? If you guessed extensibility and/or portability, then you guessed right. Let’s say the application was built to use MySQL but down the road, we need to switch to SQLServer. In the architectural setup described above, we would be tasked with stripping out all old connection instances. Depending on the complexity of the application, this could become messy really quickly. The n-tier setup helps alleviate this problem as we’ve made presentation, logic, and data access completely independent of one another. To provide a better explanation, let’s consider the following as an alternative to the overly cluttered scenario described above. If we instead follow an n-tier setup, switching to SQL Server from MySQL in the data tier would be a simple switching out of the previously used data layer with our SQL Server layer. Ideally, we wouldn’t have to change a single line of code in the other two layers as they do not care how data is retrieved in the lowest layer. The Logic tier simply makes calls to the DAL and as long as the method signatures have not changed, nothing ever changed, as the logic tier is concerned. This swapping out of layers can be done at any level. So, if the presentation layer needed to change, as before, the other two layers would not care. Now that we finally have an idea of what multi-tier is, one of the most important things to consider about this way of thinking is the portability that it provides. A major advantage provided by portability is the ability to have not only a logical separation of the different layers but a physical separation as well; each independent layer can exist on different machines. The physical isolation is where we draw the major line between n-tier and what is known as MVC (model-view-controller – we will discuss this in a later blog…) Now that you have discovered n-tier, an entirely new world of software architecture has been opened in front of you (well, hopefully :-))

Sign up for our blog updates!