CODEWITHSUNDEEP
c#sql-serverstored-procedures
user3671271
user3671271

Reputation: 568

Why does my stored procedure not run in C#?

I'm beginner in C# and stored procedure and this application write for first time, I want to write a simple application to run a stored procedure on the my SQL Server, for more explain I write this stored procedure in SQL Server:

ALTER PROCEDURE [dbo].[CDRCOLLECT]
AS
BEGIN
    EXEC xp_cmdshell 'bcp "SELECT "028",rtrim(ltrim(ANUMBER)),rtrim(ltrim(BNUMBER)),rtrim(ltrim(DATE)),rtrim(ltrim(TIME)),rtrim(ltrim(DURATION))  FROM [myTestReport].[dbo].[CDRTABLE]"  queryout f:\newOUTPUT.txt -S DESKTOP-A5CFJSH\MSSQLSERVER1 -Uuser -Ppass -f "f:\myFORMAT.fmt"  '
    print 'run successfully!'
END

and with this code in the C# windows application want to call that stored procedure:

SqlConnection sqlConnection;

try {
    sqlConnection = new SqlConnection(conn);

    SqlCommand command = new SqlCommand("CDRCOLLECT", sqlConnection);
    command.CommandType = CommandType.StoredProcedure;

    sqlConnection.Open();
    //return command.ExecuteNonQuery();
    sqlConnection.Close();
    MessageBox.Show("RUN SUCCESSFULL");
}
catch (Exception p)
{
    MessageBox.Show(p.ToString());
}

but that procedure is not run, what happened? How can I solve that?

Upvotes: 0

Views: 86

Answers (1)

FatemehEbrahimiNik
FatemehEbrahimiNik

Reputation: 613

For select command in stored procedure 

sqlConnection = new SqlConnection(conn);     

System.Data.SqlClient.SqlDataAdapter SqlDataAdapter1 = new System.Data.SqlClient.SqlDataAdapter("CDRCOLLECT", sqlConnection);
SqlDataAdapter1.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure;

//for add parameter
//SqlDataAdapter1.SelectCommand.Parameters.Add("@param1", System.Data.SqlDbType.NVarChar);
//SqlDataAdapter1.SelectCommand.Parameters["@param1"].Value = DropDownList1.SelectedValue;
System.Data.DataSet DataSet1  = new System.Data.DataSet();
SqlDataAdapter1.Fill(DataSet1 , "0");

if (DataSet1.Tables["0"].Rows.Count > 0)
{
    //you can use DataSet1.Tables["0"] for access your output data
}

For insert command with parameter for example

System.Data.SqlClient.SqlCommand scom = new System.Data.SqlClient.SqlCommand("CDRCOLLECT", sqlConnection);
scom.CommandType = System.Data.CommandType.StoredProcedure;
scom.Parameters.Add("@fName", System.Data.SqlDbType.NVarChar);
scom.Parameters.Add("@lName", System.Data.SqlDbType.NVarChar); scom.Parameters["@fName"].Value = TextBox1.Value;
scom.Parameters["@lName"].Value = TextBox2.Value;

scom.ExecuteNonQuery();

Upvotes: 3

Related Questions