none
Pass the file name to stored procedure using C# Console RRS feed

  • Question

  • Hi

     

    I have created a console application in C#, which runs the stored procedure in MS SQL 2005 that stored procedure import records from xml file to database tables.

     

    Currently I have defined a file name in a stored procedure, but I want to change it so when I run my C# Console program, it ask me a file name including path, and pass that to stored procedure.

     

    Currently I have this code in C#

            static void Main(string[] args)
            {
                
                String sConnectionstring;
                sConnectionstring = "Initial Catalog=Pubs;Integrated Security=true;Data Source=DCC2516";
             
                SqlConnection Conn = new SqlConnection(sConnectionstring);
    
                SqlCommand Arith = new SqlCommand("SET ARITHABORT ON", Conn);
                Int32 rowsAffected;
    
                SqlCommand cmd = new SqlCommand();
                //stored procedure name
                cmd.CommandText = "[dbo].[xmltext_import]"; 
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = Conn;
                Conn.Open();
               //Console.Write ("Connection is open");
                Arith.ExecuteNonQuery();
    
               rowsAffected = cmd.ExecuteNonQuery();
               Console.WriteLine("Records are insearted");
               Conn.Close();
            }
    

     

     

    And code in stored procedure (SQL 2005)

     

    ALTER PROCEDURE [dbo].[xmltext_import] 
    -- Add the parameters for the stored procedure here
    @xmlText XML = xmlText1 
    	
    AS
    BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    
        -- Insert statements for procedure here
    --@xmlText XML
    SET @xmlText = (select *from OPENROWSET (BULK 'C:\BOTHsr.xml', SINGLE_BLOB) As X)
    
    --1st Query to Insert Records into SRNumbers table
    Insert into SRNumber

     

    Thanks in Advance


    Bedroom Furniture
    Thursday, October 15, 2009 1:55 PM

Answers

  • Hi there

    I believe you should be looking at the SqlParameter class.
    This class enables you to set the value of a named parameter and then add it to the collection of parameters on the SqlCommand object.

    System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand();                
    System.Data.SqlClient.SqlParameter param = new System.Data.SqlClient.SqlParameter( "FilePath", "C:\MyPath\MyFile.xml" );
    command.Parameters.Add( param );


    HTH
    /Jens
    Developer BI & .Net
    • Marked as answer by Bin-ze Zhao Tuesday, October 20, 2009 9:13 AM
    Thursday, October 15, 2009 2:08 PM

All replies

  • Hi there

    I believe you should be looking at the SqlParameter class.
    This class enables you to set the value of a named parameter and then add it to the collection of parameters on the SqlCommand object.

    System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand();                
    System.Data.SqlClient.SqlParameter param = new System.Data.SqlClient.SqlParameter( "FilePath", "C:\MyPath\MyFile.xml" );
    command.Parameters.Add( param );


    HTH
    /Jens
    Developer BI & .Net
    • Marked as answer by Bin-ze Zhao Tuesday, October 20, 2009 9:13 AM
    Thursday, October 15, 2009 2:08 PM
  • Hi,

    You have two options, you can read the filename as a parameter (preferred) or you can request it to be entered when the program runs:

    strnig filename = null;

     

    public static int Main(string[] args) {
     foreach(string s in args)
       {
     string[] parts = s.Split( new char[] {'='});
    if ( strnig.Compare(parts[0], "filename")==0){
    filename = parts[1];
    break;
    }
    }
    if (filename!=null)
    {
      cmd.Parameters.Add("filename", DBType.string).Value = filename;
    }

    • Marked as answer by Bin-ze Zhao Tuesday, October 20, 2009 9:13 AM
    • Unmarked as answer by Bin-ze Zhao Tuesday, October 20, 2009 9:20 AM
    Thursday, October 15, 2009 3:35 PM
  • Hi Ignacio Machin MVP,
       I will go for the second option which is file name entered when the program runs, for that i have code like this but give me 6 errors, please see the code and error below.

    using System;
    using System.Collections.Generic;
    using System.Text;
    using System.Data;
    using System.Data.SqlClient;
    using System.Xml;
    
    
    namespace Run_StoredProcdure
    {
        class Program
        {
            String filename = null;
            static void Main(string[] args)
            {
                
                String sConnectionstring;
                sConnectionstring = "Initial Catalog=Pubs;Integrated Security=true;Data Source=DCC2516";
    
                foreach(String s in args)
                 {
                    String[] parts = s.Split( new char[] {'='});
                if ( Strnig.Compare(parts[0], "filename")==0){
                filename = parts[1];
                break;
                }
                }
                    if (filename!=null)
                    {
                        cmd.Parameters.Add("filename", DBType.String).Value = filename;
                    }
                
                SqlConnection Conn = new SqlConnection(sConnectionstring);
    
                SqlCommand Arith = new SqlCommand("SET ARITHABORT ON", Conn);
                Int32 rowsAffected;
    
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = "[dbo].[xmltext_import]";



    see error
    1	The name 'Strnig' does not exist in the current context	
    2	An object reference is required for the nonstatic field, method, or property 'Run_StoredProcdure.Program.filename'	
    3	An object reference is required for the nonstatic field, method, or property 'Run_StoredProcdure.Program.filename'	
    4	The name 'cmd' does not exist in the current context	
    5	The name 'DBType' does not exist in the current context	
    6	An object reference is required for the nonstatic field, method, or property 'Run_StoredProcdure.Program.filename'	

    Bedroom Furniture
    Friday, October 16, 2009 10:43 AM