11 Replies - 5205 Views - Last Post: 19 July 2010 - 10:50 AM Rate Topic: -----

#1 ItIntern3  Icon User is offline

  • D.I.C Head

Reputation: 6
  • View blog
  • Posts: 97
  • Joined: 14-July 10

Pass SqlCommand through Array

Posted 19 July 2010 - 04:55 AM

I'm trying to write an executeable INSERT SQL command in C#, and pass it through an array. I was able to establish a perfect connection with the database involved through an SQLConnectionString. I am able to run individual commands, effecting the database as desired, however I want to run say...1000 insert statements into a table without writing them all by hand. Here's the code I've been using:

namespace AutomateDatabaseUpdate
{
    class Program
    {
        //Allowing access to Master Database
        static private string GetConnectionString()
        {
            return "Data Source=serverName;Initial Catalog=Mark_Master;Integrated Security=True";
        }


        static void Main(string[] args)
        {
            string[] sCommand = new string[30];

            for (int i = 0; i <= 29; i++)
            {
                sCommand[i] = null;
            }

            //Connecting to the Master Database
            string connectionString = GetConnectionString();
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                Console.WriteLine("State: {0}", connection.State);

                // This is your command to execute:
                sCommand[0] = "Delete from Unit where UnitID > 15000";
               
                for (int i = 0; i <= 0; i++)                        //There is only one statement therefore only need to go from 0 to 0
                {
                    // This is your data adapter that understands SQL databases:
                    SqlDataAdapter da = new SqlDataAdapter(sCommand[i], connection);

                    // This is your table to hold the result set:
                    System.Data.DataTable dataTable = new DataTable();

                    try
                    {
                        // Fill the data table with select statement's query results:
                        int recordsAffected = 0;
                        recordsAffected = da.Fill(dataTable);
                        if (recordsAffected > 0)
                        {
                            foreach (DataRow dr in dataTable.Rows)
                            {
                                System.Console.WriteLine(dr[0]);
                            }
                        }
                    }
                    catch (SqlException e)
                    {
                        string msg = "";
                        for (int j = 0; j < e.Errors.Count; j++)
                        {
                            msg += "Error #" + i + " Message: " + e.Errors[j].Message + "\n";
                        }
                        System.Console.WriteLine(msg);
                    }
                    finally
                    {
                        if (connection.State != ConnectionState.Closed)
                        {
                            connection.Close();
                        }
                    }
                }               //For Loop
            }                   //Ends using                  

            Console.ReadLine();         

        }                                                 //Ends Main
    }
}




I think what I want to do involves rather than a type 'string[] sCommand' a 'SqlCommand sCommand'. I also think there's supposed to be a parameter involving the @ symbol, used to reference the 'i' in the for i loop.

What I want to do:
static void Main(string[] args)
        {
            SqlCommand[] sCommand = new SqlCommand[30];

            for (int i = 0; i <= 29; i++)
            {
                sCommand[i] = null;
            }
          
            //Connecting to Subscriber Database
            string connectionString1 = GetConnectionString1();
            using (SqlConnection connection1 = new SqlConnection(connectionString1))
            {
                connection1.Open();
                Console.WriteLine("State: {0}", connection1.State);
            }

            //Connecting to the Master Database
            string connectionString = GetConnectionString();
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                Console.WriteLine("State: {0}", connection.State);

                // This is your command to execute:
                for (int j = 0; j <= 20; j++)
                {
                    sCommand[0] = "Insert into unit (Description) values @1";
                    
                    for (int i = 0; i <= 0; i++)          //Statement used from program above
                    {
                        // This is your data adapter that understands SQL databases:
                        SqlDataAdapter da = new SqlDataAdapter(sCommand[j], connection);

                        // This is your table to hold the result set:
                        System.Data.DataTable dataTable = new DataTable();

                        try
                        {
                            // Fill the data table with select statement's query results:
                            int recordsAffected = 0;
                            recordsAffected = da.Fill(dataTable);
                            if (recordsAffected > 0)
                            {
                                foreach (DataRow dr in dataTable.Rows)
                                {
                                    System.Console.WriteLine(dr[0]);
                                }
                            }
                        }
                        catch (SqlException e)
                        {
                            string msg = "";
                            for (int j = 0; j < e.Errors.Count; j++)
                            {
                                msg += "Error #" + i + " Message: " + e.Errors[j].Message + "\n";
                            }
                            System.Console.WriteLine(msg);
                        }
                        finally
                        {
                            if (connection.State != ConnectionState.Closed)
                            {
                                connection.Close();
                            }
                        }
                    }           //For i Loop
                }               //For j Loop
            }                   //Ends using   



Any help on passing this command through the for loop is much appreciated.

This post has been edited by ItIntern3: 19 July 2010 - 05:17 AM


Is This A Good Question/Topic? 0
  • +

Replies To: Pass SqlCommand through Array

#2 ParagonDevx  Icon User is offline

  • New D.I.C Head

Reputation: 4
  • View blog
  • Posts: 17
  • Joined: 09-April 10

Re: Pass SqlCommand through Array

Posted 19 July 2010 - 06:15 AM

You are close.

The thing is, for each parameterized value in your SQL Statement (SqlCommand.CommandText) you need to create and add a SqlParameter object for the current SqlCommand object in the loop. As in:

 // This is your command to execute:
                for (int j = 0; j <= 20; j++)
                {
                    sCommand[j] = "Insert into unit (Description) values @1";
                    sCommand[j].Parameters.Add("@1",    System.Data.SqlDbType.VarChar).Value = "Description Value";
                }     




Where "Description Value" is the value to insert.

Also, get rid of the inner loop, it's unnecessary. You can perform
the whole thing using the first loop, like:

// This is your command to execute:
                for (int j = 0; j <= 20; j++)
                {
                    sCommand[j] = "Insert into unit (Description) values @1";
                    sCommand[j].Parameters.Add("@1", System.Data.SqlDbType.VarChar).Value = "Description Value";
                    
                    
                        // This is your data adapter that understands SQL databases:
                        SqlDataAdapter da = new SqlDataAdapter(sCommand[j], connection);

                        // This is your table to hold the result set:
                        System.Data.DataTable dataTable = new DataTable();

                        try
                        {
                            // Fill the data table with select statement's query results:
                            int recordsAffected = 0;
                            recordsAffected = da.Fill(dataTable);
                            if (recordsAffected > 0)
                            {
                                foreach (DataRow dr in dataTable.Rows)
                                {
                                    System.Console.WriteLine(dr[0]);
                                }
                            }
                        }
}





Do you want to perform the same command a thousand times or each of the commands has different SQL statements?

If you want to perform the same command a thousand times, you don't need a thousand SqlCommand objects, it would be more efficient to have only one SqlCommand object and execute it in a 1000 iteration loop.

If you want to perform a thousand different Sql statements, an array of strings containing them just like you had in the previous piece of code would work, just select the current one using a switch block or the loop control variable inside a loop....

This post has been edited by ParagonDevx: 19 July 2010 - 06:31 AM

Was This Post Helpful? 1
  • +
  • -

#3 ItIntern3  Icon User is offline

  • D.I.C Head

Reputation: 6
  • View blog
  • Posts: 97
  • Joined: 14-July 10

Re: Pass SqlCommand through Array

Posted 19 July 2010 - 06:40 AM

View PostParagonDevx, on 19 July 2010 - 07:15 AM, said:

You are close.

The thing is, for each parameterized value in your SQL Statement (SqlCommand.CommandText) you need to create and add a SqlParameter object for the current SqlCommand object in the loop. As in:

 // This is your command to execute:
                for (int j = 0; j <= 20; j++)
                {
                    sCommand[j] = "Insert into unit (Description) values @1";
                    sCommand[j].Parameters.Add("@1",    System.Data.SqlDbType.VarChar).Value = "Description Value";
                }     




Where "Description Value" is the value to insert.

Also, get rid of the inner loop, it's unnecessary. You can perform
the whole thing using the first loop, like:

// This is your command to execute:
                for (int j = 0; j <= 20; j++)
                {
                    sCommand[j] = "Insert into unit (Description) values @1";
                    sCommand[j].Parameters.Add("@1", System.Data.SqlDbType.VarChar).Value = "Description Value";
                    
                    
                        // This is your data adapter that understands SQL databases:
                        SqlDataAdapter da = new SqlDataAdapter(sCommand[j], connection);

                        // This is your table to hold the result set:
                        System.Data.DataTable dataTable = new DataTable();

                        try
                        {
                            // Fill the data table with select statement's query results:
                            int recordsAffected = 0;
                            recordsAffected = da.Fill(dataTable);
                            if (recordsAffected > 0)
                            {
                                foreach (DataRow dr in dataTable.Rows)
                                {
                                    System.Console.WriteLine(dr[0]);
                                }
                            }
                        }
}





