ASP.NET

Quote:

... a program called from server-side web application such as ASP.NET ...

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER
Procedure [dbo].[GetRandomOptionsold]
(@class varchar(max),
@Subject varchar(max),
@ExamType varchar(max),
@School varchar(max)
)

as

Begin

Create
Table #Options(ID int identity(1,1),Fields nvarchar(10))

Insert
into #Options

Select
COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS Where TABLE_NAME='Questions' And COLUMN_NAME like 'O%' Order By NEWID()

--Select * from #Options

Declare
@RandomCols nvarchar(100)

Set
@RandomCols=''

Select
@RandomCols=@RandomCols+Fields+' ,' from #Options

--Select @RandomCols

Set
@RandomCols=(Select REPLACE(Substring(@RandomCols,0,CHARINDEX(' ,',@RandomCols)+2),' ,',' as O1,')

+
SUBSTRING(@RandomCols,CHARINDEX(' ,',@RandomCols)+2,LEN(@RandomCols)))

--Select @RandomCols

Set
@RandomCols=(Select REPLACE(Substring(@RandomCols,0,CHARINDEX(' ,',@RandomCols)+2),' ,',' as O2,')

+
SUBSTRING(@RandomCols,CHARINDEX(' ,',@RandomCols)+2,LEN(@RandomCols)))

--Select @RandomCols

Set
@RandomCols=(Select REPLACE(Substring(@RandomCols,0,CHARINDEX(' ,',@RandomCols)+2),' ,',' as O3,')

+
SUBSTRING(@RandomCols,CHARINDEX(' ,',@RandomCols)+2,LEN(@RandomCols)))

--Select @RandomCols

Set
@RandomCols=(Select REPLACE(Substring(@RandomCols,0,CHARINDEX(' ,',@RandomCols)+2),' ,',' as O4,')

+
SUBSTRING(@RandomCols,CHARINDEX(' ,',@RandomCols)+2,LEN(@RandomCols)))

--Set @RandomCols=SUBSTRING(@RandomCols,0,len(@RandomCols))

--Select @RandomCols

Declare
@strQry nvarchar(MAX)

Set
@strQry=''

Set
@strQry='Select qno,que,'+@RandomCols+'ans from Questions where Class='''+@Class+''' and Subject='''+@Subject+''' and ExamType='''+@ExamType+''' and School='''+@School+''''

exec
(@strQry)

End

exitCode int = 2;


sql_connection = new MySqlConnection(ConfigurationManager.ConnectionStrings["SQLdb"].ConnectionString);
sql_command = new MySqlCommand("sp_delete_channel", sql_connection);
sql_command.CommandType = CommandType.StoredProcedure;

param_Channel_ID SqlParameter = new SqlParameter("@param_channel_id", SqlDbType.Int);
param_Channel_ID.Value = channel_id;
sql_command.Add(param_Channel_ID);

param_Member_ID SqlParameter = new SqlParameter("@param_volow_member_id", SqlDbType.Int);
param_Member_ID.Value = Convert.ToInt32(Session["volow_member_id"]);
sql_command.Add(param_Memeber_ID);

param_Created SqlParameter = new SqlParameter("@param_is_channel_created", SqlDbType.Bit); 
//[edit] I think it's boolean in mySQL, and bit in SQL Server
param_Created.Value = Convert.ToBoolean(ParameterDirection.Output);
sql_command.Add(param_Created_ID);

try {
   // execute the query;
   sql_connection.Open();
   sql_reader SqlDataReader = sql_command.ExecuteReader();
   sql_command.ExecuteNonQuery();
   sql_reader.Close();
   sql_connection.Close();
   exitCode = 0
}
catch ex as SQLException {
 exitCode = 1
 //Exception for the function
 //SQLException for the TSQL
}

return exitCode;

BEGIN
        SET param_is_channel_created = TRUE;
        DELETE FROM member_posts WHERE member_channel_id=@param_channel_id;
        DELETE FROM member_channels WHERE member_channel_id=@param_channel_id;
 
        IF NOT EXISTS (SELECT member_channel_id FROM member_channels WHERE volow_member_id=@param_volow_member_id) THEN
        BEGIN
           SET param_is_channel_created = FALSE;     
           UPDATE volow_members SET is_channel_created=param_is_channel_created;                
        END;
        END IF;
END

sql_connection = new MySqlConnection(ConfigurationManager.ConnectionStrings["SQLdb"].ConnectionString);
sql_command = new MySqlCommand("sp_delete_channel", sql_connection);
sql_command.CommandType = CommandType.StoredProcedure;
sql_connection.Open();
                                        
sql_command.Parameters.AddWithValue("param_channel_id", channel_id).MySqlDbType = MySqlDbType.Int32;
int result_row = sql_command.ExecuteNonQuery();

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_delete_channel`(IN param_channel_id int, IN param_volow_member_id int, OUT param_is_channel_created bit)
BEGIN
    DELETE FROM member_posts WHERE member_channel_id = param_channel_id;
    DELETE FROM member_channels WHERE member_channel_id = param_channel_id;

    IF NOT EXISTS (SELECT member_channel_id FROM member_channels WHERE volow_member_id = param_volow_member_id) THEN
    BEGIN
        UPDATE volow_members SET is_channel_created = FALSE;

        SET param_is_channel_created = FALSE;
    END;
    ELSE SET param_is_channel_created = TRUE;
    END IF;
END

sql_connection = new MySqlConnection(ConfigurationManager.ConnectionStrings["SQLdb"].ConnectionString);
sql_command = new MySqlCommand("sp_delete_channel", sql_connection);
sql_command.CommandType = CommandType.StoredProcedure;
sql_connection.Open();
                                        
sql_command.Parameters.AddWithValue("param_channel_id", channel_id).MySqlDbType = MySqlDbType.Int32;
int result_row = sql_command.ExecuteNonQuery();

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_delete_channel`(IN param_channel_id int, IN param_volow_member_id int, OUT param_is_channel_created bit)
BEGIN
    DELETE FROM member_posts WHERE member_channel_id = param_channel_id;
    DELETE FROM member_channels WHERE member_channel_id = param_channel_id;

    IF NOT EXISTS (SELECT member_channel_id FROM member_channels WHERE volow_member_id = param_volow_member_id) THEN
    BEGIN
        UPDATE volow_members SET is_channel_created = FALSE;

        SET param_is_channel_created = FALSE;
    END;
    ELSE SET param_is_channel_created = TRUE;
    END IF;
END