Link to home
Create AccountLog in
Avatar of AlHal2
AlHal2Flag for United Kingdom of Great Britain and Northern Ireland

asked on

using sqlcmd with c#

I have a job which calls a stored procedure.  If it produces output the next part of the job FTPs the output to somewhere else.
I have a C# program which I want to use to run the job.  However the dba has said that we are allowed to call stored procedures from C#, but not jobs (in case it conflicsts with existing schedules).  
The database is SQL 2005.
The job uses this command to get the stored proc to produce the file.
sqlcmd -U ID -P xxxx -S server -d DS2DS -h -1 -W -Q "exec StoredProc @Param1 = 'CPL',@Param2 = 1,@Param3 = 21" -o\\server\Path\OutputFile.txt



I'm trying to replicate what this job does with C# (asp.net 3.5) .

How can I use C# to put the file onto one remote location then FTP it to another remote location.

I'm using this code to call the stored procedure and get results, but it takes no account of sqlcmd.  Please can someone help?

            using (SqlConnection sqlCon = new SqlConnection())
            {
                using (SqlCommand sqlcmd = new SqlCommand())
                {
                    string ConnectionString = ConfigurationManager.ConnectionStrings["LocalSqlServer"].ConnectionString;
                    sqlcmd.Connection = sqlCon;
                    sqlCon.ConnectionString = ConnectionString;
                    sqlCon.Open();
                    sqlcmd.CommandTimeout = 0;
                    sqlcmd.Parameters.Clear();
                    sqlcmd.CommandType = CommandType.StoredProcedure;

                    sqlcmd.CommandText = "storedproc";
                    sqlcmd.Parameters.AddWithValue("Param1", 'CPL');
                    using (DataSet ds = new DataSet())
                    {
                        using (System.Data.SqlClient.SqlDataAdapter
                        sqladp = new System.Data.SqlClient.SqlDataAdapter())
                        {
                            sqladp.SelectCommand = sqlcmd;
                            sqladp.Fill(ds, "InputParameters");
                        }
                        StopForMaintenanceWindow();
                        foreach (DataRow dr in ds.Tables["InputParameters"].Rows)
                        {

                            StopForMaintenanceWindow();
                            sqlcmd.Parameters.Clear();
                            sqlcmd.CommandType = CommandType.StoredProcedure;
                            sqlcmd.CommandText = "StoredProc";


                            sqlcmd.Parameters.AddWithValue("@Param1", "CPL");
                            sqlcmd.Parameters.AddWithValue("@ReturnCode", SqlDbType.Int);

                            sqlcmd.Parameters["@ReturnCode"].Direction = ParameterDirection.Output;
                            try
                            {
                                sqlcmd.ExecuteNonQuery();
                                ReturnCode = (int)(sqlcmd.Parameters["@ReturnCode"].Value);
                                lblErrs.Text = "Try " + lblErrs.Text + ReturnCode.ToString();
                                if (ReturnCode != 0)  //failure
                                {
                                    SendEmail("Extract");  
                                }
                                else
                                {
                                    SendFile(); //It's worked
                                }
                            }

                            catch (Exception ex)
                            {
                                lblErrs.Text = "Catch " + lblErrs.Text + sqlcmd.Parameters["@ReturnCode"].Value.ToString();
                            }
Avatar of Easwaran Paramasivam
Easwaran Paramasivam
Flag of India image

Don't know why do you specify sqlcmd.CommandTimeout = 0;  in above line of code.

As per my understandings you want to write a file into remote location.

Specify remote location Ex., \\Network\Folder\SomeFile.txt in the WriteAllText() method.

http://msdn.microsoft.com/en-us/library/ms143375.aspx

Once it is written, then you could use Copy method to copy to FTP location:

http://msdn.microsoft.com/en-us/library/c6cfw35a.aspx
Of course it takes no account of using sqlcmd.exe, you are not using it!

If you want to use sqlcmd.exe you should call it via Process.Start() instead of using System.Data.SqlClient to establish a Connection.

Process.Start example:
ProcessStartInfo startInfo = new ProcessStartInfo();
startInfo.UseShellExecute = true;
startInfo.FileName = "osql.exe";
startInfo.Arguments = String.Format("-U ID -P xxxx -S server -d DS2DS -h -1 -W -Q \"exec StoredProc @Param1 = 'CPL',@Param2 = 1,@Param3 = 21\" -o{0}", LogFilename);
try
{
Process p = Process.Start(startInfo);
...

Open in new window


I hope, that I understood you right :)
Avatar of AlHal2

ASKER

Hi Djbaum,

Thanks for this.  @Param3 is a return code.  The full code is below.  Does this change the code above?
I'm running the code and it seems to be working, but I can't see a file.

sqlcmd.Parameters.Clear();
sqlcmd.CommandType = CommandType.StoredProcedure;
sqlcmd.CommandText = "StoredProc";
sqlcmd.Parameters.AddWithValue("@DSFIPriceSource", 'CPL');
sqlcmd.Parameters.AddWithValue("@SnapshotNum", 1);
sqlcmd.Parameters.AddWithValue("@ReturnCode", SqlDbType.Int);
sqlcmd.Parameters["@ReturnCode"].Direction = ParameterDirection.Output;
Hi AlHal2,

I'm still not sure what you want to do.

-Do you want to execute sqlcmd.exe from your C# Programm ?
or
-Do you want to do that, what was done in your sqlcmd.exe example in a C# Programm?
Avatar of AlHal2

ASKER

The latter.

The job runs a stored proc and prints the output to a file.  I want to do the same.

This will be a web based program, so I don't want black boxes appearing!  This processing must take place without user involvement.
So you should just use sqlcmd.ExecuteReader() instead of sqlcmd.ExecuteNonQuery(), than parse the Reader and write it to Logfile (using a StreamWriter).
It is descriped here

maybe like this:
	SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSqlServer"]);
	con.Open();
	SqlCommand com = new SqlCommand();
	com.Connection = con;
	com.Parameters.Clear();
	com.CommandType = CommandType.StoredProcedure;
	//Add your Parameters and command text ...
	StreamWriter writer = new StreamWriter(logfile, true);
	SqlDataReader reader = com.ExecuteReader();
	object[] values;
	string line;
	while (reader.Read())
	{
		line = "";
		reader.GetValues(values);
		foreach (object o in values)
			line += o.ToString();
		writer.WriteLine(line);
	}
	writer.Close();
	con.Close();

Open in new window

Avatar of AlHal2

ASKER

With this code I@m getting the message
Use of unassigned local variable 'values'



                            StreamWriter writer = new StreamWriter("F:\\test\\File.txt", true);
                            SqlDataReader reader = sqlcmd.ExecuteReader();
                            object[] values;
                            string line;

                            while (reader.Read())
                            {
                                line = "";
                                reader.GetValues(values);
                                foreach (object o in values)
                                    line += o.ToString();
                                writer.WriteLine(line);
                            }
                            reader.Close();
                            writer.Close();
ASKER CERTIFIED SOLUTION
Avatar of djbaum
djbaum
Flag of Germany image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of AlHal2

ASKER

I amended this line

object[] values = new Object[reader.FieldCount];
Avatar of AlHal2

ASKER

Thanks.