Do you want to perform the same command a thousand times or each of the commands has different SQL statements?

If you want to perform the same command a thousand times, you don't need a thousand SqlCommand objects, it would be more efficient to have only one SqlCommand object and execute it in a 1000 iteration loop.

If you want to perform a thousand different Sql statements, an array of strings containing them just like you had in the previous piece of code would work, just select the current one using a switch block or the loop control variable inside a loop....


I would like to run 1000 different commands, I would like to add the j value of the for j loop to the Description column inside of the unit table.
For example:

j Value Description Column Value
0 0
1 1
1000 1000
2000 2000

So it sounds like I do need to create 1000 different objects?
Was This Post Helpful? 0
  • +
  • -

#4 ParagonDevx  Icon User is offline

  • New D.I.C Head

Reputation: 4
  • View blog
  • Posts: 17
  • Joined: 09-April 10

Re: Pass SqlCommand through Array

Posted 19 July 2010 - 07:01 AM

View PostItIntern3, on 19 July 2010 - 07:40 AM, said:

I would like to run 1000 different commands, I would like to add the j value of the for j loop to the Description column inside of the unit table.
For example:

j Value Description Column Value
0 0
1 1
1000 1000
2000 2000

So it sounds like I do need to create 1000 different objects?


In that case, this modification should do the job:



