Jump to content


Check out our Community Blogs

Register and join over 40,000 other developers!


Recent Status Updates

View All Updates

Photo
- - - - -

C# and Databases - Part 1 - High Level Intro to working with Databases from C#


  • Please log in to reply
7 replies to this topic

#1 sam_coder

sam_coder

    CC Addict

  • Senior Member
  • PipPipPipPipPip
  • 380 posts

Posted 20 February 2011 - 04:28 PM

I've thought a little bit about writing a tutorial series on databases, because it seems like the C# community here on this form is a little disconnected from the subject. Though I have no idea how many tutorials I will have to write to have everyone here at an expert level. For this tutorial, I'll focus on the high level stuff. You know, what typical components are involved, and how they work together.

Part of the problem with trying to do something like this in a forum, is that you need to understand at a high level, how components are related to one another, and you have to understand a certain amount about each component.

Also, did I mention that you need to have a working Database to really make use of it? So where does that leave me,, oh yes, we also need a tutorial on how to create a database, first to connect to.

Bear with me, read this, ask questions, and tell me what you want to know about databases, and I'll start spilling my guts. Odds are, some of you will become comfortable with the topic.. :c-cool:

Understand that when you work with databases, that your database is not just a place you read and write information to. Like say, a text file. There are some key differences. Many differences are way out of scope of this small tutorial, but suffice to say, that your code that talks to the database, will not follow your normal flow of code. It's a conundrum, it's normally ugly, and well, deal with it. =)

Assume that you have a database. It's the database you want to work with. It's living somewhere, lets say, and SQL Server Express instance, that is running on your development machine. (Chances are, you will be able to connect to it right now)

The database is not managed by our application, and it's running somewhere under the control of another process. The how we talk to it, isn't really a concern for our application, ADO.NET hides a lot of that from us. It exposes an object, called a Connection, which is your 'context' to that database.

It all sounds very theoretical.. doesn't it?

That's alright, it isn't too bad, I promise.

we cannot work with the database with this connection object by itself, it really is just context to that database. In order to work with the Database, we need to use another ADO.NET object, called a Command.

A Command is used to execute an action against a Data source. We have context to that Data Source through the Connection Object. Is everyone still with me?

If you have these two things, you're golden.

There are basically 2 types of actions you can do against your Data Source, though a command.

You can Do something to the Data Source, or you can Read something from the Data Source.

The code for something like this (pseudo code) would look something like this:
string connection_string = "Data Source=localhost\SQLExpress;Initial Catalog=mydatabase;Integrated Security=SSPI;";
using (SqlConnection connection = new SqlConnection(connection_string)) {
	connection.Open();
	string query = "SELECT * FROM whatever;";
	using (SqlCommand command = new SqlCommand(query, connection)) {
		using (SqlDataReader reader = command.ExecuteReader()) {
			while (reader.Read()) {
				reader.GetValues(object_array);
			}
		}
	}
}

So, this is overly simplified. And you see what I mean? The code kind of looks awkward, and I guess it is. This is why we typically build a set of Data Components, to hide logic like this from your application.

I'd like to move this on, and write something useful to you guys. How relevant is this information? I'd like to write tutorials on specifically, how to work with connections, Commands, data readers,
I'd even write a tutorial on how to create and do specific things with the database server itself (but it seems we don't have a Database Tutorial Section)...

Part 2: http://forum.codecal...base-table.html

Edited by sam_coder, 23 February 2011 - 10:37 AM.
Adding Link to part 2

  • 3

#2 CommittedC0der

CommittedC0der

    CC Leader

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1065 posts
  • Programming Language:C#
  • Learning:C#, JavaScript, PL/SQL

Posted 23 February 2011 - 08:11 AM

I'd be interested in some more tutorials on DB's. Its been a subject Ive wanted to get around to for along time but never have.

I would like a tutorial on how to create a DB, and setup a connection so data can be transfer to it/from it. If your still up for creating some tutorials. :)

