C# Database Basics
Michael Schmalz
Copyright 2012 Michael Schmalz
OReilly books may be purchased for educational, business, or sales promotional use. Online editions are also available for most titles (.
Nutshell Handbook, the Nutshell Handbook logo, and the OReilly logo are registered trademarks of OReilly Media, Inc. C# Database Basics , the image of a capybara, and related trade dress are trademarks of OReilly Media, Inc.
Many of the designations used by manufacturers and sellers to distinguish their products are claimed as trademarks. Where those designations appear in this book, and OReilly Media, Inc. was aware of a trademark claim, the designations have been printed in caps or initial caps.
While every precaution has been taken in the preparation of this book, the publisher and authors assume no responsibility for errors or omissions, or for damages resulting from the use of the information contained herein.
O'Reilly Media
Preface
Using databases in C# can be daunting for developers moving from VB6, VBA, or Access. From the differences in the .NET syntax to the curly braces and semicolons, just looking at the code in C# for the first time can be intimidating. As you start to use C#, the small changes you need to make become easier and the code starts to flow nicely. However, you will likely find that many ways of working with data and databases that were easy in VB6 and VBA can be challenging when attempted for the first time in C#.
When you were programming in Classic VB, you could count on a good solid example of how to use a particular method, and it would be in context. For instance, if you were looking at a connection string example, it would likely include how to connect to the database, and it would probably also include a recordset or query. In C# and the other .NET languages, you will find fewer full examples and more examples that simply show the syntax. Or worse, theyll show the other objects in the example, but wont explain how to create those objects or explain where the object needs to be declared (at the form level or at the procedure level).
What led to this book was a challenge that I faced while doing something that I thought should have been very simple. I wanted to create a form with a datagrid that would load a table or query at runtime with the ability to filter, sort, and edit the records. I could do this task with Classic VB in a few minutes and in even less time with VBA inside of Access. With C#, there were pieces that were very simple, but only simple when building the connection to a single database and a single table that you define at design time. Getting code to change the datasource at runtime or connecting to a different table when your database schema changes was significantly more challenging. In addition, the help available online from within Visual Studio or even from an Internet search wasnt very complete. It isnt enough to know the method that you need to call; you need to understand where the variables are declared, the changes that are needed to the properties on the datagrid, the using references that are required, etc. Once you see it, the code is very clear, but it is less than straightforward when you are starting out.
Objectives
This book teaches you some specific items to help you get started with C# and databases. You wont tackle a full project, but rather you will get a chance to use C# in a way that helps you learn by example. Many programmers learn best by simply doing: using a concept in code that can eventually be applied to situations in the future. That is the essence of what you will accomplish by reading this book. No knowledge of C# or even VB is really required, but specific differences between Classic VB and C# will be highlighted. You dont even need to purchase any software; you can use the freely available Visual Studio Express and SQL Server Express if you dont have the full version of Visual Studio and/or Microsoft Office (for Access Databases). Also, you should generally be able to cut and paste code that you generate while working through this book to use in your other projects.
When you finish this book, you should be able to do the following:
Create a Windows Forms Application with a datagrid
Connect to multiple data sources (Access and SQL Server)
Add, Edit, and Update database data with a source set at runtime
Connect to a datasource at design time that cannot be changed
Understand roles of DataTable, DataView, BindingSource, Filters, and other objects
Understand that where variables are declared impacts the code
Build a simple webservice that connects to a database
As you follow the examples in this book, you will gain confidence in using C# and will be able to leverage this knowledge in other projects. Also, it is worth noting that both VB.Net and C# are powerful languages, and one isnt necessarily better than the other. Typically, in the past, people have used VB and VB.Net for data-rich and line-of-business applications and C# for the enterprise-level applications. But, this distinction is changing. It is true that if you are building a business application, many of the functions that you might want to use, such as net present value or other time value of money calculations, are built in to VB.Net and not to C#, which makes VB.Net the natural choice when you need that functionality. However, given how data-intense the world is becoming, you simply must know how to access, add, update, and delete data in C# if you plan to program with it. You will be able to do that if you follow the examples in this book.
Conventions Used in This Book
The following typographical conventions are used in this book:
Italic
Indicates new terms, URLs, email addresses, filenames, and file extensions.
Constant width
Used for program listings, as well as within paragraphs to refer to program elements such as variable or function names, databases, data types, environment variables, statements, and keywords.
Constant width bold
Shows commands or other text that should be typed literally by the user.
Constant width italic
Shows text that should be replaced with user-supplied values or by values determined by context.
Tip
This icon signifies a tip, suggestion, or general note.
Caution
This icon indicates a warning or caution.
Using Code Examples
This book is here to help you get your job done. In general, you may use the code in this book in your programs and documentation. You do not need to contact us for permission unless youre reproducing a significant portion of the code. For example, writing a program that uses several chunks of code from this book does not require permission. Selling or distributing a CD-ROM of examples from OReilly books does require permission. Answering a question by citing this book and quoting example code does not require permission. Incorporating a significant amount of example code from this book into your products documentation does require permission.
We appreciate, but do not require, attribution. An attribution usually includes the title, author, publisher, and ISBN. For example: C# Database Basics by Michael Schmalz (OReilly). Copyright 2012 Michael Schmalz, 978-1-449-30998-5.