How to Create Sql Query Analyzer Tool in Asp.net


I want to discuss an article with you regarding about how to Create the query analyzer Tool (i.e. same as Sql Query Analyzer Tool) using asp.net.I will briefly discuss with some snippet of code lines.

your working sql-server is under in data migration so that you can n't able to open that Sql-server Query Analyser window.But you need to work on that database.For that you need to create Sql-Query Analyzer tool.So How to create Query Analyzer tool using Asp.net.I will illustrate with some snippet of code lines

So for that You need a textbox and a button in asp.net.than in stored Procedure You write the Below snippet of Code Lines in the sqlserver


Create Procedure Buildquery
(
@query varchar(max)
)
as
Begin
Exec(@query)
End


In this stored procedure we are passing the query as input parameter.which is supplied by the asp.net Textbox.Exec() function will evaluate and execute the query in the sqlserver query analyzer.and the result will be displayed through datagrid or no.of rows effected for insertion/Deletion/Updation.

Now the below code will illustrate .How to pass the Parameter in sql-server


protected void btnclick_Click(Object o, senderEventargs e)
{
try
{
Datatable dt =new Datatable();
Sqlconnection con =new SqlConnection();
con.open();
SqlCommand cmd = new SqlCommand("Buildquery", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.parameters.Add.withvalue("@query", txtquery.text);
cmd.ExecuteNonquery();
sqldatadapter sqladapt =new sqldataadapter(cmd);
adapt.fill(dt);
if(dt.rows.count>0)
{
Gridview.Datasource =dt;
Gridview.DataBind();
}

}
Catch(Excetption ex)
{
lbltext.Text = ex.Message().tostring();
}
}


Now for some Users you grant some Permissions insert/Update/Delete/Drop.You have to write the below snippet of code before executing the query from front end.


if(Session["UserName"] != "Admin")
{

if(txtquery.text.contains("Insert") || txtquery.text.contains("Delete") ||txtquery.text.contains("Drop"))
{

lbl.text="You have no Permissions to do these Operations"
}

}



Note : if you have bulk of records 50,000 or l lakh.Filter the Query with where or using top e.t.c... .


Article by srirama
A Good advice from parent to a Child , Master to a Student , Scholar to an Ignorant is like a doctor prescribed pill it is bitter to take but when they take it will do all good for them --- Bhushan

Follow srirama or read 73 articles authored by srirama

Comments

No responses found. Be the first to comment...


  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: