using mysql with asp_net sample code for handing database operations mysql asp_net

• Posted by Admin on February 23, 2009
• 65 comments

Using MYSQL with any .net languages is very easy. There is one component developed by MYSQL that offers seemless interaction between any .net language and mysql server.

All you need is to download mysql.data.dll from mysql website, which is known as mysql connector net.

Here is the sample code that fetches the datatable from the mysql database.

string ConnectionString = "your mysql connection string";
  
        public DataTable GetDataTable(string Query)
        {

            MySql.Data.MySqlClient.MySqlConnection SqlConn = new MySql.Data.MySqlClient.MySqlConnection(ConnectionString);
            MySql.Data.MySqlClient.MySqlCommand SqlCmd = new MySql.Data.MySqlClient.MySqlCommand(Query, SqlConn);
            MySql.Data.MySqlClient.MySqlDataAdapter SqlDataAdt = new MySql.Data.MySqlClient.MySqlDataAdapter();
            DataSet ResultSet = new DataSet();
            DataTable Result = new DataTable();
            try
            {
                SqlCmd.CommandType = CommandType.Text;
                SqlCmd.CommandTimeout = 30;

                SqlDataAdt.SelectCommand = SqlCmd;

                if (SqlConn.State == ConnectionState.Open)
                {
                    SqlDataAdt.Fill(ResultSet, "Result");
                    Result = ResultSet.Tables["Result"];
                }
                else if (SqlConn.State == ConnectionState.Closed)
                {
                    SqlConn.Open();
                    SqlDataAdt.Fill(ResultSet, "Result");
                    Result = ResultSet.Tables["Result"];
                }
                else
                {
                    SqlConn.Close();
                    SqlConn.Open();
                    SqlDataAdt.Fill(ResultSet, "Result");
                    Result = ResultSet.Tables["Result"];
                }
                return Result;
            }
            catch (Exception ex)
            {
                Result.Dispose();
                ResultSet.Dispose();
                SqlDataAdt.Dispose();
                SqlCmd.Dispose();
                SqlConn.Close();
                return null;
            }
            finally
            {
                Result.Dispose();
                ResultSet.Dispose();
                SqlDataAdt.Dispose();
                SqlCmd.Dispose();
                SqlConn.Close();
            }
        }

 Make sure that the dll file MySQL.Data.dll is visible to your application in which you want to use the mysql database.

generating unique randon verification strings keys

• Posted by Admin on February 10, 2009
• 153 comments

For database driven web applications, when user signs up to your website, you may need to verify the email address of the person who signed up in your website.  You may send the verification email to the person's email address with the link that he/she needs to link in order to verify the email adress.

The key has to be supplied for security reasons; for example, http://example.com/verifyemail.aspx?key=--somekeytext--

To achieve this, you need to generate random but unique keys to avoid the inconsistency and maintain the security. Here is the code to generate the unique strings. You can then store this key in the user table and match it with the key text in the verification link.

public string GetUniqueKey()

{

  string Key = System.Guid.NewGuid().ToString();

  Key = Key.Replace("-", "");

  return Key;

}

These are the random keys that are generated using above code.

3fe76f2b27d04f518642852baabfbb90
8b579452ca2240a3b48bc87c6077614f
fc68e18bf57a4c498b112ca935257e92
447733fe67514f0d9f495105a0eadeaf
2d5469e00df84bfaacb0233560983a08

sample code for retrieving datatable from the database

• Posted by Admin on February 7, 2009
• 295 comments

In the routine programming of database driven application, we may frequently need to access the a some rows/records of a particular table(s). Hence, it is better to develop one public function that retrieves data table whenever needed.

Here is the basic code that fetches the data table according to the SQL supplied.

    public class ClassDataTable

    {

        string ConnectionString = "Your connection string here";    

        public DataTable GetDataTable(string Query)

        {

            SqlConnection SqlConn = new SqlConnection(ConnectionString);

            SqlCommand SqlCmd = new SqlCommand(Query, SqlConn);

            SqlDataAdapter SqlDataAdt = new SqlDataAdapter();

            DataSet ResultSet = new DataSet();

            DataTable Result = new DataTable();

            try

            {

                SqlCmd.CommandType = CommandType.Text;

                SqlCmd.CommandTimeout = 30;

                SqlDataAdt.SelectCommand = SqlCmd;

                if (SqlConn.State == ConnectionState.Open)

                {

                    SqlDataAdt.Fill(ResultSet, "Result");

                    Result = ResultSet.Tables["Result"];

                }

                else if (SqlConn.State == ConnectionState.Closed)

                {

                    SqlConn.Open();

                    SqlDataAdt.Fill(ResultSet, "Result");

                    Result = ResultSet.Tables["Result"];

                }

                else

                {

                    SqlConn.Close();

                    SqlConn.Open();

                    SqlDataAdt.Fill(ResultSet, "Result");

                    Result = ResultSet.Tables["Result"];

                }

                return Result;

            }

            catch (Exception ex)

            {

                Result.Dispose();

                ResultSet.Dispose();

                SqlDataAdt.Dispose();

                SqlCmd.Dispose();

                SqlConn.Close();

                return null;

            }

            finally

            {

                Result.Dispose();

                ResultSet.Dispose();

                SqlDataAdt.Dispose();

                SqlCmd.Dispose();

                SqlConn.Close();

            }

        }

    }

the Above function will fetch the datatable according to the SQL supplied.