Ejemplo n.º 1
0
        public DataSet ExecuteResourceDataSet()
        {
            var ret = new DataSet();

            var parms = new List <Param>();

            foreach (StoredProcedureParameter parm in _params)
            {
                switch (parm.DBValueType)
                {
                case ParameterType.Structured:
                    parms.Add(Param.createresourceParam(parm.Name, SqlDbType.Structured, parm.Value,
                                                        ParameterDirection.In));
                    break;

                case ParameterType.DBString:
                    if (parm.Direction == ParameterDirection.In)
                    {
                        parms.Add(Param.createresourceParam(parm.Name, DbType.String, parm.Value, ParameterDirection.In));
                    }
                    else
                    {
                        parms.Add(Param.createresourceParam(parm.Name, DbType.String, parm.Value, ParameterDirection.Out));
                    }
                    break;

                case ParameterType.DBDateTime:
                    parms.Add(Param.createresourceParam(parm.Name, DbType.DateTime, parm.Value, ParameterDirection.In));
                    break;

                case ParameterType.DBNvar:
                    parms.Add(Param.createresourceParam(parm.Name, DbType.String, parm.Value, ParameterDirection.In));
                    break;

                //handles any case that is not specificed above with default of appaneding to the string
                default:
                    parms.Add(Param.createresourceParam(parm.Name, DbType.String, parm.Value, ParameterDirection.In));
                    break;
                }
            }
            using (var sqlConn = new SqlConnection(ReadConnectionString()))
            {
                try
                {
                    sqlConn.Open();
                }
                catch
                {
                    throw;
                }
                using (var sqlCommand = new SqlCommand(_storedProcedureName, sqlConn))
                {
                    foreach (StoredProcedureParameter parm in _params)
                    {
                        sqlCommand.Parameters.AddWithValue(parm.Name, parm.Value);
                    }
                    sqlCommand.CommandTimeout = _commandTimeOut;
                    sqlCommand.CommandType    = CommandType.StoredProcedure;
                    var mySqlDataAdapter = new SqlDataAdapter();
                    mySqlDataAdapter.SelectCommand = sqlCommand;
                    mySqlDataAdapter.Fill(ret);
                }
            }
            return(ret);
        }
Ejemplo n.º 2
0
        /// <summary>
        /// Create the dataset.  This should be only visible to the DAL layer.  For MS SQL DBs
        /// </summary>
        /// <returns></returns>
        public void ExecuteDataSetWithOutputParam(out DataTable dtRet, out String sID)
        {
            DataTable ret = null;
            String    ID  = string.Empty;

            var parms = new List <Param>();

            foreach (StoredProcedureParameter parm in _params)
            {
                //JBienvenu 19202 2012-12-28 switch: Implemented ParameterTypeAdapter system.
                switch (parm.DBValueType)
                {
                case ParameterType.Structured:
                    parms.Add(Param.createParam(parm.Name, new StructuredAdapter(), parm.Value,
                                                ParameterDirection.In));
                    break;

                case ParameterType.DBString:
                    parms.Add(Param.createParam(parm.Name, new StringAdapter(), parm.Value, ParameterDirection.In));
                    break;

                case ParameterType.DBDateTime:
                    parms.Add(Param.createParam(parm.Name, new DateTimeAdapter(), parm.Value, ParameterDirection.In));
                    break;

                case ParameterType.DBNvar:
                    parms.Add(Param.createParam(parm.Name, new NvarAdapter(), parm.Value, ParameterDirection.In));
                    break;

                //handles any case that is not specificed above with default of appaneding to the string
                default:
                    parms.Add(Param.createParam(parm.Name, new StringAdapter(), parm.Value, ParameterDirection.In));
                    break;
                }
            }
            using (var sqlConn = new SqlConnection(ReadConnectionString()))
            {
                try
                {
                    sqlConn.Open();
                }
                catch
                {
                    throw;
                }
                using (var sqlCommand = new SqlCommand(_storedProcedureName, sqlConn))
                {
                    //JBienvenu 19202 2012-12-28 foreach: Changed to use parms (which were not used at all before). Implemented ParameterTypeAdapter system.
                    parms.ForEach((parm) => parm.ToSqlParameter(sqlCommand));

                    SqlParameter myparam3 = sqlCommand.Parameters.Add("@referenceTableType", SqlDbType.VarChar, 10);
                    myparam3.Direction = System.Data.ParameterDirection.Output;

                    sqlCommand.CommandTimeout = _commandTimeOut;
                    sqlCommand.CommandType    = CommandType.StoredProcedure;
                    SqlDataReader reader = sqlCommand.ExecuteReader();
                    ret = GetDataTableFromDataReader(reader);
                    reader.Close();
                    ID = (string)sqlCommand.Parameters["@referenceTableType"].Value;
                    reader.Close();
                }
            }
            dtRet = ret; sID = ID;
        }
