SQL: MySQL and Microsoft SQL Server Stored Procedures IN, OUT using csharp code, storedcsharp
MySQL stored procedure:
# Insert a returned value.
DELIMITER $ drop procedure if exists 'geovindu '. 'proc _ Insert_BookKindOut '$ create procedure 'geovindu '. 'proc _ Insert_BookKindOut '(IN param1Name NVarChar (1000), IN param1Parent Int, out id int) begin if not exists (SELECT * FROM BookKindList WHERE BookKindName = param1Name) then # If the same record exists, insert into BookKindList (BookKindName, BookKindParent) VALUES (param1Name, param1Parent) is not added; # set ID = Last_insert_id () SELECT LAST_INSERT_ID; end if; END $ DELIMITER;
Microsoft SQL Server Stored Procedure
-- Insert a returned value.
IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'proc_Insert_BookKindOut') DROP PROCEDURE proc_Insert_BookKindOut GO CREATE PROCEDURE proc_Insert_BookKindOut ( --@BookKindID Int, @BookKindName NVarChar(1000), @BookKindCode varchar(100), @BookKindParent Int, @BookKindID int output ) AS IF NOT EXISTS (SELECT * FROM BookKindList WHERE [BookKindName]=@BookKindName) BEGIN INSERT INTO BookKindList ( [BookKindName] , [BookKindCode], [BookKindParent] ) VALUES ( @BookKindName , @BookKindCode, @BookKindParent ) select @BookKindID=@@IDENTITY END GO
Csharp reads MySQL stored procedures:
/// <Summary> /// obtain the returned value tu juwen note // </summary> /// <param name = "bookKindList"> </param> // <param name = "id"> </param> // <returns> </returns> public int InsertBookKindOut (BookKindListInfo bookKindList, out int id) {int ret = 0; int tid = 0; try {MySqlParameter [] par = new MySqlParameter [] {new MySqlParameter ("? Param1Name ", MySqlDbType. VarChar, 1000), new MySqlParameter ("? Param1Parent ", MySqlDbType. Int32, 4), new MySqlParameter ("? ID ", MySqlDbType. int32, 4),}; par [0]. value = bookKindList. bookKindName; par [1]. value = bookKindList. bookKindParent; par [2]. direction = ParameterDirection. output; ret = MySqlHelpDu. executeSql ("proc_Insert_BookKindOut", CommandType. storedProcedure, par); if (ret> 0) {tid = (int) par [2]. value ;}} catch (MySqlException ex) {throw ex;} id = tid; return ret ;}
Csharp reads Microsoft SQL Server Stored Procedures
/// <Summary> /// return value of the append record /// </summary> /// <param name = "bookKindList"> </param> /// <param name = "iout"> </param> // <returns> </returns> public int InsertBookKindOut (BookKindListInfo bookKindList, out int iout) {int ret = 0; int tou = 0; try {SqlParameter [] par = new SqlParameter [] {new SqlParameter ("@ BookKindName", SqlDbType. NVarChar, 1000), new SqlParameter ("@ BookKindParent", SqlDbType. int, 4), new SqlParameter ("@ BookKindID", SqlDbType. int, 4),}; par [0]. value = bookKindList. bookKindName; par [1]. value = bookKindList. bookKindParent; par [2]. direction = ParameterDirection. output; ret = DBHelper. executeSql ("proc_Insert_BookKindOut", CommandType. storedProcedure, par); if (ret> 0) {tou = (int) par [2]. value ;}} catch (SqlException ex) {throw ex;} iout = tou; return ret ;}