Click here to Skip to main content
11,581,186 members (63,603 online)
Click here to Skip to main content

Online Query Management Studio 1.0

, 22 Mar 2013 CPOL 7.7K 187 6
Rate this:
Please Sign up or sign in to vote.
OQMS 1.0 allows you to connect your server online and manage the table like Select, Insert, Update and Delete statements.

Introduction

Online Query Management Studio 1.0 [OQMS 1.0] can be run on browser and it allows the user to manage the database like insert, select, update and delete operations. OQMS is a simple application to understand. You can use this application as GUI for your database. In making this application, I use SQL Server 2005, .NET Framework 3.5 and C#. This article is meant for all kinds of beginner users who want to get started with SQL SERVER. I have included a small feather of SQL Server like insert. update, delete in OQMS.

Background

The basic idea behind making OQMS is that some time ago, I used a hosting panel where I created a table on server but server response was very slow and sometimes, the server was getting stuck and not responding. Then I made this web application, and through this application, you have to use your SQL SERVER which is intalled on your machine and it does DDl and DML operations.

Sample Image - maximum width is 600 pixels

Using the Code

In the login page. in the above image, I use the following namespaces:

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Data.Sql;
using System.Data.SqlClient;
using System.IO;
using System.Drawing;

And the C# code is:

    public partial class Entrance : System.Web.UI.Page
{
    SqlConnection con = new SqlConnection();
    protected void Page_Load(object sender, EventArgs e)
    {
    }
    protected void btncon_Click(object sender, ImageClickEventArgs e)
    {
        if (txtdbase.Text == "" || txtserv.Text == "" || txtuid.Text == "" || txtpass.Text == "")
        {
            Page.RegisterStartupScript("UserMsg",
            "alert('All field should be filled');if(alert){ window.location='Entrance.aspx';}");
        }
        else
        {
            Session["dbase"] = txtdbase.Text;
            Session["sname"] = txtserv.Text;
            Session["uid"] = txtuid.Text;
            Session["pass"] = txtpass.Text;
            Response.Redirect("~/Default.aspx", false);
        }
    }
} 

Sample Image - maximum width is 600 pixels

The above screenshot shows selecting database. When you select a database, then in table box, all table names are related to the database are shown.

Sample Image - maximum width is 600 pixels

The above screenshot shows selecting tablename. When you select tablename, then in Column box all column names related to table are shown.

Sample Image - maximum width is 600 pixels - Click to enlarge image

The above screenshot shows a written query in a query box.

Code Behind: Using these namespaces

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.Data.Sql;
using System.Data.SqlClient;
using System.IO;
using System.Drawing;

And the C# code is:

public partial class _Default : System.Web.UI.Page
{
    SqlConnection con = new SqlConnection();
    protected void Page_Load(object sender, EventArgs e)
    {
        string server = Session["sname"].ToString();
        string dname = Session["dbase"].ToString();
        string uid = Session["uid"].ToString();
        string pass = Session["pass"].ToString();
       con.ConnectionString = "Data Source="+server +";
       Initial Catalog="+ dname  +"; User ID=" + uid +"; Password=" + pass ;
        if(!IsPostBack)
        getdbase();
    }
    protected void btnreset_Click(object sender, EventArgs e)
    {
        querybox.Text = "";
        ListBox2.Items.Clear();
        GridView1.Dispose();
    }
    protected void btnexcu_Click(object sender, EventArgs e)
    {
        functions();
    }
    void functions()
    {
        try
        {
            if (querybox.Text != "")
            {
                if (hidSelectedText.Value == "")
                {
                    hidSelectedText.Value = querybox.Text;
                }
                if (hidSelectedText.Value != "")
                {
                    con.Open();
                    SqlCommand cmd = new SqlCommand("use " +
                    ddl.SelectedItem.ToString() + " " + hidSelectedText.Value, con);
                   // SqlCommand cmd = new SqlCommand(hidSelectedText.Value, con);
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    DataTable dt = new DataTable();
                    da.Fill(dt);
                    GridView1.DataSource = dt;
                    GridView1.DataBind();
                    con.Close();
                    lblmes.Visible = true;
                    war.Visible = false;
                    ok.Visible = true;
                    lblmes.ForeColor = Color.Green;
                    lblmes.Text = "Query executed successfully";
                }
                else
                {
                    lblmes.Visible = true;
                    ok.Visible = false;
                    war.Visible = true;
                    lblmes.ForeColor = Color.DarkGoldenrod;
                    lblmes.Text = "Query completed with error";
                }
            }
            else
            {
                querybox.Text = "Write some query or select some text...";
            }
        }
        catch (Exception  ex)
        {
            querybox.Text = ex.Message;
            lblmes.Visible = true;
            ok.Visible = false;
            war.Visible = true;
            lblmes.ForeColor = Color.Red;
            lblmes.Text = "Query completed with error";
            GridView1.Dispose();
        }
      }
    protected void ddl_SelectedIndexChanged(object sender, EventArgs e)
    {
        try
        {
            ListBox2.Items.Clear();
            con.Open();
            string qry = ddl.SelectedItem.ToString();
            SqlCommand cmd = new SqlCommand("USE "+qry  + 
            " select name from sysobjects where type='U' ", con);
            SqlDataReader dr = cmd.ExecuteReader();
            ListBox1.Items.Clear();
            while (dr.Read())
            {
                ListBox1.Items.Add(dr["name"].ToString());
            }
            con.Close();
        }
        catch(Exception ex)
        {
            querybox.Text = ex.Message;
        }
    }
    protected void ListBox1_SelectedIndexChanged(object sender, EventArgs e)
    {
        try
        {
            con.Open();
            string qry =  ListBox1 .SelectedItem.ToString();
            SqlCommand cmd = new SqlCommand("use " + ddl.SelectedItem.ToString() + "
            select Column_Name from INFORMATION_SCHEMA.COLUMNS _
            where TABLE_NAME = '" + qry + "'", con);
            SqlDataReader dr = cmd.ExecuteReader();
            ListBox2.Items.Clear();
            while (dr.Read())
            {
             ListBox2.Items.Add(dr["Column_Name"].ToString());
            }
            con.Close();
        }
        catch (Exception ex)
        {
            querybox.Text = ex.Message;
        }
    }
    void getdbase()
    {
        try
        {
             con.Open();
             SqlCommand cmd = new SqlCommand("SELECT name FROM master.dbo.sysdatabases", con);
             SqlDataReader dr = cmd.ExecuteReader();
             ddl.Items.Clear();
             while (dr.Read())
              {
                ddl.Items.Add(dr["name"].ToString());
              }
              con.Close();
              lblststus.ForeColor = Color.Green;
              lblststus.Text = "Connected";
        }
        catch (Exception ex)
        {
            querybox.Text = ex.Message;
        }
    }
}

History

Soon I will have a new version available!!!

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Shubham Choudhary
Software Developer
India India
Hi!!! I am Shubham Choudhary
I consider myself an enthusiastic programmer, but I simply don't have time to code in my spare time. I have a life besides coding, also! And when I code in my free time, I am mostly hired for it as well. But I do read lots of articles and books in my free time.

You may also be interested in...

Comments and Discussions

 
GeneralMy vote of 5 Pin
onurag1927-Mar-13 7:55
memberonurag1927-Mar-13 7:55 
GeneralRe: My vote of 5 Pin
Shubham Choudhary27-Mar-13 19:50
memberShubham Choudhary27-Mar-13 19:50 
GeneralMy vote of 4 Pin
Prasad Khandekar22-Mar-13 23:48
memberPrasad Khandekar22-Mar-13 23:48 
GeneralRe: My vote of 4 Pin
Shubham Choudhary24-Mar-13 3:47
memberShubham Choudhary24-Mar-13 3:47 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.150603.1 | Last Updated 22 Mar 2013
Article Copyright 2013 by Shubham Choudhary
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid