locked
Calling SQL statements in a SQL connetion RRS feed

  • Question

  • Hello. I've used the SQL query wizard to create Insert statements on each of my tables in the dataset. Now I'm trying to come up with an sql connection that works and this is what I've found.

     System.Data.SqlClient.SqlConnection dataConnection = new SqlConnection();
    	 dataConnection.ConnectionString =
    	  @"Data Source=SW-DEV-PROWLAND\SQLEXPRESS08;Initial  Catalog=playerDb;User ID=*****;Password=********;Pooling=False";
    	 
    	 System.Data.SqlClient.SqlCommand dataCommand = new SqlCommand();
    	 dataCommand.Connection = dataConnection;
    	 
    	 //tell the compiler and database that we're using parameters (thus the @first, @last, @nick)
    	 dataCommand.CommandText = ("INSERT playerDetails (FirstName, LastName, NickName) VALUES (@first, @last, @nick)");
    	 
    	 //add our parameters to our command object
    	 dataCommand.Parameters.AddWithValue("@first", first);
    	 dataCommand.Parameters.AddWithValue("@last", last);
    	 dataCommand.Parameters.AddWithValue("@nick", nick);
    	 
    	 dataConnection.Open();
    	 dataCommand.ExecuteNonQuery();
    	 }
    	}
    
    
    Since I've already created the sql statements for each table do I need to mention it again in the CommandText and or the parameters like it shows above or can I somehow call the Insert statements somehow from the sql connection? Any help would be greatly appreciated. I'm using Visual C# Express with sql 2008


    Robin Groettum
    Thursday, December 23, 2010 10:02 PM

Answers

  • No you can't pass .sql file directly to the command object

    You have three choices for ADO.net command object's Commandtype propery

    a ) Stored procedure: The name of the stored procedure. You can use the Parameters property of a command to access input and output parameters and return values, regardless of which Execute method is called. When using ExecuteReader, return values and output parameters will not be accessible until the DataReader is closed.

    b) Text   --Inline SQL   (SQL statement like seletct, insert, update etc.)
    c) table direct  -- name of the table

    see examples available on MSDN

    Executing a Command (ADO.NET)
    http://msdn.microsoft.com/en-us/library/tyy0sz6b.aspx

    Using Stored Procedures with a Command
    http://msdn.microsoft.com/en-us/library/yy6y35y8(v=vs.71).aspx

    • Proposed as answer by Naomi N Friday, December 24, 2010 1:19 AM
    • Marked as answer by PCRider Friday, December 31, 2010 6:16 PM
    Friday, December 24, 2010 12:44 AM

All replies

  • >>Since I've already created the sql statements for each table do I need to mention it again in the CommandText and or the parameters like it shows above or can I somehow call the Insert statements somehow from the sql connection? Any help would be greatly appreciated. I'm using Visual C# Express with sql 2008

    Your syntax appears to be correct.

    If each insert statement requires different set of parameters to be pased
    then yes, you would have to use separate commandtext and pass parameters to the command object

    If parameters passed are the same, then you can wrap the whole multi table insert (using multiple insert statements) logic into a stored procedrue and execute that stored procedure using ADO.net command object

    e.g. dataCommand.CommandType = CommandType.StoredProcedure
    dataCommand.CommandText = 'StoredPRocName"

      

    Thursday, December 23, 2010 10:17 PM
  • OK. So if I'm creating sql statements and say instead of a stored procedure I use them as straight sql I would say e.g. e.g. dataCommand.CommandType = CommandType.Structured Query Language
    dataCommand.CommandText = 'StoredPRocName" Am I correct? I'm not using stored procedures, just straight sql.

    Robin Groettum
    Thursday, December 23, 2010 10:27 PM
  • No you can't pass .sql file directly to the command object

    You have three choices for ADO.net command object's Commandtype propery

    a ) Stored procedure: The name of the stored procedure. You can use the Parameters property of a command to access input and output parameters and return values, regardless of which Execute method is called. When using ExecuteReader, return values and output parameters will not be accessible until the DataReader is closed.

    b) Text   --Inline SQL   (SQL statement like seletct, insert, update etc.)
    c) table direct  -- name of the table

    see examples available on MSDN

    Executing a Command (ADO.NET)
    http://msdn.microsoft.com/en-us/library/tyy0sz6b.aspx

    Using Stored Procedures with a Command
    http://msdn.microsoft.com/en-us/library/yy6y35y8(v=vs.71).aspx

    • Proposed as answer by Naomi N Friday, December 24, 2010 1:19 AM
    • Marked as answer by PCRider Friday, December 31, 2010 6:16 PM
    Friday, December 24, 2010 12:44 AM
  • OK. 2 things:

    1) It seems like the TableDirect can only be used with OlebCommand

    2) What is the purpose of using the Insert wizard in Visual C# to set Insert, Update or Delete commands in SQL if you can't call them?  When you use the wizard, you set up the procedure and give it a name e.g.ArtistInsert. Is there a reason for this feature in Visual Studio?


    Robin Groettum
    Friday, December 24, 2010 1:48 PM
  • >>1) It seems like the TableDirect can only be used with OlebCommand

    Yes, that is correct, it can only be used with OLEDB (mostly for MS access), and I really do not see a good reason for using it...

    >>What is the purpose of using the Insert wizard in Visual C# to set Insert, Update or Delete commands in SQL if you >>can't call them?  When you use the wizard, you set up the procedure and give it a name e.g.ArtistInsert. Is there a >>reason for this feature in Visual Studio?



    I am assuming you are refering to table adapter wizard it creates sql statements and stored procedures necessary to manipulate underlying SQL server database. More info can be found here

    http://msdn.microsoft.com/en-us/library/dex7k4dw(v=VS.90).aspx

    However, personally I try to stay away with wizards (I think they are more meant for beginners  but they are not as flexible) eventhough it may sounds daunting initially it is better to write code in ADO.net (instead of auto generated code in visual studio)

    Friday, December 24, 2010 8:07 PM