Run TSQLScripts via the Web Page in c# ASP.Net

TTCG

I am trying to build the Web Page which can be used to run the TSQL scripts online.

My DB has so many databases and I am using the System Administrator account to execute these scripts.

After building the web page,

  1. I can run the simple CRUD statements successfully in all databases
  2. I can run the DML statements only for the default connected database, which is MyDB1.
  3. Whenever, I create the new Object, it sits in the MyDB1. But I want to run / create objects in another DB.

I tried to run the following command, which is very simple and it works perfectly in SSMS.

USE MyDB2
GO

CREATE PROCEDURE [dbo].MyProc

AS
BEGIN
    INSERT INTO
        dbo.Table1
        (
        FieldA
        , FieldB
        , FieldC
        )
    VALUES
        (
        'AAA'
        , 'BBB'
        , 'CCC'
        )

END

But I am getting the error message:

'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.

My c# code to execute the posted script is here:

DataTable dt = new DataTable();
            try
            {
                using (SqlConnection cn = new SqlConnection("MyConnString"))
                {
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(sql, cn);

                    using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        if (dr.HasRows)
                        {
                            dt.Load(dr);
                        }

                        dr.Close();
                    }

                    cn.Close();
                }
            }
            catch (Exception ex)
            {
                //Log Error
            }

If I removed, USE statement, it was ok, but it created the object in the default DB which is MyDB1. How I can tweak my code to run it in MyDB2?

HABO

USE is an SSMS command, not a SQL statement.

Change your connect string to specify the appropriate database.

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

I want to detect a usb device via asp.net C# web page

Run stored procedure in the background while ASP.NET C# page goes to a different page

What is the equivalent of Web Forms "Page_Load" in ASP.NET Core 5, so my code will run before any page loading?

How run stored procedure in ASP.NET Core 6 Web API using C#?

Can we call Page_Load event in static web method using C# and ASP.NET

C# screen scraping an ASP.NET web forms page - POST request not completely working

asp.net web page pass parameter to another page include "#"

Perform second popup via first popup page in asp.net

Run a javascript function after ASP.NET page load is completed

My first .net Owin program fail to run and show the web page

How to call an asp.net web application via command line?

Asp.Net Identity connecting to DB via Web Api

How to redirect via web.config ASP.NET

Is it possible to run asp.net and java web applications in the IIS server?

Run Angular and ASP.NET Web API on the same port

Run ASP.NET5 web application with Kestrel server on Windows

ASP.NET C# GetFontFamilies on web

view nlog's output as a web page with asp.net

How to handle two different forms in an asp.net web page?

Is it possible to wrap an ASP.Net Core web page with Cordova?

Looking for Tools to Create ASP.NET Web Page Layouts

Setting start page in web hosting ASP.NET

Doing heavy task and report progress in asp.net web page

URL encryption of an URL aliased asp.net web page

Redirecting to another web page instead of default Url in ASP.NET

Add Control to ASP.NET Web Page with runatserver

Order menu bar drop down on ASP.NET web page

Show ASP.NET 5 error page in Azure web app

ASP.NET Web API Help page under separate project