Example #1
0
        public static DataSet CallStoredProcedure(DBInstance dbInstance, string _Sql, ParameterItem[] _inParameters, ParameterItem[] _outParameters)
        {
            List<SqlParameter> parameters = new List<SqlParameter>();
            try
            {

                if (_inParameters != null)
                {
                    foreach (ParameterItem item in _inParameters)
                    {
                        if (item != null)
                        {
                            if (item.ParameterName != null && item.ParameterValue != null)
                            {
                                SqlParameter parm = new SqlParameter(item.ParameterName, SqlDbType.VarChar);
                                parm.Direction = ParameterDirection.Input;
                                parm.Value = item.ParameterValue;
                                parameters.Add(parm);
                            }
                        }
                    }
                }

                if (_outParameters != null)
                {
                    foreach (ParameterItem item in _outParameters)
                    {
                        if (item != null)
                        {
                            if (item.ParameterName != null)
                            {
                                SqlParameter parm = new SqlParameter(item.ParameterName, SqlDbType.VarChar);//�д�
                                parm.Direction = ParameterDirection.Output;
                                parameters.Add(parm);
                            }
                        }
                    }
                }

                DataSet ds = SqlHelper.ExecuteQuery(dbInstance.Connection.ConnectionString, CommandType.StoredProcedure, _Sql, parameters.ToArray());
                return ds;

            }
            catch (UtilException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw new UtilException(ex.Message, ex);
            }
        }
Example #2
0
        /// <summary>
        /// Execute a select query that will return a result set
        /// </summary>
        public DataSet ExecuteQuery(string cmdText, ParameterItem[] commandParameters)
        {
            //Create the command and connection
            OleDbCommand cmd = new OleDbCommand();
            OleDbConnection conn = new OleDbConnection(ConnectionString);

            try
            {
                //Prepare the command to execute
                if (conn.State != ConnectionState.Open)
                    conn.Open();

                DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

                string sheetName = dt.Rows[0]["TABLE_NAME"].ToString();

                Regex r = new Regex(@"\[\w*\$\]", RegexOptions.IgnoreCase);
                //string a = r.Match(cmdText, 0).Value;
                //string b = string.Format("[{0}]", sheetName);
                //string c = cmdText.Replace(a, b);

                //PrepareCommand(cmd, conn, null, CommandType.Text, cmdText.Replace(r.Match(cmdText, 0).Value, string.Format("[{0}]", sheetName)), commandParameters);
                PrepareCommand(cmd, conn, null, CommandType.Text, cmdText, commandParameters);

                OleDbDataAdapter adp = new OleDbDataAdapter(cmd);

                //Execute the query, fill dataset
                DataSet ds = new DataSet();

                adp.Fill(ds);

                cmd.Parameters.Clear();

                return ds;

            }
            catch
            {

                //If an error occurs close the connection as the reader will not be used and we expect it to close the connection
                throw;
            }
            finally
            {
                conn.Close();
            }
        }
Example #3
0
        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, ParameterItem[] commandParameters)
        {
            //Open the connection if required
            if (conn.State != ConnectionState.Open)
                conn.Open();

            //Set up the command
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            cmd.CommandType = cmdType;
            cmd.Transaction = trans;

            //Bind it to the transaction if it exists
            //if (trans != null)
            //    cmd.Transaction = trans;

            // Bind the parameters passed in
            if (commandParameters != null)
            {
                foreach (ParameterItem item in commandParameters)
                {
                    if (item != null)   //ADD BY Andy_liu  2007-3-12 14:33:48
                    {
                        if (item.ParameterName != null && item.ParameterValue != null)
                        {
                            SqlParameter parm = new SqlParameter(item.ParameterName, item.ParameterValue);
                            cmd.Parameters.Add(parm);
                        }
                    }

                }
            }
        }
Example #4
0
        public static DataSet ExecuteQuery(string cmdText, ParameterItem[] commandParameters)
        {
            //Create the command and connection
            SqlCommand cmd = new SqlCommand();
            SqlConnection conn = new SqlConnection(ConnectionString);

            try
            {
                //Prepare the command to execute
                PrepareCommand(cmd, conn, null, CommandType.Text, cmdText, commandParameters);

                SqlDataAdapter adp = new SqlDataAdapter(cmd);

                //Execute the query, fill dataset
                DataSet ds = new DataSet();
                adp.Fill(ds);
                cmd.Parameters.Clear();
                return ds;

            }
            catch
            {

                //If an error occurs close the connection as the reader will not be used and we expect it to close the connection
                throw;
            }
            finally
            {
                conn.Close();
            }
        }