0

Hello,
used tech: C# 4.0, sql-Server 2008
i have this stored proc:

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
CREATE procedure [dbo].[SelectDataByPermiID]
(
    @permi_id       varchar(20)

)
AS
BEGIN TRANSACTION

SELECT d.carte_id, d.lname, d.fname, ct.Birthday, ct.Foto, d.permi_id, p.minusreason, p.currentpoint, ct.street_nr, ct.city, ct.phone_mobile, ct.phone_fix, ct.email
FROM driver d, point p, contact ct
WHERE d.point_id = p.point_id and d.carte_id = ct.carte_id and d.permi_id= @permi_id
IF @@ERROR <> 0
BEGIN 
    ROLLBACK
    RETURN

END

COMMIT

GO

first Question how do i get the return value from it if i run it(return value may be many records)?
how can catch Error send back from Server if there is no records?
how to use datatable to store the return value from it?

C#-Code

 public DataTable searchByPermiID(string permi_id)
{
            conn = new Connect();
            string query = "SelectDataByPermiID";

            using (SqlCommand myCommand = new SqlCommand(query))
            {
                SqlParameter[] sqlParams = new SqlParameter[1];
                sqlParams[0] = new SqlParameter("@permi_id", permi_id);
                myCommand.Parameters.AddRange(sqlParams);
                myCommand.CommandType = CommandType.StoredProcedure;
                return conn.executeSelectQuery_1(myCommand);
            }
}

 public DataTable executeSelectQuery_1(SqlCommand myCommand)
        {
            DataTable dataTable = new DataTable();
            try
            {
                //Hier get data from sql server and fill it in a datatable
                myCommand.Connection = ConnectOpen();
                myAdapter = new SqlDataAdapter(myCommand);
                myAdapter.Fill(dataTable);

            }
            catch (SqlException e)
            {
                MessageBox.Show("Error - Connection.executeSelectQuery - Query: \nException: " + e.StackTrace.ToString()
                    , "Exception", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return null;
            }
            sqlcon.Close();
            return dataTable;
        }

any suggestion???
thanks

1
Contributor
1
Reply
6
Views
4 Years
Discussion Span
Last Post by grarhakim
0

it Works so like that, if someone need the code is for free, and if there is any suggestion to make it better, please post it here.
i hope i can help someone with that.

viele grüße aus deutschland

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.