2 01 2013
How to connect to database
Today we provide a snippet for prospective developers. In fact how to easily get a connection to a database.
I use a method which can be changed easily to connect to other database, e.g. Acces and MySql. In my example we will refer to Ms Sql.
In the example code below you can see that I use System.Data.SqlClient.SqlConnection and System.Data.SqlClient.SqlCommand for instantiation.
For a connection to an access database you would need System.Data.Odbc.OdbcConnection and System.Data.Odbc.OdbcCommand. Furthermore you have to specify the database driver which must be installed at the system. I think for Access 2003 it was:
|
1 |
DbConnectionStringBuilder["Driver"] = "Microsoft Access Driver (*.mdb)"; |
When you want connect to a MySql database you will need the MySql-Connector for .Net. You can find it at the page linked below.
http://www.mysql.de/downloads/connector/net/
The instantiation for the connection is provided by MySql.Data.MySqlClient.MySqlConnection and MySql.Data.MySqlClient.MySqlCommand.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 |
//We use a Common.DbConnection as object an instantiate it as SqlConnection //At this point we will define which type of database we use, eg. Access,MySql,Sql, etc. //Notice: DbConnection is usable with the using-directive System.Data.Common.DbConnection DbConnection = new System.Data.SqlClient.SqlConnection(); //DbCommand is for sending the queries to se database. It will be instantiated as SqlCommand in this case System.Data.Common.DbCommand DbCommand = new System.Data.SqlClient.SqlCommand(); //Subsequently we assign the connection to the command DbCommand.Connection = DbConnection; //DbConnectionStringBuilder is for easy creation of ConnectionStrings. //You can work without this and assign the connectionstring directly to the constructor of SqlConnection //For connectionstrings I recommend: <a href="http://www.connectionstrings.com/" title="http://www.connectionstrings.com/" target="_blank">http://www.connectionstrings.com/</a> //In our case we will connect to a Sql Server 2005 System.Data.Common.DbConnectionStringBuilder DbConnectionStringBuilder = new System.Data.Common.DbConnectionStringBuilder(); //Assign the server DbConnectionStringBuilder["Server"] = "localhost"; //Assign the database. You can change this with DbConnection.ChangeDatabase or directly per query DbConnectionStringBuilder["Database"] = "Northwind"; //Integrated Security means, that we will authentificate at the sql server with windows authentification //In the other case we have the specify "User ID" and "Password" for authentification DbConnectionStringBuilder["Integrated Security"] = "SSPI"; //Now we assign the connection string to the connection DbConnection.ConnectionString = DbConnectionStringBuilder.ConnectionString; try { //Open the connection DbConnection.Open(); //To be safe we check whether the connection is open before we send the query if (DbConnection.State == System.Data.ConnectionState.Open) { int Return = 0; //We have a table TEST and a column "ID" and "Value" where we want to insert a new dataset DbCommand.CommandText = "INSERT INTO dbo.TEST (ID, Value) VALUES (3,'This is a test');"; //ExecuteNonQuery returns the count of affected rows. For SELECT-statements it will always return -1 Return = DbCommand.ExecuteNonQuery(); //Now the return value should be 1 } //If you work with dispose, the Dispose-Method will also close the connection //For demonstration purpose we will close the connection directly DbConnection.Close(); } //Dispose the connection finally { DbConnection.Dispose(); } |
Comments are currently closed.