for (int j = 0; j <= 20; j++)
                {
                   SqlCommand sCommand = new SqlCommand("Insert into unit (Description) values @1");
                    sCommand.Parameters.Add("@1", System.Data.SqlDbType.VarChar).Value = (string)j; //Provide j as parameter value.



Again, in this case you don't need to create a 1000 different SqlCommand objects or whatever amount you wish because the for loop will execute the same set of statements over and over and it only needs one SqlCommand object. Each time it iterates, the for will insert the current value for j in the Description column of your unit table.

This post has been edited by ParagonDevx: 19 July 2010 - 07:14 AM

Was This Post Helpful? 0
  • +
  • -

#5 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5800
  • View blog
  • Posts: 12,635
  • Joined: 16-October 07

Re: Pass SqlCommand through Array

Posted 19 July 2010 - 07:15 AM

You seem to be missing the point of a bind variable. You just have to change the value; that's it.

e.g.
// create a command with defined connection
SqlCommand cmd = new SqlCommand("Insert into unit (Description) values @1", connection);
cmd.Parameters.Add("@1", System.Data.SqlDbType.VarChar); // define parameter
cmd.Connection.Open(); // open connection
try {
	for (int j = 0; j <= 20; j++) {
		cmd.Parameters.Value = j;
		cmd.ExecuteNonQuery();
	}
} finally {
	cmd.Connection.Close()
}


Was This Post Helpful? 0
  • +
  • -

#6 ItIntern3  Icon User is offline

  • D.I.C Head

Reputation: 6
  • View blog
  • Posts: 97
  • Joined: 14-July 10

Re: Pass SqlCommand through Array

Posted 19 July 2010 - 07:19 AM

Paragon: What is the .value (in the last line of your last post) for?

This post has been edited by ItIntern3: 19 July 2010 - 07:20 AM

Was This Post Helpful? 0
  • +
  • -

#7 JITHU  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 62
  • View blog
  • Posts: 201
  • Joined: 02-July 07

Re: Pass SqlCommand through Array

Posted 19 July 2010 - 07:26 AM

No, there is no need of creating 1000 different command objects.
You need to create only one SqlCommand object outside the for loop, and while you're in the loop, you can change the sql code associated with it by using the CommandText property.

string [] commands = new string[1000];
// TODO: Load your SQL commands

SqlCommand command = new SqlCommand();
command.Connection = connection;

for (int index = 0; index < commands.Length; ++index) 
{
    command.CommandText = commands[index];
    // Handle any parameters
    //command.Parameters.Add("@K", index); // Edit: This is obsolete now
    command.Parameters.Add("@K", SqlDbType.Int).Value = index;
    
    SqlDataAdapter da = new SqlDataAdapter() {
        SelectCommand = command
    };

    DataTable dataTable = new DataTable();

    try 
    {
        int recordsAffected = da.Fill(dataTable);
        if (recordsAffected > 0)
        {
            foreach (DataRow dr in dataTable.Rows)
            {
                Console.WriteLine(dr[0]);
            }
        }
    } 
    catch (SqlException ex) 
    {
        // Handle SqlException
    }
    finally 
    {
        // Your code...
    }
}

This post has been edited by JITHU: 19 July 2010 - 07:32 AM

Was This Post Helpful? 0
  • +
  • -

#8 JITHU  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 62
  • View blog
  • Posts: 201
  • Joined: 02-July 07

Re: Pass SqlCommand through Array

Posted 19 July 2010 - 07:40 AM

View PostItIntern3, on 19 July 2010 - 02:19 PM, said:

Paragon: What is the .value (in the last line of your last post) for?

It's a property of the SqlParameter class which is returned by the Add method, and using that property you can set or get the value of the parameter.
In your case, it will replace the parameter "@1" with the content of Value.
Was This Post Helpful? 1
  • +
  • -

#9 ItIntern3  Icon User is offline

  • D.I.C Head

Reputation: 6
  • View blog
  • Posts: 97
  • Joined: 14-July 10

Re: Pass SqlCommand through Array

Posted 19 July 2010 - 07:44 AM

View Postbaavgai, on 19 July 2010 - 08:15 AM, said:

You seem to be missing the point of a bind variable. You just have to change the value; that's it.

e.g.
// create a command with defined connection
SqlCommand cmd = new SqlCommand("Insert into unit (Description) values @1", connection);
cmd.Parameters.Add("@1", System.Data.SqlDbType.VarChar); // define parameter
cmd.Connection.Open(); // open connection
try {
	for (int j = 0; j <= 20; j++) {
		cmd.Parameters.Value = j;
		cmd.ExecuteNonQuery();
	}
} finally {
	cmd.Connection.Close()
}



I am getting an error on line 7 of your code. It says that the collection does not contain a definition for "value". I looked on msdn and did not see the value function under the methods of the class System.Data.SqlClient or the Sqlcommand class. Am I missing something?

        static void Main(string[] args)
        {
            //SqlCommand sCommand = new SqlCommand();
            string queryString = "Insert into unit (Description) values @1";


            //Connecting to the Master Database
            string connectionString = GetConnectionString();
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand sCommand = new SqlCommand(queryString, connection);
                sCommand.Parameters.Add("@1", System.Data.SqlDbType.VarChar); // define parameter  
                
                connection.Open();
                Console.WriteLine("State: {0}", connection.State);

                try
                {
                    for (int j = 0; j <= 100; j++)
                    {
                        sCommand.Parameters.Value = j;
                        sCommand.ExecuteNonQuery();
                    }
                }finally
                {
                    connection.Close(); 
                }
            }                   //Ends using                  

            Console.ReadLine();                          ///Holds console on screen--for viewable output       

        }                                                 //Ends Main


This post has been edited by ItIntern3: 19 July 2010 - 07:46 AM

Was This Post Helpful? 0
  • +
  • -

#10 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5800
  • View blog
  • Posts: 12,635
  • Joined: 16-October 07

Re: Pass SqlCommand through Array

Posted 19 July 2010 - 09:15 AM

View PostItIntern3, on 19 July 2010 - 08:44 AM, said:

I am getting an error on line 7 of your code. It says that the collection does not contain a definition for "value".



My bad, try:
cmd.Parameters[0].Value = j;



Paraemters is a collection of type Parameter. You only have one parameter, so you access it by it's index in the collection.
Was This Post Helpful? 1
  • +
  • -

#11 ItIntern3  Icon User is offline

  • D.I.C Head

Reputation: 6
  • View blog
  • Posts: 97
  • Joined: 14-July 10

Re: Pass SqlCommand through Array

Posted 19 July 2010 - 10:43 AM

Well I have the versions that I created (with your help) bug free! Thanks for all the help!
Was This Post Helpful? 0
  • +
  • -

#12 ParagonDevx  Icon User is offline

  • New D.I.C Head

Reputation: 4
  • View blog
  • Posts: 17
  • Joined: 09-April 10

Re: Pass SqlCommand through Array

Posted 19 July 2010 - 10:50 AM

View PostItIntern3, on 19 July 2010 - 11:43 AM, said:

Well I have the versions that I created (with your help) bug free! Thanks for all the help!


Glad to hear you got it right...
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1