How to get connection strings to various databases in ASP.NET 2.0

How to use connection strings to various Databases in ASP.NET 2.0

Some of the values and strings are used across all the pages of a website. It is a tedious process to code those values and strings in each page of the website.

The connection string will be used in many pages of the web site if you are developing a database driven website. Let us say that you have hard coded the connection string in all the pages, around 30 pages. Now if the value of the connection string changes, you have a headache of changing all the values of the connection string in all the 30 pages. This can’t be done easily and if you are to change the string again after about one month, it is going to be a tedious task.

Hence in such scenarios it is a practice to store those values in a central repository from where you can retrieve it and use it in all the pages. For example if you store the connection string in a central place like a web.config file you can retrieve those connection strings from the web.config file and use it. Previous to ASP.Net 2.0 the connection strings were stored in web.config file as given below:

<configuration>
<appSettings>
<add key=”connectionString” value=”connection_string_value_here” />
</appSettings>

<system.web>
…
</system.web>
</configuration>

The connection string is stored as key and value pair in the <appSettings> element of the web.config file. This value can be retrieved by writing code like:

ConfigurationSettings.AppSettings(“connectionString”)

Instead of using the <appSettings> section you can also add your own sections in the web.config file to have all your string value that you will be using in your application.

ASP.Net 2.0 provides with a special class for retrieving those customized string values.

This class is called the ConfigurationManager class.

Using this class you will be retrieving values by writing the following code for the above config file.

ConfigurationManager.AppSettings["connectionString"]

To open the ConnectionStrings section of the web.config file, you can use code like

ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString

The above code is used to retrieve ConnectionStrings from the web.config file as given below:

<connectionStrings>
<add name=”connectionString” connectionString=”Data Source=.;Initial Catalog=db_products;Integrated Security=True” providerName=”System.Data.SqlClient” />
</connectionStrings>

This code comes under the <configuration> section of the web.config file.

Here you can use the connectionStrings tag to store multiple Connection string to multiple databases and use it at your might.

Like:

<connectionStrings>
<add name=”connectionString” connectionString=”Connect_String_Value” providerName=”Connection_Provider” />

<add name=”myCString” connectionString=”Connect_String_Value” providerName=”Connection_Provider” />

<add name=”newCString” connectionString=”Connect_String_Value” providerName=”Connection_Provider” />
</connectionStrings>

You can use it in you code like :

1. ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString

2. ConfigurationManager.ConnectionStrings["myCString"].ConnectionString

3. ConfigurationManager.ConnectionStrings["newCString"].ConnectionString

Happy Connecting to database

Pagination of Data using PHP-MySQL

Pagination of Data (Paging) using PHP (Hyper Text Pre Processor) and MySQL (the great database)

 

Note: I wrote this post first on October , 2007 , nearly 3 years back and since then this post got a lot of audiences. Surely this topic is one of the good ones for the newbies. After reading the responses for this post I saw that users are facing some errors and after a long while I got some time to have a second look. So I have again installed PHP, APACHE, MYSQL and MYSQL WorkBench in my local machine and tested the script like a user. I have found some new bugs and made the modifications accordingly. Now the Script is completely new and tested fully.

To simplify I tried to use some out of box functionality so that one can run the example right away.

For the sample database I have used “SAKILA” database from MySQL. I have only used the “Customer” table for the example, but this can be extended to a bigger example. Hope you’ll find this useful.

First: Install the SAKILA Database to your local MySQL instance. You can download it from here. We will be using the CUSTOMER table. Here is the table for your reference:

CREATE TABLE customer ( customer_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, store_id TINYINT UNSIGNED NOT NULL, first_name VARCHAR(45) NOT NULL, last_name VARCHAR(45) NOT NULL, email VARCHAR(50) DEFAULT NULL, address_id SMALLINT UNSIGNED NOT NULL, active BOOLEAN NOT NULL DEFAULT TRUE, create_date DATETIME NOT NULL, last_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (customer_id), KEY idx_fk_store_id (store_id), KEY idx_fk_address_id (address_id), KEY idx_last_name (last_name), CONSTRAINT fk_customer_address FOREIGN KEY (address_id) REFERENCES address (address_id) ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT fk_customer_store FOREIGN KEY (store_id) REFERENCES store (store_id) ON DELETE RESTRICT ON UPDATE CASCADE )ENGINE=InnoDB DEFAULT CHARSET=utf8;

Second: This table already have a lot of data. You can verify it by the simple select command.

Initially it has nearly 599 rows. A very good amount of data to test our code.

Third: Create and Test this script

Create a file named pagination.php and put the below contents to that file. Upload that file to your local server or main server.

