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