Also a of couple questions, do i need an internet connection to connect to a SQL DB? And do I need any framework's or plugins for my PC or the clients PC to run SQL DB's? Sorry if those are dumb questions. :P

Thanks ~ Committed.
  • 0
There IS a war going on for your mind.
Nothing of eternal significance happens besides prayer.

#3 sam_coder

sam_coder

    CC Addict

  • Senior Member
  • PipPipPipPipPip
  • 380 posts

Posted 23 February 2011 - 08:18 AM

Hey, those aren't dumb questions. =)

short answers, you do NOT need an internet connection to connect to SQL Server database. I would assume you would be testing with it running on your own machine. So thats A-OK!

Secondly, all the framework you need will be isntalled with the .NET framework, and SQL Server Express.

you should be set. There are other clients you can use, and in production environments, where you dont have your development tools, you would have to install the .NET framework (obviously) and a proper client for ADO.NET to use, like SQL Server Native client.

Also, I have two more tutorials already waiting for moderator approval, so sit tight! and Im presently writing a third that connects all the dots, actually right to making calls to your database from a C# application.

Thanks for the feedback!
  • 1

#4 Tonchi

Tonchi

    Helping the world with programming

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1249 posts
  • Location:Zagreb
  • Programming Language:C#, Others
  • Learning:C, C++, Python, JavaScript, Transact-SQL, Assembly

Posted 04 April 2011 - 03:43 AM

is reader only for console app or i can use it on windows form app
  • 0

#5 Tonchi

Tonchi

    Helping the world with programming

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1249 posts
  • Location:Zagreb
  • Programming Language:C#, Others
  • Learning:C, C++, Python, JavaScript, Transact-SQL, Assembly

Posted 04 April 2011 - 03:44 AM

and if i can use it on windows form app is the code same or there is difference
  • 0

#6 sam_coder

sam_coder

    CC Addict

  • Senior Member
  • PipPipPipPipPip
  • 380 posts

Posted 04 April 2011 - 06:34 AM

Hey Tonchi!

Yes, it's fine to use with either a windows or a console app. I recommend using a console app to learn though, so you can focus on what is happening in the data code, rather than having to tweak lots of other things going on as well.

As you work through these tutorials, you will see that eventually where you are going with this, is code that you can call anywhere you want. For example, you will end up making a directory in your project called data, and as such, all your data classes will go in that directory and namespace. The classes that go in here will wrap the actual database implementation, and so you will be able to interact with the database like this:

Data.People people_controller = new Data.People();
Person[] people = people_controller.get_all();
foreach (Person person in people) {
   Console.WriteLine("{0}, {1}, {2}", person.ID, person.FirstName, person.LastName);
}

//or in a windows forms app
foreach (Person person in people) {
   lstvPeople.Items.Add(new ListViewItem(new string[] { person.IO.ToString(), person.Firstame, person.LastName }));
}



The whole idea is to keep your database specific code separated, then you can talk to your data in a way that makes sense. It makes every part of your application easier to manage.
  • 0

#7 cloud

cloud

    CC Resident

  • Advanced Member
  • PipPipPipPip
  • 69 posts
  • Programming Language:C#, PHP
  • Learning:C#, PHP

Posted 26 March 2013 - 04:47 PM

if i make a database in my project, i will get some error.. i dont know why.. its only say error in connection.. no source or files exist.. what will i do?


  • 0

#8 lespauled

lespauled

    CC Leader

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1360 posts
  • Programming Language:C, C++, C#, JavaScript, PL/SQL, Delphi/Object Pascal, Visual Basic .NET, Pascal, Transact-SQL, Bash

Posted 27 March 2013 - 05:07 AM

without the error message, any troubleshooting would be shooting in the dark.  Post the error message.


  • 0
My Blog: http://forum.codecal...699-blog-77241/
"Women and Music: I'm always amazed by other people's choices." - David Lee Roth




Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download