|
Creating a connection to a Microsoft SQL database is pretty easy.
This is a quick run through of it. The first thing we need to do is tell the .NET framework that we are going to be using a SQL database. We do this by adding the following line to our project:
using System.Data.SqlClient;
Right, now the next thing to do is to establish a connection:
string strConString =
"Data Source=127.0.0.1,1433;
Network Library=DBMSSOCN;
Initial Catalog=yourDBName;
User ID=user;
Password=password;
";
SqlConnection myConnection = new SqlConnection(strConString);
myConnection.Open();
Now we have an open connection to the SQL database. But what good is that? Well this is where the fun comes in, now we get to query the database. Imagine you had the following table:
TABLE_NAME: Person
personId : Integer (Primary Key)
personName : varchar
personEmail : varchar
And you wanted to retrieve all the records in the table, it would go something like this:
string strQuery = "SELECT * FROM Person";
string strConString =
"Data Source=127.0.0.1,1433;
Network Library=DBMSSOCN;
Initial Catalog=yourDBName;
User ID=user;
Password=password;
";
SqlConnection myConnection = new SqlConnection(strConString);
SqlCommand myCommand = new SqlCommand();
myCommand.CommandText = newCommand;
myConnection.Open();
myCommand.Connection = myConnection;
SqlDataReader myDataReader = myCommand.ExecuteReader();
Now we have a data reader populated with the results from the query. To loop through the results we might do something like:
while(myDataReader.Read()){
//get results here
}
Now there are a million and one ways to get the data from the rows, but this is a way that I am pretty fond of.
while(myDataReader.Read()){
int idCol = myDataReader.GetOrdinal("personId");
int nameCol = myDataReader.GetOrdinal("personName");
int emailCol = myDataReader.GetOrdinal("personEmail");
int id = myDataReader.IsDBNull(idCol) ? -1 :
myDataReader.GetInt32(idCol);
string name = myDataReader.IsDBNull(nameCol) ? "" :
myDataReader.GetString(nameCol);
string email = myDataReader.IsDBNull(emailCol) ? "":
myDataReader.GetString(emailCol);
System.Console.WriteLine("personId:" + id.ToString());
System.Console.WriteLine("personName:" + name);
System.Console.WriteLine("personEmail:" + email);
}
Pretty cool huh? So now for the cleaning up we just close either the record set or the database connection like so
myConnection.Close();
//OR the record set
myDataReader.Close();
Pretty easy huh? Next time I will show you how to use this in a very simple windows form. Have fun and if you have any queries please let me know.
|
