Database Programming with ASP.NET C# & MySql

Today I find some time to discuss Database Programming with ASP.NET C# & MySql

A BRIEF INTRODUCTION ON ASP.NET C#, MYSQL AND WINDOWS STACK

Software Requirements

1. MySQL DB with Work Bench
   DOWNLOAD LINK: http://www.mysql.com/downloads/
2. MySql Connector for .NET
   DOWNLOAD LINK: http://www.mysql.com/downloads/connector/net/
   Download the binary and install to your machine where you need to run your application.
3. .NET Platform / Visual Studio
   DOWNLOAD LINK: http://msdn.microsoft.com/hi-in/netframework/default.aspx
   You may download and install the .NET framework of your required version 1.1/2/3.5 or 4.
   This article supports all the above mentioned versions.

MySql Database Creation and Table Creation
I assume that you can create MySql Database and Tables I proceed to the C# Programming part

Web.config File
The following is a simple connection string statement I use in my development practice. You may also ignore Web.config and write the connection string inline on the code page. But it is not recommended as it is vulnerable to disclose your database credentials (Database Username and Password) in the code page. Say you may have more than one developers working on the project and you many not want to share the database credentials with them, this practice will help you the most. Apart from this the content in the web.config file is more secured than content in other pages by the ASP.NET architecture.

Syntax:

<add  name="<give some name to call this connection string>"
              connectionstring="server=<server ip address or name>
              initial catalog=<database name>
              uid=<database username="">;
              pwd=<database password>"
              providerName="MySql.Data.MySqlClient"/>

Example:

<add  name="MyConnection1"
              connectionstring="server=localhost;
              initial catalog=mysampledb; 
              uid=root;
              pwd=mypassword" 
              providerName="MySql.Data.MySqlClient"/>

ASP.NET C#
Here I use ASP.NET and C# to explain the database programming with MySql. I assume that you have basic knowledge of ASP.NET and C# programming.

Go to the code behind and proceed with the following code.

1. Incluse the usings.
   
   using MySql.Data.MySqlClient;
   
   
2. Declare the private variables inside the main class (pls not the class not the main function)
   
   //CREATING CONNECTION INSTANCE / NOTE: CONNECTION WILL NOT OPEN HERE
   MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings[
                         "myconnstr"].ConnectionString);
   //CREATING INSTANCE OF COMMAND AND DATA READER / REQUIRED FOR ACCESSING DATA MANUALLY
   MySqlCommand cmd;
   MySqlDataReader dr;//CREATING INSTANCE OF DATA SET / REQUIRED FOR BINDING DATA WITH DATAGRID/DATALIST
   DataSet ds = new DataSet();
   
3. Opening Database Connection
   
   if (con.State == ConnectionState.Closed)
   {
     con.Open();
   }
   
4. Adding Data to the Database
   
   string qry = "INSERT INTO sample_table(first_name, last_name)";
   cmd = new MySqlCommand(qry, con);
               cmd.ExecuteNonQuery();

Introduction to C# / ASP.NET with C#

ASP.NET is a Microsoft technology for web applications. ASP stands for Active Server Pages. As the name specifies the pages are programs that are stored and executed on the server. When a call is made to the page the page gets compiled (if not already compiled) and returned to the client browser as a file in HTML and JavaScript combination. The .NET platform is a proven Microsoft technology for applications. ASP.NET is limited to Windows platform. We will discuss more about the ASP.NET and C# in a separate article soon.

Introduction to MySql Database
MySql is an opensource database backed by Oracle Corporation. It is a powerful and efficient database that you can rely on it for your enterprise needs. Most companies prefer MySql for their data storage needs and high transaction processing. MySql is reliable and programmer friendly too. It has a large user base and strong community to support. However, the PHP & MySql combination is the best pair on internet! It is not that C# doesn't go well with MySql but the opensource PHP beat the C# or any other language on Internet. Let us not worry about that! We have enough articles and resources for C# integration and programming with MySql and abundant help content for MySql. We will discuss more about the MySql in a separate article soon.

How to fetch nth row from SQL Server 2008 R2 / 2005

Example Code:

Note: Code explained below

WITH temp_table AS
(
    SELECT TOP (SELECT COUNT(*) FROM [table_1])
        firstname, lastname, mobile, email,
        ROW_NUMBER() OVER (ORDER BY zip_code) AS row_no
    FROM
        [table_1]
    ORDER BY zip_code
)
SELECT
    first_name,
    last_name,
    email
FROM
    temp_table
WHERE
    row_no BETWEEN 1 AND 10

Code Explanation:

WITH temp_table AS

--create a temporary table named temp_table
(
    SELECT TOP (SELECT COUNT(*) FROM [table_1])

    --table_1 is the original table

    --top is an essential keyword when you use orderby keyword in this sub query else you can ignore TOP keyword

    -- I have fetched the rows count to fetch the entire rows, this means no difference when considered as a independent query but just to obey the inner query Syntax, that is TOP keyword should be used while using order by in the sub query
        firstname, lastname, mobile, email,

        --just fetching some fields
        ROW_NUMBER() OVER (ORDER BY zip_code) AS row_no

        --generate row number with a custom field in the temp_table for the rows fetched from the original table
    FROM
        [table_1]

        --original table
    ORDER BY zip_code

    --order by inside sub query is the complex part. I remind, use TOP keyword when using order by keyword.
)
SELECT
    first_name,
    last_name,
    email

    --simple select query from the temp_table
FROM
    temp_table
WHERE
    row_no BETWEEN 1 AND 10

    --now we can set between values to fetch records. Hope this is self explanatory, you can set 11 and 20 to fetch the next set of 10 records and so on.

Please share your comments and suggestions. You may also post your questions and doubts here.

How to fetch first 10 rows in SQL Server 2008 R2 / 2005

It is pretty simple to fetch first n records in SQL Server 2008 R2. Following is the syntax and example SQL Query to fetch the first 10 rows from a SQL Server 2008 R2 / 2005 database.

Syntax:

SELECT TOP (n) * FROM

--n could be any unsigned bigint value
-- You may also use TOP n without parenthesis. Using TOP keyword without parenthesis gives backward compatibility with SQL Server 2000 but I guess most don't require such a backward compatibility. So it is recommended by Microsoft to use TOP with parenthesis.


For advanced users: 

n is actually an expression. That is, it is capable of performing calculations to derive the final number of records to be retrieved.

Optionally you may also use PERCENT keyword next to the n to denote the input for fetching number of records is on percentage. That is, the following Example 2 will fetch 10 records from a table containing 100 records. When n is used as percentage, n will be treated as float.

Example 1:

SELECT TOP (10) * FROM  employees



Example 2:

SELECT TOP (10) PERCENT * FROM employees

It is as simple as above. I am publishing this on my blog because this is the most frequent database related question most people asked me.