C#SQL

MySQL Import ASP.NET C#

ASP.NET C# is a powerful that I have use for a long time. Due the limitation that PHP have on thread programming with background process, therefore PHP need to bring the long processing job the font process before sending the website the browser. In ASP.NET C# I can put the time consuming job the background thread and then update the browser using AJAX integrate with ASP.NET (I know PHP has AJAX, don’t get me wrong with this). But PHP is an interpreter language, while .NET is compiled language.

Why I started programming this code?

After trying to restore a database of mine to the eNom web hosting, their PhpMyAdmin is so bad that i try to restore of 334k i get a CGI error. Also after trying the phpdump.php (i don’t remember where this from), again i get the max_execute_limit = 300 seconds. Dam it, why does eNom MySQL database so slow. GoDaddy didn’t have any issues at all with restoring my database (GoDaddy Windows IIS7 yet has issue with custom php.ini).

Requirements of the web application

·          .NET Framework 2.0

·          ASP.NET AJAX 1.0 Extension

·          MySQL Connector/.NET

Codes of the MySQL Import

·          Default.aspx

·          Default.aspx.cs

·          App_Data the SQL script folder

Default.aspx



    Sathai MySQL Import ASP.NET
    
    


    


Hostname:
Database:
Username:
Password:

Query file:

SQL Processing from

Default.aspx.cs

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Collections.Generic;
using System.Threading;
using System.IO;
using System.Text;
using System.Collections;
using System.Text.RegularExpressions;
using MySql.Data.MySqlClient;

public partial class _Default : System.Web.UI.Page
{
    public static string sqlHostname;
    public static string sqlDatabase;
    public static string sqlUsername;
    public static string sqlPassword;
    public static string sqlFile;
    public static string sqlError;
    public static ArrayList query;
    public static int sqlProgress;

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            LabelHead.Text = "Sathai MySQL Import ASP.NET 2.0 AJAX";

            
            sqlHostname = "hostname";
            sqlDatabase = "database";
            sqlUsername = "username";
            sqlPassword = "password";

            sqlError = "";
            sqlFile = "SQLQuery2.sql";
            sqlProgress = 0;

            LabelHostname.Text = sqlHostname;
            LabelDatabase.Text = sqlDatabase;
            LabelUsername.Text = sqlUsername;
            LabelPassword.Text = sqlPassword;
            LabelFile.Text = sqlFile;

            Regex CommentRegex = new Regex("^--");
            query = new ArrayList();
            query.Add("");

            StreamReader sr = new StreamReader(Request.PhysicalApplicationPath + @"\App_Data\" + sqlFile);
            int x = 0;
            while (!sr.EndOfStream)
            {
                string line = sr.ReadLine();

                Match m = CommentRegex.Match(line);
                if (m.Success)
                {
                    if (query[x].ToString() != "")
                    {
                        x++;
                        query[x] = "";
                    }
                }
                else
                {
                    if (line.StartsWith("DROP") || line.StartsWith("INSERT") || line.StartsWith("CREATE"))
                    {
                        query.Add(line);
                        x++;
                    }
                    else
                    {
                        string previous = query[x].ToString();
                        query.RemoveAt(x);
                        query.Add(previous + line);
                    }
                }
            }
            sr.Close();

            LabelTotalLine.Text = string.Format("{0:0,0}", query.Count);

            //foreach(string line in query)
            //    LabelProgress.Text += line;

            ThreadStart job = new ThreadStart(ThreadJob);
            Thread thread = new Thread(job);
            thread.Start();
        }
        else
        {
            LabelProgress.Text = string.Format("{0:0,0}", sqlProgress);
            LabelError.Text = sqlError;
        }

    }

    public static void ThreadJob()
    {
        string MyConnString = string.Format("SERVER={0}; DATABASE={1}; UID={2}; PASSWORD={3};", sqlHostname, sqlDatabase, sqlUsername, sqlPassword);
        MySqlConnection MyConn = new MySqlConnection(MyConnString);
        MyConn.Open();

        int i = 0;
        while (isSet(query, i))
        {
            string iLine = query[i].ToString();//.Split(';');
            int j = 0;
            //while (isSet(iLine))
            //{
            if (iLine.Trim() != "")
            {
                try
                {
                    MySqlCommand MyCommand = MyConn.CreateCommand();
                    MyCommand.CommandText = iLine + ";";

                    int result = MyCommand.ExecuteNonQuery();

                }
                catch (Exception ex)
                {
                    sqlError += "

" + ex.Message + "


"; //LabelError.Text += "

" + iLine + "


"; } } else { } sqlProgress++; j++; //} i++; } MyConn.Close(); sqlError += "

DONE

"; } private static bool isSet(object o) { // check if the object is null if (o == null) return false; else return true; } private static bool isSet(ArrayList o, int index) { if (!(index < o.Count)) return false; // check if the object is null if (o[index] == null) return false; else return true; } private static bool isSet(string[] o, int index) { if (!(index < o.Length)) return false; // check if the object is null if (o[index] == null) return false; else return true; } }

Well I hope this was useful for you, if have any question please leave me a comment.

Thanks