Connect and call SP in MS SQL 2000

[romanne]

Hi,
Im new in C#, could you help with my codes.
I need to connect to MS SQL DB (2000) and need to call Stored Proc to return value.
Here is my codes:

private void ExecuteStoredProcs()
{
SqlConnection conn = new SqlConnection(connectionString);
 try
  {
conn.Open();
SqlCommand getRequestCmd = new SqlCommand(SP, conn);
getRequestCmd.CommandType = CommandType.StoredProcedure;
getRequestCmd.CommandTimeout = commandTimeout;
SqlDataReader getRequestRdr = getRequestCmd.ExecuteReader();

int recordsExtracted = 0;
WriteLog("Getting Data from DB....");
while (getRequestRdr.Read())
        {
array = new string[32];
recordsExtracted++;
WriteLog("recordsExtracted = " + recordsExtracted);
for (int x = 0; x < 32; x++)
    {
array[x] = getRequestRdr.GetString(x).Trim();
(DEBUG_MODE == 1)
WriteLog("array[" + x + "] = " + array[x] + " (is not null)");
    }
       }
ProcessFiles();
  }
catch (Exception e) 
      {
WriteLog("Exception error: " + e.Message + "\r\nDetails: " + e.ToString());
      }
finally
          {
conn.Close();
          }
}

=== i didnt encountered error but i think it didnt call the SP since in the logs it only says: 'Getting Data from DB....' and process ended.

Please help me with this.

[sknake]

What does your SP do? Does it return a cursor (does it have a Select query inside of it?) or are you just trying to get the integral return value of the sproc?

[romanne]

Hi Sknake,

Theres an insert into in the SP that i need to call.

[sknake]

Why are you using a datareader for executing the sproc then? You should be able to use .ExecuteScalar() and cast it to an int to get the return value.

[romanne]

I also need the data that i get in my SP and i declare the per data in an array...

like this one:

data_1 = array[1 - 1];
data_2 = array[2 - 1];
and so on....

[sknake]

If your sproc only performs an insert into then no data is returned. If you're populating an array then obviously you're returning data.

Please post the CREATE PROCEDURE code for your sproc, and all relevant C# code for this task. You're not providing me with the full story.

[romanne]

SP:

create procedure SP_getdata
as
	set arithabort off 

	declare @date1	datetime,
		@date2	datetime
create table #tmptable  (	
	data1			char(04)	null,
	data2			char(03)	null,
	data3			char(03)	null,
	..
                data35	                                char(50) null
)   

		INSERT INTO #tmptable
		SELECT data1,data2, .. , data35		                FROM GivenTable
		WHERE GeneratedStatus = 'NEW'
		AND GeneratedTime > @LUT_time

		UPDATE GivenTable
		SET LastUpdatedTime = @current_time
		WHERE GeneratedStatus = 'NEW'
	
		SELECT *
		FROM #tmptable
		drop table #tmptable

C#

private void ExecuteStoredProcs()
{
SqlConnection conn = new SqlConnection(connectionString);
 try
  {
conn.Open();
SqlCommand getRequestCmd = new SqlCommand(SP, conn);
getRequestCmd.CommandType = CommandType.StoredProcedure;
getRequestCmd.CommandTimeout = commandTimeout;
SqlDataReader getRequestRdr = getRequestCmd.ExecuteReader();

int recordsExtracted = 0;
WriteLog("Getting Data from DB....");
while (getRequestRdr.Read())
        {
array = new string[32];
recordsExtracted++;
WriteLog("recordsExtracted = " + recordsExtracted);
for (int x = 0; x < 32; x++)
    {
array[x] = getRequestRdr.GetString(x).Trim();
(DEBUG_MODE == 1)
WriteLog("array[" + x + "] = " + array[x] + " (is not null)");
    }
       }
ProcessFiles();
  }
catch (Exception e) 
      {
WriteLog("Exception error: " + e.Message + "\r\nDetails: " + e.ToString());
      }
finally
          {
conn.Close();
          }
}private void ExecuteStoredProcs()
{
SqlConnection conn = new SqlConnection(connectionString);
 try
  {
conn.Open();
SqlCommand getRequestCmd = new SqlCommand(SP, conn);
getRequestCmd.CommandType = CommandType.StoredProcedure;
getRequestCmd.CommandTimeout = commandTimeout;
SqlDataReader getRequestRdr = getRequestCmd.ExecuteReader();

int recordsExtracted = 0;
WriteLog("Getting Data from DB....");
while (getRequestRdr.Read())
        {
array = new string[32];
recordsExtracted++;
WriteLog("recordsExtracted = " + recordsExtracted);
for (int x = 0; x < 32; x++)
    {
array[x] = getRequestRdr.GetString(x).Trim();
(DEBUG_MODE == 1)
WriteLog("array[" + x + "] = " + array[x] + " (is not null)");
    }
       }
if(DEBUG_MODE == 1)
WriteLog ("before assignment");

data1 = array[1 -1];
data2 = array[2 - 1];
..
data35 = array[35 - 1];
if(DEBUG_MODE == 1)
WriteLog ("up to 30");

ProcessFiles();
  }
catch (Exception e) 
      {
WriteLog("Exception error: " + e.Message + "\r\nDetails: " + e.ToString());
      }
finally
          {
conn.Close();
          }
}

[sknake]

So you are returning a cursor. Load the results in to a datatable. You can bind a grid to the table or iterate through it and create your array.

Here is an example with a system stored procedure:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace daniweb
{
  public partial class frmSprocView : Form
  {
    private DataTable dt;

    public frmSprocView()
    {
      InitializeComponent();
    }

    private void button1_Click(object sender, EventArgs e)
    {
      const string connStr = "Data Source=apex2006sql;Initial Catalog=Leather;Integrated Security=True;";
      const string sp_name = "sp_who";
      using (SqlConnection conn = new SqlConnection(connStr))
      {
        conn.Open();
        using (SqlCommand cmd = new SqlCommand(sp_name, conn))
        {
          cmd.CommandType = CommandType.StoredProcedure;
          cmd.Parameters.Add(new SqlParameter("@loginame", SqlDbType.VarChar)).Value = "sa";
          using (SqlDataReader dr = cmd.ExecuteReader())
          {
            if (dt != null)
              dt.Dispose();
            dt = new DataTable();
            dt.Load(dr);
          }
        }
      }
      dataGridView1.DataSource = dt; //data bind

      string[,] arr = new string[dataGridView1.Rows.Count, dataGridView1.Columns.Count];
      for (int y = 0; y < dt.Rows.Count; y++)
      {
        for (int x = 0; x < dt.Columns.Count; x++)
        {
          arr[y, x] = Convert.ToString(dt.Rows[y][x]);
        }
      }

      System.Diagnostics.Debugger.Break();
    }
  }
}