Run T-SQL commands from .net

Question

I have an application which can add new login to sql server and then map it to all the DB's and make it 'db_owner' (those are the requirements...). When I built the application , I used SMO and everything worked perfect. Now the requirements have changed , and I can't use SMO no longer :( So I need to run T-SQL commands from my application instead.... My problem is when I run the application nothing happens in the SQL server - the login is not added and it is not mapped to all the DB's .... but when I execute those lines in the SQL server management studio it works just fine...

those are the commands -

create login dbUser WITH PASSWORD = 'passsword';

use DB_MyLog;
create user dbUser for login dbUser;
use DB_MyLog
EXEC sp_addrolemember 'db_owner','dbUser'

In my application I run them like this -

string connString = "Data Source=" + ConfigurationManager.AppSettings.Get("localhost_SQLEXPRESS")
                                    + ";Initial Catalog=" + "master" + ";User ID=" + ConfigurationManager.AppSettings.Get("user")
                                    + ";Password=" + ConfigurationManager.AppSettings.Get("password");

        SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(connString);

        builder.AsynchronousProcessing = true;

        using (SqlConnection sqlConnection1 = new SqlConnection(builder.ConnectionString))
        {

            using (SqlCommand cmd = new SqlCommand("CREATE LOGIN " + ConfigurationManager.AppSettings.Get("dbUser") +
                                                    " WITH PASSWORD=N'" + ConfigurationManager.AppSettings.Get("dbUserPass") + "' "
                                                     , sqlConnection1))
            {

                sqlConnection1.Open();

                cmd.BeginExecuteNonQuery();

             }
        }

and in another method I run -

  string connString = "Data Source=" + ConfigurationManager.AppSettings.Get("localhost_SQLEXPRESS")
                + ";Initial Catalog=" + "master" + ";User ID=" + ConfigurationManager.AppSettings.Get("user")
                + ";Password=" + ConfigurationManager.AppSettings.Get("password");

        SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(connString);

        builder.AsynchronousProcessing = true;

        using (SqlConnection sqlConnection1 = new SqlConnection(builder.ConnectionString))
        {
            //get all databases
            using (SqlCommand cmd = new SqlCommand("SELECT * FROM sys.sysdatabases", sqlConnection1))
            {

                sqlConnection1.Open();
                SqlDataReader reader = cmd.ExecuteReader();

                // Call Read before accessing data.
                while (reader.Read())
                {
                    using (SqlConnection sqlConnection2 = new SqlConnection(builder.ConnectionString))
                    {
                        sqlConnection2.Open();
                        using (SqlCommand cmd1 = new SqlCommand("use " + reader[0].ToString() + ";" +
                                                                "create user " + ConfigurationManager.AppSettings.Get("dbUser") + " for login " + ConfigurationManager.AppSettings.Get("dbUser") + ";" +
                                                                "use " + reader[0].ToString() +
                                                                " EXEC sp_addrolemember 'db_owner','" + ConfigurationManager.AppSettings.Get("dbUSer") + "'"
                                                                , sqlConnection2))
                        {
                            cmd1.BeginExecuteNonQuery();

                        }

                    }
                }

                // Call Close when done reading.
                reader.Close();

            }
        }

If you use BeginExecuteNonQuery and an error occurs, it only becomes visible in the callback handler. Perhaps call ExecuteNonQuery instead of BeginExecuteNonQuery. — Andomar
– Andomar, Commented Jul 8, 2012 at 8:34
@Andomar When I changed BeginExecuteNonQuery to ExecuteNonQuery I was able to catch my exception and and to fix my error....now it works fine , thanks... If you add this suggestion to the answers I will accept it... — N.D
– N.D, Commented Jul 8, 2012 at 9:17

Andomar · Accepted Answer · 2012-07-08 09:23:19Z

1

If you use BeginExecuteNonQuery and an error occurs, it only becomes visible in the callback handler. Perhaps call ExecuteNonQuery instead of BeginExecuteNonQuery.

answered Jul 8, 2012 at 9:23

Andomar

239k55 gold badges387 silver badges412 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

devio · Accepted Answer · 2012-07-08 09:01:30Z

1

Rather than re-using the SqlConnectionStringBuilder builder, does it work if you set ";Initial Catalog=" + reader[0].ToString() for sqlConnection2? You could omit the USE statements then, issue two separate SqlCommands (CREATE USER, EXEC).

Separation of these steps might help you figure out which statement fails and on which database using a try/catch inside the while() loop.

answered Jul 8, 2012 at 9:01

devio

37.3k7 gold badges85 silver badges147 bronze badges

Collectives™ on Stack Overflow

Run T-SQL commands from .net

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related