Ejemplo n.º 3
0
        /// <summary>
        /// Create the dataset.  This should be only visible to the DAL layer.  For MS SQL DBs
        /// </summary>
        /// <returns></returns>
        public DataSet ExecuteMultipleDataSet()
        {
            var ret = new DataSet();

            var parms = new List <Param>();

            foreach (StoredProcedureParameter parm in _params)
            {
                //JBienvenu 19202 2012-12-28 switch: Implemented ParameterTypeAdapter system.
                switch (parm.DBValueType)
                {
                case ParameterType.Structured:
                    parms.Add(Param.createParam(parm.Name, new StructuredAdapter(), parm.Value,
                                                ParameterDirection.In));
                    break;

                case ParameterType.DBString:
                    if (parm.Direction == ParameterDirection.In)
                    {
                        parms.Add(Param.createParam(parm.Name, new StringAdapter(), parm.Value, ParameterDirection.In));
                    }
                    else
                    {
                        parms.Add(Param.createParam(parm.Name, new StringAdapter(), parm.Value, ParameterDirection.Out));
                    }
                    break;

                case ParameterType.DBDateTime:
                    parms.Add(Param.createParam(parm.Name, new DateTimeAdapter(), parm.Value, ParameterDirection.In));
                    break;

                case ParameterType.DBNvar:
                    parms.Add(Param.createParam(parm.Name, new NvarAdapter(), parm.Value, ParameterDirection.In));
                    break;

                case ParameterType.DBInteger:
                    parms.Add(Param.createParam(parm.Name, new IntegerAdapter(), parm.Value, ParameterDirection.In));
                    break;

                //handles any case that is not specificed above with default of appaneding to the string
                default:
                    parms.Add(Param.createParam(parm.Name, new StringAdapter(), parm.Value, ParameterDirection.In));
                    break;
                }
            }
            using (var sqlConn = new SqlConnection(ReadConnectionString()))
            {
                try
                {
                    sqlConn.Open();
                }
                catch
                {
                    throw;
                }
                using (var sqlCommand = new SqlCommand(_storedProcedureName, sqlConn))
                {
                    //JBienvenu 19202 2012-12-28 foreach: Changed to use parms (which were not used at all before). Implemented ParameterTypeAdapter system.
                    parms.ForEach((parm) => parm.ToSqlParameter(sqlCommand));
                    sqlCommand.CommandTimeout = _commandTimeOut;
                    sqlCommand.CommandType    = CommandType.StoredProcedure;
                    var mySqlDataAdapter = new SqlDataAdapter();
                    mySqlDataAdapter.SelectCommand = sqlCommand;
                    mySqlDataAdapter.Fill(ret);
                }
            }
            return(ret);
        }
Ejemplo n.º 4
0
        /// <summary>
        /// Create the dataset.  This should be only visible to the DAL layer.  For MS SQL DBs
        /// Note:   THe core functionality for this procedure has been cloned to executeNonQuery() to handle routines that
        ///         do not expect any type of return value.
        /// </summary>
        /// <returns></returns>
        private DataTable MSSQL_executeDataSet()
        {
            var ret = new DataTable();

            var parms = new List <Param>();

            foreach (StoredProcedureParameter parm in _params)
            {
                //JBienvenu 19202 2012-12-28 switch: Implemented ParameterTypeAdapter system.
                switch (parm.DBValueType)
                {
                case ParameterType.Structured:
                    parms.Add(Param.createParam(parm.Name, new StructuredAdapter(), parm.Value,
                                                ParameterDirection.In));
                    break;

                case ParameterType.DBString:
                    parms.Add(Param.createParam(parm.Name, new StringAdapter(), parm.Value, ParameterDirection.In));
                    break;

                case ParameterType.DBDateTime:
                    parms.Add(Param.createParam(parm.Name, new DateTimeAdapter(), parm.Value, ParameterDirection.In));
                    break;

                case ParameterType.DBNvar:
                    parms.Add(Param.createParam(parm.Name, new NvarAdapter(), parm.Value, ParameterDirection.In));
                    break;

                case ParameterType.DBVarBinary:
                    parms.Add(Param.createParam(parm.Name, new VarBinaryAdapter(), parm.Value, ParameterDirection.In));
                    break;

                default:        //handles any case that is not specificed above with default of appaneding to the string
                    parms.Add(Param.createParam(parm.Name, new StringAdapter(), parm.Value, ParameterDirection.In));
                    break;
                }
            }
            using (var sqlConn = new SqlConnection(ReadConnectionString()))
            {
                try
                {
                    sqlConn.Open();
                }
                catch
                {
                    throw;
                }
                using (var sqlCommand = new SqlCommand(_storedProcedureName, sqlConn))
                {
                    //JBienvenu 19202 2012-12-28 foreach: Changed to use parms (which were not used at all before). Implemented ParameterTypeAdapter system.
                    parms.ForEach((parm) => parm.ToSqlParameter(sqlCommand));
                    sqlCommand.CommandTimeout = _commandTimeOut;
                    sqlCommand.CommandType    = CommandType.StoredProcedure;
                    using (SqlDataReader sqlDataReader = sqlCommand.ExecuteReader())
                    {
                        if (sqlDataReader.VisibleFieldCount > 0)
                        {
                            foreach (DataRow dRow in sqlDataReader.GetSchemaTable().Rows)
                            {
                                if (!(dRow["ColumnName"].ToString().Trim().Equals("")))
                                {
                                    ret.Columns.Add(dRow["ColumnName"].ToString());
                                    ret.Columns[dRow["ColumnName"].ToString()].ReadOnly    = false;
                                    ret.Columns[dRow["ColumnName"].ToString()].AllowDBNull = true;
                                    ret.Columns[dRow["ColumnName"].ToString()].DataType    =
                                        Type.GetType(dRow["DataType"].ToString());
                                }
                            }
                            ret.Load(sqlDataReader);
                        }
                    }
                }
            }
            return(ret);
        }