Пример #1
0
        /// <summary>
        /// Execute procedure with parameters
        /// 12/8/2017 pritey
        /// </summary>
        /// <param name="ProcedureName"></param>
        /// <param name="ParamValue"></param>
        /// <param name="dtExamAnswer"></param>
        /// <param name="TableParamName"></param>
        /// <param name="ReturnTypeOutVal"></param>
        /// <returns></returns>
        public MEMBERS.SQLReturnValue ExecuteProcedureWithParam_Datatable(string ProcedureName, object[,] ParamValue, DataTable dtExamAnswer, string TableParamName, int ReturnTypeOutVal)
        {
            SqlCommand COMMAND = new SqlCommand();

            COMMAND.CommandText = ProcedureName;
            SqlConnection MYCON = new SqlConnection(con.ConnectionString);

            COMMAND.Connection     = MYCON;
            COMMAND.CommandTimeout = 0;
            COMMAND.CommandType    = CommandType.StoredProcedure;
            SqlParameter[] param = new SqlParameter[ParamValue.GetUpperBound(0) + 1];
            for (int i = 0; i < param.Length; i++)
            {
                param[i] = new SqlParameter("@" + ParamValue[i, 0].ToString(), ParamValue[i, 1].ToString());
            }
            COMMAND.Parameters.AddRange(param);
            if (dtExamAnswer != null)
            {
                SqlParameter ParamTb = new SqlParameter("@" + TableParamName, dtExamAnswer);
                ParamTb.SqlDbType = SqlDbType.Structured;
                COMMAND.Parameters.Add(ParamTb);
            }

            COMMAND.Parameters.Add("OUTVAL", (ReturnTypeOutVal == 1 ? SqlDbType.UniqueIdentifier : ReturnTypeOutVal == 2 ? SqlDbType.Int : SqlDbType.NVarChar), -1).Direction = ParameterDirection.Output;

            if (MYCON.State != ConnectionState.Open)
            {
                MYCON.Open();
            }
            COMMAND.ExecuteNonQuery();
            MYCON.Close();
            MEMBERS.SQLReturnValue res = new MEMBERS.SQLReturnValue();
            res.Outval = COMMAND.Parameters["OUTVAL"].Value;
            return(res);
        }
Пример #2
0
        /// <summary>
        ///  Execute Procedure With Value
        ///  12/8/2017 pritey
        /// </summary>
        /// <param name="procedureName"></param>
        /// <param name="ParamValue"></param>
        /// <param name="ReturnTypeOutVal"></param>
        /// <returns></returns>
        public MEMBERS.SQLReturnValue ExecuteProcedureWithValue(string procedureName, object[,] ParamValue, int ReturnTypeOutVal)
        {
            MEMBERS.SQLReturnValue res = new MEMBERS.SQLReturnValue();
            try
            {
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = procedureName;
                cmd.Connection  = con;
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                SqlParameter[] param = new SqlParameter[ParamValue.GetUpperBound(0) + 1];
                for (int i = 0; i < param.Length; i++)
                {
                    param[i] = new SqlParameter("@" + ParamValue[i, 0].ToString(), (ParamValue[i, 1] == null ? null : (ParamValue[i, 1].ToString() == "null" ? null : ParamValue[i, 1].ToString())));
                }
                cmd.Parameters.AddRange(param);
                cmd.Parameters.Add("OUTVAL", (ReturnTypeOutVal == 1 ? SqlDbType.UniqueIdentifier : ReturnTypeOutVal == 2 ? SqlDbType.Int : SqlDbType.NVarChar), -1).Direction = ParameterDirection.Output;
                if (con.State != ConnectionState.Open)
                {
                    con.Open();
                }
                cmd.ExecuteNonQuery();
                con.Close();


                res.Outval = cmd.Parameters["OUTVAL"].Value;
            }
            catch (Exception ex)
            {
                res.Outval = ex.Message;
            }
            return(res);
        }
Пример #3
0
 /// <summary>
 /// Execute Procedure With Datatable
 /// 12/8/2017 pritey
 /// </summary>
 /// <param name="ProcedureName"></param>
 /// <param name="dtData"></param>
 /// <param name="TableParamName"></param>
 /// <param name="ReturnTypeOutVal"></param>
 /// <returns></returns>
 public MEMBERS.SQLReturnValue ExecuteProcedureWithDatatable(string ProcedureName, DataTable dtData, string TableParamName, int ReturnTypeOutVal)
 {
     try
     {
         SqlCommand cmd = new SqlCommand();
         cmd.CommandText = ProcedureName;
         cmd.Connection  = con;
         cmd.CommandType = CommandType.StoredProcedure;
         if (dtData != null)
         {
             SqlParameter ParamTb = new SqlParameter("@" + TableParamName, dtData);
             ParamTb.SqlDbType = SqlDbType.Structured;
             cmd.Parameters.Add(ParamTb);
         }
         cmd.Parameters.Add("OUTVAL", (ReturnTypeOutVal == 1 ? SqlDbType.UniqueIdentifier : ReturnTypeOutVal == 2 ? SqlDbType.Int : SqlDbType.NVarChar), -1).Direction = ParameterDirection.Output;
         if (con.State != ConnectionState.Open)
         {
             con.Open();
         }
         cmd.ExecuteNonQuery();
         con.Close();
         MEMBERS.SQLReturnValue res = new MEMBERS.SQLReturnValue();
         res.Outval = cmd.Parameters["OUTVAL"].Value;
         return(res);
     }
     catch (Exception ee)
     {
         MEMBERS.SQLReturnValue res = new MEMBERS.SQLReturnValue();
         res.Outval = ee.Message;
         return(res);
     }
 }