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... .
Follow