I have uploaded that on my local server where php is running. If you are testing this script elsewhere then change http://localhost with your server host name.

You’ll see that I have used a variable called $page_name to hold the page name with full address. This is because the link to the page is being used multiple times inside the coding. So, only one place to change your page name, rest will be taken care of automatically.

<?php //Global Variable:  $page_name = “http://localhost/pagination.php”; //REMEMBER TO CONNECT TO DATABASE! $link = mysql_connect(‘localhost’,'root’,'rootpassword’) or die(“Error connecting to Database: ” . mysql_error()); mysql_select_db(‘sakila’) or die(“Error Selecting Database: ” . mysql_error()); //**EDIT TO YOUR TABLE NAME, ETC. $t = mysql_query(“SELECT count(*) as cnt FROM customer”); if(!$t){ die(“Error quering the Database: ” . mysql_error()); } $a = mysql_fetch_object($t); $total_items = $a->cnt; echo “Total Number of records in Database: “.$total_items; echo “<br/>”; $limit= (isset($_GET["limit"])) ? $_GET["limit"] : 10; $page= (isset($_GET["page"]))? $_GET["page"] : 1; echo “This is Page Number: ” . $page . “<br/>”; echo “Current Limit: “. $limit. “<br/>”; //Set defaults if: $limit is empty, non numerical, //less than 10, greater than 50 if((!$limit) || (is_numeric($limit) == false) || ($limit < 10) || ($limit > 50)) { $limit = 10; //default } //Set defaults if: $page is empty, non numerical, //less than zero, greater than total available if((!$page) || (is_numeric($page) == false) || ($page < 0) || ($page > $total_items)) { $page = 1; //default } //calculate total pages $total_pages = ceil($total_items / $limit); $set_limit = ($page * $limit) – $limit; echo “Total Pages: $total_pages <br/>”; //query: **EDIT TO YOUR TABLE NAME, ETC. $q = mysql_query(“SELECT * FROM customer LIMIT $set_limit, $limit“); if(!$q) die(mysql_error()); $no_row = mysql_num_rows($q); if($no_row == 0) die(“No matches met your criteria.”); //Results per page: echo(“<br/>Records Per Page: <a href=$page_name?limit=10&page=1> 10</a> | <a href=$page_name?limit=25&page=1> 25</a> | <a href=$page_name?limit=50&page=1> 50</a><br/> “); //show data matching query: while($object = mysql_fetch_object($q)) { //print your data here. echo(“<br/>————————————–<br/>”); echo(“First Name: “.$object->first_name.”<BR/>”); echo(“Last Name: “.$object->last_name.”<BR/>”); echo(“Email: “.$object->email.”<BR/>”); echo(“————————————–<br/>”); } //Results Per Page: Same as earlier one echo(“<br/>Records Per Page: <a href=$page_name?limit=10&page=1> 10</a> | <a href=$page_name?limit=25&page=1> 25</a> | <a href=$page_name?limit=50&page=1> 50</a><br/> “); //prev. page $prev_page = $page – 1; if($prev_page >= 1) { echo(“<b><<</b> <a href=$page_name?limit=$limit&page=$prev_page> <b>Prev.</b></a>”); } //Display middle pages: for($a = 1; $a <= $total_pages; $a++) { if($a == $page) { echo(“<b> $a</b> | “); //no link } else { echo(“ <a href=$page_name?limit=$limit&page=$a> $a </a> | “); } } //Next page: $next_page = $page + 1; if($next_page <= $total_pages) { echo(“ <a href=$page_name?limit=$limit &page=$next_page><b>Next</b></a> > >”); } //all done ?>

Four: Run this script in browser and see pagination with php and mysql in action.

Explanation: First this script will query the database and get total number of rows by the “SELECT count(*) as cnt FROM customer ” query. It will return the count as cnt. Now get the count and store it in the variable $total_items.

Now get the limit and page number from the GET variables. For the first time both will be null, so by default we will set the limit to 10 and page to 1.

If this variables are not blank, i.e. if the users click on the next or previous or page links then the page number and number of items to displayed will be not null and we will display data accordingly.

If some how this limit and page values are greater than the specified values or less than the specified values we will set them as default.

Now we will calculate how many pages the data will span. i.e. if we select 10 data to displayed at one time and total number of data are 85 then there will be 9 pages and by default the first page will be displayed. In this way if we choose to display 25 data at one time then the total number of pages will be 4, first 3 will display 25 data each and the last will display 10 data.

Hope this tutorial will help you to explore the vast world of pagination using php and mysql.

Kindly send me any queries that you might have.

 

Here is the code to download. Its currently in a Word Document.